Dynamic Filters on Data Set

RaviK
edited February 11, 2022 in Analytics #1
<p>Hi Team,</p>
<p> </p>
<p>I have implemented one iHub report with 3 data sets (which internally gets the data from data objects (FastDB)). i would like to pass the one of the Data sets values to the other data set as dynamic filter. Ex: get the values from the Data Set 1 where Transaction currency is equivalent to the transaction currency  in the Data Set 2. in this scenario join won't works because in both the data sets we have duplicate values so if  i join them then we will get duplicate values in the data set.</p>
<p> </p>
<p>Can anyone help me, how to apply the dynamic filters on Data Set?</p>
<p> </p>
<p>Thanks,</p>
<p>- Ravi</p>

Comments

  • <p>I think you contacted support. I am working with them to build an example for you.</p>
  • <p>Hi Ravi,</p>
    <p>See attached. I am not using data sets, but using a Data Model. In general using the data model will almost always have better performance. The steps:</p>
    <ol><li>Create an array variable in beforeFactory</li>
    <li>Create a table with the Display set to 'No Display'. I would recommend setting this last so you can verify results. This table will have the first filter, and will have the values for the second filter.</li>
    <li>In the onCreate of the data item push the values in to the array.</li>
    <li>Create your second table (the one you really want). In this table you add a filter based on the array.</li>
    </ol><p>Since the first table gets created first, by the time the second table gets generated the array will be set.</p>
  • <blockquote class="ipsBlockquote" data-author="mblock" data-cid="146975" data-time="1488560275">
    <div>
    <p>Hi Ravi,</p>
    <p>See attached. I am not using data sets, but using a Data Model. In general using the data model will almost always have better performance. The steps:</p>
    <ol><li>Create an array variable in beforeFactory</li>
    <li>Create a table with the Display set to 'No Display'. I would recommend setting this last so you can verify results. This table will have the first filter, and will have the values for the second filter.</li>
    <li>In the onCreate of the data item push the values in to the array.</li>
    <li>Create your second table (the one you really want). In this table you add a filter based on the array.</li>
    </ol><p>Since the first table gets created first, by the time the second table gets generated the array will be set.</p>
    </div>
    </blockquote>
    <p>Dear Mica,</p>
    <p> </p>
    <p>Thank you for your quick help. its a really nice presentation. Dynamic filters on the table level is working fine. but i am look for dynamic filters need to be applied on data set level. Reason for this - I have 3 data sets which were created using <strong>same .data</strong> file with specific filters on each data set. after filters i need to union both Data Set for my final out put.</p>
    <p> </p>
    <p>Here is my business case</p>
    <p> </p>
    <p>1) My Data Set1's filter is Location = SG and Transaction currency in Data Set3's Transaction currency.   </p>
    <p><strong><u>**Means : </u></strong>Select * from Data Set1 where location= "SG" and Transaction currency in (Select Transaction currency from Data Set3)</p>
    <p> </p>
    <p>2) My Data Set2's filter is Location = HK and Transaction currency <strong>NOT</strong> in Data Set3's Transaction currency.   </p>
    <p><u><strong>**Means :</strong></u> Select * from Data Set2 where location= "HK" and Transaction currency not in (Select Transaction currency from Data Set3)</p>
    <p> </p>
    <p>3) Union of Data Set1 and Data Set2</p>
    <p> </p>
    <p>** just to get the clear understanding on the requirement</p>
    <p> </p>
    <p>Please provide me your valuable suggestion to achieve this. </p>
    <p> </p>
    <p>Thanks,</p>
    <p>- Ravi </p>
  • <p>Hi Ravi,</p>
    <p>Same basic methodology.</p>
    <ol><li>Create an array variable in beforeFactory</li>
    <li>Create a table with the Display set to 'No Display'. I would recommend setting this last so you can verify results. This table will have the first filter, and will have the values for the second filter.</li>
    <li>In the onCreate of the data item push the values in to the array.</li>
    <li>Create Data Set 1. Note that the data set uses a data model for it's source. In this data set you add a filter based on the array (and other filters).</li>
    <li>Add a table with the Display set to 'No Display' for Data Set 1 to force its execution.</li>
    <li>Create Data Set 2. Note that the data set uses a data model for it's source. In this data set you add a filter based on the array (and other filters).</li>
    <li>Add a table with the Display set to 'No Display' for Data Set 2 to force its execution.</li>
    <li>Create your union data set. </li>
    <li>Build your final table based on the union data set</li>
    </ol><p style="margin:0px;">See attached example that uses the same data object as the first example.</p>
  • RaviK
    edited March 7, 2017 #6
    <p>Hi Mica,</p>
    <p> </p>
    <p>Thank you very much for your quick response on all my queries.</p>
    <p> </p>
    <p>I have implemented the Dynamic filters on Data set as you mentioned in previous post. but unfortunatly it is not working.  </p>
    <p> </p>
    <p>Below are the step you suggested in our previous post. </p>
    <ol><li>Create an array variable in beforeFactory <strong>(empNumber=new Array())</strong></li>
    <li>Create a table with the Display set to 'No Display'. I would recommend setting this last so you can verify results. This table will have the first filter, and will have the values for the second filter.</li>
    <li>In the onCreate of the data item push the values in to the array. <strong>(empNumber.push(this.getValue()) ;)</strong></li>
    <li>Create Data Set 1. Note that the data set uses a data model for it's source. In this data set you add a filter based on the array (and other filters). <strong>(Data Set_hold)</strong></li>
    <li>Add a table with the Display set to 'No Display' for Data Set 1 to force its execution.</li>
    <li>Create Data Set 2. Note that the data set uses a data model for it's source. In this data set you add a filter based on the array (and other filters).<strong>(Data Set_Shipped)</strong></li>
    <li>Add a table with the Display set to 'No Display' for Data Set 2 to force its execution.</li>
    <li>Create your union data set. </li>
    <li>Build your final table based on the union data set</li>
    </ol><p style="margin:0px;">Can you please review the attached rptdesign file and correct me where i did mistake in that?</p>
    <p style="margin:0px;"> </p>
    <p style="margin:0px;">Thanks,</p>
    <p style="margin:0px;">- Ravi</p>
  • <ol><li>Your filter data set is wrong as you are getting the sales rep number from the customer data set (so you will get multiple rows for each sales rep). I changed this to use the data model and the employee category.</li>
    <li>Your filter was on the string "empNumber", instead of the variable empNumber.</li>
    <li>In this use case you can skip step 1 as you have the array in the parameter.</li>
    <li>Why are you using a dynamic filter? Is there a reason. If all you want is an IN clause you can use a simple multi-select filter.</li>
    <li>One of your filters is on status which is from orders. Which means we need to join the order table, so there is one row per order. I am guessing this is the issue with your other issue of duplicate values.</li>
    <li>Set the Page Break Interval on the fists 2 tables to 0 so you do not get empty pages.</li>
    </ol><p> </p>
    <p>In any case attached new design that works based on my comments above. </p>
    <p> </p>
    <p> </p>
  • <blockquote class="ipsBlockquote" data-author="mblock" data-cid="147011" data-time="1488899604">
    <div>
    <p> </p>
    <ol><li>Your filter data set is wrong as you are getting the sales rep number from the customer data set (so you will get multiple rows for each sales rep). I changed this to use the data model and the employee category.</li>
    <li>Your filter was on the string "empNumber", instead of the variable empNumber.</li>
    <li>In this use case you can skip step 1 as you have the array in the parameter.</li>
    <li>Why are you using a dynamic filter? Is there a reason. If all you want is an IN clause you can use a simple multi-select filter.</li>
    <li>One of your filters is on status which is from orders. Which means we need to join the order table, so there is one row per order. I am guessing this is the issue with your other issue of duplicate values.</li>
    <li>Set the Page Break Interval on the fists 2 tables to 0 so you do not get empty pages.</li>
    </ol><p> </p>
    <p>In any case attached new design that works based on my comments above. </p>
    <p> </p>
    </div>
    </blockquote>
    <p>Hi Mica,</p>
    <p> </p>
    <p>I have used the same report which you have sent in the previous post. I did some modification in that and tested that report. it is not working as expected. </p>
    <p> </p>
    <p>Below are the change i have made.</p>
    <ul><li>Modified the filter access to <strong>hidden </strong>from <strong>is required</strong>. Because user will not select this value in the report. We need to pass the entire set of values to the filter as IN CLAUSE</li>
    <li>I have applied filter on Sales Rep Data Set ->  row["Employee Number"] in 1102,1143,1504</li>
    <li>In the above filter 1504 status hold record. Expected result will be the one hold record and rest will be Shipped record.  but report generates only Shipped records.</li>
    </ul><p>Please find the attached report for your review. </p>
    <p> </p>
    <p>Thanks,</p>
    <p>- Ravi</p>
  • <p>I modified a bit further (hard coded the filter an removed the parameter) and I think it is working as expected. I highlighted the 'On Ho;d' row with a purple font. </p>
  • <blockquote class="ipsBlockquote" data-author="mblock" data-cid="147022" data-time="1488910063">
    <div>
    <p>I modified a bit further (hard coded the filter an removed the parameter) and I think it is working as expected. I highlighted the 'On Ho;d' row with a purple font.</p>
    </div>
    </blockquote>
    <p>Hi Mica,</p>
    <p> </p>
    <p>I don't want those values to be hard coded in the filters.. in my business case those values will get from one of the data set dynamically. those values can change at any point of time.</p>
    <p> </p>
    <p>My exact requirement is : Select * from Customer_Table where Customer_ID in ( select distinct customer_ID from Order_Table where Location="SG")</p>
    <p> </p>
    <p>I want to implement same requirement in iHUB with FastDB. In this Order table will load every day with new data. I think it can be achieved by using dynamic filter.</p>
    <p> </p>
    <p>Can you please suggest how to implement this?</p>
    <p> </p>
    <p>Thanks,</p>
    <p>- Ravi</p>
  • <p>The go back to the original example. Everything is there, you just need to put the pieces together.</p>