YTD Crystal Reports formula

[MyCrystalBoy]

Hi all,

I am creating a report with Crystal Reports V.11 but I am stuck with the YTD. Anyone can help me how do I have the dynamic YTD on my the report by monthly?

Here is the scenario:
In the database, there are couple fields that I am using on the report. They are:
1. {Year}
2. {period}
3. {Debit}
4. {Credit}

Here are the data from database:
Year Period Debit Credit
2015 01 $100 $500
2015 02 $150 $400
2015 03 $200 $700

To get the profit, I have a formula for called profit = round({credit}-{debit})
So based on above data, my profit in
2015 01 is $400
2015 02 is $250
2015 03 is $500

What I am trying to achieve is, if I run this report on January
I should have this layout with correct YTD amount:
Year Period Profit
2015 01 $400

and if I run the report next month, which is February, I should have this layout with correct YTD amount:
Year Period Profit
2015 02 $650

and...if I run the report on March, I should have this layout with correct YTD amount:
Year Period Profit
2015 03 $1150

so, let' say if I want to re-run the report of February in May, I should get the layout and correct YTD amount like this:
Year Period Profit
2015 02 $650

Anyone can help me to do this YTD? Thank you all

 

[LaurieHamlin]

Create a formula called Profit - in it put credit - debit.
If your report is grouped on period, put the fields in the detail and suppress the detail section. Summarize the fields and either subtract the summarized totals, or put the Profit formula in the detail too and summarize that. In order for the total debit and credit fields not to show, just suppress the totals.
I hope I understood correctly and this helps.

 

[PeterDimitrov]

I am not sure I understand the question, but if you need to rerun the report as of specific date ( your example for February in May) then you need to provide a parameter AsOfDate.
The record selection formula will something be like
table.Year<=Year({@AsOfDate}) AND table.period <=Month({@AsOfDate})
You will need to group by Year and Period and use MAX for them and a sum on the profit formula as LaurieHamlin explained.

You can also consider to create a Command and to have a sql like

SELECT MAX(Year) as Year, Max(Period) as Period, SUM( Credit-Debit) as Profit
FROM ....
WHERE Year<=Year({@AsOfDate}) AND Period <=Month({@AsOfDate})

This will allow you to avoid groups in reports

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.