Crystal Reports Calendar Report - Almost Working

[erp84]

Happy Thursday everyone!

I just completed my calendar report based on LBASS's method discussed here:

http://www.tek-tips.com/viewthread.cfm?qid=1475890

It is working beautifully so far except in the case when I do not have any records for a particular week, or the entire month. If I do not have any records in a week, it will not display this week, same if my entire month doesn't have any records it will throw some ugly array errors.

Any suggestions what to look for?

 

[PeterDimitrov]

You need a table with all dates in the period left joined with your data tables

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[erp84]

I used the dynamic method, so it shouldn't require an excel sheet, or table.

 

[CoSpringsGuy]

erp84 - I would love to hear how your dynamic method is put together

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[erp84]

Read through the link in my first post. Linda Bass walks through it quite nicely.

 

[CoSpringsGuy]

Im quite familiar with LBass explanation regarding this and as far as I know she suggests either adding a table in your own database to link to or an excel spreadsheet Peter above suggests.

In order to create this type of report, you will need to add a simple calendar table to your database. As a minimum, this table consists of one column, with a row for each day of the year to which the calendar will apply. The rows contain consecutive dates within the year.

I didn't recall her mentioning a dynamic method which is why I asked

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[PeterDimitrov]

Then obviously this is a queasion for lbass.

If you are interested to implement a dynamic table you can see an example for SQLServer here:

http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx

The function fnGetDatesInPeriod ( in the top right corner of the page) will return a table with all dates between @FromDate and @ToDate

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[CoSpringsGuy]

Thanks for that clarification Peter

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[erp84]

Ok, I got it working!

For some reason I was filtering the table I used in my main report with a select and that caused some problems. Once I removed the select all is good!

 

[lbass]

CoSpringsGuy,

Here is the link to the dynamic method: thread149-1475890 which does not require a date table.

-LB

 

[CoSpringsGuy]

Thanks LBass ... I followed along this topic a few years ago as you helped someone else and somehow I missed that. Thanks for the link .. Hope you have been doing well! I have missed your posts on here lately...

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

Thanks. I'm fine--I just have a new more demanding job that is taking up all my time, so I can only drop in here occasionally.

Best,
LB