TBSupport
asked on
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_
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
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_
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_
ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97707.ME_Job_Close_
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
ASKER
For Close Date, 01/01/1900 represents an "open" status.
ASKER
What I mentioned, in my previous two postings, are the parameters and choices for those parameters given by the end user.
TBSupport
TBSupport
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
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
ASKER
No, it's a Date--not DateTime.
TBSupport
TBSupport
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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