Table Headings issue - Local Formulas

[Former Member]

Hi,

I have a report which has the following settings

Rows

     Accounts A - member and children

     Accounts B - member and children

     Accounts C - member only

Column

     Static as per screenshot below

Local Members (rows)

     Blank Line - Attached to member Accounts A (after)

     Area B Heading - Attached to "Blank Line" local member (after)

     Blank Line After Area B - Attached to "Area B Heading" local member (after)

     Table 2 Heading 1 - Attached to "Blank Line After Area B" local member (after) - this is for Month / YTD heading for table 2

     Table 2 Heading 2 - Attached to "Table 2 Heading 1" local member (after) - this is Actual/Plan/Var heading

     There are additional local members for the headings for table 3 (Area C) but this are linked directly to "Account C"

 

The problem i have is that if i run the report is that if there is no data for Area B the report looks like the below. This is because i linked the headings to Account A in the first table. I cant link it to table 2 (Area B) because the children of Account B change.

Any ideas on how to get around this?

Cheers

[former_member186338 · Accepted solution]

What do you want to have as a result?

Vadim

[Former Member · Accepted solution]

Hi,

I would like only the table which has data to be shown. In the example above i would want the following to be displayed.if there is no data for Accounts B or C

[former_member186338 · Accepted solution]

Instead of after - use before, as I already told you!

[Former Member · Accepted solution]

Hi,

What should i do it before against?

If I do before Account B - it appears directly before account B rather than the children which could change?

If I do it before Account A - then this wont give the desired result.

Cheers

[former_member186338 · Accepted solution]

Local members for header have to be before dimension Account.

Vadim

[former_member186338 · Accepted solution]

Ups, sorry, you don't have entity in Row axis as in previous discussion... ???

[Former Member · Accepted solution]

Hi - in this scenario there is only on dimension in the rows which is account. If I do before "Account" all that happens is the heading goes to the top but does not replicate for each table as illustrated.

[former_member186338 · Accepted solution]

And what is the meaning of Area A...????

[Former Member · Accepted solution]

Hi - At this stage it is a heading hard coded into Excel for the first table. At this stage I am assuming that there will always be data in the first table.

[former_member186338 · Accepted solution]

Please explain, do you have Entity dimension in the row axis? I can see Area A,... etc

Second - do not hard code the heading for the first table - use hard coded values in the hidden rows referenced in the local member. Same header for A,B,C

Vadim

[Former Member · Accepted solution]

Hi,

Area A is based on the value of "Accounts" - if it is "Account A" - then it is part of Area A. I will look into a formula to do this.

Entity is in the page axis and use to filter the whole report.

Cheers

[former_member186338 · Accepted solution]

I have created a sample with ENTITY, but you can use the same approach to account. All headers have to be before the section including the first one:

Vadim

[Former Member · Accepted solution]

Hi,

Is it possible you can expand on how you setup the local members for the headings? What are they linked to? Do you only have one dimenson in your rows?

I am not sure your if your example is for my initial problem - which is where i am having problems for headings for headings where there is only one dimension in the row?

Cheers

[former_member186338 · Accepted solution]

Ups, looks like I understood the issue: with row axis containing only account dimension you are unable to attach the local member before the first non empty children of some parent member. You have to attach it after the parent...

Vadim

[Former Member · Accepted solution]

Hi - unfortunately, the problem is if I link it to the parent - i have to link it to the parent of the table above. In turn I have the issue initially mentioned.

Cheers

[former_member186338 · Accepted solution]

In general the idea is simple - the enabled local member will disappear from the report if the member it's linked to is not shown. In your particular case if you put parent member on top of children and link local member before parent - it will work, but with parent after children - no chance...

Vadim

[Former Member]

Hi Leo,

Maybe in your account B part headers do a check for empty results, if its empty then just display nothing, else display something?

Andy

[Former Member]

Hi,

Sure, could do that but is there a way to also get rid of the formatting as well?

Cheers