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