on 09-03-2008 6:04 AM
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.
good info
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It can be done in a workbook, do seperate columns for plan and actual by period and merge the period cells to get this.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.