I modified the options page of a report to allow both lookup and manual entry of a dimension and it works fine from design mode. After running the report, the options page lookup and manual entry select the correct cells properly, but the report always defaults back to the dimension code that was entered when I had the report open in design mode and does not use the dimension selected and shown on the options page. Is there something that I need to change on the Options page for the selected dimension to update each time I select a new dimension in report mode? I have attached the file.
Date
Votes
6 comments
-
Jet Reports Historic Posts I think the reason this is happening is because you have NL(Lookup) formulas on your options page, but your options sheet isn't actually set up to use the Jet Report Options feature. You don't have Title, Value, and Lookup tags in row 1, so your NL(Lookup) functions aren't actually doing anything. However, because there are Jet functions on the sheet and because Jet isn't recognizing it as using report options, this sheet is being treated like a report sheet by Jet.
I think the solution to this is pretty easy. Since the NL(Lookup) functions aren't actually doing anything for you, you should just delete them off the Options sheet. Then that sheet won't have any Jet formulas on it, and Jet won't treat it like a report sheet. Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Greetings Hughes, Thanks for your response. Originally I had the report set up as attached this time. I was trying to use the functionality of the JET Reports Options, but the report seems to fault after a few runs. The report worked four times and then corrupted in that the data in cells C10 and C12 ended up in C25 and C27. Can you see a reason for the attached report not to function consistently? Bob T
-
Jet Reports Historic Posts Hi Bob,
I have no idea why that would happen. If you can reproduce it, you should definitely create a ticket with Jet Reports Support for it, since it could be an issue that needs to be fixed.
It is a little odd that you've got Excel lookups on the same cells that the Jet lookups are going to write to (the Value column). I have no idea how this could affect things, so if you're going to use Jet Report Options, I would get rid of the Excel drop downs. The other thing that's slightly odd is that your Jet Lookup formulas have the same range for the Field argument (the 3rd function argument) as for the Table argument. The Field argument for a lookup with a table array should be the title of the lookup column, so it shouldn't be an array of values in this case. So you could change the formula in cell D10 to something like this:=NL("Lookup",G28:G45,"Project - Lookup")
I'm not sure if this will help, but like I said if you can figure out how to reproduce your problem, you should definitely make a ticket for it on the Jet Reports support site.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
I have removed the excel lookups (drop downs) and changed the field and table arugments so that they are no longer the same, but the report still operates the same as it did previously. The first time that I run the report from design mode it allows me to select a project code, but after that the up arrow is greyed out and I can't select the project. Do you have any other ideas on how to clean up the report as well as why the lookup function does not continue to work after the first run of the report? Thanks, Bob T -
Jet Reports Historic Posts Hi Hughes,
In regard to the report we have been discussing, is there an easy way to switch a report between a lookup function over a limited range of a given dimension and a manual entry of the same dimension? That is what I have been trying to do with this report and it has not worked, so I am wondering if you have a better suggestion on how to accomplish it. I expect that it would be easier to make it a lookup over the entire range of the dimension but that becomes a very long list to sort through? Would appreicate any suggestions. Bob T -
Jet Reports Historic Posts Hi Bob,
I have been on vacation for the last week, so I haven't been able to reply to you. There is a setting on the General page of the Jet Application Settings to adjust the lookup scan limit and record limit. Increasing those values sounds like it will help with the problem you're experiencing as far as not seeing all the values. The gotcha with increasing these values is that the lookup can take longer if Jet is going through more records, so just be aware of that.
It sounds like maybe you should create a ticket with the Jet Support site so that they can look into the other issue you're having with the Report Options.
Regards,
Hughes
Please sign in to leave a comment.