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
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