SSRS 2005 Matrix Subtotal Issue

This seems like it should be easy.  I have a matrix control that I am trying to subtotal a column in a rowgroup.  I've clicked on the leftmost entry of the rowgroup and selected 'subtotal' which adds a 'Total' row and tried the same for the column header which adds a Total for the column.  But when I run the report the subtotals only include the first entry or sometimes I get a random entry in each row (row subtotal) or column (column subtotal), but never a true subtotal for the column or row.  Why won't it add all of the cells together?
I found one posting that suggested I needed to wrap the detail in the cell with SUM(detail.value).  Unfortunately, this just puts the Grand Total for that value in every cell of the matrix.

I see a number of postings of people struggling with this.  How do I get each column to subtotal properly?  Is it a known bug and I need to apply a patch or something, because it seems like a bug?  

Any workable solution that prevents me from having to rewrite my stored proc or redo the whole report using a table control instead of a matrix would be most appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ARWAuthor Commented:

By dragging a field from the data set into the rowgroup, I was sort of able to achieve what I wanted.  However, it created extraneous columns and rows which I then had to hide and it prevented me from using lines in the report to show table grid.  Nonetheless, this issue seems to be put to bed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Megan BrooksSQL Server ConsultantCommented:
Yes, it should be simple. The Matrix data region takes some getting used to, though. The basic steps are:
  1. Create a dataset that returns the row, column, and cell data. The row and column data may be hierarchical.
  2. Add a matrix data region to the report
  3. Drag each row item to the row group area (on the left)
  4. Drag each column item to the column group area (on top)
  5. Drag the cell item to the cell area
This creates the nested row and column groups and wraps the cell value with Sum(). You can then right-click a row or column group item and add a subtotal for that group level. For overall totals, use the leftmost or topmost group.

This seems to be similar to what you have done. I don't know yet what may have gone wrong. I remember seeing extra rows and columns in one matrix report I worked on, but it was years ago and I don't remember the details. I eventually found whatever it was.
ARWAuthor Commented:
I would hazard a guess that there is a possible bug in the IDE related to manually typing things into the Matrix control versus dragging fields from the Dataset pane.  IMO I should be able to manually accomplish whatever functionality is created by dragging, but that did not seem to be the case.

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Megan BrooksSQL Server ConsultantCommented:
If you display the matrix data region properties dialog and go to the Groups tab, do you see just the expected column and row groups, or are there extra ones corresponding to the extra rows/columns you had to hide?
Megan BrooksSQL Server ConsultantCommented:
I migrated to SQL Server 2008 a year ago and that resolved many problems. The SSRS 2005 report designer had quite a few issues as I recall. I only keep it around for answering EE questions.
ARWAuthor Commented:
Indeed that is where i would surely like to go, but I am not the decision maker on that...unfortunately.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.