My requirment is ..........

[boani]

Hi all,
My univ has the foll obj:
1.Busid
2.Date
3.Amt
I want a report as crosstab with foll info:
1.Busid as row title
2.week(using the function'week(date)' as col title
3.amt at corr to a particular Busid and week

The prob i face is that if i do not have any entries for a week,that particular week itself is not displayed in the report......where as what i require is that ,the week be displayed with zero/null as entries.can anyone find a sol for this situaton.

Thanx in advance,
boani

 

[blom0344]

Hello Boani,

Probably best solution is to create a 'dummy' table (calenderfile) for 1 busid with same fields as you retrieve from your current table. Then expand the original query to a union query with the appropiate condition on the second correlated query. This will give you a recordsset which contains dates for all weeks. Of course the amt in the dummy file is 0 for every entry.

Anyway getting a calenderfile introduced into the universe is always a nice thing to have ......... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com