StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POConvert Reporting Services expression to SQL
text
Body
copied!<p>I'm trying to modify a report and I need to exclude a number of rows based on the data in one column the expression for that column is:</p> <pre><code>=iif(sum(iif(left(Fields!crs_group.Value,1) = "G",1,0) ,"GrpCourse")=0,"n/a", sum(iif(Fields!crs_group.Value="Enrolled on Course",1,0)) - sum(iif(left(Fields!crs_group.Value,1) = "G",1,0))) </code></pre> <p>I think if I can convert it to SQL I can filter out those rows in the query. My code so far is:</p> <pre><code>CASE WHEN SUM(CASE WHEN LEFT(sub.crs_group, 1) = 'G' THEN 1 ELSE 0 END)=0 THEN 999999999 ELSE SUM(CASE WHEN sub.crs_group = 'Enrolled on Course' THEN 1 ELSE 0 END) - SUM(CASE WHEN LEFT(sub.crs_group,1) = 'G' THEN 1 ELSE 0 END) END AS NumberNotInGroups </code></pre> <p>However this doesn't give matching results to when the report is run. I've change "n/a" to 999999999 because I get a conversion from VARCHAR to INT error. I've tried casting to VARCHAR with no success. Any help would be much appreciated, spent all afternoon yesterday trying to figure it out.</p> <p>EDIT</p> <p>Here is the full query:</p> <pre><code> SELECT sub.course, sub.crs_group, m.m_reference, me.e_status, me.e_id, s.s_studentreference, p.p_forenames, p.p_surname, pcd.p_surname + ',' + pcd.p_forenames as course_dir, CASE WHEN SUM(CASE WHEN LEFT(sub.crs_group, 1) = 'G' THEN 1 ELSE 0 END)=0 THEN 999999999 ELSE SUM(CASE WHEN sub.crs_group = 'Enrolled on Course' THEN 1 ELSE 0 END) - SUM(CASE WHEN LEFT(sub.crs_group,1) = 'G' THEN 1 ELSE 0 END) END AS NumberNotInGroups FROM msql.unitesnapshot.dbo.capd_moduleenrolment AS me INNER JOIN msql.unitesnapshot.dbo.capd_module AS m ON me.e_module = m.m_id LEFT JOIN msql.unitesnapshot.dbo.capd_staff scd on m.m_modulesupervisor = scd.s_id LEFT JOIN msql.unitesnapshot.dbo.capd_person pcd on scd.s_id = pcd.p_id INNER JOIN msql.unitesnapshot.dbo.capd_student s on me.e_student = s.s_id INNER JOIN msql.unitesnapshot.dbo.capd_person p on s.s_id = p.p_id INNER JOIN (SELECT m.m_id, CASE WHEN m.m_reference not like '%G_' THEN m.m_reference ELSE LEFT(m.m_reference, charindex('G', m.m_reference) - 1) END AS course, CASE WHEN m.m_reference not like '%G_' THEN 'Enrolled on Course' ELSE RIGHT(m.m_reference, 2) END AS crs_group FROM unitesnapshot.dbo.capd_module m) sub ON sub.m_id = me.e_module WHERE me.e_status = 'A' AND LEFT(m.m_reference, 2) = '12' AND SUBSTRING(m.m_reference, 7, 2) in ('VF','AB','FB') GROUP BY sub.course, sub.crs_group, m.m_reference, me.e_status, me.e_id, s.s_studentreference, p.p_forenames, p.p_surname, pcd.p_surname + ',' + pcd.p_forenames ORDER BY p.p_surname </code></pre> <p>Here is the correct result in SSRS:</p> <p><img src="https://i.stack.imgur.com/0Nz86.png" alt="enter image description here"></p> <p>And here is the incorrect result in sql-server, it should show 1 not 99999999:</p> <p><img src="https://i.stack.imgur.com/pQ9dP.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload