Friday, September 16, 2011

Case Sensitivity with SSRS Grouping ?

When I created a SSRS report to retrieve SSAS cube data, I found that the dimension members which contains same name but has different case, has been missing. As an example if you have two dimension members of Item dimension as item1 and Item1, then only one will be shown.
The reason was that it does not support case sensitivity with grouping.

To have the case sensitivity in the report I had to do two changes. One is in the cube level and the other one is in the report data set level.

At the dimension level you have to allow case sensitive. To do that open the dimension in the dimension editor using BIDS, the select the dimension and view the properties. At the top of the Properties window you will find the Collation property. Click on the button and put a tick in front of Case Sensitive value. Then process the dimension and then process the cube.

Even though you change this, you will not get the expected result in the report. To get it, select the Dataset in the Report Data tab and double click on it. Then go to the Options section and then select True from Case sensitivity: drop down list. Click Ok and preview the report. Then you should get the both item1 and Item1 records.

2 comments:

  1. how so in vs 2010 while editing an .rdlc file?

    ReplyDelete
  2. Any idea how the same issue can be handled in RDLC? I have an RDLC report, I have a column chart whose X axis is shows PerformedBy person names, Y axis shows the count of studies for each PerformedBy person.The dataset contains two PerformedBy names like 'john' and 'JOHN'. But in the chart it displays only JOHN. The data for both 'john' and 'JOHN' are combined and shown under 'JOHN'.

    ReplyDelete