RDD BAQ PARAMETER - Parse XML code

[amaragni (al maragni (USA - NY/NJ))]

In BAQRpts - the BAQR parameters could be pulled from the BAQReportParameter_GUID table by name. eg. FILTER1, OPTION2, CheckBox3 etc. and used in the SSRS

but in the new BAQ/RDD format these parameters are included in a single field - UserCriteria - stored within 4 tagged XML fields as data values for PromptID, PromptValue, PromptName, and Label.

so if i had two parameters i would see a string field of

<RptCriteriaPrompt>      
<PromptID>2</PromptID>      
<PromptValue>2019</PromptValue>      
<IsToken>false</IsToken>      
<PromptName>PromptYEAR</PromptName>      
<DataType>int</DataType>      
<Label>Fiscal Year</Label>    
</RptCriteriaPrompt>    

<RptCriteriaPrompt>      
<PromptID>1</PromptID>      
<PromptValue>12</PromptValue>      
<IsToken>false</IsToken>      
<PromptName>PromptPERIOD</PromptName>      
<DataType>int</DataType>      
<Label>Fiscal Period</Label>    
</RptCriteriaPrompt>  
</UICriteria>

Since the RDD prompts are related to BAQ parameters, I am able to create calculated fields in the BAQ based on the prompt value, and then pull them into the SSRS from there from the result data set.
But i am considering does anyone out there have code to extract/parse these individual parameters directly from the new RDD/BAQ parameter data set field. And make them available for use in the SSRS report?

TIA

[John_Mitchell]

@hkeric.wci had some stuff in the original thread but I’m not sure how far he went with it.

[amaragni (al maragni (USA - NY/NJ))]

Thanks, I never realized that code was available.
Now I am able to pull my report options out of the user criteria by name. Much Appreciated!!!

=Code.GetCriteriaPromptValue(First(Fields!UserCriteria.Value, "RptParameter"),"SYEAR")

[utaylor (Utah Taylor)]

Now if the filter is multivalued.

Been a minute since I’ve had to do this.

[utaylor (Utah Taylor)]

Maybe @hkeric.wci came up with something much more precise, but a ton of the VB types I want to use says I can’t in SSRS.

So I don’t know if it’s cause I didnt’ make the report style and report data definition in the right order, but none of my filters work or are included in the code in SSRS rdl.

So I expanded on their report criteria logic and did it for the filters.

Here’s the thing, these filters can be multivalued… so you need to concatenate them to be used in an IN clause for an SSRS filter.

To concatenate a filter that you have you can use the code below.

Just replace “EmpID” in the code below with the name of your multivalued filter.

I don’t have the code worked out for the filter expression that you’ll have to use if it’s empty so that it returns everything.

Dim filterIndex = 0
Dim concatenatedEmpIDs As String = "" ' Initialize an empty string to store concatenated EmpIDs

While True
    filterIndex = userCriteria.IndexOf("<RptCriteriaFilter>", filterIndex + 20)
    If filterIndex < 0 Then
        Exit While
    End If

    Dim filterName = GetElementValue(userCriteria, filterIndex + 20, "FilterName")

    ' Check if the filterName is "EmpID"
    If String.Equals(filterName, "EmpID", StringComparison.OrdinalIgnoreCase) Then
        Dim filterValuesStartIndex = userCriteria.IndexOf("<FilterValues>", filterIndex + 20)
        Dim filterValuesEndIndex = userCriteria.IndexOf("</FilterValues>", filterValuesStartIndex)

        If filterValuesStartIndex >= 0 AndAlso filterValuesEndIndex > filterValuesStartIndex Then
            Dim filterValuesContent = userCriteria.Substring(filterValuesStartIndex, filterValuesEndIndex - filterValuesStartIndex)

            ' Extract and append each <FilterValue> value
            Dim filterValueIndex = 0
            While True
                filterValueIndex = filterValuesContent.IndexOf("<FilterValue>", filterValueIndex)
                If filterValueIndex < 0 Then
                    Exit While
                End If

                Dim filterValueEndIndex = filterValuesContent.IndexOf("</FilterValue>", filterValueIndex)
                If filterValueEndIndex >= 0 Then
                    Dim filterValue = filterValuesContent.Substring(filterValueIndex + 13, filterValueEndIndex - filterValueIndex - 13)

                    ' Append the filterValue to the concatenatedEmpIDs string, separating with commas
                    If concatenatedEmpIDs.Length > 0 Then
                        concatenatedEmpIDs &= ","
                    End If
                    concatenatedEmpIDs &= filterValue

                    filterValueIndex = filterValueEndIndex + 1
                Else
                    Exit While
                End If
            End While
        End If
    End If
End While

' Now, concatenatedEmpIDs contains all the FilterValues separated by commas
values.Add("ConcatenatedEmpIDs", concatenatedEmpIDs)

You’d put that code in the custom code section of your SSRS report and then use the statement that @amaragni put above to get it back out. Using the code above you’d call the following code to get the concatenated empIDs.

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

[utaylor (Utah Taylor)]

I think I’m gonna have to go back to @hkeric.wci 's SQL statements instead. Getting these filters out to be used in filters on datasets is tough given the field is in a different dataset than the main dataset.

[utaylor (Utah Taylor)]

I had to add the UserCriteria field to my report query. I only ever saw one row in the table for my report so I don’t know if that’s the best practice here. Maybe there will be two rows in some other instance.

Now I can use the concatenated values to filter my dataset because usercriteria is now a field in my dataset and not a field in the RptParam dataset.

="SELECT (SELECT TOP 1 [UserCriteria] FROM dbo.[RptParameter_" + Parameters!TableGuid.Value + "]) as [UserCriteria],T1.[EmpBasic_EmpID], T1.[EmpBasic_JCDept], T1.[EmpBasic_Name], T1.[Indirect_Description], T1.[Indirect_IndirectCode], T1.[LaborDtl_ClockInDate], T1.[LaborDtl_ClockInMInute], T1.[LaborDtl_ClockinTime], T1.[LaborDtl_ClockOutMinute], T1.[LaborDtl_ClockOutTime], T1.[LaborDtl_DspClockInTime], T1.[LaborDtl_DspClockOutTime], T1.[LaborDtl_EarnedHrs], T1.[LaborDtl_IndirectCode], T1.[LaborDtl_LaborHrs], T1.[LaborDtl_LaborNote], T1.[LaborDtl_PayrollDate], T1.[LaborHed_ActLunchInTime], T1.[LaborHed_ActLunchOutTime], T1.[LaborHed_ActualClockinDate], T1.[LaborHed_ActualClockInTime], T1.[LaborHed_ActualClockOutTime], T1.[LaborHed_ClockInDate], T1.[LaborHed_ClockInTime], T1.[LaborHed_ClockOutTime], T1.[LaborHed_DspClockInTime], T1.[LaborHed_DspClockOutTime], T1.[LaborHed_LunchInTime], T1.[LaborHed_LunchOutTime], T1.[LaborHed_PayHours], T1.[LaborHed_PayrollDate], T1.[LaborHed_Shift]
 FROM DATASETNAME_" + Parameters!TableGuid.Value + " T1
"

[utaylor (Utah Taylor)]

And this is how to use that field in the dataset filter and how to make it return all results if it’s empty.

I suppose you could do all this in a calculated filter expression in a BAQ if you just added this as a parameter. Idk. Just playing around.

Here’s the top expression for Expression:

=IIF(Code.GetCriteriaPromptValue(Fields!UserCriteria.Value,“ConcatenatedEmpIDs”) = "","1",Fields!EmpBasic_EmpID.Value)

Here’s the bottom one for the value:

=IIF(Code.GetCriteriaPromptValue(Fields!UserCriteria.Value,“ConcatenatedEmpIDs”) = "","1",Split(Code.GetCriteriaPromptValue(Fields!UserCriteria.Value,“ConcatenatedEmpIDs”),","))

[dr_dan (Dan Ramirez)]

Thank you for posting and helping me to find a solution. Just in case anyone else copies and pastes from this post, the double quotes in the below excerpt are not actual double quotes… they are the little wavy version ones that don’t actually work when pasting into the SSRS report builder. LOL

@utaylor

You’d put that code in the custom code section of your SSRS report and then use the statement that @amaragni put above to get it back out. Using the code above you’d call the following code to get the concatenated empIDs.

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

[utaylor (Utah Taylor)]

that could kill someone haha, I don’t know what I can do to make them real quotes!

[utaylor (Utah Taylor)]

Just wanted to add that you can create your own dataset in the report and/or just add this to the where clause in your main dataset query since SQL supports XML. you just need to know how your xml is being formatted in the BAQRptParameter table.

SELECT
XmlData.value(‘(Customer_CustID/text())[1]’, ‘VARCHAR(MAX)’) AS CustIDs,
XmlData.value(‘(ProdGrup_ProdCode/text())[1]’, ‘VARCHAR(MAX)’) AS ProdGrups
FROM (
SELECT CAST(Filter2 AS XML) AS XmlData
FROM BAQReportParameter_0eb5a4db866a423a83f5022e436aa9d5
) AS ParsedXml

This snippit above gets both my filters and put the value in their own fields called CustIDs and ProdGrups, respectively. You could use this however you want, but it might be easier for some to do it in SQL than VB code.