Matrix trouble.

Hi all.
I got a dataset with a person and a date and i need to make a report on this.
but i want it like this:
day(date): 1 2 3 4 5 6 7
initials x x . . x x x
I have this now:
day(date): 1 2 5 6 7
initials x x x x x
Problem is i dont have a record from the 3rd and 4th, but they need to be in
the report aswell.
So i need the report to show a " . " the days they havent been there.
Can someone help me?If you don't have any data rows with values 3 and 4, they 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 7 rows with one column and these values: 1, 2, 3,
4, 5, 6, 7
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
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christian Larsen" <ChristianLarsen@.discussions.microsoft.com> wrote in
message news:E5AEA9A4-0E2A-40ED-B11A-FE0839C3DF04@.microsoft.com...
> Hi all.
> I got a dataset with a person and a date and i need to make a report on
this.
> but i want it like this:
> day(date): 1 2 3 4 5 6 7
> initials x x . . x x x
> I have this now:
> day(date): 1 2 5 6 7
> initials x x x x x
> Problem is i dont have a record from the 3rd and 4th, but they need to be
in
> the report aswell.
> So i need the report to show a " . " the days they havent been there.
> Can someone help me?

Matrix or Table?

I am trying to create a report that should have fixed columns. I
started out creating a matrix, but was having loads of trouble keeping
the columns I wanted. Here is what it should look like:
Total <0 days 1 day 2 days 3 days 4 days 5-10
days 11-15 days 16-30 days
------
Type1 | x x x x
x x x x x
Type2 | x x x x
x x x x x
Type3 | x x x x
x x x x x
------
Total | xx
Basically, I need to group the columns into the date groupings shown
above (<0 days, 1 day, etc) and have counts for each Type (Type1,
Type2) along with a grand total.
Would it be better to use a table for this or a matrix? Also, how
should I get the data grouped correctly in the most efficient manner
so the counts are sorted according to the Type and date gorups they
fall in (stored proc., views, etc.)? Any clues or tips would be
helpful.
Thanks in advance for your help.In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||If the date ranges that form the column headers are at all dynamic, I'd use a
matrix report. Create a table in your db that contains the possible column
header values, and other cols that define what each header means. E.g.:
Range Min Days Max Days
<0 null 0
1 day 0 1
2 days 1 2
...
16-30 days 16 30
Create a query that joins whatever col contains the date (or number of days)
you are grouping on to the min and max days col (make it a cartesian product
if you want to include all possible ranges defined above). Then use the
'range' field from the query as the "across" grouping.

Matrix control

Hi all,

I'm using matrix control as my reporting tools.

But i found one trouble, how can i do my matrix become like this.

Product A

Product B

Customer

Price

Qty

Price

Qty

Customer A

10

10

140

160

Customer B

12

120

120

160

Customer C

10

10

110

140

The price and qty is the static column, and i would like to make the static row also. (show in red color) but i can't.

Please help...

Thanks

Kendy

1.

Add matrix contol

2.

in the row add Customer

3. in the columns add Product

4. Right click on Data and select add column

no you will have two column

add price to the left onw and Qty to the right onw

|||

Hi Idanle,

I have create the matrix control, for the qty and price i using static column.

For static row, i have do it also but the result will be like this

Product A

Product B

Price

Qty

Price

Qty

Customer

Customer A

10

10

140

160

Customer

Customer B

12

120

120

160

Customer

Customer C

10

10

110

140

It will repeat because is static row same as static column where repeated showing out the "Price" and "Qty".

How to make the title "customer" only showing as header.

Thanks

Kendy

|||Can you send me the RDL or upload a print screen of youe design mode|||

Hi Idanle,

I have upload the excel file.

Inside the excel file, the tab name "Defect" is the problem i facing, wherelse the tab name "Output" is what i expected.

http://download.yousendit.com/ED0492A72B6C6C66

Thanks

Kendy

|||

I can't found the problem.

Can you send me your table (data source) and the RDL or the matrix definition?

|||

Hi Idanle,

As u can see from the excel file, the tab defect page.

The "Customer" header will always display as a row detail where show in red color.

But when see the output tab, where the result i wanted. The "Customer" header will remain as header, it wouldn't same like defect tab.

My question is can matrix control done as output tab, if yes how the way to do it.

Thanks

Kendy

|||

Hi,

I see the problem and I'ts seams somthing worng with your matrix setting since this problem should not occur.

This is way I ask for thr RDL and table.

If you can just create your data table in excel I will set for you the RDL

|||

Hi Idanle,

I have upload the table, rdlc and expected output i wanted.

http://download.yousendit.com/CA8B3D95276E0341

Thanks for your help.

Regards

Kendy

|||

The table you sent me is not the same table using your Expected output.

What is the problem with the current output ? it's look fine

|||

Hi Idanle,

Actually, when you look at expected output and current output. The only different is header, in expected output there are header where mark in red color wherelse in current output is without the header.

And i have try do it with static row (just try in one column name Customer), the result i get is like this, please download from the link.

http://download.yousendit.com/BC879C5820984C1A

So can i know how to do it become expected output using matrix control provided by sql server reporting services?

Thanks

Kendy

|||For column headers, what you need to do is put a rectangle in the corner cell. In this rectangle, put your Customer label textbox.

Matrix Column Trouble

I am trying to add an ungrouped columns to the end of a matrix. in RS SP2 2000

For example how do I add the three columns in red to a matrix in a report?

Q1

Q2

Q3

Total

Percentage

Turn Over Ratio

Budget Variance

Sales

100.00

100.00

100.00

300.00

100%

1.23

4.22

Cost

100.00

100.00

100.00

300.00

100%

1.23

4.22

Expense

100.00

100.00

100.00

300.00

100%

1.23

4.22

I had an instance where I wanted a total and then an average of the data in a matrix, however I was not able to figure it out. I hope someone answers, but I was unsuccessful in adding anything other than 1 subtotal.

I ended up using a table next to the matrix to display the avg. It took a bit to line it up properly but it works fine. Just a suggestion if you dont get a positive response.

|||

Thanks for the response... That is what I was afraid of, that I wouldn't be able to do this, it's just not one column that I will need to add but many. I guess I'll have to try and get something like you are describing thanks for you help. I hope somone else will respond that has had this type of issue as well.

|||but how to do it if use a table?|||

I was finally able to do it without a table. but one matrix. I did this by adding a fake groups, Expression=1, Expression=2, Expression=3.

Then using the InScope function to determine where I was at on the different subtotals.

If anyone wants to know send me an email at jwisener@.gmail.com

I just have one more problem and that is to figure out how to get rid of the white space above the matrix. Because I have to add a padding to the subtotal top to get the header to move down to the same line as the rest of the headers.

matrix column totals and the total row

I am trying to sum the columns in a matrix and am having trouble with the
totals row.
I right-click the row group and click subtotals. This creates the Total row
just fine, with the â'Totalâ' cell directly under the row group cell. However,
the cell in the data column is inaccessible, so Iâ'm not sure how or where to
put the sum expression for the columns. I canâ't put it in the â'Totalâ' cell of
the totals row, as this would simply total all the data in the matrix without
regard to column group. So what I have is the 1st row values in the total
row. If I canâ't get to the column cell on the total row, where do I enter the
sum expression for the columns?
This has to be a simple thing, if someone can just point me in the right
direction.
Thanks.It's the expression in the data cell itself. There's no separate subtotal
function.
"David" wrote:
> I am trying to sum the columns in a matrix and am having trouble with the
> totals row.
> I right-click the row group and click subtotals. This creates the Total row
> just fine, with the â'Totalâ' cell directly under the row group cell. However,
> the cell in the data column is inaccessible, so Iâ'm not sure how or where to
> put the sum expression for the columns. I canâ't put it in the â'Totalâ' cell of
> the totals row, as this would simply total all the data in the matrix without
> regard to column group. So what I have is the 1st row values in the total
> row. If I canâ't get to the column cell on the total row, where do I enter the
> sum expression for the columns?
> This has to be a simple thing, if someone can just point me in the right
> direction.
> Thanks.
>

Matrix assistance required

Hi,
I'm trying to build a report displaying a matrix that looks like the
following but have some trouble composing it:
Required output :
Code, Name, Target, Batch 1, Batch 2, Batch 3
1, One, 12, 10, 11, 12
2, Two, 9, 10, 9, 11
Totals: 21, 20, 20, 23
From the following data
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
(Assumption: all targets for a specified code are the same, batch=1 code=1
target=12 -> all targets for code=1 equal 12)
The main issue I have is adding the target totals.
Could someone please help me with this?
ErikErik,
Unless I am missing something, in your case you can simply add a matrix
subtotal element by right-clicking on a matrix group in the report layout
and choose Subtotal.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to build a report displaying a matrix that looks like the
> following but have some trouble composing it:
> Required output :
> Code, Name, Target, Batch 1, Batch 2, Batch 3
> 1, One, 12, 10, 11, 12
> 2, Two, 9, 10, 9, 11
> Totals: 21, 20, 20, 23
> From the following data
> BatchId, Code, Name, Target, Actual
> 1, 1, One, 12, 10
> 1, 2, Two, 9, 10
> 2, 1, One, 12, 11
> 2, 2, Two, 9, 9
> 3, 1, One, 12, 12
> 3, 2, Two, 9, 11
> (Assumption: all targets for a specified code are the same, batch=1 code=1
> target=12 -> all targets for code=1 equal 12)
> The main issue I have is adding the target totals.
> Could someone please help me with this?
> Erik
>|||Hi Teo,
Thank you for the reply, but that doesn't fix the problem.
The target column exists only once and doesn't repeat like the "Batch ..."
column does. I don't know exactly how to explain this issue further more
because my "matrix-knowledge" doesn't reach that far.
What I did to create the matrix is the following:
- Add a matrix control
- Drop the "BatchId" field into the "columns" cell.
- Drop the "Code", "Name" and "Target" fields into the "rows" cells (the
seconds and third fields added as row-groups.
I managed to get subtotals for the "Batch..." columns by adding a new
row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
rather not see this column in my report and it doesn't totalize the target
column.
Maybe you could post me an example based on the data described below? (if
it's not too much trouble) I like solving my own problems, but are pulling
my hairs out on this one.
Erik Tamminga, MCSD
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> Erik,
> Unless I am missing something, in your case you can simply add a matrix
> subtotal element by right-clicking on a matrix group in the report layout
> and choose Subtotal.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
> message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm trying to build a report displaying a matrix that looks like the
> > following but have some trouble composing it:
> >
> > Required output :
> >
> > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > 1, One, 12, 10, 11, 12
> > 2, Two, 9, 10, 9, 11
> > Totals: 21, 20, 20, 23
> >
> > From the following data
> >
> > BatchId, Code, Name, Target, Actual
> > 1, 1, One, 12, 10
> > 1, 2, Two, 9, 10
> > 2, 1, One, 12, 11
> > 2, 2, Two, 9, 9
> > 3, 1, One, 12, 12
> > 3, 2, Two, 9, 11
> >
> > (Assumption: all targets for a specified code are the same, batch=1
code=1
> > target=12 -> all targets for code=1 equal 12)
> >
> > The main issue I have is adding the target totals.
> >
> > Could someone please help me with this?
> >
> > Erik
> >
> >
>|||Eric,
I see now what the issue is. You need to create a subtotal on a row column
(Target). Unfortunately, this is not supported. As a workaround (if
acceptable), can you transform the data at the data source and make the
Target a Batch? In other words, instead of
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
Have
BatchId, Code, Name, Actual
1, 1, One, 10
1, 2, Two, 10
2, 1, One, 11
2, 2, Two, 9
3, 1, One, 12
3, 2, Two, 11
Target, 1, One, 12
Target, 2, Two, 9
and so on...
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:u5l62PufEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Teo,
> Thank you for the reply, but that doesn't fix the problem.
> The target column exists only once and doesn't repeat like the "Batch ..."
> column does. I don't know exactly how to explain this issue further more
> because my "matrix-knowledge" doesn't reach that far.
> What I did to create the matrix is the following:
> - Add a matrix control
> - Drop the "BatchId" field into the "columns" cell.
> - Drop the "Code", "Name" and "Target" fields into the "rows" cells
(the
> seconds and third fields added as row-groups.
> I managed to get subtotals for the "Batch..." columns by adding a new
> row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
> rather not see this column in my report and it doesn't totalize the target
> column.
> Maybe you could post me an example based on the data described below? (if
> it's not too much trouble) I like solving my own problems, but are pulling
> my hairs out on this one.
> Erik Tamminga, MCSD
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Erik,
> >
> > Unless I am missing something, in your case you can simply add a matrix
> > subtotal element by right-clicking on a matrix group in the report
layout
> > and choose Subtotal.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote
in
> > message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > I'm trying to build a report displaying a matrix that looks like the
> > > following but have some trouble composing it:
> > >
> > > Required output :
> > >
> > > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > > 1, One, 12, 10, 11, 12
> > > 2, Two, 9, 10, 9, 11
> > > Totals: 21, 20, 20, 23
> > >
> > > From the following data
> > >
> > > BatchId, Code, Name, Target, Actual
> > > 1, 1, One, 12, 10
> > > 1, 2, Two, 9, 10
> > > 2, 1, One, 12, 11
> > > 2, 2, Two, 9, 9
> > > 3, 1, One, 12, 12
> > > 3, 2, Two, 9, 11
> > >
> > > (Assumption: all targets for a specified code are the same, batch=1
> code=1
> > > target=12 -> all targets for code=1 equal 12)
> > >
> > > The main issue I have is adding the target totals.
> > >
> > > Could someone please help me with this?
> > >
> > > Erik
> > >
> > >
> >
> >
>