Dimensional row filter/slicer question

[Corrigon]

Hi All, am hoping someone can help me.

I need to produce a crosstab report. Cognos version is 10.2.1, data source is MSAS cube.

The report is based on Sales Orders, which contain Product Codes.

The requirement is to produce a list of customers who each have been billed for any combination of 4x specific codes ([ProdA], [ProdB], [ProdC], [ProdD]) within the month of November. They might have been billed for one, or many; either way, they need to show up on the report.

The report needs to display on the Left Edge of the crosstab the [Dataset] the customer lives in, the [Country], the [Name] of the Customer and then:

- What they were billed for each of the 4x Products codes, displayed separately.
- What they were billed for everything else, grouped together as one row called 'Other Products'.

Measure is [Value]. Column is November 2016. Dataset, Hub, Name and Product are all from hierarchies located in a Dimension called Customer Sales within the cube.

Can anyone suggest a way to approach this?

thanks in advance,
Dave

[MFGF]

Hi All, am hoping someone can help me.

I need to produce a crosstab report. Cognos version is 10.2.1, data source is MSAS cube.

The report is based on Sales Orders, which contain Product Codes.

The requirement is to produce a list of customers who each have been billed for any combination of 4x specific codes ([ProdA], [ProdB], [ProdC], [ProdD]) within the month of November. They might have been billed for one, or many; either way, they need to show up on the report.

The report needs to display on the Left Edge of the crosstab the [Dataset] the customer lives in, the [Country], the [Name] of the Customer and then:

- What they were billed for each of the 4x Products codes, displayed separately.
- What they were billed for everything else, grouped together as one row called 'Other Products'.

Measure is [Value]. Column is November 2016. Dataset, Hub, Name and Product are all from hierarchies located in a Dimension called Customer Sales within the cube.

Can anyone suggest a way to approach this?

thanks in advance,
Dave

Hi,

The first challenge is in isolating the relevant customers. You probably need a combination of things for this:

a. The billed amount in November. You can get this with a tuple() function, eg tuple( [your November month member], [your Billed Amount measure])
b. The total of this amount for the four Product members in question. You can use an aggregate() summary and a set() function for this eg aggregate( [the Tuple expression above] within set set([ProdA],[ProdB],[ProdC],[ProdD]) )
c. The filtered list of Customer members for whom this amount is greater than zero. You can use a filter() function for this, eg filter([Your Customers level], [your Total aggregated value above] > 0)

Now you have your customers, you can bring these in as rows in your crosstab. You can nest the four Product members alongside, and drop the billed amount in November (a. above) nested alongside these.

Lastly you need the sum of your measure in November for all other products. You can get this by aggregating your tuple value across the set of all products except these four, eg

aggregate([the Tuple expression in a. above] within set except( [your Products level], set([ProdA],[ProdB],[ProdC],[ProdD]) ) )

Drop this item directly below the four nested Product members in your row headings.

Good luck!

MF.

[Corrigon]

Thanks MFGF, that worked exactly as described! Many thanks!