Why isn't code working as an expression for parameters in SQL Server Reporting with connection to Databricks SQL Warehouse using Simba Spark

Question

Error: For more information about this error navigate to the report server on the local server machine, or enable remote errors ---------------------------- Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted)

We are using Text but an expression for the parameters to work. A coworker uses Visual Studio with the SSRS tools and his will run, but mine gets above error. Have no clue why it isn't working:

="

select ep.external_patient_id MRN, ppdoc.id as Pot_Document_ID,

case when ppdoc.pot_doc_type_id = 1 then 'POT' 

	 when ppdoc.pot_doc_type_id = 2 then 'SUPPLEMENTAL' end as DOCUMENT_TYPE, 

DATE(ppd.certification_start_date) as certification_start_date,  

DATE(ppd.certification_end_date) as certification_end_date,  

DATE(padd.order_effective_start) as order_effective_start,  

DATE(padd.order_effective_end) as order_effective_end,  

pdstype.doc_status DOCUMENT_STATUS, 

CONCAT(status_update_user.first_name, ' ',  status_update_user.last_name) Status_Updatedby_User, 

ppds.updated_on DOC_STATUS_DATE, 

CONCAT(us.first_name, ' ',  us.last_name) DOC_OWNER , 

 Case when  isnull(Sites.SITE_NRS_REGION) = 'true' Then '<Blank>' else Sites.SITE_NRS_REGION End as  NursingRegion, 

Case when   isnull(ep.state) = 'true' Then '<Blank>' else ep.state end  as state   

from opex_raw.tbl_patient_pot_doc ppdoc

JOIN opex_raw.tbl_patient_pot_doc_status ppds ON ppds.patient_pot_doc_id = ppdoc.id

JOIN opex_raw.tbl_pot_doc_status_types pdstype ON pdstype.id = ppds.pot_doc_status_type_id

JOIN opex_raw.tbl_patient_pot_assigned_nurse pdocnurse ON pdocnurse.patient_pot_doc_id = ppdoc.id and pdocnurse.isActive = 1

JOIN opex_raw.tbl_user us ON us.id = pdocnurse.user_id and us.isActive = 1

LEFT JOIN opex_raw.tbl_User status_update_user on status_update_user.user_id = ppds.updated_by

LEFT JOIN opex_raw.tbl_patient_assessment_pot_versions papv ON papv.patient_pot_doc_id = ppdoc.id

LEFT JOIN opex_raw.tbl_pot_assessement_doc_dates padd ON padd.pot_assessment_version_id = papv.id and padd.isActive = 1

JOIN opex_raw.tbl_patient_pot_detail_doc ppdd ON ppdd.patient_pot_doc_id = ppdoc.id

JOIN opex_raw.tbl_patient_pot_detail ppd ON ppd.id = ppdd.patient_pot_detail_id

JOIN opex_raw.tbl_patient_pot pp ON pp.id = ppd.patient_pot_id

JOIN opex_raw.tbl_external_patient ep ON ep.patient_id = pp.patient_id

LEFT Join enriched.d_patient_demographics referral0708 on referral0708.mrn = ep.external_patient_id

Left join enriched.d_site_master Sites on Sites.SITE_ID = Referral0708.SITE_ID

WHERE ppdoc.isActive=1 and pp.isActive = 1 and ppds.isActive = 1 and ppd.isActive = 1 and papv.isActive = 1

and

Date(from_utc_timestamp(ppds.updated_on,'CST')) >= '" + FORMAT(CDATE(Parameters!start.Value), "yyyy-MM-dd") + "' AND

Date(from_utc_timestamp(ppds.updated_on,'CST')) <= DATEADD(Day, 1, '" + FORMAT(CDATE(Parameters!stop.Value), "yyyy-MM-dd") + "')

AND Case when isnull(ep.state) = 'true' Then '<Blank>' else ep.state end IN ('" + JOIN(Parameters!state.Value, "','") + "')

AND

Case when isnull(Sites.SITE_NRS_REGION) = 'true' Then '<Blank>' else Sites.SITE_NRS_REGION End IN ('" + JOIN(Parameters!NursingRegion.Value,"','") + "')

"

I have even take the parameters off and just tried to run as an expression and it won't run it. Only way it will run it is to run as query only without the parameters not using an expression. Any suggestions?

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

When connecting to Databricks SQL using the Simba Spark ODBC driver, SSRS cannot parse the Databricks parameter syntax correctly, you can construct the SQL query as an expression in SSRS, replacing the parameter placeholders with the actual parameter values.

• In the SSRS dataset query designer, instead of using the standard query text box, switch to the Expression view.
• Build your SQL query as a string expression, concatenating the static parts of the query with the parameter values.
• For date parameters, you may need to format the dates correctly using the appropriate functions.
• For multi-value parameters, you can use the JOIN function to concatenate the values with the desired separator.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 2

Hi @Maxwell, Niki,

ODBC driver supports only the question mark (?) as parameter placeholder.

So you cannot directly use Parameters!start.Value in the query.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.