TM1 Forum

Hi Guys,

For our end of month P&L reporting we create a large number of reports with different variations for many different users. We currently maintain these via spreadsheets in Perspectives. Each spreadsheet workbook contains many sheets for all of the different variations of the P&L. They are basically views but with customised formulas,etc added. I have created a number of VBA scripts that update the customer listings and project listings with the most up to date elements. However it is very time consuming to have to update each sheet at the end of each month and then send out to each of the staff members. I was wondering if there are any tools out there to make this a little more automated. I have been thinking of programming something that would allow for these spreadsheets to be stored in a database and automatically published and bursted at the end of each month.. But it will be a lot of work and I don't want to reinvent the wheel if somebody already has a tool or process that makes this easier.

Basically the tool would need to take a report definition and list of recipients and help to automate the process of generating each of the reports and bursting to the appropriate users.

Any ideas/thoughts?

Damien

If your different reports are essentially different views, why do you have a separate Excel sheet for each user. Why not put logic in the Excel sheet that dynamically picks the correct subsets for each dimensjon, based on user, so that you can have just one template? You can build a two-dmensional lookup cube using the }Clients dimension to store the different parameters you need by user in order to make the appropriate selections in the report. This is how I would do it. Separate Excel templates would be my LAST resort. Also, stay away from VBA. Once you've gone down this road you can't use TM1 Web. Even if your users currently all use Excel, what happens when you want to add a whole class of new users that are in a different location, making Excel not an option?

Thats for the suggestion, however unfortunately for us we have different views depending on the type of person that is viewing the report, there are reports showing P&L by project and others that show P&L by customer and others that show a period comparison, etc. We have so many different combinations. Some users will even have reports that would contain multiple views for example GBU comparison per country. There are also columns including special formulas etc. Apart from programming up a tool, I don't see any other tools out there that would achieve this unfortunately. If anybody has any ideas on how this could be achieved without building a tool. I am not sure if Cognos 10 would provide a way around this.. If the report builder would allow the creation of Reports based on the TM1 cube data and allow us to structure / format the report layout in a custom way and then burst them out. Any ideas on this?

Hi Damientaylorcreata,

I'm facing the same objective today and would like to know if you get answer to your question. if yes what have you implement?

Thanks.
Benoît.

Do you own Cognos BI? If so then I would recommend looking there for complex reports that need to be emailed, bursted, prompted, multiple formats, etc.

In the BI realm TM1 is just a datasource and Report Studio should allow you to do everything you need. If needed it is even possible to have pass through security so your data is filtered for users if they run the reports live from the BI Portal. Otherwise you would provide the "burst" data as an attribute or data of a cube so that reports could be auto filtered and sent through email to end users.

PlanningDev wrote:Do you own Cognos BI? If so then I would recommend looking there for complex reports that need to be emailed, bursted, prompted, multiple formats, etc.

In the BI realm TM1 is just a datasource and Report Studio should allow you to do everything you need. If needed it is even possible to have pass through security so your data is filtered for users if they run the reports live from the BI Portal. Otherwise you would provide the "burst" data as an attribute or data of a cube so that reports could be auto filtered and sent through email to end users.

While I know BI does a really good job of report bursting, even it cannot do what the original poster wanted done. In essence he had one "report" that was actually multiple reports such that each tab in the worksheet had a customized version of the report and he assigned those different tabs, or reports, to individual users. What he was looking for was to have one "report" where the content and layout could be different for each user, and that difference was more than just which elements were being filtered in the dimensions. I'm pretty sure BI can't do that. In BI, if the reports are different for each user, then each variation would have to be a separate report.

Technically BI can do that. You can use session parameters to determine the user if necessary and then modify what objects are shown on the report. This would in effect allow you to have one report that looked different based on the user looking at it.

Generally you can use these from right within report studio but you can also augment these using macro's inside framework manager and provide even more data to report studio.

You could use an executable called from a TI Process to schedule excel reports to run (and email). One of our consultants has done this for a client. I'll try get him to document it sometime next week and put it on the blog.

Hi Damien:

Since it seems very relevant to the topic at hand, I did want to take a minute to direct you to our FrameWORQ products page on the Commercial forum, specifically ReportWORQ which is designed to help simplify the job of TM1 Excel report automation and distribution, including support for more dynamic forms of reporting in Excel like Active Forms.

Please don't make inquiries within this thread - they will be deleted per forum policies. Please send a PM or use the contact information on the page linked above.

Regards,
Mike