Can I "pre-build" excel formulas into Report Studio extracts?

[svcognos]

Can I "pre-build" excel formulas into Report Studio extracts?

In an effort to save time and formatting, and being a newbie to Cognos and Report Studio, can I build out excel formulas into columns I add to the extract? This way, when it is emailed to me or others in an excel format that the extra columns and data won't need to be manipulated?

For example: I have about 4-5 extra columns with formulas such as:

[column F]=IF(E2>=1,"Failure", "Success")

[column J]=WEEKNUM(I2)
[column K]=Table1[[#This Row],[Scheduled Start Date]]-WEEKDAY(Table1[[#This Row],[Scheduled Start Date]],1)+3
[column L]=Table1[[#This Row],[Scheduled Start Date]]-WEEKDAY(Table1[[#This Row],[Scheduled Start Date]],1)+9
[column M]=TEXT(Table1[[#This Row],[Week Start]], "mm/dd/yy") & " - " & TEXT(Table1[[#This Row],[Week End]], "mm/dd/yyy")

Thanks!

[tjohnson3050]

No, you will need to build the formula logic in an expression in Cognos.

[Lynn]

I was able to do this in a crude sort of fashion....Only works nicely in Excel 2002 format (not 2007) and assumes a static layout for the rows which means it isn't terribly dynamic.

http://www.cognoise.com/community/index.php/topic,12987.msg56196.html#msg56196

[CognosPaul]

It sounds like you're using Cognos as an intermediate step for getting the information you need. This is understandable if you're just getting used to it, but you may want to review how your organization is using Cognos. If you, and presumably other people, are doing your own analyses on exports from Cognos and using those as official numbers, there is far more chance of error. You may already be in Excel Hell. I've been there, it's not fun.

Ultimately you should be able to get the data you need with a single click of the button. Tools like Business Insight, Query Studio and Analysis Studio are available for interactive analyses and ad-hoc reporting.

[Lynn]

I totally agree with Paul, as usual. In my case the client does not have a data warehouse and some operational systems prohibit direct connection for reporting purposes (by client policy). Even though Cognos can technically connect to multiple sources and put the information together in a report, it was not permitted.

The intent in my situation was to provide the information that *could* be retrieved through Cognos with an interface where other data could be supplemented for specific reporting purposes. Truly a poor-man's approach  :-[

[CognosPaul]

The idea of putting Cognos, or any reporting system, on an operational system is completely antithetical to everything good and right in the world. So kudos on them for disallowing a direct connection. But my mind is blown at the idea of them not having a separate database to handle reports. Even mirroring a database in order to provide something until the DWH is finished is more reasonable than working with Excel exports.

Although, now that I'm thinking of it, at the very worst you could build a transformer model on a few Excel spreadsheets, or even connect to them directly via ODBC.

[Lynn]

The idea of putting Cognos, or any reporting system, on an operational system is completely antithetical to everything good and right in the world.

AMEN! There are plans for a new global data warehouse and it is already in the works for a different division. The division I am consulting for has not yet begun, so I am doing all sorts of things in the name of "interim" reporting  :'(

[cognostechie]

I completely agree that Cognos should be used for reporting and analytics instead of using it as a data extraction tool. However, from my experience, I have seen that apart from some users used to EXcel, some companies also have such an un-user friendly and clumsy Framework Models that the users just can't understand how to make their reports themselves so they tell the IT guys to give them the data in Excel. This problem is not uncommon and I think quite a substantial percentage of companies have this problem.

I fixed two such unsuccessfull implementations into fully successfull ones and the the same business users now love to use Cognos for reporting and analytics.