?
Solved

sql for crystal report - running total

Posted on 2015-02-19
9
Medium Priority
?
229 Views
Last Modified: 2015-04-01
I have an invoice list report that I run in ms access that I need to run in Crystal.  I us ms scheduler to run this report Tue-Sat.

I was hoping for some help with the SQL in Crystal.

There are 3 parts to the report - I figure the last 2 sections can be done with sub-reports:

1) list all invoice data for previous day. (report is scheduled to run on Tues - so it lists all invoices done on Mon.)

2) Sub-report - Totals for days of the week other than yesterday.

3) Sub-report - running total for all days on the report.

Here's the output for a report run on Thru. 2/19/15

Date               Company      Inv#     Amt
2/18/15          ABC                125       200
2/18/15          DEF                126       200
2/18/15          ABC                127       200

2/18/15          TOTALS                        600
2/17/15          TOTALS                        425
2/16/15          TOTALS                        500

                        WK TOTAL                   1,525

Not to sure about how to set up the SQL - I was using Date() & Weekday().

Any suggestions on where to start?

Thanks for your help.
0
Comment
Question by:johnmadigan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 40620901
Normally you don't need to write any SQL.  You create a new report, create a connection to the db in CR, add the required tables to the report and define the links between them, select the fields that you want to see and place them on the report, etc.  CR generates the SQL required to get those fields from the tables.

 To limit the data to the previous day, you could go to Report > Selection Formulas > Record and enter a record selection formula.  If the invoice date field is just a date (not a datetime), you could use:
{Invoice date field} = CurrentDate - 1

 CurrentDate - 1 just subtracts 1 day from today's date.


 If the field includes a time, you could use something like:
{Invoice date field} >= DateTime (CurrentDate) - 1 and
{Invoice date field} < DateTime (CurrentDate)

 DateTime (CurrentDate) gives you midnight today.  So, that's looking for a datetime from midnight yesterday to before midnight today.


 But what happens on Monday?  Do you want to see the invoices for Friday?  If so, the formula would have to be modified to handle that.

 If you have subreports, you could use similar tests for those, but I'm not sure exactly what you want to show in the subreports.

 James
0
 

Author Comment

by:johnmadigan
ID: 40621047
Thanks for your help with this.

we start running the reports on Tues. because during the day on Mon. they are adding invoices to the table.  so we run the report on Tues. morning to give us the invoices booked on Monday.

So we run the report on Sat. to see the invoices booked on Friday - hence running the report Tue. - Sat.

{Invoice date field} = CurrentDate - 1  is what I was looking for - this is similar to what I used in my access report to filter for the invoices for the previous day.  Thanks,


So how do we filter to get the totals for each day before yesterday & filter to get a running total for the week?  The 2 other parts of my report.

Thanks,
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40621165
Try this.

Select the data for the current week
{InvoiceDate} >= CurrentDate - DayOfWeek(CurrentDate)

Add a group to the report on the invoice date
Set the sort to be descending
Put the fields into the detail section
Right click the amount field
Click INSERT --> SUMMARY
Set the summary to be total
Put the summary in all group footers
Right click the detail section in the left  margin
Click SECTION EXPERT
Click the FORMULA BUTTON to the RIGHT of SUPPRESS
{InvoiceDate} < CurrentDate - 1
Click SAVE AND CLOSE

mlmcc
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 40622247
Referring to your first post ...

 For #2, you said that you wanted totals for the days "other than yesterday", but the daily totals in your example include 02/18, which is yesterday in that example.  Do you want those daily totals to include yesterday or not?

  For #3, do you really want a "running total"?  In CR, a running total is an ongoing total.  For example, if you had invoices for 3 days, the running total for day 1 would be the total for that day, for day 2 it would be the combined total for days 1 and 2, and for day 3 it would be the total for all 3 days.  It looks like you just want a total for the week, not a "running total".


 
 I think mlmcc's suggestion will work, but it may need some tweaking, depending on your answers to the questions above.  To explain what he's doing, rather than use subreports, he's having the report read the records for the week, but hide (suppress) all of the invoices (details) that are dated before yesterday.  You'd see the invoices dated yesterday, and then just totals for the days in the week before that.  You'd need to add another summary to get the grand total for the week at the end.  And within the invoice date group, you'd probably want to sort by invoice #.

 FWIW, I think I'd change the record selection formula to use >, instead of >= :

{InvoiceDate} > CurrentDate - DayOfWeek(CurrentDate)


CurrentDate - DayOfWeek (CurrentDate) will give you the last day of the previous week (ie. Saturday), so >= would include Saturday.  If you don't have any invoices on Saturday, then it doesn't really matter, but I'd use > to start on Sunday, just to play it safe.

 James
0
 

Author Comment

by:johnmadigan
ID: 40622391
sorry I forgot that for the #2 part of the report I would have a totals for every day of the week including yesterday.

Let me try your suggestions to see where I get.

Thanks - I'll let you know if I get stuck.
0
 

Author Comment

by:johnmadigan
ID: 40623108
I am having a problem with using the Record selection filter for the date.  {Invoices_csv.TransDate} = CurrentDate does not return any values but {Invoices_csv.TransDate} = #2/21/15# shows the records with 2/21/15 which is the current date.

I did a formula using CurrentDate and added it to my report and it returned a value of 2/21/15.  

Any suggestions?  I know when I am working with dates in access is can be fun.
0
 

Author Comment

by:johnmadigan
ID: 40623146
When I checked the SQL when I entered {Invoices_csv.TransDate}  = CurrentDate    it

shows ----     WHERE (Invoices_csv.TransDate = {d '2015-02-21'})

my date in the data table is in the format 2/21/2015  could this be why I am not returning any values on the report?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40623170
Is your date field a date or a string?

Check what Crystal thinks it is.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40700825
FWIW, if you used mlmcc's suggestion (to suppress the records that are dated before yesterday), he should get some, if not most, of the points.  I just tried to explain his suggestion, and added some possible refinements.

 James
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question