Quote from: imeu on 16 Jul 2015 06:03:05 AM
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.