Query Output Format

[Former Member]

Gurus,

Need your advice on how to achieve the following output on the Query:

................................Period 1..........Period 2.........Period 3

...............................Plan : Actual...Plan : Actual...Plan : Actual

Cost Center 115960...100....90........120.....130..........150.....160

Basically the report will show the Plan and Actual cost posted to the particular cost centre. We have total 12 Period (12 months), so 12 column on the output. Under each Period, I would like to sub-divide into 2 column u2013 for Plan and Actual cost.

Is this doable?

Please advice, thanks.

[Former Member]

good info

[Former Member]

Use 2 Strcutres in the columns in QueryDesigner.

Can only be done, if the rows are not organized as Strcuture as you can use only 2 Structures in one query...!

This is the best way in my opinion!

[pavel_afanasiev]

you can get away with only one structure in the columns: first place the period characteristic there, then the structure with the plan/actual characteristic under that - for each period, both will be shown.

then, restrict the period characteristic to periods 1-12

[Former Member]

Hi,

Add the cost center in your columns. Then you can add your characteristic containing Plan and Actual on the top of the key figure and Period over it.

Regards.

[Former Member]

Hi Pavel and all,

Thanks for your input.

I have only one Characteristic for Period, which will hold data for particular period.

Question now is how can I put the same Characteristic 12 times on the column?

Also the same as Key Figure for Actual and Plan

Please advice, thanks.

[pavel_afanasiev]

Hi again,

if you put the period characteristic in there without the restrictions, it will display all the values that are available. So, here are your two options:

1. Drag the period characteristic into the column section

2. Create a new structure in the column section, right under period characteristic (so, now there are two lines) in there. What you are telling the query designer is that for every value of the period characteristic, you are going to show the structure components.

3. Create a new selection under the structure. In the selection, drag over the Actual KF and any other characteristic that you might want to restrict. Name the selection as Actuals

4. Create a second selection in the structure - do the same thing for the Plan.

In this way, you are basically telling the query to dynamically show you all the values for the period characteristic, and then break it down by the Actuals and Plan. Depending on what Fiscal Year Variant you have, you will need to restrict the Periods characteristic, otherwise you will have more columns that you actually need. What I mean here is that if you have K4 (as an example) for the FY Variant, that means that you would have 12 Calendar periods and 4 special, so you would have 1-16 displayed in the columns. Depending on your system configuration, you also might have Period 0 (BCS uses that). So, in the global restrictions for the query, set the Period Characteristic to be 1-12

The other way of doing this is actually creating a column for each Period/KF combination. You would create a structure and then create new selections with:

1. Period 1/KF Actuals

2. Period 1/KF Plan

3. Period 2/KF Actuals

4. Period 2/KF Plans

... and so forth

On the major differences between these two query designs is what information would be shown in the output of the query. Using the first method, you will only get columns for the periods that actually have transactional data in them. So, if you system has Jan - Sept numbers in it, you will only have columns for periods 1-9, and the other 3 periods will not be shown. Using the second method, you will have the additional columns (Oct - Dec), but they will not have any data in them.

Hope this helps

[Former Member]

Hi Pavel,

Thanks so much for your help, let me try and get to you !

[Former Member]

Hi Pavel,

I have decided to follow your second option:

The other way of doing this is actually creating a column for each Period/KF combination. You would create a structure and then create new selections with:

1. Period 1/KF Actuals

2. Period 1/KF Plan

3. Period 2/KF Actuals

4. Period 2/KF Plans

... and so forth

However, I have the following output on the Row:

................................Actual 1...Plan 1

...............................USD....... USD

Cost Center 115960...100....90

Acutal 1 is the New Selection created with Period 1/KF Actuals.

Plant 1 is the New Selection created with Period 1/KF Plan.

I donu2019t understand where is the USD coming from? I didnu2019t enter it in the Column.

How to remove the Currency from here?

How to include and display the Period on top on the New Selections?

What I try to achieve is the following format:

................................Period 1..........Period 2.........Period 3

...............................Plan : Actual...Plan : Actual...Plan : Actual

Cost Center 115960...100....90

Please advice, thank you!

[Former Member]

If plan and actual are not different KF, create two restricted KF, one for actual and one for plan and put them in columns. Above the KF structure put the Period char and in lines the cost center char.

[Former Member]

It can be done in a workbook, do seperate columns for plan and actual by period and merge the period cells to get this.