kpax
asked on
Hourly Cross-Tab Report
Hello,
I need to create an hourly report regardless if the hour has the data or not.
I'm using a dataset to populate my cross-tab report and using Crystal Reports 10.
I was trying the following formula but it does not give me the correct results.
if Hour ({CrystallDeviceImpression s.PrintSta rtDate_dt} ) = -1 then //no hour in db
Hour (Time(0,0,0)) // return 0 hour
else
Hour ({CrystallDeviceImpression s.PrintSta rtDate_dt} ); // return hour from date
this is the format I need it in:
Hour Device1 Device2
24 xxxx xxxx Total Impressions
23 xxxx xxxx
22 xxxx xxxx
...
2 xxxx xxxx
1 xxxx xxxx
The only best way to show this kinda report would be a cross-tab report correct?
Thanks
kpax
I need to create an hourly report regardless if the hour has the data or not.
I'm using a dataset to populate my cross-tab report and using Crystal Reports 10.
I was trying the following formula but it does not give me the correct results.
if Hour ({CrystallDeviceImpression
Hour (Time(0,0,0)) // return 0 hour
else
Hour ({CrystallDeviceImpression
this is the format I need it in:
Hour Device1 Device2
24 xxxx xxxx Total Impressions
23 xxxx xxxx
22 xxxx xxxx
...
2 xxxx xxxx
1 xxxx xxxx
The only best way to show this kinda report would be a cross-tab report correct?
Thanks
kpax
ASKER
frodoman,
Sorry for this late reply.
In your second para you said, insert a zero value record for each hour for the sum, how do I go about doing this? what kinda formula do I use?
I'm simply passing a dataset and the cross-tab report comes out like this :
hour device1 device2
8 xxx xxx total impressions
9 xxx xxx
12 xxx xxx
14 xxx xxx
23 xxx xxx
I simply want to put zeros in the report even if it has no data kinda like this..
hour device1 device2
1 0 0
2 0 0
...
8 xxx xxx total impressions
9 xxx xxx
12 xxx xxx
....
23 xxx xxx
24 0 0
can you help me with a formula that will return zero totals?
Thanks
kpax
Sorry for this late reply.
In your second para you said, insert a zero value record for each hour for the sum, how do I go about doing this? what kinda formula do I use?
I'm simply passing a dataset and the cross-tab report comes out like this :
hour device1 device2
8 xxx xxx total impressions
9 xxx xxx
12 xxx xxx
14 xxx xxx
23 xxx xxx
I simply want to put zeros in the report even if it has no data kinda like this..
hour device1 device2
1 0 0
2 0 0
...
8 xxx xxx total impressions
9 xxx xxx
12 xxx xxx
....
23 xxx xxx
24 0 0
can you help me with a formula that will return zero totals?
Thanks
kpax
How are you populating your dataset now?
Generally speaking what I mean is that you'd have to create a "for x = 1 to 24" type loop. For each x determine if there is a corresponding record in your dataset. If not, you'd have to add it to your dataset. All of this would have to be done before the dataset is passed to Crystal.
frodoman
Generally speaking what I mean is that you'd have to create a "for x = 1 to 24" type loop. For each x determine if there is a corresponding record in your dataset. If not, you'd have to add it to your dataset. All of this would have to be done before the dataset is passed to Crystal.
frodoman
ASKER
Basically what I have is a DataAdapter that gets passed 2 dates to the sql stmnt which creates the dataset. this dataset is what I use to export my cr report.
In the cr report I use this dataset to create a cross-tab report which has devices listed as columns and (needs) hour time format as rows to get my total impressions report.
so you're saying I should use a for loop before I create the dataset? how do I add to dataset?
Thanks
kpax
In the cr report I use this dataset to create a cross-tab report which has devices listed as columns and (needs) hour time format as rows to get my total impressions report.
so you're saying I should use a for loop before I create the dataset? how do I add to dataset?
Thanks
kpax
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem though is getting the results whether the hour has data or not - regardless of if you use crosstab or not, Crystal won't report off data that isn't there. In order to do this, you're going to need to add records to your dataset for each hour that is otherwise missing.
If you're doing a "sum" on the total impressions then simply insert a zero value record for each hour - this insures you have the result in your crosstab because the sum of zero will be zero. If you're doing a "count" then it's a different story because you have to insert the record to make the hour appear but obviously you'll get a count of 1 instead of a count of zero.
HTH
frodoman