Jump to content
Changes to the Jaspersoft community edition download ×

How to make a report with dynamic columns and using PIVOT in the query?


aperez_6

Recommended Posts

Hi all,

I need someone to show me an example to see how to interact with PIVOT queries regarding the pivoted field in the main query. Because this field happens to become X fields, according to its value.

EXAMPLE:

PARAMETERS:

PARAM1    java.lang.Integer   => 2PARAM2   java.lang.String     =>  "2,3,4,5,6"[/code]

MAIN QUERY:

SELECT A.*,              B.TOTAL_IMPORTFROM(       select *                    from (  SELECT  sd.NAME,                    sd.CODE,                    fi.GROUP,                    fi.PERCENT,                    fi.IDREG_CONCEPT                 FROM TABLE1 fi, TABLE2 sd,        WHERE fi.ID = $P{ PARAM1l}             AND fi.ID = sd.ID     )      pivot        ( count( IDREG_CONCEPT )        for IDREG_CONCEPT in (  $P!{ PARAM2}  )     )) A,(SELECT        sd.ID,                    sd.NAME,                    sd.CODE,                    fi.GROUP,                    fi.PERCENT,                    fi.IDREG_CONCEPT,                    SUM( IMPORT ) TOTAL_IMPORT        FROM TABLE1 fi, TABLE2 sd,        WHERE fi.ID = $P{ PARAM1l}             AND fi.ID = sd.IDgroup by sd.ID,                    sd.NAME,                    sd.CODE,                    fi.GROUP,                    fi.PERCENT,                    fi.IDREG_CONCEPTorder by sd.ID, sd.CODE, sd.GROUP, sd.PERCENT) BWHERE A.ID = B.ID  AND A.NAME = B.NAME  AND A.CODE = B.CODE  AND A.GROUP = B.GROUP  AND A.PERCENT = B.PERCENT  AND A.CONCEPT = B.CONCEPT;[/code]

RESULT:

ID                  NAME                             CODE                      GROUP             PERCENT           CONCEPT       2                 3                   4                   5                    6  
 

So, I would like to assign different descriptions for the field label .


Thank you,

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...