Need to show only US regions when i select US and not US+Canada

[tarunkrdas2013]

I have a DMR report and My Requirement is when user selects United states value prompt and selects Reporting date from Date prompt as some date and reporting period from value prompt as
say Daily static choice ,then report should display This year sales and Last year sales for only United states,but my report is also showing Regions of Canada+Regions of USA and showing Sales
figures region wise for Each canada and US where as i want only US regions Sales .

I have attached screenshot of actual and expected results

Expression for Regioncalculation is :-

set ([NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] )

Expression for Country data item is :-

set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# )

Cany any one suggest why i am getting Canada Regions+ US regions when i select US only,i need to show only US regions when i select US

[cognostechie]

Your 'expected' and 'actual' screenshots show the same regions but different figures so how are the regions defined. US and Canada fall in the same regions?

I also think that if this post is seen by somebody from your company, they won't be too happy to see the company's sales posted on a public forum.

[MFGF]

I have attached screenshot of actual and expected results

I have removed your attachment from the forum as it looks like real data. Please try to find a way of communicating your requirements without posting sensitive information to the forum.

Please read the forum etiquette post linked below, paying particular attention to point 6:

http://www.cognoise.com/index.php/topic,24030.0.html

MF.

[tarunkrdas2013]

Sure i will not post  data next time

[MFGF]

Sure i will not post  data next time

Can you describe in more detail what the issue is? Your expression seems to be a prompt for a country member, but then you are mentioning regions. How do these relate? Are the regions lower level members in the same hierarchy as country?

MF.

[tarunkrdas2013]

Yes these  [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] are members of country hierarchy ,my requirement is when i select US the only [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] should display and when i select Canada [WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] .
It is to be noted we are passing static choices as UNITED STATES and CANADA from value prompt but any how
set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# ) is unable to filter the sets

[tarunkrdas2013]

when i write this expression for RegionCalc, i am able to get United states

except( set ([NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]),filter (set ([WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]), 'pmt_Country' = 'UNITED STATES'))

But for canada i am unable to get the same

[tarunkrdas2013]

Got the problem ,in DMR my expression for UNITED STATES is [DMR].[Company_Prompt].[Location].[Company]->[all].[1]
and i am writing expression for  country as below..My value prompt is passing UNITED STATES as Static choice which i am unable to capture with below expression

set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[' + prompt('pmt_Country','token' ) + ']'# )

can any one suggest what expression above can i change to get UNITED STATES

[MFGF]

Yes these  [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] are members of country hierarchy ,my requirement is when i select US the only [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] should display and when i select Canada [WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] .
It is to be noted we are passing static choices as UNITED STATES and CANADA from value prompt but any how
set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# ) is unable to filter the sets

Hi,

Can you confirm that in the member tree, [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] are children of the [United States] country member and [WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] are children of the [Canada] member? If so, simply bringing in the children of your prompted-for member should be all you require?

What do you mean by the statement  set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# ) is unable to filter the sets? The approach is not to filter the entire set of region members, it's to bring in the children of the selected country member. Your expression above seems to allow more than one country to be selected in the prompt though? Is that correct? If so, you're not going to be able to use a children() function, since this expects only a single member as the argument. You'd need to use a descendants() function instead

eg descendants(set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# ),[your Region level])

when i write this expression for RegionCalc, i am able to get United states

except( set ([NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]),filter (set ([WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]), 'pmt_Country' = 'UNITED STATES'))

But for canada i am unable to get the same

Why are you using this approach? It seems hugely inefficient. Can you describe the structure of the hierarchy? Are the region members children of country members?

Got the problem ,in DMR my expression for UNITED STATES is [DMR].[Company_Prompt].[Location].[Company]->[all].[1]
and i am writing expression for  country as below..My value prompt is passing UNITED STATES as Static choice which i am unable to capture with below expression

set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[' + prompt('pmt_Country','token' ) + ']'# )

can any one suggest what expression above can i change to get UNITED STATES

I'm getting confused by the changing information you're posting here - earlier you said

set( # '[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[' + prompt('pmt_Country','token' ) + ']'# )

was the expression you are using in your calculation. How does this resolve after the prompt has been fulfilled? What does the completed MUN for a country look like? I'd assume

[DMR].[Company_Prompt].[Location].[Company]->[all].[1].[@MEMBER].[UNITED STATES]

or something similar, based on this expression.

Now you're saying the USA is

[DMR].[Company_Prompt].[Location].[Company]->[all].[1]

This seems to contradict what you put earlier. Can you clarify - what does the MUN of a Country member look like? Also what does the MUN of a Region member look like?

I'm going to make some assumptions here - but bear with me. Assuming what you put in the last post is correct in terms of the USA MUN, try the following:

For the columns of your crosstab, use a query calculation (with a type of "other expression") with the following expression:

descendants(set([your Country level] -> ?pmt_Country?),[your Region level])

Remove all static choices from the Value Prompt and base the prompt on the Country level of your hierarchy.

If this isn't successful, you're going to need to describe to us how your hierarchy is structured.

MF.

[tarunkrdas2013]

You are God of cognos ...i bow my head with respect.

This thread gave me lot of learning i used below expression based on your inputs and achieved what i am looking for

intersect(descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3,self before),set ([NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]))

[MFGF]

You are God of cognos ...i bow my head with respect.

This thread gave me lot of learning i used below expression based on your inputs and achieved what i am looking for

intersect(descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3,self before),set ([NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]))

Hi,

Can you explain your approach? Using an intersect seems like a very complex way to arrive at the members you need - is there a reason you are doing this?

What results do you get simply by using descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3)

Can you answer the previous questions about the structure of your hierarchy? There's often a simple solution but unless you tell us how your hierarchy is arranged we're all guessing when making suggestions to you.

MF.

[tarunkrdas2013]

Used Intersect because i wanted in crosstab to display [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] when i select United states and [WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] when i select Canada
but when i used descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3) Crosstab was showing all members of Regions Level so i used Intersect to achieve

Hierarchy is Company then levels are Company(All),Company,Division,Region,District,Location

[MFGF]

Used Intersect because i wanted in crosstab to display [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] when i select United states and [WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC] when i select Canada
but when i used descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3) Crosstab was showing all members of Regions Level so i used Intersect to achieve

Hierarchy is Company then levels are Company(All),Company,Division,Region,District,Location

Which level (of the ones you posted above) is the level that contains the United States and Canada members? Company? I assume your region members are in the Region level?

What do you see if you use an expression descendants([your United States member],3)? Do you get all regions or just those belonging to the United States member?

I'm guessing you will get just [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] returned?

If so, then descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3) ought to return the same set of members if you select just the United States member from your prompt?

You really shouldn't need to use an intersect function here - it might work but I'm thinking it's not an efficient way of doing things.

MF.

[tarunkrdas2013]

yes when i used descendants([your United States member],3) i am getting all members so used Intersect

[MFGF]

yes when i used descendants([your United States member],3) i am getting all members so used Intersect

We are working blind here - we can't see the member tree in your package, so we can only rely on the answers you provide to tell us how your data is organised. The answers you are giving are very brief and lacking in detail, so it's almost impossible to understand the exact structure of your data in the hierarchy.

Normally in a hierarchy, you find members have descendants that pertain only to that member, and not to any other members. For example, in a Date hierarchy, you expect to find quarter members and month members for a specific year exist only as descendants of that year member. If you expanded 2014 and found months of other years as descendants, you would be rather confused. That's exactly the situation I find myself in here. I can't see your hierarchy, but you are telling me that regions for both United States and Canada are descendants of the United States member? That seems to make no sense whatsoever.

Can you explain, in detail, how this hierarchy is arranged? Why would regions for two countries exist as descendants of one specific country member?

MF.

[tarunkrdas2013]

i am extremely sorry for this confusion i posted wrong info in my recent previous post,now its correct what i type below

descendants([your United States member],3) i got regions of US

when i typed

descendants([your Canada States member],3) i got regions of Canada

[MFGF]

i am extremely sorry for this confusion i posted wrong info in my recent previous post,now its correct what i type below

descendants([your United States member],3) i got regions of US

when i typed

descendants([your Canada States member],3) i got regions of Canada

That seems a lot more sensible. It also seems to support my assertion that you don't need to use intersect(). If you get the correct regions using these fixed country members ([United States] and [Canada]) independently in your descendants() function, then all you logically need to do is to substitute the member selected from your prompt in place of the fixed country member to get the correct regions returned.

So - if you take

descendants([your United States member],3)

and convert the expression to

descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3)

then select just the United States member from your prompt, you should get the exact same set of regions returned. No need for an intersect().

Can you confirm?

MF.

[tarunkrdas2013]

When i selected United States it gave me below:-
NW1 NW2 NW3 BA1 BA2 BA3 MW1 MW2 MW3 MW4 MW5 LA1 LA2 LA3 LA4 SD1 SD2 SD3 TE1 TE2 NE1 NE2 NE3 NE4 NE5 SE1 SE2 SE3 SE4 BD1 BO1

Actually i wanted only these

[NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]

Also when i select Canada it gave me below:-

EC1 EC2 EC3 WC1 WC2 OC1

whereas i wanted only below :-

[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]

This is the reason i used Intersect as we cant change the existing look and feel of report

[MFGF]

When i selected United States it gave me below:-
NW1 NW2 NW3 BA1 BA2 BA3 MW1 MW2 MW3 MW4 MW5 LA1 LA2 LA3 LA4 SD1 SD2 SD3 TE1 TE2 NE1 NE2 NE3 NE4 NE5 SE1 SE2 SE3 SE4 BD1 BO1

Actually i wanted only these

[NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]

Also when i select Canada it gave me below:-

EC1 EC2 EC3 WC1 WC2 OC1

whereas i wanted only below :-

[WC1],[WC2],[EC1],[EC2],[EC3], [WC],[EC]

This is the reason i used Intersect as we cant change the existing look and feel of report

The example you posted above shows you requiring entirely different regions for United States than the regions that are returned as its descendants? Is that correct? How can you arrive at [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE] when these are not included in the list of regions for United States? Are NW1 NW2 NW3 BA1 BA2 BA3 MW1 MW2 MW3 MW4 MW5 LA1 LA2 LA3 LA4 SD1 SD2 SD3 TE1 TE2 NE1 NE2 NE3 NE4 NE5 SE1 SE2 SE3 SE4 BD1 BO1 something other than regions? Are they members from a different level? Your posts are very confusing!!

What *exact* expression are you using to retrieve these members?

MF.

[tarunkrdas2013]

exactly i wrote below expression to get regions specifically like [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]

descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3,self before)

to get regions what i want when i select United states and then used intersect

N
NW
NW1
NW2
NW3
BA
BA1
BA2
BA3
MW
MW1
MW2
MW3
MW4
MW5
S
LA
LA1
LA2
LA3
LA4
SD
SD1
SD2
SD3
TE
TE1
TE2
E
NE
NE1
NE2
NE3
NE4
NE5
SE
SE1
SE2
SE3
SE4
BD
BD
BD1
BO
BO
BO1

[MFGF]

exactly i wrote below expression to get regions specifically like [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE]

descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3,self before)

to get regions what i want when i select United states and then used intersect

N
NW
NW1
NW2
NW3
BA
BA1
BA2
BA3
MW
MW1
MW2
MW3
MW4
MW5
S
LA
LA1
LA2
LA3
LA4
SD
SD1
SD2
SD3
TE
TE1
TE2
E
NE
NE1
NE2
NE3
NE4
NE5
SE
SE1
SE2
SE3
SE4
BD
BD
BD1
BO
BO
BO1

Why have you added "self before" to the expression? This will return all intermediate members as well as the regions. What do you get if you use

descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),3)

MF.

[tarunkrdas2013]

used self before so as to get all members of united states ,below is the regions when i select United states whereas i wanted only [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],hence used intersect

NW1
NW2
NW3
BA1
BA2
BA3
MW1
MW2
MW3
MW4
MW5
LA1
LA2
LA3
LA4
SD1
SD2
SD3
TE1
TE2
NE1
NE2
NE3
NE4
NE5
SE1
SE2
SE3
SE4
BD1
BO1

[MFGF]

used self before so as to get all members of united states ,below is the regions when i select United states whereas i wanted only [NW],[BA],[LA],[SD],[NE],[SE],[MW],[TE],hence used intersect

NW1
NW2
NW3
BA1
BA2
BA3
MW1
MW2
MW3
MW4
MW5
LA1
LA2
LA3
LA4
SD1
SD2
SD3
TE1
TE2
NE1
NE2
NE3
NE4
NE5
SE1
SE2
SE3
SE4
BD1
BO1

Hi,

Your posts are really confusing. The list of regions you require is not in the list of regions you get returned for the United States. For example you say you want [NW], but there is no [NW] region in the list you posted. Are you looking at the correct level? Is [NW] a member of the level above? What do you see returned if you use

descendants(set( [Hierarchical].[Company].[Location].[Company]-> ?pmt_Country?),2)

Does this give you the members you require? If not, please (please please) can you explain how your data is structured?

MF.

[tarunkrdas2013]

apologies i may be very bad in explaining my problem,but your input is appreciated because based on your inputs only i was able to achieve what i was looking for ,report is working fine now..thanks a lot really ,it means a lot to me

[MFGF]

Yay! So glad we got there in the end! :)

Getting a report to work is one thing, but getting it to work in the most efficient (and simple) manner sometimes takes more thought and testing.

Cheers!

MF.