Count expression in Dimension reporting to get the childrens childrens

[cognos05]

I have a hierarchy like Region,Agency ,Rep and Accounts .

I have the region and agency shown nested in rows and now for every member of agency i need  to count the number of accoutns that had a sale greater than zero for a specified product category for that agency.

Count(([H0 Sales]) within set filter(children(currentmember([DistributorSalesCube].[ShipTo Customer].[ShipTo Customer])),tuple(currentMember([DistributorSalesCube].[Items].[Items]),[Current FYTD],[H0 Sales])>0))

This is my expression to go to one level of a member to get the children , but how do i goand get the children's children .

Thanks,
nithya

[New_Guy]

Hi,
Did you try filter(descendants(member,999,before)) instead of filter(children(member)) ?
Good luck
New guy

[cognos05]

Yes i did try that and that  gives the desired results . but when a slicer is applied on the nested row for example Agency ,

then the count always returns zero . Not sure what slicing on agency impacts the count expression .

Any help is appreciated.

Thanks,
Nithya

[New_Guy]

Hi,
Try nesting the account and see if any accounts really have sales > 0, if you haven't tried that. And shouldn't the tuple should have the account too.
Good luck
New guy


Sent from my iPhone using Tapatalk

[cognos05]

Hi all, this is from the ibm samples and the issue still exists when I use a slicer on country , the employee count expression doesnt work .

Hierarchy here is sales region,Country , Branch and employee . I have sales region and country nested in rows . 

Count(([Revenue]) within set filter(Descendants(currentMember([Cognos IBM Sample Cube].[Sales regions].[Sales regions]),[Cognos IBM Sample Cube].[Sales regions].[Sales regions].[Employee]),tuple(currentMember([Cognos IBM Sample Cube].[Products].[Products]),[YTD],[Revenue])>0))

When I add a slicer on country the Employee count doesnt  work .

There is a ticket with IBM , but I need report to be deilvered . Any alternate ways to get the Account count here . Why does it not work when slicer is added on country .

Any help is appreciated .

Thanks,
Nithya

[MFGF]

Hi all, this is from the ibm samples and the issue still exists when I use a slicer on country , the employee count expression doesnt work .

Hierarchy here is sales region,Country , Branch and employee . I have sales region and country nested in rows . 

Count(([Revenue]) within set filter(Descendants(currentMember([Cognos IBM Sample Cube].[Sales regions].[Sales regions]),[Cognos IBM Sample Cube].[Sales regions].[Sales regions].[Employee]),tuple(currentMember([Cognos IBM Sample Cube].[Products].[Products]),[YTD],[Revenue])>0))

When I add a slicer on country the Employee count doesnt  work .

There is a ticket with IBM , but I need report to be deilvered . Any alternate ways to get the Account count here . Why does it not work when slicer is added on country .

Any help is appreciated .

Thanks,
Nithya

Hi,

I'm curious. You told us:

...I have sales region and country nested in rows...

Then you said:

...When I add a slicer on country the Employee count doesn't work...

Can you explain? If you are showing country members in your rows, why are you trying to use a slicer? A slicer will change the context of the measures to be within one specific country, so most of your crosstab (apart from the one row for the one country your slicer includes) will show no measure values? It doesn't seem to make sense to do this? Can you explain what your slicer should achieve?

MF.

[cognos05]

Basically I will have a  prompt page with cascaded prompts on region and countries . So based on user selection either region or a country in a region we need to filter the query on this .say if i have 100 countries and if user wants to see specific country he is interested in , this filter will be applied and show only those results .

It makes sense why to show country if you filter by it , but is it a wrong design to have it shown . The slicer just acts as a filter on what user selects in prompts .

[cognos05]

Sorry I understood what I am doing wrong here . I think I should not use slicer , rather use my prompt values in the data item in rows .

Thanks MFGF , you always make me to think in a correct way .

Thanks,
Nithya

[cognos05]

Okay , now here is a another problem .

When I use my prompt expression in my data item I am having an issue . Say if I select a region but not a country , i need to show all countries under that region ,but thats not happening .

My Regions data item : Set(#prompt('prmRegion','mun','[K00 - KIT PACKERS],[001 - CENTRAL],[002 - MIDATLANTIC],[003 - NORTHEAST],[004 - PACIFIC],[005 - SOUTHCENTRAL],[006 - SOUTHEAST],[007 - MOUNTAINWEST],[008 - SOUTHATLANTIC]')#)

My COuntries Data Item : #prompt('prmCountry','mun','[All Regions]')#

If I dint select a country then it should show me all the countries with that region .

Thanks,
Nithya

[cognos05]

I almost made it to work , I am having issue with one use case.

COuntry expression : #prompt('prmCOuntry','mun','[All Countries]')#
All COuntries : Descendants(#prompt('prmRegion','mun','[001 - CENTRAL]','[008 - SOUTHATLANTIC]','[007 - MOUNTAINWEST]','[006 - SOUTHEAST]','[005 - SOUTHCENTRAL]','[004 - PACIFIC]','[003 - NORTHEAST]','[002 - MIDATLANTIC]')#,1)

All Regions : Set(#prompt('prmRegion','mun','[K00 - KIT PACKERS],[001 - CENTRAL],[002 - MIDATLANTIC],[003 - NORTHEAST],[004 - PACIFIC],[005 - SOUTHCENTRAL],[006 - SOUTHEAST],[007 - MOUNTAINWEST],[008 - SOUTHATLANTIC]')#)


Now the issue is only if I dont select any regions and I run the prompt with all regions , it doesnt show all countries , it shows countries only from central region .

I think my All COuntries expression needs to be tweaked .

Thanks all for your help .

[cognos05]

Okay I modified All COuntries expression to Descendants(#prompt('prmRegion','mun','[All Regions]')#,1) and everything works fine now .

Thanks MFGF for all your help on this !!