How to query multiple Connections in same report?

[jesse.p]

I am a report writer and have access to Report Studio.  The vendor controls the data sources and I cannot make changes to the data itself.



I have one data source with multiple Connections.  Each connection is a monthly snapshot of the database.  I need to create a report with 2 queries that pulls the same fields from two separate Connections, then join them together to compare changes month-to-month.



Is this possible?  My vendor is telling me no, but I can't believe there is no way to compare monthly trends of the data.  I am fairly advanced with report studio, but I do not see any options in the new query to specifically select a separate Connection.



Any advice is greatly appreciated.

[hespora]

I'm assuming you do *not* just need to include data from both, but instead, you actually need to calculate with values from both? (Cause if not, then report booklets would be your way to go)

The only way I know how to make that work is probably a hack, and everyone in charge of admining a Cognos implementation will likely roll their eyes at this:

- create a dummy report with your first query. we will discard this report later
- create a second report with your second query
- in your first report, go to the query explorer. select the query, and in its properties under data, go to generated SQL. In the window that pops up, hit convert. In the new window, hit ok.
you should now see your query referencing an SQL object
- copy your query to the second report
- create a join as per your requirements

[BigChris]

Can you hear the collective sharp intakes of breath?  ;D

[CognosPaul]

I'm not convinced that the SQL trick would work - it still calls the data source, and the user would still be prompted to select the connection. It sounds like there are multiple structurally identical databases, with a single data source calling them. My brain hurts thinking about this. I just want to point out that the Data Warehouse Toolkit is big enough to be considered a blunt object.

Jesse, you said you can't change the data, but can you create data sources? Why not clone the data source? The user will be prompted for both data source 1 and data source 2.  Then you could use the two inline SQL statements, one set to DS1 and the other to DS2.

[jesse.p]

I'm assuming you do *not* just need to include data from both, but instead, you actually need to calculate with values from both? (Cause if not, then report booklets would be your way to go)

The only way I know how to make that work is probably a hack, and everyone in charge of admining a Cognos implementation will likely roll their eyes at this:

- create a dummy report with your first query. we will discard this report later
- create a second report with your second query
- in your first report, go to the query explorer. select the query, and in its properties under data, go to generated SQL. In the window that pops up, hit convert. In the new window, hit ok.
you should now see your query referencing an SQL object
- copy your query to the second report
- create a join as per your requirements

I've already devised this trick myself and have done this before :).  I actually have a folder of SQL snippets saved in my network drive of my frequently-used fields I need to cross-reference a different source.  EDIT: It does not work in this case, because the data is in the same source, just accessed via a different connection.

[jesse.p]

Jesse, you said you can't change the data, but can you create data sources? Why not clone the data source? The user will be prompted for both data source 1 and data source 2.  Then you could use the two inline SQL statements, one set to DS1 and the other to DS2.

I literally only have access to Report Studio, and Cognos Connection (to manage the saved reports).

We are in the process of implementing our own in-house server.  I have become frustrated with little things like this that I cannot see/control.

Just to be clear, I only have 1 data source.  The source has 19 connections listed...a daily snapshot and 18 months of snapshots.  The structure of the data is identical, but the contents are different.

[dougp]

Ask your Cognos admin to create a Framework Manager model that does this for you.

[New_Guy]

Hi,
Until you have the connections sorted out, the last option you have is to run the report with each data source connection in excel and prepare a report of the differences. Not a good way to approach when you a BI tool like cognos available, but serves the purpose.
Good luck
New guy

[CognosPaul]

I think I've got it!

Set up report view that run the reports with a specified connection. Then you can use a job to build a datasets with those two reports as the base. Then you could build ANOTHER report on top of that data set that does what you need.

[jesse.p]

I appreciate your effort.  I tried this, but I don't think I have permission to create a data set.  It is not an option anywhere when creating a job, and if I go to "My Data Sets" and click create, there is nothing offered to me.

I'm writing this off as "not possible" for now, until we have migrate the Cognos server in-house and I will have access to the framework manager.

[New_Guy]

Hi,
Did you try the manage external data option, if this is enabled by your administrator for you? You can access an excel file from your local drive and create a report. Just a last thought.
Good luck
New guy.