How can I get the All Company parameter using fetchxml

(0) 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!

All responses (4)

Answers (0)

truc binh 2 on at

Like (0)

Report

RE: How can I get the All Company parameter using fetchxml

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.
Suggested answer

Charan Raju C R 3,155 Super User 2024 Season 1 on at

Like (1)

Report
RE: How can I get the All Company parameter using fetchxml

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.
clyn 20 on at

Like (0)

Report

RE: How can I get the All Company parameter using fetchxml

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!
Suggested answer

Charan Raju C R 3,155 Super User 2024 Season 1 on at

Like (0)

Report
RE: How can I get the All Company parameter using fetchxml

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 """ />"))