COGNOiSe.com - The IBM Cognos Community

Hello all,

I'm currently trying to create a report where I can show a total number of units sold for a Set, but then show the breakdown of that Set into its respective Members. The issue is that I am using a tuple to get the numbers required, and you can't use a tuple with a Set.

In more detail:
I have a number of Sales Areas, which are selected via a prompt. These are grouped in a Set called "Selected Region". I then have a member for units sold, "Tyres", and "MTD TY" as another member.

I have these set up in a crosstab as below, using Qty Sold as a default measure:

	MTD TY
TYRES	76,124

However, if I try to add "Selected Region" underneath "Tyres", it will show all of the Areas but the total units do not add up to the number shown next to "Tyres".

	MTD TY
TYRES	76,124
AREA 1	16,163
AREA 2	2,026
AREA 3	14,437
AREA 4	17,593
AREA 5	14,996
AREA 6	19,152

I understand that this is because the areas are showing all products sold, rather than just Tyres, but is there anyway that I can split out the members in this set but also tuple the results to show Tyres only? I've tried things like Children, but am getting errors such as invalid coercion from memberset to member.
I'm hoping that the answer is pretty simple, and that I've just been looking at this the wrong way, and/or for too long!

Thanks in advance for your help.
Nick

Quote from: PoeticRobot on 28 May 2019 08:04:50 AM
Hello all,

I'm currently trying to create a report where I can show a total number of units sold for a Set, but then show the breakdown of that Set into its respective Members. The issue is that I am using a tuple to get the numbers required, and you can't use a tuple with a Set.

In more detail:
I have a number of Sales Areas, which are selected via a prompt. These are grouped in a Set called "Selected Region". I then have a member for units sold, "Tyres", and "MTD TY" as another member.

I have these set up in a crosstab as below, using Qty Sold as a default measure:

	MTD TY
TYRES	76,124

However, if I try to add "Selected Region" underneath "Tyres", it will show all of the Areas but the total units do not add up to the number shown next to "Tyres".

	MTD TY
TYRES	76,124
AREA 1	16,163
AREA 2	2,026
AREA 3	14,437
AREA 4	17,593
AREA 5	14,996
AREA 6	19,152

I understand that this is because the areas are showing all products sold, rather than just Tyres, but is there anyway that I can split out the members in this set but also tuple the results to show Tyres only? I've tried things like Children, but am getting errors such as invalid coercion from memberset to member.
I'm hoping that the answer is pretty simple, and that I've just been looking at this the wrong way, and/or for too long!

Thanks in advance for your help.
Nick

Have you tried changing your default measure to a calculated tuple - ie tuple([Tyres member],[Qty Sold])

MF.

Hi MFGF,

I hadn't even considered that option. At first glance, it does exactly what I need, and I can use an aggregate currentmeasure to give me a data item to play with elsewhere in the report.

Thanks for your help.