Forum Discussion
Export Matrix without Totals using a Theme file
I am looking to preset the matrix visual to display without subtotals using a theme file. This seems to work for viewing the visual in service. However when I export the matrix subtotals appear, which is really what I am trying to prevent. Below is information from my theme file showing subtotals on rows and columns are set to false. I also included images of the matrix in service where we can see subtotals are not visible and from an image of the excel file with subtotal visible.
The odd thing is if I edit the report in service I see row and subtotals turned off by default due to the theme. If I turn on subtotals and then turn them off again the matrix exports without totals. I am trying to figure out what is the service doing when deactivating subtotals that the theme is not. I recently added the perRowLevel and perColumnLevel attributes to the theme file but that did not seem to make a difference.
Any thoughts?
"pivotTable": {
"*": {
"grid": [
{
"fontFamily": "Avenir Black",
"textSize": 10
}
],
"rowHeaders": [
{
"fontFamily": "Avenir Black",
"fontSize": 10,
"stepped": false,
"showExpandCollapseButtons": false
}
],
"subTotals": [
{
"rowSubtotals": false,
"columnSubtotals": false,
"fontFamily": "Avenir Black",
"fontSize": 10,
"perRowLevel": false,
"perColumnLevel": false
}
]
}
}
This is how the visual appears in service.
One we export the matrix to excel we receive totals in the file.
5 Replies
"However when I export the matrix subtotals appear, which is really what I am trying to prevent."
Please explain why. Also, in newer versions of Excel you can run DAX queries against your datasets, and then shape the result any which way you want.
Our organization has implemented Ad-hoc reporting templates using personalization. When the user comes to power they receive a blank template with a default visual in table format. Our users have the ability to change the table to matrix using personalization but hey do not have the ability to format the visual in service. By default the matrix visual includes subtotals on at levels on rows and totals on columns.
We have several users complaining that there are too many subtotals and makes the data difficult to work with once in excel. Currently my theme file does remove subtotals on the matrix visual when displaying the report in service. However if the user exports the matrix to excel subtotals appear.
I am trying to understand why the export is adding subtotals when they are not present on the screen. I also want to know if there is anything I can do with a theme file to prevent the subtotals on export.
It might be easier to provide the users with a pre-tensioned version of the matrix visual (with the subtotals removed)
You are right, the "Personalize Visual" option is great but it has limited formatting options. I hope they increase that functionality, it has been great so far to give report users the freedom to get insights on the fly.
Wonder what will happen if you switch from Matrix to table and back to matrix - will the subtotals then show?
