Archive

Posts Tagged ‘cascading parameters’

SSRS #74 – 2008 Cascading bug when you have a middle parameter

May 9, 2014 2 comments

Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.

clip_image002

I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).

clip_image004

The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion
  • Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
  • Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
  • The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
  • Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
  • Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.

clip_image006

clip_image008

New Solution That Worked
  • Remove the middle parameter entirely.
  • Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
  • The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
  • Voila. It works.
  • Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.

clip_image010

The nested IIF statement is now coded in the parameter expression.

clip_image012

SSRS #51 – Using Parameters in SSRS – Cascading Parameter (2)

May 28, 2011 10 comments

In the previous blog post, SSRS #50 – Using Parameters in SSRS (1), I listed 7 different ways you can use report parameters. They are also the skills that report developers need to master in order to develop interactive reports.

In this blog, I’ll show you some simple steps to use cascading parameters.

Goal

You want to allow users to select a supervisor from the first dropdown list, then be able to see the employees who report to the selected supervisor in the second dropdown list.

The end result looks like this. Note that the second dropdown list is dimmed initially. Once the selection is made from the first dropdown list, the second dropdown list will be selectable.

image

 

The screen shots are from BIDS 2008, which will be slightly different from BIDS 2005. The concept and the steps are the same though.

Step 1 – Create two datasets: One for supervisors, one for team members

In my example, they are DATASET_SUP and DATASET_UW. I have other datasets in the screen shots, but they are irrelevant for the purpose of this post. 

image

Examine the properties for the DATASET_SUP, you can see that the SQL query just simply retrieves the unique supervisor names and their IDs (within the selected date range).

image

Examine the properties for the DATASET_UW, you can see that the SQL query now has a where clause WHERE SUPCCRID IN (@SUP_CCRID).

image

The at sign @ represents a report parameter. At this point, do not worry about if parameter @SUP_CCRID has been created or not. If not, BIDS will automatically create one for you and you can modify its properties later.

Note1: make sure you have both the ID and the name fields in your queries for both the supervisors and the team members. See my blog post, SSRS #46 – A case against using character field in multi-value parameters, for the reasons why you want to use ID field to pass between queries or stored procedures.

Note 2: the where clause WHERE SUPCCRID IN (@SUP_CCRID) is the trick that makes the cascading happen.

Note 3: as observant as you are, you must have noticed that I didn’t write my where clause as this:

WHERE SUPCCRID = @SUP_CCRID

With WHERE SUPCCRID IN (@SUP_CCRID) , I am allowing multiple values of SUP_CCRID passing to my query, instead of a single value.

Step 2 – Create two parameters: one for supervisors, one for team members

In my example, @SUP_CCRID is the parameter that allows users to select a supervisor from a list, and @CCRID is the parameter that allows users to select team members from a list.

image

Let’s examine their properties.

For parameter @SUP_CCRID, this is what you will need to do on the Available Values tab:

  • 1) you need to get values from a dataset. Select DATASET_SUP from the dropdown.
  • 2) Select the ID field for the Value field.
  • 3) Select the name field for the Label field.

image

For parameter @CCRID, you will do the same as for @SUP_CCRID.

  • 1) you need to get values from a dataset. Select DATASET_UW from the dropdown.
  • 2) Select the ID field for the Value field.
  • 3) Select the name field for the Label field.

image

You are done. Preview your report, you will see the result as shown in the first screen shot in this post.

SSRS #50 – Using Parameters in SSRS (1)

May 28, 2011 1 comment

One of the readers asked me this question in Ask Sherry Li

Question

Dependent parameter in SSRS

How do I create a dependent parameter in SSRS? For example, select a Supervisor in the first drop down and then the Employees for that Supervisor appear in the second drop down.

Master Skills of Manipulating Report Parameters

Before I answer the question, I’d like to draw your attentions to skills report developers need to master.

Building interactive SQL Server Reporting Services reports not only require report developers to master skills of using parameters, but also manipulating report parameters in many different ways. Here are some of the ways we can manipulate the report parameters:

  1. Cascading parameters – example: users select a supervisor from the dropdown, then the employees who report to the selected supervisor will appear in the second dropdown.
  2. Available Values – example: pre-populate a dropdown list with all the supervisors
  3. Default Values – example: the Start Date and End Date parameter will be pre-populated with the Beginning of the Month, and the current date, respectively
  4. Multi-value – example: to see productivity of several employees at the same time, users can select multiple employees from a pre-populated dropdown list
  5. Custom code in SQL stored procedures – Stored procedures allow the ultimate flexibility to use report parameters. One example is to use SQL code to split comma separated list into table values.
  6. Using parameters to dynamically change report item properties – example: use a report view parameter to dynamically show data at aggregation or detail level
  7. Using parameters in Drill Through and Sub reports

      I’ll blog about the Steps to Create Cascading Parameters in the next post.

%d bloggers like this: