Dynamic Excel report 2010 against MS analysis services Cube using parameters

I have to create dynamic excel 2010 report.

Data will come from Cube. there will be a parameter : YEAR

Based on that select year, data will be presented. Should be look like this.

Select Year : 2000- 2012 (any one of them) Refresh Button

  **Account  Name  Amount   Address   Country**




User can select any year and just click on refresh button he should get related year data.

I am new with excel reporting , please help me in this. How will I proceed. How to write query against cube to get that table. I have to select top 25 account based on ammount.


more ▼

asked Feb 08, 2012 at 02:51 PM in Default

avatar image

17 3 3 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

@Ritesh9255 Simple answer to your question is PIVOT TABLE in Excel 2010. You would enjoy the power of PIVOT TABLE as it has many more functionalities than you are currently looking for. Since this is hard to explain in one go, please follow the following hyperlinks, which includes a demo by Peter Myers



more ▼

answered Feb 09, 2012 at 05:13 AM

avatar image

Sacred Jewel
1.7k 3 7 10

(comments are locked)
10|1200 characters needed characters left

As @Sacred Jewel mentioned, go though the demo of creating Pivot Tables using the Analysis Services.

Then you do not need any parameters for such purpose. You simply put the Year Attribute of the Date Dimension in to the Report Filer part of the Pivot table an deveryting will work as expected. Then you simply select values you want in the Report Filter and the data will be automatically fitered instantly without any special Button.

Other possibility is to use a Slicer (new feature of Excel 2010) - you will find it on the Insert Tab.

Again you set the slicer that it will use the Year attribute of the Date Dimensions and by simple click (selction) in the slicer, data will be filtered instantly.

The biggest income of the slicer is, that it can be used by multiple pivot tables and so if you have multiple reports from the same cube, all can be filtered by single slicer and you do not neet to set the filter foe each report separatelly.

If yo a aready have pivot tables and insert the slicer afterwards, you can connect it to other pivot tables by Right-Clicking on it and selecting "PivotTable Connections..."

more ▼

answered Feb 09, 2012 at 10:56 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Be careful with the slicer and any custom grouping added to the pivot table. When users do this, the subcube vector sent to analysis services will add another value that does not reflect the actual cube structure. Since the vector will not match the cube exactly any aggregations you have built will not be used.

Feb 10, 2012 at 09:57 PM Scot Hauder

@Scot Hauder, maybe depends how the Pivot is designed in overall, but when tested on my Pivot Tables, the Slicer produces exactly the same MDX queries as if the filter was applied directly on the Pivot Table. Checked by profiler and the queries are exacctly the same.

Feb 13, 2012 at 07:23 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Great. Thanks Pavel and Sacred. One small question . Can i split my year in months into columns with slicer. LIke:

 year : 2011
 data      jan     feb     mar .......   dec   grand total
 -------------------------------------------- --  
 trim        10       20       40 .......... 50   120
more ▼

answered Feb 09, 2012 at 02:28 PM

avatar image

17 3 3 7

This you wil not achieve by slicer, but by correct design of the Pivot Table Itself. Slicer allows you to filter Data (Slicing over the cube). Then you need put correctly attributes of dimensions into the Pivot Table.

Feb 09, 2012 at 02:57 PM Pavel Pawlowski

Thanks Pavel. I got it. Thanks a lot!!

Feb 09, 2012 at 04:08 PM Ritesh9255
(comments are locked)
10|1200 characters needed characters left

A quick question to all excel 2010 experts. I am using pivot table. Now I have to add one custom column(percentage) using existing columns
like a b c d are columns and custom column is e(c/d * 100). They are not base columns.They are value column. Now how to add this custom column.

I am not getting option add calculated field or item. So alternative method will be appreciated.


more ▼

answered Feb 10, 2012 at 01:40 PM

avatar image

17 3 3 7

If it is classical Pivot table (not connected to a SSAS Cube), then in the Options Tab of the Pivot Table Tools, then Under ther "Fields, Items & Sets" button you will find "Calculated Field" where you cna put your own calculation.

In case of pivot table connected to the SSAS Cube, you cannot add the calculation directly into the pivot table. In that case you have several possibilities

  1. Create calculated member directly in the cube and use it in excel.

  2. When you finish ediding the pivot table, convert it into formulas (Options Tab, OLAP Tools, Convert To Formulas), then add calculated field using the CUBEVALUE funtion. However this will not be dynamic.

  3. If you your calculation should simply use the value which is aggregated in the pivot table, you can put the field ouside the pivot table and reference the pivot table value in the calculation. howere this will not be dynamic.

  4. You can use OLAP PivotTable Extensions (The easiest for OLAP Pivot Tables)

  5. Write custome qury in VBA

Feb 10, 2012 at 02:37 PM Pavel Pawlowski

Thanks Pavel .I willl work on that and let you know if I stuck somewhere.Thanks again

Feb 10, 2012 at 08:31 PM Ritesh9255
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 08, 2012 at 02:51 PM

Seen: 3468 times

Last Updated: Feb 13, 2012 at 07:24 AM

Copyright 2016 Redgate Software. Privacy Policy