How to calculate Percentage increase on reports
Hi
I have done a sales report on which I need to add a the percentage increase column.
My Report is like
Date Total Sales Percentage increase
=================================
12/12/2010 30 50%
11/12/2010 20 33%
10/12/2010 15
I have only "Date" and "Total sales" columns now.
I need to add a new column (Percentage increase) on my report as i showed above.
Thanks a lot in advance.
shan
December 13th, 2010 10:08am
Hi Shan,
I think you need "Running Count", here is the link for more detail:-
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e7c5c52a-fb3f-4ad6-ae62-50bdb44a6402
Please let us know your feedback.
KumarKG
Free Windows Admin Tool Kit Click here and download it now
December 13th, 2010 5:18pm
Hi
Thanks for your reply.
I have added KPI Indicators on the report like increase, decrease - Is there and expression to display the percentage increase/decrease near the Indicator ?
Thanks a lot in advance.
December 14th, 2010 7:52am
Hi All
I found the below Custom Code thats calcultes the percentage increase/decrease.
Can any one guide me from here.
Thanks a lot in advance.
http://technet.microsoft.com/en-us/library/ms157328.aspx
Use a custom code function to return the value for the expression. The following example returns the percentage difference between a current value and a previous value. This can be used to calculate the difference between any two successive values and it
handles the edge case of the first comparison (when there is no previous value) and cases whether either the previous value or the current value is
null (Nothing in Visual Basic).
Copy Code
Public Function GetDeltaPercentage(ByVal PreviousValue, ByVal CurrentValue) As Object
If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then
Return Nothing
Else if PreviousValue = 0 OR CurrentValue = 0 Then
Return Nothing
Else
Return (CurrentValue - PreviousValue) / CurrentValue
End If
End Function
Free Windows Admin Tool Kit Click here and download it now
December 14th, 2010 9:43am
The following example returns the percentage difference between a current value and a previous value. This can be used to calculate the difference between any two successive values and it handles the edge case of the first comparison (when there
is no previous value) and cases whether either the previous value or the current value is
null (Nothing in Visual Basic).
As the document says, the custom code is used to calculate the percentage difference between a current value and a
previous value. However, in the sample report you posted, we need to calculate the percentage difference between a current value and a
subsequent value. Due to the processing sequence, I’m afraid we can only refer to the previous field value.
If possible, we can first sort the records by date in ascending, and specify the expression to the cell in Percentage increase column like
=IIf(IsNothing(Previous(Fields!TotalSales.Value)),
nothing,
(Fields!TotalSales.Value-Previous(Fields!TotalSales.Value))/Previous(Fields!TotalSales.Value))
Then we will get the result as the following table shows:
Date
Total Sales
Percentage increase
10/12/2010
15
11/12/2010
20
33%
12/12/2010
30
50%
Thank you for your understanding. If you have any more questions, please feel free to ask.
Regards,
Tony ChainTony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
December 15th, 2010 2:31am
Hi Tony
Yes your are right.
The Expression worked fine.
Now I just want to Round it to 2 Decimal Digits and and add a % Symbol at the end.
The expression throw out an error " Input string is not in a cocrrect format" - If i add Round Function.
Thanks a lot
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 5:34am
Hi Tony
Yes your are right.
The Expression worked fine.
Now I just want to Round it to 2 Decimal Digits and and add a % Symbol at the end.
The expression throw out an error " Input string is not in a cocrrect format" - If i add Round Function.
Thanks a lot
December 15th, 2010 5:34am
Hello,
Try this....
=Format(IIf(IsNothing(Previous(Fields!TotalSales.Value)),nothing,(Fields!TotalSales.Value-Previous(Fields!TotalSales.Value))/Previous(Fields!TotalSales.Value)), "P")
Hope its helpful....Pavan Kokkula Tata Consultancy Services.
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 5:37am
Hello,
Try this....
=Format(IIf(IsNothing(Previous(Fields!TotalSales.Value)),nothing,(Fields!TotalSales.Value-Previous(Fields!TotalSales.Value))/Previous(Fields!TotalSales.Value)), "P")
Hope its helpful....Pavan Kokkula Tata Consultancy Services.
December 15th, 2010 5:37am
Hi Pavan
Thanks a lot - It worked perfect.
Thank you all for your support
regards
shan
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 5:43am
Hi Pavan
Thanks a lot - It worked perfect.
Thank you all for your support
regards
shan
December 15th, 2010 5:43am