Old RDL Parameters
This is the old way of doing parameters. It has been moved to this separate page to make the main page look cleaner. I honestly don't even know if it works any more. It would be better to use the new parameters functionality.
Typically, when you run a report, you only want results for a certain date range, or maybe for only one provider. Values like this, which the user must provide at the time the report is run, are known as parameters. The most common parameters are dates, but there are also many other useful types of parameters. This section describes how to include parameters in your report.
For now, there is no easy user interface for adding parameters. Instead, there is special functionality built-in for including parameter definitions as part of the query. For this to work, a code module for ODR needs to be added to the XML first as described further up on this page. When first creating the report in the designer, you should try to stick to a simple query that includes all of the columns that you are interested in, because this triggers automatic creation of the field objects for the report. Then, you can later go back into the query to add the parameters. To access the query later, go to Main Menu | Data | Data Sets | Data (or similar). Ignore the Query Parameters tab; we will never use that. Instead, edit the query directly. As an example, here's what a simple query with one parameter might look like:
=Query.Get("SELECT * FROM patient WHERE ?patstatus LIMIT 1000","Name=patstatus;ValueType=Enum;Prompt=Patient Status;Snippet=PatStatus=?;EnumerationType=PatientStatus")
The above text would be typed or pasted into the box where the query would normally go. Here's a breakdown of what the above text means:
This is an instance function. So you have to manually add a class definition to the XML text. It might look something like this when you are done:
Notice that there are two class definitions in the above example. The second one is the one that defines the Query function that we are interested in.
"SELECT * FROM patient WHERE ?patstatus LIMIT 1000"
For the most part, this is just a standard query. The only exception is the '?patstatus' portion. If you are not familiar with how to write queries, you might have to get a book. You can also use various queries we have provided as a starting point. Now, imagine the above query looking more like this:
SELECT * FROM patient WHERE PatStatus=2 LIMIT 1000
So ?patstatus will be replaced by PatStatus=2 when the query actually gets run, assuming the user picks 2 when running the report. ?patstatus is the only parameter in the above query, but you can have more than one. Also, the user might pick multiple statuses, making the query look more like this:
SELECT * FROM patient WHERE (PatStatus=2 OR PatStatus=3) LIMIT 1000
This defines the parameter. There are 5 fields for each parameter:
Name: The way that it will show in the main query with a preceding ?
ValueType: Options are Date, String, Boolean, Integer, Number, Enum, or QueryData. This is the type of value that the parameter is. Date is self explanatory. String includes any ordinary text. Boolean means true or false, and user is presented with a checkbox. Integer means a number with no fractions. Number is for currency and other fractional numbers. In the database, these are called doubles. Enum stands for enumeration, which means a predefined pick list. There are many such lists in Open Dental, patient status being a good example. Enums are always hard coded and the user cannot change the pick list. QueryData is not functional yet, but the idea is that you can supply your own pick list. You will have to create a second query (remember that the RDL format supports unlimited named queries in a single report). The results of the query will fill the pick list. For instance, "SELECT ProvNum,Abbr FROM provider WHERE IsHidden=0 ORDER BY ItemOrder" would give the user a list of providers to pick from.
Prompt: This is the explanation that will be provided to the user running the report so that they will know what kind of data to enter into the parameter.
Snippet: This is the SQL that will be substituted into the query. It also includes a question mark. The question mark in the snippet will be replaced with a value before substituting. A simple snippet might consist solely of the question mark with no other text. Remember that multiple snippets can be chained together if the user selects multiple values from a pick list. If the ValueType is Boolean, then do not include a question mark. If the user checks the box, then the snippet will be included. If they do not, then it won't be included. So a boolean does not necessarily have to correspond with a value in the database. Because of the way this is implemented, the snippet can specify a true or false value, and the user can select whether to include the snippet.
EnumerationType: Only required if the ValueType is Enum. At the moment, the only enumeration type supported is PatientStatus. This will soon be enhanced to support all enumeration types in the entire program. You might need to consult the documentation in order to find the name of the enumeration you need. We will also try to post a list here.
Notice that when defining a parameter, that each field is separated by a ; with no spaces on either side. There are also no spaces on either side of the = after the field names. You can have multiple parameters in a single report. The additional parameters would be contained within the same double quotes as the first parameter. Each parameter definition would be separated by a ~ character with no spaces on either side.
If you use parameters in multiple queries in the same report, only one of the queries can define all parameters for the report. The other queries in the report can either be simple queries, or they can also contain parameter names within their query. But the additional queries cannot define any parameters or make a call to Query.Get(). They can just make use of the parameters defined in the main query. You can, therefore, make use of each parameter in whichever queries you want.
To retrieve the value of the parameter for use in the report, you can use a function like this: