Service | Customer Service, Contact Center, Fie...

How can I get the All Company parameter using fetchxml

(2) Share

Report

Posted on by clyn

Hi Expert,

I have an SSRS using FetchXML. Im having an issue getting the "All" company in my parameter.

In my parameter I have a value of ABC and DCE and ALL. I can get the ABC and DCE except for ALL(company) since I don't have a code or guid set on the value since we have a lot of records for company and can get increase.

How can I get all the value in the company table?

This is my XML filtering the company parameter.

Thank you!

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Charan Raju C R 7 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi Clyn,

We can dynamically set the fetch xml condition based on the parameter selection. Please follow the below steps to achieve it.

1. Set value of label 'All' under param_company parameter to All

2. Create a new text parameter with name Query and set below value as a default value which contains #Condition keyword to be replaced later in query expression. This parameter's visibility can be set to hidden once you confirm it is working as expected. I kept below value in a single line as it should not contains new line.

#Condition

3. Open your dataset which contains fetch XML query

4. Click on fx button next to Query text box

5. Enter below expression and run the report

=Replace(Parameters!Query.Value, "#Condition", IIF(Parameters!param_company.Value="All", "<condition attribute=""new_company"" operator=""not-null"" />", "<condition attribute=""new_company"" operator=""eq"" value=""" Parameters!param_company.Value """ />"))

Was this reply helpful? Yes No
clyn 26 on at

Like (0)

Report
Copy link

Link copied!

Hi Charan,

I got an error message. Im not sure if I did the steps correctly.

In my dataset. I remove the filter (<filter type="and" >
<condition attribute="new_company" operator="eq" value="param_company" />
</filter>) then add this and add this =Replace(Parameters!Query.Value, "#Condition", IIF(Parameters!param_company.Value="All", "<condition attribute=""new_company"" operator=""not-null"" />", "<condition attribute=""new_company"" operator=""eq"" value="""+Parameters!param_company.Value+""" />"))

In my parameter this is the new value

Please let me know what I did wrong.

Thank you so much!

Was this reply helpful? Yes No
Suggested answer

Charan Raju C R 7 Moderator on at

Like (1)

Report
Copy link

Link copied!
Value for parameter Query (not query in dataset) should contains complete fetch xml query without condition. To insert this condition later at dataset's query we added #Condition keyword.

param_company parameter value for All is not correct. It should be set to All

Below expression should be provided under dataset's query expression

=Replace(Parameters!Query.Value, "#Condition", IIF(Parameters!param_company.Value="All", "<condition attribute=""new_company"" operator=""not-null"" />", "<condition attribute=""new_company"" operator=""eq"" value="""+Parameters!param_company.Value+""" />"))

This approach is simple. We are storing our complete fetch XML query under new parameter called "Query". This fetch XML includes a keyword called #Condition, but not actual conditions. Dataset's query is using expression to replace this #Condition keyword based on the selection in param_company parameter. If 'All' is selected under param_company parameter, sets a condition to retrieve all the records, otherwise, sets the selected record id in filter condition.

Was this reply helpful? Yes No
truc binh 12 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I do the same but when I add report into Dynamics 365, it raises "Invalid fetch xml". Do you have the same problem?

Regards,

Binh.

Was this reply helpful? Yes No
Suggested answer

panug 9 on at

Like (0)

Report
Copy link

Link copied!

@Charan Raju C R

thank you for your post.

I am working on the similar requirement. But i am getting error when i am uploading this rdlc report to Dynamics with below expression.

=Replace("Sample Text", "#Condition", "")

I am just including above replace function in my dataset query expression. Actual my QueryParameter valid fetchXML, no issue with this.

Thank you

Was this reply helpful? Yes No