This is a common question from our loyal blog readers, which finally deserves its own posting.
Query with Prompts… What’s the issue? Good question. The issue is that in PeopleTools 7.5, customers could use queries with runtime parameters in them in nVision reports. Because nVision ran on the client (and not the server), the client code would have the ability to display a dialog box prompting for the values the query needed to run. When we moved to the web, this dialog could not be displayed at runtime, and therefore, there was no way to accomplish this.
Why would somebody need to do use a query with prompts? Another good question. It gives you the ability to dynamically change the filtering of the report to be run. You see, in nVision unless you modify the report, modify the query, or apply the scope you don’t have much flexibility in changing the results.
A nice workaround to the fact that timespans are only supported in ledger-based reports would be to allow the user to specify a date range in query prompts that cause rows to be returned within that date range. Basically, any time you would want to pass parameters into the report specified when you run it or pick at runtime the set of data to use in the report could be solved with this feature.
Sovling the problem… The solution to the problem has two major parts to it:
Passing in Parameters
Probably the easiest way to pass parameters is to create your own table intended to capture those parameters and use it in conjunction with a query in the report. The query could be used in one of the following ways:
I’ve seen examples of both.
Okay, so now that we’ve agreed that we can create a table, let’s go the next step and design the table and page intended to be used. So, the next question is: how would nVision know which set of paramters to use, especially since there can be multiple people running reports at the same time. The easiest answer I was able to come up with is to leverage the row level security hook in PeopleTools.
How would Row Level security cause nVision to use paramters????
It takes a little thinking out of the box, but if you understand that row level security automatically filters on oprid when it is a key in a table, you can use that to pick the right row with the right parameters. In other words, if your parameter table is keyed by OPRID and contains columns for the parameters you want to pass, then nVision will automatically filter on OPRID for the user running the report. This means the user can go to the parameters page, update the values, save it, and run their nVision report.
One other thing to note is that you have the ability to use PeopleCode in the page to help pass parameters. For example, you could prompt the user on a timespan, but actually save the from date and the to date resolved from the timespan. This gives you a lot of options that you may not already be considering.
Pretty cool, right?
Using the Parameters
So, now that you know how to give your users the option of setting parameter values for running a report, the next step is using them. As briefly mentioned above, you can do the following:
A couple of other options I looked at for solving this is to run a report to get the parameters from the table and then run it again to use them. This is problematic, because it adds complexity in scripting the sequence and modifying an instance to be used as a report. However, there’s another simple way (but much more restrictive way) of accomplishing this….
Using a Scope A scope is a dynamic filter that can be applied to a report. One option is to create a scope definition that has your filtering rules in it and pick it in your report request. You can use the scopefield variables to embed attributes of the scope value used into the report.
Although this is cool, it has several limitations:
One final note is that we’re in the process of building this feature as part of a larger nVision bolt-on. You can think of the bolt-on as a product that solves all the limitations we’ve identified in nVision. One thing our solutions will provide is a means for doing this without requiring you to do a lot of work. Our nVision drilling enhancements is an example of another product where we took a something we showed you how to build yourself in the blog and developed a more powerful, simpler, more intuitive, and lower-maintenance means of solving it.
Labels: nVision, Query