• State Not Answered
  • Replies 6 replies
  • Subscribers 5 subscribers
  • Views 4287 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Reports

I am using a!queryProcessAnalytics() to get data from a portal report.

erickp

I am using a!queryProcessAnalytics() to get data from a portal report. In SAIL, I have a From Start Date and To Start Date. These fields are going to query c8 = startDateTime from the portal report which is a DateTime! The search criteria are Date and c8 is a DateTime. I was having a few issues when the user, for example, typed 6/15/2016 to 6/15/2016 (The same dates). This wasn't returning the results as expected. What I had to do was to make it in the backend like this: 6/15/2016 0:0:0 to 6/15/2016 23:59:59. To accomplsh this I had to use the userdatetime() rule to convert the date to datetime. For example, to convert 6/15/2016, I would do userdatetime(year(ri!date), month(ri!date), day(ri!date), 0,0,0) to userdatetime(year(ri!date), month(ri!date), day(ri!date), 23,59,59) where ri!date = 6/15/2016.

This means that on the filters of the a!queryProcessAnalytics(), instead of using directly the ri!date, I used the rule as sh...

OriginalPostID-218397

  Discussion posts and replies are publicly visible

  • 0
    ...own above. That filter alone looks like the below code. In the below code, the rule P360_RULE_Conversion_dateToDatetime is basically doing the same as userdatetime(). So my question is if anyone has another solution to the problem presented here or if this is the best way of doing it. Let me know if I need to clarify what the problem and my solution is.

    if(
    and(
    ri!startDatetimeMin = ri!startDatetimeMax,
    or(
    not(
    isnull(
    ri!startDatetimeMin
    )
    ),
    not(
    isnull(
    ri!startDatetimeMax
    )
    )
    )
    ),
    a!queryFilter(
    field: ri!fieldName,
    operator: "between",
    value: {
    rule!P360_RULE_Conversion_dateToDatetime(
    date: ri!startDatetimeMin,
    hour: 0,
    minute: 0,
    second: 0
    ),
    rule!P360_RULE_Conversion_dateToDatetime(
    date: ri!startDatetimeMax,
    hour: 23,
    minute: 59,
    second: 59
    )
    }
    ),
    {
    if...
  • 0
    ...(
    isnull(
    ri!startDatetimeMin
    ),
    {},
    a!queryFilter(
    field: ri!fieldName,
    operator: ">=",
    value: rule!P360_RULE_Conversion_dateToDatetime(
    date: ri!startDatetimeMin,
    hour: 0,
    minute: 0,
    second: 0
    )
    )
    ),
    if(
    isnull(
    ri!startDatetimeMax
    ),
    {},
    a!queryFilter(
    field: ri!fieldName,
    operator: "<=",
    value: rule!P360_RULE_Conversion_dateToDatetime(
    date: ri!startDatetimeMax,
    hour: 23,
    minute: 59,
    second: 59
    )
    )
    )
    }
    )
  • 0
    A Score Level 2
    Hi erickp,
    please use below code snippet

    a!queryFilter(
    field: "c8",
    operator: "between",
    value: {today(),today()+1}
    )
    let me know if this works.
  • 0
    Yes @venkateshamu. That is another alternative of doing it. Thanks. Any other suggestions?
  • 0
    The Appian conversion function todatetime() could be used to accomplish this. The only catch is that whenever a date is converted to dateTime, it is initialized at 0:00:00. So your code will need to look something like this:
    value:{todatetime(ri!date), todatetime(ri!secondDate + 1)}
    The +1 pushed the date forward by one day, which solves the issue you had if the user selects the same date.
    forum.appian.com/.../Conversion_Functions.html
  • 0
    @erickp: For a similar scenario we are using rule as given below. Almost same as what you have done. We just made it generic to call from multiple reports. All our reports have start and end date filter and we face similar issue when user selects same date.

    Rule: Report_getConvertedStartDateTime

    /* -- inputDate_dte rule input is of type DATE -- */

    with(
    \tlocal!formattedDate: text(ri!inputDate_dte, "mm/dd/yyyy HH:mm z"),
    \tlocal!splitFr: split(split(split(local!formattedDate, " "),"/"),":"),
    \tuserdatetime(
    \ tlocal!splitFr[3],
    \ tlocal!splitFr[1],
    \ tlocal!splitFr[2],
    \ tlocal!splitFr[4],
    \ tlocal!splitFr[5],
    \ t"00"
    \t)
    )

    =========================

    Rule: Report_getConvertedEndDateTime

    /* -- inputDate_dte rule input is of type DATE -- */

    with(
    \tlocal!formattedDate: text(ri!inputDate_dte, "mm/dd/yyyy HH:mm z"),
    \tlocal!splitFr: split(split(split(local!formattedDate, " "),"/"),":"),
    \tuserdatetime(
    \ tlocal!splitFr[3],
    \ tlocal!splitFr[1],
    \ tlocal!splitFr[2],
    \ tlocal!splitFr[4],
    \ tlocal!splitFr[5],
    \ t"00"
    \t) + intervalds(23, 59, 0)
    )

© 2024 Appian. All rights reserved.