How to develop this report/Query

[Former Member]

Hi

How can i develop this report.

Our cube is loaded with Employee's Salarly on Monthly or Weekly basis (Some are on Monthly & some are weekly payroll)

Payroll date is defined as both CHAR & KF in cube

Data In Cube

Emp_ID---Payroll_Date--Amount

001

---

17.09.2008---2500

001

---

17.08.2008---2500

001

---

17.07.2008---2500

001

---

17.06.2008---2500

001

---

17.05.2008---2500

001

---

17.04.2008---2000

001

---

17.03.2008---2000

001

---

17.02.2008---2000

001

---

17.01.2008---2000

001

---

17.12.2007---2000

His Salarly is changed on 17.05.2008 from 2000 to 2500

Now my requirment is to develop a report which shows Employees Current Salarly and Previous Salraly(Changed) and Diff of change

for the above example the report output i am expecting is

Emp_ID--Current Sal (KF)Previous Sal (KF)increased amount--

---

Payroll Date

-001

---

2500

---

2000

---

500--

---

17.05.2008

I am struggling to create this report ...

Please update how can i achieve this

Thanks

[Former Member]

Hi,

I would use an Analysis Process (TA: RSANWB) for this. Using your cube as input I would suggest the following steps:

1. Aggregate the data by user and amount. Aggregation is "minimum" for Payroll date. You then have the first payroll date for each amount of pay.

2. Use a ABAP step to run through all the changes for a user.

Get the first entry of an employee and store amount in a variable. If there is a next entry, add new_amount to the result set, set Payroll date to the new date and store. Update amount in the variables and go to the next entry.

As a result you will get Employee, Payrolldate (first day of new_amount), old_amount, new_amount.

The increase of amount is a calculation then.

Kind regards,

Jürgen

[Former Member]

Hi Jürgen Kirsch

Thanks for the update

Could you please update me in detail...i was bit confused with ur solution

Thanks in advance

[Former Member]

Hi Experts

Please advise

[Former Member]

Hi,

what it confusing to you, the Analysis Process Designer or the solution ?

Kind regards,

Jürgen

[Former Member]

Hi,

Both of them..Analysis Process Designer and solution

Thaks

[Former Member]

Hi,

I think you have to look into the APD Documentation in the SAP library.

Perhaps this will give you an idea how this tool works and what can be done with it.

Aggregation and ABAP are transformation methods within the APD. I think if you go through the documentation and read my suggestion again, it may make more sense.

Kind regards,

Jürgen

Edited by: Jürgen Kirsch on Sep 18, 2008 10:28 AM

[Former Member]

The APD is an interesting solution, the problem I foresee in it is that the target is not a query, so for the end user it's not so simple.

Of course, you could design an ODS as a target, and then create a query on it.

to bw learner: VBa is really Visual Basic, and you could create a program which will analyse the result of your query and transform the records. Of course, this solution only works in Excel.

[Former Member]

Hi Frederic,

usually the result of an APD is stored in a transactional ODS. The content is then transfered to an ODS, because it is not possible to report on an ODS. If you are working with lots of data (starting from a few 100.000) I will add an Infocube for faster display. But that is usual tradeoff: Memory or disk space for speed.

Because APDs can be used in process chains, it is no problem to create a chain that takes care of moving the data to the target ODS or cube. And...the presented example is a good example to use APD, because it does not containt too much data. Even the biggest companies will have less than 100.000 employees. And a few thousand pay records as described. Therefore the APD limitation of running in memory will not be a problem here.

Kind regards,

Jürgen

[Former Member]

I would create this report by using VBa, listing all the rows, and comparing the current row with the next (or previous) one, and displaying when a break is shown.

It's a quite difficult (and interesting) query to design

[Former Member]

Hi Frédéric GAUTHIER ,

What do you mean by VBa...Visual Basic

Can you please explain me in detail how to start with

Thakns