SQL Query To Show All Report Schedule Parameters

[wcbutler]

Hello,

I have reports written that are emailed to a variety of folks on the campus I work on. They are written so that I have a query that pulls a parameter to grab a group out of the system it's reporting on and it that is used a a drop-down. Another field is an open field to type in the number of days back. Those are then used when scheduling and let me customize reports for a multi-tenancy system to send to a certain liaison.

So I have a need to report off the reporting system, and I have a report showing all the email schedules. The one piece of data I cannot find is what is included in those parameters. 

If there is a field that even says "13 down" on the parameter dropdown, I could easily report off that using a subquery.

I see a table called jireportjobparameter I would expect to see the information but it shows the job ID, parameter name, and it does show a parameter_value field but it's just reporting a size (example - (BLOB) 9 bytes). I also see a jireportunitinputcontrol that I'd expect to join to jiinputcontrol. jiinputcontrol does have a control_index but it seems to be 0, 1, or 2 where my parameter list is hundreds of items long so I assume that is the index number of the inputs on the report since I only normally have a few.

Just some pointing in the right direction would go far. I'm pretty good at reverse engineering databases (hence why I'm the Jasper reporting guy). Thanks!

[Mehak Rajkumar]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.
 

[anish.rai]

Thank you for your patience, we have not forgotten you. We need a little more time with our team of experts to come up with a solution. We will respond back with an answer as quickly as we can.

[Tirupathi Mangalarapu]

This is a unique requirement. We are checking with other teams if they can guide you in the right direction.  

[Tom C]

The content of jireportjobparameter.parameter_value field is a Java object that only the JRS class method can decipher, such as (with bytea 'escape' encoding):

25435500sr00java.util.ArrayListx201322231307a23500I00sizexp000000w000000t001t003x
 

Instead of attempting with SQL query to encode, you should use JRS REST call report job service to get the listed parameter values from the job definition:

 

GET http://<host>:<port>/jasperserver[-pro]/rest_v2/jobs/<jobID>/
 

From the return of the REST call, you should see the parameter information in the json job descriptor, something like:

 

    "source": {
        "reportUnitURI": "/public/TTC/Slack/20230602/Cascading_multi_select_topic",
        "parameters": {
            "parameterValues": {
                "Country_multi_select": [
                    "USA"
                ],
                "Cascading_name_single_select": [
                    "A & U Stalker Telecommunications, Inc"
                ],
                "Cascading_state_multi_select": [
                    "CA",
                    "OR"
                ]
            }
        }
    },
---------------------------------------------------------

For more information, please refer to "TIBCO JasperReports® Server REST API Reference" document.

 

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-rest-api-reference/v790/jobs-service#Viewing_a_Job_Definition