Export Top Level item using Tabular Multiple Column
Dear Anaplan Community,
I would like to export data using Tabular Multiple Column (to use it later as export action in Power BI with Anaplan Connector), including the top level item of my list.
In the export definition, I did NOT select "Omit Summary Items", however, in my export I don't see the Total row.
What am I doing wrong?
Kind regards,
Louise
Best Answer
-
@LouiseBourgonjon Thanks for the additional info. It appears there are some challenges due to the more stringent requirements of how filters work on a Tabular Multiple Column export. I apologize as I wasn't very familiar with these when I initially responded.
That said, I believe I found a solution that will work for you. This is still based on my initial suggestion of leveraging filter logic in Anaplan to drive the filters. My goal with the below was to calculate a single filter line item, rather than depending on Anaplan to combine the two export filters in the way that I want.
Note that per the documentation, the filter line item needs to match the dimensions of the export module exactly.
I set up my module similar to what yours looks like in the screenshots:
I also configured two filter modules like you have set up for Sites and Quarters (in my case, this is Countries and Quarters):
Finally, I created a combined filter module that includes all three of the dimensions from my original module (Countries, Quarters, and MOS Bridges):
Note my formula and summary here.
1. The formula uses an AND to ensure that my two original filters are respected together. This ultimately works the same way your current configuration does, since the Tabular Multiple Column export filters function as an AND behind the scenes (and you cannot change this).
2. The summary method does the magic here when combined with having no filter logic for MOS Bridges. By setting the summary to All, the Total (top level) will return TRUE when every row of the MOS Bridges dimension is TRUE, which will always be the case when the original filters combine to be TRUE.
Finally, I configured the export filters to use the new module above.
And the exported result now include the Total you were looking for:
0
Answers
-
@LouiseBourgonjonYou can try to pivot all dimensions as lines and use this view to export as Grid. (i recommend you to save view for future use)
0 -
I'm wondering if perhaps your filters are filtering out the top level item. Couple things to try:
1. Try running the export without the filters. Does your top level item show up now?
2. Try applying the filters to the view directly. Is your top level item being filtered out?
If this is the cause, you would have to adjust your filter logic to ensure the top level item is being included in the filter results.
0 -
hi @ryan_kohn ,
I don't use any filters in my saved view as this is not compatible with a tabular multi-column export.
So I am always exporting from my default view without filters and I use the two filter rows of the tabular multi-column export option to filter my view. The issue I face is that it also filters out the Total row, which I would not expect because I don't choose the option to omit summary items.
I tried your suggestions:
1. My top level item is shown when I don't filter. However, that is not a solution because I want to filter on site and on quarter.
2. Working with a saved view with filters and exporting as tabular multi column is not working, the filters are ignored. Again, I see all sites, all quarters, including the Total row.
0 -
Sounds like you need to adjust your filter logic. Do you have summaries turned on for those filters? If they are Boolean formatted line items, then changing the summary to "Any" should do the trick.
0 -
Hi @ryan_kohn ,
Some remarks:
1. I understood that when exporting using multi-tabular single column, one should start from an unfiltered view and use the 2 filter rows in the export definition under "Filter Rows Based Upon Boolean line items" to define the filtering. Which I do. So my starting base has all sites in page selector, all quarters in page selector and it shows the total row.
2. The filters I want to use only apply to the Sites list and the Quarters list. The comments list (1-5 and total) is the other dimension which I don't want to filter on. If I put any as summary item for the boolean filter for sites, it will export the parent hierarchy (the platform above the site), which I don't want. Same for the quarter list, if I select any, also the year total will be shown.
0 -
Hi @KirillKuznetsov ,
According to this setup guide, grid style is not supported for Anaplan Power BI Connector.
https://community.anaplan.com/t5/How-To/Power-BI-This-is-how-we-use-it-Part-2-Setup/ta-p/104365
0 -
Thank you very much @ryan_kohn for your detailed explanation.
I created an additional filter module with the same dimensions as the module I wish to export and referring to my existing filter line items and that worked!
1
