Need 2 different totals for different time frames....Subreport?

[former_member406941]

Hi everyone!!

I'm trying to calculate a formula, {@Sum of Savings}, to show up as a year-to-date and a current month calculation at the end of my report.

Right now I have the {@Sum of Savings} in the Report Footer and it calculates correctly for whatever time frame I put in the Report > Select Expert > Record.  

Can I have the {@Sum of Savings} formula calculate, for example, a current month's worth of records (like March) as well as the year-to-date (like Jan-March) both in the Report Footer.....or somewhere else?  Would I need a subreport for this?

Thanks!!

Rich

[abhilash_kumar · Accepted solution]

Hi Rich,

To be able to display both YTD and MTD, you would need to make sure that the report returns the year's worth of data (the record selection would need to be modified if it already isn't bring back that required data).

Or, you could write a SQL Query (preferred) that returns just the data you need where YTD and MTD could be columns returned by the query - this way you wouldn't need a Subreport nor would you need to return a year's worth of data if you aren't really display all that info on the report.

-Abhilash 

[former_member406941 · Accepted solution]

Thanks Abhilash.  I'm not schooled in writing SQL unfortunately.  If I started out by having the year's worth of data returned on the report, would I then be able to summarize both somewhere.....in the Report Footer?  Would this require a subreport.....or it sounds like all the data would be displayed in the report when I'd only want the month's worth?

Thank you!!

[abhilash_kumar · Accepted solution]

You would just need two separate formulas - one that calculates the YTD and one for MTD.

1) The YTD formula would be:

IF {Date_field} IN YearToDate then

     {Measure_Field}

Next, go to Insert Summary > Choose the YTD formula field > Choose Sum as the summary function and place this summary on the Report Footer

2) The MTD formula would be:

IF {Date_field} IN MonthToDate then

     {Measure_Field}

You would then insert a summary on this formula field as well.

-Abhilash