Group by week and include gaps in the date range

[beacon5]

Hi everybody,

[Specs: Crystal Reports 8.5]

I've seen similar posts that deal with the question I have, but the situations in the other posts were just a little different, were slightly vague, or the users had access to components of their system that I don't have.

On my report, I pass in a start and end date range. For this date range, I would like to group by each week in the range, even if there are no details for the week in question.

So if my date range is January 1 to March 31, 2009, my groups would look like the following:

January 4, 2009
No details
January 11, 2009
January 13, 2009 | 47 | Out of range
January 15, 2009 | 11 | In range
January 16, 2009 | 22 | Out of range
January 18, 2009
No details
.
.
.
March 29, 2009
March 30, 2009 | 17 | In range

I tried using the group by week feature, but I need the weeks to appear even if there are no details to work with and this option didn't work. It only showed those weeks that actually had data in them (like Crystal Reports was designed to do, I know).

In the other posts I saw, they mentioned creating a Calendar/Period table. I don't know how to do this, so if someone would explain it to me, I'd be happy to give it a shot.

It appeared in the posts that this Calendar/Period table was being created in the same database that the user was reporting on. In my situation, I don't have access to create tables, so I'd have to use Access or Excel...except that I'm not sure I can link to multiple data sources if that's necessary.

Thanks for any help you can throw my way and please let me know if there's anything additional you need.

beacon

 

[lbass]

You can create the table in Access or Excel, and you should be able to link to other tables. You would use a left join FROM the new table to the date field in your regular table, and then the week group should be on your new table.

-LB

 

[beacon5]

Ok...so I created the table in Access, then I added the table to my report. When the Visual Linking Expert came up, I dragged the CalendarDates field (on my Calendar table) over to the AssessmentDates field and it's giving me all kinds of error warnings like...

"File link warning. The specified fields may not link successfully due to different type."

and

"The link you added causes a link configuration with multiple paths leading to the same table. You may want to consider reversing links."

and if I reverse the links

"Invalid file link. Not an indexed field."

The fields are both Date/Time fields, but it's possible that the field on the table I don't have much access to isn't indexed.

If I leave the link the same as it was initially and ignore the warning and try to adjust the link options and change the join, all of the join options are disabled.

Am I missing something?

Thanks again...

 

[lbass]

You should doublecheck the datatypes by browsing the fields in the linking expert to make sure they are both datetimes. The format might need to be the same, too--I'm unsure.

The new table should be your left-most table, and you should be linking it FROM it TO your other table. You cannot have more the one table pointing to the same table, so if you have other tables linked to old table, you will have to link FROM the old table TO these other tables.

I am not familiar with the indexing error.

-LB