COGNOiSe.com - The IBM Cognos Community

Hi All,

I have a report with the following fields Group, Qty. In the query subject for each group I have multiple item and products. I only want to display distinct groups and show total Qty for all items within that group (I do not want to include duplicate records that might arise because of mupltiple products associated with an item). e.g. For group G, there are items It_1 with Qty_1 and It_2 with Qty_2. There might be multiple products P1, P2, P3 for say It_1 with the same Qty_1. All I want to see on the report is Group "G" and Qty as "Qty_1+Qty_2"

I tried different kinds of groupings/aggregations but doesn't seem to work. Total expression may work I believe but I can't figure out how to tell it to look for distinct items for a group. I tried total(Qty for Group) but that doesn't work.

I would appreciate any suggestions/comments.

Thanks,
H

total(distinct ([qty] for grp) should work, even though if prod. is a level of Grp, then totals, as you explained dosen't quite make sense but anyway

Grp         Items           Prod            Qty

  G         It_1             P1               1
  G         It_1             P2               1
  G         It_2             P3               2
  G         It_2             P4               1

--------------------------------------------------
G                                            3


if you put a calculation at the group footer under qty with expression

total(distinct([qty] for Item,Grp)

u should get

G                    3

is that what u want ??

hope it helps..

Thanks a lot for your reply Mallika,

I indeed want to see "G    3" on the report as you thought, but not in the group footer, rather in the actual row itself. I am not displaying item or product information. Also, I tried the formula you suggested for the total and I am getting some parsing error. I have been trying different things to get the formula to work but no luck so far.

Please let me know if you have any ideas how to make that formula work.

Thanks once again,
Himanshu

Hi Mallika,

I played with the syntax and I finally got it to work. Turns out that I didn't need a few brackets I was using in the expression.

This is what I used finally for the expression itself (not for the group footer):
total(distinct[Quantity]for[Item],[Group])

Now I am getting the information as I wanted it to.

Thanks for your help. This topic can be considered solved

Himanshu