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:
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.
Drill 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:
So 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.
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:
So, 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:
I hope this helps if you run into this SSRS error.
For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.
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.
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.
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:
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:
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
Step 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
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:
I selected Europe and then clicked on view report:
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
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!
SSRS chart didn’t show all axis labels. Here’s an example.
Note: it does NOT show all country names:
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
3. Preview your report to see if you see ALL axis labels now:
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.
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.
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.
