Sorting parent based on childrens total sales

[cognos05]

Hi ,

I have an issue in sorting in dimensional reporting.

Reps and Accounts nested in rows.

A rep might have N accounts and columns showing total sales on each account for few products.

I would like to sort my reps first based on the highest sales of accounts within them.


I am using this expression on my rep data item as

order(Reps, Total,Desc).  Where total is dataitem with expression total(currentMeasure within set([A],);

I am not getting any error,but my reps are not sorted based on their childrens ie Accounts sales total.

Any help is appreciated.

Thanks,
Nithya

[cognos05]

Hi,

I was able to get in to the issue.

my sorting should have a condition tuple( Qty, [time])
where [time] would return a set .
I cannot use a set inside a tuple, so I had done this below.

order(reps,aggregate(qty within set ([time])Desc).
I have products shown using prompts on the report which I dont include in the above data item.May be this is causing the issue.

If I need to include it ,how should my expression be.

Thanks,
Nithya

[MFGF]

Hi,

I was able to get in to the issue.

my sorting should have a condition tuple( Qty, [time])
where [time] would return a set .
I cannot use a set inside a tuple, so I had done this below.

order(reps,aggregate(qty within set ([time])Desc).
I have products shown using prompts on the report which I dont include in the above data item.May be this is causing the issue.

If I need to include it ,how should my expression be.

Thanks,
Nithya

Is your prompt returning a single product member or a set of product members?

MF.

[cognos05]

No it was taking products in to consideration.My mistake,  I just figured out whats happening.

I have Rep and he has accounts nested.

Now the expression on rep was order(reps,aggregate(qty within set ([time]),Desc). . This sorts perfectly.
Next expression on the nested account.  I am doing a filter on this guy to show only the inactive accounts.
Expression on Account data item is filter(Accounts,"Only InActive")

so logically it works correct. but is there a way for me to say sort the reps based on desc qty but do only include their childrens whos accounts are inactive.

Thanks,
Nithya