Visual Builder

Get Involved. Join the Conversation.

Topic

    Vinod Kumar Kv
    How to use aggregate and groupby clause for reporting in...
    Topic posted November 14, 2018 by Vinod Kumar KvBronze Medal: 1,250+ Points, tagged Business Objects, Database 
    112 Views, 4 Comments
    Title:
    How to use aggregate and groupby clause for reporting in VBCS.
    Summary:
    How to use aggregate and groupby clause for reporting in VBCS.
    Content:

    How to use aggregate and group by clause for reporting in VBCS.

    Business Scenario:
    We have below mentioned objects in one to many relationship:

    Table A:

    AID (Primary Key)

    Name

    Type

    Comments

    1

    Test

    Business

     -

    2

     -

     -

     -

    Table B  (Contains list of all questions asked): AID references Table A primary Key

    BID (Primary Key)

    AID (Foreign Key)

    Question

    Title

    Comments

    10

    1

    Java

    Do you know Java

     -

    11

    1

    Driving

    Do you know driving

     -

    Table C (Contains answers for each of the questions asked by different users): BID references Table B primary key

    CID (Primary Key)

    BID (Foreign Key)

    Type

    User

    Comments

    21

    1

    Yes

    A

    -

    22

    1

    Yes

    B

    -

    23

    1

    No

    C

    -

    24

    1

    Yes

    D

    -


    Now we want to generate BAR chart report as shown below in VBCS:
    For question JAVA (Do you know java?)
    1) 3 Users answered YES
    2) 1 User answered NO

    See the below image for details.

    How do we use aggregate functions in VBCS to generate this report.

     

    Image:

    Comment

     

    • Shay Shmeltzer

      One approach could be :

      In table B add columns for the totals (for example total_yes & total_no).

      Then have a trigger on your table C business object that updates the totals in table B each time that a record is inserted into table C.

      You can use groovy code to do this - something like:

      def view1 = newView('tableB');
      while (view1.hasNext()) {
        record1 = view1.next();
        record1.totalno = {
        record1.totalno+1
        }.call();
      }

       

      Your chart will then be based on the field from table B.

       

    • Vinod Kumar Kv

      Hello Shay,

      In below code we have .call() statement. What will this .call() do?

      def view1 = newView('tableB');
      while (view1.hasNext()) {
        record1 = view1.next();
        record1.totalno = {
        record1.totalno+1
        }.call();
      }

      One more quick question can we use aggregate and group-by functions in groovy code in VBCS

      ex.: SELECT agent_type, agent_code, SUM (advance_amount) FROM orders GROUP BY agent_code;

      Thanks

    • Vinod Kumar Kv

      Hello,

      Please let us know how to make aggregate and group-by rest calls in VBCS as this has huge impact and dependency on reports we are generating. 

      Thanks

      • Shay Shmeltzer

        There isn't currently a way to do the summary SQL query through REST, the approach I showed above is the workaround for this.