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

Dynamic Group Sorting in Crystal Report

i am using crystal report application in visual studio .net. Now, I make one crystal report, has 5 groups. Now, I want to change their order from VB .net application. What to do for that?? I'm new in crystal report.... I guess you need to organize your dynamic grouping in your report. You can use a parameter(s) for that, it will let you/user to select/change a group. Then create a formula based on the parameter response.

For example:
I would create a parameter to allow you/user to select 'SDate' Shipping Date or 'CustID' - Customer ID.

Then I would write a formula for my 1st group @.Group1:

If {?Group1}='SDate' then {table.shipping_date} else {table.CustomerID}

For my 2nd group, I would write a formula @.Group2:

If {?Group1}='SDate' {table.CustomerID} else {table.shipping_date}

After that, I would grouped my records on @.Group1 and on @.Grop2.

----

Or you can create one parameter per group and then a formula based on that parameter (1 per group as well):
For your 1st group I would create a parameter {?Group1} which allows me to select 'SDate' for shipping date, 'CustID' for CustomerID, 'Country' for country code etc.

Then create a formula @.Group1:

If {?Group1}='SDate' then {table.shipping_gdate} else
If {?Group1}='CustID' then {table.CustomerID} else
If {?Group1}='Country' then {table.CountryCode} else

For my 2nd group I would create a parameter {?Group2} and then a formula @.Group2:

If {?Group2}='Source' then {table.origination_number} else
If {?Group2}='DestCity' then {table.destination_city} else
If {?Grop2}='Carrier' then {tabme.CarrierID} else
.
.
.
You can create as many group options as you need.

I hope this will be helpful

Dynamic column in the query using SQL 2005

Hi All,

I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

Here is the query in bold, the rest if for creating a tmp table with approx values like the ones you use. NB date format is MM/DD/YYYY.

create table #x
(
country varchar(10),
Date datetime,
PRICE1 decimal(9,2),
)
insert #x
select 'USA', '1/1/2006', 3000 union all
select 'SG', '1/1/2006', 2500 union all
select 'USA', '1/2/2006', 2500 union all
select 'SG', '1/2/2006', 1500 union all
select 'USA', '1/3/2006', 1000 union all
select 'SG', '1/3/2006', 7550 union all
select 'USA', '1/4/2006', 500 union all
select 'SG', '1/4/2006', 300 union all
select 'USA', '1/5/2006', 350 union all
select 'SG', '1/5/2006', 400

select
country,
date,
price1 as dayAmount,
(select price1 from #x as b where datediff(dd,b.date,a.date) = 2 and a.country = b.country) as prevDayAmount,
(select sum(price1) from #x as b where datediff(mm,b.date,a.date) < 1 and a.country = b.country) as sumMonthAmount
from #x as a
where date = '01/03/2006'

drop table #x