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

Tuple - Split MemberSet into Members

Started by PoeticRobot, 28 May 2019 08:04:50 AM

Previous topic - Next topic

PoeticRobot

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
TYRES76,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
TYRES76,124
AREA 116,163
AREA 22,026
AREA 314,437
AREA 417,593
AREA 514,996
AREA 619,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


MFGF

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
TYRES76,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
TYRES76,124
AREA 116,163
AREA 22,026
AREA 314,437
AREA 417,593
AREA 514,996
AREA 619,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.
Meep!

PoeticRobot

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.