Overview

Static Files can be a convenient way to store data that you want to use in an Excel Data Source or to leverage Excel to bring in data other than by using standard Cosmos functions. For example, you may want to create a file using a PivotTable from Power BI but don't want the user to have to "run" the report using the standard Cosmos "Run" button. This article will cover how to set up Static Files and the various ways to use them.

Creating a Static File

To create a Static File, you will click the 3 vertical dots to the right of the "New Report" button and click the New Static File button.

You will then give the file a name and click Create.

Using a Static File as an Excel Data Source

When working with Excel Data Sources in Cosmos, the Static File acts as the place where your data will live. When using a Static File to store custom data, you will create the file with a specific format in mind. They key points are:

• The sheet name will be represented as the Table name in the data model
• The first row of the sheet must contain the column names that will be represented as the Field names in the data model
• The second row of the sheet must contain the data type for each column. The five supported data types are below, and it is important to note that these are case sensitive, so they should be entered as lower case in the Static File. 
  • text
  • decimal
  • number
  • date
  • boolean
• The third row needs to contain at least one row of your data

Below is an example of what this would look like:

Once the structure has been set up you can check the Static File in and interact with it just like any other item in the Reports section. You can rename the file, copy it, check it out to make changes, and set security on the file to control who has the ability to manage it and update the data in the file.

Using a Static File with Power BI PivotTables

Power BI is a very powerful tool and one feature that many users like is the ability to leverage your Power BI datasets to use PivotTables in Excel for fast, ad hoc analysis. PivotTables aren't natively supported at the moment in standard Cosmos reports because they are removed when the report runs, but with Static Files the report never runs in the Cosmos Report Engine so the PivotTables will remain. 

To leverage PivotTables in a Static File, you would create the file using the steps above and then, in Excel, you can navigate to Insert -> PivotTable -> Power BI:

If you have Power BI datasets published and shared with your user this button will be enabled, and when you click on it you will be able to choose which dataset you would like to add a PivotTable for in the right-side pane in Excel:

After clicking Insert PivotTable, a PivotTable will be inserted into Excel, and you can then leverage all of your standard PivotTable functionality against this Power BI dataset.