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
> > >
> > >
> >
> >
>