Clarity

  • 1.  Creating parameters for report that prompt for multi values

    Posted Feb 22, 2017 09:46 AM

    I am new to CPM and Jasper Studio. I created a report in Jasper studio, in the report sql I have the following parameter that should be prompting for multiple vales to select from. When I run the report there is no value. Here is my parameter:

    AND $X{IN, DWH_RES_OBS_MAPPING.OBS_UNIT, OBS_UNIT}

     

    full sql:

    <![CDATA[SELECT  DWH_RES_RESOURCE.RESOURCE_NAME ,    DWH_TME_SHEET.PERIOD_FINISH_DATE ,
                      DWH_RES_RESOURCE.RESOURCE_MANAGER ,    DWH_LKP_SCI_DEPARTMENT.SCI_DEPARTMENT,
                      DWH_TME_SHEET_LN.TIMESHEET_STATUS ,  sum(DWH_TME_ENTRY_FACTS.TIME_HOURS)
                      
                          
     FROM  DWH_TME_SHEET   DWH_TME_SHEET
     INNER JOIN  DWH_TME_SHEET_LN    DWH_TME_SHEET_LN  ON  ( DWH_TME_SHEET.TIMESHEET_KEY =  DWH_TME_SHEET_LN.TIMESHEET_KEY AND  DWH_TME_SHEET_LN.LANGUAGE_CODE ='en' )
     INNER JOIN   DWH_TME_ENTRY_FACTS  DWH_TME_ENTRY_FACTS ON  (  DWH_TME_SHEET.TIMESHEET_KEY = DWH_TME_ENTRY_FACTS.TIMESHEET_KEY )
     INNER JOIN  DWH_TME_PERIOD   DWH_TME_PERIOD  ON (DWH_TME_SHEET.TIME_PERIOD_KEY = DWH_TME_PERIOD.TIME_PERIOD_KEY )
    INNER JOIN  DWH_TME_ENTRY DWH_TME_ENTRY ON( DWH_TME_SHEET.TIMESHEET_KEY =DWH_TME_ENTRY. TIMESHEET_KEY)
    INNER JOIN DWH_INV_PROJECT  DWH_INV_PROJECT ON ( DWH_TME_ENTRY.INVESTMENT_KEY = DWH_INV_PROJECT.INVESTMENT_KEY )
     INNER JOIN DWH_LKP_SCI_DEPARTMENT  DWH_LKP_SCI_DEPARTMENT ON (DWH_INV_PROJECT.SCI_DEPARTMENT_KEY =DWH_LKP_SCI_DEPARTMENT.SCI_DEPARTMENT_KEY )

     

     INNER JOIN  DWH_RES_RESOURCE   DWH_RES_RESOURCE  ON ( DWH_TME_SHEET.RESOURCE_KEY= DWH_RES_RESOURCE.RESOURCE_KEY )  
     INNER JOIN   DWH_RES_RESOURCE_LN  DWH_RES_RESOURCE_LN  ON (DWH_RES_RESOURCE.RESOURCE_KEY = DWH_RES_RESOURCE_LN.RESOURCE_KEY AND DWH_RES_RESOURCE_LN.LANGUAGE_CODE='en')
     INNER JOIN  DWH_RES_OBS_MAPPING  DWH_RES_OBS_MAPPING  ON (DWH_RES_RESOURCE.RESOURCE_KEY = DWH_RES_OBS_MAPPING.RESOURCE_KEY)
     
     WHERE DWH_TME_SHEET.TIMESHEET_STATUS_KEY <5
     AND $X{IN,DWH_RES_OBS_MAPPING.OBS_TYPE_ID, OBS_TYPE_ID}
     AND $X{IN, DWH_RES_OBS_MAPPING.OBS_UNIT, OBS_UNIT}
     GROUP BY  DWH_LKP_SCI_DEPARTMENT.SCI_DEPARTMENT,DWH_TME_SHEET.PERIOD_FINISH_DATE ,
                           DWH_TME_ENTRY_FACTS.TIME_HOURS,DWH_TME_SHEET_LN.TIMESHEET_STATUS ,
                         DWH_RES_RESOURCE.RESOURCE_NAME ,                      DWH_RES_RESOURCE.RESOURCE_MANAGER
      ORDER BY DWH_TME_SHEET_LN.TIMESHEET_STATUS]]>



  • 2.  Re: Creating parameters for report that prompt for multi values

    Posted Feb 22, 2017 09:54 AM

    Hi Noel, 

     

    Is your question for the CA Agile Requirements Designer product? If not, please let me know what community you would like me to transfer your question to.

     

    Best regards,

    Taylor



  • 3.  Re: Creating parameters for report that prompt for multi values

    Posted Feb 22, 2017 11:09 AM

    Hi Taylor

    It's for the CAPPM community.



  • 4.  Re: Creating parameters for report that prompt for multi values

    Posted Feb 22, 2017 11:13 AM

    Hi Noel, 

     

    Thank you. Your question has been moved to CA PPM

     

    Best regards,

    Taylor



  • 5.  Re: Creating parameters for report that prompt for multi values

    Posted Feb 27, 2017 03:37 AM

    Hello,

     

    could you please specify where are no values? You mean that when you run the report in JasperStudio?

     

    I use following parameters in my custom report and it works as expected:

    and $X{IN, inv.id, program_id}

    and $X{IN, inv.manager_id, program_manager_id}

     

    Both parameters are in JasperStudio specified as on the picture below:

    In JasperStudion when running the report you have to input values manually, in JasperServer, when the report is deployed you have to specify input controls and for them query which provides the "parameters data"

     

    I hope this helps, when not please ask

     

    Regards,

    Martin