Hourly Sales Report Issue

Hi Everyone,

i'm Developing Hour wise sales sales report in which i have to show sales on hour basis.

for this, i took Integer as a data item for iteration IF Number IN [1..24] 

to make as a hour like 00:00 -00:59 to 23:00 to 23:59.But i'm stuck and fail to how to get time interval and how to apply time as a filter.


>>i'm taking integer as data time

>>start time and end time will come from Statistic time setup table

>>and calculation will come from trans header.


Please help me.Thanks 


  • If I'm understand the requirements what might be easiest is to record the hour of the sale as an Int against whatever table it is you're using then you can use that column to perform your filter/grouping against.

    The hour of a time can be extracted as an Int from a Time/DateTime by doing the following:

    // From your DateTime of when the sale occurred.
    myDateTime := CURRENTDATETIME();

    // Firstly, get the time
    myTime := DT2TIME(myDateTime);

    // Get the miliseconds from the time:
    myResultingInt := (myTime - 000000T);

    // Get the minutes  
    myResultingInt := (myResultingInt DIV 1000);

    // Seconds:
    myResultingInt := (myResultingInt DIV 60);

    // Finally, hours:
    myResultingInt := (myResultingInt DIV 60);

    // As a single line:
    hourOfSaleAsAnInteger := ((((DT2TIME(myDateTime) - 000000T) DIV 1000) DIV 60) DIV 60);


    If I've misunderstood the requirements do reply and I'll update accordingly.
    - Josh
  • In reply to Josh:

    Dear Josh Thanks for reply.

    My query is  to get time from (Statistic Time setup) Table which having start time and end time.and data is come from transaction header table which also have (TIME) field.so i have to show sales according to hour basis take a look of below line.

    Time Interval No. of Sales Transactions No. of Items Gross Amount Sales Type Average No. of Items Average Amount Net Sales

    12:00:00 AM - 12:59:59 AM 4 23 14,620.75 THE TERRACE 5.75 3,655.19 12,550.00

    in above line you can see in between 12Am to 1 Am..-23 transaction made.

    So right now i facing problem how to get start time and end time and how i can display it hour wise.


  • In reply to shyam pandey:

    i cant able to setrange on start time and end time 

    TimeInterval := FORMAT(StatisticsTimeSetup."Start Time") + ' - ' + FORMAT(StatisticsTimeSetup."End Time");

    i war tried to do it by evaluating as string.But result is same.


  • In reply to shyam pandey:

    Are you looking for the 'Duration'?

    'TimeElapsed' is of datatype 'Duration' Start/EndTime are of type 'DateTime'

    StartTime := CREATEDATETIME(191015D, 090000T);
    EndTime := CREATEDATETIME(191015D, 094500T);
    TimeElapsed := EndTime - StartTime;

    // Returns '45 Minutes 5 seconds'


    Or something more like?

    MyTransactionRec.SETFILTER("Transaction Time", '>=%1&<=%2', Setup."Start Time", Setup.EndTime);
    IF MyTransactionRec.FINDSET THEN
    NumberOfSalesInPeriod := MyTransactionRec.COUNT; 


  • In reply to Josh:

    Yes something like this Yes

    "Or something more like?

    MyTransactionRec.SETFILTER("Transaction Time", '>=%1&<=%2', Setup."Start Time", Setup.EndTime);
    IF MyTransactionRec.FINDSET THEN
    NumberOfSalesInPeriod := MyTransactionRec.COUNT;"
  • In reply to shyam pandey:

    I wrote the same logic 


          TransHeader.SETFILTER(TransHeader.Time,'>=%1',RecStatisticsTimeSetup."Start Time");

          TransHeader.SETFILTER(TransHeader.Time,'>=%1',RecStatisticsTimeSetup."End Time");

    But i got blank value.I think some white space is passed with time field so setrange returnin blank value.

  • In reply to shyam pandey:

    How about ?

    TransHeader.SETRANGE(Time, "Start Time", "End Time");


    TransHeader.SETFILTER(Time, '%1..%2', "Start Time", "End Time");

  • In reply to Erik P. Ernst:

    Erick Sir thanks for reply 

    Actully Start time and End time value is like this

    12:00:00-12:59:59 in Statistic time setup table 

    but in Transaction header time value is like this


    so sterange return nothing in result.

    please suggest me what to do in this scenario.

  • In reply to shyam pandey:

    Yes but did you use .. ? In you example you show it with a - (dash).

  • In reply to Erik P. Ernst:

    Hi All,


    Sorry for Delay replyEmbarrassed

    By taking Statistic time management table in table relation with hospitality type table my report fetching data time wise.

    Here is the code example.



    TransHeader.SETCURRENTKEY("Transaction Type","Entry Status",TransHeader.Date);

    TransHeader.SETFILTER("Entry Status",'%1|%2',TransHeader."Entry Status"::" ",TransHeader."Entry Status"::Posted);


    IF StoreFilter <> '' THEN

      TransHeader.SETRANGE("Store No.",StoreFilter);

    TransHeader.SETRANGE(TransHeader."Sales Type","Hospitality Type"."Sales Type");

    IF TransHeader.FIND('-') THEN 


      IF (TransHeader.Time >= "Start Time") AND (TransHeader.Time <= "End Time") THEN


        Counter += 1;

        TimeInterval := FORMAT("Start Time")+'-'+ FORMAT("End Time");

        GrossAmount += TransHeader."Gross Amount";

        NoOfItems += HourlySalesTemp."No. of Items" + TransHeader."No. of Items";

        NetAmount +=TransHeader."Net Amount";

        //GrossAmount += TransHeader."Gross Amount"+TransHeader."Income/Exp. Amount";>>Along with Income

        //NetAmount +=TransHeader."Net Amount"+TransHeader."Income/Exp. Amount";>>Along with Income


        RecTransSalesEntry.SETRANGE(RecTransSalesEntry."Store No.",TransHeader."Store No.");

        RecTransSalesEntry.SETRANGE(RecTransSalesEntry."POS Terminal No.",TransHeader."POS Terminal No.");

        RecTransSalesEntry.SETRANGE(RecTransSalesEntry."Transaction No.",TransHeader."Transaction No.");

        IF RecTransSalesEntry.FINDFIRST THEN


          GctAmt += RecTransSalesEntry."VAT Amount";

        UNTIL RecTransSalesEntry.NEXT=0;



        TimeInterval := FORMAT("Start Time")+'-'+ FORMAT("End Time");

    UNTIL TransHeader.NEXT = 0;

    TimeInterval := FORMAT("Start Time")+'-'+ FORMAT("End Time");



    Thanks every one for the help.it really helps me to build the logic.