Multiple column headers in Bex Report with calclation? Help please...

[former_member197527]

Hi Experts,

Really need some suggestions. Have a complex report requirement.

Tried creating 2 structures in the Columns. But not satisfying the req.

PFA the screen attached. Please let me know how this can be acheived?

Its very urgent for me.

[former_member182470 · Accepted solution]

Hi Aditya,

You can easily achieve everything except M01 and M02 functionality with my approach. You can take the idea of Uma shankar's for keeping M01 and Mo2. Rest all you can follow my approach to achieve your whole requirement in a single report only. You need not to split into two.

I have asked you to create for one quarter at least to gt confidence. Have you tried it?

Regards,

Suman

[gleo_SRAM]

Hi Aditya,

Quarter 1 is just going to be a fomula adding Jan / Feb & Mar...

What are the M01 and M02 values above representing

Are the K1, K2, K3 customer sales?  If so what you are saying is the K1 may buy from you in Jan but not in Feb or Mar so you only want to see K1 in Jan and Qtr 1?

If K1 is a customer, how many customers would you want to display in the strucutre per month?

With regards

Gill

[umashankar_poojar]

Hi Aditya,

      There are two ways of creating a child node,

1. Copy the K1 and right click on M01, and say paste as child.
2. Drag the K1 and drop over M01, while dropping you make sure put your mouse cursor on M01. Slight down will put K1 below to M01.

Hope it helps!

Thanks,

Umashankar

[umashankar_poojar]

Hi Aditya,

        Refer below screenshot for your requirement,

• One query is not sufficient to meet your requirement. Suggest you to  create two queries, one for Month and other for Quarter as shown in above pattern. In one workbook attach both queries.
• You will have group K1, K2... under M01 and M02, put it as parent and child as shown.

Hope it helps!

Thanks,

Umashankar

[former_member197527]

Thanks UmaShankar.

But how does it come as a parent and child in here...it always comes like one below other.

Probably am really not aware...

[former_member197527]

This needs to come for all the quarters followed by respective months.

k1-k5 are not direct keyfigures and they need to be based on counts.

How can I get the k4,k5 under M02 ONLY? Same goes with K1.

No user inputs for this query.

Please give some suggestions please.

[former_member182470]

Some advices from me.

1) Create all required KFs from K1 to K5 by based on exception aggregations by taking reference chars for counts

2) Hope you know umbrella concept. You have to drag 0calquarter first to KF pane and drag 0calmonth under 0calquarter.

3) What is M01  and M02? Are these values of any char? If yes the drag that char also and keep under 0calmonth. So your umbrella will look like

0Calquarter

    0calmonth

         Char

4) You have to keep all your Kfs under Kf structure

5) Drag Model in to rows

Execute the report and see how your result looks like..

Regards,

Suman

[former_member197527]

Thanks for replying.

Please share any info on the umbrella concept. Never used it yet.

No need of any structures as such in Columns?

M01 and M02 are just descriptions that I need to give for specific KF1, KF4 and KF5

Should I drag the 0calquarter as well under KF structure?

All the values except first header i.e., quarter should repeat for respective months of the first quarter.

This should be followed by the next quarter with respective months.

These counts should appear exactly as shown in picture for each set. Please help in more details

[former_member182470]

No additional structures are required.

KF itself is a structure. You should keep KF structure under 0calmonth and 0calquarter. I got your requirement clearly. Just try like below. You cannot add descriptions in the query. If you have char to show M01 and M02, then only keep char, otherwise leave it. At least achieve the report layout without that.

Your Kf section looks like below :-- Create all your KF's first and drag calmonth above KF structure. After that drag calquarter above calmonth.

0Calquarter

    0calmonth

             KF Structure

Regards,

Suman

[former_member197527]

Hi Suman,

If I do as above

Am getting output as below followed by same set...

Quarter                                                          

Month               Month

KF1 KF2.....      KF1 KF2.....

But please see my req...

These counts must first come for quarter

Then same should come for respective months....

Please suggest if am missing anything

[former_member182470]

Ok Aditya. My mistake. If that is the requirement, then umbrella concept doesn't work for you.

You have to create fixed KFs for all months(X 5) by restricting with each month.

Jan KF : K1, K2, K3,K4,K5 and restrict each KF with Jan. So for Jan you will have to create 5 KFs.

Similarly create till Mar and create a CKF( Jan +Feb +Mar) which gives you Quarter 1

Conclusion : 12 * 5 = 60 KFs and 4 CKFs for 4 quarters. Totally you have to create 64 KFs which is going to be tough task.

As umbrella concept doesn't help for you, this is the only alternative way. Don't get afraid to create all KFs. First try to achieve for one quarter to get confidence.

Regards,

Suman