Question Re: Dynamic column Header Label Repeating in Technology Q&A

Dynamic column Header Label Repeating

former_member402770 — Fri, 08 Mar 2019 18:17:09 GMT

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

Re: Dynamic column Header Label Repeating

ayman_salem — Fri, 08 Mar 2019 18:33:15 GMT

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"

Re: Dynamic column Header Label Repeating

Tom_N8 — Fri, 08 Mar 2019 18:53:11 GMT

Hi Dinya,

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

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

Done. I found this here. It works.

Regards,

Tom

Re: Dynamic column Header Label Repeating

former_member402770 — Fri, 08 Mar 2019 19:01:25 GMT

Hi Salem,

Tried Merge cell greyed out

Thanks,

Dinya

Re: Dynamic column Header Label Repeating

former_member402770 — Fri, 08 Mar 2019 19:14:36 GMT

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

Re: Dynamic column Header Label Repeating

ayman_salem — Fri, 08 Mar 2019 19:38:22 GMT

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

Re: Dynamic column Header Label Repeating

Tom_N8 — Mon, 11 Mar 2019 11:04:50 GMT

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.

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

Re: Dynamic column Header Label Repeating

former_member402770 — Mon, 11 Mar 2019 17:19:04 GMT

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

Re: Dynamic column Header Label Repeating

Tom_N8 — Wed, 13 Mar 2019 11:32:36 GMT

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:

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.
Then add another header row on top of the existing
Select all your body cells and in Format Cell... remove the border and choose no color in appearance.
Now merge the top header cells into one cell and enter your merged column header name
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.
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

Re: Dynamic column Header Label Repeating

jyothirmayee_s — Thu, 14 Mar 2019 20:34:59 GMT

Hi,

I would suggest the same as Tom suggested.

Did you tried applying break on Month?

Thanks,

Jothi

Re: Dynamic column Header Label Repeating

former_member402770 — Thu, 14 Mar 2019 21:10:05 GMT

Hi Jyothi,

I would try Tom's workaround and update.

Thanks,

Dinya

Re: Dynamic column Header Label Repeating

jyothirmayee_s — Fri, 15 Mar 2019 16:39:52 GMT

Hi Din 44,

Tom approach is in very detail :).

Thanks,

Jothi

Re: Dynamic column Header Label Repeating

former_member402770 — Sun, 24 Mar 2019 19:20:40 GMT

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

Re: Dynamic column Header Label Repeating

jyothirmayee_s — Mon, 25 Mar 2019 17:34:34 GMT

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

Re: Dynamic column Header Label Repeating

former_member402770 — Mon, 25 Mar 2019 21:51:40 GMT

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