If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

cross tab

Started by sspk, 16 Apr 2008 10:20:46 AM

Previous topic - Next topic

sspk

Hello All,
A report is needed to detail sales in units and dollasrs by item.
A row for each of the top ten customers for each item(this is dynamic field as the top ten for each item will change depending on the item)and a row for "all others" for sales by year 2006,2007 and  by quarter for 2008.

I am trying to do this like a cross tab ,in the rows i am keeping the data items like item desc,item#,customers. And in the column's i kept the year and in the measures i am droping both units and dollars.
Now my question is how to get the top ten customers(in terms of 'units') for each item and how to get quarters for 2008.Any ideas how to do this(am i doing it in correct manner)

almeids

For the top 10, use the rank function with a FOR clause (i.e. for [item]) for the appropriate scope.  Your customer dimension data item should be an expression like:
if ([rank_data_item]<11) then ([customer name]) else ('All Other')

Calculate the column categories similarly, with an expression like:
if ([year]='2008') then ([quarter]) else ([year])

...except you'll probably want to use an expression to calculate the current year, or pull it from the database, rather than hardcoding the 2008.  You may also need to calculate the [year] and/or [quarter] depending on what's available to you in your data.