querying items for subtotal and totalling - all in one column

[ry1633]

Hello,

In Report Studio, I have a slightly tricky problem (for me anyway...) that I need help with.  I have a one column report that I need to query out any records containing a specific word, let's call them "Descriptor1" and "Descriptor2",  and subtotal those records in a (sub)footer.

Then after that, I need to subtract those records I found from the overall total, and put that total number into the bottom footer.  All in a one-column report.  :)

Any help is very appreciated. 

[BigChris]

Depending what sort of output you need I'd probably create another column called Category and group on that:

If[Field] = 'Descriptor1' or [Field] = 'Descriptor2') then ('Section1') else ('Section2')

[ry1633]

Chris,

can you unpack that a bit more for me?  I'm really new to Cognos so I don't understand the syntax well yet.   What I want to do is

extract the total of "Descriptor1" and "Descriptor2" from a column - and put that in the subfooter

and then subtract that subtotal of items from the total of all items in that column - and put that in the bottom footer?

sorry if I wasn't clear, and many thanks for your help -ryan

[BigChris]

Hi Ryan,

I'll do my best. In your query at the moment I'm assuming you've got a field which has "Descriptor1', "Descriptor2", 'Descriptor3" etc. in it...for the sake of the example I'll call that field [Description]. When you pull that field into your query from your package I'm guessing it'll look something like [Presentation Layer].

.[Description]. In your query you could now drag in a Data Item, and in that you could put the expression: if([Description] contains 'Descriptor1' or [Description] contains 'Descriptor2') then ('Category1') else ('Category2') On your page you could then have your two columns, Category and Description. You can group on the Category column. Then click on the Description column and go to the aggregation button and select Count. That should produce a subfooter for each category and an overall footer.

[ry1633]

Thanks for that; I can try those.    Maybe put those into a new row:   how do I add new row or column?   Can't seem to find that in the menus, and when I try to add a new footer it won't let me.