Creating a complex filter to allow dashboard linked filters

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

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.

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

Hi Nathan,

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

Thanks again for your help.

Regards,

Joan.

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.

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

Hi Joan,

Have you had any luck in resolving this?

Regards,

Nathan

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.

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