How to Calculate Growth% in reportviewer (Visual Studio)

Question

User-360368664 posted

Dear All

please help with the calculation of growth% in <g class="gr_ gr_33 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="33" data-gr-id="33">reportviewer</g> (visual studio)

the sheet linked with SQL server. we have a location wise sales data and we want to compare like-for-like sales growth in the same report

I have run a query with date filters and convert <g class="gr_ gr_976 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="976" data-gr-id="976">date</g> into <g class="gr_ gr_997 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="997" data-gr-id="997">year</g> as mentioned below so how can I calculate the %growth between these two sales figure

i am working on visual studio <g class="gr_ gr_1483 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="1483" data-gr-id="1483">reportviewer</g> 

Report Format/Example

		Header Group
Rows		2017	2018	Will Add
Location	Sale	Sale	Growth%??
Delhi	55555	99999
Mumbai	55555	99999
Bangalore	55555	99999
Total	166665	299997

Answer 1

User347430248 posted

Hi Shuaib,

I suggest you to calculate the growth in SQL query and use the same query in SSRS report.

You can refer the query below as an example.

ITH SalePercentage_cte AS ( 
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY ProductName,SalesYear ORDER BY ProductName,SalesYear, SalesMonth) rn, 
        ProductName, 
        SalesYear, 
        SalesMonth, 
        Sales 
    FROM  @Sales 
) 
SELECT s1.ProductName,s1.SalesYear,s1.SalesMonth, 
    CAST(( ( s1.Sales - s2.Sales ) / s2.Sales ) * 100.0 AS DECIMAL(7, 2)) AS Percentage 
FROM SalePercentage_cte AS s1 
LEFT JOIN SalePercentage_cte s2 
ON  s1.rn = s2.rn + 1  
    AND s1.ProductName = s2.ProductName  
    AND s1.SalesYear = s2.SalesYear 

Reference:

How to calculate the increasing percentage of sales each month using T-SQL

Further, you can try to modify the query to get expected result.

Regards

Deepak