Selecting parameter from values of a SQL Query

[brommycanary]

Hi,

I hope I am posting this in the correct place.

I'm a relative newbie to Jasper Reports having worked with both Crystal and SSRS in the past, and I am having some issue getting my head around a report. I am using iReport Designer 4.5.1 to design the report.

I need to produce a report that selects a set of values from a database table based on a company. I would like the user to be able to select the name of the company from a list of available companies rather than having to remember and type in the company's unique database ID number. I can't work out how to confgure the report to load a list of the customers from the database and use this list in the parameter prompt.

So, in short, how do I create a parameter prompt that contains a drop-down list of values that are loaded from the database and then use the value selected from that drop-down list to run the SQL query in the report itself?

Thanks for any help or advice you can offer!

 

Chris.

[cbarlow3]

This is a very important technique to know, in my opinion.

1. In your report, create a new parameter called $P{companyId} if you are only going to allow them to select a single company, but just so I can show you how to handle supporting a LIST, let's call the parameter $P{companyList}, and make the data type of that list be java.util.List.

2. In the WHERE part of your query, you now want to add either AND CompanyTable.Id=$P{companyId}, or if you are using a list, you use the following special JasperSoft syntax:  $X{ IN, CompanyTable.Id, companyList }  The word "IN" is a keyword for the $X{} syntax and should not be surrounded by quotation marks, the second argument is the value you are trying to see if it's in a list or not, and the third value is understood to always be a parameter, so you don't include the $P{} syntax.

3. This is all you need in theory to get a selection list working for iReport preview mode, but you still have to enter the different values you want, and like you said, who remembers the internal Id values, are you supposed to surround the text you enter with quotation marks or not, how do you use commas, etc.  An interesting thing to note while you're testing in iReport is that if you just choose the default (null) value for this list parameter, the report will actually include all companies in your WHERE--any company value it finds is considered to be "IN" null.  That's a little counter-intuitive, but handy, when you think about it.  In most cases I run into, selecting "none" intentionally would always result in an empty report, so it seems that the default behavior is more handy.  You could always code special logic if you didn't want that to be the default behavior:  AND $P{companyList} IS NOT NULL AND $X{IN, CompanyTable.Id, companyList}, for example.

4. Now when you deploy to JasperReports Server (I'm making an assumption that's how your end user is running the report), you're going to want to add an input control, but you first need to create a query.  If this is an input control that you will probably need for other reports, I recommend making a special folder in your Organization called Queries and another called InputControls.  From the Repository Navigator pane in iReport, right click on your new Queries folder and choose Add | Query to bring up a Query dialog box.  In the General tab, let's specify a name of companyQuery and a label of "Company Query" (without the quotation marks).  In the Query tab, set the Query Language to SQL and set your Query to something like the following (I obviously don't know your database):  SELECT CompanyTable.Id AS ID, CompanyTable.Description AS Description FROM CompanyTable WHERE CompanyTable.Status='Active' ORDER BY CompanyTable.Description.  In this query, I'm making an assumption that although internally we'll be selecting a list of primary keys called Id, I'm hoping that the more user-friendly field that the user would like to see is called Description and that it is also forced to be unique within the table.  On the Data Source tab, choose your data source from the repository (presumably the same one that your report uses), and click "Save".

5. Now right click on your new InputControls folder and choose Add | Input Controls to bring up the Input Controls Dialog.  In the General tab, let's give a name of companyList (this must agree with the name of your parameter exactly) and a label of "Company List" (without the quotation marks).  In the Input Control Details tab, scroll down and choose Multi Select Query (if you want to also define an input control called companyId, you might instead choose Single Select Query), and keep the "Visible" checkbox checked.  When you choose "Multi-Select Query", it automatically adds a new section to the dialog box that has a "Query Resource" tab and a "Value and Visible Columns" tab.  For Query Resource, select the "From the Repository" radio button and then browse for the query that you just created in step 4.  In the "Value and Visible Columns" tab, you want the Value to be the thing that your main report is going to be comparing against--in this case, it's the ID column returned from your query, so just put "ID" (without quotes) in the "Value Column" box.  For "Visible Query Columns", you can actually list multiple columns from your query that the user will see when making their selection(s) from a dropdown box, but since the ID isn't likely to mean much to the end user, we'll just add a single visible query column:  click the "Add" button and type DESCRIPTION.  The description values that appear will be in the order that our query puts them, which is why I put ORDER BY CompanyTable.Description.  Click "Save".

6. Back to your report...the next time you need a companyList input control, you won't have to do steps 4 and 5 again...they're already done.  So, deploy your report to JasperServer, then expand it in the Repository Navigator pane so you can see the Input controls folder that is local to that report.  Right click on "Input controls" and choose "Link to an existing input control".  Now browser your way to the companyList input control that is in your (fairly) new Organization | Input Controls folder, and click the "Open Resource" button.  I will usually also right click on the report node itself in the tree and choose "Properties".  The dialog that comes up has a General tab, a "Main Report and Data Source" tab, and an "Other" tab.  I go into the "Other" tab and check the "Always prompt" checkbox.  But of course, I do that any time my report has an input control to fill a parameter, not just when that input control is a list based on a query.

7. If all has gone well so far, then when you run the report in JasperReports server, it should prompt for "Company List" and provide a dropdown list of company descriptions in alphabetical order.  You can select just one, you can hold down the CTRL key and select/de-select as many as you like, or you can not select any (which is the shortcut for selecting all, unless you've specfiically programmed something special to handle this case).  Even though the user is seeing the description field, the parameter will instead contain a list of the ID values that go with the descriptions they selected.  The special $X{IN} syntax in your WHERE clause will help your query determine whether or not a specific companyTable.Id value should or should not be selected based on whether or not it is contained in that list in $P{companyList}.

I've tried to be pretty detailed in my explanation.  Once you've created the query and the input control in a place where they can be re-used, it is of course much faster than I'm making it out to be.  You just remember that you already have a multi-select input control called $P{companyList} and you remember that it prompts by description but returns a list of IDs, and you're set!

Hope that helps.

Carl

Post Edited by cbarlow3 at 03/21/2012 15:40

[mgbrillo]

Hi Carl,

just wanted to ask if it's possible to have an 'independent' list for a prompt parameter - where instead of just a one-line data entry, the user can instead select from a drop-down list and have that (selected) value as the value for the prompt parameter to be used (later) in other queries.

Thanks in advance.

Mark

[cbarlow3]

I think I'm having a hard time understanding your scenario.  The technique I describe lets the end user be prompted at runtime with a dropdown list that is generated in real time based on a query (like a list of company names) and the user is allowed to pick one of them (or pick multiple ones, depending on how you set up the input control), and the main report parameter is filled in with some key value(s), based again on how you set up the query and input control.  So, your parameter in your main report might accept a company name as a String.  Or it might accept a LIST of company IDs (primary key for a company table, for example), etc.  Your main report would use the value or list in that parameter to filter out results in a WHERE clause.

So...every time you run the main report, you first select a few company names from an alphabetical dropdown list, and then the report is generated, selecting only the companys you picked. 

I can't tell from your last comment if you're trying to affect the PROMPT (instead of the resulting parameter) or what you mean when you say that something you're doing affects prompts "later".  Certainly I am building input controls from either database queries or hard-coded "list of values" at one point in time and then linking them to various report specifications later in time as I create them, but the actual USE of the input control happens mere seconds before the main query runs, so I just am not sure I understand whether you are looking for the same thing I described already or something completely different.  If you can walk me through a chronological example of what you want the end user to experience, maybe I can help.

Carl

P.S.: I should say that we also need to verify whether your end user is running these reports in JasperReports Server or some other environment, since the way input parameters are filled is very different depending on whether you are running reports from within a Java program of your own making, preview mode of iReport, from within JasperReports Server, or some other environment.  My advice thus far has all been under the assumption that you are using JasperReports Server, but this is really the iReport forum, so I figured I'd stop assuming and ask again.

P.P.S.: I just now noticed that the questions were from two different posters.  Sorry, I thought for a minute that the recent posting was a restatement of the original question from the original poster.

Post Edited by cbarlow3 at 03/26/2012 14:42

Post Edited by cbarlow3 at 03/26/2012 14:45

[brommycanary]

 Hi,

 

Just a quick thank you to say that your solution worked perfectly! Thank you for taking the time to write such a detailed (and super helpful) response!

 

Chris.

[mgbrillo]

sorry if my questions are confusing.. this is the very first time that i have to use iReports or Jasper Reports - let alone no experience in java (i'm using c# for various assignments).

my assumption is that it will be in JasperReports (i know that the client has a custom 'enterprise reports' where users will be using to view these reports). i'll try to collect this information later when i can.

i wasn't asked to design a report, but just to find out (and how to implement) where users can be prompted to enter a Table name. These table names should be selected from a drop-down list - instead of users manually typing this in (independent of datasource, or not getting the list from a database).

can this list be done manually? like i can have a list with hard-coded values: "Table1", "Table2", "Table3"? within the jrxml?

or do i have to have a java code where these values are hard-coded and be retrieved when the jrxml is called?

the objective is:

1. user prompted to enter the table name to view.

2. user selects the table name from a drop-down list.

3. report will contain data from that selected table name (at this point, i assume that the value selected from that parameter prompt will be used in the SQL query that is executed when generating the result report).

*what i wanted to accomplish is to show/demo in iReports that I can have this drop-down list (with hardcoded values) available for users to select.

i keep on looking at the parameter prompt as this seems to be the only 'input' data entry for users to be prompted for a value.

thanks and i appreciate your time in helping out.

Mark

Post Edited by mgbrillo at 03/26/2012 17:02

Post Edited by mgbrillo at 03/26/2012 18:16

[cbarlow3]

Well, there are two things that don't sound like they fit very well with JasperSoft:

1. Using parameter input to filter the results of a query is very normal, and that input can be a user entering text, a date, selecting hardcoded values from a dropdown, selecting values returned from a query, etc.  But in most cases, the main purpose of this parameter input is to control filtering or MAYBE sorting of the data.  The actual column definition is typically hardcoded in the query (I'm going to return the company name, the city, the state, and the annual sales for that company, but I will let the user select what companies they want to include on the report or let them pick a date range for those sales, etc.).  It sounds like you want the user to be able to actually MODIFY the query right down to what tables are accessed.  I know that I've seen examples where people actually have parts of the SQL modified at run-time, but I avoid it because I feel that it's kludgy and potentially dangerous.  It sounds like you're thinking of having the parameter get filled in with a tablename (never mind for the moment what the UI part looks like to the user) and then have your query just dump fields from that table, like:

SELECT * FROM $P!{UserSelectedTableName}.

That sounds like a horrible idea to me.  I DO actually have a standard report that produces a PDF of all the tables and column names in our database (but none of the data), but I'm creating that based on a query of two of our database tables actually called TABLES and COLUMNS that store metadata about our database definition.  I find this report helpful to remind me what the tablenames, fieldnames, and data types are, as well as the relationship between various tables.  But I don't have a generic report that takes the place of a sort of database explorer portal.

2. The other thing that sounds strange to me is that you would want to hardcode the list that the user can select from.  This certainly IS possible.  The main difference between this and my previous notes is that instead of defining a QUERY first in JasperReports Server, you instead define a "List of Values" (and this can even be in the Public folder), where you define what the prompts will be and what will actually be filled in for the parameter.  You then define an input control that uses that List of Values instead of an input control that uses a query.  You then create a link from the Input Controls folder of your individual report(s) to the actual input control you created.  In our database, I have for example a "List of Values" called "CheckStatusValues", where the options are hardcoded to Pending/P, Issued/I, Voided/V, Stop Payment/S, Reconciled/R, Escheated/E.  The text before the slash is what I've defined as the "Label" that the user will see in the dropdown box, and then I associate each with a "Value", which in this case corresponds to the single letter abbreviation that we actually use in our database to represent a check status.  Then I have a "multi-select from list of values" input control called CheckStatusList that points to that list of  values.  Now whenever I'm writing a check-related report and I want the user to be able to select what check statuses to include, I create a List parameter called $P{CheckStatusList}, and I link to that CheckStatusList input control wtihin JasperReports Server.  Naturally, I also make sure my main query has a WHERE clause that includes AND $X{ IN, CHECK.STATUS, CheckStatusList }

I hope some of this information is useful.  If you're looking for a tool that will just let an end user browse through the database (I'd like to take a peek in this table, see what the fields are, look at some sample data, etc.), I don't think that's the purpose or purview of JasperSoft.  The maker of the database itself (DB2, SQL Server, Oracle, etc.) would presumably provide such tools themselves.

Carl

I forgot to absorb your mention of JAVA.  That probably makes all my discussion of JasperReports Server (including creating what it calls "input controls" moot.  If you're using Java to make calls to JasperReports, then I think you have to create all your own user interface anyway...everything the user sees, you're creating as part of your Java program.  Maybe I am misunderstanding your anticipated runtime environment.  In fact, I still don't think I really understand how your end user will run your report or what your report is trying to do, so I think I'm going to stop providing any additional advice, as I'm probalby confusing you as much as you're confusing me.  Sorry I couldn't be more help.

Post Edited by cbarlow3 at 03/26/2012 18:53

[mgbrillo]

thanks.. and no i'm not trying to get confused - instead, trying to aborb the info here. i know i'll be able to use (and others as well) these steps.. i appreciate all your inputs.

i'm trying to follow your steps as much as i can on the limited sources i have (i only have iReports and no DB connection). again, this task is just to show that in the new iReports (we are using jasper reports 0.1.1) and is trying to upgrade.

i'm using an xlsx as a database listing table names finding a way how to access the data as values for my drop-down list for the prompt parameter (i followed your guide on how to setup xlsx as datasource and was able to use the values from the fields as per the xlsx file).

in the xlsx:

table name:

Table 1

Table 2

Table 3

The report output should just show the count - say based on another prompt parameters: start date and end date

so in the DB for example, i have ALL the tables with the same columns as: document_name, document_create_date

say, my tables are TABLE1, TABLE2 and TABLE3 (again, for demo purposes, i don't want to retrieve these values from any datasource, but just a hardcoded list showing TABLE1, TABLE2 and TABLE3).

user selects TABLE1 from the list, then SQL should be:

SELECT COUNT(*) FROM $P!{TableNameList} WHERE DOCUMENT_CREATE_DATE >= $P!{StartDate} AND DOCUMENT_CREATE_DATE <= $P{EndDate}

result in the report will be:

TABLE NAME              COUNT                      DATE START                    DATE END

TABLE1                        250                             01-Jan-2012                     29-Feb-2012

*the problem we have right now is that we have 1 template for each of the available table in the DB. having a drop-down list for user to select the desired table to view will allow us to maintain only one template instead of providing multiple templates with the same contents and/or format.

*while the prompt parameter is enough, we don't want the users to enter/or type an incorrect table name. providing a list of valid table names wil eliminate this problem.

 

 

Post Edited by mgbrillo at 03/26/2012 21:47

[rpachouli12]

cbarlow3
Wrote:hello can anyone send me query how i find detail of those students which name is started with letter a?

This is a very important technique to know, in my opinion. 1. In your report, create a new parameter called $P{companyId} if you are only going to allow them to select a single company, but just so I can show you how to handle supporting a LIST, let's call the parameter $P{companyList}, and make the data type of that list be java.util.List. 2. In the WHERE part of your query, you now want to add either AND CompanyTable.Id=$P{companyId}, or if you are using a list, you use the following special JasperSoft syntax:  $X{ IN, CompanyTable.Id, companyList }  The word "IN" is a keyword for the $X{} syntax and should not be surrounded by quotation marks, the second argument is the value you are trying to see if it's in a list or not, and the third value is understood to always be a parameter, so you don't include the $P{} syntax. 3. This is all you need in theory to get a selection list working for iReport preview mode, but you still have to enter the different values you want, and like you said, who remembers the internal Id values, are you supposed to surround the text you enter with quotation marks or not, how do you use commas, etc.  An interesting thing to note while you're testing in iReport is that if you just choose the default (null) value for this list parameter, the report will actually include all companies in your WHERE--any company value it finds is considered to be "IN" null.  That's a little counter-intuitive, but handy, when you think about it.  In most cases I run into, selecting "none" intentionally would always result in an empty report, so it seems that the default behavior is more handy.  You could always code special logic if you didn't want that to be the default behavior:  AND $P{companyList} IS NOT NULL AND $X{IN, CompanyTable.Id, companyList}, for example. 4. Now when you deploy to JasperReports Server (I'm making an assumption that's how your end user is running the report), you're going to want to add an input control, but you first need to create a query.  If this is an input control that you will probably need for other reports, I recommend making a special folder in your Organization called Queries and another called InputControls.  From the Repository Navigator pane in iReport, right click on your new Queries folder and choose Add | Query to bring up a Query dialog box.  In the General tab, let's specify a name of companyQuery and a label of "Company Query" (without the quotation marks).  In the Query tab, set the Query Language to SQL and set your Query to something like the following (I obviously don't know your database):  SELECT CompanyTable.Id AS ID, CompanyTable.Description AS Description FROM CompanyTable WHERE CompanyTable.Status='Active' ORDER BY CompanyTable.Description.  In this query, I'm making an assumption that although internally we'll be selecting a list of primary keys called Id, I'm hoping that the more user-friendly field that the user would like to see is called Description and that it is also forced to be unique within the table.  On the Data Source tab, choose your data source from the repository (presumably the same one that your report uses), and click "Save". 5. Now right click on your new InputControls folder and choose Add | Input Controls to bring up the Input Controls Dialog.  In the General tab, let's give a name of companyList (this must agree with the name of your parameter exactly) and a label of "Company List" (without the quotation marks).  In the Input Control Details tab, scroll down and choose Multi Select Query (if you want to also define an input control called companyId, you might instead choose Single Select Query), and keep the "Visible" checkbox checked.  When you choose "Multi-Select Query", it automatically adds a new section to the dialog box that has a "Query Resource" tab and a "Value and Visible Columns" tab.  For Query Resource, select the "From the Repository" radio button and then browse for the query that you just created in step 4.  In the "Value and Visible Columns" tab, you want the Value to be the thing that your main report is going to be comparing against--in this case, it's the ID column returned from your query, so just put "ID" (without quotes) in the "Value Column" box.  For "Visible Query Columns", you can actually list multiple columns from your query that the user will see when making their selection(s) from a dropdown box, but since the ID isn't likely to mean much to the end user, we'll just add a single visible query column:  click the "Add" button and type DESCRIPTION.  The description values that appear will be in the order that our query puts them, which is why I put ORDER BY CompanyTable.Description.  Click "Save". 6. Back to your report...the next time you need a companyList input control, you won't have to do steps 4 and 5 again...they're already done.  So, deploy your report to JasperServer, then expand it in the Repository Navigator pane so you can see the Input controls folder that is local to that report.  Right click on "Input controls" and choose "Link to an existing input control".  Now browser your way to the companyList input control that is in your (fairly) new Organization | Input Controls folder, and click the "Open Resource" button.  I will usually also right click on the report node itself in the tree and choose "Properties".  The dialog that comes up has a General tab, a "Main Report and Data Source" tab, and an "Other" tab.  I go into the "Other" tab and check the "Always prompt" checkbox.  But of course, I do that any time my report has an input control to fill a parameter, not just when that input control is a list based on a query. 7. If all has gone well so far, then when you run the report in JasperReports server, it should prompt for "Company List" and provide a dropdown list of company descriptions in alphabetical order.  You can select just one, you can hold down the CTRL key and select/de-select as many as you like, or you can not select any (which is the shortcut for selecting all, unless you've specfiically programmed something special to handle this case).  Even though the user is seeing the description field, the parameter will instead contain a list of the ID values that go with the descriptions they selected.  The special $X{IN} syntax in your WHERE clause will help your query determine whether or not a specific companyTable.Id value should or should not be selected based on whether or not it is contained in that list in $P{companyList}. I've tried to be pretty detailed in my explanation.  Once you've created the query and the input control in a place where they can be re-used, it is of course much faster than I'm making it out to be.  You just remember that you already have a multi-select input control called $P{companyList} and you remember that it prompts by description but returns a list of IDs, and you're set! Hope that helps. Carl Post Edited by cbarlow3 at 03/21/2012 15:40

[cmcconnell]

"The special $X{IN} syntax in your WHERE clause will help your query determine whether or not a specific companyTable.Id value should or should not be selected based on whether or not it is contained in that list in $P{companyList}."

This report and $X{} parameter works wonderfully in iReports, but when uploaded to jasperreports I am getting error...

java.lang.Exception: 1 - Incompatible java.lang.String value assigned to parameter 'name of parameter' in the 'name of report' dataset.

I have changed the parameter (which is an ID field -  math.bigDecimal) to string (to_char in query) and still get the same error.

It is a simple report - with a simple single select query parameter. Basically just select customer name - value column is cust_id.

Any ideas???

 

[josemm.1709]

Excelent, Thx.

[beltaha]

Thank you very much for this explanation. Pretty clear and accurate.

[martin.clarke]

Carl,

 

I WAS going to nominate you for a Nobel. I faithfully adapted your iReport instructions for Studio, it only took a day (I'm OLLLD), I could see the banks of the Jordan after a bipolar week with Jasper to this point, pulled down the list on the server, could only choose one item (DANGER! Danger Will Robinson), but knew I'd conquered such insurmountable obstacles before, then:

 

net.sf.jasperreports.engine.JRRuntimeException: Invalid type java.lang.String for parameter CustomerOrganisationList used in an IN clause; the value must be an array or a collection.

 

Is the 'value' referred to the single one only I can select in the server parameter pull-down list and that is of the type java.lang.String?

 

That isn't in the Studio Design or Source?

 

Studio Parameter CustomerOrganisationList is of class java.util.List, as per your instructions.

 

Best regards

Martin

martin.clarke@dionglobal.com

[cbarlow3]

Martin,

 

I think your two problems (can only select a single item and getting an error back that you are using a String where you want an array or collection) are related, and you're REALLY close to the functionality you're looking for. My original post was from when I was using iReport 3.7. Now I'm using Jaspersoft Studio 5.6. In the repository explorer, find the input control that you created, double click it to bring up the Resource Editor, make sure you're looking at the "Input Control" pane, and set the "Type" dropdown to read "Multi Select Query" (I'm guessing that yours is currently set to "Single Select Query", which is also useful, but it returns a single string rather than a collection/list). Then save the change and test.

 

The multi-select experience for the end user is different than it used to be back in 3.7. Now you just use a normal left click to turn on or off selections one at a time. You used to have to use CTRL to select individual items that weren't next to each other. Play around with that, but I think if you just change that type field on the input control definition, it will allow you to select more than one item and will also return the data type that the parameter is expecting and then work in the $X{} part of your WHERE condition. Please post whether you get it working or not.

[martin.clarke]

That did it Carl. Multi Select Query. Are you a single-malt or Cohiba man?

[cbarlow3]

Glad that worked for you! On a related note, one thing that's very different about Jaspersoft Studio 5.6 vs. when I used to use iReport 3.7 is the publishing feature (the button that lets you publish the report to JasperReports Server). It took me a little getting used to for some reason as compared to the right click | create method in the repository navigator. One tip for when you are publishing a report where one or more of your input controls are supposed to be links to existing input controls instead of local resources: when the list of input controls comes up in the publishing wizard with checkboxes checked, right click on one that is supposed to be a link instead and the choose the option that says you want to create a link to an existing input control, and it will prompt you to drill down and select that resource from the repository (so make sure you've created it BEFORE you try to publish your report). Repeat for any other input controls that should use links.

[nazaf]

Hello,

I have my cascaded parameter report working. But i wanted a functionality as if i select a value from first input control(?) , the results which will come out after filtering in dropdown should be all selected and not just the first one. How do i do that?

Thanks.