SSRS custom reports, how to filter by multiple field values

(0) Share

Report

Posted on by TrevorB

105

Hi All Using Dynamic 365 online V9. I’m trying to create a custom report in VSTS that has two parameters but I want the parameters to be OR rather than AND. DataSet 1 has: a Contacts column: an Account the Contact is related to column: an Account the Contact is connected to column. DataSet 2 gets a list of the Accounts the Contacts are related to DataSet 3 gets a list of Accounts the Contacts are connected to. I have created two parameters allowing multi select values, one based on DataSet 2 and the other based on DataSet 3 Even if I select ‘All’ in both my parameters this only return records where the selected parameter 1 Accounts are in the Accounts the Contacts are related to column AND where the selected parameter 2 Accounts are in the Accounts the Contacts are connected to column. I know this is the logical result but I need to show records where the Account is in either where Accounts are in the Accounts the Contacts are related to column OR where the Accounts are in the Accounts the Contacts are connected to column. Do I need to have two parameters that are somehow connected with an OR statement or is there some way of combining this into a single parameter. Done a fair bit of searching online and I did come across this article which looked like it might help denglishbi.wordpress.com/.../reporting-services-ssrs-or-filter-logic But when I run my report I get the following error

Not even sure if this is on the right track or if I am just missing something.

Hope this all makes sense. Any help would be greatly appreciated.

Regards Trevor

*This post is locked for comments

All responses (4)

Answers (0)

TrevorB 105 on at

Like (0)

Report

RE: SSRS custom reports, how to filter by multiple field values

Hi Alex

Thanks for your suggestion. I have been battling away at this for quite some time and have only managed to part solve the problem.

Firstly, I think I tried to over engineer my solution. After taking a much closer look at my data set it became clear that I didn’t need 2 parameters at all as any value that is in ConnectedContact:name will be in AccountContact:name anyway.

I managed to get this working in part. It work fine as a single value paramater once I figured out how to write the expression correctly that basically said if AccountContact:name OR ConnectedContact:name is = to Parameter1, then return results.

After a bit of trial and error I figured out the expression:

=Fields!AccountContact_name.Value = Parameters!ReportParameter1.Value OR Fields!ConnectedContact_name.Value = Parameters!ReportParameter1.Value

Set Operator to = and Value to true and the Expression type to data type Boolean

This works fine on a single value parameter.

However I can’t get it to work using a multi value parameter. I have tried using the =join function as I understand it from the link provided. My limited understating of SSRS and adapting from the SQL example to FetchXML may have me going about this the wrong way.

Below is what I have tried so far so if anyone can point out where I am going wrong I would really appreciate it as I have spent way too much time trying to solve it.

I added Dataset1 as using the query I posted before:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >

<entity name="contact" >

    <attribute name="fullname" />

    <attribute name="contactid" />

    <attribute name="pirsa_regionid" />

    <attribute name="jobtitle" />

    <filter type="and" >

      <condition attribute="statuscode" operator="eq" value="1" />

    </filter>

        <link-entity name="pirsa_contact_industry" from="contactid" to="contactid" visible="false" intersect="true" >

      <link-entity name="pirsa_industry" from="pirsa_industryid" to="pirsa_industryid" alias="ak" >

        <filter type="and" >

          <condition attribute="pirsa_industryid" operator="eq" uiname="RDA-LGA-Staff-Member" uitype="pirsa_industry" value="{87DDF6E8-7614-E911-A968-000D3AD24077}" />

        </filter>

      </link-entity>

    </link-entity>

    <link-entity name="account" from="accountid" to="parentcustomerid" visible="false" link-type="outer" alias="AccountContact" >

      <attribute name="name" />

<order attribute="name" descending="true" />

    </link-entity>

    <link-entity name="connection" from="record1id" to="contactid" link-type="outer" alias="ConnectedContact" >

      <attribute name="name" />

      <attribute name="record1roleid" alias="ConnectedContactRole" />

    </link-entity>

</entity>

</fetch>

And Dataset2 as

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >

<entity name="account" >

        <link-entity name="contact" from="parentcustomerid" to="accountid" alias="Contact" >

      <link-entity name="pirsa_contact_industry" from="contactid" to="contactid" link-type="inner" alias="ContactIndustry" intersect="true" >

        <link-entity name="pirsa_industry" from="pirsa_industryid" to="pirsa_industryid" link-type="inner" alias="Industry" >

          <filter type="and" >

            <condition attribute="pirsa_industryid" operator="eq" value="{87DDF6E8-7614-E911-A968-000D3AD24077}" />

          </filter>

        </link-entity>

      </link-entity>

      <link-entity name="account" from="accountid" to="parentcustomerid" link-type="outer" alias="AccountContact" >

        <attribute name="name" />

        <order attribute="name" descending="false" />

      </link-entity>

    </link-entity>

</entity>

</fetch>

I added a single parameter called ReportParameter1

Setting it to Allow multiple value

Added dataset2 as the available values

To the dataset filter I have added the expression below

=Fields!AccountContact_name.Value orElse Fields!ConnectedContact_name.Value

Setting data type to: Text

Operator: In

Value: [ReportParameter1]

To the dataset Parameter added the Parameter Value expression

=Join(Parameters!ReportParameter1.Value, " ,")

I also tried using =Split(Join(Parameters!ReportParameter1.Value, " ,"), " ,") in the Parameter Value expression that I came across on another forum.

I have also tried using the =join in the Parameter Value expression with the Boolean = true example that works with the single value parameter example at the top of the post changing the parameter to Allow multiple values but in each case I still get the ‘Failed to evaluate FilterExpression of the DataSet ‘DataSet1’ error message.

If anyone can assist me with where I am going wrong it would be greatly appreciated.

Regards Trevor
Suggested answer

Alex Fun Wei Jie 33,626 on at

Like (0)

Report

RE: SSRS custom reports, how to filter by multiple field values

Hi,

for the or condition, you can build a multi select parameter, it required some efforts. You also need to check is the account or contact parameter contain data or not. You can easily google for that.

you can refer below article.

www.mssqltips.com/.../working-with-multiselect-parameters-for-ssrs-reports
TrevorB 105 on at

Like (0)

Report

RE: SSRS custom reports, how to filter by multiple field values

Hi Alex

Thanks for your reply

My query below. It pulls all the Contacts based on them being associated to an Industry (GUID)

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >

<entity name="contact" >

<attribute name="fullname" />

<attribute name="contactid" />

<attribute name="pirsa_regionid" />

<attribute name="jobtitle" />

<filter type="and" >

<condition attribute="statuscode" operator="eq" value="1" />

</filter>

<order attribute="fullname" descending="false" />

<link-entity name="pirsa_contact_industry" from="contactid" to="contactid" visible="false" intersect="true" >

<link-entity name="pirsa_industry" from="pirsa_industryid" to="pirsa_industryid" alias="IndustryID" >

<filter type="and" >

<condition attribute="pirsa_industryid" operator="eq" uiname="RDA-LGA-Staff-Member" uitype="pirsa_industry" value="{87DDF6E8-7614-E911-A968-000D3AD24077}" />

</filter>

</link-entity>

</link-entity>

<link-entity name="account" from="accountid" to="parentcustomerid" visible="false" link-type="outer" alias="AccountContact" >

<attribute name="name" />

</link-entity>

<link-entity name="connection" from="record1id" to="contactid" link-type="outer" alias="ConnectedContact" >

<attribute name="name" />

<attribute name="record1roleid" alias="ConnectedContactRole" />

</link-entity>

</entity>

</fetch>

I want to filter this where the linked entities AccountContact:name OR ConnectedContact:name equals a specified account or selection of accounts.

This works ok if I add the below filter to my query specifying the Account/s

<filter type="or" >

<condition entityname="AccountContact" attribute="name" operator="eq" value="ABC Company" />

<condition entityname="AccountContact" attribute="name" operator="eq" value="XYZ Company" />

<condition entityname="ConnectedContact" attribute="name" operator="eq" value="ABC Company" />

<condition entityname="ConnectedContact" attribute="name" operator="eq" value="XYZ Company" />

</filter>

But I need users to be able to select the Accounts to filter by from a multi value parameter and while I have some custom report writing experience I am still pretty new to this and can’t figure out how to get this to work.

Thanks in advance for any assistance.

Regards Trevor
Alex Fun Wei Jie 33,626 on at

Like (0)

Report

RE: SSRS custom reports, how to filter by multiple field values

Hi,

please share the query.