Reports generated from arbitrary SQL query

[nicholas]

I'm evaluating some reporting products, such as JasperServer and OpenReports.

 

OpenReports lacks some of the things we need, but has one feature JasperServer appears to miss: Being able to enter an arbitrary SQL query and have a simple table report generated.

 

Is this possible with JasperServer? I've been playing with the Professional version (we don't mind paying for features) but haven't figured out how to achieve this.

 

It looks like I could let users enter SQL queries, but I'd have to already have generated a suitable JRXML file?

 

I'd like to do this from within JasperServer so that we can use the scheduling, PDF, CSV etc. export features.

 

Regards,

 

Nick

[mdahlman]

Nick,

 

The best way to achieve this functionality in JasperServer would be to use the Ad Hoc report editor. Specifically, JasperServer Professional 2.1 introduced the "Ad Hoc Launcher". Before this existed you always needed to define a Topic (a .jrxml file) that contained the SQL query and use this topic for your Ad Hoc report. Now you can define your own query and pass this to the Ad Hoc editor. So you could write a page that let's a user type in a SQL query, and this would get passed to the Ad Hoc editor.

 

This example ships with JasperServer, and it should help out:

...jasperserver-pro-2.1samplescustomAdHoc

 

Regards,

Matt

[nicholas]

That sounds good, I'll check it out. Thanks!

 

---

 

I've built and deployed it (according to ant), but I'm afraid I don't see how to invoke this functionality.

 

I've read jasperserver-adhoc-custom.xml and applicationContext-adhoc-custom.xml for clues, but I'm still not sure.

 

I've started tomcat since I deployed the new code.

 

I've also grepped the jasperserver-pro-2.1 directory for anything which mentions customAdHoc in case this is documented somewhere.

 

I'm logged in as Administrator (jasperadmin).

Post edited by: nicholas, at: 2007/12/10 21:30

[nicholas]

I've found how to invoke it - http://localhost:8080/jasperserver-pro/browseDB/browseDB.html?action=pickDatasource

 

As far as I can tell, this example allows me to select which table to use, but not specify any other filter-type 'where' clauses.

 

I see I can read launchAdHoc() to learn how to allow the user to type in an arbitrary SQL query.

 

Thanks for your help.

[nicholas]

When I attempt to edit a custom Ad Hoc report, this error appears:

org.springframework.webflow.engine.NoMatchingTransitionException: No transition found on occurence of event 'customEdit' in state 'editAdhoc' of flow 'listReportsFlow' -- valid transitional criteria are array<TransitionCriteria>[[eventId = 'done']] -- likely programmer error, check the set of TransitionCriteria for this state

 

I believe I need to add something to listReportsFlow.xml in this part:

Code:

    <subflow-state id="editAdhoc" flow="adhocFlow">
      <attribute-mapper>
	<input-mapper>
	  <mapping source="requestParameters.reportUnit" target="adhocReport"/>
	</input-mapper>
      </attribute-mapper>
      <transition on="done" to="listReports"/>
    </subflow-state>

 

Probably something specific to the custom editor used, so that I knows where to transfer the user to browseDB/browseDB.html?action=displayTables ?

 

I'm using an evaluation license for jasperserver-pro-2.1

 

Thanks,

 

Nick

[mdahlman]

Hi Nick,

 

Excellent work tracking down the url. It's there in the sample code... but there is no nice doc to walk you through it yet (it's coming).

 

Can you give a little more background before "When I attempt to edit a custom Ad Hoc report..."? Were you already successful in creating, running, saving the report initially?

 

I'm not sure if the issue is really with listReportsFlow.xml. At first glance it appears to be a bug in the sample code. If I choose View->Repository I can reproduce your error when I click the Edit button. However if I start by choosing Manage->Repository then the Edit button for the relevant report works as expected. (Note that you must be logged in as an administrator to have the Manage menu.)

 

Is the behavior the same for you?

 

Regards,

Matt

[nicholas]

Yes, I can create new custom adhoc reports, and run them without problem.

 

It's only editing after saving that doesn't work - and thank you for noticing that it works ok from the 'repository admin' view. That helped me track down what was missing from listReportsFlow.xml .

 

This part:

Code:

    <subflow-state id="editAdhoc" flow="adhocFlow">
      <attribute-mapper>
	<input-mapper>
	  <mapping source="requestParameters.reportUnit" target="adhocReport"/>
	</input-mapper>
      </attribute-mapper>
      <transition on="done" to="listReports"/>
    </subflow-state>

 

needs changing to

 

Code:

[code] <subflow-state id="editAdhoc" flow="adhocFlow"> <attribute-mapper> <input-mapper> <mapping source="requestParameters.reportUnit" target="adhocReport"/> </input-mapper> <output-mapper> <output-attribute name="editorURI"/> </output-mapper> </attribute-mapper> <transition on="done" to="listReports"/> <transition on="customEdit" to="redirCustomEditor"/> </subflow-state> <end-state id="redirCustomEditor" view="externalRedirect:${flowScope.editorURI}"/>

 

Then the custom editor is launched without trouble from the standard repository view.

 

This also needs doing to repositoryFlow.xml, but take care that the on="done" transition differs, so don't just copy and paste.

 

Thank you for your help,

 

Nick

[nicholas]

Is it possible to bypass the Ad Hoc Editor entirely? So that we can have a sequence like this:

 

User selects "New SQL Query Report"

They are shown a text box to type an SQL query into. This page also has a drop down list of data sources

They click 'run' and the report is run - they don't need to select which columns to show, all available columns are presented.

They can click 'back' and edit the query, or save it.

[/ul]

 

Maybe we can't avoid seeing the Ad Hoc editor, but is it possible for our 'custom ad hoc launcher' to pre-fill the report so that it contains all available columns? (It might have to run the SQL query in order to find them, though... and it could be a very slow query.)

 

We're trying to have this very simple feature: A text box a user an type an SQL query into, and then they can see the results. They want to be able to then schedule that query to run again later.

 

Thanks,

 

Nick