Displaying Single & Multi-Valued Report Parameter Selections in SSRS

Overview:  A quick review of how to display Single-Value and Multi-Value parameter selections within an SSRS report heading. 

Level:  Basic familiarity with parameters is assumed.

Placing Parameter Values in a Report Subheading

Let’s first chat about what the goal is here. If you have a parameterized report in which the user makes a selection, it’s a best practice to include those parameter values in a subheading (if not elsewhere within the report itself).  The objective is for the report to clearly state what is being displayed, especially if the report consumer is not the original author.

With the parameter displayed in the heading, if a user prints or saves the report for later, the parameter selections made at the time are preserved. This adds value to a report, and aids understandability for others – especially when you have a more complicated report with numerous parameters. For example:

Using the “Value” or “Label” Property

When using the Parameters! collection, you have 4 properties available:  Value, Label, Count and IsMultiValue.

    

Value:  The Value field from when the parameter was set up.  This is the actual value being used to drive the logic for what the report parameter needs to do.  If the report parameter is associated to a dataset parameter (the most common use of parameters), then you may parameterize the source query on something like Customer Key from a data warehouse.  When using an SSAS data source, this might be a unique member such as: [Customer].[Customer].&[35].  The Value field may or may not be suitable to display to a user on the report. 

Label:  The Label field from when the parameter was set up.  This is a user-friendly label shown to the user.  This might be a Customer Name, for example (rather than the Customer Key used for the Value).  Most often you want to correspond the Label to what’s being displayed in a report heading.

Count:  The count of the number of values selected.  This is only useful for parameters which have been set to “allow multiple values.”

IsMultiValue: A True/False indicator that multiple values are permitted.  Useful for conditional logic.

Displaying a Single Parameter Value

The expression in the heading shown above is using the Parameters! collection:

(Note that either Value or Label could be used in the expression.  I used Value just to keep things simple.)

This syntax only works when you are displaying a single parameter value. Usage of a single parameter value is controlled by two things:

1. Usage of an = within the source query.

2. Ensuring the “Allow multiple values” checkbox within the Parameter properties is left unchecked. This tells SSRS it’s a single-value parameter.

Displaying a single parameter value is pretty straightforward. Let’s see what happens when we change to a multi-value…

Displaying a Multi-Valued Parameter

Following is an example of what we’d like to see if a multiple values are allowed for a parameter:

The expression in the heading shown above is using the Parameters! collection. However, usage of the JOIN() function is required when the parameter allows multiple values.

(Note that either Value or Label could be used in the expression. I used Value just to keep things simple.)

This syntax only works when you are displaying a multi-valued parameter. This is controlled by two things:

1. Usage of an IN() within the source query (rather than the = as used with a single-valued parameter) within the source query.

2. Ensuring the “Allow multiple values” checkbox within the Parameter properties is checked.

#Error in Textbox if JOIN() Function Not Used for a Multi-Valued Parameter

If you have #1 and #2 above set correctly for a multi-valued parameter, but you don’t use the JOIN() function within the expression for the heading, you’ll receive a warning when the report is executed and a “#Error” is displayed by the textbox.

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox8.Paragraphs[0].TextRuns[0]’ contains an error: Operator '&' is not defined for string "Year Selected: " and type 'Object()'. C:\Users\melissac\Documents\Visual Studio Projects\Blog_SSRS_Project\Blog_SSRS_Project\ParameterValueDisplay_MultiValue.rdl 0 0

Adding the JOIN() function fixes the above #Error from being rendered: 

       ="Year(s) Selected: " & JOIN(Parameters!Year.Value, ", ")

Dealing With a Large Number of Parameter Values

If the number of possible parameter values is short, like the Year example shown above, then you’re done.  But what if you have a super long list of possible values and the user selected an awful lot of them?  That could make for a very unwieldy list of values in your report subheading.  Depending on your requirements, a possible workaround might be:

       ="Year(s) Selected:  " & IIF(Parameters!Year.Count > 10, “Multiple Years”, JOIN(Parameters!Year.Value, ", "))

In this example, the 10 is an arbitrary number I picked.  In this situation, a better solution is probably going to be getting the data value into the report rows or columns (rather than the heading).  Of course, that depends upon your requirements & the aggregation level of the report.

Displaying the First Multi-Valued Parameter Selection

If you have a need to display just the first parameter value, the syntax is:

       =Parameters!Year.Value(0)

Displaying the Last Multi-Valued Parameter Selection

If you have a need to display just the last parameter value, the syntax is:

        =Parameters!Year.Value(Parameters!Year.Count-1)

Finding More Information

MSDN – Expression Examples

Technet – Using Parameters Collection References

SQLChick Blog – Relating Parameters & Filters in SQL Server Reporting Services

Workaround for “Could not update a list of fields for the query” error in SSRS

Overview:  A quick workaround for an intermittent SQL Server Reporting Services error.

Reporting Services Error

The error I received is:

Could not update a list of fields for the query.  Verify that you can connect to the data source and that your query syntax is correct.

I received this error sometimes after updating the dataset (with the preview of the query running fine), at the point when I clicked OK to save the dataset changes.  In my situation the data source was an Analysis Services cube (SQL Server 2008 R2).

    

Workaround

Since I started using this method, I haven’t seen the error.

1.  Go to Dataset Properties.  (Specifically…do not  jump directly to the Query.)

    

2.  Select the Query Designer button.  (Yes, it’s an extra click, I know.)

    

3.  Update your query.  Click OK within the Query Designer.

4.  Click the Refresh Fields button, and then OK.

    

That’s it.  I haven’t had any trouble since I started accessing and saving the query “the long way” and now it’s just habit.

Relating Parameters & Filters in SQL Server Reporting Services

Overview:  A quick review of the optional relationship between parameters and filters for a SQL Server Reporting Services report.  SQL Server 2008 R2 is used in all screenshots.

Level:  Basic familiarity with parameters & filters is assumed.

Parameters in Reporting Services

When talking with people, sometimes I notice they refer to parameters synonymously with filters for a report.  This is often true, but parameters can be so much more!  Filters are quite often based on a user choice, but they don’t have to be.  The way I think of the optional relationship:

• A parameter can be bound to a filter, but it doesn’t have to be.
• A filter can be bound to a parameter, but it doesn’t have to be.

Other (non-filtering) uses for a parameter include things like:

• Hide/show report sections
• Provide conditional formatting
• Control sorting and grouping
• Choices for actions
• etc...

Two Types of Parameters

Using SSRS lingo, there are two types of parameters:

• Dataset Parameter. The purpose of a Dataset Parameter (aka Query Parameter) is to perform a filter within the source query.

      

• Report Parameter. The Report Parameter properties contain all of the settings related to a parameter (available values, default values, etc).  A Report Parameter might or might not be associated to a Dataset Parameter.  Unless it's hidden, the purpose of a Report Parameter is to interact with the user:

      

The vast majority of the time, a Report Parameter is associated to a Dataset Parameter – i.e., its purpose is to filter the data being displayed based on the user’s selection.  However, if a Report Parameter is not bound to a Dataset Parameter, the Report Parameter could be used for all kinds of cool & creative things related to layout, formatting, or actions.

Two Types of Filters

The two types of filters in SSRS are:

• Dataset Filter.  This is the most common:  filtering within the source query.  When you implement a filter within the dataset, less data is sent over the network from the source database server to the Report Server - usually a good thing.
• Report Filter.  This includes filtering after the source query has come back – on a data region (like the Tablix), or a data grouping.  When you implement a filter within the report, when the report is re-executed again with different parameter choices, the Report Server uses cached data rather than returning to the database server.  This is a viable option if you intentionally want to return all possible data from the database server in the initial query.  Keep in mind this could be far less performant if the volume of data is high & the report won’t be re-executed numerous times.

Generally speaking, using a Dataset Filter is the most efficient method.

Just to confuse the matter a wee bit - in talking with a business analyst or another non-BI-developer regarding requirements, I use the term Report Filter – when in reality I’m actually implementing a Dataset Filter.  With non-developers, I try to use the conceptual terminology rather than the SSRS lingo.

A Report Parameter can be associated to a Dataset Filter, or a Report Filter, as shown in the next 2 sections.

Associating a Report Parameter to a Dataset Filter

When you add a query variable to parameterize a dataset (using the ampersand @ symbol), Reporting Services does you a favor and creates a Report Parameter automatically.  This is the behavior most SSRS developers are familiar with.

      

      

After SSRS makes the association between the Dataset Filter and Report Parameter for you, if you check the Dataset Properties you’ll see the parameter properties:

      

Don’t forget!  It is up to you to go visit the Report Parameter properties to validate them, and finish setting them up (for example, default values).  Also, if you delete a parameter from the dataset, SSRS isn’t going to and delete it from the Parameters for you – you’ll need to do that manually.

Associating a Report Parameter to a Report Filter

Contrary to the previous example, this is handled a bit more manually.  There’s 2 steps:

1.  Associate the Report Filter (such as a Group property shown below) to a Report Parameter.  Following is an example using @Country:

      

2.  Set up the Report Parameter properties manually.  SSRS doesn’t set it up dynamically like it does with a Dataset Filter (which is fine, since this is much less commonly used).

      

Finding More Information

Technet - Parameters (Report Builder 3.0 and SSRS)

MSDN – Tutorial:  Adding Parameters to a Report (SSRS)

Pros & Cons of Using the OLE DB Provider for SSAS 10.0 when Reporting in SSRS

Overview:  This entry discusses some considerations for using the OLE DB Provider for SSAS 10.0 connection.  The pros & cons mentioned are relevant to SQL Server Reporting Services, with SQL Server Analysis Services as a data source.  SQL Server 2008 R2 is used for all screen shots.

Introduction

Typically when using SQL Server Reporting Services (SSRS) to retrieve data from an Analysis Services (SSAS) database, the first choice is to use the default data processing extension: 

 

However, there are times it’s useful to use the OLE DB Provider instead:

Versions of the OLE DB Provider:

• Version 10.0:  SQL Server 2008
• Version 9.0:  SQL Server 2005
• Version 8.0:  SQL Server 2000

Pros & Cons of the OLE DB Provider for SSAS 10.0

So, why would you want to use OLE DB when reporting within SQL Server Reporting Services?  To get some additional flexibility not afforded by standard SSAS connectivity.

Pros:

1. Returns parent/child results more flattened, in separate fields. 
2. Intrinsic Member Properties (aka Dimension Properties) can be returned in the dataset as “real” fields.
3. Can use a cellset in the query.
4. Allows you to place measures on rows rather than just columns. 
5. Better performance in some situations. 

Cons:

1. Does not accept query parameters.  (The workaround for this is to combine expressions with strings to build the query, which results in far more complex datasets which are more difficult to maintain.)
2. No automatic mapping of Extended Field Properties (aka Cell Properties).  (The workaround for this is to adjust the connection string.)
3. Default field names are much longer. 
4. No query designer available.  (The workaround for this is to use the query expression pane.)
5. No support for server aggregates.

The following sections explore these Pros & Cons in greater detail.  Let’s take a look at the Pros first.

Pro 1:  Returning Parent/Child Results More Flattened

Following is a very simple query, using the parent/child hierarchy in the Employees dimension:

 

The above screen shot is using the standard SSAS connection.  Using the MDX generated by the GUI (shown on the right above), I will create a second dataset using an OLE DB connection instead.  In the next screen shot below, see how Level 2 is a different field than Level 3, which is different from Level 4, etc?

The MDX is actually the same in both datasets shown above; it’s the difference in the connection that drives the output.

The “flattening” of the parent/child levels can provide additional flexibility in report layout depending on what you need to do with your report.  You definitely need to be careful with this though – summing the measure accurately is trickier after it’s been flattened out.  It may be better to leave this as is, and use SSRS properties to report parent/child levels.

Pro 2:  Intrinsic Member Properties Can Be Returned in Dataset as “Real” Fields

The same query used in Pro 1 above illustrates this topic nicely.  In the following screen shot we have the MDX generated by SSRS, which asks for Dimension Properties (i.e., intrinsic member properties):

With the standard connection, member properties are not available in the drag-and-drop MDX Designer interface in SSRS, but they are accessible via expressions. Instead of the typical “Value” in an expression, the syntax would be:

• Fields!FieldName(“PropertyName”) or
• Fields!FieldName.PropertyName

However, if we use an OLE DB connection, we get the member properties of MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, and LEVEL_NUMBER are returned as “real” fields:

This isn’t a major advantage of the OLE DB connection type, but is kind of nice.  It can be a bit more clear if an SSRS novice is trying to understand a report which has already been developed. 

Pro 3:  Using a Cellset in the Query

This one is a bit misleading – we do still need a rowset for Reporting Services.  However, we can get away with a bit more in the MDX source query.

With the standard connection, a multidimensional cellset type of query (shown below) is not permitted.

If you attempt to use a cellset (like shown in the above query) with the standard connection, you will get this error:

The query cannot be prepared: The query must have at least one axis.  The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.

However, you can execute the same query with the SSAS OLE DB connection type.  The results are still flattened into a rowset for SSRS to process - see how the Measure & the Product Line are combined into one field returned by the dataset:

The flexibility of using a cellset is a particularly nice reason to use the OLE DB connection. 

Pro 4:  Place Measures on Rows

Very similar to Pro 3 mentioned above, the OLE DB connection permits you to place measures on the rows, whereas the standard connection requires measures to be placed only in columns.

With the standard connection, a query with measures on the rows (shown below) is not permitted.

If you attempt to place measures on the rows with the standard connection, you will get this error:

The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.

However, you can execute the query with the SSAS OLE DB connection type.  The results are still flattened into a rowset for SSRS to process - see how the “Measures Level” is combined by the dataset:

The flexibility to place measures on either columns or rows is another nice reason to use the OLE DB connection.

Pro 5:  Better Performance

I haven’t done any performance testing, so I will defer to Chris Webb’s discussion, as it relates to populating a drop-down list for a parameter.

Con 1:  OLE DB Connection Does Not Accept Query Parameters

This is by far the biggest negative to using the OLE DB connectivity.  The workaround to using parameters is to build the query with a combination of expressions & strings.  For example:

Note in the above query the equals sign ( = ) is used at the beginning, so the entire thing is evaluated as an expression.  The only thing not within double quotes are the expressions which SSRS evaluates to bring back the parameter value.

The vbcrlf’s are used just for readability – you could avoid a lot of the plus signs ( + ) and double quotes if more text was on each line.  However, since the SSRS expression box doesn’t wrap terribly well, I tend to make my rows pretty narrow.  Usage of the vbcrlf is discussed in this blog entry.  A side benefit of formatting your MDX for readability with the vbcrlf’s is the MDX comes out quite readable within SQL Server Profiler.

Con 2:  No Automatic Mapping of Extended Field Properties

Cell Properties, also known as Extended Field Properties, don’t come through the OLE DB connection type automatically.  However, there is a workaround for this:  you can modify the data source connection string.

Let’s start with the following query.  Note how several Cell Properties are requested, such as BACK_COLOR, FORE_COLOR, FORMAT_STRING, etc.

Using the OLE DB connection with its default settings, these Cell Properties are not returned in the following dataset.

The standard connection doesn’t return the Extended Properties as “real” fields, and they aren’t available in the drag-and-drop MDX Designer interface in SSRS, but they are accessible via expressions. Instead of the typical “Value” in an expression, the syntax would be:

• Fields!FieldName(“PropertyName”) or
• Fields!FieldName.PropertyName

However, if we modify the connection string within our OLE DB connection, we can get them to return as “real” fields.

Provider=MSOLAP.4;Data Source=Localhost;Initial Catalog="Adventure Works DW 2008R2";Extended Properties="ReturnCellProperties=true"

After modifying the connection string, we get them returned as “real” fields:

Con 3:  Default Field Names are Much Longer

This is not a big issue, but the field names generated by SSRS are significantly longer when using the OLE DB connection type.  You could edit them if you prefer, but I don’t usually bother.

Con 4:  No Query Designer Available

This is a big one in terms of usability:

• No Query Designer is available
• Cannot execute this query & preview results within the SSAS dataset

You need to use the expression pane for the query, as shown below.  The Query Designer window (even if you click MDX mode instead of GUI mode) is not available. 

Remember…if you click the “fx” button & paste in an mdx query (from SSMS or another tool), be sure to remove the equals sign ( = ) in there by default.  (Or, alternatively, wrap the entire thing in double quotes if the = sign remains in front.)  Normally I don’t use the = sign unless parameters are involved, which was discussed up in the Con 1 section above.

After you paste a query into the expression pane, if you click on the Fields pane (on the left), you’ll see the fields get added.  If you manually edit the query & add or change fields, the Fields pane will not detect those changes automatically. 

Con 5:  No Support for Server Aggregates

This refers to the ability to use an expression such as =Aggregate(Fields!FieldName.Value) within the OLE DB connection type.  The results will be blank because, according to Robert Bruckner, the OLE DB provider does not implement a RS data extension interface (IDataReaderExtension).   You can use a Sum() or Count(), etc.

Summary

Personally, I use the standard SSAS connectivity unless I have a reason to switch to the OLE DB connection.  The standard connectivity is newer, and is recommended by Microsoft.  However, if you need some flexibility, using the OLE DB connectivity a good option to consider.

Finding More Information

MSDN:  Data Sources Supported by Reporting Services (SSRS)

Chris Webb’s Blog:  Tuning SSRS-Generated MDX Parameter Queries

Teo Lachev’s Blog:  How to Get Extended Properties with SSAS OLE DB Provider

Greg Galloway’s Blog:  Retrieving Cell Properties    <—Really good stuff here!

MSDN:  Analysis Services OLE DB Provider (Analysis Services – Multidimensional Data)

Analysis Services and PowerPivot Team Blog:  Impact of the SQL Server OLE DB Deprecation on Analysis Services

MSDN:  Intrinsic Member Properties

MSDN:  Using Extended Field Properties for an Analysis Services Dataset

Repeating Column Headers on Every Page in SSRS Doesn’t Work! …Or Does It?

Overview: A few tips for getting the column headings to display at the top of each page of a SQL Server Reporting Services (SSRS) report.  SQL Server 2008 R2 was used for all examples.

Using the Advanced Properties

This entry is to expand on my previous blog about Repeating Column Headings which discusses the use of Advanced Tablix Member Properties to repeat column headings on each page.  If you haven’t used the “RepeatOnNewPage” property too much, it can seem like sometimes it works & sometimes it doesn’t.  It can definitely be a bit frustrating.  

Before we go into a few examples, I want to mention the Tablix Properties.  These are great when working with a Matrix; however, they have no affect with a regular Table data region. 

    

Rather than using the above Tablix Properties, since we have a Table we’re going to be using the Advanced Tablix Member Properties.  The Advanced Tablix Member Properties become available when you click the little arrow on the right side of the grouping pane.

    

    

Case 1:  No Row Groups – Column Headings in a Header Row – Works!

This report has one Details group, but no parent groups.  Notice the column headings are located in a header row of the table.  The RepeatOnNewPage property works perfectly in this type of layout (i.e., no row groups).

    

Case 2:  One Row Group – Column Headings in a Header Row – Repeating Does NOT Work

In this layout I moved the Product to be a parent group of the Details.  I left the column headings like they were in the previous example:  in a header row of the table.  Although I set the same advanced tablix member properties, the column headings do NOT repeat on the following page.

Why, you ask?  Read on to Case 3.

    

Case 3:  One Row Group – Column Headings Within the Group – Works…Maybe

This layout moves the column headings to be within the row grouping (see how they are within the bracketed rows, rather than one row on top (like they were in Case 2). 

    

This technique works, but since the headings repeat within every single group, it might or might not work for your real life situation.

    

Case 4:  Using a Fake Group – Row Headings Within the Group – Works Great!

This last layout improves on Case 3 by creating a fake group.  This fake group is so we can keep the Column Headings within a row group, yet not have them repeat for every product that changes.

    

The group is set up like this; it really doesn’t group on anything:

    

The column headings now only appear once, at the top of every page.  Perfect!

    

Finding More Information

There’s a key concept discussed at this MSDN article:  How to Display Row and Column Headers on Multiple Pages (Reporting Services).  It's this comment that inspired me to use the "fake group" approach discussed in Case 4.

"For a table, the row that contains column names is controlled by the tablix member in a row group. Set tablix member properties in the Properties pane."

Updating the SSRS “Name” Property to Control CSV Column Headings

Overview:  A quick tip about generating proper column names when exporting to a CSV file from SQL Server Reporting Services.

When Is the SSRS Textbox Name Relevant?

When SSRS creates the Name property for a textbox, SSRS tries to be helpful & use the field name.  This could be good, or it could be rather cryptic.  It’s also common to have many textboxes named something like “Textbox1” and “Textbox2.”  Although I like my reports to be tidy, I’ve not been terribly disciplined in the past about cleaning up the textbox names, except in these situations:

• When defining Visibility or Interactive Sorting.  Ex:  Detail rows are set to Show when textbox named “Division” is clicked.  I blogged about Visibility Settings here.
• Usage of the ReportItems collection.  Ex: Referring to the value in textbox “Quota_For_Month” in an expression within another textbox:  =Sum(ReportItems("Quota_For_Month").Value).  You can check out few examples of using the ReportItems collection here.

As of today, I have a new situation to add to the list:

• When end users export the report to CSV format.

CSV Format Uses “Name” Property as Column Headers

By default, exporting to a CSV (comma separated values) format will utilize the textbox names for each column heading.  Rather than seeing useless column headings like this....

    

....We’d rather see well-named column headings such as this:

    

Updating the Textbox Name Property

The “Name” property can be updated within the Properties Pane:

    

Or, it can be updated within the Property Pages:

    

Taking just a bit of time to update each Name property provides a really nice benefit for end users, should they use CSV export functionality. The end user says to me: “I have to update the column headings each time I export.” Ouch! That hurts! So glad he mentioned it so I could vastly improve the end user experience with such a simple change.