Excel Output with Formulas

[Lynn]

I need to produce a report rendered to Excel that (for various reasons) requires formulas in some situations rather than just the value result of a calculation.

Formulas contained in a rich text item work beautifully, but only rendered as Excel 2002. If I render to 2007 using the same technique, the formula works only if I first click into the cell (as if I'm going to edit it) and then it seems to understand it as a formula and shows the result.

I would guess this difference is somehow due to mht vs. native excel rendering, but even if I'm right that doesn't help me any further. I would prefer to render to Excel 2007 so that when I open it up the formulas show the calculation result just as they do in 2002.

My users would hang me from the flagpole out front if I try to tell them "just click into and then out of every formula cell and you're good to go." I am hopeful that telling them to open and then "save as" Excel 2007 format would only subject me to an occasional menacing glance, but a true 2007 solution would spare me even that (at least for this reason anyway).

I'd be happy to hear anyone's ideas.

[melee]

I think it's that crazy XLSX container. It must be messing with the XML that is outputted. You can take a look at the XML file itself using 7-zip, but I'm not sure it will help :(

[Lynn]

Thanks melee. I'm not sure I'd even know what I was looking at if I managed to do that!

I read your post quickly the first time and my mind saw 7-Up instead of 7-zip so I thought "yeah, not sure that will help either!" :)

[Lynn]

I gave up on 2007....

As a side note, I found that trying to apply a number format for the excel formulas didn't come through when run to Excel which sort of makes sense, assuming Cognos realizes the content contains text and not a number so ignores it.

I found a silly way around this. I created a bogus data item that with an expression that is just the number 0. I unlocked the report and placed this next to the rich text item and applied the appropriate data format. Then I created an advanced conditional style for the bogus item. It is always true (1=1) and has the Visible property set to No. The only thing that comes through on Excel 2002 is the formula with the format applied.

A total hack job, I realize, but sometimes we just gotta do what we just gotta do....

[aruntechnocrat]

Hi Lynn,

Can u please explain in brief how you incorporated formulas through cognos for excel outputs.

Thanks,
Arun

[Lynn]

I was able to make it work when rending to Excel 2002. Put a rich text item from the toolbox in your list and enter the formula there. If it is something static you can use text as the source type. For example: =IF(B4=0,0,B3/B4)

The above assumes there is a static display of rows such that the content of rows 3 and 4 always means the same thing, such as revenue in row 3 and business plan in row 4...computation shows the variance % from plan.

If the formula needs to be derived based on other data in your query then you will have to build a query item and use data item value as the source type.

The workaround I noted in my earlier post was just a way to force a data format on the cell for number of decimal places, etc. and not strictly necessary.

[aruntechnocrat]

Thanks Lynn,

Now i am facing another problem, the formula doesn't get populated for the subtotals and totals it remains blank and the the row id gets resumed so the fomula gets changed for the next row after the totals and subtotals row...hereby i am attaching the screenshot of my error. If any one can able to figure it out please let me know

[Lynn]

I'm not clear on how you are deriving your formulas. Are they being built as part of your query using a row id function? You may need separate query calculations to figure out the sub-total and total formulas if that is the case. Then unlock the layout and put them explicitly where they need to go.

You may need a query reference to get at the row number information to compute what you need for the summaries.