Function to display missing dates

[danstaunton]

Hello, I was wondering if someone can help me with a very tricky Crystal Reports issue.

My report takes data from our helpdesk package and provides a summary of our helpdesk activity for the last 8 days. I have attached a screen shot of how the report looks when it is working.

The problem I have is that if no calls are logged within my helpdesk system on a particular day. Then my graph shows a gap for that date as does my crosstab.

I was wondering if there is function that can find the missing date in the 8 day range and populate it within my report. All I want is to be able to display a 0 for this date.

Thanks in advance for your help

 

[RTag]

what is the database type ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[danstaunton]

The database type is MYSQL

 

[RTag]

There is a similar active thread in experts-exchange. Did you post it? I don't want to repeat what was said there if you already tried it.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[danstaunton]

Yes I posted it, the question was answered correctly however I cannot apply the formula so that it work on charts / cross tabs

 

[RTag]

I cannot say more than I said there.
However if you want to see an example in SQLServer here is one:

http://r-tag.com/PivotSample.aspx

The procedure [dbo].[fnGetDatesInPeriod] (in right) will generate the dates table. Left join between this table and your data will return the table with all data that you can use to create the crosstab and the chart.

The example goes further creating the crosstab in SQLserver which you don't need to do.

If you have SQLServer (even express version) you can retrieve the data from your database and process it with the options provided by SQLServer. I don't like creating dummy layer just to be able get some data , but if you don't have other options ...

Did you try to generate a table in MySQL with all dates ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[danstaunton]

I'm not sure how to do the equivalent in MYSQL, and even if I did I don't know how I could join it to my data.

The difficulty I have is that my dates are store as a string and in this format dd/mm/yyyy hh:mm:ss , this makes it hard to join any data to the report. If I want to do anything I first have to use a formula to convert it to a date i.e date({table.field}) this would then convert my field to dd/mm/yyyy.

 

[RTag]

You need just the date portion (without the time)

In SQLServer the left join will look like this:

SELECT *
FROM <GeneratedDates> gd
LEFT JOIN <YourData> yd ON gd.Date = CONVERT(DATETIME,LEFT(yd.Date,10))

As I mentioned in the other thread <GeneratedDates> could be a hardcoded table which contains all possible dates. It will be left joined with your data.
Conversion of the string date could be done by cutting the left 10 chars: LEFT(yd.Date,10) which should return dd/mm/yyyy and convert this to a datetime: CONVERT(DATETIME,LEFT(yd.Date,10))

In MySQL the syntax will be different . You need to find replacement for 2 function : LEFT and CONVERT (to datetime)

May be somebody else will propose a crystal reports solution.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.