Reporting Services Query Parameters

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
amcalister
Posts: 8
Joined: Tue 07 Dec 2010 00:04

Reporting Services Query Parameters

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

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
Posts: 8
Joined: Tue 07 Dec 2010 00:04

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

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
Posts: 8
Joined: Tue 07 Dec 2010 00:04

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

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).

Post Reply