sheppard.deangmail.com Posted June 28, 2011 Share Posted June 28, 2011 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. Link to comment Share on other sites More sharing options...
cbarlow3 Posted June 30, 2011 Share Posted June 30, 2011 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.Id2. 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! :)CarlPost Edited by cbarlow3 at 06/30/2011 22:06 Link to comment Share on other sites More sharing options...
sheppard.deangmail.com Posted July 4, 2011 Author Share Posted July 4, 2011 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now