Independent Filters

#1

Greetings,

How do I go about giving a dashboard multiple filters that are independent of each other?

Backing up a bit. I have a manual select data cube pulling from a very large data set, that grows daily.

The client requires that customer service be able to look up any record from anytime via the dashboard. There are currently 5 different data elements that customer service can potentially use to identify a record.

I’m going back and forth with support to figure out how to pass a parameter back to the sql server. (due to my lack of experience writing scripts)

Until then I’m using a warehouse storage and would like to have those 5 different data elements as filters that are not dependent on each other.

The available values that customer service will be provided to locate a record will change between the 5 based on who called etc…

I hope this made a little sense. i hit my frustration wall for the day and came here to see if anyone had a solution.

Thank you in advance !

Jason

#2

Hi Jason,

I may have misunderstood your question, but if what you want to acheive is to have 5 filters containing all the possible values for each field, you need to build 5 distinct hierarchies based upon the values that each field can contain:

  1. build a DataCube for each field that will be used as filter : SELECT MyFieldValue, MyFieldLabel FROM MyTable --the value and the label can point to the same field, having two fields returned by the query is just for using them in the hierarchy
    In this case the best way is to build a view based on this query in the DB and build the DataCube based on the view (in order to avoid the circular reference error…)
    Or (a far better solution) if you have the values declared in a separated table and you’re using foreign keys in the table you want to set the filters on, SELECT Id, Label FROM MyLinkedTable

  2. Then build each Hierarchy based on MyFieldValue (or Id) and set MyFieldLabel (or Label) as the string shown in the Hierarchy’s dropdown

  3. in the DataCube that will return all the rows contained in the targeted table ( SELECT MyField1, MyField2,MyField3,MyField4,MyField5 FROM MyTable) without any “WHERE” condition, set the corresponding Hierarchy on each corresponding field

  4. In the MetricSet based on the DataCube returning all the values of the targeted table, set the default values for each field used for filtering to “none” (do this only on the fields that will be used for filtering)

  5. In the Dashboard, create a filter per field (according to the field format) and link it to the corresponding field

As soon as the end-user will select a value from a filter, the result will be based only on the rows containing the value(s) selected for the corresponding field. And if several filters are set the rows returned will be the ones corresponding to those filters value(s) , just if you were using “WHERE” on those values in a SQL query.

I hope this helps.

1 Like
#3

Thank you for your detailed reply Olivier!

I’ll give that a try.