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.

Be Sociable, Share!

• 
• 

Related posts:

1. Retrieve SSRS Report Definition, Role Assignments, Executions Using T-SQL …
2. Resolving SSRS and PowerShell New-WebServiceProxy Namespace Issue …
3. Exploring SSRS 2008 R2 Report Manager …
4. SQLSaturday#65 SSRS 2008/2008 R2 from the Ground Up …
5. Download SSRS Parameterized Reports in PDF with PowerShell …
6. How to Download All Your SSRS Report Definitions (RDL files) Using PowerShell …