Overview

PivotTables can be a very useful tool for ad hoc analysis. While the standard Cosmos Report Engine doesn't support PivotTables currently, it is still possible to use them in conjunction with Power BI. When a Power BI dataset is published within the Power BI service, that dataset can also be used by any users that it has been shared with to use PivotTables in Excel. This article covers how to set this up in your Cosmos Portal.

Details

Since PivotTables aren't currently supported in the Cosmos Report Engine, you will want to create a Static File where the PivotTable will live. To do this, click the three vertical dots to the right of New Report and click New Static File.

You will then name the file and click Create.

Once the file is created, you can add in a connection to your Power BI datasets by going to Insert -> PivotTable -> From Power BI

A Power BI pane will open on the right which will show all of the datasets that are shared with you. You can click the Insert PivotTable button on the desired dataset, and it will insert a PivotTable within Excel. 

You can then leverage all of your favorite PivotTable functionality to build out what you want for your users.

Once the PivotTable is done, you can check in the file and use the built-in Report Security to control who has access to it, just like you would for any other Cosmos reports.

Users with access to the file can then log in to the Cosmos Portal, click the "..." on the report card for the file, and click Download to download a copy of the PivotTable file to work with it.