Adding Query Parameters to SQL Server 2008 Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

In yesterday’s post “Adding Filter Parameters to Reports”, we explored how to add a filter to the report dataset. This had the advantage of being very fast when you wanted to look through different sets of data. The disadvantage to this method is that it brings back all possible rows for the report at once. Thus it can be very slow and database intensive and unnecessary when the user only wants to look at a subset of the data.

An alternative to Filters are Query Parameters. Query Parameters are applied at the SQL statement level, before the call to the SQL Server (or other database platform) is made. This limits the number of rows brought back from the server, increasing the speed and reducing the memory footprint required on the reporting server.

To begin, let’s create a base report. We’ll do the same as steps 1 to 10 in the “Adding Filter Parameters to Reports” lab, but in case you have not worked through it yet I will repeat them here.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a new dataset to act as a source for the filter parameter

There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.

Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.

clip_image002

Give the new data source a good name. Here we can use Region.

We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).

In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.

SELECT [RegionCountryName]
  FROM [ContosoRetailDW].[Report].[V_Regions]
 ORDER BY [RegionCountryName]

Once you’ve entered the above, click OK.

Step 10. Add the Region parameter

Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.

Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.

Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”

You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.

Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:

clip_image004

Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.

Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.

A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.

In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.

clip_image006

For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.

Step 11. Adding the Region Parameter to the report dataset

Next we’ll need to do two things to add the parameter to the dataset. First, right click on the main reports dataset and go to Dataset Properties.

Since we want to apply this parameter at the database level, we’ll need to add it as a parameter to our SQL query. In the query area of the Dataset Properties window enter:

SELECT [FiscalYear] 
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] IN (@pRegion)

Next, we need to tell the query where the @pRegion parameter comes from. Go to the Parameters area of the Dataset Properties dialog. If Visual Studio / BIDS has not already done so, click add to add the @pRegion. Then in the drop down pick the @Report parameter object. Click OK to save the changes.

clip_image008

Return to the Preview tab and view the report several times, using various items in the list. Everything should update fine.

Except… having the Select All can cause some issues, which are covered in the next post.

About these ads

19 Responses to “Adding Query Parameters to SQL Server 2008 Reporting Services Reports”

  1. SQL Server 2008 Reporting Services and the “Select All” Parameter Issue « Arcane Code Says:

    [...] Adding Query Parameters to SQL Server 2008 Reporting Services Reports [...]

  2. Stan Says:

    Hi, I have a report in SSRS 2008 and followed all the the steps from this post. When I use a single parameter value the report works fine. The strange things start when I check the multi value parameter box. I get a message like this:

    An error occurred during client rendering.
    Exception of type ‘Microsoft.ReportingServices.ReportProcessing.ReportProcessingException’ was thrown. (rsProcessingError)

    No information is available about this particular error on the web. I just stumbled upon this issue and can’t get it resolved.

    Stan

  3. Angie Says:

    Excellent!!! Microsoft should have people like you writing their how-to library. you save my date.

    Angie

  4. shaunmacrae Says:

    You can also incorporate parameters into your query when using the query designer (rather than manually writing sql code) in the applied filters section.

    It works great for strings. You say . It’s not working for Integer parameters though and it is driving me crazy! If anyone has any tips, it would be greatly appreciated.

    • shaunmacrae Says:

      I figured this out after all. If you are ticking the ‘Parameter’ option when entering your query filters in the query designer, you must leave Value blank. I was putting the parameter name in the Value field for the filter and it was complaining because the field I was filtering on was an int.

      • vs Says:

        Can you please show me what you did… i ma also having the same trouble passing integer to query for composite through ODBC.

        having error : ERROR [HY000] Unable to parse query text: unexpected char: ‘@’. On line 10, column 18. [parser-2904201]
        Cause: unexpected char: ‘@’

        here is my Query:
        SELECT
        DISTINCT
        f.fund_id
        ,f.fund_name
        ,f.fund_abbrev
        ,sf.subfund_abbrev
        FROM
        others.fund f
        INNER JOIN others.subfund sf ON f.fund_id = sf.fund_id
        AND f.fund_id= (@pr_fund_id)

        Query parameter Defined:
        @pr_fund_id, param value[@pr_fund_id]

  5. subha Says:

    hi all,

    The article is so gud that i found it correct for my requirnment.
    I followed the steps.
    While adding the parameter, i am nt able to see the parameter properties window.
    can you please guide me on how to get the parameter properties window visible.

    Regards,
    Subha.

  6. subha Says:

    thank you i got it dne..:)

  7. Marvin Says:

    This has really helped me out.

  8. Arti Says:

    very nice… clear and simple to understand

  9. Aaron Says:

    For my parameter called DateStart, I have set the Data Type as “Date/Time”, I setup the Default Value as “specify values”, then created the expression:
    =iif(Parameters!DateRangeType.Value = 2,dateadd(“m”,-4,Datestring),dateadd(“d”,-1*Weekday(Datestring)-6,Datestring))
    My default for the Parameter DateRangeType is “1”, and the correct default shows up. What I really want is that when I change the value of DateRangeType to “2”, I want the default contents of DateStart to be refreshed.

    It does not do this. Do you know of any way that this can work for me?

  10. Jason Says:

    any idea how to get openquery to take parameters, but not declare and set variables, which report wizard doesn’t like?

  11. Betty Jo Enell Says:

    Must the query parameter be listed in the result set i.e. the select list?

  12. bloger7791 Says:

    It’s interesting. Thanks.

  13. beyaz birth control Says:

    Thanks for your marvelous posting! I quite enjoyed reading it, you are
    a great author. I will make sure to bookmark your blog and will eventually come back at some point.
    I want to encourage you to continue your great writing, have a nice evening!

  14. Google Says:

    1- Find a product to promote, something you feel
    passionate about and that you know people will want to buy
    it. Based on their experience, they could know how much is
    required before going into details. And then on March 20, the world’s largest paid private blog network – BMR – announced that its vast network had been almost entirely de-indexed by Google, causing chaos in the internet marketing industries.

  15. Pro Repair auto shop software Says:

    I had an old business plan for this, my brother had an idea
    similar, licensing a major brand like Starbucks which I think is the
    best one and calling it Jumping Java or some other
    cool name. In doing so, you might consider a document found on the
    CTIA’s web site, entitled “Safe – Driving – Talking – Points2″ (created June 6, 2006) that states
    “But for some reason, legislative efforts to prevent driver distractions have been narrowly focused on wireless phone use. So, I kept his information, and phone number in case I decide to start another cool, or hot rather, business.

  16. プーマpuma メンズ 男性用 雑誌でカジュアルシューズ Says:

    There is definately a great deal to find out about this issue.
    I like all the points you’ve made.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 104 other followers

%d bloggers like this: