Footers 1

[UcfMike]

I apologize for all the questions but so far the migration from Impromptu to Report Studio isn't going smoothly.

I've added a list footer to my report with the goal of summarizing data for that group. However whenever I add fields I don't get the results I want. I either get totals for the whole report, or the last value from the group that the list footer is based off. What am I missing. In Impromput I just created a new query calculation that totaled the field that I wanted it and associated it to the proper group.

 

[blom0344]

Well, I am just a novice like you in report studio, but the proper way is to select the non-fact columns and then use the group icon. If you then use the aggregate icon on the fact, then the proper subtotals are shown in the result.

Ties Blom

 

[mfgf]

Hi Mike,

Do you want a footer at the end of each group, or at the end of the report? (or both)?

The easiest way to do this is to select the column you want to summarize, and use the Aggregate button on the toolbar (looks like a yellow sigma). This will add footers for each grouping level and for the entire report.

If you want to perform this process manually and take more control over the summaries, add the required footer(s) (as you have already done), then drag a query calculation from the toolbox into the footer. Code your syntax as follows: Total([Measure Item] for [Grouped Item]) or alternatively: Total ([Measure Item] for Report) if you want an overall summary.

Best regards,

MF.

 

[blom0344]

Actually , it looks as though Cognos creates n-1 subtotals if I group on n columns.

The column directly to the left of the fact does not receive a break (sorry, another BO phrase) subtotal..

Ties Blom

 

[mfgf]

Hi,

This is as it should be, as the measure you are seeing is already being aggregated for its immediately preceding non-measure item by virtue of the default aggregation performed within the report.

Regards,

MF.

 

[blom0344]

MF,
yes obviously.. Need more coffee

Ties Blom

 

[mfgf]

Yup - I have days like that too (most days, in fact! )

MF.

 

[blom0344]

Well,

adding the query calculation IN the footer seems to be a no-no. I keep getting error messages about:

'The insertion location does not have an associated query'?

Ties Blom

 

[mfgf]

Hi,

What kind of footer are you trying to drop the calculation into? If it's the page footer, it will not be associated with the list's query by default. If it's a list group footer, list overall footer or list page footer (all available from the List Headers & Footers button), you should be fine.

MF.

 

[blom0344]

I have a list page footer under the list column body, but I cannot drag a query calculation into this cell.

Same error message again...

Ties Blom

 

[mfgf]

Hi,

Did you unlock the structure (padlock button) before you tried to drag in the calculation? If not, it will be dropped outside the list in the page body, and produce the error you are getting.

MF.

 

[blom0344]

Yep sorry, that was in fasttrack.
I'm going to read through the training services manuals once again.

Many thanks for all answers!

Ties Blom

 

[UcfMike]

I've tried several different things, none that has yet to work properly.

I added the group footer from the structure menu. Next I added the field I wanted in the footer from the query and set my rollup aggregation to total for that field. That totaled the field for the entire report.

Next I tried a query calculation which totaled the field I wanted and set the grouping to "for store." So it was total([PRICE]for[STORE]). That didn't work either.

I've tried my Rollup Aggregate Function everyway possible and I'm still not getting the results I should.

I'm starting to wonder if I've set my Aggregation wrong in Framework Manager.

 

[mfgf]

Hi,

Can you try the following:

Add a new query calculation to your list as a list column. For the formula, use Total([Price] for [Store])

What results does this give you when used in a list column?

MF.

 

[UcfMike]

That produces a very wrong number as well, and also two of my other columns no longer produce values when that column is inserted. Looks like I may have bigger problems.

 

[mfgf]

Hi,

Can you try creating a new list report with just the three or four required items in it to see if something else you did earlier on in the current report has caused these problems to occur? You should be able to find out fairly quickly if that works OK...

Regards,

MF.

 

[blom0344]

I have tried the same exercise.
If I add the query calculation to the footer I get the value of the last one in the report , but when I add the calculation as a list column then the data is correctly shown.

In the properties the aggregate and rollup aggregate are set at 'automatic'

Ties Blom

 

[mfgf]

Hi,

I would expect this if your calculation is totalling a measure for a grouped item. If you include it in the List Page Footer, it will give you the total for the last group. If you include it in the group footer for the relevant grouped item, it will work correctly.

MF.

 

[UcfMike]

I tried the new report idea and it's still producing the wrong numbers. Could I have a setting wrong in my package from FM?

 

[mfgf]

Hi Mike,

It sounds likely. What does the Cognos SQL query for the new report look like?

MF.