SQL Scrapbook

Short and simple SQL Server posts

Archive for the ‘SSRS’ Category

How to generate RDL file from report manager?

leave a comment »

Your boss asked you to change a report that’s currently in production. You realize that you don’t have a RDL or a solution file for that dirty report. What do you do?
The process of regenerating the rdl from a report on the report manager is slightly different in 2005 and 2008.
2005: Navigate to the report properties page. In the report definition section click edit, a file download page will appear, click save and specify the location where you want to save the rdl file.
2008: On the open menu of the report click download and specify the location where you want to save the rdl file.

Once you have the rdl file you can add this as an existing item to a report server project.

Written by sqlscrapbook

December 28, 2010 at 8:27 pm

Posted in SSRS

Tagged with ,

Different background color for alternate rows

leave a comment »

It’s a very common request to alternate the background color of rows in SQL Server Reporting Services. There is a simple way to this and we will see how.

In the properties window of the detail row(of the data region), find background color and choose expression. In the edit expression window, paste this expression.

=IIF( ROWNUMBER (NOTHING) MOD 2, “Silver“,”Grey“)

Click Ok and you are all set.

Written by sqlscrapbook

July 24, 2010 at 1:32 pm

Posted in SSRS

Tagged with

Custom message when there are no rows in the data source

leave a comment »

Some times when there is no data in the data source the SSRS report created on top of this will show only the header information. There is an inbuilt option to show your own message when this is the case. In this quick post i will demonstrate how to do this.First, i will create a dataset and make sure that this dataset doesn’t return any rows.

SELECT    TOP 0 FirstName, LastName, [State]
FROM dbo.ResultTable

If you take a close look at the above query, i used TOP 0 to make sure that the dataset returns 0 rows. Now that we have the dataset lets go ahead with the report design. I created a table report and the fields are First Name, Last Name and State.

Since there are no rows returned when you run the report we will see only the report headers.

Now to display a custom message when there are no rows there is a property called “NOROWS”. Select the table and in the properties pane find NOROWS.

Click on the expression button and enter any custom text.

Now that you have the NOROWS property configured you will see this message when you run the report and there are no rows returned by the dataset.

Conclusion: NOROWS property in SSRS can be set to display a user friendly message when the data set doesn’t return any data.

Written by sqlscrapbook

July 13, 2010 at 10:06 pm

Posted in SSRS

Tagged with ,