Valuable Sources of Information

Helpful tools to answer questions and troubleshoot issues for Microsoft SQL Server. 

 

SSRS Expression Cheat Sheet

 Problems Expression
Return first day of current Week
(ex. Default Start Date parameter to return WTD)
Expression on the parameter default value:
=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Expression Output Example: 11/7/2010 12:00:00 AM
Return first day of current Month
(ex. Default Start Date parameter to return MTD)

Expression on the parameter default value:
 =DateAdd("d",-(Day(today)-1), Today)

or

=DateSerial( year(today()), month(today()), 1)
Expression Output Example:11/1/2010 12:00:00 AM

Return first day of current year
ex. Default Start Date parameter to return YTD)

Expression on the parameter default value:
 

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)
Expression Output Example: 1/1/2010 12:00:00 AM

Return period over period
(ex. Default date parameters to a rolling year)

Expression on the parameter default value:

Week over Week
=DateAdd("ww",-1, Today)

Month over Month
=DateAdd("m",-1,Today)

Year over Year
=DateAdd("yyyy",-1, Today)

Expression Output Example:10/9/2010 12:00:00 AM
Return current month name Expression in Text Box:
=MonthName(Month(Today()))

Expression Output Example:November
Uppercase fields Expression in Text Box:
=UCASE(Fields!FieldName.Value)

Expression Output Example:NOVEMBER
Convert text to proper case
(ex. 1st letter in each word is uppercase)
Expression in Text Box:
=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Replace NULL with another value Expression in Text Box:
=iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
Alternating row color (Banding effect) BackgroundColor property on Text Box:
=iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
Handling division by zero Expression in Text Box:

=iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/

iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
security number) Expression in Text Box:
=Replace(Fields!EmailAddress.Value,"-","")

 

Common SSRS Problems and Solutions

 Problems Solutions
Parameter with dropdown box

Solution: Create an additional dataset that with populate the parameter dropdown box.  Change the available values page of the parameter to get values from a query.  Select the dataset that you created to populate the dropdown box.

Cascading Parameters

Solution: Cascading parameters means one parameter can populate the contents of the next parameter.  Create additional datasets to populate each of the dropdown box parameters you want.  When writing these datasets you will want to make sure that the lower level only shows values available in the higher level.  The datasets may look like this:

Select distinct EnglishCountryRegionName From DimGeography

Select distinct StateProvinceName From DimGeography

Where EnglishCountryRegionName = @Country

Select distinct City From DimGeography
Where EnglishCountryRegionName = @Country and StateProvinceName = @State

The country dataset passes the country selected into the state dataset.  Next, the State dataset passes into the city dataset the country and state.  This way when you get to the City parameter you only see cities that are in the selected country and state.

Change the available values page of the parameters to get values from a query.  Select the dataset that you created to populate the dropdown box

Create a report template

Solution: Copy a Report you design to one of the following folders:

64 bit location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

32 bit location: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Deploying to SharePoint

Solution: Install and configure Reporting Services add-in for SharePoint for your version of SharePoint.  Set the Deployment properties of the Report project to the following:

TargetDatasetFolder

http://yourservername/site/library/datasets

TargetDataSourceFolder

http://yourservername/site/library/datasources

TargetReportFolder

http://yourservername/site/library/reports

TargetReportPartFolder

http://yourservername/site/library/reportparts

TargetServerURL

http://yourservername/

Setting up a drillthrough report Solution: Right-click on the textbox you wish to allow the drillthrough action and select Text Box Properties.  Select the Action page and select which type of object you want to drillthrough to (Go to report, Go to bookmark, or Go to URL).
Creating a Document Map

Solution: Document Maps provide an easy way to navigate through a large report with a table of contents for your report.  To add a Document Map to a report right-click on a row or column grouping (this assumes you have already added a grouping to the report) and select Group Properties.  On the Advanced page you will find the Document map property where you will select the fields to add to the table of contents.

Copyright 2014 by Pragmatic Works