Blog

Created on Thursday, 18 May 2006 01:42

Written by Larry Grey

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:

1. Figuring out how to pass parameters when running an nVision report. Unfortunatly, nVision only knows how to handle the parameters it knows about in the report request (as of date, requesting business unit, report title, etc). If you can't even pass in a parameter, you won't be able to do anything with it.
2. Figuring out how to use the parameter inside the nVision report. Depending on what you want to accomplish, there are a couple of approaches you can take.

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:

1. It could join the parameters to the data table and filter the date.
2. It could be put into its own tabular layout in the report and control excel logic (i.e. be used in an excel function, etc.)

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:

1. Join the parameter table to the data table and use the parameter values to filter the results in the data table. This is probably the most common use of it. This means using common SQL techniques.
2. Putting the parameters into Excel for use. Although you can include those parameters when joining to the data table for use, you can also embed a tabular worksheet in your nVision report that returns the parameters. Once in Excel, you can use those parameters for the following:
   
   
   • To control formatting in the report
   
   
   • To pass parameters into macro code in the report (see instancehooks).
   
   
   • To be used in calculations or functions in the report (such as passing a rate to use, or changing the results of an =if() function)

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:

1. It doesn't work on date fields.
2. It is limited to selecting values or tree nodes (no ranges, not exists, etc).
3. Scopes can be cumbersome when there are a lot of them (the key is 8 characters, and there is no security on them... this means that it's hard to find the one you want to use).

Other options:

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