| Author |
Message |
ScottD
Newbie
Joined: 19 May 2008
Location: United States
Online Status: Offline
Posts: 3
|
  
Topic: Showing no data when no data is present
Posted: 19 May 2008 at 10:33pm |
I'm writing several reports that take a date field and group the data by weeks. I'm reporting how many hours are worked each week based on rows of data that show date and hours worked.
My problem is that there are some weeks where no hours are worked and in that case, I can't find a way to show that there were zero hours worked. In my reports, the week is just skipped - and not showing zero hours worked in a week is a problem.
How do I get Crytal to show that a week has zero hours worked when there is no data for that week?
Thank you very much (in advance) for your help. I've struggled enough with this and very much apreciate your help.
ScottD
|
|
ScottD
|
 IP Logged |
|
|
|
ashisahb
Groupie
Joined: 15 May 2008
Location: India
Online Status: Offline
Posts: 48
|
Posted: 20 May 2008 at 1:55am |
Scott,
u can apply the formula field and update if it has null values.
eg:
if isnull(fieldname)
then 0
else
fieldname
|
|
Regards,
Ashish
|
| IP Logged |
|
ashisahb
Groupie
Joined: 15 May 2008
Location: India
Online Status: Offline
Posts: 48
|
Posted: 20 May 2008 at 2:28am |
else if no data is there , check for the length of the field.If length is 0 then update value as 0.
|
|
Regards,
Ashish
|
| IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 20 May 2008 at 5:26am |
|
Unfortunately, that solution won't actually help. There's not a null value to be tested, as there is no record at all.
This is a classic problem, that I do wish Crystal could find a way to simply automate. But, they haven't.
Essentially, what you need to do is create a table that has each of your weeks in it. Or, in this case, you will probably need to do each day. Then, simply do a left join with this table, to ensure that you have at least one record for each week (or day).
|
| IP Logged |
|
ScottD
Newbie
Joined: 19 May 2008
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 20 May 2008 at 8:02am |
That was the direction I was going to go but wanted to see if there was a way I could do it without creating a new, disconnected table. That is fine, though - this will work.
My linking skills are a little weak when it comes to complex linking. The data I am using is a date,time format. 5/8/08 12:34:21. How do I link this to a date table that I create? Can I do a link that says, greater than or equal to only the first item on the table that is bigger than the data? Obviously, I don't want to create a table with every second over the span of a few years.
Thanks so much for your help. I'm glad I didn't keep beating my head against the wall on this problem.
Scott D
|
|
ScottD
|
| IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 21 May 2008 at 4:32am |
|
The simplest solution in Crystal is to simply use the DateValue function on both sides of the link. This will collapse the datetime value into a simple date value, creating a relationship by day.
|
| IP Logged |
|
ScottD
Newbie
Joined: 19 May 2008
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 21 May 2008 at 11:42am |
Thank you very much for your time and your help.
You said, "use the DateValue function on both sides of the link".
How do I do link on a formula field? I don't know how to do this.
I'm under the impression that you can only link records that are in the database you are hooked to. That is all that is displayed in the linking screen. Is there way I can use a function and link based on the function's output?
Thanks again,
ScottD
|
|
ScottD
|
| IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 22 May 2008 at 4:56am |
|
Ah, my bad for being imprecise.
You can't do it with Crystal's linking utility. That is a pretty limited function for anything outside the most basic joins.
Ideally, if you have the ability to create the date table in your database, then you should have the ability to create the query in your database. Creating the query there and not in Crystal generally leads to better performance and expanded options. Just use whatever flavor of SQL your database calls for.
If that is not an option, you can create a SQL Command object in Crystal. In the Database Expert, at the point where you would normally pick the table(s) for the data source, choose "Add Command" instead. This allows you to put in a SQL query, with all the normal ANSI options.
When entering the SQL query, you can use functions to modify the join conditions.
|
| IP Logged |
|
bfasula
Newbie
Joined: 22 May 2008
Online Status: Offline
Posts: 4
|
Posted: 22 May 2008 at 7:24am |
|
The way i have done this is by using a sub report. I suppress the main report by checking for recordnumber =1 and isNull(field1) and printing the subreport instead which has my no data information.
|
| IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
Posted: 23 May 2008 at 4:29am |
Originally posted by bfasula
The way i have done this is by using a sub report. I suppress the main report by checking for recordnumber =1 and isNull(field1) and printing the subreport instead which has my no data information.
How does this resolve the missing records situation? We are still experiencing the problem of accounting for records that don't exist, not just records that have NULL values. Or, are you saying this is a solution to the tricky join problem?
|
| IP Logged |
|
|
|
