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.

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