A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Friday, September 18, 2009

Enumerating Options using VBScript

ActiveReports is limited in the ways that a user can input parameters at run-time. At least it is limited in the way it has been implemented for Shelby v.5's ShelbyQUERY application. In fact the only way to capture a parameter at run-time is to use VBScript's InputBox() function. This is serviceable for many contexts, but it can be a problem when the parameter is a long text string, such as the name of an Event from the Registrations application or a company name from the General Ledger. In these cases the user must remember the exact wording of the text and then type it in without any mistakes in order to match the value in the query results exactly.

Error checking can alert the user that the value has been typed incorrectly, but it would be best to help the user along by prompting him or her with what the acceptable values are. In fact it would be ideal to give the user an enumerated list of options, so the user only has to choose the number of the desired value and enter one number instead of typing a long text string. The script below will do just that.

This script should be associated with the ActiveReports Document object and the OnReportStart event. That way it will pop up at run-time and provide a filter for the contents of the report.

After you paste the script into your VBScript editor window, be sure to update the value of the strColumnName variable so that it corresponds to the desired column in your actual query results. Also change the strPromptMessage and strPromptTitle variables so that the InputBox() prompt is worded appropriately for your report.

Sub OnReportStart

Set oDict = CreateObject("Scripting.Dictionary")
Set rsQuery = rpt.DataSource_Shelby_.RecordSet

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

intCounter = 1
strCurrItem = ""
strTestItem = ""
strColumnName = "QueryColumnName"
strPromptMessage = "Enter the prompt message here."
strPromptTitle = "Enter the Prompt Title here"

strCurrItem = rsQuery(strColumnName)
If strCurrItem <> strTestItem Then
oDict.Add intCounter, strCurrItem
strList = strList & vbCR & intCounter & " - " & strCurrItem
strTestItem = strCurrItem
intCounter = intCounter + 1
End If
Loop Until rsQuery.EOF

intChoice = InputBox(strPromptMessage & vbCr & strList, strPromptTitle)
If intChoice <> "" and IsNumeric(intChoice) Then
strItemChoice = oDict.Item(CInt(intChoice))
rsQuery.Filter = strColumnName & " = '" & strItemChoice & "'"
If rsQuery.RecordCount > 0 Then Exit Do Else rsQuery.Filter = ""
End If
If intChoice = "" Then Exit Do



MsgBox("There are no results to use for this report. Please check the query and try again after you have a set of results.")

End If

End Sub

With this code in your ActiveReports document, your end-user will have a much easier time filtering the report without have to double-check the spelling of a long text value and will avoid frustration of locating a tiny typo in the string.

1 comment:

  1. Fixed a bug in the script that would allow invalid values to be accepted by the input prompt.