Hyperlink to a Drilldown Report

[tunguyen]

Hi,

 

I have a requirement to do a drill-down to a detail report from a master report via hyperlink. On my master report, I have a TextField whose Hyperlink tab has "ReportExecution" for the Hyperlink Type attribute.

 

This is my question: What value do I need to fill in what tab/field of this Hyperlink tab to actually make it invoke a different report when the user click on the master report's TextField? I also need to pass a parameter, but I think the "Link Parameters" tab takes care of that.

 

I bought the iReport manual, but it did not address this situation.

 

After I upload the report to JasperServer 3.0.0, and run the master report via the web interface to JasperServer, the error I get when clicking on the hyperlink in the master report at runtime is:

 

com.jaspersoft.jasperserver.api.JSException: jsexception.view.report.could.not.load

at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:113)

at com.jaspersoft.jasperserver.war.action.ViewReportAction.checkForParams(ViewReportAction.java:148)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)

at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)

at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)

at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)

at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)

at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)

at org.springframework.webflow.engine.State.enter(State.java:200)

at org.springframework.webflow.engine.Flow.start(Flow.java:557)

 

 

Thanks for your help,

-Tu

Post edited by: tunguyen, at: 2008/07/16 14:39

[jmastajam]

Hi Tu,

I'm trying to do pretty much the same thing that you're doing. I'm searching for a solution too.

 

I need to know two things:

1. What is the link expression need to be.

2. How do I pass parameters from the master report to the drill down. For example, in my requirement, if the user clicks on state CA, I want to display the detail info for CA.

 

I guess I might as well post this so you don't feel so lonely. :)

 

Nghia

[jmastajam]

Tu,

Just in case you're still reading this thread (and anyone has the same issue), I have found the solution.

 

This is how you call one report from another and passing variables to it. I'm doing this all with iReport (and I'm too lazy to track down the jrxml file code for you).

 

In the MASTER report, at the control (field, image w/e) -> properties -> hyperlink->link parameters

_report = <relative path + report label on your server>   (ie. "/reports/reportlabel")

This parameter is required so that the "drill down" report is pulled.

You can add all the parameters you need to pass to the drill down here. When the URL is built, it'll grab from this list.

Oh, be sure to add the input controls in JasperServer. These input control's name need to match to the letter the arguments you have in the drill down.

That is it! You don't have to set the anchor or anything unless you don't need to drill down to a specific area  in a report. I haven't tried this btw.

 

Hope it helps.

Nghia

[aezaz123]

 Hi All,

I am new to jasper soft, i am struck in drill down report. I saw this discussion on drill down. Can any one please explain me how to do drill down in jasper soft IReport.

Its very urgent kindly please help me..

 

Thanks in Advance

 

Aezaz Ahmed

[cbarlow3]

My advice is to first get the report that you are drilling down to working.  Presumably it will be a report that accepts at least one parameter as input (that it will be getting from your main report eventually).  Let's say that this report is called "AccountsSingleCity" and that the two parameters passed to it are $P{TargetState} and $P{TargetCity}.  Then create the "main" report (actually, I have reports where you can drill down and then "drill back up" again, so "main" is in the eye of the beholder) that will output a field in the detail that has prints the city in question.  Let's say this "main" report is called "AccountsSingleState" (I have such a report...it's a report that is also drilled down into by another report that displays a Fusion Map of the United States).  And let's say that in this main report I have an input parameter called $P{TargetState} and a field called $F{ADDRESS_CITY}.  Test this report to make sure that it accepts an input State and displays a list of cities correctly.

So far, so good.  Now to make the main report drill down to the city report and pass it the state and city parameters it needs:

1. In the Report Inspector, right click on the $F{ADDRESS_CITY} field in the Detail band and select "Hyperlink".

2. In the "Hyperlink" dialog box that comes up, set the "Hyperlink type" to "ReportExecution", using the dropdown box.

3. In the "Link parameters" tab, Add a parameter where the Parameter name is _report and the Expression is "repo:/Reports/AccountsSingleCity" (no ".jrxml" or ".jasper" needed, although the quotation marks are needed, and your exact path may vary.  While you're still testing in iReport, I think you can just make it be the name of the report without "repo:", etc.  When you really get fancy, you can use a variable value here instead that will change depending on whether you are running locally or in the repository.)

4. Add another parameter where the parameter name is TargetState and the Expression is $P{TargetState}

5. Add another parameter where the parameter name is TargetCity and the Expression is $F{ADDRESS_CITY}

6. In the Hyperlink dialog box, I usually do NOT have to fill in "Anchor Name Expression", "Bookmark Level", anything in the "Reference", "Anchor", "Page", or "Tooltip" panes, and I leave the "Hyperlink target" set to "Self".

By the way, in my example, I also have a text field in the column header of the report that I drill down to that is also a hyperlink.  The text says "Click here to see totals by city" (and I made sure to underline the text), and I set it up just like above, except that the _report expression is obviously different, and I only pass a TargetState (I pass back the same $P{TargetState} that I received, and I don't have to pass a TargetCity.  This is how I drill back up to the report that called me.

And that's my primer on drill-down reports.  Hope it helps!

Carl

[aezaz123]

 Hi Carl,

 

Thanks for your quick reply...

 

I tried your tips but in preview i am just able to see hyper link  mark but its not drilling down.

I will explain my report please correct me if i am wrong.

I have developed a report called Detail_Report with will display "Product" and "Revenue" month wise. also i have created one parameter here called $P{year} when i preview it, gives me report output.

Now i Developed one more report called Master_Report. here i am displaying "Product" and "Revenue"  "Year"  wise. here also i have created one parameter again $P{year}. I kept both the reports on my desktop in JRXML format. Now i followed step explained by you i.e right click on parameter $P{year} in Master_Report and selected hyperlink then in dialog box selected "Self" in Hyperlink target and "ReportExceution" in Hyperlink type. Now in Link parameters tab i Added two parameter one is _report with report name "Detail_Report" in Expression (also in place of report name i gave full path i.e C:/.../.../report name but its not working) then i added one more parameter year.

I have attached Hyper link dialog box for my master_report for your ref.

When i see preview i am able to see only hyper line mark(hand mark) on "year" but its not drilling down. Can you please let me know that am i doing any thing wrong, is there any thing else to add.

   Please its very urgent.. hope you will help me

 

Thanks once again

Aezaz Ahmed

 

[cbarlow3]

Your screenshot looks pretty good to me.  There are only three things that raise little questions for me:

1. Is your report called "Detail_Report" or "Details_Report" with an "s"?  And are you sure the report has an underscore in the name of the resource?  You might try saving the report with a name that has no underscore or space (change it in the jrxml up near the beginning and also do a "Save as"), rerun it, and then change the hyperlink tab in the Main_Report to agree.

2. I don't know if you have to have already compiled the DetailReport into a .jasper for the MainReport to find it.  I don't think so, but since running the DetailReport is probably a test you do anyway, it should be a moot point.

3. Now the thing I find the strangest:  you are putting the hyperlink on the printout of a PARAMETER in the PAGE HEADER, but you're making the value that you pass to the DetailReport be a FIELD:  $F{year}.  That seems unintuitive to me.  I would normally expect that $F{year} would be a value that shows up in your DETAIL band and varies from one detail to the next, which makes the drilldown accept a different value depending on which detail record in MainReport you click on.  But here, it looks like you enter a single year as a parameter for MainReport, display it in the page header of every page (unchanging), and make it be the link to ANOTHER report that I THINK you must want to use the same year that was used as input for the MainReport.  If you're trying to make the year vary per detail based on a field, then you should be putting the hyperlink where the $F{year} appears in the detail band.  On the other hand, if you're just trying to make the DetailReport use the same year that the MainReport uses throughout, then you should keep the hyperlink where it is, but change the Expression in the Hyperlink dialog box from $F{year} to $P{year}.

Actually, I was wondering before what the problem could be that it would not pull up a DetailReport but ALSO not give you an error at runtime like "report not found" or something like that.  It occurs to me that maybe it IS running the DetailReport, but maybe it's passing a null value to the DetailReport, and if that parameter is used as part of the SQL query, it might be an empty report and therefore just not displaying.  As a quick experiment before you tinker with the other ideas, you might go into your DetailReport and click on the top level in the Report Inspector tree (which is the name of the report), and then in the Properties pane scroll down to the property labeled "When No Data".  Change the dropdown selection from "No Pages" to "All Sections, No Detail".  Run it again by itself with "null" as the input parameter and verify that it still outputs a report with a title/summary/etc.  Then try your MainReport again.  If the hyperlinks work but the report comes up empty, then it's a problem with what data you're passing in to the parameter, which is my answer #3 above.

Good luck!

Carl

[aezaz123]

 Hi Carl,

I tried your tips but still its not drilling down. I am not sure the reason, i have attached my master and details jrxml files for your ref. please have a look on it and let me know am i right. If possible please share any working drill down master and details jrxml files.

 

Looking forward for you reply

 

Thanks

Aezaz 

[cbarlow3]

Okay, I just created two related reports Drill1 and Drill2 from scratch to remind myself how it works and see what was missing from my explanation.  I'd say I left out two very important steps:

1. I don't think the hyperlinks work in Preview mode on iReport.  I think you have to actually deploy the reports to the repository and run them there.

2. If a report is going to pass parameters to another report, the target report needs to have input controls defined that have the same names as the parameters.  I really don't understand why that would be true, but it seems to make a difference for me.  Naturally, if a report like Drill2 is intended to be useful as a standalone report, it makes sense that you would create input controls so the user can input values for the parameters (iReport automatically prompts you for parameters if you have the appropriate checkbox checked, but JasperServer does not), so I hadn't noticed this before.  But it does seem odd to me that if you created a report that was only intended to be run from another report that supplies the parameters that you would need input controls.  Still...when I ran Drill1 from within the repository and clicked on a link, it came up with an empty Drill2 report.  I added the input controls and ran it again, and the links correctly passed the appropriate parameters to Drill2.  I deleted the input conrols, and the links again failed to pass the values correctly.  I think that's either a bug or bad design: when you specify that a hyperlink is supposed to fill in a "Parameter name" with a particular "Expression", why does there need to be a corresponding input control created?

Anyway, I've attached the jrxml files for Drill1 and Drill2.  The two steps I've listed above have no effect on the jrxml.  They are steps that are done in the repository.  Nevertheless, I'm including the jrxml files just in case there's something useful in them.  They have "fake" data at the top of the SQL expression, all from Common Table Expressions, so you should be able to run each of them in iReport (but without the links working) immediately, regardless of your data source.

Carl

[aezaz123]

Carl,

You said in your reply that  " I added the input controls and ran it again, and the links correctly passed the appropriate parameters to Drill2."

i didn't get you where exactly you have added input controls and how please explain me, it might be simple but i am confused kindly clear my confusion.

 

Thanks

Aezaz 

[cbarlow3]

In iReport, I have a tab/window open for the "Repository Navigator" that I use to deploy the report to the repository. (I right click on the folder that I want the report to go into (mine is called "Reports"), then I choose "Add" | "Report Unit" and answer the prompts in the Wizard).  But a report that requires input parameters either needs to be run from a Java program that can pass those parameters to it or it needs to be run within JasperServer, which requires that input controls be created.  If you look in the tree that is displayed in the Repository Navigator and fine the folder that holds your report (Reports/Sample Drill Down 2 in my case), there should already be a folder called "Input Controls", but when you click the + sign next to it, the folder is empty.  If you right click on the "Input Controls" folder, one of the choices is "Create a local input control".  That will take you through a Wizard, and how you answer will depend on what datatype you're expecting.  Give the input control the same name and data type as the parameter you're trying to fill.  In my case, I created two text input controls called TargetStateAbbr and TargetStateName.  When I run the report in JasperServer (this is Drill Down 2...the one that eventually will be called by Drill Down 1), it doesn't actually prompt me for these two...it runs with the default values for the parameters.  But it DOES have an icon in the upper left that has two little checkboxes in it with a tooltip of "Report Options".  When I click THAT, it prompts for the two inputs the report needs, and it displays output based on those two inputs.

Once I have Drill Down 2 working, I also deploy Drill Down 1 to JasperServer, test that it outputs the correct information by itself, and then I try the hyperlink, and it seems to work fine.  If you're running your deployed reports some other way besides JasperServer, then I'm not sure these input controls would be necessary...it seems very suspicious to me that you need input controls that you're never planning on actually using (unless Drill Down 2 is also useful as a stand-alone, of course)

Carl

[aezaz123]

 Hi Carl,

I need some clarification on drill down in jasper soft, as per my knowledge the thing we discussed in this topic is hyper linking the reports and passing parameter through url. But drill is a feature of BI tool where drill report and drill down report is one single report template only parameter is to be change, suppose like if i have a report displayed data based on region and when i click on region it has to show data based on state wise but all in same report this is drill as per my knowledge. Correct me if i am wrong.

 

Please let me know is it possible the second type of drill which I explain above is possible in jasper soft ireport.

 

Looking forward for your reply

 

Thanks

Aezaz Ahmed

 

[cbarlow3]

I believe you're correct that JasperSoft uses the term "drill" in two senses.  As far as I know, the inherent ability to recognize hierarchies of data within a single report and dynamically zoom in or out of a level of detail during the running of the report is currently possible in JasperAnalysis, but not in JasperServer.  You CAN actually get a similar effect when you're working on a Crosstab report in JasperServer during the design of a report, but when you actually run the report, I don't think you're able to dynamically expand and collapse various row or columns like you can in design.

The way I've described to handle going from a map of the United States to a report on a specific state to another report on a specific city in that state (and also be able to link back up a level) is done through three separate reports where the data is hyperlinked and passed to other reports via parameters.  In the JasperServer forum I asked whether or not the expand and collapse capability in JasperServer designer is planned as a future feature at run-time, but never heard anything back.

Carl

P.S.: I've never used JasperAnalysis yet, so I don't know any details from experience on how it works.

[yait]

thanks a lot for this post it was very helpful !

[arnav_negi]

Carl,

I have been trying to remove the input control dependence in child report (drill down 2) in my scenario by using variables and fields, but the child report takes "null" for these parameters wherein at parent report variables show correct value. Have you found any way of passing iReport parameter to child report without needing equivalent input controls to be created in child? (For reference: i am using professional jasperreports server 4.01.)

[cbarlow3]

I think you need to create input controls on the child report as a way of making the child report accept input from the parent report into the appropriate parameters.  And I don't understand why that's a "waste" as you mentioned in another similar post.  BUT...if the problem is that your child report is getting the correct input AND still prompting, then there's an easy solution.  In JasperReports Server, say that you want to "Edit" the child report.  Go to the "Controls and Resources" page that lists your input controls, and make sure that the check box marked "Always prompt" is unchecked.  That worked for me, but if that doesn't 100% fit the bill, you might also look in iReport at how those parameters are defined.  There's a checkbox for each individual parameter called "Use as a prompt".  I have those checked, since the parameter value is coming from an outside source (the other report in this case), not being internally calculated based on prior variables, environment, etc.

Hope that helps,

Carl

[tulsi]

hi  carl

 

thank you for your great discussion  i got so meny thing

but i think this hyperlink want work with ireport version 4.1.1 thats true?

i m struggling with that a lot  i had just create report like u but it doesnt work

thank you

tulsi,

 

 

Post Edited by tulsi at 10/19/2011 08:47

[cbarlow3]

Unfortunately, I'm still on 3.7, Professional Edition, so I can't say for sure, but it would be very strange for it not to be backwards compatible for something as basic as a hyperlink.  Good luck.

Carl

[tulsi]

hi

 

thanks for reply, i find out that  it works its my mistake n it doesnt work in case of reference and it should be capital and somthing like that anyways thanks a lot

tulsi,

[arnav_negi]

Carl,

The "waste" here will be that input control in child report which will be shown to user even when the child report doesn't want that control as filter (and is used only as a parmeter being passed to child so that parent report can be filtered using those controls in case of a loop back link). The checkbox you are referring to is a way of either keeping a control as prompt or static report filter.

So my question still remains. If i have to pass a parameter (not IC) from parent to child, how to do it?

[cbarlow3]

Sounds like one of two things is happening:

1. Either you are getting an input control prompt for the child report when you click on the hyperlink (that doesn't happen in the example I wrote...the child report has an input control defined, but the parent report just passes the value behind the scenes the way you want it to), or

2. You've gotten the child report to work correctly from the link, but you also want it to be run independently sometimes and not prompt for input (I think this is less likely...I would think that if you run it independently of the parent report, it would be okay to prompt).

If #1 is the issue you're experiencing, then I reiterate that I don't have that problem with mine.  If I have time today, I'll try to come up with two reports that work even without a connection to a database, and I'l pay close attention to how I hook up the plumbing when I deploy to JasperServer.

Carl

[arnav_negi]

Carl,

Thanks for replying.

Things are a bit disarrayed here. First the case i am attempting is none of 2 mentioned by you. The child has one IC which it takes as link parameter from parent. So far so good. But now i have 2 parameters in parent which are IC in parent. These parameters need to be passed to child so that these can be sent back to parent from child through a reverse-link in child.

Iam tussling to send these 2 parameters to child as these can not be IC in child.

 

PS:My hyperlinked reports otherwise work fine.

 

Thanks,

Arnav

[cbarlow3]

So, the student (report) becomes the master (report), eh?  Okay, I understand most of your example, parent report has two input controls that are used for actual prompting of the user.  Then hyperlinks are created in parent report to call child report, passing a different value depending on which link is chosen, and the child report uses that as input for a parameter.  But the parent must also pass the two input values from the original prompts so that a single hyperlink can be created at the top (let's say) of the child report that will take you back (rerun) the parent report, remembering the two original input values.  Your constraints are as follows:

1. If you run the parent report in JasperServer, you want to be prompted for the first two input values.

2. When you click on a hyperlink in the parent report, you do not want to be prompted for the single input value that the child report actually uses.  You want to just pass the value and have the child report appear with no prompts.  (we also plan to silently pass the original two input values that the parent report used, even though the child report doesn't need them for the visible part of the report, because we anticipate that we want to remember these two values when we build the hyperlink in the child report)

3. Similarly, when you click on the hyperlink in the child report that is supposed to take you back to the parent report, you do not want to be prompted for the two input parameters the parent report actually uses--even though you are prompted for those two values when you run the parent report as a standalone instead of from a hyperlink.

4. I'm not clear on whether or not you have a requirement about what happens if you try to run the child report as a stand-alone report instead of through the hyperlink.  You stated that the two values that you're trying to remember for the parent "can't be input controls for the child", but I assume what you mean is that you don't want the user to be visibly prompted--at least not when they are running the report from a hyperlink, and I already stated that requirement in #2 above.

So I'm going to try to work up a short example that meets criteria #1-3 above, and I'm not going to worry too much about #4 behavior until I can get #1-3 working, and then I'll just see what I end up with.  Stay tuned.

Carl

[cbarlow3]

Okay, I think I've finally worked out the details.  I won't bore you with the whole diary of my testing.  I've attached a parent and child report where the parent has a single parameter FavoriteColor that you want to prompt for when you run it as a stand-alone report.  The report lists U.S. state and territory names and lets you click on one to execute the child report.  When you execute the child report, you pass it two parameter values:  FavoriteColor (which the child report doesn't really care about but is remembering as a courtesy to the parent report) and StateAbbr (which the child report needs for filtering purposes).  The child report then lists various cities in the selected state and also displays the favorite color value just to prove it passed through okay.  It also has a hyperlink at the top of the report that lets you call the parent report, passing back the value of FavoriteColor.

Within iReport, on the Parent report I said that FavoriteColor should be used as a prompt, and on the child report, I said that StateAbbr should be used as a prompt, but FavoriteColor should not.  I'm not 100% sure that has any effect on anything.

Okay, that's actually the easy part--the part that is more of a mystery is how to handle the input controls in JasperServer.  First, I think you need an input control for every parameter, whether you are planning on actually displaying a prompt or not.  So in the parent report, it was pretty straightforward: I made the FavoriteColor prompt have the default properties:  not mandatory or read only, but visible.  For the child report, I set up StateAbbr the same way, but for FavoriteColor I set it to Read Only, not mandatory, and not visible, because I don't want a user to be able to set or change the value of favorite color from within the child report.  Either you still remember what was passed to you from the parent report or (if you ran the child report as a standalone) you're stuck with the default value for this parameter (I chose "null" as the default).

When I tested this, it almost worked the way I wanted: I originally had the JasperServer properties for both reports say "Always prompt".  When I ran the parent report, it prompted for favorite color, then gave a list of state names, and when I picked "California", it ran the child report, but it prompted for state abbreviation (although it did have the value "CA" already filled in, which was nice).  It did not prompt for FavoriteColor, since I made that input control invisible.  I clicked "Okay" and the child report ran correctly, reflecting that the favorite color from the original parent report prompt had been passed through correctly, filtering based on the state abbreviation that had been passed through, and also having a link at the top for re-running the parent report.  When I clicked on that hyperlink, I was again prompted for favorite color, although this time the current value was already entered as the default.  If I click Okay, the parent report looks fine.

So then I unchecked the "Always prompt" checkbox in the JasperServer properties for both of these reports and retested.  Now the two reports call each other just fine and pass the parameter values silently, but if I try to run either report as a stand-alone report, it first runs with the default value for the parameter, and I have to click on the "Report options" icon to set/change the value for the parameter. 

So, the crux of the problem seems to be this:  how do I tell JasperServer that there's an input control that I want to prompt for before running the report when the report is run as a stand-alone, but I want to not prompt first if I'm being run from a hyperlink?  I don't know the answer to that one, although I can think of a work-around at least:  deploy two copies of the parent report to JasperServer.  They are identical except in three things:  resource name (of course), label and/or description (so a user can tell which one is which), and the value of "Always prompt".  The one where you have "Always prompt" checked is the one you want users to actually run from the menu of reports.  The other one is the report name you want to put in your child report hyperlink _report parameter to run.  Similarly, if you need your child report to be available as a stand-alone report, you might need two versions of it as well.  The one that does not prompt is the one you code into your parent report's hyperlink _report parameter.

It would be nice if instead of "Always prompt" yes or no you had a little more flexibility in defining the conditions under which you want to prompt the user first, but I don't know of any way to do that.

Carl

 

[tomcushing70]

I am doing something very similar and I am not having the problem you are describing.  Try doing this.

In iReport, right click on the report (at the highest level) in the JasperRepository.  Select "Properties".  Click on the "Other" tab.  Select "Top of page" for Controls Layout.  This way, the input controls will always show up on the top of the page in Jasper Server.

Now if any of your input controls are mandatory, and your matching parameter on the report does not have a default value, I believe you will be forced to enter the values for those input controls before the report is displayed.

I hope this helps.