What's New

Convert Kepion Form to Excel Pivot Table

By - Updated April 10, 2019

Here we will explore how to convert a Kepion Form to an Excel Pivot Table.

Let’s first understand the layout structure of our example form.

We see that we have the following layout definition:

On the row definition we have the following:

And on the column definition we have the following:

The column definition is using variables such as Prior Months and Future Months.

The definitions of Prior Months and Future Months are defined as a Named Set in MDX Script.  This means that these Named Sets can also be leveraged in Excel Pivot Table.

Let’s now go to Excel and create our Pivot Table.  Connect to the Kepion cube and create an empty PivotTable.  From Excel, go to the Data tab->Get Data->From Database->From Analysis Services.

Find your cube and create a new Pivot Table.  Next, click on the Analyze tab in Excel, select Fields, Items & Sets option and click on Manage Sets.

Select the Create Set using MDX option.

Give the Set name as “Rows” and copy the row MDX definition from Kepion and paste it in the Set definition:

Repeat the step above, but this time do it for “Columns”.

Add the same filters as in the Kepion Form over to the Excel Pivot Table.

Right click the Pivot Table and choose Pivot Table Options.  Select the Display tab and uncheck the Display field captions and filter drop downs.

Now you will have a Pivot Table with the same structure as the Kepion Form.