March 27, 2008 at 10:53 am
I have worked repeatedly, trying to use the matrix and ADD a column that will be a YTD total. Had found an article concerning "getting the matrix to display two subtotals for the same group", but have been unable to get it to work.
Current matrix sample is attached.
The SQL obtains values for each 'row' type, by month. Using the matrix, the subtotal feature is added to total all months for each row type (adjusted plan and actual/forecast). The row type is taken from the actual column name. I used the static group feature to combine the rows. The Over/Under Hire field is calculated in the matrix (hired - adjusted). I had also added code to the SQL to obtain a YTD_Adj and YTD_Hired Total, but do not want to use them. Want to be able to have a column that will total only those months where the "sortdate" value is <= month(now).
Current data sample is attached.
The SQL data results are used in 4 different reports, for different levels of reporting.
Would REALLY appreciate any ideas someone might have. Thanks!
March 28, 2008 at 3:09 am
Is this report ALWAYS going to print only a 12 month period (i.e. one year of data from Jan to Dec).
If the answer to this is Yes then I'd probably decide not to use a matrix to achieve it, the reason being that the sub-totals and grand totals on a matrix are based on content, so you would have to mess around with the query a little to achieve what you want. I might be missing something but I have never found the ability to add a filter only to a sub-total, or even to add an expression only to the sub-total.
The way to do it in a matrix would be to split the values you want totalled from the values you don't want, so you would have two value columns from your query, the first is the months up to this month and the second is the remainder of the months. You place both value columns into your matrix and ensure that zero's are suppressed at the column level, then place a sub-total against only the first column (I think you can do this anyway).
As you can see, if the answer to the first question is yes (even if it's for a rolling 12 month) then doing this in a table would be much easier to control.
Good luck.
Nigel West
UK
March 28, 2008 at 7:47 am
Thanks for your info! For this report, I would rather not have to switch to a table. Four levels of reports are all accessed through this main report. It is important that the look and feel of all the reports are matching.
Yes, this will always be a Jan through Dec report. There are initial parameters that choose the year to view.
I used the matrix feature because it seemed the most like our current Excel reports, and wanted the drill downs, columns expand as months are added, etc.
The article I had read, showed using the matrix, and using InScope in a second subtotal column. I have not been able to get the InScope feature to work.
It seems like such a simple column to add - one column for all year total, and another separate column that would total only month 1 through current month number.
March 28, 2008 at 7:50 am
OK, I understand a bit more about what you need, let me do a little work on it and I'll come back to you.
Nigel West
UK
March 28, 2008 at 8:07 am
Take a look through this article, I haven't read all of it yet but it would appear that this will cover your needs and more. I'll probably try working through it over the weekend.
http://www.simple-talk.com/sql/sql-server-2005/advanced-matrix-reporting-techniques/
Good luck.
Nigel West
UK
March 28, 2008 at 8:30 am
I had found this article previously and read through it. I have tried using the InScope as described in this article, but was unable to get the values in the YEAR subtotal column to accurately reflect the YTD. I will reread the article though and make sure I didn't miss something.
I did not attach a copy of the 'layout view' of the report before. I am attaching it now, in case you can see what I might be missing.
Thanks for all your help! I really appreciate your time. The only other alternative I have is to start over with the SQL and create multiple rows for each month, each having a name like the adjusted total, actual hired, etc. Then use this in the same manner as the months field - that is WAY too much work for all the different layout versions/levels I need.
Note - I have widened the columns on the layout view so that the field names show.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply