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
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
asked Feb 08 '12 at 02:51 PM in Default
@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/channel9.msdn.com/Blogs/rdoherty/Demo-Creating-an-Excel-2010-PivotTable-Using-a-SQL-Server-2008-R2-Analysis-Services-Cube
answered Feb 09 '12 at 05:13 AM
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..."
answered Feb 09 '12 at 10:56 AM
Great. Thanks Pavel and Sacred. One small question . Can i split my year in months into columns with slicer. LIke:
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
I am not getting option add calculated field or item. So alternative method will be appreciated.Thanks
answered Feb 10 '12 at 01:40 PM