Reporting Services (SSRS) Tutorial Video….AND ONLINE CLASS!

If you’re looking to ramp up on Reporting Services, I recently made a tutorial video that you might find interesting.  While the video Focuses on building reports with SQL Server Data Tools (Visual Studio), I’m also teaching a monthly 4 hour online “Reporting Services 101” class! The class focuses more on Report Builder and SharePoint:  In that class we take it step by step….starting at step one to give you a good foundation, and by the end we’re cranking out some pretty robust reports.  

You can check out the details for the class here: http://sharepoint.rackspace.com/ssrs-training

You can check out the short tutorial video of building a report using SQL Server Data Tools (Visual Studio) below. 

Reporting Services using SSDT



How to fix Reporting Services broken images when hosting reports in a PerformancePoint dashboard.

I’ve been looking for the solution to this problem on and off for months, and finally stumbled upon the answer.

The scenario was this:

I placed an SSRS report into a PPS dashboard. The SSRS report has nothing but a gauge, and a table with some related data. For some reason the gauge image only shows up when I have the “show toolbar” option checked, otherwise the gauge shows up as a broken image or red x. In either case, the data for the table always shows up. If I navigate to the report server directly and view the report I have no problems, it only seems to happen from within PPS (both in designer and once deployed).

Apparently when Reporting services is configured to authenticate via “Trusted Account” mode, you need to specify the URL of the Report Server proxy rather than just the URL to the Report Server.

So for example, when configuring the “Report Server URL” in your Performance Point SSRS report you would typically enter it as

http://server/reportserver

however if you’re experiencing the problem described above, try referencing it including _vti_bin in the URL like so

http://server/_vti_bin/reportserver

That seemed to do the trick for me. Hope this saves someone some frustration.

 

How do I get ReportItems referenced in my Report header to show on all pages?

In Reporting Services (SSRS) 2005 there are a few tricks to being able to display data items other than report parameters in the header of your report. For some reason if we return values in a dataset and want to use them in the header of the report, the values we want to use in that dataset must appear somewhere in the body of the report. Well, that’s easy enough. We can simply

  • Put a textbox in the body of the report.
  • Set its visibility to hidden
  • Drop in our dataset result
  • Reference the textbox in the header of our report like so: “=ReportItems!TextboxName.Value”.

This works great if your report is < 1 page. However if your report becomes more than 1 page, someone will eventually notice that your report header only displays those values on the first page of the report. If you need those values to appear on all pages of your report you’ll need to make a few additional tweeks to get there….namely:

  1. Add a function to our report to keep track of what our value is.
  2. Add that function call to the header of our report.

**UPDATE: The solution provided in this example only works while viewing reports online. I later noticed that while printing, or in “Print Preview” mode, items in my header were once again appearing as blank. Ultimately to work around this problem, I converted any item I needed to reference in my report header into a parameter, and set it to “internal” or “Hidden”.

To add code to our report, we’ll need to navigate to the report properties by clicking on “ReportàReport Properties…” (Remember the menu options in the report designer are context sensitive, so make sure you’re on the “Layout” tab of your report). This should open a new window, you’ll want to click on the “Code” tab and add the following function:

Dim lastDivision As String = Nothing

Function GetDivision(ByVal currentDivision As String) As String
If Not currentDivision = Nothing Then
lastDivision = currentDivision
End If
Return lastDivision
End Function

clip_image001[4]

If all has gone well, you should be looking at something similar to the image above. Click “OK” back to the designer.

Next we need to call our new function in our header to get the results. To do so all we need to type is “=Code.FunctionName(ReportItems!YourTextbox.Value)”. So in my example I’m getting a Division name that I’m looking up based on one of the report parameters, thus I named the function in this example “GetDivision”. The textbox that’s storing the value in the body of my report is named “DivisionName”……put that all together and my call in the header looks like this:

=Code.GetDivision(ReportItems!DivisionName.Value)

Now my division name will show up on all headers of all pages of my report….schweet.

You’ll probably want to change the VB we entered in the code window to more accurately reflect whatever value you’re retrieving (or more accurately, keeping track of across pages if you examine the code). Also note that if you want to do this for multiple values, you’ll need to do a bit more customization to the code, and add new variables.

Hope this helps.

 

How To Pass A Multi-Select Parameter to Stored Procedure from Reporting Services (SSRS)

I've seen this question asked about a million times on different message boards, so I figured I'd do a walk through on one of the ways we might solve this problem. The main problem people run into is that Reporting Services (SSRS) passes the entire set of selected values from a multi-select box as one comma separated string into their stored procedures parameter. This usually results in the report working fine as long as only one option is chosen, but it typically returns nothing if more than one value is selected, or you get an error because your parameter was probably expecting a key (int) value but it got a string instead.

There are a few different ways we can get around this problem (using scripting in SSRS and table-value parameters etc), but the easiest way I've found is to create a UDF that splits the string into a table of values, and then call that UDF in your report stored procedure. Some of the other ways like using table-value parameters are probably going to be faster, but if you're only going to be passing a few values this method performs pretty well.

I'm not going to go into the specifics of how you tie a report parameter to a stored procedure parameter in this post. I'm assuming anyone who's reading this probably already knows how to do that...otherwise you wouldn't be looking up how to do a multi-select parameter in your report. The only thing that should change about your parameter in SSRS is that you'll want to check that box that says "Allow Multiple Values" when you create it, so that your report allows the user to select multiple values. Everything else will stay the same in SSRS. What we'll be changing is how our stored procedure handles the value passed to it from SSRS...and that'll be done in the DB itself.

First off, we're going to create a new User Defined Function (UDF) in our database. This UDF will take a string of delimited values, and separate them into a table based on whatever character we tell it to split on, and then return the table to us. In the case of Reporting Services, we'll want to split our values using a comma ",". Here's the code to create our UDF we'll need to call:

/*================================================================

created by: Tavis Lovell

created on: 2009.07.01

description: This UDF is used when you need a stored procedure to

take in multiple values for a parameter. This UDF allows you to

split a comma (or anything else) seperated list of values and

returns then as a table so they can be compared againsts in the stored

procedure.

the udf should be called like this:

ThemeID IN (SELECT Convert(INT, VALUE) FROM dbo.Split(@p_ThemeID,','))

ModifiedBy        Date              Desc

--------------------------------------------------------------------

Tavis Lovell      2009.07.02        Initial Creation

--================================================================*/

      CREATE FUNCTION [dbo].[Split]

      (

            @List nvarchar(2000),

            @SplitOn nvarchar(5)

      )

      RETURNS @RtnValue table

      (

            Id int identity(1,1),

            Value nvarchar(100)

      )

      AS

      BEGIN

            While (Charindex(@SplitOn,@List)>0)

            Begin

                  Insert Into @RtnValue (value)

                  Select

                        Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

                  Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

            End

            Insert Into @RtnValue (Value)

            Select Value = ltrim(rtrim(@List))

     

            Return

      END

GO

Just copy the above code into query analyzer and run it in the same DB that the stored procedure for your report resides in.

Once we've created our UDF we'll need to make a few modifications to our main stored procedure the report calls. First and foremost you'll want to change your parameter that is receiving the multi-select values from SSRS to something that can handle a string. You also want to make sure it's long enough to handle the amount of values you'll be passing in. In my case I'm only going to be passing two values, so VARCHAR(50), will be fine for me.

Currently my procedure looks like this (pre multi-select changes)

CREATE PROCEDURE [dbo].[usp_GetGamesMulti]

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem = @p_GameSystem

END --proc 

Next we'll need to change our WHERE statement so that it can compare to multiple values. There are several different ways of doing this, but for simplicity sake, we're just going to change the "=" to an "IN" and then call our UDF to return a table that we can compare against. See the yellow highlighted code below for the changes.

CREATE PROCEDURE [dbo].[usp_GetGamesMulti]

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem IN (SELECT VALUE FROM dbo.Split(@p_GameSystem,','))

END --proc 

NOTE HOWEVER that if you’re comparing against an INT value you'll need to convert your values in the select....if that's the case, our "IN" statement would look something more like this:

IN (SELECT CONVERT(INT, VALUE) FROM dbo.Split(@p_GameSystem,','))

That should do it. Your procedure is now ready to handle the string value that an SSRS multi-select parameter will pass to it. Hopefully this helps. If there's a strong need from people to see more details around how we tie the parameters in SSRS to the proc etc I might do a more in depth post, so send me an email if that's the case. Email address is on my contact page.