x

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**

1

2

3

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.

Thanks
more ▼

asked Feb 08 '12 at 02:51 PM in Default

Ritesh9255 gravatar image

Ritesh9255
17 3 3 5

(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

http://blogs.office.com/b/microsoft-excel/archive/2009/10/12/excel-2010-pivottable-what-if-analysis.aspx

http://channel9.msdn.com/Blogs/rdoherty/Demo-Creating-an-Excel-2010-PivotTable-Using-a-SQL-Server-2008-R2-Analysis-Services-Cube
more ▼

answered Feb 09 '12 at 05:13 AM

Sacred Jewel gravatar image

Sacred Jewel
1.6k 2 4 5

(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 '12 at 10:56 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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 '12 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 '12 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

cut  

laminate

----------------------------------
more ▼

answered Feb 09 '12 at 02:28 PM

Ritesh9255 gravatar image

Ritesh9255
17 3 3 5

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 '12 at 02:57 PM Pavel Pawlowski
Thanks Pavel. I got it. Thanks a lot!!
Feb 09 '12 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.

Thanks
more ▼

answered Feb 10 '12 at 01:40 PM

Ritesh9255 gravatar image

Ritesh9255
17 3 3 5

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][1] (The easiest for OLAP Pivot Tables)
  5. Write custome qury in VBA
[1]: http://olappivottableextend.codeplex.com/
Feb 10 '12 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x672
x535
x166
x108

asked: Feb 08 '12 at 02:51 PM

Seen: 2388 times

Last Updated: Feb 13 '12 at 07:24 AM