INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

YTD Crystal Reports formula

YTD Crystal Reports formula

(OP)
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

RE: YTD Crystal Reports formula

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.

RE: YTD Crystal Reports formula

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

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close