Subtotals in a Matrix 2

[ookete]

I LOVE the matrix for pivoting out data. However, I am having a hard time with subtotals. Actually, I would like to put more than just subtotals at the bottom of a matrix (such as an average), but I am unable to. Example of what I want:

       A   B   C
A      1   2   3
B      2   3   4
C      3   4   5
Total  6   9  12
Avg    2   3   4  <-- can't do this one

Does anyone know how to add more "subtotal" regions than one, and how to make them something other than Total?

Thanks!

 

[Catadmin]

When you say "Can't do this one", what do you mean? You can't do the whole row of averaging or can't do the last column?

I would think that all you need to do is add the appropriate groups or rows/columns cells and then right click, go to Expression, then try a Avg() function of some sort. Expressions seem to require Visual Basic language. One of my co-workers says it's VB Script, but I'm not so sure it isn't just plain VB or VB .Net.

In a Matrix, you can right click the row group and enter "Add Row Group" to get another row grouping. Or you can click in the bottom right box, and add individual columns and rows that are within the groupings you've already set up.

---------------------------------------------------
| Empty Box | COlumn Group |
---------------------------------------------------
| Row Group | Results |
---------------------------------------------------

This is your typical Matrix set up before adding Row Groups or Column Groups. Right click the Column Group or Row Group headers and you can add more groupings in either direction. Row groups add horizontally, Column Groups add Vertically. If you right click in the Results labeled section, that is were you can get additional rows and columns that are in the first grouping (or whichever grouping you click on).

I found out by experimentation that all you really need in a Matrix is a Row group. You can leave Column Group as Blank and just enter multiple columns of text as needed.

Hope that helps some.

Catadmin

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Thanks for your reply.

In my example I was approaching Subtotals only. So when you check "Subtotal" for the existing Row Group, it is a single line that shows a Summation of its rows. It seems you can't have multiple "Subtotals" on the same Row Group that have different aggregates (Avg, etc).

I did play around with the "Add Row" function of the lower right box (the Results box in your grid). But this adds one extra row per record inside of the Row Group. What I need is an extra Aggregate row appended to the bottom of the Row Group that allows me to do more than just Sum.

I don't want to add any Row Groups because that adds nested grouping that I do not need. I only need extra "Subtotal" functionality on my existing Row Group.

Again, thanks for your response. I will keep playing with it, and feel free to post any other thoughts or discoveries.

 

[Catadmin]

Well, I'm still learning RS myself. If I find anything that'll help, I'll post. Right now, I'm playing with cascading parameters, drop down lists that expose or conceal drilldown result sets and subreports nested in tables.

I think my boss found the most difficult report that a body could possibly as for and then assigned it to me for development. @=)

Catadmin


Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

To confirm. Expressions and custom functions are in .NET

ookete - could you not add another group but use the same grouping item ??

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[ookete]

xlbo,

I can add another group, but that just nests a level inside (or outside) of my existing group. Let me expand my example a bit.. What I have right now:

       A   B   C
A      1   2   3
B      2   3   4
C      3   4   5
Total  6   9   12  <-- the normal "subtotal" row that exists now

If I add another group, I would get something like (with drill-down buttons):

                   A   B   C
+Product1
-Product2   A      1   2   3
            B      2   3   4
            C      3   4   5
            Total  6   9   12
+Product3
+Product4

I don't want or need this grouping layer. What I really want is just another Aggregation line under the subtotal

       A   B   C
A      1   2   3
B      2   3   4
C      3   4   5
Total  6   9   12
Avg    2   3   4   <--- I want this extra Aggregate row

I am not sure if this is possible. I hope this example makes my need a little more clear. Thank you.

 

[Catadmin]

I think what he's saying, Ookete, is there may be no other way than "faking out" RS by adding another grouping to put your averaging on and hiding it's GroupTitlebox.

I certainly haven't been able to find another way so far.

Remember, too, that technically speaking, the current version of RS is a "beta" that was only meant to released by MS with SQL 2005. The reason, I heard, that they released it even though they weren't done with it, was that they were supposedly begged by many DBAs for an MS reporting tool that could handle SQL. All of this essentially means that RS could very well be "broke" or the functionality to do what you want doesn't exist yet.

Now that I think about it, though, the only way that Crystal is able to get away with what you want to do is that Crystal starts off with a free form. Maybe what you need to do is increase the height of the Row Group where the subtotals are, then embed a List control in each field so you can do a subtotal field and an Average field one right above the other ....

I'll warn you that I haven't tested this yet...



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Thanks for the ideas.

What I have done in the past (using Access reports) is have a stored procedure that manually pivots each column out and sends a recordset that "looks" like an already-crosstabbed table. This is a pain and inflexible, but it allows me to add any rows in the details footer for Total, Average, StDev, etc.

I was hoping that the Matrix grid was a dream-come-true, but it now seems only good if you want basic subtotal functionality.

I think you are right Catadmin. RS is only partially completed at the moment. Let's hope for more things to come.

 

[xlbo]

Catadmin - exactly - working on pretty much that at the mo. Have worked with Busines Objects for 5 years now so very used to faking it !!

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

Hey,

Just came across an interesting reference in SQL Server 2000 Reporting Services, Step by Step, published by Microsoft Press, page 131.

When working with no-additive values like ratios or percentages, you must perform the division on aggregated values. TO accomlish this, you can use a report item expression to summarize percentage values in rows in which you are using subtotals...

In the example they use, they add a column. YOu might be able to add those rows in the Matrix as I first suggested. Then you'll need to use some sort of row counter to make sure your average comes out. Here's their expression:

= ReportItems!<objectname>.Value / ReportItems!<objectname>.Value

In your case, the second value would be your row count.

See if that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???