[An error has occurred. (6632) - SQL validation of stored procedure in 4.5 breaks all report queries?]

Great !

[Call mySQL function from iReport Designer's "Report Query window.....]

Robi,

 

How did you solve this?

Regards Ruud

[Editable reports with write back functionality]

 

We created on top of jaspersoft a solution to edit reports and write back data to the database.

For this we used the existing javascript libraries within jaspersoft prototype and jquery and database procedures/functions.

The report behaves in 2 modes: Report or Input.

User can see the report as is, and (if authorised) can make changes to certain data. see attachment images.

This functionality we use amongst others for planning and maintenance of data and settings.

Ones you start with it the possibilities are almost endless.

 

Main Benefit:

No need to create separate data maintenance modules.

Report layout and Input layout are the same.

User gets instant feedback.

Organisation can do with less spreadsheets in order to complete "incomplete" reports.

If there is interest for this we can add the sample report and code as well.

Best Regards

Ruud Heemskerk

Company: Ordina

Jaspersoft Partner in the Netherlands.

 

 

 

 

[How to pass cascading inputcontrol in URL]

Thanks, that works indeed.

[Use parameter(s) in inputcontrol-query which are no inputcontrol in the report]

 

 

 

 

Case:

 

In resourcefolder we defined following inputcontrols:

• DEPARTMENT_OPTIONAL  -> parameter: $P{DEPARTMENT_OPTIONAL}

• DEPARTMENT_MANDATORY  -> parameter: $P{DEPARTMENT_ MANDATORY}

• SUBDEPARTMENT  -> parameter: $P{SUBDEPARTMENT}

 

 

Query for DEPARTMENT_OPTIONAL – value DEPARTMENT_CODE

SELECT

  DEPARTMENT_CODE

, DEPARTMENT_NAME

FROM ORGANISATION

 

Query for SUBDEPARTMENT – value SUBDEPARTMENT_CODE

SELECT

  SUBDEPARTMENT_CODE

, SUBDEPARTMENT_NAME

FROM ORGANISATION

WHERE 1=1

AND CASE WHEN $P{DEPARTMENT_OPTIONAL} IS NOT NULL THEN DEPARTMENT_CODE=$P{DEPARTMENT_OPTIONAL} ELSE 1=1 END

 

We would like to include the following:

AND CASE WHEN $P{DEPARTMENT_MANDATORY} IS NOT NULL THEN DEPARTMENT_CODE = $P{DEPARTMENT_MANDATORY} ELSE 1=1 END

 

Main Problem:

If the parameter in the query is not one of the inputcontrols on the report then the query does nothing (the inputcontrol is completely empty)

 

For this we have to create the following set of inputcontrols:

• SUBDEPARTMENT_ DEPARTMENT_OPTIONAL

• SUBDEPARTMENT_ DEPARTMENT_MANDATORY

 

For usage in combination on report A:

• • DEPARTMENT_OPTIONAL

  • SUBDEPARTMENT_ DEPARTMENT_OPTIONAL

 

For usage in combination on report B:

1.       DEPARTMENT_ MANDATORY

2.       SUBDEPARTMENT_ DEPARTMENT_ MANDATORY

 

Bigger problem:

• We have sometimes reports with 5+ parameters/inputcontrols depending on each other, however these inputcontrols are in mixed usage for different reports

 

Due to the main problem we have to make all kind of inputcontrols for all kind of combinations.

 

Our request:

Allow usage of parameters in the inputcontrol queries which are not used as an inputcontrol on the report. (the parameter has to be defined as parameter on the report)

 

In that  case we can do:

Inputcontrols

1.       DEPARTMENT_MANDATORY

2.       SUBDEPARTMENT_OPTIONAL

3.       EMPLOYEE_MANDATORY

 

Query for EMPLOYEE_MANDATORY – value EMPLOYEE_CODE

SELECT

  EMPLOYEE_CODE

, EMPLOYEE_NAME

FROM ORGANISATION

WHERE 1=1

AND CASE WHEN $P{DEPARTMENT_OPTIONAL} IS NOT NULL THEN DEPARTMENT_CODE=$P{DEPARTMENT_OPTIONAL} ELSE 1=1 END

AND CASE WHEN $P{DEPARTMENT_MANDATORY} IS NOT NULL THEN DEPARTMENT_CODE = $P{DEPARTMENT_MANDATORY} ELSE 1=1 END

AND CASE WHEN $P{SUBDEPARTMENT_OPTIONAL} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_OPTIONAL} ELSE 1=1 END

AND CASE WHEN $P{SUBDEPARTMENT_MANDATORY} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_MANDATORY} ELSE 1=1 END

 

 Best regards,

Ruud Heemskerk

 

 

[How to pass cascading inputcontrol in URL]

Question: How to pass cascading inputcontrol in URL

Suppose parameter $P{CITY}

Normal value for CITY can be passed in URL like &CITY=Amsterdam

What if cascading values of city are Amsterdam, Rotterdam.

How to pass these values in the url?

Best regards,

Ruud Heemskerk

[Text being truncated when exporting to MS Word]

Did you try the strech option? This might solve your problem.

Regards Ruud

[Parameters not working.]

You get nullpointer exception when you define an inputcontrol but forget to create the parameter within the report for this input control.

As stated: the name of the inputcontrol must be exactly the same (case sensitive) as the pareameter.

Suppose: Input control: DATE_UNTILL label Date Untill then you have to create a parameter: DATE_UNTILL

Regards Ruud