percentage calculation in matrix in ssrs
Hi Fanny, Thanks for your reply, but i want to show the fiscalyear in column wise, can you please help me with this.Nagaraj
September 27th, 2012 5:25am

HI I am having a matrix report in ssrs 2008 r2, like this in the in the above report the 3 columns 2002,2003,2004 are there in fiscalyear column. i need (2003-2002 ) sales percentage how can i do this. any information will be helpful. Nagaraj
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 8:33am

Most often a Fiscal year starts on a specific day of the week so it usually doesn't start on the 1st of the month. One year it is June 3, The next May 30. Without embedding it in your data source from a reliable source, the best I can suggest is to get close. If your fiscal year is around the start of June each year (June 2012 is FY2013) then you can get the fiscal year with: =Year(DateAdd(DateInterval.Month,7,Fields!CalendarDate))
September 30th, 2012 8:42am

You may be able to use RunningValue for this. To get % Sales what are you considering the total? Is it Sum(Sales in Target Years)/Sum(Sales in All Years)?
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 9:54am

Hi Nagaraju- Assuming you are trying to get 2002-2003 sales as a percentage of total sales, you can try the following: 1) In design view, right click [Sum(Sales)] 2) Go to "Add Total" and add a column total 3) Right click in the newly created column total field and choose "Expression" 4) In the expression window type =sum(iif(Fields!FiscalYear.Value <> 2004, (Fields!Sales.Value), 0))/sum(Fields!Sales.Value) Hope this helps~Bonediggler
September 30th, 2012 11:44am

Hi bonediggler, Thanks for replying, i want to know the increase percentage in sales from 2002 to 2003 and 2003 to 2004, it is displaying as fiscalyear column group, how can i take particular columns(2002 sales, 2003 sales) in the expression.Nagaraj
Free Windows Admin Tool Kit Click here and download it now
October 1st, 2012 12:33am

Hi There Thanks for your posting. Can you please have a look on this post that might help you http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/79a3f878-2f13-4047-83b1-7cc8c5df2ad2 Many thanks Syed Qazafi Anjum
October 1st, 2012 12:50am

Hi Nagaraju, Just as the thread Syed posted above, we can use previous function to calculate the adjacent instances of a row group. In order to meet your requirement, please redesign your report and drag the fiscalyear field to the Row Groups pane of the matrix data region. The following screen shot shows the structure and preview of my test report. Please take the following expression as reference. =IIF(IsNothing(Previous(Sum(Fields!SalesAmount.Value))) or Fields!CalendarYear.Value=First(Fields!CalendarYear.Value,"SalesTerritoryGroup"),"N/A",(Sum(Fields!SalesAmount.Value)-Previous(Sum(Fields!SalesAmount.Value)))/IIF(IsNothing(Previous(sum(Fields!SalesAmount.Value))),1,Previous(Sum(Fields!SalesAmount.Value)))) For more information about previous function, please see: http://msdn.microsoft.com/en-us/library/dd255200(v=sql.100).aspx Regards, Fanny LiuFanny Liu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
October 1st, 2012 4:16am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics