Repost : Help on matrix and or table or ??

Hi Gurus
Sorry to insist but the need of an answer begin more urgent
we have a dataset like this
Key1 Primary Key of record
Key2 1st Foreign Key
Key3 2nd Foreign Key
Duration in seconds
Product Pn (field for pivot)
Qty Qn (value link to Product)
what we need
P1--Pn
Key3 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
Key3 Key2 Key1 Duration Q1--Qn
Gran Total Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
I seach among the messages of this group and i did not found an answer to
our problem.
It seems for us, that matrix answer to the need of building multiple
columns depending on the different content of one field ie product , and
give the possibility to associate a value link to this field ie Qty in our
example, the informations on rows seems to be only grouping fields, but
not value of field linked to this grouping field ie duration and count of
the primary key in our example
With table i can associate fields to grouping fields but we need to know all
the fields we want to display , if we have more then 20 products we have
to previous one field for each value possible of product : 20 and try to
hide the bank fields
We thought about to to reverse the table by an Stored procedure , but we did
not find a way to build or add dynamic columns to the table
What we need is a mix of table and matrix depending togather on the same
grouping fields .
Hope this is more clear, and thanks to inform us if exist a solution or not
with RS to our problem
Philippe
Espace-NTICYou can nest a table in a matrix (and vice versa). But I don't quite
understand your question. Maybe if you provided some instance data
(examples) it would be easier. Also, if your newsreader allows you to attach
an example instead of posting inline, it might be easier to read.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> Hi Gurus
> Sorry to insist but the need of an answer begin more urgent
> we have a dataset like this
> Key1 Primary Key of record
> Key2 1st Foreign Key
> Key3 2nd Foreign Key
> Duration in seconds
> Product Pn (field for pivot)
> Qty Qn (value link to Product)
> what we need
> P1--Pn
> Key3 Sum(Duration) Count(Key1)
> Sum(Q1)--Sum(Qn)
> Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
> Key3 Key2 Key1 Duration
> Q1--Qn
> Gran Total Sum(Duration) Count(Key1)
> Sum(Q1)--Sum(Qn)
>
> I seach among the messages of this group and i did not found an answer to
> our problem.
> It seems for us, that matrix answer to the need of building multiple
> columns depending on the different content of one field ie product ,
> and
> give the possibility to associate a value link to this field ie Qty in
> our
> example, the informations on rows seems to be only grouping fields, but
> not value of field linked to this grouping field ie duration and count of
> the primary key in our example
> With table i can associate fields to grouping fields but we need to know
> all
> the fields we want to display , if we have more then 20 products we
> have
> to previous one field for each value possible of product : 20 and try to
> hide the bank fields
> We thought about to to reverse the table by an Stored procedure , but we
> did
> not find a way to build or add dynamic columns to the table
> What we need is a mix of table and matrix depending togather on the same
> grouping fields .
> Hope this is more clear, and thanks to inform us if exist a solution or
> not
> with RS to our problem
> Philippe
> Espace-NTIC
>
>
>|||OK, I understand now. We are looking at supporting this natively in a future
release but I don't think you can do this in V1 without some tricks. I'll
forward it on to our trick specialist and get him to post a suggestion...
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Brian
> for an example assume we start from the "Company Sales" report from RS
> samples
> This report has two grouping lines based on ProductCategory field and
> ProductSubcategory field
> This report has dynamic columns based on the different values of two
> nested
> fields OrderYear OrderQuarter
> At those dynamic column is associated a field : sum(SalesValue)
> what we need is ,:
> ADD at the left side of those dynamic columns , TWO STATIC columns one
> giving for example
> the content of SUM(SalesOrderDetail.UnitPrice *
> SalesOrderDetail.OrderQty
> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one the
> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
> The content will follow the same grouping as for dynamic columns
> we join an XLS file in zip mode,showing the result we expect
> We have a lot of those reports built actually under Excel , that we want
> to
> move under RS
> Thanks for your help
> Philippe
> Espace-NTIC
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
>> You can nest a table in a matrix (and vice versa). But I don't quite
>> understand your question. Maybe if you provided some instance data
>> (examples) it would be easier. Also, if your newsreader allows you to
> attach
>> an example instead of posting inline, it might be easier to read.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
>> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
>> > Hi Gurus
>> >
>> > Sorry to insist but the need of an answer begin more urgent
>> >
>> > we have a dataset like this
>> > Key1 Primary Key of record
>> > Key2 1st Foreign Key
>> > Key3 2nd Foreign Key
>> > Duration in seconds
>> > Product Pn (field for pivot)
>> > Qty Qn (value link to Product)
>> >
>> > what we need
>> >
>> > P1--Pn
>> > Key3 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Key1 Duration
>> > Q1--Qn
>> > Gran Total Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> >
>> >
>> > I seach among the messages of this group and i did not found an answer
> to
>> > our problem.
>> >
>> > It seems for us, that matrix answer to the need of building multiple
>> > columns depending on the different content of one field ie product ,
>> > and
>> > give the possibility to associate a value link to this field ie Qty in
>> > our
>> > example, the informations on rows seems to be only grouping fields,
> but
>> > not value of field linked to this grouping field ie duration and count
> of
>> > the primary key in our example
>> >
>> > With table i can associate fields to grouping fields but we need to
>> > know
>> > all
>> > the fields we want to display , if we have more then 20 products we
>> > have
>> > to previous one field for each value possible of product : 20 and try
> to
>> > hide the bank fields
>> > We thought about to to reverse the table by an Stored procedure , but
>> > we
>> > did
>> > not find a way to build or add dynamic columns to the table
>> >
>> > What we need is a mix of table and matrix depending togather on the
> same
>> > grouping fields .
>> >
>> > Hope this is more clear, and thanks to inform us if exist a solution
>> > or
>> > not
>> > with RS to our problem
>> >
>> > Philippe
>> > Espace-NTIC
>> >
>> >
>> >
>> >
>> >
>>
>
>|||Sleazy Hack # 4123:
1. Put two matrixes side-by-side with the same row groupings.
2. The left matrix should have the static columns. The right matrix should
have the dynamic columns.
3. In the right matrix, remove the text from the row headers and shrink the
row headers to be as narrow as possible.
4. As a final optimization, hand-edit the RDL to set the width of the row
headers to 0 (this can't be done in the design tool). After doing this, it
will be hard to edit the matrix in the design tool, so make sure you do this
after you're 100% done with all of your other edits.
Note: This won't work if you have drilldown enabled on row groupings, since
there's no way to synch up the drilldowns between the two matrixes. If you
need drilldown on rows, you'll need to wait for a future version where we
will support this functionality natively.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ugkhjUmXEHA.3668@.TK2MSFTNGP09.phx.gbl...
> OK, I understand now. We are looking at supporting this natively in a
> future release but I don't think you can do this in V1 without some
> tricks. I'll forward it on to our trick specialist and get him to post a
> suggestion...
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
>> Hi Brian
>> for an example assume we start from the "Company Sales" report from RS
>> samples
>> This report has two grouping lines based on ProductCategory field and
>> ProductSubcategory field
>> This report has dynamic columns based on the different values of two
>> nested
>> fields OrderYear OrderQuarter
>> At those dynamic column is associated a field : sum(SalesValue)
>> what we need is ,:
>> ADD at the left side of those dynamic columns , TWO STATIC columns one
>> giving for example
>> the content of SUM(SalesOrderDetail.UnitPrice *
>> SalesOrderDetail.OrderQty
>> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one the
>> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
>> The content will follow the same grouping as for dynamic columns
>> we join an XLS file in zip mode,showing the result we expect
>> We have a lot of those reports built actually under Excel , that we want
>> to
>> move under RS
>> Thanks for your help
>> Philippe
>> Espace-NTIC
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
>> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
>> You can nest a table in a matrix (and vice versa). But I don't quite
>> understand your question. Maybe if you provided some instance data
>> (examples) it would be easier. Also, if your newsreader allows you to
>> attach
>> an example instead of posting inline, it might be easier to read.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
>> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
>> > Hi Gurus
>> >
>> > Sorry to insist but the need of an answer begin more urgent
>> >
>> > we have a dataset like this
>> > Key1 Primary Key of record
>> > Key2 1st Foreign Key
>> > Key3 2nd Foreign Key
>> > Duration in seconds
>> > Product Pn (field for pivot)
>> > Qty Qn (value link to Product)
>> >
>> > what we need
>> >
>> > P1--Pn
>> > Key3 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Key1 Duration
>> > Q1--Qn
>> > Gran Total Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> >
>> >
>> > I seach among the messages of this group and i did not found an answer
>> to
>> > our problem.
>> >
>> > It seems for us, that matrix answer to the need of building multiple
>> > columns depending on the different content of one field ie product
>> > ,
>> > and
>> > give the possibility to associate a value link to this field ie Qty
>> > in
>> > our
>> > example, the informations on rows seems to be only grouping fields,
>> but
>> > not value of field linked to this grouping field ie duration and
>> > count
>> of
>> > the primary key in our example
>> >
>> > With table i can associate fields to grouping fields but we need to
>> > know
>> > all
>> > the fields we want to display , if we have more then 20 products we
>> > have
>> > to previous one field for each value possible of product : 20 and try
>> to
>> > hide the bank fields
>> > We thought about to to reverse the table by an Stored procedure , but
>> > we
>> > did
>> > not find a way to build or add dynamic columns to the table
>> >
>> > What we need is a mix of table and matrix depending togather on the
>> same
>> > grouping fields .
>> >
>> > Hope this is more clear, and thanks to inform us if exist a solution
>> > or
>> > not
>> > with RS to our problem
>> >
>> > Philippe
>> > Espace-NTIC
>> >
>> >
>> >
>> >
>> >
>>
>>
>>
>|||Hi Brian,
in fact we need to drill dow on rows.
I have some ideas in mind to turn around the problem like
linking one report based on first level grouping to another one based on
second level grouping and so on
Other way' with a table object is it possible to add dynamicaly new columns
coming from a dataset calilng a crosstab PS building the columns'
Anyway thanks a lot for your help and congratluation to RS Team for this
product giving us the ability to add new fonctionalities of reporting to our
web application
Philippe
Espace-NTIC
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
message de news: uHTPwtsXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Sleazy Hack # 4123:
> 1. Put two matrixes side-by-side with the same row groupings.
> 2. The left matrix should have the static columns. The right matrix
should
> have the dynamic columns.
> 3. In the right matrix, remove the text from the row headers and shrink
the
> row headers to be as narrow as possible.
> 4. As a final optimization, hand-edit the RDL to set the width of the row
> headers to 0 (this can't be done in the design tool). After doing this,
it
> will be hard to edit the matrix in the design tool, so make sure you do
this
> after you're 100% done with all of your other edits.
> Note: This won't work if you have drilldown enabled on row groupings,
since
> there's no way to synch up the drilldowns between the two matrixes. If
you
> need drilldown on rows, you'll need to wait for a future version where we
> will support this functionality natively.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ugkhjUmXEHA.3668@.TK2MSFTNGP09.phx.gbl...
> > OK, I understand now. We are looking at supporting this natively in a
> > future release but I don't think you can do this in V1 without some
> > tricks. I'll forward it on to our trick specialist and get him to post a
> > suggestion...
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> > news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
> >> Hi Brian
> >>
> >> for an example assume we start from the "Company Sales" report from RS
> >> samples
> >>
> >> This report has two grouping lines based on ProductCategory field and
> >> ProductSubcategory field
> >>
> >> This report has dynamic columns based on the different values of two
> >> nested
> >> fields OrderYear OrderQuarter
> >> At those dynamic column is associated a field : sum(SalesValue)
> >>
> >> what we need is ,:
> >> ADD at the left side of those dynamic columns , TWO STATIC columns one
> >> giving for example
> >> the content of SUM(SalesOrderDetail.UnitPrice *
> >> SalesOrderDetail.OrderQty
> >> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one
the
> >> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
> >> The content will follow the same grouping as for dynamic columns
> >>
> >> we join an XLS file in zip mode,showing the result we expect
> >>
> >> We have a lot of those reports built actually under Excel , that we
want
> >> to
> >> move under RS
> >>
> >> Thanks for your help
> >>
> >> Philippe
> >> Espace-NTIC
> >>
> >>
> >> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
> >> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
> >> You can nest a table in a matrix (and vice versa). But I don't quite
> >> understand your question. Maybe if you provided some instance data
> >> (examples) it would be easier. Also, if your newsreader allows you to
> >> attach
> >> an example instead of posting inline, it might be easier to read.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> >> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> >> > Hi Gurus
> >> >
> >> > Sorry to insist but the need of an answer begin more urgent
> >> >
> >> > we have a dataset like this
> >> > Key1 Primary Key of record
> >> > Key2 1st Foreign Key
> >> > Key3 2nd Foreign Key
> >> > Duration in seconds
> >> > Product Pn (field for pivot)
> >> > Qty Qn (value link to Product)
> >> >
> >> > what we need
> >> >
> >> > P1--Pn
> >> > Key3 Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> > Key3 Key2 Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> > Key3 Key2 Key1 Duration
> >> > Q1--Qn
> >> > Gran Total Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> >
> >> >
> >> > I seach among the messages of this group and i did not found an
answer
> >> to
> >> > our problem.
> >> >
> >> > It seems for us, that matrix answer to the need of building
multiple
> >> > columns depending on the different content of one field ie
product
> >> > ,
> >> > and
> >> > give the possibility to associate a value link to this field ie Qty
> >> > in
> >> > our
> >> > example, the informations on rows seems to be only grouping fields,
> >> but
> >> > not value of field linked to this grouping field ie duration and
> >> > count
> >> of
> >> > the primary key in our example
> >> >
> >> > With table i can associate fields to grouping fields but we need to
> >> > know
> >> > all
> >> > the fields we want to display , if we have more then 20 products
we
> >> > have
> >> > to previous one field for each value possible of product : 20 and
try
> >> to
> >> > hide the bank fields
> >> > We thought about to to reverse the table by an Stored procedure ,
but
> >> > we
> >> > did
> >> > not find a way to build or add dynamic columns to the table
> >> >
> >> > What we need is a mix of table and matrix depending togather on the
> >> same
> >> > grouping fields .
> >> >
> >> > Hope this is more clear, and thanks to inform us if exist a solution
> >> > or
> >> > not
> >> > with RS to our problem
> >> >
> >> > Philippe
> >> > Espace-NTIC
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>