Calculated query with parameters in HANA/CrystalReports

Question

I'm having trouble trying to explain my necessity, so I'll describe the scenario.

---

Scenario:

• Product A has a maximum production of 125KG at a time.
• The operator received a production order of 1027,5KG of product A.
• The operator have to calculate how many rounds he'll have to manufacture and adjust the components quantity for each round.

We want to create a report where this calculations are already done and what we believe would be the first step, based on the values of this scenario, is to return something like this:

ROUND QUANTITY(KG)
1             125
2             125
3             125
4             125
5             125
6             125
7             125
8             125
9             27,5

After that, the recalculation of the components could be done with simple operations.

---

The problem is that we couldn't think of a way to get the desired return and we also couldn't think of a different way of achieving the said report.

All we could do is get the integer part of the division

SELECT FLOOR(1027.5/125) AS "TEST" FROM DUMMY

and the remainder

SELECT MOD(1027.5,125) AS "TEST" FROM DUMMY

We are using:

• SAP HANA SQL
• Crystal Reports
• SAP B1

Any help would be appreciated

Thanks in advance!

Answer 1

There are several ways to achieve want you described.

One way is to translate the requirement into a function that takes the two input parameter values and returns the table of production rounds.

This can look like this:

create or replace  function production_rounds(
            IN max_production_volume_per_round decimal (10, 2)
          , IN production_order_volume         decimal (10, 2)
          )
returns table   (
          production_round integer
        , production_volume decimal (10, 2))
as
begin
declare rounds_to_produce integer;
declare remainder_production_volume decimal (10, 2);

    rounds_to_produce := floor( :production_order_volume / :max_production_volume_per_round);
    remainder_production_volume := mod(:production_order_volume, :max_production_volume_per_round);

    return 
        select /* generate rows for all "max" rounds */
                 s.element_number                   as production_round
               , :max_production_volume_per_round   as production_volume
        from
            series_generate_integer
                (1, 1, :rounds_to_produce + 1) s
    UNION ALL
        select /* generate a row for the final row with the remainder */
                :rounds_to_produce + 1              as production_round
              , :remainder_production_volume        as production_volume
        from
            dummy
        where 
            :remainder_production_volume > 0.0;

end;

You can use this function just like any table - but with parameters:

select * from production_rounds (125 , 1027.5) ;

PRODUCTION_ROUND    PRODUCTION_VOLUME
1                   125              
2                   125              
3                   125              
4                   125              
5                   125              
6                   125              
7                   125              
8                   125              
9                   27.5             

The bit that probably needs explanation is SERIES_GENERATE_INTEGER. This is a HANA-specific built-in function that returns a number of records from a "series". Series here is a sequence of periods within a min and max limit and with a certain step-size between two adjacend periods. More on how this works can be found in the HANA reference documentation, but for now just say, this is the fastest way to generate a result set with X rows.

This series-generator is used to create all "full" production rounds. For the second part of the UNION ALL then creates just a single row by selecting from the built-in table DUMMY (DUAL in Oracle) which is guaranteed to only have a single record. Finally, this second part needs to be "disabled" if there actually is no remainder, which is done by the WHERE clause.