Tuesday, March 30, 2010

Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid

One of my colleagues had been facing a funny issue since yesterday, and I had to find a solution for it. It is related to Reporting Services 2008. He had created a doughnut chart that shows some values for some categories and had set the tooltip for the series. The data source he had used was Analysis Services cube. Report preview was working fine but he got the issue when the mouse is moved over it too see the tooltip. Whenever the mouse is moved over the doughnut, chart is disappeared and error is shown as Parameter is not valid. This does not happen when the tooltip is NOT set. Funny?

I am not sure whether it is a bug or it is the standard behavior of it. As usual I googled to see a solution but no luck. Fortunately I found the issue. It is because of some categories contain values “0”. Since “0” values are not displayed with doughnut charts (this is same for pie charts too), it throws this error when the mouse is moved over it. Once the zero values are excluded from the data set, it started working fine.

Interesting, let me show you how it comes. Create a report and add a dataset with following query.

   1: SELECT 'A' AS Type, 200 AS Value
   2: UNION
   3: SELECT 'B' AS Type, 150 AS Value
   4: UNION
   5: SELECT 'C' AS Type, 0 AS Value
   6: UNION
   7: SELECT 'D' AS Type, 40 AS Value
   8: ORDER BY 1

Then add a doughnut chart to the layout and drag the “Type” and drop onto Category Field Section. Next, drag the “Value” and drop onto Data Field Section. Now get the “Series Properties” of the “Value” which is on Data Field Section. Set the “Tooltip” as “[Value]”. Click on Preview to see the chart. You should see something like this.

Doughnut1 Move the mouse over it now. You should see this.

Doughnut2

If you remove the third record from the dataset, you will not get this issue. Let me share the MDX query which had been written for the original chart and fixed one.

   1: // Old query. It returns some members with zero values
   2: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS
   3:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 
   4:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
   5: FROM [Cube]
   6:  
   7:  
   8: // Modified query. It does not return members with zero values
   9: WITH MEMBER [Measures].[Measure1New] 
  10:     AS 'IIF([Measures].[Measure1] = 0
  11:             , NULL
  12:             , [Measures].[Measure1])'
  13:  
  14: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS
  15:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 
  16:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
  17: FROM [Cube]

Note that you will NOT get this issue if you enable 3D Effects on Doughnut :).
You may know some different reasons for this and different workaround for this. If so, share with us.

No comments: