filtering Nested row in Dim Reporting

[cognos05]

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

[MFGF]

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.

[cognos05]

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

[cognos05]

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

[cognos05]

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

Thanks,
Nithya