Just wanted to share some common “tips” or “tricks” I find myself using over and over again when creating reports.
I think I’ve had this draft for a while, and just never was able to publish it because things got so busy. Anyway, I will just publish in parts. Better late (and something) than never (and nothing!)
Common Report Tasks
Common page headers and footers
Report (reportname) generated by DOMAIN/User on 02/03/2010 4:51:03 PM
="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals.ExecutionTime"
Page X of Y
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
If you have start and end date parameters, and want to display them in your header or footer, with date formatted as MMM dd, YYYY
="Report Details from " & Format(Parameters!StartDate.Value, "MMM dd, yyyy") & " to " & Format(Parameters!EndDate.Value, "MMM dd, yyyy")
How to make alternating rows colored
You have to set the expression for the background color to something like:
=IIF(RowNumber("MyDataSet") Mod 2 = 0, Nothing, "LightCyan")
How to format negative numbers
Usually I will be asked to put negative numbers in brackets, and to make these numbers red. So here’s the 2 step process:
In the Format property, use this expression. Note there’s no equal sign.
#,##0.00;(#,##0.00)
In the Color property, use this expression:
=IIF(Fields!Amount.Value < 0, "Red", "Black")
Get today’s date
=Today()
Pass parameters through the URL
Here’s an example. Note that parameter names are case sensitive.
http://server/ReportServer/Pages/ReportViewer.aspx?%2fFolderName%2fReportName&rs;:Command=Render&StartDate;=1/1/2009&Param2;=Value
or
http://server/ReportServer?/FolderName/ReportName&rs;:Command=Render&StartDate;=2/2/2009&Param2;=Value
Other formatting parameters you can pass through the URL:
&rs;:Parameters=Collapsed
&rs;:Parameters=true
&rs;:Parameters=false
&rs;:toolbar=false
&rs;:format=EXCEL
&rs;:format=PDF
&rs;:Format=EXCEL&rc;:OmitFormulas=true
&rs;:StyleSheet=CustomStyle
&rs;:ClearSession=true
How to get number of rows in your result set
=CountRows("YourDataSet")
How to join selected multi-valued parameter values in a single sring
=InStr(Join(Parameters!which.Value, ", ")
How to sort a report that has been exported to Excel
– Open the excel report
– Select all (Ctrl + A)
– Go to Format > Cells
– Uncheck “Merge Cells”
– Click OK to save changes, then retry sort.
How to format date values
Important thing, make sure your value – whether it’s a field or a parameter – is a DateTime data type not a String.
=Format(Parameters!Date.Value,"dd-MM-yyyy") --- 15-03-2010
=Format(Parameters!Date.Value,"dd/MM/yyyy") --- 15/03/2010
=Format(Parameters!Date.Value,"MMM-dd-yyyy") --- Mar-15-2010
=Format(Parameters!Date.Value,"MMM-dd-yy") --- Mar-15-10
How to interactively sort multiple columns
Sort your first field, then hold SHIFT, then sort other fields.
Errors
Invalid object name ‘sysdatabases’
When you configure SSRS, and in the Database Setup you get “Invalid object name ‘sysdatabase'”, then this means the account/login you are using to connect to the Report database is set to something other than master.
Solution: Check this login, and set the default database to master.
The latest Service Packs should have fixed this issue already though.
Useful References
Considerations for Installing Reporting Services
Installing SQL Server Reporting on a NonDefault Website. This is a pretty good step-by-step by Greg Van Mullem.
Post Installation Checklist for SSRS
Configuring SQL Server Report Server URLs
Storing Credentials in Data Source
Deploying SQL Server Reports in SharePoint mode – Resource 1
Deploying SQL Server Reports in SharePoint mode – Resource 2
SSRS Report Expressions
Few useful takeaways:
– You can embed reports in your web page, or another application. You can use the ReportViewer control – download the redistributable (ReportViewer Control 2005 Redistributable) or (ReportViewer Control Redistributable 2008)
– If you are storing the credential in the data source, and if you are using a Windows (trusted) account, then you need to make sure you check Use as Windows credentials when connecting to the data source otherwise authentication will fail.
What a great list! Thanks for sharing. I learned a couple new things today.
Quite handy and useful stuff, thanks for sharing
Great find! Though, where’s part two?