Skip to Content

Group By /Having problem with dynamic AsOfDate

Hello - I have a requirement: Given a date/date range(AsOfDate), show the credit limit for that customer on that date/date range. With SQL it is easy to do with Where Effective date <= AsOfDate, Group by and Having clause (Having Effective date =Max).

In Universe Designer, I first tried with derived table for user to enter AsOfDate Prompt, then get a snapshot of the credit limit on that date. But user do not like this because this will limit their choice of date operator(In, equal to, Between And etc; can only hardcode one in the Derived SQL script).

This requirement also makes Pre-condition option difficult, because in the Subquery for getting MaxEffective date, will still need hard code prompt for as of date. (AsOfDate filter outside of the subquery won't work, correct?)

So now I had a struggle with the following dataset displayed at report level. I also tried making a Max(LoadTime) object and let the auto generated Having clause to handle it. But not working because the Group By clause will include Credit limit if you choose the object. Ideally it should only show two rows marked in yellow. Please help, thx a lot!

credit-limit.jpg (68.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Sep 18, 2017 at 01:01 AM

    If you don't want to use a derived table you might have some looking using a window function within your dimension. The actual syntax could vary depending on your database, but here's how that would look in T-SQL:

    SELECT LAST_VALUE(CreditLimit) OVER (PARTITION BY AsOfDate ORDER BY LoadTime) FROM ...
    Add comment
    10|10000 characters needed characters exceeded