Create Record when no records exist

[Tiffc0922]

I've written a sales report showing invoiced sales compared to the budgeted amount. This is then grouped by the post date by week, along with running totals that reset for each sales rep. The problem I'm having is that the running totals aren't updating since no sales/post date doesn't exist for that week.

What I'm trying to accomplish is to evaluate if sales exist for that week and if not I want to add a record display a zero so that we can show every week per sales rep regardless if there are sales or not.

 

[Charliy]

Do you have another file that will have dates even if there are no sales? If you do, then you can use it as a "driver" and link to the sales table (left outer join). Then group on the dates from the driver table.

 

[Tiffc0922]

Charliy,

Unfortunately I don't, is there any other way to go around this, some type of formula?

 

[ianoctdec]

I don't think a formula would help. You could use an Excel file that has all the dates.
If your database is Oracle 9i or higher (you need analytical functions), and you can write SQL commands in Crystal, you could write a query that will generates all dates. You would have to use an SQL command as datasource for the report too, as linking an SQL command to tables is slowing down considerably the report.
Below query (somebody posted it on Oracle forum)generates all dates starting with desired StartDate:
SELECT C.REF_DATE, TO_CHAR(C.REF_DATE, 'Dy FMDD/Mon/YY') AS REF_DATE_FORMAT
FROM
(SELECT TRUNC({?StartDate}) + N.DAY_COUNTER AS REF_DATE
FROM
(SELECT ROWNUM AS DAY_COUNTER
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <= SYSDATE - {?StartDate}
) T
) N
) C

You left join this query to the sales query by date, and you'll have all dates in your report.

Dana