Search code examples
reporting-servicesparametersdynamics-crmreportfetchxml

SSRS - The report parameter has a defaultValue or a ValidValue that depends on the report parameter

In an SSRS report, I have a fetchXML query that is dependent on the parameters that the users will choose from the UI that are defined in the report:

    <fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="new_r10logentries">
    <attribute name="new_quarter" />
    <attribute name="new_cr884_company" />
    <link-entity name="cr884_company" to="new_cr884_company" from="cr884_companyid" alias="cr884_company" link-type="inner">
      <attribute name="cr884_company" />
      <attribute name="cr884_companyid" />
      <filter>
        <condition attribute="cr884_company" operator="eq" value="@CompanyParam">
          <value />
        </condition>
      </filter>
    </link-entity>
    <filter>
      <condition attribute="new_quarter" operator="eq" value="@QuarterParam" />
    </filter>
  </entity>
</fetch>

The parameters settings that I have are the below: enter image description here

The companyParam is based on the dataset of the fetchXML and will populate the list of the companies' names. The quarterParam is a list of options that are not based on the dataset.

When trying to publish/preview the report I got the below error: enter image description here

Knowing I am passing the param as per the above query and the company column name that I am querying against is of type text and not GUID:

enter image description here

I hardcoded the company name in the query it works perfectly and in the parameters, it populates the list of the companies in the UI. However, when I pass the param in the fetchXML nothing happens as the CompanyName in the UI display null values.

Could you please support solving this issue? I have spent 3 hours changing in query with no result.

Resources that I look for support:

Input parameter SSRS

Community Dynamics

Any help is greatly appreciated.

Thank you!

Solution

  • your CompanyParam is list of companies which you retrieve from Dataset1 (from screenshot--> get values from query).

    You pass this list(companies) selected one comapny by user to same dataset1 to retrieve your result and this is your circular reference.

    Create a new Dataset "DatasetRetrieveCompanies" This dataset will give you all companies based on your fetchxml. Then add this dataset to your CompanyParam (get values from query). This should most probably solve your issue