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 - how to let column group stay in separate lines?

I would like to have this matrix format:
JAN-06 FEB-06 ... Total
Sales Dept 100 120 220
Team One 60 70 130
Emp A 30 35 65
Emp B 30 35 65
Team Two 40 50 90
Emp C 20 25 45
Emp D 20 25 45
The default layout is sales dept, team one, and employee are on the same
line, I would like them in separate line to save space.
Thanks in advance.Sorry, I mean row groups in separate lines
"qye2020" <qye2020@.yahoo.com> wrote in message
news:uUKfBjcTGHA.5468@.TK2MSFTNGP14.phx.gbl...
>I would like to have this matrix format:
> JAN-06 FEB-06 ... Total
> Sales Dept 100 120 220
> Team One 60 70 130
> Emp A 30 35 65
> Emp B 30 35 65
> Team Two 40 50 90
> Emp C 20 25 45
> Emp D 20 25 45
> The default layout is sales dept, team one, and employee are on the same
> line, I would like them in separate line to save space.
> Thanks in advance.
>|||I hope someone answers your question...I am trying to do something similar
also.
"qye2020" wrote:
> Sorry, I mean row groups in separate lines
> "qye2020" <qye2020@.yahoo.com> wrote in message
> news:uUKfBjcTGHA.5468@.TK2MSFTNGP14.phx.gbl...
> >I would like to have this matrix format:
> > JAN-06 FEB-06 ... Total
> > Sales Dept 100 120 220
> > Team One 60 70 130
> > Emp A 30 35 65
> > Emp B 30 35 65
> > Team Two 40 50 90
> > Emp C 20 25 45
> > Emp D 20 25 45
> >
> > The default layout is sales dept, team one, and employee are on the same
> > line, I would like them in separate line to save space.
> >
> > Thanks in advance.
> >
>
>

Master-detail subreports and performance.

If I create a master detail-report using subreports, will a separate query
run for each subreport at the time it loads? Lets say I have a report with
500 master rows, will it have to run 500 queries for the subreports before
the first page loads?
I know there is no on-demand subreport load, and from what I read you can't
do a heterogeneos joins between 2 record sets in the same report - so that
the detail recordset would filter under each master row based on an id.
So what is the best way to produce a large master-detail report with
reasonable performance?
Thanks.You are correct, the subreport is executed for each master record. Other
than making sure your database is optimized (correct indexes for example)
there isn't much you can do to speed it up the report running. However, if
the report is only needed for a particular time (for instance, this report
is run daily) you could use snapshots. Read up on that in books online.
The other possibility is to consider drill through. In that case you show
the master tables with a link that when the user wants additional data they
click on the link and use jump to report to pull up the additional data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
> If I create a master detail-report using subreports, will a separate query
> run for each subreport at the time it loads? Lets say I have a report
> with 500 master rows, will it have to run 500 queries for the subreports
> before the first page loads?
> I know there is no on-demand subreport load, and from what I read you
> can't do a heterogeneos joins between 2 record sets in the same report -
> so that the detail recordset would filter under each master row based on
> an id.
> So what is the best way to produce a large master-detail report with
> reasonable performance?
> Thanks.
>|||Thanks Bruce for confirming my suspicions. I'm definitely not doing a
report that generates over 500 queries to load a page regardless of database
optimizations or snapshot usage.
Well, I ended up doing a SQL join between my master recordset query and my
detail recordset query to produce one mammoth recordset containing all
fields. Then I grouped on the master columns, and displayed the detail
records as expandable sections. Its not efficient by any means but it works
reasonably well, and its only a single query, and it gives me the
master-detail effect.
Any idea when we are going to see the next version of Reporting Services.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
> You are correct, the subreport is executed for each master record. Other
> than making sure your database is optimized (correct indexes for example)
> there isn't much you can do to speed it up the report running. However, if
> the report is only needed for a particular time (for instance, this report
> is run daily) you could use snapshots. Read up on that in books online.
> The other possibility is to consider drill through. In that case you show
> the master tables with a link that when the user wants additional data
> they click on the link and use jump to report to pull up the additional
> data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "David W" <spivey@.nospam.post.com> wrote in message
> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>|||What you are explaining here is drill down. It is a nice user interface but
not one I do too often because of the amount of data returned.
I would consider looking into using drill through in the future. Users are
very comfortable with it and you get really good performance.
The next release of RS (non-service pack) is with Katmai (next version of
SQL Server). RS is part of SQL Server and releases with both service packs
and major releases for SQL Server. Early on this was not true but now it is
totally in sync with the rest of SQL Server.
http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx
Right now they are just saying 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:u4QXU2vlHHA.3872@.TK2MSFTNGP04.phx.gbl...
> Thanks Bruce for confirming my suspicions. I'm definitely not doing a
> report that generates over 500 queries to load a page regardless of
> database optimizations or snapshot usage.
> Well, I ended up doing a SQL join between my master recordset query and my
> detail recordset query to produce one mammoth recordset containing all
> fields. Then I grouped on the master columns, and displayed the detail
> records as expandable sections. Its not efficient by any means but it
> works reasonably well, and its only a single query, and it gives me the
> master-detail effect.
> Any idea when we are going to see the next version of Reporting Services.
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> You are correct, the subreport is executed for each master record. Other
>> than making sure your database is optimized (correct indexes for example)
>> there isn't much you can do to speed it up the report running. However,
>> if the report is only needed for a particular time (for instance, this
>> report is run daily) you could use snapshots. Read up on that in books
>> online.
>> The other possibility is to consider drill through. In that case you show
>> the master tables with a link that when the user wants additional data
>> they click on the link and use jump to report to pull up the additional
>> data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "David W" <spivey@.nospam.post.com> wrote in message
>> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>>
>