Matrix with two datasource

Hi,
Could we a Matrix in report with moe than on datasource?
I want to show the sales amount for this week and previous week in a Matrix.
I use one datasource to retrive this week's data, and the other datasource
to retrive previous week's data. How cound I show these two data in a
matrix?On Nov 22, 2:45 pm, "ad" <fly...@.wfes.tcc.edu.tw> wrote:
> Hi,
> Could we a Matrix in report with moe than on datasource?
> I want to show the sales amount for this week and previous week in a Matrix.
> I use one datasource to retrive this week's data, and the other datasource
> to retrive previous week's data. How cound I show these two data in a
> matrix?
There are a couple of limited options here. The most flexible is to
use multiple datasources at the stored procedure/query level (that is
sourcing the matrix control). If you need to access different
databases or SQL Server Instances, this should be fairly straight
forward. For SQL Server Instances, you will want to create a linked
server. The other option, though limited, is to reference a second
report dataset via an aggregate (which does not seem to fit your needs
in this case). For this option, you would need to access a second
dataset via an aggregate expression. Something like this would work
for the second one (if it were an option):
=Sum(Fields!SomeFieldName.Value, "dsSecondDataset")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Matrix with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

Matrix with custom row total - is it possible?

Hell Everybody,
Let's suppose on my report I have a matrix with sales data - regions in
columns, years in rows. Now I need to add an additional total column with,
let's say, total profit.
I tried to accomplish that adding a hidden value. Unfortunately it seems
like I have no control over which total is displayed and which is not.
Theoretically I could place table object next to my matrix, with the same
row and header size but when it comes to pagination results are disastrous.
For any reason renderer breaks my matrix and table at different row.
Sometimes the difference is more than one row, sometimes it does not break
the table but breaks matrix - I am aware of KeepTogether property.
Please advise.
TomaszHi Tomasz,
Thank you for your post.
Have you tried SubTotal column? To add a subtotal to a matrix, add a
subtotal to an individual group within the matrix. Groups do not have
subtotals by default. To add a subtotal to a group, right-click the group
column or row header and then click Subtotal. This will open a new header
for the subtotal. Reporting Services will calculate the subtotal based on
the aggregate in the data cell for the group.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks for the answer. I have tried this. The better problem definition is
this: I have a matrix with two different measures. I want to show one of
measures with no subtotals while for the other one I want subtotals only. So
the result would look like this:
year/regional sales, USA, Canada, Total Profit
2004, $29334.00, $23232.00, ($6552.00)
2005, $534435.00, $387745.00, $223445.00
Notice that data in the last column has nothing to do with data in other
columns - it is a different measure for which region/year details are not
visible - I want subtotals only. In contrast, for the region/year sales
measure I do NOT want to show subtotals (yearly sales in this case).
Thanks,
Tomasz
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:uLCdsGKdGHA.5024@.TK2MSFTNGXA01.phx.gbl...
> Hi Tomasz,
> Thank you for your post.
> Have you tried SubTotal column? To add a subtotal to a matrix, add a
> subtotal to an individual group within the matrix. Groups do not have
> subtotals by default. To add a subtotal to a group, right-click the group
> column or row header and then click Subtotal. This will open a new header
> for the subtotal. Reporting Services will calculate the subtotal based on
> the aggregate in the data cell for the group.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tomasz,
Thanks for the update.
How about hide the column you just want Subtotals?
If possible, would you please provide some test data so I could test on my
side?
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix Row Counts

I have a matrix which sums sales to a customer by Month/Year.
Rows - Customer Name, Sum(NetSales)
Columns - Month/Year
What I want to capture is the total number of rows in the matrix.
For example, I am passing the report 144 records from my query; however
there are only 35 distinct customers.
Is there a function or property of the matrix that will give me this
information? I am currently doing a SELECT DISTICT on customer no in
another query to get this result.Using the CountDistinct Aggregate would do the job so you would not
need the second query.

Matrix Reports Filter

I am trying to have a matrix report filter on records that had sales in a prior month but no sales in the current month. Basically looking for accounts we “lost”

For example:

Name Invoice Date Quantity

Joe 4/1/2007 7

John 5/1/2007 13

John 4/1/2007 7

Tim 6/12/2007 6

The matrix report pulls back

Name Month

April May June

Joe 7

Tim 7 13

John 6

I am trying to return only names that had quantity in May and don't in June.

I would like it only to return Tim’s record because we lost his business in June.

Any ideas?

I think in your example you switched Tim and John between the query results and matrix.

Anyway, the best place to do this is in your query. I suspect you'll be running this report based on a "Month" parameter.

So in pseudo code you would write your query like

Code Snippet

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month - 1 month

AND account NOT IN

(

SELECT account

FROM accounts_table

WHERE quantity > 0

AND month = @.param_month

)

Matrix Report Calculation Issue.

Hi There,
I have this matrix report that shows sales for last couple of years and is
grouping on 5 different regions and separate years on different columns, I
want to calculate the % Sales for each region in another column. how do I
accomplish this in Reporting Services? I think the static cell on a matrix
don't allow this, I have been trying to somehow call the single cell name
from a Expression but no luck. I think this could be accomplish with some
code, if it is, can you please give some samples or direct me to a place
where I could research this topic a bit more?
any advices would be really appriaciated.
Thanks very much in advance.
MannyHi Manny:
There is a way to achieve this using "IIF" and "InScope" function. I did it
on a Matrix Report and it will dynamically calculate the percentage based the
Row/Column grouping.
Manny wrote:
>Hi There,
>I have this matrix report that shows sales for last couple of years and is
>grouping on 5 different regions and separate years on different columns, I
>want to calculate the % Sales for each region in another column. how do I
>accomplish this in Reporting Services? I think the static cell on a matrix
>don't allow this, I have been trying to somehow call the single cell name
>from a Expression but no luck. I think this could be accomplish with some
>code, if it is, can you please give some samples or direct me to a place
>where I could research this topic a bit more?
>any advices would be really appriaciated.
>Thanks very much in advance.
>Manny
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1|||Can you give us a sample on how you accomplished this please?
Thanks,
Manny
pmak wrote:
>Hi Manny:
>There is a way to achieve this using "IIF" and "InScope" function. I did it
>on a Matrix Report and it will dynamically calculate the percentage based the
>Row/Column grouping.
>>Hi There,
>[quoted text clipped - 12 lines]
>>Manny|||Here is the example:
IIF(InScope("matrix1_RowGroup1"), IIF(InScope("matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF
(InScope("matrix1_Program"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!
Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup1"),Sum(Fields!Funding.Value,"matrix1_Program")/Sum
(Fields!Funding.Value,"matrix1_ColumnGroup2")),""))IIF(InScope
("matrix1_RowGroup2"), IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF(InScope
("matrix1_RowGroup3"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value,"matrix1_Program")/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup2")),""))
Paul
Manny123 wrote:
>Can you give us a sample on how you accomplished this please?
>Thanks,
>Manny
>>Hi Manny:
>[quoted text clipped - 7 lines]
>>Manny
--
Message posted via http://www.sqlmonster.com

matrix report and customized subtotals - can it be done?

I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.

The data used for the report is like following (INV is sold and FC is forecasted):

rtype region

week

wgt

INV EU 1 150 INV US 2 200 FC US 2 400 FC US 3 1000 FC EU 2 100

I want the report to show data like this:

WEEK 1

WEEK 2

WEEK 3

TOTAL

INV

INV

FC

FC

INV

FC

EU 150 100 150 100 US 200 400 1000 200 1400

So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:

WEEK 1 WEEK 2 WEEK 3 TOTAL INV INV FC FC EU 150 100 250 US 200 400 1000 1600

How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.

I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.

Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?

Thank you in advance,

Wapper

In regards to the first question...I had a simular question:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1

as for the second question...

search forum for conditional formatting along with Inscope to accomplish what you desire...

Hope that helps...

|||

Thanks for your reply.

Yes, of course I can add my own calculated subtotals to the data list using UNION ALL and GROUP BY, so that I don't need to use totals provided by the matrix report. But is it really the only way? This looks a bit ugly because I need to query my data source twice to get basically same data. Another approach with second matrix just for totals is also ugly for the same reason. It would be much better if matrix report could do this kind of summary for me.

But of course it is still a solution.

About conditional format, I found out that this can be done without InScope function. InScope is not required because I am always within the scope of "rtype". So the expression for conditional background color should be:

=SWITCH(MAX(Fields!rtype.Value, "matrix1_rtype") = "INV", "PaleGreen", MAX(Fields!rtype.Value, "matrix1_rtype") = "FC", "LightBlue")

Wapper

|||

Hello Wapper,

It seems that a lot of the features that are desired are not yet implemented yet in SSRS 2005 and Microsoft is looking to a future release to handle additional features. I too feel that many elements of aggregation is missing and needs to be upgraded so that the majority of the calculation can be done with in SSRS instead of MS SQL Data structures.

goodguy1974

Matrix Question

I have a 2 column matrix, (2 months) with sales totals by day in the rows. Is there a way to add a 3rd column and show the +/- % for each day?

Thanks!

BobP

You'll need to add a calculation column in the query first, then add the column to the matrix.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.