Browsed by
Category: SSRS

Using SSAS dimension data with different labels

Using SSAS dimension data with different labels

In relation to my article about using date pickers to filter SSRS cube data, I have been asked a few times how it would be possible to use one set of parameters to select the date range that points to dimension data from different cubes.
For example, I have a report that pulls data from two different cubes. In the one cube, the main time dimension is called “Date”, and in the other cube there is a time dimension that is calle “Value Date”.

Basically there are two ways to accomplish this. The first one is exactly how I described in my previous post about this topic. For each date dimension you would have to create two extra parameters and use the expression as I mentioned. In the example above, you would have to create four parameters:

Parameter: DateFrom
Default value: =”[Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: DateTo
Default value: =”[Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateFrom
Default value: =”[Value Date].[Date].&[” & Format(Parameters!FromDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

Parameter: ValueDateTo
Default value: =”[Value Date].[Date].&[” & Format(Parameters!ToDatePicker.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second way (I don’t particularly see on beneficial way) is to use the Replace-function. As you can see above, the difference between the “DateFrom” parameter and the “ValueDateFrom” parameter is only in the first couple of characters. As a default value for the “ValueDateFrom” parameter, I also could have put this:

=Replace(Parameters!DateFrom.Value, “[Date].[Date]”, “[Value Date].[Date]”)

One little side note: When you add the date dimensions from different cubes in your report and let SSRS just create the parameters for you, you don’t have to create those parameters additionally. Make sure though that in the above example, there would have been two datasets for the available values for the ValueDateFrom and ValueDateTo parameters. Since we don’t see these parameters in the report anyway, you can set the available values to None and delete the datasets.

SSRS: Using date pickers to filter SSAS cube data

SSRS: Using date pickers to filter SSAS cube data

When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this. This post does not describe how you should format the MDX to make this work, I assume that you have your dataset ready to go to use in your report.

The first step is to create two dimensions for the date range. The types of these parameters is “Date/Time”. There are no specific other settings for these parameters, as these are the date pickers that the end user can use to select a date range. So when you start the report, you will only have these parameters:

 

The next thing to do is create parameters that contain the formatted values that the MDX query can read. To accomplish this, create two more parameters called “FromDate” and “ToDate”, of type “Text”, and set the visibility on “Internal” (this causes the parameter to be hidden, but the values will change when the selection of other parameters change). On the “Available Values” tab, select “None”. On the “Default Values”, the following expression should be used:

=”[Order Date].[Date].&[” & Format(Parameters!FromDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second parameter should contain this expression as the default value:

=”[Order Date].[Date].&[” & Format(Parameters!ToDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

When you run the report now, you will see that the selected dates will be applied to your dataset. If not, you can always set the last two parameters to “Visible” and run the report. That way you will see how these parameters are formatted. Most of the errors that I got is that the descriptions of the cube dimensions were not completely correct.

Open SSRS action link in a new window

Open SSRS action link in a new window

From an SSRS report item, you can choose the “Action” in the properties to link basically to anything. The most popular ones to link to, are another report or an external URL. Personally I’ve used the URL actions the most, but somehow it’s not possible to select if you want the page to open in a new window or in the same window. By default, Reporting Services wants you to view the linked page in the same window.


But what if you want the linked report to open a new window? There is a little trick to this… the only thing you have to do is add a little line of javascript. When you choose the URL, make sure you go into the expression editor, and type the following line:


=”javascript:void(window.open(‘”+ Fields!ReferURL.Value + “‘,’_blank’))”


In this case, I have a field called “ReferURL” which already contains the full URL to where the report item needs to link to. Of course you can enter your own URL which would look something like this:


=”javascript:void(window.open(‘http://www.google.com’,’_blank’))”

Dynamic grouping in SSRS reports – Part 1

Dynamic grouping in SSRS reports – Part 1

It happens to me lots of times that I’ve created an SSRS report, grouped on a certain dimension, and while the end user is happy with the result, I get the question back “Can I also group this table on this-and-this dimension as well?“. Well, sure you can! You can create a second tablix or even a second report that retrieves exactly the same data but then you group the table on another dimension. I was at that stage where I was thinking that there should be a better way to do that instead of copying and pasting the report and change some settings. And there is, so I’ll share the way I’ve solved this issue and made one report very dynamic so the user of the report has a lot more flexibility than just filtering some data. I’m well impressed with the possibilities in this area, so I’ll call this “Part 1” and devote another blog post to go deeper into this subject.

To start off with, let me explain the business situation of the case. This case is dealing with support calls that have been registered, and the business controller wants to have a report in Reporting Services on the amount of cases were created and handled. He wanted to have flexibility on grouping the data, for example to show by division, by manager, by employee who handled it, etc.

So how did I solve this? In the Business Intelligence Development Studio, you create a report as you would normally do, and you create a dataset with the data you want to display. Make sure you include all the fields in here for which you want to include the grouping possibility for. For example, I chose the fields division, cost center, manager, employee and a sum on the support calls. As a side note, it doesn’t matter here if you retrieve your data from a database directly or from an Analysis Services cube.

Here is what the dataset looks like:

Next step is to create a tablix. It doesn’t matter if you create a table or matrix here, that depends if you want to add column groups as well. This tutorial only displays the functionality with row groups. For now, select any of the fields in the dataset to at least create one group, it would be easier in my example if I already create two groups. In the end, when I’ve created my table, it looks like this:

You can run your report to see if you get the results that you want. Next step is to add the selection for the end user so that they can decide what fields to group on. To do this, create two new variables (or one, if you only use one group). I’ve called these parameters RowVariable1 and RowVariable2. Some important settings for the parameters are the following:

  • Data type: text
  • Multiple select: No
  • Available values: Add the values that you want the end users to select. The label doesn’t matter what you enter, the value must be the same name as the field name in your report data.
  • Default value: Whichever value you think the end user wants.
For example, the tab Available Values looks like this:
Go back to the report and in the bottom of the screen, select the first row group and choose “Group properties”. Click on the expression icon at “Group on” and enter the following value:
=Fields(Parameters!RowVariable1.Value).Value
Notice that the syntax of the expression is a little different of what you usually use for field references, but this is just another way of building the expression and the other way of writing does not work in this case.
Click OK and if you have a second group, do the same for that group but then change the RowVariable1 into RowVariable2.
When you’re back at the report, right-click in the textbox where the label for the group is displayed (in my case above the textbox where it says “[Division]”). Enter the same expression here, because you want to make sure that the correct data will be displayed. Again, if you’re using a second group, do the same for that textbox that contains the label of the second group (in my case where it says “[Cost Center]”).
Finally, as you can see in my illustration of the table above, in the grey area I’ve added labels for the columns to make it clear to the end user what they’re looking at. But when other fields are selected to group on, these labels have to change as well. To change this, simply right-click on the textbox and choose Expression, and add the following expression:
=Parameters!RowVariable1.Label
And as you might have expected by now, do the same in the second group but then for RowVariable2.
Allright, when you’ve followed all these steps, your table should look like the following illustration:
Now run the report. You’ll see that you’ll have two parameters to choose from. Select a field value from these parameters (which are actually just text value entered in the parameter properties) and view your report.
With this method, it’s not just possible to dynamically fill the row groups, the same could be done with column groups. That and other nifty stuff I’ll cover in later parts!
Description for an SSRS report in BIDS?

Description for an SSRS report in BIDS?

Usually when I deploy a report from the Business Intelligence Development Studio to the Report Manager, I add a short description in the Report Manager what the report is all about or where I can track back the definitions of the report.

It would be really helpful if I could already set this description in Visual Studio when I design the report, but unfortunately this option is not there. It looks like a minor thing that would be nice to have, but this doesn’t affect the general process of developing beautiful reports.
Just in case you were searching everywhere where the report description property was…
Use of the field ListLevel in SSRS

Use of the field ListLevel in SSRS

In SQL Server Reporting Services, there is the property “ListLevel” that you can use in your tables to add some padding to the different groups that you have created in your tablix. It’s nice to have that feature available, but I use other methods because of these reasons:

  • You have no control over how much space is added when the text is indented.
  • If you have several list levels and also multiple levels in your tablix that you can expand and collapse, the plus and minus icon will stay in the far left of the tablix.
Because of these reasons, I use the padding option of every cell. It lets me control the padding exactly how much I want to have the text indented.
Avoid SSRS to change your customized MDX datasets

Avoid SSRS to change your customized MDX datasets

I’ve experienced many times that when I changed the main dataset in SSRS (that reads from an SSAS cube), that the datasets of the parameters are changed again automatically. This has usually been a very annoying thing because I change the MDX in the parameter datasets very frequently.
To avoid this automatic change, you can add SuppressAutoUpdate=true in the code of your report that suppresses the change of this MDX. Right click the report and choose View Code. Find the dataset, and in the relevant node pertaining to your dataset, modify it. Then Save.

<Query>

<DataSourceName>DataSource1DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>truerd:SuppressAutoUpdate>

<rd:Hidden>falserd:Hidden>

Query>

Source:
Use colors in your reports consistently

Use colors in your reports consistently

I’m in the process of creating a financial report that in the meantime has blown out of its own proportions. Some changes that seems like easy changes have to be changed in so many places that it’s not so easy anymore.

One of the things was to set colors for percentage figures that represent things like turnover compared to budget, costs compared to last year, and so on. If it concerns income, everything under 90% should be colored red, everything above 110% should be green. For the costs it’s the other way around, red when higher than 110%, green when it’s 90% or less.
Instead of changing the “Color” property of every single cell, I created a function that has the type of value as the input, and the color is returned. The color that is returned depends on the input, this is just an example of how I needed to use it, but it can be applied to any coloring or formatting.
The function looks like this:

Public Function setMarginColor(ByVal FieldType As String, ByVal FieldValue As Single) As String

Dim ReturnString As String = ""

Dim GoodColor As String = "LimeGreen"

Dim BadColor As String = "Red"

Dim NeutralColor As String = "Black"

If FieldType = "Incoming" And FieldValue <> 0 Then

If FieldValue < 0.9 Then

ReturnString = BadColor

ElseIf FieldValue > 1.1 Then

ReturnString = GoodColor

Else

ReturnString = NeutralColor

End If

ElseIf FieldType = "Outgoing" And FieldValue <> 0 Then

If FieldValue < 0.9 Then

ReturnString = GoodColor

ElseIf FieldValue > 1.1 Then

ReturnString = BadColor

Else

ReturnString = NeutralColor

End If

Else

ReturnString = NeutralColor

End If

Return ReturnString

End Function

There are a few things that happen here. First of all I’m setting the variables used and give them a default value. The field types that I use is “Incoming” for things like turnover and profits, and “Outgoing” for costs and losses. Then I evaluate the value passed from the report and by using a couple of if-statements I determine the right color. In the end I return the color.
In the color properties of the textbox or table cell, the following statement needs to be used (in this case I determine the color for last year’s total costs):
=Code.setMarginColor(“Outgoing”, Sum(Fields!Total_costs_LY.Value))
That’s it! When something change in the coloring or you need to use different evaluation criteria, all you have to do is change it in the function. All the properties in the report don’t need to be changed.
You could even add a user input to this. Let’s say the user would be able to set the margins in which the colors need to be applied. For example, this time someone wants the colors to be applied when the percentage is higher than 105% or lower than 95% (so a 5% difference instead of 10%). Obviously the choice of this boundary would be put into a parameter, and the function has to be extended that the parameter is part of the input variables.
I hope this gives you an idea about how you can use a simple function to apply a consistent color scheme to your report!
Deploying SSRS reports larger than 4 mb

Deploying SSRS reports larger than 4 mb

When you have an SSRS report with a file size larger or close to 4 megabytes that you want to upload to the report server, you might get the following error:

System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.

This has to do with the limit on the report server that by default, it only allows files up to 4 megabytes. To increase the allowed file size, you have to modify the web.config of your report server.

Go to the following directory where the web.config is located:

(Reporting Services folder)Reporting ServicesReportServer

Notice that the folder “ReportManager” also has a web.config. This is not the correct file to edit though, you need to open the web.config in the ReportServer folder.

Open the file with Notepad and search for the following line:

httpRuntime executionTimeout=”9000″

 

 

Add this to this tag:

maxRequestLength=”8096″

In case your report is really big and more than 8 mb, you have to change the number to a desired level. This number is the amount in kilobytes.

Save the file and restart the SQL Reporting Service in the Windows Services.

Now you should be able to deploy the report to the report server!

Matrix with “other” column

Matrix with “other” column

In my earlier blog post “Group small values into an ‘other’ category in a Reporting Services chart“, I talked about how to group small values together into a chart under one category called “other”. Currently I am in the process of setting up a similar idea for a matrix. I have generalized the business case as much as possible to make it easy to understand.

In the case, I have several departments where I want to report costs figures. These are the following departments:
  1. Sales
  2. Consulting
  3. Support
  4. HR
  5. Development
In the matrix below, you can see that I have the departments in the columns and the countries in the rows:
The layout of this matrix is quite simple, it uses a column group for the department grouping, and a separate adjacent column group for the totals. I have added this column group for the totals myself:
To make the matrix more readable, I would like to consolidate the Consulting, Development and HR department into a group called “Other”. The matrix would then look something like this:
These are the steps that I’ve taken to accomplish this. It doesn’t look like the most dynamic way since you’re dealing with hard coded department names in an expression, but when these things are set in stone, it is a nice option to use.
1. In the group properties of the column group that groups the departments, I have set a filter to the departments that I would like to have the detailed figures (the departments displayed with the blue column labels):
2. I have added an adjacent column group in between the two existing groups, and I group these on the field “continent”. All these values have the continent Europe, so this setting should make sure that only one column is displayed. So a prerequisite of this method is that you have at least one column where every value is the same!
3. You might think that you have to set the filter in the group properties of this newly added group to the other departments that you want to show. Unfortunately this doesn’t work because you have grouped on a different field. In this case you have to modify the expression in the data part of the column to make this work. Instead of the Sum(Fields!amount.Value), the expression changes to the following:
=Sum(
Switch(Fields!department.Value = “Dev”, Fields!amount.Value,
Fields!department.Value = “Consulting”, Fields!amount.Value,
Fields!department.Value = “HR”, Fields!amount.Value))
Basically what it does here is for every record, the amount field is evaluated in the Switch function and is included in the Sum when it corresponds with the three conditions in the Switch function.
The final result looks like this: