Hello I am running CRX for LRS using SQL Server 2005. I have my a rpt written via an SQL OLEDB connection for speed. I derived a 'date' field through text on PACOMMENTS. I get all four comments lines in CR when I run it locally but only the first line when I run it via Portal on the reporting server. Anyone ever have this issue? Any suggestions?
Are you reporting just from PACOMMENTS or are you joining to it from another table? If join, what type?
Here's the SQL:
SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "PACOMMENTS"."CMT_TEXT", "PACOMMENTS"."ACTION_CODE", "PACOMMENTS"."R_DATE", "PACOMMENTS"."SEQ_NBR", "EMPLOYEE"."DEPARTMENT", "DEPTCODE"."NAME", "EMPLOYEE"."PROCESS_LEVEL"
FROM "DB30_LAW_PROD"."lawuser"."PACOMMENTS" "PACOMMENTS" INNER JOIN ("DB30_LAW_PROD"."lawuser"."DEPTCODE" "DEPTCODE" INNER JOIN "DB30_LAW_PROD"."lawuser"."EMPLOYEE" "EMPLOYEE" ON (("DEPTCODE"."COMPANY"="EMPLOYEE"."COMPANY") AND ("DEPTCODE"."PROCESS_LEVEL"="EMPLOYEE"."PROCESS_LEVEL")) AND ("DEPTCODE"."DEPARTMENT"="EMPLOYEE"."DEPARTMENT")) ON ("PACOMMENTS"."COMPANY"="EMPLOYEE"."COMPANY") AND ("PACOMMENTS"."EMPLOYEE"="EMPLOYEE"."EMPLOYEE")
WHERE "EMPLOYEE"."PROCESS_LEVEL"='300'
ORDER BY "EMPLOYEE"."DEPARTMENT", "EMPLOYEE"."EMPLOYEE", "PACOMMENTS"."SEQ_NBR"
Thanks. I've tried a bunch of permutations to get what I need and don't think that I can.
Line 1 of the comments comes over as 09/17/07 XXXXXXXXXX
I have to derive date from the first 8 characters of line 1 and then select lines 2, 3, etc...
Because I can't concatenate the fields and views aren't an option, I don't see how I can do this.