Monday September 25th 2017

Categories

Insider

Archives

Automated Business Intelligence – products profitability on month to month basis for 3 months, growth and Ranking grouped under store

I came across the client’s request to develop a report that shows products profitability on month to month basis for 3 months, growth and Ranking grouped under store. as follows;

reportranking. Database was my sql and ranking function was not available. There are number of ways to achieve this in the absence of Ranking function i.e. we can create temp tables in Database side, however we adopted the functionality in Crystal report formula workshop to achieve this request.

We used the following logic;
Ranking formula

Running total to calculate the ranking – in sub report as follows ;
whileprintingrecords;
if {@onlastrecords} = 1 then
Numbervar Profitabilty1:=Profitabilty1+1 else
if{@FormattedProfitabilty1}<0 then Numbervar Profitabilty1:=Profitabilty1+1 else if {@Formatted Profitabilty1} = next({@Formatted Profitabilty1}) then Profitabilty1+1 else if {@Formatted Profitabilty1}=0 then Numbervar Profitabilty1:= Profitabilty1+0 else numbervar Profitabilty1 := Profitabilty1+ 1 Please see the figure below It gives us the Ranking of each product, just for the sake of demonstration it shows the data as follows; profitability

After that we place this sub report within their product group header and conditionally supressed the ranking and only show which matched the group. As follows;

whileprintingrecords;
shared stringvar Product;
{Command.Product} <> Product

With the above mechanism the report was completed. The above information is for demo only feel free to ask any question on that.

By Kaleem
Visit here to see my portfolio

Crystal Reports Consultant and Developer
Datamart.org