SQL Server Reporting Services: How to display Multi Select parameter selected on the report?

Standard

Here’s the problem statement:

You’ve a multi select parameter on your SSRS report and you need a way to display that on that the SSRS report. (Note: It’s a good practice because when the report is printed out the parameter values that were selected gets displayed and the consumer know that’s right off the bat.) – So how do you do that?

You added an expression on your report and double clicked on the parameter. If you do so, something like this will show up on your expression values: =Parameters!ProductCategory.Value(0) and after you changed Value to Label “=Parameters!ProductCategory.Label(0)” – here’s what you get on the report:

Multi Select parameter SSRS display

Solution:

I learned this trick via this stackoverflow thread.

Go back to expression and set the value of the expression to:

=Join(Parameters!ProductCategory.Label,",")

And after I did that, it fixed the problem!

Multi Select parameter SSRS display Join Value Label fixed

I hope this gives a good starting point!

Paras Doshi

Advertisements

SQL Server Reporting Services: What are Drill Down & Drill Through reports?

Standard

Analyzing data at summary level is great! it’s gives business users information that they need at summary level to make informed business decisions. But often, they also need to look at details. To satisfy this business requirement, there are two types of reports that you can create in SSRS to show detail level information

1. Drill Down Report.

2. Drill Through report.

So what is the difference in their layout?

Drill Down report:

Note the presence of Toggle Items. + for expand. and – for collapse. As you can see, this makes it easy to go a level below and see the details.

SQL Server Reporting services Drill Down reportDrill Through report:

Note the presence of a formatted text. It’s shown like a hyper linked text. For this report, To see Product level detail of Bikes Category, click on Sales Amount for Bikes category & it would take you a drill through report that shows the details:

SQL Server Drill Through Reporting ServicesSo when to choose Drill Down report vs Drill Through report?

They have different feature & serves different purposes. But there are two things that help me decide in majority of the cases:

1. Is the performance of Drill Down report acceptable (especially if data is surfaced through stored-procedures/queries hitting the data source without a semantic layer in between)?

Yes? Great. If not, then try drill through reports.

2. Are there too many (30-40+) data items show if a user drill downs? Yes? Try drill through.

Related resources:
Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS)

Author
Paras Doshi

SSRS Error: “The value expression for the … contains an error. [BC30451] ‘code’ is not declared”

Standard

I was working on some enhancements on an existing SQL Server Reporting Services (SSRS) report & so before started adding enhancements to the report, I made a copy of the existing report. And after that, I tried running the report as-is to make sure everything was functioning as it should. But I got an error:

SSRS Code not declaredSo, when you get an error like this, look for missing custom code in the SSRS report.

If you look at the expression which is throwing the error, you’ll see something like code.<custom function> in the expression.

To solve the error, you’ll have to define the custom function for your report. In my case, I had to copy the code from the existing report to the new version of report that I was working on.

To do that, follow the steps:

Design View > Right click on the surface outside the border of the report > Report Properties > Code:

SSRS Custom Code reporting services

I hope this helps if you run into this SSRS error.

SSRS in SharePoint mode: Data alert & Subscribe are grayed out or disabled.

Standard

Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.

Problem:

For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.

data alert subscribe ssrs sharepoint 2012

What do you have to do?

You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.

It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.

If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

Real-world story:
what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.

SQL server reporting services: Expression to show current month name & year

Standard

Here’s a SSRS expressions code that I’ve using lately to show current month name & year in SSRS reports:


="REPORT NAME"&" - "&MonthName(Month(Today()))&"'"&Year(Today())

This is a nice little code that you can add to your expressions which will show current name & year. Small little things that you can do to make your business users happy! I hope this helps someone out there.

SSRS Email subscription: “SSRS Subscriptions cannot be created because the credentials used to run the report are not stored…”

Standard

I recently ran into error message inside SSRS report manager: “SSRS Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid”.

My Setup:

Mode: Native

Authentication: Windows

So I changed the Report’s data sources properties to store the credential:

SSRS store credentials in the report server

Official resource: http://msdn.microsoft.com/en-us/library/ms159736.aspx

After doing this, I was able to set up email subscriptions.

How to Parametrize a SSRS report where data source is Analysis services cube?

Standard

In this blog post, we’ll see steps to Parametrize a SQL Server Reporting Services report where data source is Analysis services (SSAS) cube:

Step 1: Let’s say you have reached to a point with your SSRS report where you’ve configured your data source, data sets and data fields that you want to use for your report. For the purpose of this blog post, I’ll be starting with a SSRS report that shows Sales VS country names:

SQL Server reporting services 1

Step 2: Now, let’s say the requirement are such that you want to parametrize the report by a data field in the Analysis services cube: continents

Step 3: Switch to Design View. Now navigate to query designer: Select your Data-set > Right Click > DataSet Properties > Query > Query Designer

ssrs query designer SQLStep 4: Drag the field to the filter area. For the purpose of this blog post, I am going to select Continent Name and add it to Filter area.

To add a field to filter area, there are two options:

#1: Select the field > Right click > Add to Filter

#2: Select the field > use your mouse to drag it to filter area

Step 5: Once you’ve added your desired field to the filter area, we’ll have to add it as parameter.

Now chances are that you are not seeing the parameter check box for this field because the dialog box is minimized. You can either maximize the dialog box or scroll to the right side of the filter area.

Once you see it, check it > click ok

parameter query sql server reporting

Step 6: Once you’re back on Design View. Try “preview” report. you should be able to see the option to select parameter value before the report gets populated with data:

parameter in sql server reporting ssas

I selected Europe and then clicked on view report:

parameter in sql server reporting ssas 2

Step 7: One last thing, Let me also point out how you can change the properties of the parameters.

Go Back to design view > from the report data pane > Expand parameters folders > select the parameter > Parameter Properties

parameter properties report data ssrs ssas

I’ll leave you with exploring what you can do with parameter properties! And with that I conclude this blog post, Your comments are very welcome!

This was a beginner’s level post, I’ll encourage you to follow up by watching three videos here: http://sqlserverbiblog.wordpress.com/2013/12/03/reporting-services-mdx-queries-video-tutorials/

SQL Server Reporting Services: Why am I not seeing every axis label in a chart?

Standard

Problem:

SSRS chart didn’t show all axis labels. Here’s an example.

Note: it does NOT show all country names:

axis labels sql server reporting services

Solution:

So what do you do if you want to show all axis labels in the report and do not want to skip the axis labels? Here are the steps:

1. Go to the Chart Axis properties

2. Under Label, change the value of Label Interval from Auto to 1

ssrs chart aix label properties

3. Preview your report to see if you see ALL axis labels now:

axis label ssrs issue solved

Conclusion:

The above chart is NOT perfect. There are other things that can be done but the goal of the blog post has been achieved! We have changed the axis label property so that all axis labels now show up on charts.

 

SQL Server Reporting Services: Month Names sorting Issue

Standard

Problem:

I was developing a SQL Server Reporting Services report from data that was coming from Analysis Services cube. And when I created the report having Month Names, it was not sorting it correctly.

It was:

April, August, February, Jan….

But It should have been:

Jan, Feb, Mar…

because I had correct sorting settings in the cube. So what was wrong? Or what can we fix?

Solution:

Turns out, the solution was pretty simple. Here are the steps:

1) Select the chart.

2) Select the Month Name category group

3) Go to Properties

4) Go to Sorting section

And delete the sorting property that sorts the Month Names by A to Z. seems this overrides over sorting properties that’s in the cube.

sorting sql server reporting services month names

5) After deleting the sorting property in SSRS fixs the issue.

Please preview the report to see if the issue has been resolved for you.