multiple customers on the same reporting server.

Hi,
I think this is a question for the specialists among us.
Can I use one general reporting server (installed on instance
MAINREPORTING)
for multiple customers who all have their own sql instance (CUST1,
CUST2, CUST3, ..)
I would user UserAuthenciation on the reportserver url to display the
reports specific customers
can use.
Is this possible, and what do i have to take care off concerning
installation and/or configuration (especially on
the reporting side).
Greetings
VinnieWhat you want to do is have your data source based on an expression. The
problem with this is that you have to design all your reports this way and
the data source cannot be a shared data source.
But, if you do design your reports this way you can have the expression be
based on the user (global variable User!UserID).
You would need a table somewhere mapping a user to a database, query that
table and use the result for the expression that the data source is based
on.
This all works only for RS 2005 (not RS 2000).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vinnie" <vsempoux@.gmail.com> wrote in message
news:7b3fec53-bb60-4ad9-b8d3-81d9021f1831@.e4g2000hsg.googlegroups.com...
> Hi,
> I think this is a question for the specialists among us.
> Can I use one general reporting server (installed on instance
> MAINREPORTING)
> for multiple customers who all have their own sql instance (CUST1,
> CUST2, CUST3, ..)
> I would user UserAuthenciation on the reportserver url to display the
> reports specific customers
> can use.
> Is this possible, and what do i have to take care off concerning
> installation and/or configuration (especially on
> the reporting side).
> Greetings
> Vinnie

Multiple counts in table

I've got a table containing (among some other columns) 3 different type columns.

TABLE
xxxx as int
type 1 as string
type 2 as string
type 3 as string
date as Date

First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type.

select type1, count(type1) from table group by type1
union
select type2, count(type2) from table group by type2...

Now this is time consuming and not what I would like to do

Secondly i'll try to do something like

SELECT type1 ,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar'
,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar'
,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars'
,SUM(CASE WHEN MONTH(date) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(date) = 5 THEN 1 END) AS 'Mai'
,SUM(CASE WHEN MONTH(date) = 6 THEN 1 END) AS 'Juni'
,SUM(CASE WHEN MONTH(date) = 7 THEN 1 END) AS 'Juli'
,SUM(CASE WHEN MONTH(date) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(date) = 9 THEN 1 END) AS 'Sept'
,SUM(CASE WHEN MONTH(date) = 10 THEN 1 END) AS 'Okt'
,SUM(CASE WHEN MONTH(date) = 11 THEN 1 END) AS 'Nove'
,SUM(CASE WHEN MONTH(date) = 12 THEN 1 END) AS 'Desem'
,SUM(CASE WHEN YEAR(date) = 2006 THEN 1 END) AS 'TOTAL'
FROM table
WHERE YEAR(date) = 2006
GROUP BY type

This in combination with union the other 2 types results in what I'd like to do but then again, phuu this is some crappy approach :)

Any suggustions in how to solve this?
I.E I'd like to list each type count for each month like this

Jan Feb Mars ...
type1 23 43 45
type2 12 11 15
type3 54 55 65

Any hints?

/J

Maybe something like?

set nocount on

-- --
-- I am confused by the way that the 'type' column is laid
-- out. The table design seems to indicate that there are
-- separate columns for types 1, 2 and 3; however, the report
-- layout indicates that types 1, 2 and 3 are rather specific
-- instances of a single type column. Since the latter is
-- more commonly practiced, that is the way that I shall
-- approach the problem.
--
-- --
declare @.table table
( rid integer not null
primary key,
type varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number,
'Type' + convert (char(1), number%3 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from ( select type,
month (date) as [month],
count(*) as typeCount
from @.table
group by type,
month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 11 9 10 10 11 10 10 10 5 0 0 0
-- Type2 10 10 10 10 10 10 11 10 4 0 0 0
-- Type3 10 9 11 10 10 10 10 11 4 0 0 0

|||

Unfortunatly the types are 3 different columns (at design time this report was not considered).
To work around this I union these to one "alltypes" column. This is timeconsuming and I would prefere not to do so.

Part from that your approach works find (part from pivot, I'm on SQL server 2000, not a problem though).

Is there a workaround for the unions?

Thanks

|||Sure there is; please stand by|||

set nocount on

declare @.table table
( rid integer not null
primary key,
[type 1] varchar (10),
[type 2] varchar (10),
[type 3] varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number + 1,
'Type' + convert (char(1), number%7 + 1),
'Type' + convert (char(1), (number+1)%7 + 1),
'Type' + convert (char(1), (number+5)%7 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from( select value as Type,
month (date) as [Month],
count(*) as typeCount
from @.table
unpivot ( value for Type in ([Type 1],[Type 2],[Type 3])
) as unpiv
group by value, month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 14 12 12 14 13 12 14 13 6 0 0 0
-- Type2 14 12 13 13 14 12 14 13 6 0 0 0
-- Type3 14 12 13 12 14 13 13 14 5 0 0 0
-- Type4 13 12 14 12 14 13 12 14 6 0 0 0
-- Type5 12 12 14 13 13 13 13 14 5 0 0 0
-- Type6 13 12 14 13 12 14 13 13 5 0 0 0
-- Type7 13 12 13 13 13 13 14 12 6 0 0 0