Create reports and forms for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

About creating reports and forms

Financial planning forms and reports can be created from multidimensional data models by using Microsoft Excel 2010 PivotTables. Here we will define forms as planning PivotTables used in collecting plan data while reports are read-only PivotTables and used primarily for reporting. Our focus here will be on plan forms. It is important to be aware that we have two kinds of PivotTables, one that comes natively in Excel 2010 and one that is part of the PowerPivot Add-in. Here we will look at the holistic benefits of that include both kinds of PivotTables. Benefits of forms and reports authoring in Excel 2010 include the following:

Write-back enabled PivotTables let IWs publish updates to SQL Server Analysis Services cubes.
"What-if" capability let IWs evaluate ‘on the fly’ changes against SSAS cubes.
Enable spreading and allocation entry against SSAS cubes.
PivotTable Sets for authoring of common axis definitions (row, column, etc.) for easy reuse in other forms and reports.
Support for MDX against SSAS cubes when you define sets.
Publish to SharePoint for web reporting requirements.
Full access to native Excel formatting capabilities.
Familiar Excel environment for IWs to work in.

However, some considerations to be aware of include the following:

PivotTable writeback provides little control on what areas should be available for input. Designers may have to manually update regions on the PivotTable to show availability of input but cannot prevent data entry to those areas unless secured through SSAS security.
Using security to define updateable data regions in PivotTable can result in more difficult to maintain security setups.
Authoring of dynamic expressions for row and column definitions can be difficult. Single member select capability on dimensions can be tiresome and tedious.
Lack of ability for IWs to view all changes currently made to data model could lead to confusion as to what is ultimately being published.
No ability to submit annotations together with financial data updates

In this section, we will focus on authoring a sample form to demonstrate some flexibility found within Excel’s Pivot Table designer. The outline of how to create our sample form is as follows:

PivotTable creation

Establish connection to data model.
Create PivotTable.
Design sets by using MDX.
Design Layout.
Publish to SharePoint.

Connect to data model

Before you create a form, first establish a connection with the data model. Connect to the SQL Server 2008 Analysis Services server from Excel and view the available cubes that you can use for planning.
For a sample form, connect to the Forecast cube from the “AdventureWorks Planning” database.

We can also establish a connection to our PowerPivot model to build our form. First open the workbook file that stores the PowerPivot model, this can be from either your local client computer or from a SharePoint PowerPivot Gallery. Start by inserting a PivotTable at a desired location on the spreadsheet. Next, select Existing Connection, where you can select which local PowerPivot model to create a form from. Once selected, we can start to build our form.

Create PivotTable

A PivotTable is created by defining its axis. The axes for a PivotTable include Row, Column, Filter and Data. We start by selecting from our data model, the appropriate dimensions and properties to position on each of our axis by using the PivotTable Field List selection. For our sample form, we have the data shown in the following table.

Row	Column	Filter	Data
Product.ProductGroup	Time	Geography	Value
Product.ProductSubGroup		Scenario
Product.MemberName		Account

Considerations when you build plan form include the following:

Use the Report Layout as ‘Show in Tabular Form.’ This allows for clear labeling on row headers and provided for a compact format for data displayed on the form
Use PivotTable sets for creating asymmetric layouts such as on columns, where ‘Actual’ is for the first three months followed by ‘Forecast’ for the remaining months of the year. Saving the column definition to sets will allow for easy reuse by other forms.
Use the PivotTable’s options for display to suppress empty rows by de-selecting ‘show items with no data on row’
As a general form design rule, it is usually a good idea to keep large contiguous input ranges together instead of having multiple sections broken out with large spaces in-between. This is especially true when the plan data has contiguous data regions with similar calculation logic. Why is this important? IWs will often use Excel drag and fill when filling our formulas on the sheet and by keeping contiguous input section together, This allows for for easier usage by IWs.

Custom MDX forms

For generating more customized reports off the Analysis Service cubes, IT specialists can use MDX to develop a form through PivotTable Sets. We start PivotTable Sets by selecting Fields, Items, & Sets under Calculations of PivotTable Tools, and then select Manage Sets. Here we can create a new set for rows, columns and filters by pasting in our pre-defined MDX.

Once we have finished creating our sets, we can go back to the PivotTable Field List and use these sets to define our Row, Column and Filter fields.

Design layout

With PivotTable Design, we can build custom formatted forms that show the data in the most appropriate format for input. Here, for our planning scenario, we will color code with white the sections that are read-only, apply yellow for sections that are for plan input and gray for sections that are calculated.

Excel 2010 provides a rich format editor that can be leveraged to perform various formatting capabilities.

Create reports and forms for BI planning solutions and scenarios

About creating reports and forms

Connect to data model

Create PivotTable

Custom MDX forms

Design layout

See Also

Concepts

Additional resources