Report Builder DataSet Query

I have successfully copied, modified, added, to our SSRS reports. I’m more of a crystal reports person, how to get the DataSet query parameter generic? I’m using the tableguid in the query and it works fine…just for that one report.

FROM JobOper_" + Parameters!TableGuid.Value + " T1

Open up one of the out of the box reports and look at that one to see how it is generally done.

Try to update your select to be:

Select T1.ActLaborCost From JobOper_" + Parameters!TableGuid.Value + " T1 WHERE T1.SubContract = 0 "

Thanks John for the suggestion, I did look at that previously and I couldn’t get it to work.
SELECT
JobOper_" + Parameters!TableGuid.Value + “.ActLabCost
FROM
JobOper_” + Parameters!TableGuid.Value + "
WHERE
JobOper_" + Parameters!TableGuid.Value + ".SubContract = N’False’

You don’t need it in the fields, just the table. So.

Use alias for the table names and the general format would be

SELECT T1.ActLabCost
FROM JobOper_0123456789ABCDEF  T1
WHERE T1.Contract = 1

And since that’s actually a VB formula it will need to result in a string, so the expression would be

"=SELECT T1.ActLabCost
 FROM JobOper_" + Parameters!TableGuid.Value + "  T1
 WHERE T1.Contract = 1"

Which isn’t obvious until you reformat it like:

"=SELECT T1.ActLabCost "
+ " FROM JobOper_" + Parameters!TableGuid.Value + "  T1 "
+ " WHERE T1.Contract = 1"

edit

make sure you have spaces separating the various string parts. So you don’t end up with
"SELECT T1.ActLabCostFROM JobOper_0123456789ABCDEF T1WHERE T1.Contract = 1"

Interesting, I’ve tried the code and I’m getting an error.
SELECT
ActLabCost
FROM
JobOper_” + Parameters!TableGuid.Value + "
WHERE
SubContract = N’False’

Incorrect syntax near ‘”’.
Unclosed quotation mark after the character string ’
WHERE
SubContract = N’False’’.

Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)

An error has occurred during report processing. (rsProcessingAborted)

Calvin, is this Text or a Stored Procedure? Not sure which one is which as i’m still new to this process.

First off, be careful when copying code from posts. If the person that posted it didn’t use the formatting syntax, single and double quotes are replaced with “fancy quotes”

real Quotes -> "double", 'single'
fancy quotes -> “double”, ‘single’

See the difference:
image

Code in E10 can’t use the Fancy quotes.

Since the query is actually an expression, the window will look like:

image

Clicking the [fx] button (arrowed above), brings up the expression editor:

image

this is where your expression needs to be.

And one last thing, you do have a datasource setup?

Yep, I do have a DataSource for the report. I can run the report if I take out the query for the Labor Cost.
Capture
Good Point on the quotes, missed that one, I will check.