Sharepoint Excel Services: Setting up report parameters
// February 25th, 2008 // Sharepoint
Over the past few days ive been exploring the possibility of using Excel Services to solve some of the reporting problems for a IT management portal which would expose charts and data on:
1. Planned Downtime
2. Unplanned Downtime
3. Cumulative Downtime (Y2D)
4. Percentage Downtime
and all the other great things CIO’s like to see. Presently the data and reports are distributed ala ’6 versions of Excel document emailed around’ and updated by one person. We needed to change this.
Excel Services has on many sites, Microsoft ones especially, been talked up a fair bit. Rightly so. The possibilities are endless when one allows an Excel spreadsheet to be exposed almost in an ‘iframe’ way, where users can interact with it in a read only way on the reporting dashboard.
Another handy feature (when i finally worked out how to use it) is the ability to pass parameters to pivot-tables within the Excel workbook and thus generate range specific reports and charts. I found this feature especially handy since it opens up the doors for vastly different dashboards. Setting this up however can be a mission. Firstly generate the sample dashboard so you can see how they did it, it really helps to see how Microsoft achieved this before setting out to do it.
I battled to find anything which stepped it out on the net, so heres my recollection on how to create charts where different date parameters can be passed to generate different reports:
1. You HAVE to have Excel 2007
2. Open up your datasheet and insert a pivot chart by selecting the data you wish to chart
3. In your Pivot-table Field List (pops up once you select your data) select the fields you wish to report on.
4. Choose a field which will be your ‘Report Filter’
5. This will generate a cell on your worksheet where you will be able to filter the field values you just chose.
6. Click on this cell and name it (so if the cell is A2, you will see the A2 name in the far left hand drop down: change it)
7. When you are ready, publish the document by clicking the Office logo and choosing Publish
8. Click the ‘Excel Services options’ button before you publish
9. Choose the Chart you just created then click on the paramaters tag
10. Add in the parameter you just created.
11. Publish
Then:
On your reporting dashboard open up a Excel Web Access web part and a filter web part depending on the values you wish to pass. Once you have chosen the chart on the Web Access web part (named item) then modify the filters properties. Send filter values to the web access chart you just created and make sure in the ‘Configure connection’ you choose the paramater you just published.
Hope my experiences helped you!
