XS Advanced, Use SQLScript in Calculation Views

In your db/src folder, create a new folder called functions.

Create a function called SO_RANKING

A template is created for you with different sections

Remove the placeholder for the namespace. Paste the following code into the input section:

IP_FR_DATE DATE,
IP_TO_DATE DATE,
IP_REGION NVARCHAR(4)

Add the following as a returning table:

table ( COMPANY_NAME NVARCHAR(80),
                  REGION NVARCHAR(4),
                  SALES DECIMAL(18,2),
                  ORDERS INTEGER,
                  SALES_RANK INTEGER,
                  ORDER_RANK INTEGER  )  

Add the following code between BEGIN and END:

return

SELECT   company_name, region, sales, orders, sales_rank, order_rank from(  
  select
	 T2."COMPANYNAME" as COMPANY_NAME,
	 T3."REGION" as REGION,
	 sum(T1."NETAMOUNT") as SALES,
	 count(T0."SALESORDERID") as ORDERS,
	 dense_rank() over ( order by sum(T1."NETAMOUNT") desc ) as sales_rank,
	 dense_rank() over ( order by count(T0."SALESORDERID") desc ) as order_rank
	 from "SO.Header" T0
     inner join "SO.Item" T1 on T0."SALESORDERID" = T1."HEADER.SALESORDERID"
     inner join "MD.BusinessPartner" T2 on T0."PARTNER.PARTNERID" = T2."PARTNERID"
     inner join "MD.Addresses" T3 on T2."ADDRESSES.ADDRESSID" = T3."ADDRESSID"  
     where TO_DATE(T1."DELIVERYDATE") between :IP_FR_DATE and :IP_TO_DATE
       and T3."REGION" = :IP_REGION
     group by T2."COMPANYNAME", T3."REGION"
     order by sales_rank, T2."COMPANYNAME"
     )
     where sales_rank < 11;

Save and build the function. This is what it should look like

For more information about DENSE_RANK() and other function modules, visit the SAP HANA and System Views Reference