COGNOiSe.com - The IBM Cognos Community

Hi ,

I have a hierarchy like Region->Distributor->Reps->Accounts.

I have my distributor in row and have nested the respective account level to it.

Account Data Item - Expression -? filter( Accounts level ,Accounts that had sales in last 2 years) and out of these accounts I will add one more filter to get the accounts that dint had any sale in last 6 months


This works fine, but I have to show my distributor only if there are any accounts under them.

So now what happens, since there is a distributor level in front of accounts, it shows the distributor eventhough there are no accounts under it.

So how do I write a expression on the distributor data item to show only if it has children, i.e Accounts.

Thanks,
Nithya

Quote from: nithya1224 on 14 May 2015 09:04:13 AM
Hi ,

I have a hierarchy like Region->Distributor->Reps->Accounts.

I have my distributor in row and have nested the respective account level to it.

Account Data Item - Expression -? filter( Accounts level ,Accounts that had sales in last 2 years) and out of these accounts I will add one more filter to get the accounts that dint had any sale in last 6 months


This works fine, but I have to show my distributor only if there are any accounts under them.

So now what happens, since there is a distributor level in front of accounts, it shows the distributor eventhough there are no accounts under it.

So how do I write a expression on the distributor data item to show only if it has children, i.e Accounts.

Thanks,
Nithya

I'd suggest another filter function - this one filtering the Distributors level based on aggregate(currentMeasure within set [your accounts expression]) > 0

Cheers!

MF.

Hi MFGF,

I am still getting the distributor that doesnt have Accounts displayed in my crosstab.

Assume  my current distributor levels expression is.

filter( distributorlevel, [Qty]>0)

Now you ask me to add another filter here

filter(filter( distributorlevel, [Qty]>0), aggregate(currentMeasure within set [your accounts expression]) > 0)

but this is still bringing in my distributor which doesnt has accounts .

so my accounts expression will give all the accounts based on my account filter condition, how will my distributor filter know the accounts specific to that distributor  and then do a aggregate? I think here its just calaculating the accounts of other distributor also.

Thanks,
Nithya

Hi,

Say my first filter gives 5 distirbutor , now out of these distributors for one distributor my account data item doesnt bring any accounts, but still I am getting the distibutor shown up.
Can anyone let me know if my expression below used will work.

Thanks,
Nithya

Guys, this issue is not still resolved. Any inputs are appreciated!!

Thanks,
Nithya