I hope you are able to help...
Some Background...
We currently have an ODS which is being updated by 2 update rules. One is item data direct from our IS-U source system and one is Header Data which comes via another ODS. We load the Item data and once activated in the ODS load the header data to update certain fields within the ODS.
I have tried to lay out a table below as an example.
Column 1 is the key used to match the fields. (Documnet Number)
Column 2 is line number
Column 3 is a key figure updated by the item extract.
Column 4 is a Key Figure Updated by the Header Data.
Column 1 Column 2 Column 3 Column 4
00001 1 2.75 10.25
00001 2 2.50 10.25
00001 3 2.50 10.25
00001 4 2.50 10.25
Currently when I view this in a query that is summerising the data I get the following results:(I have removed column 2 from the drilldown)
Column 1 Column 3 Column 4
00001 10.25 41.00
The result I want is:
Column 1 Column 3 Column 4
00001 10.25 10.25
Is there anyway to stop Column 4 from totaling its self up. I have tried the settings within BEx and the Backend but to no avail.
I would be grateful if somebody could help me.
Thanks,
Tim
Answer:
Hello. Your fourth column should allow you to specify a summation / media / ... While displaying the result on BEx, right-click the result cell and choose Calculate / Results As. Now you can choose the way the results are calculated.
If you want to save these settings you have to specify it in the query definition: Query properties.
Maybe you could give us some background about your BW version if this still doesn't work.
Answer:
Hi Eric,
I have tried these options and none of them have worked. Enev changing them permamently doesn't work.
We are currently using version 3.1c if that's any help.
The only method I can think of at the moment is to create another key figure and update it in the update rules by dividing it by the number of line items for that document. Therefore when it adds up the value in BW it will come to the correct total!!
Any more help anyone.
Ed
Answer:
Hi,
What is it you want to show in column 4: Minimum Value, Maximum Value, Average, Amount for a particular line number? It sounds like you need a new formula or selection on your query but the design of this would depend on what you want to see in column 4.
Cheers,
BW Fan.
Answer:
You may want to check out the infoobject setting in the aggregation tab. There are Average...., maximum or minimum.
Slamdunk
Answer:
Hi
In my opinion the behavior is quite correct. The key fig from the header is simply not valid on item level and should not be updated on that level. Take a closer look at the DW concept of granularity to see the point.