Report Studio Group Totals

[UcfMike]

I'm trying to create a calculated group total in a list footer. In my filter, I've limited the numbers that the user is seeing. This works great, but when I try to create totals for the report, the report is generating totals for the underlying data, not the data that appears to the user.

For Instance if this is the data
AA 2
AB 3
AC 8
BB 1
BZ 4

and I'll filter the data for only records whose last character is B. So I'll get

AB 3
BB 1

When I try to calculate totals I would want to get the (3 + 1) = 4, but instead I'm getting (2 + 3 + 8 + 1 + 4) = 18.

Just for a twist, If I click on the column and choose Summary total, it calculates correctly but if I use the total function, it calculates incorrectly.

Thanks for the help.

 

[blom0344]

What type of filter did you define? Cognos distinguishes 2 types. One against individual records and a second one on the resultset..

Ties Blom

 

[UcfMike]

I tried before auto aggreagation and after auto aggregation. I also tried the filter in the summary.

Yesterday I got it to work, but I can't really figure out why.

When I originally created the totals, I added a footer to the section. Yesterday, I let Cognos add the footer when I let it do a total summary. I then replaced its summary with my calculation, and the whole thing worked.

Not sure why.

 

[blom0344]

If you add an before auto-aggregation filter, then the filter should affect the where clause of the database query.
In that case it will only fetch records where last character is B (and only corresponding facts) That should pretty much prevent what you are witnessing.

Did you check the SQL generated?

Ties Blom

 

[UcfMike]

I'm going to have to recheck it. I just updated the package, a change that shouldn't affect this report, and my numbers are off again.

 

[CognosProfessional]

You shouldn't have to worry about the package being updated for a report total. In the report studio, there are so many options for the properties of totals. That is the beauty of it. Check out your totals properties, and change them to see different results and what you are looking for.

CP