SQL Lookup Function

[sheppard.deangmail.com]

Hi All,

I am creating a report where the report query contains a number of foreign keys to various tables such as statuses, codes, etc. I was wondering, is it possible to have a lookup function? I would like to be able to call the function with a passed in id and have it run a sql query to return the value such that I can display the returned value.

 

Any help would be immensely appreciated.

 

Regards,

Dean.

 

[cbarlow3]

It sounds possible, but I can't really provide an example until I undertsand better what you're trying to do.  Here are four guesses what you might mean.  All four assume that you have two tables:  Employee( Id, Name, StatusId) (where Id is a String, Name is a String, and StatusId is a foreign key to the Id field of the Status table) and Status( Id, Description). Maybe Employee looks like this:

{ (1057, George, 2), (1635, Sally, 2), (1844, Mike, 3), (1044, Fred, 1) }  and Status looks like this:

{ (1, "Applying"), (2, "Currently Employed"), (3, "Terminated"), (4, "Retired"), (5, "Leave of Absence") }

1. Are you trying to output a list of employees, but put the status description instead of the value of the foreign key itself?  This doesn't require any input parameter, you just use a JOIN in your SQL query:

SELECT Employee.Id, Employee.Name, Status.Description FROM MySchema.Employee AS Employee INNER JOIN MySchema.Status AS Status ON Employee.StatusId = Status.Id

2. Are you trying to use the Status description as an input so you can filter the results of the previous report?  If you're looking for only a single status and the user can be counted on to type it in correctly, you could use the same query as before but just add  WHERE Status.Description=$P{Status} and have the user be prompted for a string parameter called $P{Status}. 

3. If you want to allow them to pick from a list, this can be done within JasperReports Server by first creating a query:  SELECT Status.Description FROM MySchema.Status AS Status ORDER BY Status.Description.  Then create an Input Control called Status that has a Type of either "Single Select Query" or "Multi-Select Query" (depending on whether you want the user to be able to only enter a single status description from a list or multiples).  If you created a Single Select Query as the basis for your Input Control and called the Input Control "Status", you can use the same query as from guess #2 above, only now the user won't have to type in the value of the String...they'll instead be presented with a list from which they can choose one value.  If you instead call the input control "StatusList" and make it a multi-select query, then you have to change the WHERE condition in your main report so that it compares the Status.Description to a list parameter instead of to a String parameter.  The syntax would be like this:

WHERE $X{ IN, Status.Description, StatusList }

4. Or are you trying to build a "report" where you enter a Status.Id field and the report returns to you the Status.Description?  That doesn't sound like a very useful report, but it's easy enough to build.  You create a parameter called $P{StatusId} that has the appropriate data type (String, integer, whatever the key field really is), and then you make the query be:

SELECT Status.Id, Status.Description FROM MySchema.Status WHERE Status.Id=$P{StatusId}

Hope one of those is close to what you're trying to do! :)

Carl

Post Edited by cbarlow3 at 06/30/2011 22:06

[sheppard.deangmail.com]

Hi Carl,

 

Thanks for the response, unfortunately neither of those options is what I am looking to do. I've achieved what I am looking to do through sub-reports, which works well but I'm not sure it is a best design practice.

 

Essentially, I have some reports that contain a foreign key, which is easy enough to do through a join in the report query. However, a couple of my reports have 10 references and I didnt want to join the table so many times. I thought it would be easier to have a field that called a SQL query directly which I could pass a parameter into. Which is now what I have done through sub-reports.

 

I'm probably not explaining this really well, but a field type of sqlfield would be ideal ;)

 

Thanks,
Dean.

www.power-project.ca