Top 10 Results on a Crosstab Report with multiple data items

[SusieNewbie]

Hi All,

I am really new to Cognos and most of my reporting experience has been using Relational data and now someone has had the bright idea of giving me Dimensional data to create a report from....and I am stuck!
I am trying to create a crosstab report using dimensional data showing the top 10 sales for 2015 by supplier, brand and product.

All my data items are at different levels of their dimensions so they have been created by using

Code Select Expand

descendants([Member],LEVEL NUMBER)

I have created a new data item with the following expression for the supplier top 10:

Code Select Expand

topcount([SUPPLIER LEVEL],10,[GROSS_SALES])
and to get the corresponding data for Brand and Product, I have 2 other data items with expression

Code Select Expand

topcount([PRODUCT/BRANDLEVEL],1,[GROSS_SALES])

I am getting results but on further analysis, I am not getting the top 10 based on sales. I have read on a few forums that it is not possible to do a topcount on multiple data items but then some forums have suggested that this is possible??

Please forgive me if I have any of the terminology incorrect and feel free to ask me any questions if I haven't explained myself correctly.
Any help would be greatly appreciated
Susan

[MFGF]

Hi All,

I am really new to Cognos and most of my reporting experience has been using Relational data and now someone has had the bright idea of giving me Dimensional data to create a report from....and I am stuck!
I am trying to create a crosstab report using dimensional data showing the top 10 sales for 2015 by supplier, brand and product.

All my data items are at different levels of their dimensions so they have been created by using

Code Select Expand

descendants([Member],LEVEL NUMBER)

I have created a new data item with the following expression for the supplier top 10:

Code Select Expand

topcount([SUPPLIER LEVEL],10,[GROSS_SALES])
and to get the corresponding data for Brand and Product, I have 2 other data items with expression

Code Select Expand

topcount([PRODUCT/BRANDLEVEL],1,[GROSS_SALES])

I am getting results but on further analysis, I am not getting the top 10 based on sales. I have read on a few forums that it is not possible to do a topcount on multiple data items but then some forums have suggested that this is possible??

Please forgive me if I have any of the terminology incorrect and feel free to ask me any questions if I haven't explained myself correctly.
Any help would be greatly appreciated
Susan

Hi Susan,

Writing reports against dimensional sources is a whole new ball-game if you've only ever done relational reporting. It can seem daunting at first, but once you get used to the fact you're not dealing with rows (but with members, sets, hierarchies and measures) it soon becomes easy, and you wonder how you ever managed in the limited relational world! :)

I'm not quite clear on your requirement here, though. Do you want just ten members in total, or are you looking for three distinct sets of top ten members?

Using topcount() is a good start - it's a very useful dimensional function and much easier to use than the relational rank / filter technique.

topcount(SUPPLIER LEVEL],10,[GROSS SALES]) will give you the top 10 supplier members based on Gross Sales measure values. Simple? Not quite as simple as it seems, though. I'm assuming you have a number of other dimensions in your cube, too? If you are not referencing these in your expression, then the top 10 will be based on the default member of every other dimension you're not referring to.

Let's say you have three other dimensions - Date, Product and Brand. Usually these will have a root member at the top of the hierarchy, acting as the default member (All Dates, All Products and All Brands). The expression above is the top 10 Gross Sales suppliers for All Dates, All Products and All Brands. Whenever you code an expression like this, you need to consider members from all the dimensions, even if you don't explicitly refer to them.

If you wanted the top 10 Gross Sales suppliers in 2015, then that brings in a different member from the Date dimension (the 2015 year member). Your expression would be

topCount([SUPPLIER LEVEL],10,tuple([your 2015 year member],[GROSS _SALES]))

Again, we're not specifying all dimensions in the expression, so this would be for All Products and All Brands

If you put this expression into the rows of your crosstab, you will get 10 supplier members returned - the top 10 based on GROSS_SALES in 2015.

What I'm not clear about is how top 10 products and top 10 brands fit into this. Do you need to show them separately? If so, you could add extra query calculations stacked below the suppliers in the rows area - one for top 10 products by GROSS_SALES in 2015 and one for top 10 brands by GROSS_SALES in 2015. Just use the same technique as above.

If I'm misunderstanding, please forgive my ignorance - after all, I am a muppet :)

Cheers!

MF.

[SusieNewbie]

MF, you are a star!

I originally was thinking about it in terms of rows but now you have explained the difference between the two, it has made me look at it in a totally different light  and with your help, I am getting the results I was expecting.....Plus it has also given me an idea (lightbulb moment  :)) for a new report which I think will benefit my customers.

Thank you so much for all your help
Susan