asked on

sql for crystal report - running total

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.

James0628

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

johnmadigan

ASKER

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,

Mike McCracken

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

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnmadigan

ASKER

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.

johnmadigan

ASKER

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.

johnmadigan

ASKER

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?

Mike McCracken

Is your date field a date or a string?

Check what Crystal thinks it is.

mlmcc

James0628

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