NCHASHIM

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.

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.

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?

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?

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?

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})

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')]

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.

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.

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)

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.

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) ) )

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.

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