Skip to Content

Group By /Having problem with dynamic AsOfDate

Aug 25, 2017 at 02:55 PM


avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

William Ayd 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:

10 |10000 characters needed characters left characters exceeded