How to set the 'command text" in SSRS report 'Dataset'

Question

I have an SSRS report connecting to Snowflake data source. In order to connect to snowflake, I need to pass a string query for all parameters (see below) in the report.:

                     Dataset: ***dsConsolidatedAccountsFlow***

See query string below:

="SELECT * FROM TABLE (DEV_CL_DB.SRC_GS_ASCEND.ASCEND_ALL_SEARCH_REPORT('" + Format(Parameters!EffectiveDateMin.Value, "yyyy-MM-dd") + " 00:00:00','" + Format(IIf(IsNothing(Parameters!EffectiveDateMax.Value),DateValue("12/31/2999"),Parameters!EffectiveDateMax.Value), "yyyy-MM-dd") + " 23:59:59','" + IIf(IsNothing(Parameters!ReferenceNumber.Value),"",Parameters!ReferenceNumber.Value) + "','" + IIf(IsNothing(Parameters!ProductDesc.Value),"",Parameters!ProductDesc.Value) + "','" + IIf(IsNothing(Parameters!NewOrRenewal.Value),"",Parameters!NewOrRenewal) + "','" +IIf(IsNothing(Parameters!BrokerName.Value),"",Parameters!BrokerName.Value) + "','" +IIf(IsNothing(Parameters!Status.Value),"",Parameters!Status.Value) + "','" +IIf(IsNothing(Parameters!PrimaryOrExcess.Value),"",Parameters!PrimaryOrExcess.Value) + "','" +IIf(IsNothing(Parameters!Carrier.Value),"",Parameters!Carrier.Value) + "','" +IIf(IsNothing(Parameters!PremiumMin.Value),"",Parameters!PremiumMin) + "','" +IIf(IsNothing(Parameters!PremiumMax.Value),"",Parameters!PremiumMax) + "','" + IIf(IsNothing(Parameters!InsuredName.Value),"",Parameters!InsuredName.Value) + "','" +IIf(IsNothing(Parameters!BrokerContactName.Value),"",Parameters!BrokerContactName.Value) + "','" + IIf(IsNothing(Parameters!BusinessSegment.Value),"",Parameters!BusinessSegment.Value) + "','" + "'"+ ")) ORDER BY 1;"

When I execute the query I get the following error message.

I would appreciate any suggestions on how to get the query to execute correctly. Thanks in advance for your help.

Answer 1

Hi @Barnes, Pauline L (Chief Information Office - IT)

According to the expression of the dataset query, the problem can be checked from the following aspects.

1 Check if you can connect to the data source, try a simple sql query to check the connection.

2 Try splitting the query into parts and see if each part works.

3 Check whether the expression of the dataset query is normal. A syntax error occurs when an expression is used in a dataset query. Try putting the query expression into the textbox and get the query. Check for syntax problems.

You can refer to this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e9e31318-932d-4a8d-9732-ed99e243bc94/cant-set-the-command-text-for-the-dataseterror-during-the-processing-of-the-command-text?forum=sqlreportingservices.

Best regards,

Aniya