Reporting Services SP using Dynamic Queries

Hey i am using Stored Procedures in Sql Reporting Services.
This SP has some parameters and based on these parameter's value Final query is being created at run time. But when i use this SP in VS.Net Reporting Services as dataset then it doesnt show its fields.

Here I am writing some part of my SP to show how things are working at my end. @.Country , @.StartDate , @.EndDate are SP parameters and based on these parameter’s value, some part of query is being added to main query. So in this case again I am not able to get fields in report.

IF LTRIM(RTRIM(@.Country)) IS NOT NULL

BEGIN
SET @.CountryQuery = " AND ID3.id_value LIKE '" + LTRIM(RTRIM(@.Country)) +"' "
END

IF LTRIM(RTRIM(@.StartDate)) IS NOT NULL AND LTRIM(RTRIM(@.EndDate)) IS NOT NULL
BEGIN
SET @.DateQuery = " AND ( "+@.FieldName+" >= '" + LTRIM(RTRIM(@.StartDate)) + "' AND "+@.FieldName+" <= '" + LTRIM(RTRIM(@.EndDate)) + "' ) "
END

SET @.Query = " SELECT DISTINCT field1, field2, field3 FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id_fk = T2.id_code " + @.CityQuery + " WHERE tr_status =1" + @.DateQuery

EXEC (@.Query)

Even I have used temp table in SP and stored values in it but could not able to get desired result (get fields in report).

PLease help me
Thanks

Hi,

Try these steps:

1. In your DataSet put the query with fixed values (i.e. without parameters):

SELECT DISTINCT field1, field2, field3 FROM Table1 ...

2. Execute the Query

3. Go to the "DataSets Window" and select "refresh"

4. Now you should replace the query above with the Stored Procedure command

I think this solves your problem.

--

Telmo Moreira

Reporting Services OLAP Report through Report Manager

I am creating a report in Reporting Services that hits an Analysis services
cube using MDX. The report has parameters so some of the MDX queries are
placed into strings. When running the report on my local machine it works
correctly, but when I deploy the report and run it through Report Manager it
locks up and will not display. It also brings down the Web server for a few
minutes.
The parameter lists in the report are created with a static MDX statement
and they will display, but the report keeps turning and never renders to the
screen.
I have looked at the site
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
but I cannot find anything on why the report would work locally but not
through Report manager.
Any advice or directions would be greatly appreciated.I was able to solve this problem. The SQK server with my Cubes had Analysis
Services SP3. The box that I was developing the OLAP report was also running
AS SP3 but the Report Server did not have any service packs installed. This
was causing the OLAP report to lock up on the report server. Analysis
services was installed onto the report server after the report server and
visual studio. I believe that Analysis services over wrote the MSOLAP80.dll
to the 8.0.195 version.
After installing AS SP3 on the report server I was able to view OLAP Reports
through the Report Manager without locking up or bring down the web server.
"AndyT" wrote:
> I am creating a report in Reporting Services that hits an Analysis services
> cube using MDX. The report has parameters so some of the MDX queries are
> placed into strings. When running the report on my local machine it works
> correctly, but when I deploy the report and run it through Report Manager it
> locks up and will not display. It also brings down the Web server for a few
> minutes.
> The parameter lists in the report are created with a static MDX statement
> and they will display, but the report keeps turning and never renders to the
> screen.
> I have looked at the site
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
> but I cannot find anything on why the report would work locally but not
> through Report manager.
> Any advice or directions would be greatly appreciated.
>

Reporting Services Error - Please help

Dear all

Please give kind attension to my queries

When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help

Reporting Services Error

---

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help

---

What OS are you using?|||

Thank u

I am using windows server 2003

|||

Here is one solution... requires a reinstall.

http://ismailtutumluer.wordpress.com/2007/01/16/the-version-of-the-report-server-database-is-either-in-a-format-that-is-not-valid-or-it-cannot-be-read-the-found-version-is-unknown-the-expected/

cheers,

Andrew

|||

Sir Thank u for ur attension to my queries

Again I installed the reportserver in another instance name after using windows cleanup utility but the same error is coming when I am configuring the report server

the following are not activated

1. report server virtual directory

2. report manager virtual directary

3. web service identity

4. database setup

Please help me...

With regards

Polachan

|||

Can you connect to the database from SQL Management Studio?

Did you try changing the Reporting Services service security account?

Another thing to try would be to use SQL Profiler to determine what is happening.

cheers,

Andrew

|||have u checked whether ur IIS s poing to right virual directory ?
-prashant
|||

Dear Andrew

Thank u for attention to my queries. Please help me for the following

I could not connect report server from SQL Management Studio. I did the following ways from SQL server Management studio

to connect the database

1. Servertype - ReportServices

2. Server Name - Not available in the list .

I am beginner so let me know

How we can change Reporting Services service security account . Need ?

What is the step to get SQL profiler

Help me Andrew

Regards

Polachan

|||

Dear Prashant

No I didnot check IIS.

Please give me detail to check IIS about virtual directory..

I am waiing for hearing from u ..

Regards

Polachan Paily

|||ok...goto Start>Settings>Control Panel>Administrative Tools. Click on Interner Information Services ..open it
expand the nodes in left side panel. go to yourmachinename> Web Sites > Default Web Sites> Reports ...right click on Reports and see whether its pointing to right directory.
also do same thing for ReportServer node.

in command prompt type services.msc, a window will open ...check whether IIS service is running.

then go to http://localhost ....what do u see?

|||

Dear Sir,

I tried like that way still it is not working . At last we created a virtual pc and and through that vritual pc , Report server can be used.. No problem there. I create a sample report using report server in Virtual PC

Thank u sir for ur caring in my queies

With regards

Polachan

|||hey ...what is virtual pc ? how do u use it ? i might need that for my own reporting .....can u tell me more about it ?

-prashant
|||

Polachan,

Have you tried to upgrade the database version?

Under the start->Programs->Microsoft SQL Server 2005->Configuration Tools->Reporting Services Configuration.

select "Database setup" and here you will have an option to upgrade the database version.

Rahul

|||

Yes Rahul many time I tried like that way

Thank for ur caring in my quries

Reporting Services Error - Please help

Dear all

Please give kind attension to my queries

When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help

Reporting Services Error

---

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help

---

What OS are you using?|||

Thank u

I am using windows server 2003

|||

Here is one solution... requires a reinstall.

http://ismailtutumluer.wordpress.com/2007/01/16/the-version-of-the-report-server-database-is-either-in-a-format-that-is-not-valid-or-it-cannot-be-read-the-found-version-is-unknown-the-expected/

cheers,

Andrew

|||

Sir Thank u for ur attension to my queries

Again I installed the reportserver in another instance name after using windows cleanup utility but the same error is coming when I am configuring the report server

the following are not activated

1. report server virtual directory

2. report manager virtual directary

3. web service identity

4. database setup

Please help me...

With regards

Polachan

|||

Can you connect to the database from SQL Management Studio?

Did you try changing the Reporting Services service security account?

Another thing to try would be to use SQL Profiler to determine what is happening.

cheers,

Andrew

|||have u checked whether ur IIS s poing to right virual directory ?
-prashant
|||

Dear Andrew

Thank u for attention to my queries. Please help me for the following

I could not connect report server from SQL Management Studio. I did the following ways from SQL server Management studio

to connect the database

1. Servertype - ReportServices

2. Server Name - Not available in the list .

I am beginner so let me know

How we can change Reporting Services service security account . Need ?

What is the step to get SQL profiler

Help me Andrew

Regards

Polachan

|||

Dear Prashant

No I didnot check IIS.

Please give me detail to check IIS about virtual directory..

I am waiing for hearing from u ..

Regards

Polachan Paily

|||ok...goto Start>Settings>Control Panel>Administrative Tools. Click on Interner Information Services ..open it
expand the nodes in left side panel. go to yourmachinename> Web Sites > Default Web Sites> Reports ...right click on Reports and see whether its pointing to right directory.
also do same thing for ReportServer node.

in command prompt type services.msc, a window will open ...check whether IIS service is running.

then go to http://localhost ....what do u see?

|||

Dear Sir,

I tried like that way still it is not working . At last we created a virtual pc and and through that vritual pc , Report server can be used.. No problem there. I create a sample report using report server in Virtual PC

Thank u sir for ur caring in my queies

With regards

Polachan

|||hey ...what is virtual pc ? how do u use it ? i might need that for my own reporting .....can u tell me more about it ?

-prashant
|||

Polachan,

Have you tried to upgrade the database version?

Under the start->Programs->Microsoft SQL Server 2005->Configuration Tools->Reporting Services Configuration.

select "Database setup" and here you will have an option to upgrade the database version.

Rahul

|||

Yes Rahul many time I tried like that way

Thank for ur caring in my quries