problem with calculating cumulative payroll for a week in a year

[Desperado]

Hi All,

I have a report which have a prompt on a particular column called payroll. The data in payroll will be the paycycle names. We have weekly paycycle. so, the data will be something like this 23 ,23.1,23.2,23.3,23.4,24,24.1,24.2,24.3,24.4,25, ....for all the weeks in a year.

In my report i have to display two fields.
1. amount for current pay cycle. ( i created a section on payroll and as it is a prompt, the user selected prompt, i'm getting the value correctly

2. Cumulative Pay cycle  ( for this the data should be the aggregate for the paycycle selected by user.
i mean, if the user selects 23.2 from the drop down in prompt page, then it has to display the aggregate(amount) for particular group and for the paycycle of 23,23.1,23.2,23.4,23.4)

the problem for me is , as i'm using the prompt for payroll the data is restricted only to the pay cycle period 23.2 (in the above scenario)

if i'm not using the prompt for the paycycle, everything is working fine, i can do the cumulative paycycle column. But the user wants to have payroll as a prompt.

Please do the needful

Thanks in Advance

[trevorp]

Hi

Try creating a new data item in the report and use this as the Cumalative pay value

if (payroll week no <= payroll week no prompt) then (value) else (0).


The total of this value should then be the cumulative payroll value

[Desperado]

Thank you for the response. I tried it earlier. But it dint work . I think it's because of the payroll prompt that i have in my report. We are trying to finish the report using a stored procedure now.

[MFGF]

Hi,

You could probably achieve this by adding a second query to your report which retrieves the cumulative payroll for the week.  Add a calculated item that retrieves the prompt value from the parameter, and use this to join the query to your main report query.

Regards,

MF.

[Desperado]

I attached a second query and it worked for me . But the problem is i have 3 more columns called Current Fiscal Year (YTD)
Previous Fiscal Year Thru Same Payroll
Previous Fiscal Year Total.

How do i calculate these if i have to do these using report studio only?
do i need to create a query for each of the column to be populated?


Thanks in Advance

[MFGF]

Hi,

Yes, you would need to take the same approach for each of the other columns you require.

Regards,

MF.

[Desperado]

I was thinking that it will effect the performance of the report...  But seems i have to do it that way only. I'll try it. Thank you :)