Excel Formulas in Cognos Output

[crawfoam]

I've been searching around and cannot find a clear answer to the issue I am having. I would like to run a report from Cognos in Excel 2002 or 2007 (no preference) and I would like the totals from the report be formulas in Excel. The reason is, we do manipulation to some of the numbers after output, and we have to re-total each column manually. If it is possible to automatically have the formula totals included, that would be great! Please let me know if this is possible. Thanks!

[bdbits]

I do not think that is possible using out-of-the-box functionality.

If the output is consistent enough, an Excel macro could be written to run after-the-fact and put in the formulas.

[Lynn]

You can do it using Rich Text Items plus a few other little tricks to get the desired data format. Attached is a report spec illustrating the technique (although I use that term loosely...the label 'kludged hack' might be a better label). It is done in 10.2 against the Cognos sample data.

If you run to Excel 2002 you should see the result of each formula you created from your rich text items upon opening the file. In 2007 you see the formula when you first open it rather than the result and would need to double click into each cell and then click elsewhere to get the value. Obviously 2007 is not the ideal solution since that can be horribly tedious. I suppose 2002 comes with its own set of issues, but perhaps the lesser of the evils in this situation.

You can use report expressions or data item values as the source for your Rich Text Items. This might be necessary to figure out cell references. If your rows and columns are always fixed then you could use text as the source and just type the formula in directly.

If you run the attached spec you'll see column A shows product line, B shows planned revenue, C shows revenue, D shows a Cognos calculation for Planned revenue minus revenue, and column E shows the same calculation, but done with Excel formulas via a Rich Text Item. Here I show how to use a report expression as the source for the formula. On row two of the output you'll see the formula is =B2-C2 --- I construct that formula using a RowNumber() function in the report spec so each row gets the correct formula.

There is also an example of a summary row formula on line 7 of the output. I summarize planned revenue (column B) using Cognos so you only see the resulting figure, but on column C there is an excel formula that I created using a data item value as the source for my rich text item. I needed to compute the maximum number of rows (6) in order to produce the formula =SUM(C2:C6) in a data item specifically intended as the source for column C total. I'd need additional data items in order to produce formulas for other columns.

Obviously the consistency of your layout is important to understand so you can properly construct formulas with the correct cell references. I don't really know how different versions of Excel that people have installed out there in the world may affect the output, but I did this several years ago against an older version of Cognos with an older version of Excel installed and I get the same results today with the latest Cognos version and Excel 2013 installed on my laptop.

As for the little tricks I mentioned .... if you need to apply a data format to your formula result you'll find that formats are ignored if the only thing in the cell is the rich text item. To get around this you can create a phony data item in your query. Mine just has the number zero hard coded in it. Then I set an advanced conditional style on the fake item to make it never display. The mere presence of this data item, even though it never displays, is enough to make Cognos apply the data format to the cell.

Kinda wacky, huh?

[crawfoam]

Thank you so much! It looks like I have a lot of things to try and play around with. I appreciate greatly the detail put into your answer. You answered my question as well as the follow up questions I would have had.

I hope to give it a shot this week , and I'll report back if I have any issues/questions. Thanks again!