Post reply

OLAP cube output to Excel sheet template

  • July 30, 2007 at 12:30 am

    #117664

    Hi Everyone

    I need your help. I have manage to create olap tubes which work just fine. Now, my problem is when you extract some data out of it the user must copy and paste in excel and try to modify the rows and columns put titles and everything to produce the report they want. Here the reports are of the same format year in year out only the data varies.

    Therefore, my question is I want to design this reports as templates (in excel)and to connect the relevant data in the cubes directly to cells in  these templates so that we can produce the report on the fly.

    Is there any possibility for that?

    Thanks

  • July 30, 2007 at 1:13 pm

    #723397

    If you're using Excel 2003/XP (and I *think* 2000) then you can download and install the Excel OLAP Addin. This provides both 'strict' pivottable reporting (like the current Excel pivot tables) but also 'free format' reporting, where cells are true functions/equations, so they can be placed anywhere, titles can be added, formating applied etc etc.

    My understanding was that Office 2007 was to supply the same approach but as yet i haven't looked at any free-formated reports in that UI.

    Steve.

  • July 31, 2007 at 12:03 am

    #723471

    Thanks Steve!

    I will look into that. It sure seems what I want. But if I have a template already. Now, I have the annual abstract which is filled with statistics. I wanted to follow the format of each page of the abstract and link it to the cubes and the abstract it produced by a click.

    anyways,you are a great help and I will see with Excel OLAP Addin

  • July 31, 2007 at 12:04 am

    #723472

    Thanks Steve!

    I will look into that. It sure seems what I want. But if I have a template already. Now, I have the annual abstract which is filled with statistics. I wanted to follow the format of each page of the abstract and link it to the cubes and the abstract it produced by a click.

    anyways,you are a great help and I will see with Excel OLAP Addin

  • August 1, 2007 at 8:57 am

    #723900

    There's a number of approaches, which depends on the specifics of what you're doing. (Need more information). However, here's a couple of suggestions:

    1. Assuming that you simply want to put custom headings on top of the pivot table, you can hide the pivot table rows and simply enter your own headings above. Make sure you turn off the auto format for the table. The same is true if you're doing a lot of formatting using color, etc.
    2. Given that you really need to copy / paste the data, you can create a macro to do the necessary work of iterating through the pivot table, moving (or linking) the data as required. You'll have to pay attention to the specific range definitions you're using or building on.
    3. My preferred approach is to simply create the report in Report Services. It has the advantage of being less of an ad-hoc environment, where you can more closely control / define the user experience. Be prepared to create the MDX manually though, since the wizard is unsuitable for any but the simplest applications.

    Option 3 is by far the best approach, since you have good control over headers and footers, it's integrated with VS, it's browser enabled, and you can control access via the security model. Of course, there's other fine OLAP tools in the market, but I'm assuming your options are limited.

  • August 1, 2007 at 9:05 am

    #723905

    Dave's spot on re:using Reporting Services. I would add the following points too: i) once produced in RS, the user can export to Excel (if still required) or pdf easily; ii) if you're using RS2005 the click-n-drag MDX query generator isn't too bad, and it allows for the creation of calc membrs with little effort.

     

    Steve.

  • August 2, 2007 at 12:02 am

    #724069

    Thanks! I will try the options you gave me.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply