1 2 Previous Next 29 Replies Latest reply: Aug 16, 2013 12:28 PM by ScriptMan RSS

    Assign results column to Drop Down Value List

    ScriptMan


      Hyperion Interactive Reporting vs 9

      I have a drop down filter I use in a report dashboard. Works great.  I have no problem creating drop down filters when the Value List are hand typed in by me. Like year, 2013, 2012, 2011, 2010.  Or Region, North, South, East, or West.  But what if I want to filter by Customer Name?  We have hundreds of customers in the table.  Plus the customer accounts change daily.  How can I get the drop down to display the customers? In return distinct, sorted alphbetically?  It would be extremely useful to my users to have the ability to produce sales activity for one particular customer....in fact, essential.  How do I set up the drop down list ?

        • 1. Re: Assign results column to Drop Down Value List
          AlexLiu

          The way I will handle this - assuming there is a data source for Customer information.

           

          1. Create a new query to retrieve this information and sort them by the name or account # (your preference)

          2. Then you can use this result to populate your Drop-Down box.

           

          This method is more advance and assuming the data source will be updated systematically.  Like your own Year DropDown list, you can source a calendar table instead of update the Value List annually.

           

          Good luck.

          • 2. Re: Assign results column to Drop Down Value List
            DougP

            I've combined a textbox and a listbox to provide this functionality.

             

            When the document opens, I run the Customers query.  While the results of this query can be filtered, I find it's easier for me to write the results into an array.  It may run a little faster, too.  I have added a method called filterBegins to the Array class to handle the filtering required for the following behavior.

             

            As the user types into the textbox, the listbox is cleared and repopulated with the results of filtering the values in the array (or Results) to just those values that begin with what the user has typed.  If I wanted to (for performance reasons), I could limit the list to some maximum number of entries.  If the user clicks on a value in the listbox, that value is written to the textbox and the listbox disappears.

            • 3. Re: Assign results column to Drop Down Value List
              ScriptMan

              This is what I figured, to have a query, or a column from a results set feed to the drop down list.  What I'm seeking is the event code that would make this possible.

              Let's say I have Results set "Results_AnnualSales" with a column "CustName".  My drop down list on my dash board is "drop_CustName".

              How would I set the code up, and where, to make the feed to this drop down list show a select distinct list of customers sorted alphbetically?

              What would the code look like?

              • 4. Re: Assign results column to Drop Down Value List
                AlexLiu

                The following is the code from Year DropDown list from Sample1mod.bqy that came with your Interactive Reporting install on EPM Workspace.

                 

                You should be able to figure out with the following code.  Good luck.

                ------------------------------------------------------------

                 

                var choice = ActiveDocument.Sections["EIS"].Controls.DropDown1[DropDown1.SelectedIndex];

                var sect_name = "SalesResults";

                var col_name ="Year";

                 

                 

                //alters local limit

                ActiveDocument.Sections[sect_name].Limits[col_name].SelectedValues.RemoveAll();

                ActiveDocument.Sections[sect_name].Limits[col_name].SelectedValues.Add(choice);

                ActiveDocument.Sections[sect_name].Limits[col_name].Operator=bqLimitOperatorEqual;

                 

                 

                //sets label text

                TextLabel2.Text=choice;

                • 5. Re: Assign results column to Drop Down Value List
                  ScriptMan

                  This looks doable.   What event does this code take place ?  On the Drop Down ?

                  • 6. Re: Assign results column to Drop Down Value List
                    AlexLiu

                    Yes, you would assign this script to the DropDown object that you want to control the filter(s) in any Result table.

                    • 7. Re: Assign results column to Drop Down Value List
                      ScriptMan

                      How would I assign the script?  Is there an event associated with it....where exactly would the script go?

                      • 8. Re: Assign results column to Drop Down Value List
                        ScriptMan

                        I've, in the past, only used the "OnSelected" event on a drop down.  I don't think this code belonggs there.  Can you tell me where it should go to populate the drop down list?

                        • 9. Re: Assign results column to Drop Down Value List
                          AlexLiu

                          I thought you have figured this out by now.

                           

                          On your dashboard, press Ctl-D to get into Design mode, right-click on the DropDown object and select Script, and paste the script to it.

                           

                          Good luck.

                          • 10. Re: Assign results column to Drop Down Value List
                            ScriptMan

                            It may be that I'm being unclear. My question is what "event" is associated with the script.  I see two events on the drop down.  OnSelected and OnClientSelected.  If the script belongs in this space, which event is it assigned to?

                            • 11. Re: Assign results column to Drop Down Value List
                              WVanSluys


                              it really depends on what client your user are using to view and interact with your dashboard.

                               

                              Interactive Reporting Web Client (Plug-in) then you will use OnSelected

                               

                              if the users are using the WorkSpace Client (iHTML) then you will want to use the OnClientSelected

                               

                              -w

                              • 12. Re: Assign results column to Drop Down Value List
                                ScriptMan

                                Thank you.  I'm using the OnSelected event.  But what I don't understand is by definition, this script will trigger when a user makes a selection.  I, however, am trying to polulate the list with values before the user makes a selection.  I want those values to come from a column in a results set.  The code I've created, based on the Sample1Mod.bqy example above is:

                                ...................................................................................

                                //__Local Dashboard Drop Down
                                var choice=ActiveDocument.Sections["Home_RptDetail"].Controls.drop_CustName[drop_CustName.SelectedIndex];

                                 

                                //__Results set the drop down list values will come from
                                var sect_name="Results_Master_Accounts";

                                 

                                //__Column Name in the results set we want to feed to the drop down list
                                var col_name="Customername";

                                 

                                //__ALTERS LOCAL LIMIT
                                ActiveDocument.Sections[sect_name].Limits[col_name].SelectedValues.RemoveAll();
                                ActiveDocument.Sections[sect_name].Limits[col_name].SelectedValues.Add(choice);
                                ActiveDocument.Sections[sect_name].Limits[col_name].Operator=bqLimitOperatorEqual;

                                ...............................................................................

                                Unfortunately this isn't working.  Nothing happens. Do you see anything wrong with the procedure.  Have I been unclear in what I'm attempting to do?

                                • 13. Re: Assign results column to Drop Down Value List
                                  AlexLiu

                                  Look at the same Sample1mod.bqy

                                   

                                  "EIS" is the Dashboard, when you select the Year from the DropDown, it will execute the code assigned to it.  However, on the Dashboard itself, it's an object you will need to Activate it.

                                   

                                  In your Script Editor, Go thru all the Object(s) and their  perspective  Event Trigger(s).  You will learn  how to control the different objects.

                                   

                                  Good luck.

                                  • 14. Re: Assign results column to Drop Down Value List
                                    ScriptMan


                                    I don't have access to the Sample1mod.bqy file.

                                     

                                    I've replaced "EIS" with the name of my dashboard.

                                     

                                    How do I perfrom the activation your speaking of?

                                    1 2 Previous Next