SSRS problem using Report model as datasource

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS problem using Report model as datasource

Author

Topic

michaeljc70
Starting Member

7 Posts

Posted - 2012-12-21 : 09:57:51

I have written many SSRS reports, but am not that familar with deploying them or report models.

Basically, I am maintaining some reports that use report models. The server and database changed. I went into the project with the datasource and model and updated the server/database in the data source and deployed the datasource and the model. The reports still don't work. The error (in Report Manager) is:

"Cannot create a connection to data source 'dataSource1'".
I get the same error when opening the report project and trying to refresh the report.

What confuses me is in the report itself, the datasource has "Report Server Model" selected, but has a long semantic query that doesn't point to the model (that I can tell).

The report has the report model selected in report manager as the data source.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 10:02:18

can you view code for report (the XML) and see if you can spot any instances of old server and database inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michaeljc70
Starting Member

7 Posts

Posted - 2012-12-21 : 10:09:44

quote:
Originally posted by visakh16

can you view code for report (the XML) and see if you can spot any instances of old server and database inside?

Yes, but there isn't any reference other than to the model. The model is in a separate solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 10:15:59

and you've redeployed the model right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michaeljc70
Starting Member

7 Posts

Posted - 2012-12-21 : 10:17:36

quote:
Originally posted by visakh16

and you've redeployed the model right?

Yes. Twice.

I can see the reference to the model in the code, but where do I see it in the designer? In the connection string is just that semantic query.

michaeljc70
Starting Member

7 Posts

Posted - 2012-12-21 : 10:19:40

The DB was also upgraded to 2008 from 2005 if that matters.

navya krishna
Starting Member

39 Posts

Posted - 2013-01-20 : 00:14:42

Dear all,

i am new for ssrs reports.i need some clarifications on ssrs.

we have one client he want's to display sales report by customer wise using different databases.for example he is maintaining every year databases seperatly in single server.so he need to display in single report.how can i display in single report.could you please any one explain clearly how to do that.

navya krishna katta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-20 : 01:29:16

are the databases in the server? if yes, you could do like


SELECT * FROM DB1.dbo.table
UNION ALL
SELECT * FROM DB2.dbo.table
...

then use this as query behind for dataset in the report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

navya krishna
Starting Member

39 Posts

Posted - 2013-01-22 : 05:28:56

Hi visakh,

if i write that query i am not getting result.

navya krishna katta

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources