Using Between, Case When, IF THEN ELSE in a Cube based report

[imeu]

Hi All,

Im very new to Dimensional reporting. Im creating a Crosstab report in that I need to create a range for the measure.

Column1 : count the Number of Employee for the Measure values falls 10-30
Column2 : count the Number of Employee for the Measure values falls 30-50
Column3 : count the Number of Employee for the Measure values falls 50-70
Column4 : count the Number of Employee for the Measure values falls 70-100

Row is Company

How do i do this in Cube based reporting.

Im aware of to do this in Relational Model. Could you please help. Should i use IF THEN ELSE or CASE WHEN or what to use to get correct result.

Thanks a lot in Advance

[MFGF]

Hi All,

Im very new to Dimensional reporting. Im creating a Crosstab report in that I need to create a range for the measure.

Column1 : count the Number of Employee for the Measure values falls 10-30
Column2 : count the Number of Employee for the Measure values falls 30-50
Column3 : count the Number of Employee for the Measure values falls 50-70
Column4 : count the Number of Employee for the Measure values falls 70-100

Row is Company

How do i do this in Cube based reporting.

Im aware of to do this in Relational Model. Could you please help. Should i use IF THEN ELSE or CASE WHEN or what to use to get correct result.

Thanks a lot in Advance

Hi,

You need to think about things rather differently when creating reports over cubes. Rather than having rows of data in tables, you are dealing with sets of members and with measures.

To get a set of employees which have a measure value between 10 and 30, you would probably use a filter() function in a query calculation as follows:

filter([Your Employees level from the cube], [your measure] between 10 and 30)

You could then count these members by adding a count() summary around this, eg

count(currentMeasure within set filter([Your Employees level from the cube], [your measure] between 10 and 30))

You can add similar query calculations with slightly different filter values for the other items you require

Just ensure your ranges don't overlap - in your example, where would an Employee with a measure value of exactly 30 fall? Your logic would seem to indicate the employee should be counted in both the first and second columns? I'm assuming this is not the case, and your second column would be 31-50?

Cheers!

MF.

[imeu]

Hi MF,

Thanks a lot. I was exploring these way of calculating it but was not aware that between can be used inside a filter after measure. Thank you so much.

Yes you are right range will be repeat. Thanks for that.