SSRS: Column Duplication (Fixed Columns) on Printouts

This blog post covers implementing something like the "fixed columns" in excel which allow you to have a column (or columns) repeat on multiple pages. This only happens whey you view the report in a non-interactive way (i.e. not when viewing online).

This  blog post was put together using SQL Server 2008R2 and Report Builder 3 but it might be applicable to other versions.


Open your report in Report Builder (I'm using 3.0) and add in your duplicate column (you will need to do some sizing - for example setting up your printer format to A4 or US Letter and expanding your page 1 columns to cover the entire width of the page);

After you have added in your duplicate column select the column, right-click the heading and select "Column Visibility";

Enter the expression;

[&RenderFormat.IsInteractive]

Into the "Show or hide based on an expression" entry box.

The column will now only appear when the report is not being viewed interactively (to test you can click "Run" and then choose "Print Layout").

SSRS: Scheduling Reports Using SQL Server Reporting Services 2008

This blog post gives detailed step-by-step instructions on how to schedule reports to run using SQL Server Reporting Services 2008 Standard Edition. Additional features are available in the Enterprise Edition and these will be covered separately.


Prerequisites
The example Reporting Services project used throughout this guide (put together using Bussiness Intelliegnece Development Studio) is available for download here. You might want to download and unzip this file, load it into the Business Intelignence Development Studio and deploy the reports to your server before attempting to continue with this step-by-step guide.

Accessing Reporting Services
In order to access the Self Service interface for reporting services you need to point your web browser to the page:

https:///reports

You will then see a list of folders for the reports available on that server and the "Data Sources" folder which contains shared data sources (that we won't be using during this demo).

Click on "GoogleKnolSSRSProject".

You can now see three reports; ComplexParameters, SimpleParameter, and NoParameter.

Scheduling A Report With No Parameters
To start things out simply we'll begin by scheduling the "NoParameter" report. Click on it.

This is the report itself (it simply displays a label showing information about this report). Click on the "New Subscription" button at the top left.

This screen allows you to create a new Subscription for this report. As you can see you have to specify two things; the report delivery options (how you want to receive it), and the schedule for delivering the report to you.

The delivery options avaialble to you will depend on how the SQL Server you are using has been setup. The default (if available) is via email but I'll take you through both email and delivery to a fileshare (which is sometimes more convienient if you're looking at something that is accessed by multiple people for example).

Deploying the Report via E-Mail
Select "E-Mail" in the drop down:

The screen will change to show you options available for an email. These are all pretty self-explanatory so I don't intend to go through them one at a time. For the purposes of this example I'm going to be outputting to report into Excel so change the "Render Format" from MHTML (web archive) to "Excel".

It's worth noting that the version of this screen you see is dependant on the permissions you have on the server. If you are a "normal user" then you get the screen shown above. If you have extra permisisons you will see extra fields (i.e. CC, BCC, Reply-To, Comment, and the "To" field becomes editable so you can send the subscription to multiple users).

Now that what we want to do is defined click "Select Schedule":

This new screen presents the options you have in the scheduler. For the purposes of this test I'm going to select this report to run on weekdays a few minutes in the future (just so it runs while I'm writing this!).

At the bottom of this screen is an option to have a start/end schedule for the report:

I won't be using this but feel free to experiement.

Once you've selected the schedule you want click "OK".

You are now presented with the Delivery Options and Schedule you've created. Click "OK" to start the subscription.

After the subscription time has passed an email will arrive with the report:

Deploying the Report via a File Share
Select "Windows File Share" in the "Delivered by:" drop down:

Pick a schedule and click "OK".

NOTE: SQL Server needs to be able to "see" the share to which you're trying to write the file. If it can't (for whatever reason) you will see a message like:

You will need to contact the person reponsible for Administering your server in order to get these permissions problems resolved (especially if you are trying to write this to a "private" share).

Other Deployment Options
Other options are available depending on how your SQL Server is configured (for example if your SQL Server is running in SharePoint integrated mode).

Scheduling a Report with a Simple Parameter
Return to the report selection screen:

Select the "SimpleParameter" report:

Unlike the pervious report you're now being prompted for a parameter before the report is executed. Enter the value "5" and then click "View Report". As you can see this report is pretty similar to the previous one except that it displays the value of the parameter passed to it. Click on "New Subscription" at the top:

This screen is exactly the same as the previous screen so I don't intend going over the Report Delivery Options (top) and the Subscription Processing Options (middle) again. The new section at the bottom is "Report Parameter Values". This section allows you to specify parameters than should be used to run the report.

Enter the value "7" and setup an appropirate schedule and then click "OK".

At the scheduled time the report will be processed and you will receive an email:

You'll notice that leaving the Render format set to "MHTML (web archive)" gives you a nice email in outlook that you can see without having to open an attachment (which can be a problem on certain mobile devices).

Scheduling Reports with Complex Parameters
Return to the report selection screen:

Select the "ComplexParameters" report:

As you can see this report takes four parameters; Day and Time and both pciked from a list (day has a default value, time does not), and Attendees and Guests are simple paremeters (attendees has a default, guests does not).

Pick the time "08:00-11:59" and enter "2" for guests and click "View Report":

Again this report simply re-displays the values of the parameters passed into it. Click on "New Subscription":

The "Report Parameter Values" section at the bottom of the Subscription screen allows you to use the drop downs to pick values for Day and Time OR, in the case of Day, to check the "Use Default" checkbox.

Check the two "Use Default" checkboxes (for Day and Attendees) pick the time 08:00-11:59 and enter "1" for Guests set the Report Delivery Options and pick a schedule and click "OK".

After a few minutes an email will arrive:

The advantages of using Default parameters for scheduling is that, for example, if you are running monthly reports for a full month then if the Default value for the parameter is "last month" then by selecting to use the default when the value of "last month" (i.e. Jan > Feb > Mar > etc) changes the report will automatically pick up the change. If, in February, you ran the report and specified January when the report ran again in March it would just run again for January.

SSRS: Working With Multiple Value Parameters

It is generally a good idea when writing reports to provide some space on the report to display the parameter values the user is using to run the report. It makes it a lot easier to deal with helpdesk requests along the lines of "Why's it doing this?" accompanied by a screen shot or a saved report - if you include the parameter values in the report then it should be fairly easy to duplicate it.

The problem comes when you're dealing with Multiple Value Parameters. For example if I look at the General Ledger Accounts we have setup in our ERP System we have around 804. Most users will select a couple, all, or one to report on. How do we make sure that enough meaningful information is displayed to help us debug any issues with the report?

The following function follows some simple rules;

• If the number of selected records is the same as the number of records available in the dataset then display "All",
• If a single record is selected then display it,
• If more than 10 records are selected display "Multiple", and otherwise
• Build a comma-separated list of the selected labels making sure that the last item is separated by ", or " rather than a just a "," (it does this by joining together all the selected items separated by "@@@@@", removing the last item and then adding it back in with the separator)

The code to process these rules is;

=IIF(
  Count(Fields!KEY.Value, "LIST_GLACCOUNTS") =
        Parameters!ACCOUNTLIST.Count, "All",
    IIF(
       Parameters!ACCOUNTLIST.Count = 1,
       Join(Parameters!ACCOUNTLIST.Label, ""),
       IIF(
         Parameters!ACCOUNTLIST.Count > 10,
         "Multiple",
         Replace(
           Left(
             Join(
               Parameters!ACCOUNTLIST.Label, "@@@@@"),
             InStrRev(
               Join(
                 Parameters!ACCOUNTLIST.Label, 
                   "@@@@@"),
               "@@@@@") - IIF( Parameters!ACCOUNTLIST.Count < 2, 0, 1)),
           "@@@@@", 
           ", ") + ", or " + 
           Parameters!ACCOUNTLIST.Label(
             Parameters!ACCOUNTLIST.Count-1))))

SSRS: Commonly Used Date/Time Functions

The list below is a few simple functions which I've come across for generating various dates based on the current date/time (using the Now() function).

Hopefully they'll prove useful and as new ones come up I'll update this post;

Last day of the current year,
=DateSerial(Year(Now()), 12, 31)

Last day of previous month
=DateAdd(""d"", -1, DateSerial(Year(Now()), Month(Now()), 1))

First date of previous month
=DateAdd(""m"", -1, DateSerial(Year(Now()), Month(Now()), 1))

First day of last year
=DateSerial(Year(Now())-1, 1, 1)

Last day of last year
=DateSerial(Year(Now())-1, 12, 31)

Last day of this year
=DateSerial(Year(Now()), 12, 31)

First day of this year
=DateSerial(Year(Now()), 1, 1)

SSRS: Common Issues with #Error and IIF

Is there anything more frustrating then seeing this non-error message and wondering what of massively complicated function you've just entered is causing the problem (let's not forget that the Expression editor in Report Builder 3 doesn't allow you to do multi-line statements - just to make it easier!). 

Drives me wild.

Having worked with other people to debug functions the one thing that seemed to really help people was giving them a better understanding of how IIF worked (oddly IIF was the cause of *most* of the problems!).

The IIF function (and the key word here is "function") requires ALL the values passed to it to be valid.

For example if you try IIF( a > 0, 200/a, 0) you will get a "Divide By Zero" error when the value for a is zero because SSRS will still try and evaluate 200/a even though it will never be displayed.

The (clumsy) way around this would be to change the statement to;

=IIF(a > 0, 200/IIF(a = 0, 1, a), 0)

This would prevent the error by setting a to 1 when it is used as the divisor. 

You should also watch out for this problem when using InStr or InStrRev in conjunction with a function to get a substring (as, for example, asking for the 9-character of a 8-character string will give you the dreaded #Error).

If ever there was a bunch of functions crying out for a default value to return in the event of an an error it's these!