Display 0 value in a chart 1

[Simon9999]

Hi

I am using Crystal Xi.

I have a chart that displays the number of sales won in each month. The sales are grouped by month on the X axis and the count is shown on the Y axis.

The trouble I have is where there are no sales, the month is not shown. I would like the grouping still to be shown even though there are no sales so that I dont keep getting asked what has happened in the 0 month.

Any suggestions?

Thanks

Simon

 

[synapsevampire]

The easiest and common means is to fabricate the data.

Please post your database and version.

It might also prove beneficial to include the Database->Show SQL Query

-k

 

[Simon9999]

Thanks

I am using a SQL server, not sure of version but sql code is:


SELECT "Opportunity"."Opportunity ID", "Opportunity"."Project Name", "BidEventDates"."Submission", "BidEventDates"."Result", "Opportunity"."Opportunity In Use", "BidEventDates"."ResultCompleted", "BidEventDates"."ITNIssueCompleted", "Opportunity"."Status", "BidEventDates"."SubmissionCompleted", "Opportunity"."AIS/ABS-ROW", "Opportunity"."Sub Status Description", "Opportunity"."Business Stream Desc"
FROM "BidPipeline"."dbo"."Opportunity" "Opportunity" INNER JOIN "BidPipeline"."dbo"."BidEventDates" "BidEventDates" ON "Opportunity"."Opportunity ID"="BidEventDates"."Opportunity ID"
WHERE "BidEventDates"."SubmissionCompleted"=1 AND "Opportunity"."Opportunity In Use"=1 AND ("BidEventDates"."Submission">={ts '2006-09-01 00:00:00'} AND "BidEventDates"."PQQ-Submission"<{ts '2007-09-01 00:00:00'})
ORDER BY "Opportunity"."Business Stream Desc"

I have set null values to 0 in report options.

Would appreciate anmy hel

Cheers

Simon

 

[synapsevampire]

A Union is a common solution, check with your dba for more details.

Crystal allows for real SQL to be pasted in under tyhe connection type using Add Command.
SELECT 0 as 'Opportunity ID', ' ' as 'Project Name' , minimum("BidEventDates"."Submission"_, 0 as 'Result', ' ' as 'Opportunity In Use', getdate() as 'ResultCompleted', ' ' as 'ITNIssueCompleted', ' ' as 'Status', ' ' as 'SubmissionCompleted', 0 as 'AIS/ABS-ROW', ' ' as 'Sub Status Description', ' ' as 'Business Stream Desc'
FROM "BidPipeline"."dbo"."BidEventDates" "BidEventDates"
WHERE "BidEventDates"."Submission">= {?MyStartParm}
group by year("BidEventDates"."Submission"), month("BidEventDates"."Submission")

UNION ALL

SELECT "Opportunity"."Opportunity ID", "Opportunity"."Project Name", "BidEventDates"."Submission", "BidEventDates"."Result", "Opportunity"."Opportunity In Use", "BidEventDates"."ResultCompleted", "BidEventDates"."ITNIssueCompleted", "Opportunity"."Status", "BidEventDates"."SubmissionCompleted", "Opportunity"."AIS/ABS-ROW", "Opportunity"."Sub Status Description", "Opportunity"."Business Stream Desc"
FROM "BidPipeline"."dbo"."Opportunity" "Opportunity" INNER JOIN "BidPipeline"."dbo"."BidEventDates" "BidEventDates" ON "Opportunity"."Opportunity ID"="BidEventDates"."Opportunity ID"
WHERE "BidEventDates"."SubmissionCompleted"=1 AND "Opportunity"."Opportunity In Use"=1 AND ("BidEventDates"."Submission">={?MyStartParm}

Not sure why you're yusing two different date fields here, but use parameters or whatever...

AND "BidEventDates"."PQQ-Submission"<{ts '2007-09-01
00:00:00'})
ORDER BY "Opportunity"."Business Stream Desc"

Anyway, if you grasp SQL, you should be able to leverage the above.

Note that when using a command object you create procedures within that screen. Once you create it, insert it directly into the SQL.

Not sure how your dates were set, parameters, hardcoding, derived, etc...

-k