Using Dates as Field Headers 1

[FlyerNut]

I created a report that is based on 1 week of data, I created a case statement in sql that will give me the amount of records for each date (this is based on 7 days), what I'd like to do is have the date as the field header, e.g.

1-1-04 1-2-04 1-3-04

25 20 35

and so on.

I don't like Crystal's cross tab report, anyone have a suggestion?

 

[synapsevampire]

You'll have to create manual formulas.

One means is to create Running Totals, and use the Evaluate->Use a formula to supply the date info.

Since you didn't share the fields returned, rather a text description of what they are, I can't supply specifics. Try to supply:

Crystal version
Database/connectivity used (we may have been able to offload this to the database)
Example data
Expected Output

Rather than a description.

Assuming that you always have 7 consecutive days of data you might use:

For the first day use the following in the Evaluate Use a Formula for each Running Total:

{table.date} = minimum({table.date})

For the second day:
{table.date} = minimum({table.date})+1

etc.

Then for the label use:

First day:
minimum({table.date})

Second day:
minimum({table.date})+1

All data would be displayed at the report footer level.

If you need it to display before, you could drop the date field in to the report, right click and select Insert->Grand Total->Minimum

This will create a formula that can be referenced by other formulas throughout the report.

-k

 

[FlyerNut]

K,

Thanks for the reply. Here's a piece of my query:

select jbtb.jbid, sitb.sina,
case when datepart(dw,altb.alts)= 4 then altb.alts else null end as Wed,
case when datepart(dw,altb.alts)= 5 then altb.alts else null end as Thurs,
case when datepart(dw,altb.alts)= 6 then altb.alts else null end as Fri,
case when datepart(dw,altb.alts)= 7 then altb.alts else null end as Sat,
case when datepart(dw,altb.alts)= 1 then altb.alts else null end as Sun,
case when datepart(dw,altb.alts)= 2 then altb.alts else null end as Mon,
case when datepart(dw,altb.alts)= 3 then altb.alts else null end as Tues,

intb.pbid, pbtb.pbna, intb.zgid, zgtb.zgna,
altb.alts,intb.indl, intb.indt as mindate, jbna from jbtb
join jptb on jptb.jpid = jbtb.jbid
JOIN intb ON intb.InId = (select min(intb.inid) from intb where intb.JpId=jptb.JpId
and intb.InDt = (select min(intb.InDt) from intb where
intb.jpid=Jptb.jpid))
join altb on altb.alid = (select min(altb.alid) from altb where altb.jpid=jbtb.JbId
and altb.alts = (select min(altb.alts) from altb where
altb.jpid=jbtb.jbid and stid = 10020 and altb.alts >= @start and altb.alts <= @end))
join pbtb on pbtb.pbid = intb.pbid
join zgtb on zgtb.zgid = intb.zgid
join fstb on fstb.fsid = jptb.fsid
join smtb on smtb.smid = fstb.smid
join sitb on sitb.siid = smtb.siid


where InDt >= @start and Indt <= @end


I'm using Crystal 9, against a SQL 2K db.

Example data:

sina Wed Thurs Fri Sat Sun Mon Tues
---- --- ----- --- --- --- ---- ----

RDS Null 2004-4-8 Null Null Null Null Null
RDS 2004-4-7 Null Null Null Null Null Null


Expected Output

Page Header 4/7/2004 4/8/2004 4/9/2004 and so on

Group - RDS
1 1


Tony