Crystal Plus T-SQL: Not Picking up All of the Data

Hello:

Attached is my Crystal report.

At the very end of this posting is the T-SQL programming of the view, called "CARBON", that the report is based upon.

For some reason, the report is not picking up all of the data.  I think that it has to do with the Select Expert.  The data in the first half of this T-SQL statement, before the UNION ALL, is not being picked up.  Specifically, I think that it has to do with the CloseDate field.  When I use the parameters of the report, only the second half of the view is being picked up.

The following is the T-SQL view, if you add "where" clauses to it from what is supposed to be in the Select Expert.  (I hope that makes sense.):

SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97708.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97708.ME_User_Defined BETWEEN '' AND 'YCH'
                      and ME97708.ME_Job_Close_Date = '1900-01-01'
UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
                      where ME97704.DOCDATE BETWEEN '01/01/2010' and '07/31/2015'
                      and ME97707.ME_Job_ID BETWEEN 'C150000' and 'C159999'
                      and ME97707.ME_Job_Close_Date <> '1900-01-01'
                      and ME97707.ME_User_Defined BETWEEN '' AND 'YCH'

Again, the report is not picking up the first half of the T-SQL programming, prior to UNION ALL.

If there is a fix for the Select Expert, please let me know what that is.

Thank you!

TBSupport




SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT     /*DISTINCT */ ME97707.ME_Job_ID AS [Project], ME97707.ME_User_Defined AS [Editor], cast(COALESCE (ME97704.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97704.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN cast(ME97707.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97707.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97707 LEFT OUTER JOIN
                      ME97704 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97707.MEuserdefined3
CARBON-COMMISSION.rpt
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TBSupportAuthor Commented:
If this helps any, attached is a .rpt file that gets me fairly close to what I need, from a Select Expert standpoint:

Dates = date range
Editor = '' to 'whatever'
Job = 'whatever' to 'whatever'
Status = 'Open' or 'Closed' or both
Close Date = date range or date range plus 01/01/1900(i.e. null) or 01/01/1900 (i.e. null)

TBSupport
CARBON-COMMISSION.rpt
0
TBSupportAuthor Commented:
For Close Date, 01/01/1900 represents an "open" status.
0
TBSupportAuthor Commented:
What I mentioned, in my previous two postings, are the parameters and choices for those parameters given by the end user.

TBSupport
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mlmccCommented:
Is the closed date field a datetime?
As I recall from you other questions it is.  In that case it will never equal 1900-01-01

Try

ME97708.ME_Job_Close_Date = DateTime(1900,01,01,0,0,0)

mlmcc
0
TBSupportAuthor Commented:
No, it's a Date--not DateTime.

TBSupport
0
mlmccCommented:
I couldn't open the report last night but got it open this morning.

Try changing the order of your select filter.  If Crystal tries to use a NULL field for anything but an IsNull test, it returns nothing.  If the closed date is null for open then you need to reverse the set of tests around the or

({CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status}  and
(isnull({CARBON.CloseDate}) OR {Carbon.closedate} = Date(1900,1,1))) 
OR
({CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date}) 

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
Hi mlmcc:

I got this work, by using the following.  I had not read your post. yet, but your looks pretty close if not exactly like mine:

//open or closed or both:
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.Project} = {?Job} or
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})

//also, open or closed or both (this is the one that is activeely used in the report (08/14/2015):
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} or
{CARBON.Project} = {?Job} and
{CARBON.DocumentDate} = {?Dates} and
{CARBON.Editor}  = {?Editor} and
{CARBON.Status} = {?Status} and
{CARBON.CloseDate} = {?Close Date} and
isnull({CARBON.CloseDate})


Thank you!

TBSupport
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.