Hello Guys,

I am a newbie and stuck on this problem of getting YTD in my report. My report has the following fields:

<Employee>       <Title>      <date> <Other related fields>     <Leaves>    <YTD Leaves>

Fields on this report are grouped by the Employee field. The leave field basically captures the category of the leave i.e. if it was a sick leave or a scheduled leave etc. I want to the calculate the total number of leaves taken till date irrespective of category in the <YTD> column. So, ideally I would want the column to get updated automatically as the new data for the current date is added and to not change if there is a filter put on the Date field.

I know, I will have to use a formula but am not sure where to start, so please help me out here..


Any help is highly appreciated.


Thanks,

Thanks for replying. However, this is what I am trying to achieve. I wrote this formula for YTD. Its working, however it is not taking into account the groups. i.e. after placing this formula in the detail section of my report, I get YTD Leaves corresponding to each date, but it continues adding across the groups (Employees). Something like this:

Employee  Date  Leaves  YTD
A                2/1    1             1
                  2/2    1             2

B                2/1    1             3  (This should be 1)
                  2/2    1             4  (This should be 2)


Formula, I wrote is

Numbervar YTD;
if {Date} in YearToDate then
YTD := YTD + Leaves


I am not sure if I can achieve what I am trying to achieve using Running Total. Please suggest if there is a way to achieve the aforementioned either through a formula or Running Total.

Thanks,

My "Leave" value is numeric. The field contains "1" if someone took a leave. So, basically carrying a cumulative addition.  All, I need to get is a way to reset the value when it reaches the end of group. I am kind of bent on using this method since I need to calculate MTD too.

As far as your second question is concerned, I am not sure if I get it. My data is pretty simple. I have Employee name, date and the field "Leave" which will contain 1 if an employee took a leave. Something like this.

<Employee>     <Date>     <Leave>
A                          2/1/10        1
                            2/2/10        1
                            2/3/10        0

B                          2/1/10        0
                            2/2/10        0
                            2/3/10        1

So, this basically means employee A was absent on 2/1/10 & 2/2/10 while B was absent on 2/3/10. I tried the Running Total method and its working especially resetting for each group. However, I am slightly bent on using the variable method since I need to calculate MTD too. I could add another group for Month and use RT for MTD, but I guess it will be easier for me to use the formula if I can somehow get a way to reset my variable at the end of each group.

Thanks,

For YTD Running Total:

Edited by DBlank - 08 Feb 2010 at 11:27am

create another variable and place it in the group header