Dimensional Retrieves All

[Francis aka khayman]

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?

[MFGF]

... 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.

[Francis aka khayman]

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? :)