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.