1 of 1 people found this helpful
Stupid question perhaps, but did you already create the report parameter "AssetParameter" in the dataset?
If the report parameter is defined you should be prompted to specify an example parameter value when the query is run.
Thanks for the reply
See my reply to Kenji.
OLEDB-Enterprise only allows "?" as a name for a parameter.
I tried a lot of vscenario's but this is my conclusion.
A little bit disappointing.
2 of 2 people found this helpful
Ok. When creating reports in the past I've tried going directly against any of the OLEDB providers directly - but I've had more success setting up linked servers in SQL Server, and going through that route using OPENQUERY.
I'm also a fan of separating business logic from the presentation layer so I tend to create the SQL queries in stored procedures, and then just call these from the report. That way you can manage the queries in a central place close to the data source, and also have the power to properly handle the parameters you provide from the report. Build the query dynamically by concatenating the different parts with the report parameters, and provide this query variable as the second parameter of the OPENQUERY function!
PI OLEDB only allows '?' for parameters. So you should use '?' instead of @Assetparameter.
Please try it and let us know the results.
Thanks for your reply.
Using the ? was already tested by me and this works. Howeven, with multiple parameters all the parameters are named "?", and this leads to a very confusing and disapointing code. See the following screenshots:
Only "?" will be accepted as a paramter-name in the query: even "?1", "?2", etc will not be accepted. Also another letter/symbol will not be accepted.
As far as I know now is the order off appaerance in the sql-script determining important for the mapping to the report-parameters.
Is there another way to control this?
Unfortunately I do not know except for using "?". PI OLEDB does not understand @parametername.
Though I can understand that it is confusing...
I believe Asle's suggestion of using stored procedures is one option to deal with the situation.
Building Reports using PI SQL access and SSRS was subject of the UC 2016 lab titled Build Better Reports with PI SQL Framework.
Please consider downloading UC 2016 Hands-On Lab Workbooks. I was looking at the before mentioned lab manual and it has a very detailed step-by-step example for building a report using SSRS including using Report Parameters.
Like Asle, I prefer to use linked servers in SQL Server and use OPENQUERY for pass through querying, and use stored procedures to contain your query logic. With a parameterised stored procedure, you can embed your parameter names in the query (such as @AssetParameter), and then call the stored procedure from your report.