Group rows in crosstab and aggregate measures

[morin]

Hello,

My task is to create a crosstab that shows to which amount employees ( different types: generic, intern, extern) have been booked a month: 

              2014-01     2014-02     2014-03
----------------------------------------------------
generic   0      5      0   
intern   10      10      20
extern   0      0      0


The data comes from a fact table groupAbsorption(employee, type, month, value)


At the moment the table looks like this:

         2014-01      2014-02     2014-03
----------------------------------------------------
generic   0      5       0   
generic    0      0      0
intern   4      4      10
intern   4      4      5
intern   2      2      5

I tried to group the crosstab on employee type (generic, intern, ...) but the message "you can't group the selected objects" appears.

Do you know how I can manage to have the crosstab grouped by employee type and the value automatically summed?

I appreciate any help that you can provide!

[Francis aka khayman]

http://www.cognoise.com/index.php/topic,24030.0.html

   c. If you are asking for help with how to achieve a requirement, please indicate whether you are using a relational package or a dimensional package.

[morin]

I'm using a dimensional package. In the meantime I read somewhere that it is not possible to group the rows of a crosstab, since they are already grouped. My workaround was to group the data in Data Manager.

[Francis aka khayman]

crosstab automatically groups by the level businesskeys... i am quite certain that in your data generic(0,5,0) has different businesskey from generic(0,0,0)...  same for intern.

one way to get around this is to eliminate the 'dimensionality' (so to speak) of your members.

try this...


substring(caption([employees]),1)

just a warning, the performance might affected.