In Microsoft Report Builder (SSRS), I created two calculated fields (RevFund and FundVariance) via SQL Select function rather than Calculated field.
I then created a parameter with values A, B and C. And tried to use WHERE function to map A to filter all records, B to filter Fund Variance = 0 and C to filter Fund Variance <> 0 or != 0.
However, this is neither recognizing @Filter nor FundVariance.
SELECT
pa_journal_detail.jl_effective_date
,pa_journal_detail.jl_journal_num
,pa_journal_detail.jl_line_num
,pa_journal_detail.jl_account_type
,pa_journal_detail.a_project
,pa_journal_detail.jl_seg2
,pa_journal_detail.jl_seg3
,pa_journal_detail.jl_source_code
,pa_journal_detail.jl_ref1
,pa_journal_detail.jl_comment
,pa_journal_detail.jl_transaction_amt
,pa_journal_detail.jl_transact_type
,pa_journal_detail.jl_jnl_year_period
,pa_journal_detail.jl_journal_number
,pa_journal_detail.jl_journal_seq
,pa_journal_detail.jl_gl_org
,pa_journal_detail.jl_gl_obj
,pa_journal_detail.jl_ovrhd_calc
,pa_journal_detail.jl_costed
,pa_journal_detail.jl_workflow_stat
,pa_journal_detail.jl_posted
,gl_master.a_account_type
,gl_master.a_fund_seg1
,gl_master.a_object
,gl_master.a_org
,gl_master.a_charcode
,gl_master.a_charcode_desc
,pa_project_master.ma_project_type
,pa_project_master.ma_project_status
,pa_journal_detail.jl_alloc_exp
,CASE Pa_journal_detail.jl_seg2
WHEN 'CIPMSFP' THEN '360'
WHEN 'INSPSUR' THEN '240'
WHEN 'TRANS' THEN '260'
WHEN 'WATER' THEN '510'
WHEN 'STORM' THEN 540
WHEN 'WASTE' THEN '550'
WHEN 'CIPMSTR' THEN '360'
WHEN 'CIPMSWT' THEN '513'
WHEN 'CIPMSSW' THEN '543'
WHEN 'CIPMSWW' THEN '553'
WHEN 'CIPMSUR' THEN '390'
WHEN 'CIPMSGD' THEN '330'
WHEN 'DEV' THEN '240'
ELSE '0'
END as RevFund
,CASE
WHEN gl_master.a_fund_seg1 = '998' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'NOBILLING' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSFP' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'INSPSUR' AND gl_master.a_fund_seg1 = '240' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'TRANS' AND gl_master.a_fund_seg1 = '260' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WATER' AND gl_master.a_fund_seg1 = '510' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'STORM' AND gl_master.a_fund_seg1 = '540' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WASTE' AND gl_master.a_fund_seg1 = '550' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSTR' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWT' AND gl_master.a_fund_seg1 = '513' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSSW' AND gl_master.a_fund_seg1 = '543' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWW' AND gl_master.a_fund_seg1 = '553' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSUR' AND gl_master.a_fund_seg1 = '390' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSGD' AND gl_master.a_fund_seg1 = '330' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'DEV' AND gl_master.a_fund_seg1 = '240' THEN '0'
ELSE pa_journal_detail.jl_transaction_amt
END as FundVariance
FROM
pa_journal_detail
LEFT OUTER JOIN gl_master
ON pa_journal_detail.jl_gl_org = gl_master.a_org AND pa_journal_detail.jl_gl_obj = gl_master.a_object
LEFT OUTER JOIN pa_project_master
ON pa_journal_detail.a_project = pa_project_master.a_project
WHERE
pa_journal_detail.jl_account_type = N'E'
AND pa_journal_detail.jl_source_code != N'PAB'
AND pa_journal_detail.jl_source_code != N'POE'
AND pa_journal_detail.jl_source_code != N'PAJ'
AND pa_journal_detail.jl_source_code != N'POL'
AND pa_project_master.a_project LIKE N'CIP%'
AND pa_journal_detail.jl_transact_type = N'1'
AND gl_master.a_charcode NOT LIKE N'L%'
AND ( (@Filter = 'A')
OR (@Filter = 'B' AND FundVariance = 0)
OR (@Filter = 'C' AND FundVariance != 0) )
ORDER BY gl_master.a_fund_seg1 ASC, pa_journal_detail.jl_gl_org ASC, RevFund ASC, pa_journal_detail.jl_transaction_amt ASC
July 3, 2021 at 3:51 am
Sorry, some days I'm dumber than a freaking rock. I think your problem is that FundVariance doesn't have a value when you're trying to filter it. It's evaluated after the WHERE clause, so you have to basically rearrange your SQL statement a little to force that column to be calculated before the filter is applied. Something like this:
SELECT
pa_journal_detail.jl_effective_date
,pa_journal_detail.jl_journal_num
,pa_journal_detail.jl_line_num
,pa_journal_detail.jl_account_type
,pa_journal_detail.a_project
,pa_journal_detail.jl_seg2
,pa_journal_detail.jl_seg3
,pa_journal_detail.jl_source_code
,pa_journal_detail.jl_ref1
,pa_journal_detail.jl_comment
,pa_journal_detail.jl_transaction_amt
,pa_journal_detail.jl_transact_type
,pa_journal_detail.jl_jnl_year_period
,pa_journal_detail.jl_journal_number
,pa_journal_detail.jl_journal_seq
,pa_journal_detail.jl_gl_org
,pa_journal_detail.jl_gl_obj
,pa_journal_detail.jl_ovrhd_calc
,pa_journal_detail.jl_costed
,pa_journal_detail.jl_workflow_stat
,pa_journal_detail.jl_posted
,gl_master.a_account_type
,gl_master.a_fund_seg1
,gl_master.a_object
,gl_master.a_org
,gl_master.a_charcode
,gl_master.a_charcode_desc
,pa_project_master.ma_project_type
,pa_project_master.ma_project_status
,pa_journal_detail.jl_alloc_exp
,CASE Pa_journal_detail.jl_seg2
WHEN 'CIPMSFP' THEN '360'
WHEN 'INSPSUR' THEN '240'
WHEN 'TRANS' THEN '260'
WHEN 'WATER' THEN '510'
WHEN 'STORM' THEN 540
WHEN 'WASTE' THEN '550'
WHEN 'CIPMSTR' THEN '360'
WHEN 'CIPMSWT' THEN '513'
WHEN 'CIPMSSW' THEN '543'
WHEN 'CIPMSWW' THEN '553'
WHEN 'CIPMSUR' THEN '390'
WHEN 'CIPMSGD' THEN '330'
WHEN 'DEV' THEN '240'
ELSE '0'
END as RevFund
,CASE
WHEN gl_master.a_fund_seg1 = '998' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'NOBILLING' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSFP' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'INSPSUR' AND gl_master.a_fund_seg1 = '240' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'TRANS' AND gl_master.a_fund_seg1 = '260' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WATER' AND gl_master.a_fund_seg1 = '510' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'STORM' AND gl_master.a_fund_seg1 = '540' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WASTE' AND gl_master.a_fund_seg1 = '550' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSTR' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWT' AND gl_master.a_fund_seg1 = '513' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSSW' AND gl_master.a_fund_seg1 = '543' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWW' AND gl_master.a_fund_seg1 = '553' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSUR' AND gl_master.a_fund_seg1 = '390' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSGD' AND gl_master.a_fund_seg1 = '330' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'DEV' AND gl_master.a_fund_seg1 = '240' THEN '0'
ELSE pa_journal_detail.jl_transaction_amt
END as FundVariance
FROM
pa_journal_detail
LEFT OUTER JOIN gl_master
ON pa_journal_detail.jl_gl_org = gl_master.a_org AND pa_journal_detail.jl_gl_obj = gl_master.a_object
LEFT OUTER JOIN pa_project_master
ON pa_journal_detail.a_project = pa_project_master.a_project
WHERE
pa_journal_detail.jl_account_type = N'E'
AND pa_journal_detail.jl_source_code != N'PAB'
AND pa_journal_detail.jl_source_code != N'POE'
AND pa_journal_detail.jl_source_code != N'PAJ'
AND pa_journal_detail.jl_source_code != N'POL'
AND pa_project_master.a_project LIKE N'CIP%'
AND pa_journal_detail.jl_transact_type = N'1'
AND gl_master.a_charcode NOT LIKE N'L%'
) x
WHERE ( (@Filter = 'A')
OR (@Filter = 'B' AND x.FundVariance = 0)
OR (@Filter = 'C' AND x.FundVariance != 0) )
ORDER BY x.a_fund_seg1 ASC, x.jl_gl_org ASC, x.RevFund ASC, x.jl_transaction_amt ASC
July 3, 2021 at 6:16 pm
Thank you so much, pietlinden. And no you are not estupidez at all, you are a saint for helping dumb old me.
Quick follow-up question - where is the start of the parenthesis for ) x? Will it be before Select?
July 6, 2021 at 5:41 pm
Hi pietlindin,
This is not working because I may not be putting the starting parenthesis in the right area or maybe it doesn't work in Microsoft Report Builder.
I read somewhere to use CTE (Common Table Expression) approach instead but I can't seem to find that article.
Very similar to what you mentioned but looks like I had to rely on CTE for it to work. Here is the below code.
With CTE as
(
SELECT
pa_journal_detail.jl_effective_date
,pa_journal_detail.jl_journal_num
,pa_journal_detail.jl_line_num
,pa_journal_detail.jl_account_type
,pa_journal_detail.a_project
,pa_journal_detail.jl_seg2
,pa_journal_detail.jl_seg3
,pa_journal_detail.jl_source_code
,pa_journal_detail.jl_ref1
,pa_journal_detail.jl_comment
,pa_journal_detail.jl_transaction_amt
,pa_journal_detail.jl_transact_type
,pa_journal_detail.jl_jnl_year_period
,pa_journal_detail.jl_journal_number
,pa_journal_detail.jl_journal_seq
,pa_journal_detail.jl_gl_org
,pa_journal_detail.jl_gl_obj
,pa_journal_detail.jl_ovrhd_calc
,pa_journal_detail.jl_costed
,pa_journal_detail.jl_workflow_stat
,pa_journal_detail.jl_posted
,gl_master.a_account_type
,gl_master.a_fund_seg1
,gl_master.a_object
,gl_master.a_org
,gl_master.a_charcode
,gl_master.a_charcode_desc
,pa_project_master.ma_project_type
,pa_project_master.ma_project_status
,pa_journal_detail.jl_alloc_exp
,CASE Pa_journal_detail.jl_seg2
WHEN 'CIPMSFP' THEN '360'
WHEN 'INSPSUR' THEN '240'
WHEN 'TRANS' THEN '260'
WHEN 'WATER' THEN '510'
WHEN 'STORM' THEN 540
WHEN 'WASTE' THEN '550'
WHEN 'CIPMSTR' THEN '360'
WHEN 'CIPMSWT' THEN '513'
WHEN 'CIPMSSW' THEN '543'
WHEN 'CIPMSWW' THEN '553'
WHEN 'CIPMSUR' THEN '390'
WHEN 'CIPMSGD' THEN '330'
WHEN 'DEV' THEN '240'
ELSE '0'
END as RevFund
,CASE
WHEN gl_master.a_fund_seg1 = '998' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'NOBILLING' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSFP' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'INSPSUR' AND gl_master.a_fund_seg1 = '240' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'TRANS' AND gl_master.a_fund_seg1 = '260' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WATER' AND gl_master.a_fund_seg1 = '510' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'STORM' AND gl_master.a_fund_seg1 = '540' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'WASTE' AND gl_master.a_fund_seg1 = '550' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSTR' AND gl_master.a_fund_seg1 = '360' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWT' AND gl_master.a_fund_seg1 = '513' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSSW' AND gl_master.a_fund_seg1 = '543' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSWW' AND gl_master.a_fund_seg1 = '553' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSUR' AND gl_master.a_fund_seg1 = '390' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'CIPMSGD' AND gl_master.a_fund_seg1 = '330' THEN '0'
WHEN Pa_journal_detail.jl_seg2 = 'DEV' AND gl_master.a_fund_seg1 = '240' THEN '0'
ELSE pa_journal_detail.jl_transaction_amt
END as FundVariance
FROM
pa_journal_detail
LEFT OUTER JOIN gl_master
ON pa_journal_detail.jl_gl_org = gl_master.a_org AND pa_journal_detail.jl_gl_obj = gl_master.a_object
LEFT OUTER JOIN pa_project_master
ON pa_journal_detail.a_project = pa_project_master.a_project
)
Select *
From CTE
WHERE
cte.jl_account_type = N'E'
AND cte.jl_source_code != N'PAB'
AND cte.jl_source_code != N'POE'
AND cte.jl_source_code != N'PAJ'
AND cte.jl_source_code != N'POL'
AND cte.a_project LIKE N'CIP%'
AND cte.jl_transact_type = N'1'
AND cte.a_charcode NOT LIKE N'L%'
AND
(
(@Display = 'A')
OR (@Display = 'B' AND cte.FundVariance != 0)
OR (@Display = 'C' AND cte.FundVariance = 0)
)
July 6, 2021 at 9:15 pm
That will work. The basic idea is that you have to have the first query execute, and then the second work on the first... and that's essentially what a CTE does.
July 6, 2021 at 9:45 pm
What was your solution though - it seemed more elegant and one that possibly could get nested. Unsure how many times I can nest With CTE etc.
I think I was just messing up where the start of the parenthesis was in the ) x.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply