multiple filters in crosstab

[CognosAdmn]

Hello,

I need to create a report with multiple calculations and filters as follows.

This crosstab reports needs to have "Months" as the Column and "Location" nested below "Months". There are only two locations: Toronto and Ottawa.

I need to count the "Products" for each "Location", "ProductLine" and "ProductType". There are only 3 different "ProductLines" and 4 different "ProductTypes".

In other words

Row1 & Column1 will have "Number of Products when ProductLine is 'A' and ProductType is 'W' and Location is "Toronto"
Row2 & Column1 will have "Number of Products when ProductLine is 'A' and ProductType is 'X' and Location is "Toronto"
Row3 & Column1 will have "Number of Products when ProductLine is 'A' and ProductType is 'Y' and Location is "Toronto"
Row4 & Column1 will have "Number of Products when ProductLine is 'A' and ProductType is 'Z' and Location is "Toronto"

Row5 & Column1 will have "Number of Products when ProductLine is 'B' and ProductType is 'W' and Location is "Toronto"
Row6 & Column1 will have "Number of Products when ProductLine is 'B' and ProductType is 'X' and Location is "Toronto"
Row7 & Column1 will have "Number of Products when ProductLine is 'B' and ProductType is 'Y' and Location is "Toronto"
Row8 & Column1 will have "Number of Products when ProductLine is 'B' and ProductType is 'Z' and Location is "Toronto"

Row1 & Column2 will have "Number of Products when ProductLine is 'A' and ProductType is 'W' and Location is "Ottawa"
Row2 & Column2 will have "Number of Products when ProductLine is 'A' and ProductType is 'X' and Location is "Ottawa"
Row3 & Column2 will have "Number of Products when ProductLine is 'A' and ProductType is 'Y' and Location is "Ottawa"
Row4 & Column2 will have "Number of Products when ProductLine is 'A' and ProductType is 'Z' and Location is "Ottawa"

Row5 & Column2 will have "Number of Products when ProductLine is 'B' and ProductType is 'W' and Location is "Ottawa"
Row6 & Column2 will have "Number of Products when ProductLine is 'B' and ProductType is 'X' and Location is "Ottawa"
Row7 & Column2 will have "Number of Products when ProductLine is 'B' and ProductType is 'Y' and Location is "Ottawa"
Row8 & Column2 will have "Number of Products when ProductLine is 'B' and ProductType is 'Z' and Location is "Ottawa"

Is this possible? If it is, could someone please be able to help me out? Thank you.

I appreciate your assistance in advance.

Shaam

[ydeliwala]

Try this!

Bring Product Line in rows, nest Product Type under Product line.

Bring Months in columns (I am assuming you will filter on the year to get only one year and you have appropriate logic on month column to sort them on the order of months), nest location under Months.

Bring Product Number (or whichever data item that uniquely identifies product) on the measures. Click the Product Number in the default measure space and change its aggregation property to Count. Change the measure label to No of Products.

It should get you where you need to. Let me know if this works!

[CognosAdmn]

This is perfect. Thank you YO!.

However... :)
1. Instead of having nested crosstab, [ProductLine | ProductType ], the users would like to see a single row header for example, if ProductLine is A and Product Type is X, then the leftmost row should read "Number of new X products sold in A section", instead of two fields indicating  [A | X].  I could change the label of ProductTypes to match the user-friendly sentences, but how about the ProductLine Labels?

2. For one particular product, the users would like to see the ratio. So if ProductLine is B and ProductType is Y then the next immediate row should have the count of Products/Total Products. Does crosstab allow this?

3. Also, if a certain ProductLine and ProductType were not sold in the reporting year, then this row is missing in the report, but the users would like to see it with a '0' beside it. I understand that if the fields dont exist in the database then, there is nothing to be displayed in Cognos. But is there a way to default the numbers to 0 in Cognos? Or is there something I could do in the database?


Thank you in advance for your help.

Shaam

[dhavcog]

Hi , I was having same problem, I have solved it,, You need to do bit work around with this,,

create one static query with all Month Name and City Name,,
and join it with your existing query with (0 -1 ) cardinality, in other words perform outer join between these two query,, so now you final query will have null columns if product is missing for that month,, and change the data format for that column , change the setting display null values as 0.

it will work,, let me know,, otherwise,, i will show you in  bit depth,,

thanks,
dhaval

[CognosAdmn]

Hi Dhaval,

The tables are joined using left outer join. But they are joined using the DayKey and not the MonthKey. I am not sure if this the cause for dropping some combination of ProductLines and ProductTypes.

In another forum i was advised to create a query with 0's and create a UNION between them. I will post back if this works.

Thank you for your time.

Shaam