How can I get records with a null value to display
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3113
Printed Date: 03 May 2024 at 12:40am
Topic: How can I get records with a null value to display
Posted By: Cutty
Subject: How can I get records with a null value to display
Date Posted: 06 May 2008 at 11:55am
|
I have a report that uses two tables, LABOR and LABTRANS. The LABOR table contains info on the workforce such as employee ID, job title, supervisor, ect. The LABTRANS table contains info on transactions pertaining to the labor records such as Vacation, Sick Leave, Training, Work, ect. These tables are linked by Employee ID. with a Left Outter Join. When running my report with this query "
{LABOR.SUPERVISOR} = "718585" and
{LABTRANS.STARTDATE} in DateTime (2008, 04, 13, 00, 00, 00) to DateTime (2008, 04, 19, 00, 00, 00 "
the results set returns only Labor Records with labor transaction for the specified time period. I need it to display all labor records that Supervisor = "718585 " even if the records do not have transactions for the selected time period.
This query returns all Labor records where Supervisor = " 718585 " which is what I want. After adding
and {LABTRANS.STARTDATE} in DateTime (2008, 04, 13, 00, 00, 00) to DateTime (2008, 04, 19, 00, 00, 00 the report only returns Labor records with transaction for the specified time frame.
|
Replies:
Posted By: Lugh
Date Posted: 08 May 2008 at 6:25am
There are two ways to handle this, depending on your comfort level and access to the data source.
One is to use a stored procedure inside your database. You can use a SQL Command object instead, but it's tricky to make it work right with parameters and whatnot. You would put the date restrictions for the LABTRANS table in the JOIN statement, rather than in the WHERE statement. This is the ideal solution.
The other solution is to allow your report to pull all of the data (i.e., don't reference LABTRANS in the Select Expert at all), but suppress any records that are outside the date range. The main disadvantage here is that you will have an impact to performance, as you are pulling in every record from the LABTRANS table for that supervisor.
I can get more detailed on the solution, if you would like.
|
Posted By: hilfy
Date Posted: 08 May 2008 at 7:34am
|
Another way would be to manually update your selection formula to something like this:
{LABOR.SUPERVISOR} = "718585" and
(IsNull({LABTRANS.STARTDATE}) or {LABTRANS.STARTDATE} in DateTime (2008, 04, 13, 00, 00, 00) to DateTime (2008, 04, 19, 00, 00, 00 "))
Note the placement of the parentheses - this is important! Also, you must check for IsNull first, otherwise you won't see your null values.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: Lugh
Date Posted: 08 May 2008 at 8:23am
There is a caveat to Hilfy's suggestion, though.
If there is a record in LABTRANS that exists for the supervisor, but not one that falls within the date range, then no records will be returned. There is neither a record within the date range, nor will the outer join generate a NULL value, because there is a matching record in the join.
|
Posted By: Cutty
Date Posted: 09 May 2008 at 7:46am
|
Thanks for the replies folks, I will try some of these fixes. I would like you to go more into your second option
|
|