Prev: Link from One report to another
Next: Reporting Services not loading
From: James on 23 Mar 2010 08:59
Is it possible to compare two matrixs

I have one matrix that returns

Jan Feb March April
5 10 15 20


And the second returns


Jan Feb March April
10 50 45 40

I then want to create a 3rd matrix that compares the first one to the Second
in %...so in this example I would want


Jan Feb March April
50% 10% 33% 50%


how do I do this?




From: Mike Lum on 23 Mar 2010 14:24
What I would do is in the third matrix, store the row from the first
and second matrix in the third matrix but hide them. You can then
perform the calculations on these hidden rows.


From: James on 23 Mar 2010 16:38
Thanks
But how do I get the results of the first 2 matrix's into the 3rd? And once
I've done that, how do I perform calculations on the matrix??


"Mike Lum" <mlum(a)comtime.com> wrote in message
news:a605d2bf-60af-4fc8-85af-b39d0d63e28e(a)z4g2000yqa.googlegroups.com...
> What I would do is in the third matrix, store the row from the first
> and second matrix in the third matrix but hide them. You can then
> perform the calculations on these hidden rows.
>
>
From: Patrice on 24 Mar 2010 09:45
Hello,

Not sure what is the problem. You could just create a query that computes
the data and use this ?

Not sure if thinking of this as matrices is intentional but IMO it would be
better to start from the data you need and you just have to combine both
query to be able to compute v1/v2...

--
Patrice

"James" <Private(a)non.com> a �crit dans le message de groupe de discussion :
OZZwYioyKHA.2012(a)TK2MSFTNGP04.phx.gbl...
> Is it possible to compare two matrixs
>
> I have one matrix that returns
>
> Jan Feb March April
> 5 10 15 20
>
>
> And the second returns
>
>
> Jan Feb March April
> 10 50 45 40
>
> I then want to create a 3rd matrix that compares the first one to the
> Second in %...so in this example I would want
>
>
> Jan Feb March April
> 50% 10% 33% 50%
>
>
> how do I do this?
>
>
>
>
>

From: Patrice on 24 Mar 2010 13:09
> So I don't even have the numbers I am working with until the data is put
> into the matrix...

You could also group using SQL. Basically the idea is that you need only a
single row per row/col combination. It could be something such as :

CREATE TABLE t(Row INT,Col SMALLDATETIME,Value INT)
GO
INSERT INTO t
SELECT 1,'20090101',1
UNION ALL SELECT 1,'20090101',1
UNION ALL SELECT 1,'20090101',1
UNION ALL SELECT 1,'20090101',1
UNION ALL SELECT 1,'20090101',1
UNION ALL SELECT 1,'20090201',10
UNION ALL SELECT 1,'20090301',15
UNION ALL SELECT 1,'20090401',20
UNION ALL SELECT 1,'20100101',10
UNION ALL SELECT 1,'20100201',50
UNION ALL SELECT 1,'20100301',45
UNION ALL SELECT 1,'20100401',40
GO
-- Matrix 1
SELECT Row,Col,SUM(Value) AS Value FROM t WHERE Col BETWEEN '20090101' AND
'20091201' GROUP BY Row,Col
-- Matrix 2
SELECT * FROM t WHERE Col BETWEEN '20100101' AND '20101201'
-- Matrix 3
SELECT *,100.0*a.Value/b.Value FROM (
SELECT Row,Col,SUM(Value) AS Value FROM t WHERE Col BETWEEN '20090101' AND
'20091201' GROUP BY Row,Col
) a
JOIN t b ON b.Row=a.Row AND b.Col=DATEADD(year,1,a.col)
WHERE a.Col BETWEEN '20090101' AND '20091201'

Note :
- that the value 5 for january 2009 is entered as 5 lines, grouping them on
the fields used to create the rows/columns allows to aggregate those value
server side and still keep the ability to create a matrix in RS
- that the matrix 1 query is just taken as is in matrix 3
- I could have done the same for matrix 2
- that with the matrix 3 you have all the data needed to create whatever
matrix you want so you could even keep a single query and be able to create
all those matrices...
--
Patrice


 | 
Pages: 1
Prev: Link from One report to another
Next: Reporting Services not loading