Forum Stats

  • 3,854,212 Users
  • 2,264,340 Discussions
  • 7,905,609 Comments

Discussions

create dynamic pivot with subtotals at each group

malikadeel
malikadeel Member Posts: 86 Red Ribbon
edited Oct 26, 2019 1:43AM in APEX Discussions

I have below Sample data from database to show as pivot with subtotal on each group

 

Balance TypeService TypeDvisionOUBalance
Past DueService 1Division 1Ou 110
Past DueService 1Division 1Ou 220
Past DueService 2Division 1Ou 130
Past DueService 2Division 2Ou 240
CurrentService 1Division 1Ou 150
CurrentService 2Division 1Ou 260
CurrentService 2Division 2Ou 170
CurrentService 2Division 2Ou 270

Expected Output in PLSQL Dynamic Content

      

Service 1Service 1 TotalService 2Service 2 TotalGrand Total
Division 1Division 1 TotalDivision 1Division 1 TotalDivision 2Division 2 Total
Balance TypeOu1Ou2Ou1Ou2Ou1Ou2
Current50505060607070140200250
Past Due102030303030404070100
Grand Total6020808030609070110180270350

I want to create PLsql Dynamic Content to display a html report using htp.p with css or javascript . Also if possible i want some collapsible groups at each level.

malikadeelMahmoud_Rabietimmy3780Veerendra Patil
«134

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,972 Gold Crown
    edited May 3, 2019 4:31PM

    Why not use a standard report with a custom layout?  Fac586 has talked about this MANY times here in the forum, you can do a search and see if it meets your requirements..

    Thank you,

    Tony Miller

    Los Alamos, NM

  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited May 3, 2019 4:35PM

    Can you please elaborate what you mean by custom layout as i think there IR and Pivot option in Action Menu but there i can't use grouping and subtotals. can you please share a little example or mock up 

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,219 Red Diamond
    edited May 3, 2019 5:15PM
    2776393 wrote:

    Update your forum profile with a recognisable username instead of "2776393".

    When you ask a question, always include the information detailed in these guidelines so we can provide version-, environment-, and application-specific advice.

    I have below Sample data from database to show as pivot with subtotal on each groupBalance TypeService TypeDvisionOUBalancePast DueService 1Division 1Ou 110Past DueService 1Division 1Ou 220Past DueService 2Division 1Ou 130Past DueService 2Division 2Ou 240CurrentService 1Division 1Ou 150CurrentService 2Division 1Ou 260CurrentService 2Division 2Ou 170CurrentService 2Division 2Ou 270

    Sample data should be posted in the form of DDL/DML statements or CTE selects: How do I ask a question on the forums?

    Are we to assume from the use of "dynamic" in the subject line that the number of services, divisions, and OUs is variable? Are these defined in reference data tables or as dimensions in a data warehouse? If so, please post the relevant table definitions.

    Expected Output in PLSQL Dynamic ContentService 1Service 1 TotalService 2Service 2 TotalGrand TotalDivision 1Division 1 TotalDivision 1Division 1 TotalDivision 2Division 2 TotalBalance TypeOu1Ou2Ou1Ou2Ou1Ou2Current50505060607070140200250Past Due102030303030404070100Grand Total6020808030609070110180270350I want to create PLsql Dynamic Content to display a html report using htp.p with css or javascript . Also if possible i want some collapsible groups at each level.

    Why the obsession with wholly unnecessary PL/SQL Dynamic Content regions? APEX development is much more effective without them.

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited May 3, 2019 5:31PM
    2776393 wrote:I want to create PLsql Dynamic Content to display a html report using htp.p with css or javascript .

    no you don't.

    First off, that code will be hard to maintain.

    Second - you would be doing nothing more than "reinventing the wheel". -- specifically - a Region Plugin

    Because of the dynamic requirement, you do not want to do the pivot within the database.

    You want to solve the dynamic-ness with JavaScript and CSS, not PL/SQL.

    My $0.02

    MK

  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited May 3, 2019 6:02PM

    Mike Kutz so can you provide the Solution actually

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited May 6, 2019 5:17PM

    no.  My answer comes from my experience of working with various forms of HTML based applications ( PERL::CGI, JSP, APEX, etc.)

    My JavaScript capabilities is only slightly better than my FORTRAN capabilities.  I would do you no good.

    If I did provide a solution, I would post it on http://apex.world for the everyone else also.

    If this is "work related", tell your boss you need someone with the required JS/CSS skill set.

    We can help turn that into a Plug-In.

    If this is "for fun", find some friends with the require JS/CSS skill set.

    My $0.02

    MK

  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited May 7, 2019 8:36AM

    @Mike Kutz Really Appreciate your answer and Work Experience You have , i think this Layout is Globally required by most of Apex developers as Apex itself has some limited pivot capability. So if you provide a Mock-up on http://apex.world/ as Pseudo Code or Real time JS/CSS code so every one get any Idea or solution to Come up with the solution. However this plugin is already developed here http://www.apexsmartpivot.com/ but its paid and costly to afford myself. Thanks Adeel 

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,219 Red Diamond
    edited May 7, 2019 5:34PM

    As Tony says this is demonstrably possible using the classic report/custom template technique described here: Matrix report

    The multi-level headers rather complicate things, but this can be resolved by generating the headers as report rows.

    The example demonstrates the ability to dynamically handle a variable number of data points and grouping levels. This is restricted by the 4000 byte limit imposed by using listagg in the construction of the header rows. That could be overcome by using xmlagg instead, but it's pretty close to the practical layout and usability limits of the report format anyway.

    Mahmoud_RabieVeerendra Patil
  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited May 8, 2019 3:29PM

    @fac586 Thanks for Update as i have seen the both links https://apex.oracle.com/pls/apex/f?p=APEX_50_EXAMPLES:DYN_PIVOT_MULTI_HDR:0:::::  this looks as per my requirements. Can you please share the source page of this application so i can see this as it looks like fulfilling my desired design

  • malikadeel
    malikadeel Member Posts: 86 Red Ribbon
    edited May 9, 2019 9:06AM