MDX query for an Histogram

Hi All,

Trying to plot an Histogram chart in Reporting services using the data source as a cube. Currently the cube doesnt retrives the intervals from the cube for which the count is 0. I need the chart to show all the intervals everytime even when the count for that particular interval is 0.

Has anyone created any such chart? or can any one please help me with the correct MDX query for it.

Awaiting a response.

Regards,
Sumeet

What you will find is that if a count measure does not have any values it will be empty rather than 0 and in your query you probably have a "NON EMPTY" statement on the axis which is excluding the empty cells. Removing the "NON EMPTY" clause from the axis in question should display the members with empty (0) values. If this does not help, posting your query might help in seeing what is going on.|||

The Non empty thing doesnt works, already tried with that. The query used in the Data source view is as follows:-

Case when Earned_Value <> 0 or Earned_Value <> null then
Case when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) < -0.50 then
'Less than -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.50 and -0.41 then
'-41% to -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.40and -0.31 then
'-31% to -40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.30 and -0.21 then
'-21% to -30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.20 and -0.11 then
'-11% to -20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.10 and -0.01 then
'-1% to -10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) = 0 then
'0%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.01 and 0.10 then
'1% to 10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.11 and 0.20 then
'11% to 20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.21 and 0.30 then
'21% to 30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.31 and 0.40 then
'31% to 40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.41 and 0.50 then
'41% to 50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) > 0.50 then
'Above 50%'
end
end

The above query excludes the intervals itself for which their is not a value and thus no zero value is assigned to that interval as the interval itself doesnot exists. So actually the above query itself should be modified so that zero value can be assigned to the intervals for which no value is present.

Do anyone have an idea how this can be done?

Regards,
Sumeet

|||

I'm not sure if this will help or not, you have swapped from talking about an MDX query against the cube to wanting to change a named view in the DSV. But if all you want to do is to assign the records with 0 or no Earned_Value some sort of label, then adding the lines in red below should fix this.

Case when Earned_Value <> 0 or Earned_Value <> null then
Case when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) < -0.50 then
'Less than -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.50 and -0.41 then
'-41% to -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.40and -0.31 then
'-31% to -40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.30 and -0.21 then
'-21% to -30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.20 and -0.11 then
'-11% to -20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.10 and -0.01 then
'-1% to -10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) = 0 then
'0%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.01 and 0.10 then
'1% to 10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.11 and 0.20 then
'11% to 20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.21 and 0.30 then
'21% to 30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.31 and 0.40 then
'31% to 40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.41 and 0.50 then
'41% to 50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) > 0.50 then
'Above 50%'
end
else
'no value'
end