Question: Issue with Multiple Parameter Values in Power BI Paginated Report

[gautampruthi]

Hi,
I've recently started working with Power BI paginated reports and I'm encountering an issue when selecting multiple values in a parameter. When I select more than one value, the report fails to execute and throws the following error:

 "An expression of non-boolean type specified in a context where a condition is expected, near ','."
----------------------------
Query execution failed for dataset 'INS_SMR'.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.

Here’s the SQL query I'm using:

SELECT *
FROM (
SELECT
srs_sce.*,
ins_stu.*,
ins_smr.*,
ins_moa.MOA_NAME,
srs_crs.*,
LEFT(srs_sce.sce_crsc, 3) AS Course_Code,
-- Residency Status
CASE WHEN srs_sce.SCE_DPTC = 'QS' THEN 'International' ELSE 'Home' END AS Residency_Status,
-- Student Status Name
CASE
WHEN srs_sce.sce_stac = 'C' THEN 'Current'
WHEN srs_sce.sce_stac = 'W' THEN 'WITHDRAWN'
WHEN srs_sce.sce_stac = 'S' THEN 'SUSPENDED'
WHEN srs_sce.sce_stac = 'P' THEN 'PROVISIONAL'
WHEN srs_sce.sce_stac = 'PA' THEN 'PROV BUT ACTIVE'
WHEN srs_sce.sce_stac = 'EO' THEN 'EXAM ONLY'
WHEN srs_sce.sce_stac = 'G' THEN 'GRADUAND'
ELSE 'UNKNOWN'
END AS Status_Name,
-- Intake Month
CASE
WHEN srs_sce.sce_occl = 'A' THEN 'Sep'
WHEN srs_sce.sce_occl = 'A01' THEN 'Jan'
WHEN srs_sce.sce_occl = 'A02' THEN 'Feb'
WHEN srs_sce.sce_occl = 'A03' THEN 'March'
WHEN srs_sce.sce_occl = 'A04' THEN 'April'
WHEN srs_sce.sce_occl = 'A05' THEN 'May'
ELSE 'Other'
END AS Intake_Month,
ins_mod.MOD_NAME AS Module_Name,
ins_smr.AYR_Code AS [Academic Year],
ins_spr.spr_note AS Note,
cam_smc.smc_titl as Rpcl,
ins_spr.spr_eref AS Previous_Letter
FROM srs_sce
LEFT JOIN ins_stu ON srs_sce.sce_stuc = ins_stu.stu_code
LEFT JOIN ins_moa ON srs_sce.sce_moac = ins_moa.moa_code
LEFT JOIN srs_crs ON srs_sce.sce_crsc = srs_crs.crs_code
INNER JOIN ins_smr ON srs_sce.sce_scjc = ins_smr.SPR_Code
AND ins_smr.smr_proc = 'COM'
AND ins_smr.smr_agrg = 'P'
LEFT JOIN ins_mod ON ins_smr.mod_code = ins_mod.MOD_CODE
LEFT JOIN ins_spr ON srs_sce.sce_scjc = ins_spr.spr_code
LEFT JOIN cam_smc ON srs_sce.sce_scjc = cam_smc.smc_titl -- (Note: This join condition seems unusual — should it be on smc_titl?)
WHERE srs_sce.sce_ayrc = '24/25'
AND (ins_stu.stu_imac IS NULL OR ins_stu.stu_imac = 'STA GRAYDON')
AND srs_sce.sce_stac IN ('C', 'W', 'S')
-- Filters based on parameters
AND (@crs_facc IS NULL OR srs_crs.crs_facc IN (@crs_facc))
AND (@crs_dptc IS NULL OR srs_crs.crs_dptc IN (@crs_dptc))
AND (@crs_code IS NULL OR srs_crs.crs_code IN (@crs_code))
AND (@crs_name IS NULL OR srs_crs.crs_name IN (@crs_name))
-- Course and Block Pattern Matching
AND (
(srs_sce.sce_crsc LIKE '___1%' AND srs_sce.sce_blok = '1') OR
(srs_sce.sce_crsc LIKE '___2%' AND srs_sce.sce_blok = '2') OR
(srs_sce.sce_crsc LIKE '___3%' AND srs_sce.sce_blok = '3') OR
(srs_sce.sce_crsc LIKE '___4%' AND srs_sce.sce_blok IN ('F', 'P')) OR
(srs_sce.sce_crsc LIKE '___5%' AND srs_sce.sce_blok = 'P') OR
(srs_sce.sce_crsc LIKE '___6%' AND srs_sce.sce_blok = 'P') OR
(srs_sce.sce_crsc LIKE 'MCS1PRPT%' AND srs_sce.sce_blok = 'P') OR
(srs_sce.sce_crsc LIKE 'T__1%' AND srs_sce.sce_blok = 'F') OR
(srs_sce.sce_crsc LIKE 'T__2%' AND srs_sce.sce_blok = 'P')
)
) AS subquery;

 In the parameter setting i have selected multiple and allow null.

[v-sathmakuri]

Hi @gautampruthi ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @gautampruthi ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @gautampruthi ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sathmakuri]

Hi @gautampruthi ,

Thank you for reaching out to Microsoft Fabric Community.

You are encountering this issue due to the highlighted statement below. When passing parameters, only the distinct values selected through filters in the paginated report are used. The report cannot run without selecting any values. If no selection is made, it will result in an error as shown in the screenshot below. To fix this replace
AND (@crs_facc IS NULL OR srs_crs.crs_facc IN (@crs_facc)) with AND (srs_crs.crs_facc IN (@crs_facc))

Script which is causing error: 

AND (@crs_facc IS NULL OR srs_crs.crs_facc IN (@crs_facc))

Error screenshot if we don't select any value in for the parameter.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!