10 Replies Latest reply on Aug 10, 2016 11:47 AM by Sarah McGraw

    Parameter-controlled containers and other filters--can you nest or combine them somehow?

    Sarah McGraw

      Thanks to this community, I have been connected with a ton of helpful information about how to manage graph content on a dashboard: This one about using containers to swap out charts is particularly interesting for my purposes.

       

      I would like to know if it's possible to combine the parameter-driven swap in that example (or something like it) with a more basic filter-driven data change. I know that probably doesn't make sense, so I'll try to explain a simplified version of my challenge.

       

      • I need to create a dashboard to display performance measure data. The data is already hierarchically/semantically organized, so that I need to keep related measures in the same dash regardless of data and chart type.
      • Many measures are percentage-based, many are integer, and some are currency. Integers and currency must be displayed with column charts, and percent charts must use lines. All will need correct y-axis formatting.

       

      As an example, let's say I have a performance category with nine submeasures. Of these, three are percentages, three are integers, and three are currency.

       

      • If I were to create a basic filter on the submeasure, I could easily swap out the underlying data using only one underlying graph--but then all data would display with the same data type (eg integers and $ would look like %s), which would be wrong.
      • If I were to use the nifty parameter trick above, I could easily swap out the three types of charts. However, it looks like I'd be stuck making nine separate source graphs instead of three, which is super inefficient (because I actually have way more than nine measures). Also, it looks like each graph that gets dropped into a container a la that same nifty method creates some padding. Three sets of padding might go unnoticed, but nine sets is going to be a problem.

       

      Is there a way to combine these strategies, so that I have three parameter-driven graphs that swap out, each of which can toggle through three different datasets using a drop-down? If so, is there a way to get all those choices into the same drop-down so that the different sources are not apparent to the user? The user should feel that she gets to choose between nine options, only one of which should display at a time.

       

      Here's a sample of what it needs to look like--except that the dropdown would need to provide additional choices that use alternate chart/number format types. (That one uses a basic filter, not a parameter).

       

      Thanks a ton!

        • 1. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
          corey levinson

          So let's say you have three percentages, three integers and three currencies.  Let's look specifically at one of them, viz. currencies.

           

          Let's call our three currencies Currency1, Currency2, and Currency3.  The inefficient way would be to create three sheets of Currency1, Currency2, and Currency3.  The solution is to create a new string parameter called CurrencyFilter with the values "Currency1", "Currency2", and "Currency3".

           

          Now create a calculated field called DynamicCurrency that says:

           

          case [CurrencyFilter]

          when "Currency1" then [Currency1]

          when "Currency2" then [Currency2]

          else [Currency3]

          end

           

          Now, rather than making three sheets that pertain to specifically Currency1, Currency2 or Currency3, simply make one sheet, but drag in DynamicCurrency as your dimension.  This should solve it

          • 2. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
            Sarah McGraw

            Hi Corey,

             

            Thanks for that fast response!

             

            Unfortunately, I'm not sure I follow all of your solution--but it's hard to say for sure because I'm having trouble recreating the early steps. When I try to create the calculated field Dynamic_Integer, the values/targets that I previously defined in Integer_Filter are not recognized by the code ("reference to undefined field"). My naming schema has periods in it (.)--could that be the problem? I also don't understand how the parameter actually attaches itself to the data, since at no point in the parameter or the filter does it look like you actually reference the source field (code).

             

            Assuming I get past that step and then go through this process for the other two data types (currency, percentage), I'll have three pages, each of which can be filtered on the associated parameter. Now, how do I get all the underlying data into the same parameter control on the final dashboard? Inside the parameter dialog box I see "add from parameter"--is it as simple as pulling in all the fields from the other parameters?

            • 3. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
              Sarah McGraw

              I tried swapping out the periods for dashes--it didn't help. I think I must be doing something wrong with the construction of the parameter. Possibly something really basic.

               

              In your example, what would the source of the values "Currency1", "Currency2" and "Currency3" be? Are you referencing values in a particular underlying field? Are you specifying field names in your underlying data?

               

              In each parameter, I am trying to reference specific values in a source field called "Code", which is an identifier column in my source data (actually each code specifies three "Type" rows in my source--"actual", target", and "forecast"--but it doesn't seem like that part should make a difference here).

              ParameterDialog.PNG

              1 of 1 people found this helpful
              • 4. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                Sarah McGraw

                OK, so I figured out how to get past the first step, but I did it differently that you--maybe that's why the next part didn't work for me.

                 

                Instead of using the calculated field, I used my existing "Code" field as a conditional filter on each page, which I set equal to the applicable parameter for that page like this

                [Code]=[RJ_Integer_Filter]

                or

                [Code]=[RJ_Currency_Filter]

                or

                [Code]=[RJ_Percent_Filter]

                 

                I was then able to use the parameter filter to cycle through the data on each page.

                 

                But I don't know how to bring those all into a single final parameter. I tried creating one, and pulled the values into it from the other three, but it appears to do nothing.

                 

                Am I totally off track here? If I'd used a calculated field in the filter would that have made a difference?

                • 5. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                  corey levinson

                  Sarah, "Currency1", "Currency2", and "Currency3" are simply strings.  They do not reference any fields.  You could just as easily name them "C1", "C2" and "C3" or even "ice" "cream" "cone".  Your goal here is to have a calculated field itself that changes based on a parameter you choose.  All you need to do is make a field that says "when my user clicks on "cream", I want to show this type of currency.

                   

                  I'll walk you through my idea using the SuperStore data.  Your parameter should look like this:

                  s1.JPG

                   

                  Then right click it in the menu and select "Show Parameter"

                   

                  Your field should look like this:

                  s2.png

                  Now, when you select different things with your parameter, your calculated field will change.

                  1 of 1 people found this helpful
                  • 6. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                    Sarah McGraw

                    Hi,

                     

                    Thanks for all your help with this. I figured out what I was doing wrong last night while AWAY from my computer, and it was really dumb. I think I had been staring at my computer too long. In my project, the parameter strings correspond not to fields, but to elements within a single field. My calculated field needed to look like this:

                     

                    CASE [type1_filtername]

                    WHEN "code_a" THEN [code]

                    WHEN "code_d" THEN [code]

                    WHEN "code_e" THEN [code]

                     

                    CASE [type2_filtername]

                    WHEN "code_b" THEN [code]

                    WHEN "code_f" THEN [code]

                    WHEN "code_i" THEN [code]

                     

                    The reason I need to do this is that my actual values are stored in fields called "Actual", "Forecast", and "Target", and the codes are the only way to sort them out.

                     

                    I was confused because, in most examples, I couldn't tell whether the parameter strings were referencing fields or elements (since I didn't know the data), so it wasn't immediately obvious to me that I was trying to do something different than the posters.

                     

                    Now my experiment/ mockup seems to be working, and here's how:

                     

                    1) I created a single parameter ("Choose a measure:") that references all the codes I want available for swapping in my final view, regardless of their destination format.

                     

                    2) Based on that field, I created three calculated fields, each to connect the source field ("Code") with a specific subset of the codes specified in the parameter.

                     

                    3) I created one worksheet per calculated field, used the corresponding calculated field in the filter, set that filter equal to the parameter, and formatted each worksheet according to the number type.

                     

                    4) I created a dashboard with all three worksheets dropped into a container with titles removed. Now the single parameter control switches through all views.

                    • 7. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                      corey levinson

                      Glad I could help.  As a second tip, you know how you can switch sheets in a container using a parameter?  Try removing the container and just floating the sheets with the parameter switch still on.  I think that might get rid of that little white padding that occurs.

                      • 8. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                        Sarah McGraw

                        Interesting, so you mean you float the charts so that each is exactly on top of the one beneath?

                         

                        If that works, it will simplify the task of getting the data table tick marks to line up correctly—unless you have another tip for that?

                         

                        Thanks so much!

                        • 9. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                          corey levinson

                          Yea, I haven't tried it myself because I've been a container sucker, but I reckon you can just float them on top of each other and edit the XY coordinates in the bottom left so they are exactly on each other to get rid of the padding.

                           

                          I don't understand your second sentence though.

                          • 10. Re: Parameter-controlled containers and other filters--can you nest or combine them somehow?
                            Sarah McGraw

                            Ah--I wasn't very clear. I will be inserting data tables beneath each column or line chart, with the data columns/ cell dividers aligned with x-axis tick marks. I was worried that the padding from the container trick might make it harder to line the two elements up accurately for all the source charts and data sets. If they're exactly on top of each other, there should be no shifting.

                             

                            Up to now, I have just been doing the alignment manually, and as far as I can tell that's probably going to continue to work for this project. I figure there's probably a better way, but it doesn't seem like a pressing concern at this time, since I don't need the x-axes to actually be dynamic. I just need each graph to match its own data table.