Cannot pass datetime params to a simple query and show results in a table

0

Running Jasper Studio 6.9.0.  For some reason, a report in Jasper Studio has a default dataset.  I've set that up with a datasource that points to an oracle database.  It works fine.

Only parameters and fields from this MAIN dataset can be dragged onto the report.  However, a table element can ONLY use a subdataset.  The table cannot see the report's main dataset.

All I want to do is capture two datetime parameters (start_time and end_time) when the report is executed and pass those parameters to a stored proc on the database that returns some data to a table.  That's it.

My query for my subdataset that I have to use in my table is this:

{call JJM_TESTPROC($P{start_time}, $P{end_time}, $P{ORACLE_REF_CURSOR})}

As asinine as extracting data from Oracle is, it works -- it can see all of the fields returned by this oracle stored procedure... I'm not getting errors, but I'm not getting data either.

However, start_time and end_time params do not exist in the subdataset.  They only exist in the main report dataset.  But I can't choose the main report dataset as the source for the table, and I can't drag parameters from the subdataset onto the report!  Should I put start and end time params in the main dataset AND the subdataset?  I've tried both of these options and neither works.  If the parameters are not in the main report, it does not ask me to choose dates when I first run the report, and yet those parameters cannot be seen by the subdataset when it is run.

This is getting ridiculous, as this is the most basic thing you could possibly want a report to do.  What am I missing here?

matthayllar's picture
Joined: Aug 12 2019 - 9:07am
Last seen: 1 month 5 days ago

1 Answer:

2

I answered my own question.....  the main thing you have to do here is LINK your subdataset parameters to the main report parameters.  So you need start_time, end_time, and ORACLE_REF_CURSOR in both the subdataset and main report parameter list.  ORACLE_REF_CURSOR gets created automatically in the subdataset when you select PLSQL as the language for your query there.  Copy/paste them in to your main report as well.  In order to link the parameters together, you have to set it up in the properties window of the TABLE that you have created and dropped onto your report design.  In the table properties, you have to select your subdataset from the dataset tab --- then to link, you scroll to the bottom of the properties windows and click the parameters button.  The "name" column is the name of the parameter in your subdataset that you want to link, and the "expression" contains the path to the parameter on the main report that you want to link to.  This is about as obfuscated and asinine as it gets (duplicating parameters?)  I mean, could we have thought a little more about the UI design??  I digress....  I was finally able to call an Oracle stored procedure using entered dates passed to the stored proc and receiving the data into fields that can then be used as table columns.

matthayllar's picture
Joined: Aug 12 2019 - 9:07am
Last seen: 1 month 5 days ago
Feedback