SQL Server Forums Profile | Register | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2008 Forums  Analysis Server and Reporting Services (2008)  Divide Two Cells in a Matrix New Topic  Reply to Topic  Printer Friendly
Author  Topic

blacky35
Starting Member

1 Posts

 Posted - 05/18/2012 :  17:14:28 Hello -For the last two days I have been struggling to figure out how to divide two cells in a Matrix. Maybe this is the wrong way to think about it....another way of saying it...dividing a conditional total sum by a total row sum from the data set. Here is where I am at:I have the following matrix built:[URL=http://imageshack.us/photo/my-images/707/testmatrixdesignview.png/][/URL]And here are the results:[URL=http://imageshack.us/photo/my-images/716/testmatrixpreview.png/][/URL]I need to build an expression to populate the G% column correctly, which is simply (SUM(Sales) WHERE Indicator = "G")/SUM(Sales). So in this example it would be the following calculations:23,386 / 28,574 = 81.84%23,602 / 28,810 = 81.92%11,839 / 16,025 = 73.88%12,476 / 16,837 = 74.10%12,023 / 15,974 = 74.27%So far my expression looks like this: =(IIF(Trim(Fields!Indicator.Value) = "G",Fields!Sales.Value, 0)/SUM(Fields!Sales.Value))But it is obviously returning 0.00% for all fields. Another funky thing is if I change the expression to look for Indicator.Value = "B" it seems to work just fine:[URL=http://imageshack.us/photo/my-images/521/testmatrixpreviewb.png/][/URL]Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts

 Posted - 05/19/2012 :  14:57:35 you need to apply two row groups here. outer group on Client and next group on yearmonth field------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Topic
 New Topic  Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC