Best practice to build a crosstab with differents filters

[philelmousse]

Hello,

I want build a crosstab and each row should use a different filter. I have to create a query for each row and make union to get totals, right ?

Is this the correct way ?

Thanks for the advices.

[MFGF]

If it's a relational package, you will need multiple queries yes.  If you're using a dimensional package then you can do it all in one query.

Regards,

MF.

[philelmousse]

its based on a star schema. So one query for each row with singleton in the crosstab ? Argh

[MFGF]

Is there any option to model it as DMR in Framework Manager?  It would make reports like this much easier to write.

MF.

[philelmousse]

I made my model with regular dimensions and a fact.

[philelmousse]

So If I make the filters on my fact table directly in framework manager I will be able to use only one query for my crosstab, right ?

[MFGF]

No - not with a relational model.  You would need to build a dimensional model by creating Regular Dimensions and Measure Dimensions in Framework Manager, then publishing these into a package and using them.

Regards,

MF.

[philelmousse]

Thats what i did :) But i'm blocked to filter each row in my crosstab

[philelmousse]

please, how do it in one query ? I have regular dimension and measure but not able to filter each row. I tried to drag and drop my members on each level and then cut the level but it aggregate the total

[philelmousse]

I change the box type to none. Is it correct ?

[MFGF]

How do you want the 'filters' to work? There are different ways of doing this, either by just bringing in individual members for your rows, or by creating query calculations for each row using tuples/sets/filter functions etc.  If you can give us some ideas of what you're facing, we might be able to suggest the best way forward.

Best regards,

MF.

[philelmousse]

In the rows I have calculated queries where I juste make something like that :
"Member1 + Member2 + Member3"
To filter that I add members of another dimension on the right of calculated query and its ok i get the good values. But now I want delete the column where I have the calculated queries and keep the same values. So change the box type property is the way to do it ?