Dynamic column Header Label Repeating

[former_member402770]

capture.jpgHi Experts,

Iam trying to merge dynamic column header "label".

The cross tab column added with Multiple list values as "AFRVP", "EAPVP" as dimension. Inserted an row above to add "Label". but Label seems tobe repeating as with column dimension, Attached screehsot for reference.

Could you please help to merge repeating Label to single "Label" and align center.

Thanks,

Dinya

[Tom_N8 · Accepted solution]

Hi Dinya,

You cannot merge two merged header cells into one single merged header. As a workaround you may want to consider creating a dummy table that just shows your desired headers. Make sure you have fixed column width for your columns in the "real" table. Then model a dummy header table as follows:

1. Insert table and then add as many columns as you need. In the example below (see screenshot) I need just three columns for my three months (Jan to Mar) plus an additional column for the vertical axis in my crosstab. Add more columns if you require more.

   

2. Then add another header row on top of the existing
3. Select all your body cells and in Format Cell... remove the border and choose no color in appearance.
4. Now merge the top header cells into one cell and enter your merged column header name
5. For your months you will need to apply a formula for each of the three header columns, e.g. =[Month] Where([Month="1"). Otherwise you will get a #MULTIVALUE error.
6. Now move your dummy header table on top of your "real" table. Go to Format Table Layout and choose the same horizontal distance as your cross table. For the vertical alignment you choose 0 cm from the top edge of your cross table.

   

This workaround does not work if you need your column headers dynamic, i.e. where your first month in the crosstab changes according to possible user input at query runtime. In this case you would have to think about additional calculations to resolve the header names dynamically.

Kind regards,

Tom

[jyothirmayee_s]

Hi,

How is your BEx query designed and display results as?. PLAN and ACTUALs as 2 different KF's with in a Column structure in BEx?. then you can have single crosstab to show these 2 KF's. No need of another crosstab aligning with relative positions.

Thanks,

Jothi

[former_member402770]

Hi Jothi,

Its an HANA universe, PLAN and ACTUALs as 2 different KF's using same Regions dimension group or splitups. I also need to define Actual and Plan Column Header Label.

Thanks,

Dinya

[jyothirmayee_s]

Hi Din 44,

Tom approach is in very detail :).

Thanks,

Jothi

[former_member402770]

Hi Jothi\Tom

Here is the sample crosstab structure, Actual is an static text grouped by dynamic dimension regions as A, B, C, D and has Actual KF values in body which is fine. I wanted same set for Plans next to Actuals without using relative position. Plan KF values should display in body for every dynamic inlist dimension regions as A, B, C, D

Thanks,

Dinya

[jyothirmayee_s]

Hi,

I would suggest the same as Tom suggested.

Did you tried applying break on Month?

Thanks,

Jothi

[former_member402770]

Hi Jyothi,

I would try Tom's workaround and update.

Thanks,

Dinya

[Tom_N8]

Hi Dinya,

Re your last comment in which you state:

"In the similar lines i wanted to this merged dummy column variable with another merged column text to result as single Text

Could you please help on this.

Below screenshot where 1st Actual displayed is merged using above little trick solution and this needs again to merge with another merged column value "Actual" to display as single "Actual" . Here merge option is gryed out so couldnot able to merge."

Judging by your screenshot you are now trying to merge two merged column headers into one single column header (stretching across multiple columns?

I have no issue in merging multiple header cells in a cross table, with or without using tricks. I am afraid I do not fully understand your question. Could you please provide more detail and more illustrative screen shots, maybe based on an Excel spreadsheet example with some explanations around it?

It would also be helpful if you did not open multiple threads on the same question. Please keep to one thread per question and close those that you feel have already been answered.

Kind regards,

Tom

[former_member402770]

Hi Tom,

Judging by your screenshot you are now trying to merge two merged column headers into one single column header (stretching across multiple columns?

Yes that is exactly what iam looking further assistance.

Thanks,

Dinya

[former_member402770]

Hi Salem,

Tried Merge cell greyed out

Thanks,

Dinya

[Tom_N8]

Hi Dinya,

Ayman's solution does not work in cross tables. For cross tabs, you will need to apply a little trick:

1. Create a dummy variable with ="Text".
2. Add the dummy variable to the header of your cross table.
3. Now apply a break on this variable.
4. In Manage Breaks untick "Break Footer" and "Merge" duplicate values.

Done. I found this here. It works.

Regards,

Tom

[former_member402770]

Hi Tom,

Many Thanks! it worked.

In the similar lines i wanted to this merged dummy column variable with another merged column text to result as single Text

Could you please help on this.

Below screenshot where 1st Actual displayed is merged using above little trick solution and this needs again to merge with another merged column value "Actual" to display as single "Actual" . Here merge option is gryed out so couldnot able to merge.

Thanks,

Dinya

[ayman_salem]

Hi Tom, it work in cross tables if you have two measure (as in this example "work and status") in the body of the cross table.

However, if you have only one measure, your solution is more appropriate

[ayman_salem]

Simply Select the cells you want to merge (Ctrl + left mouse click), and then under Alignment, select the "Merge Cells" check box and "Horizontal -> Center"