Monday 25 February 2013

Filter dataset records based on another dataset data in ssrs


Senario:
we have two datasets.
dataset1 and dataset2.
based on dataset1 values we need to filter dataset2 data.

Doing thourgh FetchXml.

First create the Dataset1 with enableprefiltering="1".
Ex:


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="salesorder" enableprefiltering="1">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="statuscode" />
    <attribute name="totalamount" />
    <attribute name="createdon" />
    <attribute name="ordernumber" />
    <attribute name="quoteid" />
    <attribute name="salesorderid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="name" operator="not-null" />
    </filter>
    <link-entity name="account" from="accountid" to="customerid" alias="aa">
      <attribute name="name" />
      <attribute name="address1_name" />
      <attribute name="address1_line1" />
      <attribute name="address1_line2" />
      <attribute name="address1_line3" />
      <attribute name="address1_city" />
      <attribute name="address1_stateorprovince" />
      <attribute name="address1_postalcode" />
      <attribute name="address1_country" />
      <filter type="and">
        <condition attribute="name" operator="not-null" />
      </filter>
    </link-entity>
    <link-entity name="businessunit" from="businessunitid" to="jmh_store" alias="ab">
      <attribute name="address2_name" />
      <attribute name="address2_line1" />
      <attribute name="address2_line2" />
      <attribute name="address2_line3" />
      <attribute name="address2_city" />
      <attribute name="address2_stateorprovince" />
      <attribute name="address2_postalcode" />
      <attribute name="address2_country" />
      <filter type="and">
        <condition attribute="name" operator="not-null" />
      </filter>
    </link-entity>
  </entity>
</fetch>

the about code will join "order", "Account","businessunit" tables.

next :
dataset2:


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="salesorderdetail">
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />   
    <attribute name="baseamount" />
    <attribute name="jmh_orderamount" />    
    <attribute name="salesorderid" />
    <attribute name="salesorderdetailid" />
    <order attribute="productid" descending="false" />
    <filter type="and">
      <condition attribute="quantity" operator="not-null" />
    </filter>
  </entity>
</fetch>

now you need to get the data in dataset2 relating of dataset1;

create parameters 










Give the values as default values.
Use this filter and create another dataset3 for the Salesorder.
after that create another parameter



so using 3 parameter
create filter on dataset2.






u will get the correct details.

No comments:

Post a Comment