We have four prompts in a report. We want to be able to specify the values in some prompts but not others at will. We cannot change the database values. If we do not specify a value for a given prompt, we want it to return all values for that prompt. For example, a report has the following prompts: Process Date, Project Type, Account, Client Location. If we fill in only Process Date and Client Location, we want to see all Accounts and Project Types for the specified Client Location on the specifies Process Date. If we run the report again, we may want to only fill in the Project Type and Process Date, in which case we would want to see all Client Locations and Accounts for the specified Process Date and Project Type. Is there a way to accomplish this?
Another way is you can use like fallowing VBA macro at report level to set the default values for prompts:
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Call Default
End Sub
Sub Default()
For i = 1 To ActiveDocument.Variables.Count
If ActiveDocument.Variables.Item(i).IsUserPrompt = True Then
If ActiveDocument.Variables.Item(i).Name = "Enter Country" Then
ActiveDocument.Variables.Item(i).Value = "USA"
End If
End If
Next i
End Sub