Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Service | Customer Service, Contact Center, Fie...
Suggested answer

How can I get the All Company parameter using fetchxml

(2) ShareShare
ReportReport
Posted on by 26

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.

pastedimage1624353864046v1.png

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

This is my XML filtering the company parameter.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="new_companyenrollment" >
<attribute name="new_companyenrollmentid" />
<attribute name="new_company" />
<attribute name="new_staffname" />
<attribute name="new_customername" />
<filter type="and" >
<condition attribute="new_company" operator="eq" value="@param_company" />
</filter>
</entity>
</fetch>

Thank you!

  • Suggested answer
    panug Profile Picture
    9 on at
    How can I get the All Company parameter using fetchxml
    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
     
     
  • truc binh Profile Picture
    2 on at
    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 Profile Picture
    7 Moderator on at
    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.

               Report_2D00_Param2.png

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

               Report_2D00_Param3.png

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

          Report_2D00_Param4.png

    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 Profile Picture
    26 on at
    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.

    pastedimage1624364033580v1.png

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

    pastedimage1624364777863v7.png

    In my parameter this is the new value

    pastedimage1624364312418v3.png

    pastedimage1624364471475v4.png

    Please let me know what I did wrong.

    Thank you so much!

  • Suggested answer
    Charan Raju C R Profile Picture
    7 Moderator on at
    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 """ />"))

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Service | Customer Service, Contact Center, Field Service, Guides

#1
Muhammad Shahzad Shafique Profile Picture

Muhammad Shahzad Sh... 51 Most Valuable Professional

#2
Ramesh Kumar Profile Picture

Ramesh Kumar 42

#3
David Shaw_UK Profile Picture

David Shaw_UK 27

Featured topics

Product updates

Dynamics 365 release plans