Customer Portal

Get Involved. Join the Conversation.

Topic

    Patricia Harvath
    incidents.created report based on user defined dates
    Topic posted August 11, 2011 by Patricia HarvathBronze Medal: 1,250+ Points, last edited October 29, 2011 
    3859 Views, 31 Comments
    Title:
    incidents.created report based on user defined dates
    Content:

    Hello,

    I need to create a report that lists all incidents created between two user defined dates.  Any idea how I go about this?

    Thanks!

     

    Comment

    • Andrew Challis

      Do you want to display this in the customer portal or in the agent desktop?

       

      Creating a report to filter by created date just requires you to add a filter of "incidents.created" to a report and select the operator to "between"

    • Patricia Harvath

      Hey,

      I need to do this in the customer portal.  Basically the report brings back all incidents for a specific group.  There are two calendars where an agent can filter incidents between two dates.  I have the Yahoo calendar rendering the dates when selected, but I am at al loss of how to get those dynamically created dates into the reports filter. 


    • Jeremy Watson

      Patricia,

      We don't support this out-of-the-box, but you should be able to create a custom widget to do what you need.  You can probably start by copying the search/FilterDropdown2 widget and modifying it to use your calendar rather than a dropdown.  The only other tweak is that date filters expect the incoming data to be a Unix timestamp (for testing, I converted dates to a timestamp using http://www.onlineconversion.com/unix_time.htm).  You'll need to do that in your widget's logic.js file to set this._eo.filters.data.val before firing the event.

      Hopefully that should get you started, but let me know if you have further questions.

      James

    • Patricia Harvath

      Hi James,

      I am trying to modify the FilterDropdown2 widget, but I am not having much luck.  I getting a PHP error 'Invaild argument supplied for foreach() in the rightnow/optimized_includes.php file, line number 3695.

      My confusion rests with the FilterDropdown2 widget getting one value when the filter in the report is asking for two values, a 'from date' and a 'to date'.  Do I have two FilterDropdown2 widgets on the page?  If so, each one has to be named the same to reflect the filter in the report.

      What am I missing here?

      Thanks so much for your help, James.

      Patricia

    • Jeremy Watson

      Patricia,

      I think you'll have an easier time if you create two filters and then associate a different widget with each filter.

      James

    • Patricia Harvath

      Hi James,

      I am back working on this functionality.  I have added the custom FilterDropdown2 widget to my page and associated it with a date filter just to see if I can get the page to load properly, but I get an error message about an invalid argument supplied foreach().

      Is this because the widget is expecting a string?  or does it need a Unix timestamp?

      Also, I have no idea how to modify it to use the Yahoo calendar.

      Is there any example code that might give me some idea how to set this up?

      Thanks once again for your help.
      Patricia

       

       

       

    • Jeremy Watson

      Patricia,

      Sorry, but I don't really have any example code to go with this.

      In terms of integrating with the Yahoo calendar, you'll need your custom widget to know where to find the Yahoo calendar on your page and pull out the selected date from that widget.

      Regarding the invalid argument error with foreach, I'm not entirely sure, since I don't know what your code looks like.  Can you provide some more information, like the exact error message and the code for your custom widget?

      James

    • Patricia Harvath

      Hi James,

      It has been a while since I have had time to revisit this issue.  I hope you're ready to jump back into...  :)


      Instead of using the search/FilterDropDown2 widget, I used the KeywordText widget (my version 5).
      <rn:widget path="custom/KeywordText5" report_id="107085"  filter_name="fromDate" />

      The code on the view page of the widget is this:
      <div id="rn_<?=$this->instanceID;?>" class="rn_KeywordText2">
          <div class="calendarWrapper">
            <label for="rn_<?=$this->instanceID;?>_Options"><?=$this->data['js']['name']?></label>
               <input type="text" name="fromDate" id="fromDate" maxlength="0" class="KeywordField" value="1325376001" />
               <div id="cal1Container"></div>
          </div>
      </div>

      I haven't changed the controller or js file for this widget.  As you can see from the code, I have hard coded a value of "1325376001" which is the timestamp for 01/01/2012 00:00:00.  The page displays, but the filter isn't working.  The filter is set to incidents.created, greater than or equals, No Value.

      Do you want to see the code for the calendar?

      Any direction from here is really appreciated!

      Thanks,
      Patricia

    • Jeremy Watson

      Does the logic file know to get the data from an input element with an id of 'fromDate'?  Have you tried to output/alert the data that your widget is sending in the evt_searchFiltersResponse event?


      If the logic file seems to be sending the correct data for the event, two things to make sure that data is making it to the report model:


      1) Use Fiddler to see if the AJAX request is sending that filter data or not.


      2) Create a pre_report_get hook and output the report data.  There's an example of creating a hook here.


      James

    • Patricia Harvath

      Hey James,

      Thanks for the suggestions.  I took the other KeywordText widgets off the page and can get the 'fromDate' KeywordText widget working partically.  It will filter the incidents only returning those incidents created after the 'fromDate' YEAR. If the 'fromDate' is 4/1/2012, all incidents in the year 2012 are returned.

      Also, I can't get the 'toDate' widget working even when it is the only KeywordText widget on the page.  I have it set up exactly like the 'fromDate' KeywordText widget.  I have named the 'fromDate' widget, KeywordText5 and the 'toDate' widget KeywordText6 so I can point the logic file to the correct element.

      Any thoughts to why it will work in one case and not the other?  Below is my calendar script:

      YAHOO.namespace("enterprise.calendar");
          YAHOO.enterprise.calendar.init = function() {
              var calendarObj1 = document.getElementById("fromDate");
              var calendarObj2 = document.getElementById("toDate");
              
              YAHOO.enterprise.calendar.cal1 = new YAHOO.widget.Calendar("cal1","cal1Container", { title:"Choose a date:", close:true } );
              YAHOO.enterprise.calendar.cal1.render();
              
              YAHOO.enterprise.calendar.cal2 = new YAHOO.widget.Calendar("cal2","cal2Container", { title:"Choose a date:", close:true } );
              YAHOO.enterprise.calendar.cal2.render();

              // Hide Calendar if we click anywhere in the document other than the calendar
              YAHOO.util.Event.on(document, "click", function(e) {
                      var el = YAHOO.util.Event.getTarget(e);
                      var c1El = document.getElementById("cal1");
                      var c2El = document.getElementById("cal2");
                      if (el != c1El && !YAHOO.util.Dom.isAncestor(c1El, el) && el != calendarObj1 && !YAHOO.util.Dom.isAncestor(calendarObj1, el)) {
                              YAHOO.enterprise.calendar.cal1.hide();
                      }
                      if (el != c2El && !YAHOO.util.Dom.isAncestor(c2El, el) && el != calendarObj2 && !YAHOO.util.Dom.isAncestor(calendarObj2, el)) {
                              YAHOO.enterprise.calendar.cal2.hide();
                      }
              });

              YAHOO.enterprise.calendar.cal1.selectEvent.subscribe(function() {
                      if (YAHOO.enterprise.calendar.cal1.getSelectedDates().length > 0) {
                              var selDate = YAHOO.enterprise.calendar.cal1.getSelectedDates()[0];
                            if (dStr >= 1 && dStr <=9){
                                  dStr = "0" + dStr;
                              } else {
                                  dStr = dStr;
                              }
                              var mStr = (selDate.getMonth() + 1);
                              if (mStr >= 1 && mStr <=9){
                                  mStr = "0" + mStr;
                              } else {
                                  mStr = mStr;
                              }
          //                    var mStr = YAHOO.enterprise.calendar.cal1.cfg.getProperty("MONTHS_SHORT")[selDate.getMonth()];
                              var yStr = selDate.getFullYear();
                              calendarObj1.value = mStr + "/" + dStr + "/" + yStr;
                          } else {
                              calendarObj1.value = "";
                      }
                      YAHOO.enterprise.calendar.cal1.hide();                
              });

              YAHOO.enterprise.calendar.cal2.selectEvent.subscribe(function() {
                      if (YAHOO.enterprise.calendar.cal2.getSelectedDates().length > 0) {
                              var selDate = YAHOO.enterprise.calendar.cal2.getSelectedDates()[0];
                            if (dStr >= 1 && dStr <=9){
                                  dStr = "0" + dStr;
                              } else {
                                  dStr = dStr;
                              }
                              var mStr = (selDate.getMonth() + 1);
                              if (mStr >= 1 && mStr <=9){
                                  mStr = "0" + mStr;
                              } else {
                                  mStr = mStr;
                              }
          //                    var mStr = YAHOO.enterprise.calendar.cal1.cfg.getProperty("MONTHS_SHORT")[selDate.getMonth()];
                              var yStr = selDate.getFullYear();
                              calendarObj2.value = mStr + "/" + dStr + "/" + yStr;
                      } else {
                              calendarObj2.value = "";
                      }
                      YAHOO.enterprise.calendar.cal2.hide();
              });

              // bind focus events
              YAHOO.util.Event.addListener("fromDate", "focus", YAHOO.enterprise.calendar.cal1.show, YAHOO.enterprise.calendar.cal1, true);
              
              YAHOO.util.Event.addListener("toDate", "focus", YAHOO.enterprise.calendar.cal2.show, YAHOO.enterprise.calendar.cal2, true);
              var seldate = YAHOO.enterprise.calendar.cal2.getSelectedDates();
              if (seldate.length > 0) {
                      // Set the pagedate to show the selected date if it exists
                      YAHOO.enterprise.calendar.cal2.cfg.setProperty("pagedate", seldate[0]);
                      YAHOO.enterprise.calendar.cal2.render();
              }
          }
          YAHOO.util.Event.onDOMReady(YAHOO.enterprise.calendar.init);

       

       

      Here is the change I made to the KeywordText widget logic file to convert the date to a timestamp:

      _onGetFiltersRequest: function(type, args)
          {
              this._oe6.filters.data = YAHOO.lang.trim(this._textElement6.value);
              this._searchedOn = this._oe6.filters.data;

      //Added the following to convert date to timestamp and to verify conversion
              var strDate = this._searchedOn;
              var datum = Date.parse(strDate);
              this._searchedOn = datum/1000;    
              this._oe6 = datum/1000;    
              alert(this._searchedOn);

              RightNow.Event.fire("evt_searchFiltersResponse", this._oe6);
          },

       

      Thanks for your insights and guidance!
      Patricia

    • Jeremy Watson

      I think outputting the filter data with the pre_report_get hook will help.  If your code is similar between the two, then I would suspect that the issue could be the operator used with the toDate filter (e.g. OPER_GT instead of OPER_LT).


      This post contains some of the details of the different operators available.


      I might be able to provide some more help if you can include the filters that get passed to the pre_report_get hook.


      James

    • Patricia Harvath

      hey james,

      How do I get the filter ids? 

      Specifially, where do I run getRuntimeFilters($reportID)?

       

      Thanks!

    • Jeremy Watson

      The getRuntimeFilters function is in the report model.

              $this->CI->load->model('standard/Report_model');
              $this->CI->Report_model->getRuntimeFilters($reportID);

      James

    • Patricia Harvath

      Thanks James!  (I keep getting sidetracked by other requests!)

       

      Here are the filters from the getRuntimeFilters function:

      filter Info = [{"fltr_id":1,"name":"Product Heirarchy","type":1,"oper_id":10,"data_type":1,"optlist_id":9,"expression1":"incidents.prod_hierarchy",
      "attributes":273,"default_value":"~any~","prompt":"Product Heirarchy","required":0},

      {"fltr_id":2,"name":"Brand","type":1,"oper_id":10,"data_type":1,"optlist_id":50331720,"expression1":"incidents.c$brand",
      "attributes":131081,"default_value":"~any~","prompt":"Brand","required":0},

      {"fltr_id":3,"name":"Keyword","type":1,"oper_id":7,"data_type":5,"optlist_id":null,"expression1":"incidents.search_thread",
      "attributes":32785,"default_value":null,"prompt":"Keyword","required":0},

      {"fltr_id":4,"name":"Group","type":1,"oper_id":10,"data_type":1,"optlist_id":50331653,"expression1":"incidents.c$group",
      "attributes":131081,"default_value":"~any~","prompt":"Group","required":0},

      {"fltr_id":5,"name":"Status","type":1,"oper_id":1,"data_type":1,"optlist_id":19,"expression1":"incidents.status_id",
      "attributes":131073,"default_value":"~any~","prompt":"Status","required":0},

      {"fltr_id":6,"name":"AssignedGroup","type":1,"oper_id":1,"data_type":1,"optlist_id":16777217,"expression1":"incidents.c$assigned_group",
      "attributes":131081,"default_value":"1","prompt":"incidents.c$assigned_group","required":0},

      {"fltr_id":7,"name":"fromDate","type":1,"oper_id":6,"data_type":4,"optlist_id":null,"expression1":"incidents.created",
      "attributes":1,"default_value":null,"prompt":"fromDate","required":0},

      {"fltr_id":8,"name":"toDate","type":1,"oper_id":4,"data_type":4,"optlist_id":null,"expression1":"incidents.created",
      "attributes":1,"default_value":null,"prompt":"toDate","required":0}];

       

      Patricia

    • Jeremy Watson

      Okay, so those are the filters that are set in your report.  If you can do something similar in a pre_report_get hook, we can see what you're actually trying to send to the report model.

      James