Reporting Services Query Parameters

amcalister · Post by **amcalister** » Tue 07 Dec 2010 17:58

I'm trying to use dotConnect with Reporting Services. It mostly works except that I can't figure out how to use a report parameter in a query. Does this work? If so how? I've created a parameter and then tried to reference it with @param and :param without success.

Shalex · Post by **Shalex** » Wed 08 Dec 2010 17:58

I have tried dotConnect for PostgreSQL v 5.0.58 with SQL Server Reporting Services 2008 (the project type is Report Server Project) and successfully generated report with this command text:
SELECT deptno, dname, loc
FROM dept
WHERE (deptno = :p1)

Please tell us:
1) the versions of your products;
2) describe the problem -
a) the exact steps we should follow to reproduce the issue;
b) the error text or incorrect behaviour you have obtained.

amcalister · Post by **amcalister** » Wed 08 Dec 2010 19:47

I'm using dotconnect 4.55 with Postgres 8.3.

I have a query that looks like this:

SELECT actnum, lglnum, notenum
FROM mainschema.actfile
WHERE
(mainschema.actfile.lglnum = :xlegal)

xlegal is defined as a report parameter.

When I try to preview the report I get this error: Parameter 'xlegal' is missing.

Shalex · Post by **Shalex** » Thu 09 Dec 2010 13:51

Probably, you are facing the limitation of Report Server Project's Query Designer - it doesn't parse the database and schema names before the name of object (database_name.schema_name.object_name). Please set the name of your schema in your connection string (schema=mainschema;) and use the following query:
SELECT actnum, lglnum, notenum
FROM actfile
WHERE (lglnum = :xlegal)

Also please note that the user id and password connection string parameters should be set separately in the Credentials tab when creating Shared Data Source in the Report Server Project.

amcalister · Post by **amcalister** » Thu 09 Dec 2010 17:30

Removing the schema name from the query produces the same result; Parameter 'xlegal' is missing.

Shalex · Post by **Shalex** » Mon 13 Dec 2010 17:12

The parameterized commands in Reporting Services are supported by dotConnect for PostgreSQL starting from the 4.65.79 version (revision history). Please upgrade or check the workability of new versions by using dotConnect for PostgreSQL Trial v 5.0.58 (the trial period in 5.x version is reset - it does not depend on usage of the previous versions of dotConnect for PostgreSQL).