Tuesday, March 20, 2012

RS2000 Matrix, Percent of subtotal

Problem: Subtotal is not calculated correct in a Matrix expression.
Need some help with an expression in a matrix that should calculate percent
of subtotal for a measure.
The matrix look like this
Column_OrderYear
RowGroup1 | RowGroup2 | Sales
% Share
I want % Share to calculate as sales / subtotal on RowGroup1(per
Column_OrderYear). If I use RowGroup1 in the scope it summerize the subtotal
for all Column_Year (all years) on that row.
I have included a modified version of Company Sales.rdl that is included in
samples for RS2000. In this example, for the first group Component and Bottom
Bracket, % Share should be calculated as 37977.81 / 6778096.94. Instead its
calculated as 37977.81/11233756.04.
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.0625in</rd:GridSpacing>
<RightMargin>0.5in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>5.73232cm</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Share">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>White</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.68068cm</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>P</Format>
<BackgroundColor>White</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Sales.Value) /
sum(Fields!Sales.Value, "matrix1_ProdCat")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.54454cm</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Sales.Value, "matrix1_ProdCat")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.67072cm</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>White</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Sales.Value, "matrix1_SubCat")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.67072cm</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>5.09452cm</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>Sales</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_OrderYear">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderYear_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<TextAlign>Right</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>OrderYear_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderYear.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.599cm</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>12.90389cm</Width>
<Top>2.85714cm</Top>
<Left>0.15873cm</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_ProdCat">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="ProdCat">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>ProdCat</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProdCat.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Crimson</BackgroundColor>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicRows>
<Width>2.69841cm</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_SubCat">
<GroupExpressions>
<GroupExpression>=Fields!SubCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!SubCat.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!SubCat.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Crimson</BackgroundColor>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicRows>
<Width>2.80455cm</Width>
</RowGrouping>
<RowGrouping>
<Width>2.30641cm</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<TextAlign>Right</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Sales</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<TextAlign>Right</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value> % Share </Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<TextAlign>Right</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>subtotal 1 </Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Green</BackgroundColor>
<TextAlign>Right</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>subtotal 2</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
<Textbox Name="Title">
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>18pt</FontSize>
<Color>DarkSlateBlue</Color>
<FontWeight>800</FontWeight>
</Style>
<Height>0.9375in</Height>
<Width>3.125in</Width>
<Top>0.0625in</Top>
<Value>Adventure Works
2002 - 2003
Sales - Modified</Value>
<Left>0.0625in</Left>
</Textbox>
</ReportItems>
<Style>
<BackgroundImage>
<Source>Embedded</Source>
<Value>logoback</Value>
</BackgroundImage>
</Style>
<Height>6.80507in</Height>
</Body>
<TopMargin>0.5in</TopMargin>
<DataSources>
<DataSource Name="AdventureWorks">
<rd:DataSourceID>25d3314c-0d4f-49cc-9c22-10194e825490</rd:DataSourceID>
<DataSourceReference>AdventureWorks</DataSourceReference>
</DataSource>
</DataSources>
<Width>15.87302cm</Width>
<DataSets>
<DataSet Name="Sales">
<Fields>
<Field Name="ProdCat">
<DataField>ProdCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SubCat">
<DataField>SubCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderYear">
<DataField>OrderYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderQtr">
<DataField>OrderQtr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Sales">
<DataField>Sales</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AdventureWorks</DataSourceName>
<CommandText>SET DATEFORMAT mdy
SELECT ProductCategory.Name AS ProdCat,
ProductSubCategory.Name AS SubCat, DATEPART(yy, SalesOrderHeader.OrderDate)
AS OrderYear,
'Q' + DATENAME(qq,
SalesOrderHeader.OrderDate) AS OrderQtr, SUM(SalesOrderDetail.UnitPrice *
SalesOrderDetail.OrderQty)
AS Sales
FROM ProductSubCategory INNER JOIN
SalesOrderHeader INNER JOIN
SalesOrderDetail ON
SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID INNER JOIN
Product ON
SalesOrderDetail.ProductID = Product .ProductID ON
ProductSubCategory.ProductSubCategoryID = Product .ProductSubCategoryID INNER
JOIN
ProductCategory ON
ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE (SalesOrderHeader.OrderDate BETWEEN
'1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy,
SalesOrderHeader.OrderDate), ProductCategory.Name, ProductSubCategory.Name,
'Q' + DATENAME(qq,
SalesOrderHeader.OrderDate),
ProductSubCategory.ProductSubCategoryID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<EmbeddedImages>
<EmbeddedImage Name="logoback">
<MIMEType>image/jpeg</MIMEType>
<ImageData>/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCADCAbYDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD3+iiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAoo
ooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAEJxWHdazJ5pWAKFHGSMk1tsNykeoxXIzwvBM0bggg/nQBtadqjTyiGYAMfukd61s1y1hbvPPkZCqCWI+lRC5nQ/LM4x/tGgDrqK5Qahdr0nf8TUo1a8H/LTP1FAHTUVzq61cggsEYfTFb8MnmwpJjG4ZxQA+iiigAoqGW5jicKx5PtUqkMoIOQaLALRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAVDNFC6lpUVgBnLDpU1ZOs3eyMW6Hl+Wx6UAQ2+qM12sSRIIWbaFUYP1qw2hwMxO9xk+1V9FtMk3LDpwv9TW5QBz95YWlmvzTSFyOFGKzURpHCopLHoBW5q1i8xE8fJAwV9azbG9Nm5PlhgepPUUAX7PSUiAluiCeoXsPrWl9qgXjeOPSoYYRcKJZJPMB5AB4qwIIh0jX8qegCxzJLnYwOKfTVREJ2qBn0pJJBEhdug6UgM28x9pb8Kv2oItkz6VQjRrmck9Cck1pM6RJliABVydlYErklJVCXUgOI1z7mq5nupz8u78BWXMi1TfU18gdxSb1/vCsn7LdN1B/FqabO4X+D8qXMyvZx7myCKXNYJ82I87l/SpY76ZOp3D0NHP3B0n0NjilqpBeRykA/Kx7GrY6VSdzNprcKKKKYgooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiijNABRRRQAUUUUAFFGaM0AFFFFABRR
RQAUUUZoAKKM0ZoAKgmuFiO0Dc56KKfPJ5UTOBkjpUVvDtHmPy7cnPahARSed5TSSyeWijJC9awFWS8ugucs54J9K6p0WRCjDKkYIqvbafBayM8anJ4ye1FwGRxz2kaqmHjXtjmrMMyzLleo6j0qTtVW4jMTCePgj7w9RT3AsOQqlj0AzWLcac1yj3MYAck/KO4rUncNaMy9GFSQDECY9KOgHO2V9JZSbWBMefmU9q6KGZJoxJGwKmqWo6aLgGSIYlH/j1V9It7mKZ/MVljxyD3NIDTluo4uCcn0FU2WW5O+Q7Ix60skgSZ1ihLPnljR9lmm+a4k2j0o5uxSj1YNdpEvlwLuPqe9MW1nuG3SEge9Tq9vbnES739uaUi6m6YjWlyt6sfOl8I5LW3g5bGfUmpBcQDgOoqEafnlpCTTX0/j5HP0NUoxIbb3LqurDKsCPY0tY7LJA/dWHcVo2kzTREt1HBPrTcbATMisMEZFUp7BWy0R2n0q/SVDSY1JrYxPs0wcAIc5raQEIAeuKXFLSUbFSm5bhRRRVEBRRRQAUUUUAFFFFABRRRQAUGig0AQ3Evkx5AyxOAPU1FDNK05jkVRhd2RTLxz50SqpbB3ECltn3maZhjtj6VDepdtBHuZQ0hRUKJwSTzUrzlbeOQLy2OPrVXB+xAdGmfn8TVi6A2wr28wflRcHYlMpM4jAzgZY+npUPnzvJIIlQqpxknvToP9W87fxnP4dqitFnADfJscljnOadwsSzzvDbh2A38cDpSmY7oVUDMnJ+lRXY8yaOPtgsabaHzJlPaOMD8TSvqFtLkt1PLCAVVSvv604SyLbu8qgMATgHrTLn57iCPtu3H8Kden9wIx1dgtMQee2IPlG6Q8j0FEs8gn8qJVJ27jmmgbr4DtGn6mq7ySh55oyoXO059vSlcaWpaS4LWrTMAMA/jSRSXL7WKIEPXnnFRzr5dkkScFiFFWLcTAHzNmO22ncTSsTdqhmlKSRqoG
WP6VNVG8fbPGR94K2B7ngU27CirsebvidgOEOB9afNM0USfKDIxxj3qnAmZRFnOH3E59B/jVmb576JOygsam5TSTHQTSvK8cgUFQOVpj3Mu6QoqFEPUmltmGJpj3Y4+gqqvmeUobAjlcZx96i7Gki7JOVtkcD5mxgH3pUmLzMigFF4J9/SoLsM8sUceNwy3Pan2RPkurAZViMjvTuKytcSS5k/eFEUpGcEsetWYn3xK5GCRkiqOHhwjojJI/r6mtADgYpoUkOormp9Su1uJFWYhQ5AGB61e0i6nuZZRLIWAAIpkmvTWAZSD3FOHSkJwCaAKcI32skf90kVNavut19RwaZZjKO395jTWzazFwCYn647GqYFykpFcOoKkEU6pAilSQj90wU9yRUItCxzLKze1W6KLsCNIkjHyqBUgoooAKKKKAGSRpIMOoIoRFjGFXAp9FABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAVRG/2qSQrxtAX3pghkWwZAuZGByM46/wD1quUUrDuUoopXki3psSPpznNSXcTyqgQfxcn0FWqSiwcxBMjfZmSNcnGAKZbGdQqNEFQDGcirfakosFyssTG6eRl+XaFWksoGhRtwwSf0q1iiiwX6Ffy3N6ZGX5AuAfem3SSl4njTeEycZx9KtUUWC5WhjkXzJHUB35wD6VH9nk+yrHtyWfL89s1doosHMVLlZfMiMabgmScnH0qaFpWUmRNp9M5ptxdQ2y7pXxnoO5rOfXVBIjgJHqzYoSC5sdqqSwNJeq5HyKvB96pJrqE4kgIHqrZ/Tirn9o25tzMhLqv3go5H4UNXBOw61gaOSV2GCzcfSo5EuFuXdEDBhgHPSov7ctf+ec35D/GrVreRXis0eRtPIbrRyhzaiGF0sTEg3NjHX1o8lvMgAHyRg5Oe/ap5HWNGdyAqjJqlDq0E8qxIkpZjjoP8aLBzE4jf7XJIV4CgLz1piwutgyY/eEHP1
NSz3UNsm6VgvoB1NZ0muqD+7gLD1Zsf0osFy1FFM0kW+MIsee+c1erHj11C2JICB6q2f8K0be6huV3ROGx1HQihKwN3OYuf+Pqb/fb+daWg/wCum/3RWbc/8fUv++f51paD/r5v90UxG7SEBgQehpskiRIXkYKo6k1mS65ChxFG0nuTtFAGmiLGoVRgClIDDBGQaxl145+a349n/wDrVettSt7k7VYq/wDdbjNADjbNG26CTb/snpVpc4Geveis1tWiW9MTD92ON/v/AIUAadFIrAgEEEGloAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACmSOERnPRRk0+muodWU9CMGgDk7id7idpHPJP5Vf03TUuY/OmJ25wFBxms+5t3tpmjcdOh9RV7TdTW2j8mUHZ1BHOKANCXR7V0IRWQ9iGJ/Q0yx0v7O8jSkOT8q46Yp0ms2qj5Czn0Ax/Om6fqRu52jdAD1XHTHpQBkX1qbS4KfwHlD7UWdybS5Vx93ow9RW/f2n2q2IH+sXlD71zBBBIIII4waANbV74SBYImyuAzEHr6CptKtRb27XMg+Yrkf7tZ2n2n2u5AI/dry3+H410rIGjKEfKRjA9KAOTuJ3uJmlc8k8D0rQ0zTUuIzNMTszgKO9ULm3e2naOTqOh9R61d03U1to/JlB2dQRzigDQk0e0dMIrIfUEn9Dmm6fpptZXkkYM3RcdhSyazaqPkLOfQDH86bYakbuZo3QA9Vx6UAYlz/x9S/75/nWloP8Arpv90fzrNuf+Pqb/AH2/nWloP+um/wB0fzoAg1a5aa7aPOEjOAPeqsHkeaPPD7PROtW9WtWhuml/gkOQfQ+lVIDCJQJwxTvt6igDVR9IkXbtC/UEH86eNGRbpHVz5Q6qev0zTEOjxoG4J/2gxNL/AG0huVATEPQsev1oAtald/ZbU7T87/Kv
+Nc0eeT9at3k7X158oJBO1B7VoTaMrWyiI4lA5z0agClYak9q2x8tF6d1+ldFFKk0QkRgynuK449a3NCZzDMD90MMfXv/SgDYooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAIZ7aK5XbKm4dvUVnSaFGT8kzL/vDNa9FAGRHoMYP7yZ2+gx/jWhBaw2y7YkC+p7mp6KAEzj2rlr50nvXMS8EgDHc1vagtw9uY7dCzPwTkDAqlpumyRT+bcJtK/dXIPPrQBesbT7JbhP4jyx96t0g6UtAEM9tFcLtlQMO3qKzpNCjJ+SZl/wB4ZrXooAyI9BjB/eTO30GP8a0ILaK2XbEgX37mp6KAM19Ft5HZ2eXLEk8j/Cp7TT4rNmaNnO4YO4j/AAq3RQAx0EilWUFT1B5rOl0SBzmNnj9uorUooAxl0FQfmuCR7Jj+tWl0m3SJkXeCwwXz82P6VfooAoW2lwWswlTcWHA3YwP0q1NCJ4mjYsobuOCKlooAx/7BTdxO23028/n/APWrTggS3iEca4UVLRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUU
UAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFAH/2Q==</ImageData>
</EmbeddedImage>
</EmbeddedImages>
<LeftMargin>0.5in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description>Adventure Works sales by quarter and product category. This
report illustrates the use of a matrix data region that provides drilldown
from summary data into detail data by showing and hiding rows. This report
also illustrates the use of background images.</Description>
<rd:ReportID>312657d4-5053-4f4c-b4fa-c580a92fd43b</rd:ReportID>
<BottomMargin>0.5in</BottomMargin>
</Report>
--I suspect you need use the column scope not the row scope.
If you can't get that to work, build the year total into each row of
the dataset then you can do Sum(Fields!Amount.Value) /
First(Fields!Total.Value) instead. NB: note the use of FIRST.
--
Regards
Chris
Riccardo wrote:
> Problem: Subtotal is not calculated correct in a Matrix expression.
> Need some help with an expression in a matrix that should calculate
> percent of subtotal for a measure.
> The matrix look like this
> Column_OrderYear
> RowGroup1 | RowGroup2 | Sales
> % Share
> I want % Share to calculate as sales / subtotal on RowGroup1(per
> Column_OrderYear). If I use RowGroup1 in the scope it summerize the
> subtotal for all Column_Year (all years) on that row.
> I have included a modified version of Company Sales.rdl that is
> included in samples for RS2000. In this example, for the first group
> Component and Bottom Bracket, % Share should be calculated as
> 37977.81 / 6778096.94. Instead its calculated as 37977.81/11233756.04.
>
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/report
> definition"
> xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesig
> ner"> <rd:GridSpacing>0.0625in</rd:GridSpacing>
> <RightMargin>0.5in</RightMargin> <Body>
> <ReportItems>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>5.73232cm</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="Share">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>White</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Sales.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.68068cm</Height>
> </MatrixRow>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <Format>P</Format>
> <BackgroundColor>White</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Sales.Value) /
> sum(Fields!Sales.Value, "matrix1_ProdCat")</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.54454cm</Height>
> </MatrixRow>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Sales.Value,
> "matrix1_ProdCat")</Value> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.67072cm</Height>
> </MatrixRow>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>White</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Sales.Value,
> "matrix1_SubCat")</Value> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.67072cm</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>5.09452cm</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>Sales</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_OrderYear">
> <GroupExpressions>
> <GroupExpression>=Fields!OrderYear.Value</GroupExpression>
> </GroupExpressions> </Grouping>
> <ReportItems>
> <Textbox Name="OrderYear_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>OrderYear_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderYear.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.599cm</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>12.90389cm</Width>
> <Top>2.85714cm</Top>
> <Left>0.15873cm</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_ProdCat">
> <GroupExpressions>
> <GroupExpression>=Fields!ProdCat.Value</GroupExpression>
> </GroupExpressions> </Grouping>
> <ReportItems>
> <Textbox Name="ProdCat">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>ProdCat</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProdCat.Value</Value>
> </Textbox>
> </ReportItems>
> <Subtotal>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Crimson</BackgroundColor>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Total</Value>
> </Textbox>
> </ReportItems>
> </Subtotal>
> </DynamicRows>
> <Width>2.69841cm</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_SubCat">
> <GroupExpressions>
> <GroupExpression>=Fields!SubCat.Value</GroupExpression>
> </GroupExpressions> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!SubCat.Value</SortExpression>
> <Direction>Ascending</Direction> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!SubCat.Value</Value>
> </Textbox>
> </ReportItems>
> <Subtotal>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Crimson</BackgroundColor>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Total</Value>
> </Textbox>
> </ReportItems>
> </Subtotal>
> </DynamicRows>
> <Width>2.80455cm</Width>
> </RowGrouping>
> <RowGrouping>
> <Width>2.30641cm</Width>
> <StaticRows>
> <StaticRow>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Sales</Value>
> </Textbox>
> </ReportItems>
> </StaticRow>
> <StaticRow>
> <ReportItems>
> <Textbox Name="textbox8">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox8</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value> % Share </Value>
> </Textbox>
> </ReportItems>
> </StaticRow>
> <StaticRow>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>subtotal 1 </Value>
> </Textbox>
> </ReportItems>
> </StaticRow>
> <StaticRow>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Green</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <Color>White</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>subtotal 2</Value>
> </Textbox>
> </ReportItems>
> </StaticRow>
> </StaticRows>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> <Textbox Name="Title">
> <Style>
> <FontFamily>Tahoma</FontFamily>
> <FontSize>18pt</FontSize>
> <Color>DarkSlateBlue</Color>
> <FontWeight>800</FontWeight>
> </Style>
> <Height>0.9375in</Height>
> <Width>3.125in</Width>
> <Top>0.0625in</Top>
> <Value>Adventure Works
> 2002 - 2003
> Sales - Modified</Value>
> <Left>0.0625in</Left>
> </Textbox>
> </ReportItems>
> <Style>
> <BackgroundImage>
> <Source>Embedded</Source>
> <Value>logoback</Value>
> </BackgroundImage>
> </Style>
> <Height>6.80507in</Height>
> </Body>
> <TopMargin>0.5in</TopMargin>
> <DataSources>
> <DataSource Name="AdventureWorks">
> <rd:DataSourceID>25d3314c-0d4f-49cc-9c22-10194e825490</rd:DataSourceID
> > <DataSourceReference>AdventureWorks</DataSourceReference>
> </DataSource> </DataSources>
> <Width>15.87302cm</Width>
> <DataSets>
> <DataSet Name="Sales">
> <Fields>
> <Field Name="ProdCat">
> <DataField>ProdCat</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SubCat">
> <DataField>SubCat</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="OrderYear">
> <DataField>OrderYear</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="OrderQtr">
> <DataField>OrderQtr</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Sales">
> <DataField>Sales</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>AdventureWorks</DataSourceName>
> <CommandText>SET DATEFORMAT mdy
> SELECT ProductCategory.Name AS ProdCat,
> ProductSubCategory.Name AS SubCat, DATEPART(yy,
> SalesOrderHeader.OrderDate) AS OrderYear,
> 'Q' + DATENAME(qq,
> SalesOrderHeader.OrderDate) AS OrderQtr,
> SUM(SalesOrderDetail.UnitPrice * SalesOrderDetail.OrderQty)
> AS Sales
> FROM ProductSubCategory INNER JOIN
> SalesOrderHeader
> INNER JOIN
> SalesOrderDetail ON SalesOrderHeader.SalesOrderID => SalesOrderDetail.SalesOrderID INNER JOIN
> Product ON SalesOrderDetail.ProductID = Product .ProductID ON
> ProductSubCategory.ProductSubCategoryID = Product
> .ProductSubCategoryID INNER JOIN
> ProductCategory ON
> ProductSubCategory.ProductCategoryID => ProductCategory.ProductCategoryID WHERE
> (SalesOrderHeader.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
> GROUP BY DATEPART(yy,
> SalesOrderHeader.OrderDate), ProductCategory.Name,
> ProductSubCategory.Name, 'Q' + DATENAME(qq,
> SalesOrderHeader.OrderDate),
> ProductSubCategory.ProductSubCategoryID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <EmbeddedImages>
> <EmbeddedImage Name="logoback">
> <MIMEType>image/jpeg</MIMEType>
> <ImageData>/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAs
> LDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJC
> QwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjI
> yMjIyMjIyMjL/wAARCADCAbYDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECA
> wQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaE
> II0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZ
> WZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMX
> Gx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAA
> AAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRM
> iMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVV
> ldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba
> 3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAP
> wD3+iiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACii
> igAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooo
> oAKKKKACiiigAooooAKKKKACiiigAoo
> ooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKK
> KACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiii
> gAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAEJxWHdazJ5
> pWAKFHGSMk1tsNykeoxXIzwvBM0bggg/nQBtadqjTyiGYAMfukd61s1y1hbvPPkZCqCWI+
> lRC5nQ/LM4x/tGgDrqK5Qahdr0nf8TUo1a8H/LTP1FAHTUVzq61cggsEYfTFb8MnmwpJjG
> 4ZxQA+iiigAoqGW5jicKx5PtUqkMoIOQaLALRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRR
> RQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUU
> UAFFFFABRRRQAVDNFC6lpUVgBnLDpU1ZOs3eyMW6Hl+Wx6UAQ2+qM12sSRIIWbaFUYP1qw
> 2hwMxO9xk+1V9FtMk3LDpwv9TW5QBz95YWlmvzTSFyOFGKzURpHCopLHoBW5q1i8xE8fJA
> wV9azbG9Nm5PlhgepPUUAX7PSUiAluiCeoXsPrWl9qgXjeOPSoYYRcKJZJPMB5AB4qwIIh
> 0jX8qegCxzJLnYwOKfTVREJ2qBn0pJJBEhdug6UgM28x9pb8Kv2oItkz6VQjRrmck9Cck1
> pM6RJliABVydlYErklJVCXUgOI1z7mq5nupz8u78BWXMi1TfU18gdxSb1/vCsn7LdN1B/F
> qabO4X+D8qXMyvZx7myCKXNYJ82I87l/SpY76ZOp3D0NHP3B0n0NjilqpBeRykA/Kx7GrY
> 6VSdzNprcKKKKYgooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiijNABRRRQAU
> UUUAFFGaM0AFFFFABRR
> RQAUUUZoAKKM0ZoAKgmuFiO0Dc56KKfPJ5UTOBkjpUVvDtHmPy7cnPahARSed5TSSyeWij
> JC9awFWS8ugucs54J9K6p0WRCjDKkYIqvbafBayM8anJ4ye1FwGRxz2kaqmHjXtjmrMMyz
> Lleo6j0qTtVW4jMTCePgj7w9RT3AsOQqlj0AzWLcac1yj3MYAck/KO4rUncNaMy9GFSQDE
> CY9KOgHO2V9JZSbWBMefmU9q6KGZJoxJGwKmqWo6aLgGSIYlH/j1V9It7mKZ/MVljxyD3N
> IDTluo4uCcn0FU2WW5O+Q7Ix60skgSZ1ihLPnljR9lmm+a4k2j0o5uxSj1YNdpEvlwLuPq
> e9MW1nuG3SEge9Tq9vbnES739uaUi6m6YjWlyt6sfOl8I5LW3g5bGfUmpBcQDgOoqEafnl
> pCTTX0/j5HP0NUoxIbb3LqurDKsCPY0tY7LJA/dWHcVo2kzTREt1HBPrTcbATMisMEZFUp
> 7BWy0R2n0q/SVDSY1JrYxPs0wcAIc5raQEIAeuKXFLSUbFSm5bhRRRVEBRRRQAUUUUAFFF
> FABRRRQAUGig0AQ3Evkx5AyxOAPU1FDNK05jkVRhd2RTLxz50SqpbB3ECltn3maZhjtj6V
> DepdtBHuZQ0hRUKJwSTzUrzlbeOQLy2OPrVXB+xAdGmfn8TVi6A2wr28wflRcHYlMpM4jA
> zgZY+npUPnzvJIIlQqpxknvToP9W87fxnP4dqitFnADfJscljnOadwsSzzvDbh2A38cDpS
> mY7oVUDMnJ+lRXY8yaOPtgsabaHzJlPaOMD8TSvqFtLkt1PLCAVVSvv604SyLbu8qgMATg
> HrTLn57iCPtu3H8Kden9wIx1dgtMQee2IPlG6Q8j0FEs8gn8qJVJ27jmmgbr4DtGn6mq7y
> Sh55oyoXO059vSlcaWpaS4LWrTMAMA/jSRSXL7WKIEPXnnFRzr5dkkScFiFFWLcTAHzNmO
> 22ncTSsTdqhmlKSRqoG
> WP6VNVG8fbPGR94K2B7ngU27CirsebvidgOEOB9afNM0USfKDIxxj3qnAmZRFnOH3E59B/
> jVmb576JOygsam5TSTHQTSvK8cgUFQOVpj3Mu6QoqFEPUmltmGJpj3Y4+gqqvmeUobAjlc
> Zx96i7Gki7JOVtkcD5mxgH3pUmLzMigFF4J9/SoLsM8sUceNwy3Pan2RPkurAZViMjvTuK
> ytcSS5k/eFEUpGcEsetWYn3xK5GCRkiqOHhwjojJI/r6mtADgYpoUkOormp9Su1uJFWYhQ
> 5AGB61e0i6nuZZRLIWAAIpkmvTWAZSD3FOHSkJwCaAKcI32skf90kVNavut19RwaZZjKO3
> 95jTWzazFwCYn647GqYFykpFcOoKkEU6pAilSQj90wU9yRUItCxzLKze1W6KLsCNIkjHyq
> BUgoooAKKKKAGSRpIMOoIoRFjGFXAp9FABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQ
> AUUUUAVRG/2qSQrxtAX3pghkWwZAuZGByM46/wD1quUUrDuUoopXki3psSPpznNSXcTyqg
> Qfxcn0FWqSiwcxBMjfZmSNcnGAKZbGdQqNEFQDGcirfakosFyssTG6eRl+XaFWksoGhRtw
> wSf0q1iiiwX6Ffy3N6ZGX5AuAfem3SSl4njTeEycZx9KtUUWC5WhjkXzJHUB35wD6VH9nk
> +yrHtyWfL89s1doosHMVLlZfMiMabgmScnH0qaFpWUmRNp9M5ptxdQ2y7pXxnoO5rOfXVB
> IjgJHqzYoSC5sdqqSwNJeq5HyKvB96pJrqE4kgIHqrZ/Tirn9o25tzMhLqv3go5H4UNXBO
> w61gaOSV2GCzcfSo5EuFuXdEDBhgHPSov7ctf+ec35D/GrVreRXis0eRtPIbrRyhzaiGF0
> sTEg3NjHX1o8lvMgAHyRg5Oe/ap5HWNGdyAqjJqlDq0E8qxIkpZjjoP8aLBzE4jf7XJIV4
> CgLz1piwutgyY/eEHP1
> NSz3UNsm6VgvoB1NZ0muqD+7gLD1Zsf0osFy1FFM0kW+MIsee+c1erHj11C2JICB6q2f8K
> 0be6huV3ROGx1HQihKwN3OYuf+Pqb/fb+daWg/wCum/3RWbc/8fUv++f51paD/r5v90UxG
> 7SEBgQehpskiRIXkYKo6k1mS65ChxFG0nuTtFAGmiLGoVRgClIDDBGQaxl145+a349n/wD
> rVettSt7k7VYq/wDdbjNADjbNG26CTb/snpVpc4Geveis1tWiW9MTD92ON/v/AIUAadFIr
> AgEEEGloAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAoo
> ooAKKKKACiiigAooooAKKKKACmSOERnPRRk0+muodWU9CMGgDk7id7idpHPJP5Vf03TUuY
> /OmJ25wFBxms+5t3tpmjcdOh9RV7TdTW2j8mUHZ1BHOKANCXR7V0IRWQ9iGJ/Q0yx0v7O8
> jSkOT8q46Yp0ms2qj5Czn0Ax/Om6fqRu52jdAD1XHTHpQBkX1qbS4KfwHlD7UWdybS5Vx9
> 3ow9RW/f2n2q2IH+sXlD71zBBBIIII4waANbV74SBYImyuAzEHr6CptKtRb27XMg+Yrkf7
> tZ2n2n2u5AI/dry3+H410rIGjKEfKRjA9KAOTuJ3uJmlc8k8D0rQ0zTUuIzNMTszgKO9UL
> m3e2naOTqOh9R61d03U1to/JlB2dQRzigDQk0e0dMIrIfUEn9Dmm6fpptZXkkYM3RcdhSy
> azaqPkLOfQDH86bYakbuZo3QA9Vx6UAYlz/x9S/75/nWloP8Arpv90fzrNuf+Pqb/AH2/n
> WloP+um/wB0fzoAg1a5aa7aPOEjOAPeqsHkeaPPD7PROtW9WtWhuml/gkOQfQ+lVIDCJQJ
> wxTvt6igDVR9IkXbtC/UEH86eNGRbpHVz5Q6qev0zTEOjxoG4J/2gxNL/AG0huVATEPQse
> v1oAtald/ZbU7T87/Kv
> +Nc0eeT9at3k7X158oJBO1B7VoTaMrWyiI4lA5z0agClYak9q2x8tF6d1+ldFFKk0QkRgy
> nuK449a3NCZzDMD90MMfXv/SgDYooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAC
> iiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAIZ7aK5XbKm4dvUVnSaFGT8k
> zL/vDNa9FAGRHoMYP7yZ2+gx/jWhBaw2y7YkC+p7mp6KAEzj2rlr50nvXMS8EgDHc1vagt
> w9uY7dCzPwTkDAqlpumyRT+bcJtK/dXIPPrQBesbT7JbhP4jyx96t0g6UtAEM9tFcLtlQM
> O3qKzpNCjJ+SZl/wB4ZrXooAyI9BjB/eTO30GP8a0ILaK2XbEgX37mp6KAM19Ft5HZ2eXL
> Ek8j/Cp7TT4rNmaNnO4YO4j/AAq3RQAx0EilWUFT1B5rOl0SBzmNnj9uorUooAxl0FQfmu
> CR7Jj+tWl0m3SJkXeCwwXz82P6VfooAoW2lwWswlTcWHA3YwP0q1NCJ4mjYsobuOCKlooA
> x/7BTdxO23028/n/APWrTggS3iEca4UVLRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUU
> UUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFF
> FABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRR
> QAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUU
> AFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFA
> BRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQA
> UUUUAFFFFABRRRQAUUU
> UAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFF
> ABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQ
> AUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUA
> FFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFAH/2Q==</ImageData>
> </EmbeddedImage> </EmbeddedImages> <LeftMargin>0.5in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid> <rd:DrawGrid>true</rd:DrawGrid>
> <Description>Adventure Works sales by quarter and product category.
> This report illustrates the use of a matrix data region that provides
> drilldown from summary data into detail data by showing and hiding
> rows. This report also illustrates the use of background
> images.</Description>
> <rd:ReportID>312657d4-5053-4f4c-b4fa-c580a92fd43b</rd:ReportID>
> <BottomMargin>0.5in</BottomMargin> </Report>
> --
>|||Thanks for your reply!
Well, the scope for the column is not working, since then it only summerize
for all rowgroups.
There must be an other solution than suggested calculating the subtotals in
the dataset and using "First(Fields!Total.Value)" , since my real case has
several rowgroups and columngroups and measures, subcalculating will do the
dataset to complex and maybe to slow.
"Chris McGuigan" wrote:
> I suspect you need use the column scope not the row scope.
> If you can't get that to work, build the year total into each row of
> the dataset then you can do Sum(Fields!Amount.Value) /
> First(Fields!Total.Value) instead. NB: note the use of FIRST.
> --
> Regards
> Chris
>
> Riccardo wrote:
> > Problem: Subtotal is not calculated correct in a Matrix expression.
> >
> > Need some help with an expression in a matrix that should calculate
> > percent of subtotal for a measure.
> >
> > The matrix look like this
> > Column_OrderYear
> >
> > RowGroup1 | RowGroup2 | Sales
> > % Share
> >
> > I want % Share to calculate as sales / subtotal on RowGroup1(per
> > Column_OrderYear). If I use RowGroup1 in the scope it summerize the
> > subtotal for all Column_Year (all years) on that row.
> >
> > I have included a modified version of Company Sales.rdl that is
> > included in samples for RS2000. In this example, for the first group
> > Component and Bottom Bracket, % Share should be calculated as
> > 37977.81 / 6778096.94. Instead its calculated as 37977.81/11233756.04.
> >
> >
> > --
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/report
> > definition"
> > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesig
> > ner"> <rd:GridSpacing>0.0625in</rd:GridSpacing>
> > <RightMargin>0.5in</RightMargin> <Body>
> > <ReportItems>
> > <Matrix Name="matrix1">
> > <Corner>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </Corner>
> > <Height>5.73232cm</Height>
> > <ZIndex>1</ZIndex>
> > <Style />
> > <MatrixRows>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="Share">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>White</BackgroundColor>
> > <TextAlign>Right</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Sales.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.68068cm</Height>
> > </MatrixRow>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="textbox9">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <Format>P</Format>
> > <BackgroundColor>White</BackgroundColor>
> > <TextAlign>Right</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox9</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Sales.Value) /
> > sum(Fields!Sales.Value, "matrix1_ProdCat")</Value>
> > </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.54454cm</Height>
> > </MatrixRow>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>4</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Sales.Value,
> > "matrix1_ProdCat")</Value> </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.67072cm</Height>
> > </MatrixRow>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>White</BackgroundColor>
> > <TextAlign>Right</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Sales.Value,
> > "matrix1_SubCat")</Value> </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.67072cm</Height>
> > </MatrixRow>
> > </MatrixRows>
> > <MatrixColumns>
> > <MatrixColumn>
> > <Width>5.09452cm</Width>
> > </MatrixColumn>
> > </MatrixColumns>
> > <DataSetName>Sales</DataSetName>
> > <ColumnGroupings>
> > <ColumnGrouping>
> > <DynamicColumns>
> > <Grouping Name="matrix1_OrderYear">
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!OrderYear.Value</GroupExpression>
> > </GroupExpressions> </Grouping>
> > <ReportItems>
> > <Textbox Name="OrderYear_1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Green</BackgroundColor>
> > <TextAlign>Right</TextAlign>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>OrderYear_1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!OrderYear.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </DynamicColumns>
> > <Height>0.599cm</Height>
> > </ColumnGrouping>
> > </ColumnGroupings>
> > <Width>12.90389cm</Width>
> > <Top>2.85714cm</Top>
> > <Left>0.15873cm</Left>
> > <RowGroupings>
> > <RowGrouping>
> > <DynamicRows>
> > <Grouping Name="matrix1_ProdCat">
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!ProdCat.Value</GroupExpression>
> > </GroupExpressions> </Grouping>
> > <ReportItems>
> > <Textbox Name="ProdCat">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Green</BackgroundColor>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>ProdCat</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!ProdCat.Value</Value>
> > </Textbox>
> > </ReportItems>
> > <Subtotal>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Crimson</BackgroundColor>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Total</Value>
> > </Textbox>
> > </ReportItems>
> > </Subtotal>
> > </DynamicRows>
> > <Width>2.69841cm</Width>
> > </RowGrouping>
> > <RowGrouping>
> > <DynamicRows>
> > <Grouping Name="matrix1_SubCat">
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!SubCat.Value</GroupExpression>
> > </GroupExpressions> </Grouping>
> > <Sorting>
> > <SortBy>
> >
> > <SortExpression>=Fields!SubCat.Value</SortExpression>
> > <Direction>Ascending</Direction> </SortBy>
> > </Sorting>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Green</BackgroundColor>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!SubCat.Value</Value>
> > </Textbox>
> > </ReportItems>
> > <Subtotal>
> > <ReportItems>
> > <Textbox Name="textbox14">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Crimson</BackgroundColor>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox14</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Total</Value>
> > </Textbox>
> > </ReportItems>
> > </Subtotal>
> > </DynamicRows>
> > <Width>2.80455cm</Width>
> > </RowGrouping>
> > <RowGrouping>
> > <Width>2.30641cm</Width>
> > <StaticRows>
> > <StaticRow>
> > <ReportItems>
> > <Textbox Name="textbox7">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Green</BackgroundColor>
> > <TextAlign>Right</TextAlign>
> > <Color>White</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
Posted by rodshapedasdc at 2:06 AM
Labels: , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)