You may be frustrated by the limitations on moving a part of you PivotTable somewhere else, or inserting rows or columns within it. For some of your management or financial reporting projects you need to be able to layout your reports or dashboards, and sections within them, in a specific way. But you want to retain the dynamic link to your cubes or Data Models. And PivotCharts have their uses but can be limiting in the control you have over presentation.
This is where Excel's Cube Formula Reports
can help. Driven by Cube Functions
that, like any Excel function, can be placed pretty much anywhere, they allow you to structure your reports and dashboards the way you want them. But they still retrieve data dynamically from your models or cubes (or both) according to the parameters that you supply to the functions (date, product, region, customer, business unit, department, metric and so on).
Excel makes it easy to get going with Cube Formula Reports, with the Convert to Formulas
menu option, under the OLAP Tools
drop-down, of the PivotTable Tools
tab; use it to convert a PivotTable to formulas, usually easier than starting from scratch, and much easier than building GETPIVOTDATA
functions off various PivotTables squirreled away in your workbook.
As easy as it is to Convert to Formulas you'll want to make your reports and dashboards more robust while easier to maintain and enhance. Here are two multimedia Training Kits to help you do just that.
You will get up and running, developing reports quickly securing 80% of the benefits with 20% of the effort. In addition to topics specific to each of these Training Kits you will learn about:
- Making your reports more robust, easier to maintain, and re-usable
- Adding drop-down lists, slicers, and timelines for smoother report filter selection and dashboard navigation
- Adding functionality to check key report numbers and display alerts if numbers don't tie
- Automating report printing using VBA macros
- Debugging formulas, working with nested dimensions, member attributes, multiple data sources, adding new data rows and columns to existing reports
- Converting legacy "copy/paste/re-key" reports to cube formula reports
- Setting up your reports so they remain dynamic when published to SharePoint
- Leveraging Excel's functionality and your skills to enhance your reports and dashboards.
Find out more here: Excel Cube Formula Reporting with Microsoft Analysis Services (2nd Edition) Excel Cube Formula Reporting with the Data Model (2nd Edition)