Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show a line of data for all days in a month even if there is no raw data

Status
Not open for further replies.

roena26

Programmer
Aug 23, 2013
7
US
I have a report that I created in Crystal Reports 2008 which connects to a SQL database.

I am reporting on dollar amounts over a one-month time frame and a summary of the total is displayed by date.

I would like to display all 30 days in June, even if there is no raw data for the date. For example, we're not open on Sundays so there should be no dollar amount for that day although I would like it to be displayed.

I do have a table in my SQL database that lists every single date in the calendar for several years that I can use. I tried placing this table "DATE" as my left-most table and linking to the table with the business date, we can call it "AMOUNT". I updated all of the groups and group selection formulas to use the DATE table instead of the AMOUNT table.

When I view my report I only see the days that there were transactions in the AMOUNT table.

I cannot use a COMMAND statement in this report as I am using SQL Expression fields in the report.

The joins:

FROM DATE
LEFT OUTER JOIN AMOUNT ON DATE.CALENDAR_DT=AMOUNT.BUSINESS_DATE
 
Make sure to check for nulls first if you use fields from AMOUNT table in the formulas
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top