Report Design: Sum with Date from Formula

Sum with Date from Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17525
Printed Date: 18 May 2024 at 2:11pm

Topic: Sum with Date from Formula

Posted By: eGeeked
Subject: Sum with Date from Formula
Date Posted: 10 Sep 2012 at 7:00am

I am relatively new to Crystal but I am learning quick. I am trying to have a SUM of a field appear using a specific date array built into a formula. Below I will show my code and explain things a little better.

These first 2 formulas get me the first day of the month and the last day. I have several of these for going back several months.

@11BackBeg


Local DateTimeVar d := {TimeRecord.ActualDateTime};
DateSerial(Year(d), Month(d) - 0 - 11, 1)

@11BackEnd


Local DateTimeVar d := {TimeRecord.ActualDateTime};
DateSerial(Year(d), Month(d) - 0 - 10, 0)

Now I have a formula to get me the range I want to pull from - I need a way to tell it where I want to pull from and for who, so I have.

@11Back Time


{TimeRecord.OwnedBy} = {?AS Team Member} and
 mailto:%7b@ActualDate - {@ActualDate } in  mailto:%7b@11BackBeg - {@11BackBeg } to  mailto:%7b@11BackEnd - {@11BackEnd }

So I need to make the SUM of the field display for in between these dates, I am unsure how to write the SUM function. I currently have whats below but need a little help making it work.


Sum ( mailto:%7b@F - {@F  AdminTime}, {TimeRecord.OwnedBy})

I appreciate all your help, thank you.

Replies:

Posted By: hilfy
Date Posted: 14 Sep 2012 at 8:56am

So, if I understand you correctly, you're trying to sum only the numbers that are between the specific start and end dates. Is that correct?

If so, you have a couple of options.

1. Create a formula and then sum it. The formula would look something like this:

If {TimeRecord.OwnedBy} = {?AS Team Member} and {@ActualDate} in {@11BackBeg} to {@11BackEnd} then

{@F AdminTime} else 0

2. Use a running total that evaluates on the formula

{TimeRecord.OwnedBy} = {?AS Team Member} and {@ActualDate} in {@11BackBeg} to {@11BackEnd}

and resets as needed.

If you only need the summary at one group level I would use the running total, if you need it at more than one, I would use the formula/sum. This is because you would have to create a separate running total for each level where the summaries would require just the one formula and can be set up for all group levels at the same time.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics