Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Hourly Cross-Tab Report

Posted on 2004-09-17
5
Medium Priority
?
614 Views
Last Modified: 2012-05-05
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 ({CrystallDeviceImpressions.PrintStartDate_dt}) = -1 then //no hour in db
    Hour (Time(0,0,0))  // return 0 hour  
else
    Hour ({CrystallDeviceImpressions.PrintStartDate_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
0
Comment
Question by:kpax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 12084513
Yes, you probably want a crosstab.

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
0
 

Author Comment

by:kpax
ID: 12102214
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
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12102850
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
0
 

Author Comment

by:kpax
ID: 12102941
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
0
 
LVL 42

Accepted Solution

by:
frodoman earned 1000 total points
ID: 12102994
Create your dataset as you're doing now, but then you'll want to add rows to your dataset before passing to Crystal.

To add rows to the dataset basically it's like this:

Dim dr as datarow = dt.tables(0).newrow
dr.item("yourcolumnname") = "bla bla bla"
dr.item("etc....") = "etc....."
ds.tables(0).Rows.Add(dr)

If you need more code help with this just search for adding rows to a dataset in the .net or vb.net forum - there's lots of info there already.

Just be careful not to call an "ds.update" because that will attempt to write the new rows to your database!?!?

frodoman
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question