Skip to Content
0
Former Member
Nov 27, 2006 at 01:53 AM

query with generating multiple records out of one!

23 Views

Hi Gurus,

i have data coming in the following format to the cube.

calmonth order start date finish date duration (working days)

- 101 15.01.2006 15.03.2006 -

i.e., for every order, no calmonth & duration is maintained. only start date & finish date. these are characteristics. the key figure is, plan hours for every order, say for example, 500 plan hours for order 101.

Now, i want the above record to be broken down to multiple records which looks like this:

calmonth order start date finish date duration (working days)

01.2006 101 15.01.2006 15.03.2006 10

02.2006 101 15.01.2006 15.03.2006 20

03.2006 101 15.01.2006 15.03.2006 10

which populated the calmonth and duration characteristics by first findign the work days in each month that lies between the start date & finish date!

and then the intention is to distribute the key figure (plan hours) into those months by dividing it by the total number of work days and then multiplying it by no. of work days available in every respective month.

so the challenge lies in populating the one record into as many number of records, as many months are there in between start date and finish date for that order and findign the number of work days. i can even calculate the number of workdays. but do we have to BPS to do this.. or a way otu in BW?

In 3.5 there was this return table options aganist key figures, which is absent in 7.0 or is it there anywhere else?

Please help.