CA Service Management

Expand all | Collapse all

Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

Jump to Best Answer
  • 1.  Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 05-04-2016 09:56 AM

    Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

     

    I am able to perform most of the PdmXXX functions and now I am stuck where I need a report which needs the data from 6AM till 6PM.

     

    Plese help me with the query.



  • 2.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 06-07-2016 01:05 PM

    Did you get somewhere here or still need help?

    all you need in in fact to use the pdm function to build the right query.

    /J



  • 3.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.
    Best Answer

    Posted 06-10-2016 10:20 AM

    Hm, that's a bit more tricky and I couldn't find (fast) a way to 'correctly' do this. What works is this: create your query in the query designer, then Edit it and pus 2 extra conditions:

    (chg.close_date > (PdmToday(-1) + 60*60*6) ) and ( chg.close_date < (PdmToday(-1), 60*60*18) )

    query.png

    This should give you only the records where the Close Date is between 6 and 18. Unfortunately PdmToday has no syntax for specifying an hour adjustment.

    Now, I say this is not a 'correct' way - there should be a possibility to add a new Filter in the Change Filters which would take into account the formula above - you can try to do that and you can then you can get rid of the custom query editing and just drop the Filter in Filter Panel in WebI designer.



  • 4.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 06-10-2016 09:29 AM

    Thanks for the reply again.

     

    I am pulling the CO details based on the closed date. But the thing is I want to schedule the report and for that I need to edit the sql query explicitly so when I schedule it pull the dates dynamically.

     

    All I wanted is something like this. pdmtoday(06:00:00) AND pdmtoday(18:00:00) or if possible stacking it with pdmdate or day functions..

     

    Any help would be really appriciated.



  • 5.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 06-10-2016 09:22 AM

    What exactly are you trying to get out: Incident, Request, Problems, Change ?
    Which date field needs to be 'between 6 AM and 6 PM' : Open Date, Resolve Date, Close Date, etc ?

     

    You can just get the field from the object folder and drag it to the Filters pane on the right and you can put the Date criteria there.See the screenshot from:

    How to make Date type dimension filtering by time in Query Filter of WEBI report



  • 6.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 06-10-2016 09:05 AM

    Thanks for the reply Mayer. I am still looking for a PDM function for pulling data from 6AM till 6PM



  • 7.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Posted 06-30-2016 11:33 PM

    del.png

     

     

    Cristi Mitrana :Thanks for the awesome reply. This was really helpful.

     

     

     



  • 8.  Re: Please provide a query to pull the data from 6am till 6pm in Web Intelligence portal.

    Former Employee
    Posted 06-14-2016 05:37 PM

    Hi jeffgtx - Did mitu 's response help answer your question? If so please mark as Correct Answer. Thanks!