Clarity

 View Only
Expand all | Collapse all

Jaspersorf parameter based on query filtered on currently logged user

  • 1.  Jaspersorf parameter based on query filtered on currently logged user

    Posted Aug 07, 2015 03:05 AM

    Hello,

     

    I have a custom report with parameter (resource_id). In Jaspersoft I created the parameter based on query, but the query returns all resources, is it possible to filter this query as it's in Clarity lookups, to let the query show only users which the reporter executer has rights on? In Clarity it's something like this: @WHERE:SECURITY:RESOURCE:RESOURCES.ID@, does exist something like this for Jasper?

     

    Thank you in advance,

    Martin



  • 2.  Re: Jaspersorf parameter based on query filtered on currently logged user
    Best Answer

    Posted Aug 10, 2015 07:42 AM

    It is. take a look at the code in the solution pack jaspersoft reports which come with 14.2.

     

    resources

     

    AND  ($P{resourceOBSUnitKey_1} IS NULL

          OR    

          r.resource_key IN (SELECT DISTINCT obsm.resource_key

                             FROM   dwh_res_obs_mapping obsm

                                    INNER JOIN dwh_cmn_obs_hierarchy obsh ON obsm.obs_unit_key = obsh.child_obs_unit_key

                             WHERE  obsh.parent_obs_unit_key = $P{resourceOBSUnitKey_1}))      

    AND  ((EXISTS (SELECT user_key

                   FROM   dwh_res_security

                   WHERE  global_view_right = 1

                   AND    user_name = $P{ppmUser}))

           OR

          (EXISTS (SELECT resource_key

                   FROM   dwh_res_security

                   WHERE  resource_key = r.resource_key

                   AND    user_name = $P{ppmUser})))      

    GROUP BY r.resource_key, r.resource_name,r.primary_role_key, r.primary_role, r.resource_manager_key, r.resource_manager

     

     

    Projects

     

    AND    ($P{projectOBSUnitKey_1} IS NULL

            OR    

            i.id IN (SELECT obsa.record_id

                     FROM   prj_obs_associations obsa, prj_obs_units_flat obsf

                     WHERE  obsa.unit_id = obsf.unit_id

                     AND    obsa.table_name  = 'SRM_PROJECTS'

                     AND    obsf.branch_unit_id = $P{projectOBSUnitKey_1}))

    AND    ((EXISTS (SELECT user_id

                     FROM   cmn_sec_assgnd_obj_perm_r_v

                     WHERE  object_type = 'RECORD'

                     AND    object_code = 'PRJ_PROJECT'

                     AND    permission_code = 'ProjectViewManagement'

                     AND    component_code = 'PRJ'

                     AND    user_name = $P{ppmUser}))

            OR

            (EXISTS (SELECT object_instance_id

                     FROM   cmn_sec_assgnd_obj_perm_v

                     WHERE  object_instance_id = i.id

                     AND    object_type = 'RECORD'

                     AND    object_code = 'PRJ_PROJECT'

                     AND    permission_code = 'ProjectViewManagement'

                     AND    component_code = 'PRJ'

                     AND    user_name = $P{ppmUser})))