[Can LoggedInUsername be used to populate a query]

I found my problem.  The input control specified $P{LoggedInUsername} as the VALUE column.   

I changed it to UNIT_IDENTIFICATION_CODE and the parameter display now displays the values from the table.

 

[Stored Procedure to populate parameter list. Is that possible?]

I created the following procedure to populate the parameter list for a report.  It returns a SQL error.  Can stored procedures be used to populate the  Input Control/query ?

CREATE OR REPLACE PROCEDURE owsadhoc.adhoc_testproc(p_LoggedInUsername in OWSADHOC.ADHOC_PLANNER_RPT_UIC_LIST.jasper_account%TYPE)
AS
  cur SYS_REFCURSOR;
bEGIN
    OPEN cur FOR select 
                    UNIT_IDENTIFICATION_CODE
                    FROM (SELECT 'ALL TYPES' UNIT_IDENTIFICATION_CODE
                          FROM DUAL
                          UNION ALL
                          SELECT *
                          FROM (SELECT DISTINCT
                                  uic AS UNIT_IDENTIFICATION_CODE
                                from owsADHOC.adhoc_planner_rpt_uic_list
                                where JASPER_ACCOUNT = p_LoggedInUsername
                                ORDER BY UIC)
                                );
    DBMS_SQL.RETURN_RESULT(cur);
END;
/

 

TEST IN ORACLE WORKED

EXEC owsadhoc.adhoc_testproc('MOORE_MARIAN');

 

I tried adding this to the jaspersoft query 

EXEC owsadhoc.adhoc_testproc('$P{LoggedInUsername})

And I got a sql error.  

 

 

[Can LoggedInUsername be used to populate a query]

My report has a number of parameters, the last of which is PLANNER_AUIC_LIST to be used in this portion of the query's where clause
    $X{IN, lurn2.auic, PLANNER_AUIC_LIST} 

I am trying to populate the PLANNER_AUIC_LIST collection by this query
select 
UNIT_IDENTIFICATION_CODE
FROM (SELECT *
      FROM (SELECT DISTINCT
              uic AS UNIT_IDENTIFICATION_CODE
            from owsADHOC.adhoc_planner_rpt_uic_list
            where JASPER_ACCOUNT = $P{LoggedInUsername}
            ORDER BY UIC)
            )

I verified that $P{LoggedInUsername} is returning the expected value by temporaily sticking the value in the header.  
The parameter PLANNER_AUIC_LIST is only displaying the final record returned by the query.  (I tried replacing all of the values and noticed that it only display one value--the last--no matter what is in the table.)

What am I missing?   Can LoggedInUsername not be used a a query parameter?

[How to limit values in selection]

I am trying to find a way to limit the selection in a collection.  For example, the manual has the following example: SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}

In my case, the collection field (here, myCountries) has over 13000 members.  I don't want the user to pick more than 50. 

First

I tried the following query

SELECT UNIT_IDENTIFICATION_CODE from

            (select

                q.*, rownum rn

            from  ( select

                        DOMAIN.UNIT_IDENTIFICATION_CODE

                    FROM OWS.LK_UIC_RUIC_NRA DOMAIN

                    ORDER BY DOMAIN.UNIT_IDENTIFICATION_CODE

                   ) q)

            where rn between p_startRow and p_endRow

 

This works in Oracle, but Jaspersoft highlights the field rownum in red.

Second

Usually, I can rewrite the query as a function or procedures.  However, the procedure returns a cursor.  Jaspersoft is choking on the cursor in my code:

SELECT  OWSADHOC.retrieve_UIC_list.f_rtrv_uic_cursor (1, 52) AS UNIT_IDENTIFICATION_CODE

    FROM DUAL;

Third:

I went back to my query and removed the rownum references and the parameter screen does display but—UNIT_IDENTIFICATION_CODE has over 13000 members.

Is there a way to (a) limit the number of values that the user selects from the list? Or (b) count the number of values that the user selects and kicks him out if he goes over my limit?

[Change Report Display upon export?]

Is it possible to change the format of a JasperStudio 6.2.1 report upon export?

I have a customer that wants the Report Title removed when he exports to the XLSX format. 

I changed the report title to a variable in hope that I could reset its value to NULL when an export was done.  However, I don't see any builtin functions that recognizes that the operator has requested an export.  Is it possible to do this with any kind of in-house written java function? 

[Brackets around $P!(UICFILTER2)) clause]

Just to answer my own question.  This is how the query ended up.

SELECT OWSADHOC.GENERATE_UIC_FILTER($P{LoggedInUsername},$P{ROLE_LIST},   'RESERVE_REPORTING_UNIT_CODE')  as UICFILTER2 FROM DUAL

 

The SQL in the report reads

AND $P!{UICFILTER2}

AND $P{ROLE_LIST} IS NOT NULL

I had to define ROLE_LIST AS

String.join(",", $P{LoggedInUserRoles})

[Brackets around $P!(UICFILTER2)) clause]

My first report query

select (pi.last_name || ', ' ||
        pi.first_name || ' ' ||
        pi.mid_initials) name,
       trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address,
       trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') ||
                          nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip,
       trim(ud.EMAIL_ADDRESS) Email_Address,
       pi.reserve_reporting_unit_code ruic
FROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssn
WHERE $X{IN, reserve_reporting_unit_code, RUIC_LIST}                             
ORDER BY name

However, RUIC_LIST must generated dynamically.  It will be different for every operator based on their role. 

This code SHOULD be the query that populates RUIC_LIST, but Jasperserver choked on the regexp_substr statement

select distinct uicf.uic          
        FROM OWSADHOC.ADHOC_UIC_FILTER UICF
                 INNER JOIN OWSADHOC.ADHOC_USER_ROLE  UROLE
                    ON UICF.ROLE_NAME = UROLE.ROLE_NAME
                 inner join OWSADHOC.ADHOC_USER_DATA uname
                    on uname.USER_ID = UROLE.USER_ID
        WHERE uname.JASPER_ACCOUNT = P{LoggedInUserName}
        and UROLE.ROLE_NAME in (SELECT NAME
                                    FROM (SELECT regexp_substr(LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL
                                            FROM   DUAL
                                            CONNECT BY LEVEL <= regexp_count(LoggedInUserRoles, ',') + 1)
                                ) 

Therefore, I created a function that would generate the entire WHERE clause in standard SQL format.
I changed the report query to

select (pi.last_name || ', ' ||
        pi.first_name || ' ' ||
        pi.mid_initials) name,
       trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address,
       trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') ||
                          nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip,
       trim(ud.EMAIL_ADDRESS) Email_Address,
       pi.reserve_reporting_unit_code ruic
FROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssn
WHERE $P!{UICFILTER)                            
ORDER BY name

 

The function definition is

CREATE OR REPLACE FUNCTION OWSADHOC.GENERATE_UIC_FILTER (P_LoggedInUserName IN owsadhoc.adhoc_user_data.jasper_account%TYPE, P_LoggedInUserRoles IN VARCHAR2, P_FIELDNAME IN VARCHAR2
) RETURN VARCHAR2  AS P_FILTER VARCHAR2(255);
/*****************************************************************************************
This procedures is used to generate the filter used on various adhoc jasper reports.
Version 0.00
Created by: Marian Moore
Date: 21 Oct 2019
Description: Initial Creation
*****************************************************************************************/
BEGIN
SELECT DISTINCT P_FIELDNAME ||' IN (' ||
                listagg ( chr(39) ||  uic || chr(39) , ',') within group (order by uic) || ')' 
into P_FILTER              
from ( select distinct uicf.uic          
        FROM OWSADHOC.ADHOC_UIC_FILTER UICF
                 INNER JOIN OWSADHOC.ADHOC_USER_ROLE  UROLE
                    ON UICF.ROLE_NAME = UROLE.ROLE_NAME
                 inner join OWSADHOC.ADHOC_USER_DATA uname
                    on uname.USER_ID = UROLE.USER_ID
        WHERE uname.JASPER_ACCOUNT = P_LoggedInUserName
        and UROLE.ROLE_NAME in (SELECT NAME
                                    FROM (SELECT regexp_substr(P_LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL
                                            FROM   DUAL
                                            CONNECT BY LEVEL <= regexp_count(P_LoggedInUserRoles, ',') + 1)
                                ) 
           
         )
      ;
RETURN P_FILTER;        

end  GENERATE_UIC_FILTER;
/

If I test this (using hard-coded values)
SELECT OWSADHOC.GENERATE_UIC_FILTER('MOORE_MARIAN', 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE', 'RESERVE_REPORTING_UNIT_CODE') AS uicfilter FROM DUAL;
/

The function returns
RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')

Great!  However, when I execute the new function via Jasper, the Jasper report is still crashing.  When I look at the error, the clause is surrounded by brackets for some inexplicable reason.  It reads

[RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')]

           

[Brackets around $P!(UICFILTER2)) clause]

My report SQL includes a clause

WHERE  ($P!{UICFILTER2}  )

where UCIFILTER2
is mapped to a query
SELECT domain.GENERATE_UIC_FILTER($P{LoggedInUsername}, 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE',  'RESERVE_REPORTING_UNIT_CODE')  as RSVUIC_QUERY2 FROM DUAL

When I execute the report, I am receiving an error.  I noticed that the where clause reads

WHERE ([RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')])

What did these brackets [] come from? 

Does Jaspersoft require the use of the $X{} format in the SQL statement? 

Note:I actually want that second parameter to be LoggedInUserRoles and a hard-coded list of roles.  However, I am afraid that I would still get these brackets crash the sql statement.
 

 

 

[Can Jaspersoft Studio Create a report that allows for user-entered text parameters?]

I am using TIBCO Jaspersoft® Studio - Visual Designer for JasperReports. 6.2.1

 

All of the reports that I've worked with earlier have either had no parameters (rare) or they have a query-generated parameter list from which the operator chooses one to many values.

Is there any way to give the opertor a blank entry line?  They want to enter the selection criteria as comma separated list of values.  

Meanwhile, I am also trying to verify that I can take that comman separated list of values, recreate it as an array and use it in $X{IN, field_name, array_name) statement.

However, first I need to find out if the input control can be a blank line for operator-entered-data.

 

[Saved Input Control entries -- where are they?]

When you save an input control selection, where is it saving the selection?  Is that location editable by the system administrator?  I have two reports that require selecting 134 choices out of a multi-select box that contains 1003 entries.  Is there an easier way for me to select those 134 entries and save them for the end-user? 

Is there a way to delete earlier saved selections  for the input control?  (For example, my earlier test selections)

 

[Difference between the two page_count variables?]

What is the difference between $V{PAGE_COUNT} and ${MASTER_PAGE_COUNT}?    

I was using CONCATENATE("PAGE ",TEXT($V{PAGE_NUMBER},"#")," OF ",TEXT($V{PAGE_COUNT},"##") ) on my report, but I noticed in another question/answer that MASTER_PAGE_COUNT is preferred.  <Help> doesn't describe the difference.

[How to modify query clauses based on parameter input?]

Thanks.  I wanted to check back in to say this works (with adjustment) for the simplest case.  So, I inserted the following line in the where clause of the sql:
 
and    WC.WORK_CENTER_ID = NVL2($P{WORK_CENTERS_CASCADED}, $P{WORK_CENTERS_CASCADED}, wc.work_center_id)

That works fine.   
The following runs too long so I think I will have to continue to work on it.  The original java program added the test on work_center_id only if the work_center_id parameter was provided.

and    ot.tracking_number in (select tracking_number
                                    from ows.historical_orders
                                    where order_status <> 'CANCEL'
                                    AND FISCAL_YEAR = $P{FY_FROM_2002_TO_TODAY_PLUS_5}
                                    and reserve_reporting_unit_code in (select reserve_reporting_unit_code
                                                                            from ows.work_center_ruc
                                                                              where WC.WORK_CENTER_ID = nvl2($P{work_center_id}, $P{work_center_id}, WC.WORK_CENTER_ID)
                                                                        )
                                 )

 

[How to modify query clauses based on parameter input?]

I am trying to modify a query based on parameter input and I am going in circles.  Can anyone help?

The user should be able to enter a value that can be used to add a clause to the existing sql query.  The clause would allow the query to be filtered by the entered value.

For example, they could leave local office parameter null, and all local offices would be selected OR
they could enter a value in the local office parameter and I would all 'AND LOCAL OFFICE = value' to the existing query

I tried  created a second parameter named filter_clause defined as  IF($P{localOffice} == null, "AND wc.localOffice like '%"," AND wc.localOffice= $P{localOffice} ") 
but it looks as if the value is not established until after all of the parameters are set.   I dropped both localOffice and filter_clause on the report so that I could check the result.  local_office was entered and has a value, but the filter_clause is printing the default string used when localOffice is null.

I also tried creating a variable with the same definition as my filter clause.  That is closer.  It has a value (so I assume variables are valued in between parameters and the sql code).  However, it doesn't look like I can include the $V{filter_clause} within the existing query. 

Are there other options?    Note.  there are two filters and this is the simplest one.  The other would add a 'field in (select stmt)' based on user input.

[Creating a tablular report template]

I will admit readily that I am a newbie.  Perhaps someone can tell me where I am going wrong.

I created a custom template to be used as we transition to the latest release of Jaspersoft.  I added the  "Table_CH" and "Table_TD" styles to the custom  template.  The "field" and "label" fields have a horizonal layout so that they spread out on the report evenly.  The "Field" is defined as having the Table_TD property; the "Label" field is defined as having the Table_CH property.  The custom template was created with the bands that we require: page header, column header, detail band,  and page footer. 

I find that when I use the new template, new fields do not adopt the Table_CH and Table_TD automatically when they are dropped into the column header and detail band.  Is there any way to accomplish this?

Product: JASPERSOFT Studio Professional 6.1.1