How to correctly set expression Query.CommandText in SSRS?

1
votes

In my SSRS report builder I have an expression set for one of my dataset (name is dsTagAttributes). The query type is text.

This is the query expression

="select m.TagName, ta.descriptor, t.[State]  from MasterTags m inner join TagAttributesClassic ta on m.ID = ta.TagID inner join TagStates t on t.ID = m.StateID where m.PIServerID = @ServerID and t.[State] = @State and m.MetaData1 = @Station " & Parameters!AndOr.Value & "m.MetaData2 = @Device"

enter image description here

The above query expression has a parameter which is concatenated in the select statement.

The parameter @AndOr data type is text, has a default value of "or", and parameter visibility is set to hidden (screen shot below). I have set this parameter because it will be used to dynamically change the dataset result from or to and during the report runtime.

enter image description here

But when I run the report, an exception is thrown saying,
enter image description here

What could go wrong in my query command text?

Any help is greatly appreciated.

1
sql-serverreporting-servicesreportbuilder

1 Answers

0
votes

You can run Profiler against Data Source to see the query.

I assume the problem is that you miss "'" before and after each varchar value (@State, @Station).

I wouldn't use that way to use OrAnd condition

You can modify TSQL like

...
    AND 
    (m.MetaData2 = @Device AND @OrAnd = 'AND')
) 
    OR
    (m.MetaData2 = @Device AND @OrAnd = 'OR')

Just make sure you place ')' and '(' correctly, depending on business logic.