Dynamic report with several datasources

Hello,

I am currently working with SQL 2005 Reporting Services and MS Visual studio 2005.

In my SQL server I have data about organizations, so that each organization's data is stored in an individual database.
In addition, the number of organizations/databases is variable. I know the number of organizations and the name of each one (that it agrees with the name of its database)
because there is a database (organizationsDB) with a table (organizationsTB) containing the list of the organizations.

So, It would be possible to create a report containing a summary of each organization? .... for example a table in which each row contains data of each organization.

Could you give me any ideas?

Thanks in advance!

You could probably do something with a stored procedure, temp tables and some dynamic sql... eg.

DECLARE @.sql as varchar(8000)

DECLARE @.mydbname

DECLARE @.tempTable TABLE

{
-- insert table schema for org summary here
}

-- insert cursor to populate each database name from the table

Set @.Sql = 'Insert into @.tempTable Select * from ' + @.mydbname + '.dbo.Mytable'

EXEC(@.Sql)

-- loop cursor

-- display on report.

Select * from @.tempTable

I'm not a fan of cursors but that should work.

cheers,

Andrew

|||

Hello,

I am trying the solution that Andrew proposed but I got this Error: Must declare the scalar variable @.tempTable

The stored procedure code is the following:

ALTER PROCEDURE [dbo].[usp_GetListOrganizations]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.sql as varchar(8000);

DECLARE @.mydbname as varchar(50);

DECLARE @.tempTable as TABLE(SumMaxSize bigint NOT NULL,SumActualSize bigint NOT NULL, NumberOfUsers int NOT NULL);

-- insert cursor to populate each database name from the table

Declare myCursor Cursor For SELECT DomainName FROM domainsDB.dbo.domainsTB

Open myCursor

Fetch Next From myCursor

Into @.mydbname

While @.@.FETCH_STATUS = 0

Begin

Set @.Sql = 'Insert into @.tempTable Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'

EXEC(@.Sql)

-- loop cursor

Fetch Next From myCursor

Into @.mydbname

End

Close myCursor

SELECT * FROM @.tempTable

END

what is wrong? Could you help me?

Thanks in advance!

|||

...I have proved to use sp_executesql too:

Set @.Sql = 'Insert into ' + @.tempTable + ' Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'

EXEC sp_executesql @.Sql, N'@.tempTable TABLE, @.mydbname varchar(50)', @.tempTable, @.mydbname

But result is 'Must declare the scalar variable @.tempTable' twice

Thanks!

|||

Unfortunately that won't work very well since it's running in a different memory space.

What about

INSERT INTO @.tempTable

EXEC(@.Sql)

where @.Sql is the dynamic select SQL you are working with?

Alternatively, you could use a physical table and some sort of common identifier like user id + datetime.

Hope this works. Also check syntax on @.tempTable declaration. Not sure if it is correct.

cheers,

Andrew