Help ASAP Please

[MrHelpMe]

Hello,

I was wondering if someone could help me urgently. I have created a cross tab with days of the week across columns and names in the rows. Through an ASP page, the user is allowed to select week of. Therefore, for example, the user chooses the week of Nov. 20, 2002. This day is a Wednesday. I need the report to show the Monday to Friday regards of what day during the week the user chooses. In this example, my report is capturing Monday, Wednesday, Thursday, Friday. I see why it is doing this. It is doing this because I used a specified group option and said if day = 2 then tuesday, if day = 3 then wednesday etc. How do I get the days of the week, from Monday to Friday to show across columns regardless if there is any data or not.

Also, along the same lines, how do I add a note(text) if no data exists(blank report for week of). Something like No data exists for that week in a crosstab. Thanks everyone and hope you can help.

 

[synapsevampire]

There's no simple solution here, as you don't have the data, but you want it anyway.

I always suggest that a reporting database contain a Periods table, which has all dates already defined.

So when you need to query for a set of dates that are not in your data, use the periods table, filtered using your date selection, and then link your tables to that.

Some write stored procedures or use other tricks to accomplish this, but it's resolved forevermore here using a standard data warehousing concept.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com