Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

sql

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

Report Model Project: Multiple Data Sources

I have setup a "Report Model Project" with multiple data sources, and
imported tables from each of these into my data source view. When I try to
use the wizard to create a report model I get an error on the "Completing the
Wizard" stage when I click "Run".
An error occurred while executing a command.
Message: Invalid object name 'dbo.<table name>'.
Command:
SELECT COUNT(*) FROM [dbo].[<table name>] t
The <table name> will always relate to the first table it attempts to
process from the secondary (non-primary) data source. I figured this out by
setting up the project from scratch with the opposite data source as primary.
It seems like it is looking in the primary data source for the table that it
should be trying to use the secondary data source to query.
Any help is appreciated. :)A report model can only reference one data source in this release. Data
source views are more flexible than supported by report models.
-Carolyn
"Greg T. Smith" wrote:
> I have setup a "Report Model Project" with multiple data sources, and
> imported tables from each of these into my data source view. When I try to
> use the wizard to create a report model I get an error on the "Completing the
> Wizard" stage when I click "Run".
> An error occurred while executing a command.
> Message: Invalid object name 'dbo.<table name>'.
> Command:
> SELECT COUNT(*) FROM [dbo].[<table name>] t
> The <table name> will always relate to the first table it attempts to
> process from the secondary (non-primary) data source. I figured this out by
> setting up the project from scratch with the opposite data source as primary.
> It seems like it is looking in the primary data source for the table that it
> should be trying to use the secondary data source to query.
> Any help is appreciated. :)

Report Model from multiple data source

Creating a Data Source View from multiple Data Sources is no problem but what happens when you try to create the actuall Report Model. Using the primary Data Source doesn't give access to other databases, since each table name starts with dbo. Is there a option/flag to set to include database name?

Report Model DSVs only support one database, to pull in data from other databases you'll need to create a Named Query.

Analysis Services, on the other hand, don't have that problem, except for being limited to one database per DSV.

Note: I'm pretty sure I'm correct, but not 100%

sql

Report model data sources

Is it possible to have a custom data processing extension as the data source in a report model project?

No, this is currently not supported.

-- Robert

sql

Report Model Data Source other than SQL Server?

Hello, is this a true statement: "The only Data Sources available to a Report Model are SQL Server 2000, SQL Server 2005, and SQL Server Analysis Services 2005".

I had read somewhere that there was going to be an ability to specify a "custom" query processors so that you could use data sources in addition to the items listed above.

Thanks!

It is true statement in this release.
MS people may know about future releases.
Philippe

Report mdoel

Hi,
Is there any way of leveraging the new report model / ad hoc functionality
when reporting from xml data sources? Everything seems to be based around if
your data is actually in an xml format. Is this at all possible?
More specifically would it be possible if the xml data source was in fact a
web service?
I realise this is kind of quirky, it's just that I'm using RS to report off
a web service and now I'm wondering is there any way I coudl allow users to
simply build their own reports.
Any help / pointers would be much appreciated...
Thanks,
PhilXML data sources do not seem to be supported for model generation.
If you go to Report Manager, Data Sources, and look at the properties of a
SQL data source, you'll see a Generate Model button at the bottom. I thought
that if you had an XML data source, you would be able to generate a model
from there as well, however, that button disappears when the data source is
XML type.
"Phil Aldis" wrote:
> Hi,
> Is there any way of leveraging the new report model / ad hoc functionality
> when reporting from xml data sources? Everything seems to be based around if
> your data is actually in an xml format. Is this at all possible?
> More specifically would it be possible if the xml data source was in fact a
> web service?
> I realise this is kind of quirky, it's just that I'm using RS to report off
> a web service and now I'm wondering is there any way I coudl allow users to
> simply build their own reports.
> Any help / pointers would be much appreciated...
> Thanks,
> Phil

Report Manager on 64 bit machine does not recognize 32 bit data sources

I'm a report developer who has created some reportsd against sybase data on another server. I am developing on a 64 bit Server 2003 box using SQL 2005 Reporting services. I hace created 32 bit ODBC data dsns using the supplied utitlity (odbcad32.exe). As I am creating the reports I can pull data using the data sources without a problem, however when I deploy my datasource and report to Report manager and run I get the following:

An error has occurred during report processing.

Cannot create a connection to data source 'Chicago_Reporting'.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This has been driving me crazy for 2 days now. Any help would be very much appreciated.

Thanks

Hi Rick!

I use a 64-bit Windows Server 2003 Enterprise server also and connect to about seven different database types, although Sybase is not one of them. Have you installed SQL Server 2005 Service Pack 2?

|||

HI Chuck,

Thanks for the response. I have limited experience with server admin stuff. I was brought for my reporting experience more then anything else. As far as I can tell our build number is 2005.090.3042.00 which equates to the original version of SP2 on the build list.

Rick

Report Manager - Data Source question

I have a report on the Reporting Server Manager where in the Data Sources
link it shows two data sources. I have removed the report in question and
redeployed the report. The two data sources reappear. Does anyone know
where the second data source is coming from in the Report Manager.
Thanks.In the project do you have 2 datasources? For some reason when you
deploy a report it deploys all of the datasources in the project. If
you have 2 datasources in the project then you will get 2 datasources
in the folder that you deploy to. I haven't found a way around this
yet...if you find a way post it so that I can do the same. I find
myself just deleting any datasource as I have folder of shared
datasources. I have 130+ reports and only 3 datasources.|||I looked in the project and I did not see the second data source. I cannot
locate the second data source in the Report Manager.
"Luke" wrote:
> In the project do you have 2 datasources? For some reason when you
> deploy a report it deploys all of the datasources in the project. If
> you have 2 datasources in the project then you will get 2 datasources
> in the folder that you deploy to. I haven't found a way around this
> yet...if you find a way post it so that I can do the same. I find
> myself just deleting any datasource as I have folder of shared
> datasources. I have 130+ reports and only 3 datasources.
>