Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: showing zeros for no DB records Post Reply Post New Topic
Author Message
curt
Newbie
Newbie
Avatar

Joined: 15 Jul 2009
Location: United States
Online Status: Offline
Posts: 4
Quote curt Replybullet Topic: showing zeros for no DB records
     Posted: 31 Aug 2009 at 3:59pm
Hi everyone,
 
I have developed a report in XI that draws from one of my software vendors' databases.  The software is a time tracking application that records what executables were in use on a computer and for how long.  My report summarizes usage for a selected period of time and then averages out the total usage per day.  The problem I'm running into is that my average is thrown off by days when there is no data recorded.  If i pull usage for the week and one day shows nothing, my total time is only being divided by six to create the average.  The database doesn't seem to be storing any zeros or null records when this happens.  Is there any way that I can make crystal fill in zeros for these days and then calculate it in the average?  Am I just stuck pleading with the software vendor to write a record into the DB when there is no usage?  It's a SQL DB, does anyone have any advice on writing a stored proc that fills in this data or should I steer away from that too, seeing as how it's not my application and I could mess things up?  I appreciate your help in advance.  Thanks!
 
Curt
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Aug 2009 at 4:45pm
I don't think you need to add blank rows into the DB to get this info but that is one possibility.
Here are some other options.
You can create another source table that has all of the dates as rows and left join to that.
I assume you are using parameters for your date range to analyze the info so probably the easiest solution though is to just use a fomrula field using datediff...datediff("d",begin param, end param)... to get the total # of days which can be used iin formulas to get your daily average.
IP IP Logged
curt
Newbie
Newbie
Avatar

Joined: 15 Jul 2009
Location: United States
Online Status: Offline
Posts: 4
Quote curt Replybullet Posted: 01 Sep 2009 at 8:37am
Brilliant, okay.  I like the idea of the formula, that way I don't have to go messing around in the DB.  There's just one problem that I'm running into with that.  I am using parameters, but my date parameter is not split into two seperate parameters.  It's one date range parameter.  Is there a way I can pull a date diff from this parameter, or do I need to split it up into two parameters (startdate, enddate)?  When I try to use the datediff function, it is expecting more arguments.
 
Thanks again!
 
Curt
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Sep 2009 at 8:45am
try:
datediff("d",minimum({?param}),maximum({?param}))
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.