Getting Optional Parameters for SSRS Report

Kinetic ERP
1

I have an SSRS report which is based on a BAQ Datasource. The BAQ has a number of parameters, some of which are optional and marked in the BAQ as “Skip Condition if Empty”. For example, I am creating a report of shipments over a time period, and entering the customer ID is optional. So, I have parameters of a From Date, a To Date, and a Cust ID which is optional. If they enter a Customer ID, they get just that customer. Or they can leave it blank and it will return all customers.

On the header of my report, I’m listing the parameters that the report ran with. I’m using the built-in Code that’s delivered with the SSRS report to get the prompt values and display them. So for example:

=Code.GetCriteriaPromptValue(First(Fields!UserCriteria.Value, “RptParameter”) , “ProdCode”)

The problem is that when the user doesn’t enter a Customer ID, the parameter doesn’t exist in the RptParameter, the code fails, and on the header it’s showing “error”. I’ve tried various tricks to try to get rid of/hide this. I’ve tried the “IsNothing” function, tried an iif statement with the IsError function, even tried to alter the visibility properties with an IsError function. So far, haven’t hit the magic trick.

Has anyone else dealt with this and have an easy solution. My “workaround” is to make the parameter mandatory, default the value to “ALL”, and have some logic in the BAQ that looks at the value to determine whether to select the records. But I’m thinking there must be a quicker solution without adding complexity to the BAQ.

Thanks!
Kevin Simon

2

In those types of situations, I usually found success with an OR in the first part of the IF statement. Something like “IsNothing or = ‘’”. Sorry I can’t be more specific, but I don’t have SSRS at the moment.

3

Try the formula below, works for me:

=IIF(len(Code.GetCriteriaPromptValue(First(Fields!UserCriteria.Value, “RptParameter”) , “ProdCode”))>0, Code.GetCriteriaPromptValue(First(Fields!UserCriteria.Value, “RptParameter”) , “ProdCode”), “”)

4

Thanks. I checked, and unfortunately that throws an error as well. When I look at the RptParameters UserCriteria field where the function pulls this data from, I think the issue is that the parameter doesn’t show up at all as a prompt, as opposed to being there but being empty.

Here’s a dump of the UserCriteria field:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<UICriteria>
<RptCriteriaSet>
<Company>DMP</Company>
<RptDefID>u-OntimeShips</RptDefID>
<RptCriteriaSetID>rptCriteria</RptCriteriaSetID>
<Description>Report Criteria</Description>
</RptCriteriaSet>
<RptCriteriaPrompt>
<PromptID>1</PromptID>
<PromptValue>2023-04-01T00:00:00</PromptValue>
<IsToken>false</IsToken>
<PromptName>fromDate</PromptName>
<DataType>datetime</DataType>
<Label>From Date</Label>
</RptCriteriaPrompt>
<RptCriteriaPrompt>
<PromptID>2</PromptID>
<PromptValue>2023-04-30T00:00:00</PromptValue>
<IsToken>false</IsToken>
<PromptName>toDate</PromptName>
<DataType>datetime</DataType>
<Label>To Date</Label>
</RptCriteriaPrompt>
<RptCriteriaPrompt>
<PromptID>5</PromptID>
<PromptValue>false</PromptValue>
<IsToken>false</IsToken>
<PromptName>showDetail</PromptName>
<DataType>bit</DataType>
<Label>Show Detail</Label>
</RptCriteriaPrompt>
</UICriteria>

Note that I have prompts for fromDate, toDate and showDetail (prompts 1, 2 and 5). Prompts 3 and 4 are optional, and since a value wasn’t entered, it’s not even listed in the code. If I had this in the code, I’d be golden. Somehow I need an “exists” type function.

<RptCriteriaPrompt>
<PromptID>3</PromptID>
<PromptValue></PromptValue>
<IsToken>false</IsToken>
<PromptName>CustID</PromptName>
<DataType>string</DataType>
<Label>Customer ID</Label>
</RptCriteriaPrompt>
5

You’re right, it doesn’t work. I’m not sure what I was doing yesterday :slight_smile: I tried it once and it seemed to work.
Anyway, another option would be to force it by adding error handling to that function. In SSRS, right click anywhere outside your page and select Report Properties, then select ‘Code’ on the left section.

Replace the function below:

Public Function GetCriteriaPromptValue(ByVal userCriteria As Object, ByVal promptName As String) As String
    If Me.promptValues Is Nothing Then
        Me.promptValues = FillPromptValues(userCriteria)
    End If

    Return Me.promptValues(promptName)
End Function

with this one:

Public Function GetCriteriaPromptValue(ByVal userCriteria As Object, ByVal promptName As String) As String
On Error GoTo err1
    If Me.promptValues Is Nothing Then
        Me.promptValues = FillPromptValues(userCriteria)
    End If

    Return Me.promptValues(promptName)

err1:
    Return ""
End Function

Another option would be to take a closer look at that code than I did and make it work properly.