If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Creating calculations using column totals

Started by Tsunami, 15 Sep 2010 02:37:17 PM

Previous topic - Next topic

Tsunami

I'm working on a Receivables Aging report and I have various columns that show if it's on time or over due(1-5 days, 6-15 days etc).  These columns have been totaled for each customer AND for the entire report.

I want to use the report totals to perform various calculations.  For example, Total Current Amount/ Total Balance Due.  How would I perform this calculation?  I would prefer to have a row below the Summary Total row that shows these calculations but can't seem to get the formating right.  Any suggestions/tips?  I've already got the calculations writtne out, I just don't know how to put it into the report.

Thanks for any help.

MFGF

Hi,

Assuming this is a List report, I'd suggest a query calculation - use syntax such as total([Current Amount] for report) /total ([Balance Due] for report)

Depending where you want them to be displayed, you could bring in a table or blocks from the toolbox, and drop the calculations into the relevant part.  If these are outside the List object, you will need to associate your List Query with the report to get these to display properly (or use Singletons, pointing the query back to the main List Query each time).

Regards,

MF.
Meep!

Tsunami

It is a list report.  However, I'm not sure how to format it correctly.  I can add a new table and add the calculations I need but how do I get the columns to line up properly?  I want them to line up with the column that they are referencing.

Lynn

You could add a row to the summary footer from the structure menu. Then put your query calculations in the appropriate spots.

This is assuming that the query calculations are in the same query that is attached to the list object. If they are coming from a separate query then it is really a pain to get two different containers to line up nicely. If that is the case, try using percentages for each column in the size and overflow property and set them the same for both containers.

Tsunami

I went to structure -> Headers/Footers -> Add footer and it said one already existed.  ???  I think the summary row is considered as a list report footer.

For what it's worth, I'm only using one query.

MFGF

If you already have a summary row, you could unlock the structure and drag a table with multiple rows into the existing footer.  Pop the existing footer items into the top row, and your new items into the subsequent row(s).

MF.
Meep!

Lynn

Try Structure > Headers/Footers > Insert List Row Cells Below. First be sure you have clicked somewhere within that footer on the layout. Otherwise the insert list row options will be greyed out.

You already have the footer so all you want is just another row within that footer to plop in some additional custom calculations.

Tsunami

Thanks MFGF and Lynn.  I was able to get the row added so I can put query calcuations in the individual cells.  However, I'm running into a different problem.

The summary totals aren't labeled differently from the subtotals.  When I run the report it functions correctly, however, the problem is when I try to create a query calculation using the summary totals.  Cognos doesn't know the difference so I think it's calculating improperly.