[Sergios Rahmatoulin]

Hi,

I have an SSRS that shows some numbers bases on Year and Quarter (I have created parameters for those). When I run the report I have no issue with Year but it only works for Quarter 1. If I run this in the Query Designer then it works for both Year and Quarter with no issue.

Here is my code:

IF @Quarter = ‘1’
(select EMPLOYID as EmployeeID,SUM(Q1Gross) as Q1Gross,SUM(Q1fedWages) as Q1fedWages,(SUM(Q1Gross)-SUM(Q1FedWages)) as “Gross – FedWages” from (

select EMPLOYID, GROSWAGS_1 + GROSWAGS_2 + GROSWAGS_3 Q1Gross,
Federal_Wages_1 + Federal_Wages_2 + Federal_Wages_3 as Q1fedWages
from UPR00900 where YEAR1 = @Year

union ALL

select a.EMPLOYID,-(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3),0 as wages from UPR30301 as a inner join UPR00500 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.DEDUCTON and a.PYRLRTYP = 2
where a.YEAR1 = @Year and b.SFRFEDTX = 1
union all

select a.EMPLOYID,+(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3),0 as wages from UPR30301 as a inner join UPR00600 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.BENEFIT and a.PYRLRTYP = 3
where a.YEAR1 = @Year and b.SBJTFDTX = 1

)x group by EMPLOYID having sum(Q1Gross) <> sum(Q1fedWages))

ELSE IF @Quarter = ‘2’
(select EMPLOYID as EmployeeID,SUM(Q2Gross) as Q2Gross,SUM(Q2fedWages) as Q2fedWages,(SUM(Q2Gross)-SUM(Q2FedWages)) as “Gross – FedWages” from (

select EMPLOYID, GROSWAGS_4 + GROSWAGS_5 + GROSWAGS_6 Q2Gross,
Federal_Wages_4 + Federal_Wages_5 + Federal_Wages_6 as Q2fedWages
from UPR00900 where YEAR1 = @Year

union ALL

select a.EMPLOYID,-(MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6),0 as wages from UPR30301 as a inner join UPR00500 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.DEDUCTON and a.PYRLRTYP = 2
where a.YEAR1 = @Year and b.SFRFEDTX = 1
union all

select a.EMPLOYID,+(MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6),0 as wages from UPR30301 as a inner join UPR00600 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.BENEFIT and a.PYRLRTYP = 3
where a.YEAR1 = @Year and b.SBJTFDTX = 1

)x group by EMPLOYID having sum(Q2Gross) <> sum(Q2fedWages))

ELSE IF @Quarter = ‘3’
(select EMPLOYID as EmployeeID,SUM(Q3Gross) as Q3Gross,SUM(Q3fedWages) as Q3fedWages,(SUM(Q3Gross)-SUM(Q3FedWages)) as “Gross – FedWages” from (

select EMPLOYID, GROSWAGS_7 + GROSWAGS_8 + GROSWAGS_9 Q3Gross,
Federal_Wages_7 + Federal_Wages_8 + Federal_Wages_9 as Q3fedWages
from UPR00900 where YEAR1 = @Year

union ALL

select a.EMPLOYID,-(MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9),0 as wages from UPR30301 as a inner join UPR00500 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.DEDUCTON and a.PYRLRTYP = 2
where a.YEAR1 = @Year and b.SFRFEDTX = 1
union all

select a.EMPLOYID,+(MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9),0 as wages from UPR30301 as a inner join UPR00600 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.BENEFIT and a.PYRLRTYP = 3
where a.YEAR1 = @Year and b.SBJTFDTX = 1

)x group by EMPLOYID having sum(Q3Gross) <> sum(Q3fedWages))

ELSE IF @Quarter = ‘4’
(select EMPLOYID as EmployeeID,SUM(Q4Gross) as Q4Gross,SUM(Q4fedWages) as Q4fedWages,(SUM(Q4Gross)-SUM(Q4FedWages)) as “Gross – FedWages” from (

select EMPLOYID, GROSWAGS_10 + GROSWAGS_11 + GROSWAGS_12 Q4Gross,
Federal_Wages_10 + Federal_Wages_11 + Federal_Wages_12 as Q4fedWages
from UPR00900 where YEAR1 = @Year

union ALL

select a.EMPLOYID,-(MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12),0 as wages from UPR30301 as a inner join UPR00500 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.DEDUCTON and a.PYRLRTYP = 2
where a.YEAR1 = @Year and b.SFRFEDTX = 1
union all

select a.EMPLOYID,+(MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12),0 as wages from UPR30301 as a inner join UPR00600 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.BENEFIT and a.PYRLRTYP = 3
where a.YEAR1 = @Year and b.SBJTFDTX = 1

)x group by EMPLOYID having sum(Q4Gross) <> sum(Q4fedWages))

The issue has to do with the If statement, I’m sure, but I don’t know how to fix it. The report only shows the fields from the first If statement.

Can someone help please?

Thanks

——————————
Sergios Rahmatoulin
Systems Analyst
Hope Community Resources, Inc.
Anchorage AK
——————————

[Steve Erbach]

Sergios,

As far as SQL code goes, your query doesn’t require the ELSEs. Each IF statement is checking a different possibility for the same variable so they’re mutually exclusive.

Since our company doesn’t use the Payroll module, I simplified your query to test its logic:

DECLARE @Quarter CHAR(1) = ‘3’;

IF @Quarter = ‘1’ (

   SELECT @Quarter AS Answer

) ELSE

IF @Quarter = ‘2’ (

   SELECT @Quarter AS Answer

) ELSE

IF @Quarter = ‘3’ (

   SELECT @Quarter AS Answer

) ELSE

IF @Quarter = ‘4’ (

   SELECT @Quarter AS Answer

)

The Answer is ‘3’ in this case. If you remove the ELSEs you get the same result.

As far as the report only working for Q1, that’s because your report’s Dataset only expects the Q1 field names from the 1st IF statement. You have to change the query so that the “Q1Gross” field is just “Gross” and the Q1fedWages field is FedWages. Ditto for each of the IF statements. Always return the same field names, not different ones depending on the Quarter.

Sincerely,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Steve Erbach]

Sergios,

In order to differentiate between quarters in your report, I would add a column in each of your IF queries for the quarter. Instead of having field names that differentiate quarters, use a value in a “Quarter” field. Then your SSRS report can utilize the content of that field to let you know which quarter is which.

Regards,

——————————
“Sparkly” Steve Erbach – Business Analyst & MS Dynamics Platform Administrator
WOW Logistics Company – Appleton, WI
VP for Communication, GPUG WI (Milwaukee) Chapter
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 (16.00.0439) / MR 2012 CU15
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: @serbach
Welcome to Dynamics!
You do not have permission.
Call Steve. (hat tip: Lou Spevack)
——————————
——————————————-

[Sergios Rahmatoulin]

Guys thank you for all your replies…they were all really good and useful!

However, what solved my problem the easiest way was this:

As far as the report only working for Q1, that’s because your report’s Dataset only expects the Q1 field names from the 1st IF statement. You have to change the query so that the “Q1Gross” field is just “Gross” and the Q1fedWages field is FedWages. Ditto for each of the IF statements. Always return the same field names, not different ones depending on the Quarter.

Thank you!

——————————
Sergios Rahmatoulin
Systems Analyst
Hope Community Resources, Inc.
Anchorage AK
——————————
——————————————-

[Neil Blotske]

In line with what Steve suggested, you could see if this works for you.  I only checked to verify this returned data, but haven’t taken the time to validate numbers – there’s my dislaimer . . . and your homework 😉

declare @year int = 2016
declare @Quarter int = 2

select EMPLOYID as EmployeeID,SUM(Gross) as Gross,SUM(fedWages) as fedWages,(SUM(Gross)-SUM(FedWages)) as “Gross – FedWages”
from (

SELECT        EMPLOYID, GROSWAGS_1 + GROSWAGS_2 + GROSWAGS_3 AS Gross, Federal_Wages_1 + Federal_Wages_2 + Federal_Wages_3 AS fedWages
FROM            UPR00900
where YEAR1 = @Year and  { fn QUARTER(LSTPCKDT) } in (@Quarter)

union ALL

select a.EMPLOYID,
Case when @Quarter = 1 then -(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3)
    when @Quarter = 2 then -(MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6)
    when @Quarter = 3 then -(MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9)
    when @Quarter = 4 then -(MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12)
 else 0 end as gross,0 as wages
from UPR30301 as a inner join UPR00500 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.DEDUCTON and a.PYRLRTYP = 2
where a.YEAR1 = @Year and b.SFRFEDTX = 1

union all

select a.EMPLOYID,
Case when @Quarter = 1 then (MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3)
    when @Quarter = 2 then (MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6)
    when @Quarter = 3 then (MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9)
    when @Quarter = 4 then (MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12) else 0 end as fed,
0 as wages
from UPR30301 as a inner join UPR00600 as b on
a.EMPLOYID = b.EMPLOYID and a.PAYROLCD = b.BENEFIT and a.PYRLRTYP = 3
where a.YEAR1 = @Year and b.SBJTFDTX = 1

)x group by EMPLOYID having sum(Gross) <> sum(fedWages)

——————————
Neil Blotske
KT Connections, Inc.
Rapid City SD
——————————
——————————————-

[Blair Christensen]

I work with SSRS quite a bit and I would propose a slight adjustment to your query such:

DECLARE @Yr as INTEGER

, @Qtr as INTEGER

;

select EMPLOYID

, SUM(GrossWages) as GrossWages

, SUM(FederalWages) as FederalWages

, SUM(GrossWages) – SUM(FederalWages) as DIFF

from (

select EMPLOYID

, CASE WHEN @Qtr = 1 THEN GROSWAGS_1 + GROSWAGS_2 + GROSWAGS_3

WHEN @Qtr = 2 THEN GROSWAGS_4 + GROSWAGS_5 + GROSWAGS_6

WHEN @Qtr = 3 THEN GROSWAGS_7 + GROSWAGS_8 + GROSWAGS_9

WHEN @Qtr = 4 THEN GROSWAGS_10 + GROSWAGS_11 + GROSWAGS_12

ELSE 0

END as GrossWages

, CASE WHEN @Qtr = 1 THEN Federal_Wages_1 + Federal_Wages_2 + Federal_Wages_3

WHEN @Qtr = 2 THEN Federal_Wages_4 + Federal_Wages_5 + Federal_Wages_6

WHEN @Qtr = 3 THEN Federal_Wages_7 + Federal_Wages_8 + Federal_Wages_9

WHEN @Qtr = 4 THEN Federal_Wages_10 + Federal_Wages_11 + Federal_Wages_12

ELSE 0

END as FederalWages

from UPR00900

where YEAR1 = @Yr

union ALL

select a.EMPLOYID

, CASE WHEN @Qtr = 1 THEN -(MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3)

WHEN @Qtr = 2 THEN -(MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6)

WHEN @Qtr = 3 THEN -(MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9)

WHEN @Qtr = 4 THEN -(MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12)

ELSE 0

END as GrossWages

, 0 as FederalWages

from UPR30301 as a

inner join UPR00500 as b on a.EMPLOYID = b.EMPLOYID

and a.PAYROLCD = b.DEDUCTON

and a.PYRLRTYP = 2

where b.SFRFEDTX = 1

and a.YEAR1 = @Yr

union all

select a.EMPLOYID

, CASE WHEN @Qtr = 1 THEN MTDWAGES_1 + MTDWAGES_2 + MTDWAGES_3

WHEN @Qtr = 2 THEN MTDWAGES_4 + MTDWAGES_5 + MTDWAGES_6

WHEN @Qtr = 3 THEN MTDWAGES_7 + MTDWAGES_8 + MTDWAGES_9

WHEN @Qtr = 4 THEN MTDWAGES_10 + MTDWAGES_11 + MTDWAGES_12

ELSE 0

END as GrossWages

, 0 as FederalWages

from UPR30301 as a

inner join UPR00600 as b on a.EMPLOYID = b.EMPLOYID

and a.PAYROLCD = b.BENEFIT

and a.PYRLRTYP = 3

where b.SBJTFDTX = 1

and a.YEAR1 = @Yr

) x

GROUP BY EMPLOYID

HAVING SUM(GrossWages) – SUM(FederalWages) <> 0

@Yr and @Qtr become the parameters in SSRS you allow the user to select.  Because you get to determine the column names in SSRS, I try not to name the columns in my base query using long names or names requiring [] or “”.  This version is also much simpler to read and maintain (and probably runs faster).

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-