Hi Abhiram,
As per my understanding you can show your fields in matrix.
Only problem is to create the additional column which will have the %SalesDifference value right?
If yes,
Just create one column as shown in below screen,
It will create one column, Name the header of this newly created column as %SalesDifference
In below this header , where you want to show the value for %SalesDifference as
=sum(iif(Fields!year.Value=Parameters!year.Value,(Fields!SalesAmount.Value),-Fields!SalesAmount.Value))/Fields!SalesAmount.Value
Set the property of the column in number as Percentage.
run the report, you will see as below screen.
For your reference I am attaching my RDL code, you can save as .rdl file and run the report (sample Data inside the report only)
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<Body>
<ReportItems>
<Textbox Name="textbox1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Matrix</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>14pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>SteelBlue</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.37in</Height>
<Width>5in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
<Tablix Name="Tablix1">
<TablixCorner>
<TablixCornerRows>
<TablixCornerRow>
<TablixCornerCell>
<CellContents>
<Textbox Name="Textbox16">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Product</Value>
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox16</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCornerCell>
</TablixCornerRow>
<TablixCornerRow>
<TablixCornerCell>
<CellContents>
<Textbox Name="Textbox17">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox17</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCornerCell>
</TablixCornerRow>
</TablixCornerRows>
</TablixCorner>
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1.79722in</Width>
</TablixColumn>
<TablixColumn>
<Width>1.76722in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="SalesAmount">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!SalesAmount.Value)</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>SalesAmount</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox76">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=sum(iif(Fields!year.Value=Parameters!year.Value,(Fields!SalesAmount.Value),-Fields!SalesAmount.Value))/Fields!SalesAmount.Value</Value>
<Style>
<Format>0.00%</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox76</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="month">
<GroupExpressions>
<GroupExpression>=Fields!month.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!month.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.25in</Size>
<CellContents>
<Textbox Name="month1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=MonthName(Fields!month.Value)</Value>
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>month1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<Group Name="year">
<GroupExpressions>
<GroupExpression>=Fields!year.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!year.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.25in</Size>
<CellContents>
<Textbox Name="year">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!year.Value</Value>
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>year</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
<TablixMember>
<TablixHeader>
<Size>0.25in</Size>
<CellContents>
<Textbox Name="Textbox61">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>%SalesDifference</Value>
<Style>
<FontStyle>Normal</FontStyle>
<FontWeight>Bold</FontWeight>
<TextDecoration>None</TextDecoration>
<Color>#000000</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox61</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>0.25in</Size>
<CellContents>
<Textbox Name="Textbox62">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox62</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="Product">
<GroupExpressions>
<GroupExpression>=Fields!Product.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Product.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1.38889in</Size>
<CellContents>
<Textbox Name="Product1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Product.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Product1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Top>0.38in</Top>
<Left>0.04667in</Left>
<Height>0.75in</Height>
<Width>4.95333in</Width>
<ZIndex>1</ZIndex>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>1.20167in</Height>
<Style />
</Body>
<Width>5.1in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource1">
<DataSourceReference>DataSource1</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>501ee6de-61fb-416f-9a92-011661d01cba</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<DataSourceName>DataSource1</DataSourceName>
<QueryParameters>
<QueryParameter Name="@year">
<Value>=Parameters!year.Value</Value>
</QueryParameter>
<QueryParameter Name="@Month">
<Value>=Parameters!Month.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>select * from
(
select 'apple' Product ,1 month ,2014 year,2000 SalesAmount
union
select 'apple' Product ,1 month,2015 year,3000 SalesAmount
union
select 'dell' Product ,1 month,2014 year,3000 SalesAmount
union
select 'dell' Product ,1 month,2015 year,2500 SalesAmount
union
select 'apple' Product ,2 month,2014 year,1500 SalesAmount
union
select 'apple' Product ,2 month,2015 year,3000 SalesAmount
union
select 'dell' Product ,2 month,2014 year,3000 SalesAmount
union
select 'dell' Product ,2 month,2015 year,5500 SalesAmount
)t
where year between @year-1 and @year
and Month=@Month</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Product">
<DataField>Product</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="month">
<DataField>month</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="year">
<DataField>year</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SalesAmount">
<DataField>SalesAmount</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="year">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>2015</Value>
</Values>
</DefaultValue>
<Prompt>year</Prompt>
</ReportParameter>
<ReportParameter Name="Month">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>1</Value>
</Values>
</DefaultValue>
<Prompt>Month</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>ee1a8383-6595-42b1-94f2-c68d681c85d3</rd:ReportID>
</Report>
Thanks
Prasad
------------------------------------------------------------
Mark this as Answer if it helps you to proceed on further.
-
Marked as answer by
Abhiram542
27 minutes ago