Skip to Content
0
Aug 25, 2017 at 02:55 PM

Group By /Having problem with dynamic AsOfDate

102 Views Last edit Aug 25, 2017 at 03:12 PM 2 rev

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!

Attachments

credit-limit.jpg (68.9 kB)