This discussion is archived
5 Replies Latest reply: Feb 6, 2013 11:42 AM by fac586 RSS

APEX Interactive Report - sum multiple columns table1 X with table2 value

826436 Newbie
Currently Being Moderated
I have a challenge in oracle apex, were i want to sum multiple columns to give 3 extra rows namely points, Score, %score. There are more columns but chose only few for now.
Below is an example structure of my data:

Town | Sector | Outside| Inside |Available|Price
Roy-----Formal----0----------0----------1------0
Kobus Formal--0 ---------0--------- 1------0
Wika ---Formal----0----------0--------- 1------0
Mevo----Formal----1----------1---------1------0
Hoch----Formal----1----------1---------1-------1

Points--------------2----------2----------5-------1
Score-------------10---------10---------10------10
%score-----------20---------20---------50------10

Each column has a constant weighting (which serves as a factor and it can change depending on the areas) in this case the weighting for this areas are the ones in the first row for the sector Formal:

Sector |Outside| Inside |Available|Price
Formal----1----------1 ----------1-----1
Informal--1----------0 ----------2-----1

I tried using the aggregate sum function in apex but it wont work since i need the factor in the other table. Which is where my challenge started

To compute the rows below the report:

points = sum per column * weighting factor per column
Score = sum of no of shops visited (in this case its 5) * weighting factor per column
% score = points/Score * 100

The report should display as described above. With the new computed rows below.

I kindly ask anyone to assist me with this challenge as i tried searchin on the internet for solutions but havent come across any.

Thanks a lot for your support in advance!!
  • 1. Re: APEX Interactive Report - sum multiple columns table1 X with table2 value
    fac586 Guru
    Currently Being Moderated
    >

    Please update your forum profile with a real handle instead of "823433".
    I have a challenge in oracle apex, were i want to sum multiple columns to give 3 extra rows namely points, Score, %score. There are more columns but chose only few for now.
    Below is an example structure of my data:
    Use <tt>\
    ...\
    </tt> tags as described in the FAQ when posting code and sample data and layouts. Please edit the OP and reformat the example using <tt>\
    ...\
    </tt> tags so it's clear.
    Town | Sector | Outside| Inside |Available|Price
    Roy-----Formal----0----------0----------1------0
    Kobus --Formal----0 ---------0--------- 1------0
    Wika ---Formal----0----------0--------- 1------0
    Mevo----Formal----1----------1---------1------0
    Hoch----Formal----1----------1---------1-------1
    
    Points--------------2----------2----------5-------1 
    Score-------------10---------10---------10------10
    %score-----------20---------20---------50------10
    Each column has a constant weighting (which serves as a factor and it can change depending on the areas) in this case the weighting for this areas are the ones in the first row for the sector Formal:
    Sector |Outside| Inside |Available|Price
    Formal----1----------1 ----------1-----1
    Informal--1----------0 ----------2-----1
    I tried using the aggregate sum function in apex but it wont work since i need the factor in the other table. Which is where my challenge started

    To compute the rows below the report:

    points = sum per column * weighting factor per column
    Score = sum of no of shops visited (in this case its 5) * weighting factor per column
    % score = points/Score * 100

    The report should display as described above. With the new computed rows below.

    I kindly ask anyone to assist me with this challenge as i tried searchin on the internet for solutions but havent come across any.
    How critical to the solution is using an interactive report? It would be much easier to do this in a standard report where you've complete control over the query SQL and the report structure.

    -----

    When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

    <li>Full APEX version
    <li>Full DB/version/edition/host OS
    <li>Web server architecture (EPG, OHS or APEX listener/host OS)
    <li>Browser(s) and version(s) used
    <li>Theme
    <li>Template(s)
    <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

    With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
  • 2. Re: APEX Interactive Report - sum multiple columns table1 X with table2 value
    826436 Newbie
    Currently Being Moderated
    Thank you very much for your prompt response.

    It will be fine if i can well have the output in a standard report. Kindly guide me how to achieve that.

    Regards,
    Sharen
  • 3. Re: APEX Interactive Report - sum multiple columns table1 X with table2 value
    fac586 Guru
    Currently Being Moderated
    823433 wrote:
    Thank you very much for your prompt response.

    It will be fine if i can well have the output in a standard report. Kindly guide me how to achieve that.
    See above. This is your problem, so you do the donkey work. Reproduce the basic report on apex.oracle.com by creating the required database objects and sample data. When you reach the point where you get stuck, post guest developer credentials for the workspace here and we'll take a look.
  • 4. Re: APEX Interactive Report - sum multiple columns table1 X with table2 value
    826436 Newbie
    Currently Being Moderated
    Hi,

    Thanks for you reply, The moment you told me to update my profile i did it, its only that i needed to sign out and sign in thats when the changes took effect. Any way thanks alot for you interest in assissting me.

    Please ignore this post as i already find the solution.

    Thanks,
  • 5. Re: APEX Interactive Report - sum multiple columns table1 X with table2 value
    fac586 Guru
    Currently Being Moderated
    Sharen wrote:

    Please ignore this post as i already find the solution.
    Which is?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points