Confirm

Expand all | Collapse all

TODAY in widget expression

  • 1.  TODAY in widget expression

    Posted 09-20-2019 06:59
    Hi All,
    Has anyone used getdate() or current_date() in a widget expression in order to limit the results displayed in a widget?
    If so how?
    I didn't particularly want to alter my data source as it is used for other things and I tend to put extra parameters into widgets in order to avoid running lots of scheduled reports whenever I can. Being able to calculate from today's date in the widget would be a big help.
    Thanks
    Lesley

    ------------------------------
    Lesley Cocker
    Dorset County Council
    ------------------------------


  • 2.  RE: TODAY in widget expression

    Pitney Bowes
    Posted 30 days ago
    Hi Lesley,

    You can use the following code for sorting by month and year on a Dashboard.

    CONVERT(VARCHAR, DATEADD(mm, DATEDIFF(mm, 0, central_enquiry.log_effective_date) + 0, 0), 111)

    This calculates the 1st day of the month and changes to format YYYY/MM/DD.

    There is an enhancement Idea which explains why the format has to change when used on a Dashboard.

    https://ideas.pitneybowes.com/ideas/CONFIRM-I-1567

    ------------------------------
    Abhimanyu Kumar Singh
    Pitney Bowes Software India
    Noida
    ------------------------------



  • 3.  RE: TODAY in widget expression

    Posted 29 days ago
    Hi Abhimanyu,
    That looks very promising - I will definitely give that a try as soon as I can.
    Thank you
    Lesley

    ------------------------------
    Lesley Cocker
    Dorset County Council
    ------------------------------



  • 4.  RE: TODAY in widget expression

    Posted 29 days ago
    Hi Lesley,

    I'm not aware if you can filter data in a widget? If you have found a way to do this, can you share how you did it please, as I would ideally like to reduce the number of report schedules I have, run from the same query with numerous qualifications created?

    Regarding your original query, I add variations of the following code to all my datasource queries to allow for date filtering in my qualifications.

    CASE
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() ), 0) ) THEN 'Current Day'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE() ), 0) ) THEN 'Previous Day'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 2, GETDATE() ), 0) ) THEN '2 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 3, GETDATE() ), 0) ) THEN '3 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 4, GETDATE() ), 0) ) THEN '4 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE() ), 0) ) THEN '5 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 6, GETDATE() ), 0) ) THEN '6 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 7, GETDATE() ), 0) ) THEN '7 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 14, GETDATE() ), 0) ) THEN 'Within 14 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 28, GETDATE() ), 0) ) THEN 'Within 28 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 30, GETDATE() ), 0) ) THEN 'Within 30 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 60, GETDATE() ), 0) ) THEN 'Within 60 Days Ago'
    WHEN cenq.enquiry_time >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 90, GETDATE() ), 0) ) THEN 'Within 90 Days Ago'
    ELSE 'Earlier'
    END as reporting_period

    I add similiar code to also filter by month, calendar year & financial year if/when required.

    These are always added at the end of my SELECT statement, and I advise users to ignore as it's used to help schedule the reports.

    This does mean the datasource needs to be amended and a qualification created and specified on the report schedule as opposed to specifying in the Widget.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 5.  RE: TODAY in widget expression

    Posted 29 days ago
      |   view attached
    Hi Haf,
    Not sure if this screenshot will be legible but yes, we do apply extra criteria in widgets to save scheduled reports.
    Here's an example of an expression we use:
    IF( {Job Type Code} IN ('SNPT','SIGN'), {Status Code} +" : "+ {Status Name}, 'Unknown')
    Steve Bish first put us on to this and I think PB said that "Info Maker" would indicate the correct syntax for it but I have found it very trial and error. Of course there are some functions suggested but they are very limited.
    Regards
    Lesley


    ------------------------------
    Lesley Cocker
    Dorset County Council
    ------------------------------



  • 6.  RE: TODAY in widget expression

    Posted 28 days ago
    Hi Lesley,

    Thanks for sending this, I've got it to work with one of my widgets, so understand how it works now.

    Based on this concept, the expression you would be looking for should be as follows. I've appended to your code as an example, assuming you have the job_entry_date field included in the SQL.

    if({Job Entry Date} >= DATEADD(SS, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() ), 0) ) AND {Job Type Code} IN ('SNPT','SIGN'), {Status Code} +" : "+ {Status Name}, 'Unknown')

    The code in bold is to calculate the beginning of the current day.

    However, it looks like there maybe a limitation where the date functions (DATEADD(), DATEADIFF(), etc) do not work in the Expression Window, including the basic GETDATE().

    CONVERT() does not work so I wasn't able to change the date to text and compare it as an alternative.

    You may want to try the above on your instance as it maybe an issue with the version of Confirm or SQL Server we are currently running.

    Hopefully someone from PB can advise if this is a limitation in functionality or incorrect syntax somewhere?

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 7.  RE: TODAY in widget expression

    Pitney Bowes
    Posted 28 days ago
    Hi Haf,

    The expressions used in dashboard widgets and report templates are InfoMaker expressions - a decent source for what can be used can be found here :https://docs.oracle.com/cd/E17046_01/InfoMaker.pdf in Chapter 24 (page 631).

    Thanks
    Daniel

    ------------------------------
    Daniel Vokes
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------