how to execute SQL queries against stored procedure resultset

[kmcnabb]

I have been trying out iReport 5 for only a few days, so my question may seem ridiculously simple minded to you veterans.  I am trying to develop a prototype report as a proof of concept to show that iReport could be a viable reporting tool for our company’s hosted solutions.

Our existing reporting is based on MS SQL stored procedures.  They work well and there is no particular reason not to utilize them.  My job is to determine if reports can be created without a lot of custom code or complex configuration using the stored procedures as the data source.

I followed the tips in the Ultimate Guide and on this forum for using the stored procedure as the report query.  It worked on first try using a JDBC connection.  I found that I can set the type of data source either to SQL or plsql.  Either way, the output of the stored procedure automatically populates the fields.  (I did not have to create fields myself matching the schema of the returned data.)  Also, if I go into the Report Query dialog I can click “First 100 rows” and also the Refresh command button and see the first 100 rows of data displayed.

So, I have a live data source and iReport is fetching data.  What I'd like to do now, though, is find a way to treat the result set of the stored procedure LIKE a database and run – preferably – ordinary SQL queries to slice and dice the resultset data.  For example, I would want to sum all values in Column A of the resultset where Column B = ‘something’.

I have not found anything in the documentation or on the Community postings that gives me a step by step process for doing that – creating report elements that run SQL queries against the output of the report query.  I realize that there would be other ways of slicing and dicing the source data, but SQL queries would require no JavaBeans or JXML learning curve.  I wrote Java and beans years ago, but I'm very rusty now.  Besides that, I am trying to demonstrate that reports can be developed with a minimum of custom code.

[djohnson53]

You need to learn Input Controls. They filter the result set from the SQL Query.  Thats it.

[kmcnabb]

Could you possibly be more specific?  If there is a straightforward way to make the result set output of the stored procedure work from that point onwards as the datasource in subreports, for example, surely the step-by-step process can be described.  

Let's take a simple example.  Suppose I want to display only a single row of the resultset (data returned by the stored procedure)  using the Summary band of the Designer, and by selecting the record where Column A has a unique value. 

[ilona]

Input Controls are documented in the User Guide: http://community.jaspersoft.com/documentation/jasperreports-server-user-guide/using-input-controls

There are also a number of forum questions about input controls. This link will give you the list: http://community.jaspersoft.com/search?query=input%20controls

 

[kmcnabb]

With further study through the documentation, I have become convinced that there is no way to "pipe" the initial stored procedure returned data into a cached data source within the tool and then perform selective queries (or built in expressions that operate on entire records, not just fields) on the retrieved data image.  The documentation clearly states that fields are the only elements that can be used for mapping retrieved data from the query into a report.  I contend that all this talk about input controls is a bunch of hooey.  "Input controls" is not even a term in the index of the Ultimate Guide.

 

Let me step back.  The stored proced returns an immense amount of data that will have to be "sub queried", so to speak.. I do not want to hit the physical database more than once.  Based on what I am seeing, further actual querying of data in subreports reqires new queries against the physical data source and not against a result set returned to the report tool and cached in the report tool itself. 

 

I also do not want to create temporary tables in the physical database as intermediary datasources.  That would take up too much bandwidth.

 

Also, if someone cannot give me a straight answer and can only point me to more documents I have already read, red flags are up.

 

I am moving on to study other available tools.

[kmcnabb]

I appear to have found the answer in this post:

 

http://community.jaspersoft.com/questions/536421/how-pass-all-data-master-each-subreport

 

As I found after many hours of searching, it is NOT possible to run the report query only once and then pass the resultset of that query to each subreport for futher filtering in subreport sections.  The data source object is not rewindable.  It implements only the Next and Get methods.

 

I do sincerely appreciate the responses, which were well meaning.  Input controls have nothing to do with what I need to do, but "filtering" does.  If only the filtering could be cascaded from the data returned by a single, main report query, I would be in business.  Filtering can be applied only once per query, though.  If you specify a new query with new filtering in a subreport, there will be a new hit against the underlying database.

[drummer54]

If you happen to see this (I know it's been awhile), what reporting tool did you settle on? I've been playing with JasperReports and anything beyond a very simple report seems to require jumping through a lot of hoops, assuming you can figure it out or get a straight answer.

[isthismeee]

I'm even later to the party than @drummer54. :-D

 

I'm in a slightly similar situation as the OP, only my company is looking to get rid of stored procedures altogether and just put all logic in the report itself. My experience is with MS SSRS and @kmcnabb's findings about only being able to filter dataset data one time doesn't surprise me. That seems pretty standard, as it's this way in SSRS too. I'm not sure what Jaspersoft is capable of, but in SSRS you can write code, so that's an option, but not for the OP because his company wanted no complex code.

 

@drummer54, did you happen to find a software that you settled on? Your statement about Jaspersoft only being able to generate very simple reports is the vibe that I've been getting from most BI oriented reporting tools. Did you find one that was capable of BI oriented friendliness, as well as complex report designs? Thanks.