Cube View - Return Blank Columns Where No Members Have a Specific Text Value

[benmac360]

Hello - I'm working on a cube view that will be used to add variance commentary. The rows have accounts and subtotals, the first column will have a specific entity that will not change, then the next set of columns will be dynamic. I have it set to pull base entities based on specific text values. The entities that do have the first text value (called "DP_2023Q4") are appearing as intended, however there are no entities that have the text value (AQ_2023Q4) in the member filter of the next column, so when I run the CV it just repeats the first column to the right of the columns with the DP_2023Q4 entities. I have several questions for this:

1. How can I set the cube view to return a blank column if there are no base members that have a particular text value? I added a column override with a UD8 dynamic calc set to return nothing if there's no data, and that didn't work.

2. Right now the member filters are set to look for text values that have "DP_|WFYear|Q4" and "AQ_|WFYear|Q4". This should be dynamic too, because there will be text values with quarter suffixes in the future like "DP_2024Q1" for example. The WF year parameter is working, but I can't figure out how to retrieve the quarter "sub-period" (the 4 in Q4), so I wouldn't need to update this filter every quarter. How would I do this?

[db_pdx]

Hi benmac: 

For 1, can you share your current memberfilter(s) here?  While it won't insert a blank, a standard E#YourTopEntity.Base.Where(Text1 Contains 'AQ_2024Q1') should properly return the list of entities, and suppress if none meet the metadata criteria.  But I might not be understanding how you have this setup so an example would be helpful.

For 2: will AQ_|WFYear||WFSubPeriod| work for your needs?  I don't have any quarterly workflows, but that works with my monthly workflows.

[benmac360]

Hi db

Yes - the member filter I have for the first dynamic column is E#TopEntity.base.Where (Text8 Contains [DP_|WFYear|Q4]), and for the second its the same syntax, except the Text8 is AQ_|WFYear|Q4. The second dynamic column is being suppressed since there are no entities with the AQ_2023Q4 text value. However the first column where the member filter has the specific entity is repeated in its place. I also have the primary dimension type to be Entity in Nested Member Expansion 2 for all of the columns - could that be the source of the issue?

Here's a screenshot of the cube view - the entity that's blacked out is the one I have in the first column's member filter. This is where both dynamic columns do not find text values that match the member filter.

The result for WFSubPeriod would be "M12" if the WF period is 2023M12, right?

[db_pdx]

Hmm, is that UD8 active on these columns?  I'm wondering if that is what is causing the entity to appear where it otherwise shouldn't.  That said, I cannot think of a way to have a blank column appear only when there is not data using standard member expansions.  Can you do a blank column at all times as a spacer between where the entity lists would be?

For the |WFSubPeriod| you are correct, it would be M12 if you are using monthly workflows. You might need a simple XFBRString to help convert from M12 to Q4.