I am converting our relational model into dimensional. I created the following crosstab and filtered for a SalesRep1
Sales Month
Country, Product
I am expecting the result to be
Jan Feb
USA Product1 10 20
USA Product2 5 5
where Product1 and Product2 are Products sold by SalesRep1. This is the result I got for relational modeling and simple SQL.
However I was surprised the crosstab returned other products not by SalesRep1 with null values:
Jan Feb
USA Product1 10 20
USA Product2 5 5
USA ProductA null null
USA ProductB null null
I have to suppress the nulls in Crosstab property to take them out. Is this normal behaviour or I screwed up somewhere in my model?
Quote from: khayman on 02 Aug 2013 04:03:11 AM
... filtered for a SalesRep1...
Hi,
Reporting from a dimensional source is a rather different experience than from a relational source. It's a bit of a steep learning curve at first, but once you get the hang of it you will wonder however you managed to live without it :)
One of the golden rules is that you don't use detail filters in reports against dimensional sources. Instead you can either just drag the required members into the crosstab (ie just the two Product members you want), or you can drag in a query calculation that filters the products level based on the tuple of the sales rep member and the measure being greater than zero
ie filter([Your Products level],tuple([Your chosen sales rep member],[your measure]) > 0)
Cheers!
MF.
This is great info, I got what you mean about filtering products and about dimensional reporting to be rather different. I hope you bear with me while I'm getting the hang of it...
Ok my time dimension is designed this way:
Date: business key = 20130101 caption '20130101'
Month: business key = 201301 caption 'Jan'
Year: business key = 2013 caption '2013'
and I want a crosstab with 2 years data comparing Sales for Jan this year and sales for Jan last year (by Country, Product for rows).
However when I layed out the crosstab, after the first set of 12 months, I got another set:
Jan Feb Mar.... Oct Nov Dec Jan Feb Mar ... Oct Nov Dec
USA Product 1 x x x x x x x x x x x x
I figure this is because the businesskey of my month. Since Jan of this year (201301) has different from Jan last year (201201) Cognos will not combine them.
My question is is this the usual way of doing Time Dimension dimensionally? :)