InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:42:40 GMT20http://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxI think that you've done it the best way possible with 2005.Tue, 08 Jan 2013 20:06:37 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxThanks for checking. I ended up creating a summary table in sql and will populate summary data there for calculations and then report.I appreciate the time and effort.Tue, 08 Jan 2013 18:54:32 GMTdpelanhttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxMy apologies I didn't see that it was for BIDS 2005. I've logged on to one of my old SQL Server 2005 machines and luckily it had BIDS 2005 installed on it so I could try.Wow I forget how painfully bad that version was. The jump to 2008 is light years ahead. I can't work out how to do what I was suggesting in 2005, I'm sorry.You may have to do something in the SQL to make it work, like make use of pivot/unpivot and cube/rollup.Tue, 08 Jan 2013 16:53:31 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxExcuse my ignorance, but does this work in SQL 2005? If so how do i add another line in my matrix below the total line to to the calcualtions.Thanks in advance.Tue, 08 Jan 2013 09:28:06 GMTdpelanhttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxYou can use multiple groups in the same tablix that aren't nested within each other. That way your 3 month average doesn't need to be in a second tablix. You can also add totals or variances in cells that are outside any grouping at all yet still within the same tablix.Mon, 07 Jan 2013 15:18:13 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxI have tried that but a matrix doesn't allow it. I get the following error message - "Report item expressions can only refer to to other report items within the same grouping scope or a containing grouping scope."Mon, 07 Jan 2013 12:49:25 GMTdpelanhttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxName the cells so they are easy to remember e.g. MonthTotal and then refer to those cells directly in an expression:[code="other"]=ReportItems!MonthTotal.value[/code]Sun, 06 Jan 2013 23:12:39 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1403386-150-1.aspxI have a matrix reporting data by week by location. The matrix works great for this. The challenge, however, is I want to take my total from the matrix and provide a 3 month average - easy enough to do with another matrix. Then i need to take the last weeks data (matrix 1) compare it to the 3 month average (matrix 2) and calculate a variance. Then show the max value from the 3 months and calculate a variance against the last weeks data. for example location A location B location CWeek 1 100 300 350Week 2 550 350 700Week 3 250 550 450Total 900 1200 1500Weekly Avg 300 400 500Last Week Var -50 150 200Max 550 550 700Variance -54% 0% -35%Any suggestions on how to accomplish the bottom calculations when you can't use fields from various matrices in a report?Thanks in advance.Sun, 06 Jan 2013 18:27:22 GMTdpelan