If u want to create a report without any input parameters, you can have a look at the Group Membership Profile report (Reports > Operational reports > Group Membership Profile)

That report does not have any input parameters in the stored procedure. You will not need to have any specific parameters then and only the first 12 parameters will be needed.

For Example:

XML Type

<Report layout=”Tabular”>
<StoredProcedure>
<InputParameters>
</InputParameters>
</StoredProcedure>
<ReturnColumns>
<ReturnColumn name=”Users.User ID” label=”report.LockedUsers.label.userLogin” position=”Table” filterColumn=”true” filterColumnName=”usr.usr_login” />
<ReturnColumn name=”Users.First Name” label=”report.LockedUsers.label.firstName” position=”Table” filterColumn=”true” filterColumnName=”usr.usr_first_name” />
<ReturnColumn name=”Users.Last Name” label=”report.LockedUsers.label.lastName” position=”Table” filterColumn=”true” filterColumnName=”usr.usr_last_name” />
<ReturnColumn name=”UsersUpdate” label=”report.LockedUsers.label.DateofAccLocked” position=”Table” filterColumn=”false” filterColumnName=”usr.usr_update” />
</ReturnColumns>
</Report>

XL_SP_GroupMembershipProfile

create or replace PROCEDURE XL_SP_GroupMembershipProfile (
csrresultset_inout IN OUT sys_refcursor,
intuserkey_in IN NUMBER,
strsortcolumn_in IN VARCHAR2,
strsortorder_in IN VARCHAR2,
intstartrow_in IN NUMBER,
intpagesize_in IN NUMBER,
intdocount_in IN NUMBER,
inttotalrows_out OUT NUMBER,
strfiltercolumnlist_in IN VARCHAR2,
strfiltercolumnvaluelist_in IN VARCHAR2,
strudfcolumnlist_in IN VARCHAR2,
strudfcolumnvaluelist_in IN VARCHAR2–,
– strgroupname_in IN VARCHAR2
)
AS
BEGIN
DECLARE
whereclause VARCHAR2 (8000);
select_stmt VARCHAR2 (8000);
strcolumnlist VARCHAR2 (4000);
strfromclause VARCHAR2 (4000);
strwhereclause VARCHAR2 (4000);
strorderbyclause VARCHAR2 (2000);
intsortdirection_in PLS_INTEGER;
grpfromclause VARCHAR2 (2000);
str_row EXCEPTION;
do_cnt EXCEPTION;
no_logged_in_user EXCEPTION;
PRAGMA EXCEPTION_INIT (str_row, -20001);
PRAGMA EXCEPTION_INIT (do_cnt, -20002);
PRAGMA EXCEPTION_INIT (no_logged_in_user, -20003);
BEGIN
– Throw exception if the start row or page size is either NULL or have
– values less than or equal to zero
IF ( intstartrow_in <= 0
OR intpagesize_in <= 0
OR intstartrow_in IS NULL
OR intpagesize_in IS NULL
)
THEN
RAISE str_row;
END IF;
– Throw exception if the intdocount_in parameter is NULL or has a value
– other than 0 and 1
IF intdocount_in NOT IN (0, 1, 2) OR intdocount_in IS NULL
THEN
RAISE do_cnt;
END IF;
– Throw exception if the intuserkey_in (logged in user) parameter is NULL
IF intuserkey_in IS NULL OR intuserkey_in <= 0
THEN
RAISE no_logged_in_user;
END IF;
whereclause := ‘ where ‘;
/* IF strgroupname_in IS NOT NULL THEN
WhereClause := whereclause
|| ‘ usg.ugp_key is NULL or usg.ugp_key in (select ugp.ugp_key from ugp where UPPER(ugp.ugp_name)
LIKE ‘
|| UPPER(”” ||strgroupname_in || ””) || ‘) AND ‘;
END IF;
*/
– Added to the where clause to implement the security model
–Login as an administrator (member of System Administrators group) and see the report results
– Login as a non admin and verify that he can see data for only those users whose organization he is an admin or a delegated admin of
/* whereclause := whereclause || ‘ usr.act_key IN (SELECT DISTINCT act2.act_key FROM ‘||
‘ act act2, aad, usg, ugp, usr usr5 ‘||
‘ WHERE act2.act_key = aad.act_key ‘||
‘ and aad.ugp_key = usg.ugp_key ‘||
‘ and ugp.ugp_key = usg.ugp_key’||
‘ and usg.usr_key = usr5.usr_key’||
‘ and usr5.usr_key = ‘||intuserkey_in||’)';
*/
whereclause := whereclause || ‘ usr.act_key IN (SELECT DISTINCT act.act_key FROM ‘|
|
‘ act , aad, usg, usr ‘||
‘ WHERE act.act_key = aad.act_key ‘||
‘ and aad.ugp_key = usg.ugp_key ‘||
‘ and usg.usr_key = usr.usr_key’||
‘ and usr.usr_key = ‘||intuserkey_in||’)';
/* Construct the select query by calling XL_SPG_GetPagingSql.
This is the main query for this stored procedure*/
strcolumnlist :=
‘GroupMem.usergcount MembInNoOfGrps ,count(GroupMem.usrkey) NoOfUsers’;
–Create GroupMem alias table containing user key and his group member ship count
strfromclause :=
‘ (select usr.usr_key usrkey, count(usg.ugp_key) usergcount ‘
|| ‘from usr left outer join usg on usr.usr_key = usg.usr_key ‘ || whereclause
|| ‘group by usr.usr_key) GroupMem ‘;

strwhereclause := ‘ 1=1 group by GroupMem.usergcount ‘;
strorderbyclause := ‘ order by GroupMem.usergcount ‘;
– construct select statement to count the total number of rows out
select_stmt :=
‘select ‘
|| strcolumnlist
|| ‘ from ‘
|| strfromclause
|| ‘ where ‘
|| strwhereclause ;

– Perform the count query and store the result in inttotalrows_out
inttotalrows_out := 0;

IF intdocount_in IN (1, 2)
THEN
EXECUTE IMMEDIATE ‘select count(*) from (‘ || select_stmt
|| ‘)’
INTO inttotalrows_out;

– UI needs the SP to return result set always. The following is returned
– when the indocount is 2 which does not return any result set but count
IF intdocount_in = 2
THEN
select_stmt := ‘SELECT ”dummy” FROM dual’;

OPEN csrresultset_inout
FOR select_stmt;
END IF;
END IF;
– If intdocount_in is 2, UI just wants to get the totalrows to give
– the warning to users if the result set exceeds the limit set by
– UI. When ntdocount_in is 2, the following block won’t be executed.
IF intdocount_in IN (0, 1)
THEN
IF strsortcolumn_in IS NULL
THEN
strorderbyclause := ‘ GroupMem.usergcount’;
ELSE
strorderbyclause := strsortcolumn_in || ‘ , GroupMem.usergcount ‘ ;
END IF;

IF strsortorder_in = ‘DESC’
THEN
intsortdirection_in := 0;
ELSE
intsortdirection_in := 1;
END IF;
xl_spg_getpagingsql (strcolumnlist,
strfromclause,
strwhereclause,
strorderbyclause,
intsortdirection_in,
intstartrow_in,
intpagesize_in,
select_stmt
);

OPEN csrresultset_inout
FOR select_stmt;
END IF;
–Exception Handling
EXCEPTION
WHEN str_row
THEN
raise_application_error
(SQLCODE,
‘Start Row/Page Size cannot be NULL OR less than or equal to zero ‘
);
WHEN do_cnt
THEN
raise_application_error (SQLCODE, ‘Do Count must be 0, 1 or 2. ‘);
WHEN no_logged_in_user
THEN
raise_application_error
(SQLCODE,
‘Logged-in User Key cannot be NULL OR less than or equal to zero ‘
);
END;
END XL_SP_GroupMembershipProfile;