Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SSRS report


Help with SSRS report

Author
Message
abrahamanish
abrahamanish
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30
Hello,

I'm trying to create a report for listing the % compliance by Supervisor along with employee name, id, dept, and dates.

SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
E.FLDDEPT,
D.FLDDESCR AS DEPT,
E.FLDJOB,
J.FLDDESCR,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
TBLAST = CONVERT(VARCHAR(10),PPDL.FLDDATELAST,101),
TB = CONVERT(VARCHAR(10),PPDx.FLDDATEDUE,101),
TBSS = CONVERT(VARCHAR(10),TBSSx.FLDDATEDUE, 101),
TBSL = CONVERT(VARCHAR(10),TBSSL.FLDDATELAST, 101),
CXRAY = CONVERT(VARCHAR(10),CDUEx.FLDDATEDUE, 101),
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
E.FLDSUPRNAME,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Compliant,


CASE WHEN F.FLDDATE IS NULL OR (F.FLDDATE+365) < GETDATE() THEN 'NO' ELSE 'YES' END AS Fit_Compliant,

Tb_Compliant = CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL
AND CDUEx.FLDDATEDUE IS NULL
THEN 'YES'
ELSE 'NO'
END

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDTYPE IN ('109','111') AND I.FLDDATE = (SELECT MAX(FLDDATE) FROM IMMUNE I2 WHERE E.FLDREC_NUM = I2.FLDEMPLOYEE) AND I.FLDDATE >= @Flu_Date AND I.FLDDATE <= GETDATE()


LEFT OUTER JOIN JOB J ON
E.FLDJOB = J.FLDCODE

LEFT OUTER JOIN DEPT D ON
E.FLDDEPT = D.FLDCODE

LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL = '110' AND
PPDx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM PPDL
ON PPDL.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDL.FLDPHYSICAL = '110'

LEFT OUTER JOIN REQEXAM TBSSx
ON TBSSx.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSx.FLDPHYSICAL = 'TBSS' AND
TBSSx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM TBSSL
ON TBSSL.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSL.FLDPHYSICAL = 'TBSS'


LEFT OUTER JOIN REQEXAM CDUEx
ON CDUEx.FLDEMPLOYEE = E.FLDREC_NUM
AND CDUEx.FLDPHYSICAL = '109' AND
CDUEx.FLDDATEDUE <= getdate()


LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
AND F.FLDDATE = (SELECT MAX(FLDDATE) FROM FITTEST F2 WHERE E.FLDREC_NUM = F2.FLDEMPLOYEE)

WHERE
E.FLDCOMP = @company AND
E.FLDSTATUS = 'A' AND E.FLDSUPRNAME <> ' ' AND E.FLDID <> ' '
ORDER BY E.FLDSUPRNAME

*******************
I modified the above code to get the % by supervisor.

SELECT
E.FLDSUPRNAME AS Supervisor,
COUNT(E.FLDID) AS CNT,
1.0*SUM(CASE WHEN I.FLDDATE IS NULL THEN 0 ELSE 1 END)/COUNT(E.FLDID) AS Percent_Flu_Compliant,
1.0*SUM(CASE WHEN F.FLDDATE IS NULL OR (F.FLDDATE+365) < GETDATE() THEN 0 ELSE 1 END)/
COUNT(E.FLDID) AS Percent_Fit_Compliant

FROM EMPLOYEE E

LEFT OUTER JOIN DEPT D
ON D.FLDCODE= E.FLDDEPT
LEFT OUTER JOIN IMMUNE I
ON I.FLDEMPLOYEE = E.FLDREC_NUM
AND I.FLDTYPE IN ('109', '111')
AND I.FLDDATE = (
SELECT MAX(FLDDATE)
FROM IMMUNE I2
WHERE E.FLDREC_NUM = I2.FLDEMPLOYEE AND I2.FLDTYPE IN ('109','111')
)
AND I.FLDDATE >= @Flu_Date
AND I.FLDDATE <= GETDATE()

LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
AND F.FLDDATE = (SELECT MAX(FLDDATE) FROM FITTEST F2 WHERE E.FLDREC_NUM = F2.FLDEMPLOYEE)

LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL = '110' AND
PPDx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM PPDL
ON PPDL.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDL.FLDPHYSICAL = '110'

LEFT OUTER JOIN REQEXAM TBSSx
ON TBSSx.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSx.FLDPHYSICAL = 'TBSS' AND
TBSSx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM TBSSL
ON TBSSL.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSL.FLDPHYSICAL = 'TBSS'


LEFT OUTER JOIN REQEXAM CDUEx
ON CDUEx.FLDEMPLOYEE = E.FLDREC_NUM
AND CDUEx.FLDPHYSICAL = '109' AND
CDUEx.FLDDATEDUE <= getdate()

WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
AND E.FLDSUPRNAME <> ' '
AND E.FLDID <> ' '
GROUP BY
   E.FLDSUPRNAME
ORDER BY
E.FLDSUPRNAME
*********************
It worked fine for Flu and Fit test compliance. For TB Compliance I got the error "Operand data type varchar is invalid for sum operator" - 1.0*SUM(CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL AND CDUEx.FLDDATEDUE IS NULL THEN '1' ELSE '0' END)/COUNT(E.FLDID) AS Percent_TB_Compliant

The problem is that I need to list employee id, name, dept and fludate, fit test date and tb date along with % compliance.

Please let me know if you have any insight on this. Many thanks for your time.

thanks
kevin.minteer@gmail.com
kevin.minteer@gmail.com
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 368
Are you sure the code you pasted is the code that you are trying to execute?

The line of code in your error message doesn't appear in the sample code you posted above: "1.0*SUM(CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL AND CDUEx.FLDDATEDUE IS NULL THEN '1' ELSE '0' END)/COUNT(E.FLDID) AS Percent_TB_Compliant"

That being said, assuming the error message is accurate the problem is that you've got the 1 and the 0 in your expression wrapped in single quotes - in other words they are varchar data types instead of integers. Remove the single quotes and the error should go away.
abrahamanish
abrahamanish
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30
Thanks for pointing this out and that fixed the error.

Now the problem is that I need add more coulmns for example employee name, dept, flu date etc along with % compliance.

Any insight on how to do this?

****************************

SELECT
E.FLDSUPRNAME AS Supervisor,
E.FLDLNAME, E.FLDFNAME,E.FLDID, E.FLDDEPT,
   
COUNT(E.FLDID) AS CNT,

   1.0*SUM(CASE WHEN I.FLDDATE IS NULL THEN 0 ELSE 1 END)/COUNT(E.FLDID) AS Percent_Flu_Compliant,

1.0*SUM(CASE WHEN F.FLDDATE IS NULL OR (F.FLDDATE+365) < GETDATE() THEN 0 ELSE 1 END)/
COUNT(E.FLDID) AS Percent_Fit_Compliant,

1.0*COUNT(CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL
AND CDUEx.FLDDATEDUE IS NULL THEN 1 ELSE 0 END)/COUNT(E.FLDID) AS Percent_TB_Compliant

FROM EMPLOYEE E

LEFT OUTER JOIN DEPT D
ON D.FLDCODE= E.FLDDEPT

LEFT OUTER JOIN IMMUNE I
ON I.FLDEMPLOYEE = E.FLDREC_NUM
AND I.FLDTYPE IN ('109', '111')
AND I.FLDDATE = (
SELECT MAX(FLDDATE)
FROM IMMUNE I2
WHERE E.FLDREC_NUM = I2.FLDEMPLOYEE AND I2.FLDTYPE IN ('109','111')
)
AND I.FLDDATE >= @Flu_Date
AND I.FLDDATE <= GETDATE()

LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
AND F.FLDDATE = (SELECT MAX(FLDDATE) FROM FITTEST F2 WHERE E.FLDREC_NUM = F2.FLDEMPLOYEE)

LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL = '110' AND
PPDx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM PPDL
ON PPDL.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDL.FLDPHYSICAL = '110'

LEFT OUTER JOIN REQEXAM TBSSx
ON TBSSx.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSx.FLDPHYSICAL = 'TBSS' AND
TBSSx.FLDDATEDUE <= getdate()

LEFT OUTER JOIN REQEXAM TBSSL
ON TBSSL.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSL.FLDPHYSICAL = 'TBSS'


LEFT OUTER JOIN REQEXAM CDUEx
ON CDUEx.FLDEMPLOYEE = E.FLDREC_NUM
AND CDUEx.FLDPHYSICAL = '109' AND
CDUEx.FLDDATEDUE <= getdate()



WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
AND E.FLDSUPRNAME <> ' '
AND E.FLDID <> ' '
GROUP BY
   E.FLDSUPRNAME,E.FLDLNAME, E.FLDFNAME,E.FLDID, E.FLDDEPT
ORDER BY
E.FLDSUPRNAME
kevin.minteer@gmail.com
kevin.minteer@gmail.com
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 368
I'm not entirely sure I follow what the issue is. Wouldn't you just need to add those fields to your Select and Group By?
DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 460
I'll second that. All that's required is to add the extra fields you need into your SELECT and GROUP BY clauses, assuming you have JOINed to the relevant tables.
abrahamanish
abrahamanish
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30
If you look at the code, I have already added the fields on select and group by statements. But now I'm not getting the %compliance I needed.

For example, it's showing 100% compliance for all supervisors.
AASHEIM, DONNA J.               100.00%   1000.00% 100.00%
MARTIN JJACOB 8788888
JOEL ABRAHAM 8968598
ADAM, JANICE G.                100.00%   1000.00% 100.00%
CYNTIA SUNNY 7898888
AMBURGEY, DAVID A.               100.00%   100.00%   100.00%
GEORGE MANUEL 87969096
JIM PONDER 67488888
SUZANNE MASON 97578588
ANDERSON, PATRICIA               100.00%   100.00%   100.00%
LINDA FoGUS 74848888
CHAR CONNER 89947889
SAM SON 78848888
KATHY MAHER 9899888

When I remove name and id fields from the select statement and group by, then I'm getting the correct result
AASHEIM, DONNA J.               100%      100%   100%   
ADAM, JANICE G.                100%      100%   0%   
AMBURGEY, DAVID A.               92.7%      96.3%   88.9%   

Let me know
kevin.minteer@gmail.com
kevin.minteer@gmail.com
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 368
I think I understand what you're saying. If you are selecting E.FLDID and also using the count(E.FLDID) in your expressions, you're getting one row per E.FLDID, which means each expression is going to evaluate to either 0/1 or 1/1. I don't think you want E.FLDID in your Select list or your Group By.

Unless of course, I'm misunderstanding what you're trying to accomplish.
abrahamanish
abrahamanish
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30
Yes you are right. If I remove ID, and name from select and group by it works fine.

But I have to list Employee ID, name along with % percentage compliance by supervisor.


Is there anyway to do this?
DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 460
You could try a derived table expression using the WITH construct, something like:

WITH <table> AS
(SELECT ....


)

SELECT
.....


where the fields in your "parent" SELECT needn't be present in your derived table select, if you get my meaning
abrahamanish
abrahamanish
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30
Ok, thanks for the insight.

I will try that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search