Detail Dynamic Date 3

[RobNauticus]

Hello all, I am trying to write a report (CR 8.5) that will show a dynamic date range (given from parameters at runtime).





Example: (Parameter = startdate(6/13/05), enddate (6/18/05))

Group1 - Foreman Name

Detail -
6/13 Job#, Job Name, Task1, Task2, Task3
6/14 Job#, Job Name, Task1, Task2, Task3
6/15 Job#, Job Name, Task1, Task2, Task3
6/16 Job#, Job Name, Task1, Task2, Task3
6/17 Job#, Job Name, Task1, Task2, Task3
6/18 Job#, Job Name, Task1, Task2, Task3

I need all of the dates to show even if there is no data to display for that date.

Each of the tasks are date fields that one of the 3 must fall within the range specified.

Preferably I should create a group for each date so there can be multiple Jobs per date if necessary.

I am in the process of trying to create a dynamic array that is fed from a for loop creating a cell for each day then I will spit it out to the group definitions. I am not too good at coding so I am not sure if I am headed in the right direction.

If anyone can offer any suggestions, they are warmly welcomed.

Thanks so much
Rob M.

 

[lupins46]

'I need all of the dates to show even if there is no data to display for that date.'

Not possible. CR only reports on data that is present.
If you want zeros in your results you will have to create a table of required dates and use that as part of your query.

 

[kwalters110]

Does your database have some sort of Time Dimension in it (a table that is a list of possible dates)? Most data warehouses have them, but not all.

If so then you can use a left outer join to relate that to the date in your job table. Then you could use a formula to show zeros if your records for jobs are null.

 

[RobNauticus]

No the database does not have any time dimension to it, I was hoping to create that with an array (or something) to create a mini-time table. I want thet array to be based upon the parameters given.

The table is flat that this data is coming from, maybe I am looking too deep into this problem?

 

[kwalters110]

Well I don't know much about arrays in crystal, but is there any chance you could create a time dimension using an Access Database and adding that to your report?

 

[campsys]

RobNauticus,

Here is another option if you do not have a table containing all dates.

Take a look at lbass's post in thread767-526332
stamped 20 Jul 03 17:46

Thread title:
A real puzzler - displaying database 'gaps'......
thread767-526332

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

It is a little complicated but works great for displaying all dates in a parameter range even when no data exists for a specific date.

Crystal 8.5 does have a limit on the size of a string but since I converted my report to Crystal 10 there is no problems with exceeding the size of the string. I can run a report for 6 months and all dates display on the report regardless of if there is any data for that date.

 

[RobNauticus]

That is a good start! I am wondering if that solution will work for me since it does not create an additional record for each date. In other words how do I get the other fields to correspond to the date printed?

BTW - You guys are great, I really appreciate the help!