Creating a complex filter to allow dashboard linked filters

Joan Poblet shared this question 2 years ago
Answered

Hi,


It is difficult to explain what we would like to do, but I will try:


Just imagine that we have two different reports with two views and dimensions:

REPORT1:

TABLE1

Field CHOICES (N:M RELATIONSHIP) with possible values 'CH1','CH2','CH3'

FILTER1: Applying a simple choice filter in the field CHOICES


REPORT2:

TABLE2 (in this case the previous N:M relationship was transformed in 3 boolean fields)

Field CH1 with possible values Yes/No

Field CH2 with possible values Yes/No

Field CH3 with possible values Yes/No

FILTER2. Filter by CH1 or CH2 or CH3??? We would like to simulate FILTER1.


That's is because we would like to join both reports in a dashboard. In order to do it in a nice way, we would like to join both filters but we don't know whether it is possible or not to create a FILTER2 which simulates the same working of FILTER1 or vice versa, even with Freehand SQL.


Many thanks for you help.


Best Regards,

Joan.

Comments (8)

photo
1

Hi Joan,


Unfortunately I am a little confused as to what you are going for here. Would you be interested in scheduling a time to screen-share? If so, please let me know what time works best for you. I am on United States Mountain Time (MST).


Regards,

Nathan

photo
1

Hi Nathan,

Thank you for your proposal but, because of the time difference, it could be difficult. Let me try to explain myself better with an excel file and, if not, we will find a way to make the screen-share at the beginning of your day and at the end of mine.

Thank you.

Best Regards,

Joan.

photo
1

Hi Joan,

Thank you for the clarification, I understand what you are going for now, but am having a hard time thinking of good ways to do this. I will continue to work with various ideas over the next couple of days and let you know if I can get anythign to work!

Regards,

Nathan

photo
1

Hi Nathan,


Thank you for your help. Don't worry, I know it is difficult! :)


Thanks again for your help.


Regards,

Joan.

photo
2

Hi Joan,

Sorry for the delay in response, I finally came up with a method that should accomplish this.

First we will need to create an append sub-query, joining on something constant across the entire data-set. I often will create a view level calculated field that simply returns 1 (called "one" in the example below), for this purpose. Within this append sub-query we can then create a new pre-defined function that concatenates all possible values for this row (so when you filter, this string will adjust accordingly). We can then adjust this function to contain a case statement that says when "CH1"(user defined) in that string, then 'Yes', else "No".

In the end I have two pre-defined functions, the first "Group Concat" simply proves how this is working. You can find instructions on how to get this working here. The second creates columns to display entirely yes or no depending on if that element is present (see below for the code for this)

In the end your primary report should look similar to this. (Note that it is necessary to pass the wildcards through the user-defined second string as well) In this example I have filtered my group list which normally contains A through D, down to just A and C.

4cb1db2502592e998bbfcce4f7d37dd6

Once you have a field for each of your CHOICES, you can link these to your related reports. This will need to be done through the link on "fields" option (rather than the standard link on "filters")

Here is the MySQL specific pre-defined function I used:


  1. <function>
  2. <name>Group Concat Contains, MySQL</name>
  3. <argument>
  4. <index>1</index>
  5. <name>String</name>
  6. <datatype>text</datatype>
  7. </argument>
  8. <argument>
  9. <index>2</index>
  10. <name>String</name>
  11. <datatype>text</datatype>
  12. </argument>
  13. <sql>
  14. <![CDATA[
  15. CASE WHEN GROUP_CONCAT(DISTINCT($1)) LIKE $2 THEN 'Yes' ELSE 'No' END
  16. ]]>
  17. </sql>
  18. <aggregate>$1</aggregate>
  19. <database>MySQL</database>
  20. <return>text</return>
  21. </function>

Please let me know if this makes sense or if you have any trouble getting this to work!

Regards,

Nathan

photo
1

Hi Joan,


Have you had any luck in resolving this?


Regards,

Nathan

photo
1

Hi Nathan,


I'm sorry for the delay. We cannot check it yet because we are in the middle of an updating issue (one of your colleagues is already taking care of it) and we don't want to mix both changes in YF configuration.


You can close the ticket if you prefer and, once we can check it, I could open a new one by referencing this one in case I have any doubt.


Thank you.


Regards,

Joan.

photo
1

Hi Joan,


That sounds good. I will close this for now, but if you have any questions on this, just reply to this same ticket and the case will be re-opened.


Regards,

Nathan