Writing IF parameter = ""and ELSE conditions in ssrs
-
I have a payroll budget report that has a parameter to hide a column in a group but now i need to say in a text box in ssrs for payroll total that if Parameter!IncludePTO = "Yes", Then add Payroll + OT+PTO else add Payroll +OT:
Now the Payroll and Ot and PTO come from the field strCategory fields.
what I have tried and not working at all
=IIF(Parameters!IncludePTO.Value=1,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value)
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value)
+
IIf(Sum(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value)
,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value)
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value)The report looks like this:
The SQL for the report :
Declare @StartDate as Date = '1-1-2022'
Declare @EndDate as Date = '06-30-2022'
DECLARE @Prop INT = 8005
SELECT
CONCAT(tblProperties.intProp, ' - ', tblProperties.strAlias)Title,
qryFinal.strTemplate,
qryFinal.intGroup,
qryFinal.strGroup,
qryFinal.intOrder,
qryFinal.intCategory,
Case qryFinal.strCategory
When 'Payroll' then 'Payroll'
when 'Payroll - Overtime' then 'OT'
when 'Payroll PTO' then 'PTO'
end as strCategory,
qryFinal.intCategoryOrder,
qryFinal.dtMonth,
qryFinal.intProp,
qryFinal.mnyMonthActual,
qryFinal.mnyMTDActual,
qryFinal.mnyMonthBudget,
qryFinal.mnyMTDBudget,
tblProperties.strAlias
FROM
(
SELECT
tblGLRowTemplate.strTemplate,
tblGLGroup.intGroup,
tblGLGroup.strGroup,
tblGLRowItem.intOrder,
tblGLCategory.intCategory,
tblGLCategory.strCategory,
tblGLCategory.intOrder AS intCategoryOrder,
qryCategoryData.dtMonth,
qryCategoryData.intProp,
SUM(CASE
WHEN tblGLGroup.fNegate = 1 THEN ISNULL(qryCategoryData.mnyMonthActual, 0) * -1
ELSE ISNULL(qryCategoryData.mnyMonthActual, 0)
END) AS mnyMonthActual,
SUM(CASE
WHEN tblGLGroup.fNegate = 1 THEN ISNULL(qryCategoryData.mnyMTDActual, 0) * -1
ELSE ISNULL(qryCategoryData.mnyMTDActual, 0)
END) AS mnyMTDActual,
SUM(ISNULL(qryCategoryData.mnyMonthBudget, 0)) AS mnyMonthBudget,
SUM(ISNULL(qryCategoryData.mnyMTDBudget, 0)) AS mnyMTDBudget
FROM
tblGLRowItem
INNER JOIN tblGLRowTemplate ON tblGLRowItem.intTemplate = tblGLRowTemplate.intTemplate
INNER JOIN tblGLGroup ON tblGLRowItem.intGroup = tblGLGroup.intGroup
INNER JOIN tblGLCategory ON tblGLRowItem.intItem = tblGLCategory.intItem
LEFT JOIN
(
SELECT
qryPreExclude.intCategory,
qryPreExclude.dtMonth,
qryPreExclude.intProp,
qryPreExclude.mnyMonthActual,
qryPreExclude.mnyMTDActual,
qryPreExclude.mnyMonthBudget,
qryPreExclude.mnyMTDBudget
FROM
(
SELECT
tblGLCategory.intCategory,
tblGLCategory.strCategory,
tblGLCategory.intItem,
tblGLCategory.intOrder,
tblGLAccount.intAccount,
tblGLAccount.strAcctCode AS strAccountAcctCode,
tblGLAccount.strSegment AS strAccountSegment,
tblGLAccount.fStoresOnly,
tblGLAccount.fExclude,
qryBudgetAndActual.*
FROM tblGLCategory WITH (NOLOCK)
INNER JOIN tblGLAccount WITH (NOLOCK) ON tblGLCategory.intCategory = tblGLAccount.intCategory
CROSS JOIN
(
SELECT
CASE WHEN qryActual.intProp IS NOT NULL THEN qryActual.intProp ELSE qryBudget.intProp END AS intProp,
CASE WHEN qryActual.strAcctCode IS NOT NULL THEN qryActual.strAcctCode ELSE qryBudget.strAcctCode END AS strAcctCode,
CASE WHEN qryActual.strSegment IS NOT NULL THEN qryActual.strSegment ELSE qryBudget.strSegment END AS strSegment,
CASE WHEN qryActual.dtMonth IS NOT NULL THEN qryActual.dtMonth ELSE qryBudget.dtMonth END AS dtMonth,
qryActual.fStore,
qryBudget.mnyMonthBudget,
qryBudget.mnyMTDBudget,
qryActual.mnyMonthActual,
qryActual.mnyMTDActual
FROM
(
SELECT
intProp,
dtMonth,
NULL AS strSegment,
strAcctCode,
SUM(CASE WHEN dtMonth >= @StartDate and dtMonth<= @EndDate THEN mnyBudget ELSE 0 END) AS mnyMonthBudget,
SUM(mnyBudget) AS mnyMTDBudget
FROM
tblAcumaticaBudgets WITH (NOLOCK)
WHERE
intProp In(@Prop) AND
dtMonth >= @StartDate and dtMonth<= @EndDate
GROUP BY intProp,dtMonth,strAcctCode
)qryBudget
FULL JOIN
(
SELECT
intProp,
dtMonth,
strSegment,
strAcctCode,
fStore,
SUM(CASE WHEN qryAct.dtMonth >= @StartDate and qryAct.dtMonth<= @EndDate THEN qryAct.mnyActual ELSE 0 END) AS mnyMonthActual,
SUM(qryAct.mnyActual) AS mnyMTDActual
FROM
(
SELECT
intProp,
dtMonth,
RIGHT('0000' + CAST(intProp as varchar), 4) AS strSegment,
strAcctCode,
1 AS fStore,
SUM(mnyDebit) - SUM(mnyCredit) AS mnyActual
FROM
tblAcumaticaGL WITH (NOLOCK)
WHERE
intProp IN(@Prop) and
dtMonth >= @StartDate and dtMonth<= @EndDate
GROUP BY intProp, dtMonth, strAcctCode
)qryAct
GROUP BY intProp, dtMonth, strSegment, strAcctCode, fStore
)qryActual ON qryBudget.dtMonth = qryActual.dtMonth AND qryBudget.strSegment = qryActual.strSegment AND qryBudget.strAcctCode = qryActual.strAcctCode
)qryBudgetAndActual
WHERE
tblGLCategory.fActive = 1 AND tblGLAccount.fExclude = 0 AND
CASE
WHEN tblGLAccount.fStoresOnly = 0 AND tblGLAccount.strSegment IS NULL AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode THEN 1
WHEN tblGLAccount.fStoresOnly = 0 AND tblGLAccount.strSegment IS NOT NULL AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode AND tblGLAccount.strSegment = qryBudgetAndActual.strSegment THEN 1
WHEN tblGLAccount.fStoresOnly = 1 AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode AND qryBudgetAndActual.fStore = 1 THEN 1
ELSE 0
END = 1
)qryPreExclude
LEFT JOIN tblGLAccount tblGLAccountExclude ON qryPreExclude.intCategory = tblGLAccountExclude.intCategory AND qryPreExclude.strAcctCode = tblGLAccountExclude.strAcctCode AND qryPreExclude.strSegment = tblGLAccountExclude.strSegment AND tblGLAccountExclude.fExclude = 1
WHERE tblGLAccountExclude.intAccount IS NULL
)qryCategoryData ON tblGLCategory.intCategory = qryCategoryData.intCategory
WHERE tblGLRowItem.fActive = 1 AND tblGLCategory.fActive = 1
and tblGLCategory.strCategory in('Payroll', 'Payroll - Overtime','Payroll PTO')
and tblGLRowTemplate.intTemplate IN (22)
GROUP BY
tblGLRowTemplate.strTemplate,
tblGLGroup.intGroup,
tblGLGroup.strGroup,
tblGLRowItem.intOrder,
tblGLCategory.intCategory,
tblGLCategory.strCategory,
tblGLCategory.intOrder,
qryCategoryData.dtMonth,
qryCategoryData.intProp
)qryFinal
INNER JOIN tblProperties ON qryFinal.intProp = tblProperties.intProp
WHERE
qryFinal.dtMonth>= @StartDate and qryFinal.dtMonth <= @EndDate
or qryFinal.mnyMonthBudget is Null
ORDER BY qryFinal.intProp, qryFinal.dtMonth,qryFinal.intCategoryOrder,
CASE WHEN strCategory = 'PayRoll' THEN 1
WHEN strCategory = 'OT' THEN 2
WHEN strCategory = 'PTO' THEN 3
ELSE 4 END -
Use something like this in your report:
= IIF(Parameter!IncludePTO = "Yes", [Payroll] + [OT] + [PTO Sales], [Payroll] + [OT])
Or use a CASE statement in your T-SQL code to do it.
-
I can't use expressions because PTO, Payroll, and OT are not fields in my dataset they are values in the Field name strCategories.
-
Can you use this logic to unpivot that part? (so you get a column for each)
=IIF(Parameters!IncludePTO.Value=1,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value)
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value)
+
IIf(Sum(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value)
,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value)
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value)
-
I just looked briefly, so maybe I am off but it looks like your "IIF(Sum(Fields!strCategory.Value="Payrole..." expression is missing a lot of closing parentheses? 11 by my count.
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
-
I know it missing ) just not sure where to put them. I also get an error that says:
The Value expression for the textrun ‘Textbox126.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group,
=IIF(Parameters!IncludePTO.Value=1,IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0)))
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0)))
+
IIf(Sum(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value),CInt(0))),
IIF(Sum(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0)))
+
IIF(SUM(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0))) -
Maybe flip SUM and IIF? It looks like it is trying to sum up things that can't be summed (Sum(Fields!strCategory.Value="Payrole" etc.):
Maybe this?
=IIF(Parameters!IncludePTO.Value=1
,SUM(IIF(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0)))
+ SUM(IIF(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0)))
+ SUM(IIf(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value),CInt(0)))
,SUM(IIF(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0)))
+ SUM(IIF(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0)))
)
...or this?
=sum(
IIF(Parameters!IncludePTO.Value=1
,IIF(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0))
+ IIF(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0))
+ IIf(Fields!strCategory.Value="PTO",CInt(Fields!mnyMTDActual.Value),CInt(0))
,IIF(Fields!strCategory.Value="Payrole", CInt(Fields!mnyMTDActual.Value),CInt(0))
+ IIF(Fields!strCategory.Value="OT",Cint(Fields!mnyMTDActual.Value),CInt(0))
)
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
-
Some reason the Math in the equation not calculating correctly
-
Which equation are you currently using for that column (looks to be the same as variance)?
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply