COGNOiSe.com - The IBM Cognos Community

Regarding GTP

Hi Preethi,

well there are 2 ideas that i would started to consider.

1st - create someting like there is for "depreciation simulation" in standard installation lib. with analyst - go_capex_contributor - look at that 4 cubes (asset_purchases, depreciation & depn police should be enough to have a look at). You can have one cube (depn police) where you can set annual salaries for each salary bracket. You can have the other one - where can be details about empoyes (e.g. names ?, their salary bracket, date of entering the company.... - quite a lot of data, but without the timescale dlist - that will be used as a 1 dlist formated items - like in asset_purchases cube). And the 3rd cube could look like "depreciation" and will have a timescale & will combine data from all the previosly mentioned cubes - that could work fine (just have a look on that..)

2nd approach - is to solve it in your own way - trying to use "zeroes & ones" (0 & 1) in the timescale, the filnal salary will be a formula multiplying the "0&1 row" with the row for the whole years salary (something like you mentioned - a row filled allways from january to dec.) but after appropriate calculation (*0 or *1) only relevant month remains.

I think the 1st approach could work good and will be better (i will start with that) - the second option - i would use only if there were some unexpected & serious reasons why not to use the first one. (The second onlo requries much more trying & testing circles.. :-) & more work..)...

Hi Preethi,
It can be easily implemented with introduction of one calculation item using @Last BiF and two D-links
from HR to Finance for data


following is the example

HR Cube (New item with BiF): --

Months	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Salary/Month	0	0	0	0	0	0	6000	0	0	0	0	0	6000
@Last-BiF	0	0	0	0	0	0	6000	6000	6000	6000	6000	6000	0

Finance Cube (After running 1st link to fetch the @Last-BiF data):--

Months	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Salary Cost	0	0	0	0	0	0	6000	6000	6000	6000	6000	6000	36000

Finance Cube (After running 2st link to salary data value for break-back):--

Months	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Salary Cost	0	0	0	0	0	0	1000	1000	1000	1000	1000	1000	6000

I hope this serves ur purpose..

Hi Jitesh,

Thanks for sharing your ideas.

But the problem is we are not using the 4months dimension in HR Cube. We have a field called DOJ (Date of Joining) in calculation dlist.

:-(

Looking forward to your response.

Preethi

Hi Preethi,

I was trying to work out some solution for the given situation..
Here I have created 2 cubes. first HR Info cube with assumption that it has dimensions like Employees, DOJ & Salary Info Input, etc.. 
I have also created a cube name Salary Calc to calculate salary based on month of joining.
finance cube will get the final salary values from this cube
following is the detail of structure & calculation explanation I hope now this time it solves ur issues... :D


HR Cube
(With additional salary per month calculation)
Cube Example:-

		Date of Joining	Salary/Year	Salary/Month
Employee 1		31/12/2010	6000	500
Employee 2		15/06/2010	1200	100
Employee 3		01/07/2010	2400	200
Employee 4		-	0	0
Employee 5		-	0	0

Salary calc
(to calculate salary and will flow the final salary amount to Finance )
following is the calculation for this cube

Item Name	Format	Calcuation	Data Source
Date of Joining	Date, Blank If Zero	-	From HR Info Cube
Period Start Date	Date, Blank If Zero	BiF@Time(3)	-
Period Start End	Date, Blank If Zero	BiF@Time(5)	-
Date Validation	None	IF Date>={Period Start Date} AND Date<=({Period End Date}-1) THEN 1 ELSE 0	-
Salary Condition	Noen	BiF@Last(Date Validation)	-
Salary/Month	None	-	From HR Info Cube
Final Salary Amount	None	{Salary Condition}*{Salary/Month}	-

Cube Example:-

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Date of Joining	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010	31/12/2010
Period Start Date	01/01/2010	01/02/2010	01/03/2010	01/04/2010	01/05/2010	01/06/2010	01/07/2010	01/08/2010	01/09/2010	01/10/2010	01/11/2010	01/12/2010
Period Start End	01/02/2010	01/03/2010	01/04/2010	01/05/2010	01/06/2010	01/07/2010	01/08/2010	01/09/2010	01/10/2010	01/11/2010	01/12/2010	01/01/2011
Date Validation	0	0	0	0	0	0	0	0	0	0	0	1	1
Salary Condition	0	0	0	0	0	0	0	0	0	0	0	1	1
Salary/Month	500	500	500	500	500	500	500	500	500	500	500	500	6000
Final Salary Amount	0	0	0	0	0	0	0	0	0	0	0	500	500

through this apporch u can also calculate salary for each day if u have working day based calculation...
the only overhead is that there will be an annual maintenance activity to change time scale for Salary Calc cube

I hope it helps you this time...

Hi Jitesh,

First of all, sorry for the delay!

You are really superb and thanks for helping.

Nice to hear that it worked for you this time(uuufffff)... ;D

Now the question for which you had aksed for...
if ur company does planning for Jun 2010 -Jul 2011, no issue same logic will work for it just start ur month dimension with Jun 2010 as 1st month and change accordingly the timescale info....

about calculating salary for the person who joined in Apr 2010...you can not do this with same logic.. as its only for calculating salary for new joinees of budgeted year..

now there are several ways to handle it...
1> you create month dimension which has more than one year like Jun 2009 - Jul 2011. this will calculate salary for Apr 2010 joinee.

2> which I feel is the correct case is that this Apr 2010 employee is an old employee of a company and will have entire years salary so calculate his salary for entire year blindly...... may be you can put condition in ur HR cube like
IF date of joining < starting date of period(1st Jun 2010 in over example) then calculate entire years salary else 0
and then flow this salary amount directly to Finance cube
Or
transfer monthly salary for all months to Finance cube


3> Tweak the existing condition for Data validation in Salary Calc cube
something like below
IF Date of Joining <=({Period End Date}-1) THEN 1 ELSE 0
this will removing the opening condition and will calculate salary for older employee as well...

But Preethi if you are building a proper HR module(seems from looking at ur requirement) which involves calculation of salary for old employee, new joinees and retired employees... then this is not the perfect model to work with...
I don't know know ur exact requirement so can not say much...

let me know how this time it works...

(I wont be accessing this forum for next few days, but you can send me email on my personal id(mentioned is profile details) i will reply back as soon as possible)

Hi Sir,

Thanksssssssssssssssssssssssssssssssssssssssssssssssssssssss for sharing the valuable suggestions and ideas...You are right, there was some confusion on the way users told us. Now, its corrected.



Thanks once again... :)


Preethi