0

I have an input control that will need to be populated by a query. When I wrote the code in My SQL Server, it works perfectly as planned. However, when I put it in the the input control, the drop down is blank. Are cases not allowed in the input control queries? Is there a work around?

here is the seemingly simple sql code:

SELECT a.PRSTART as date,
  case when(a.prstart <= getdate() - 7 and a.prfinish - 1  >= getdate() - 7) then 'Previous Period'
            when (a.prstart <= getdate() + 7 and a.prfinish - 1  >= getdate() + 7) then 'Next Period'
            else 'Current Period' end as period
        FROM   PRTIMEPERIOD a
        WHERE  
        (a.prstart <= getdate() and a.prfinish - 1  >= getdate())
        or
        (a.prstart <= getdate() - 7 and a.prfinish - 1  >= getdate() - 7)
        or
        (a.prstart <= getdate() + 7 and a.prfinish - 1  >= getdate() + 7)

as you can see, I need to return "Previous", "next", or "current time period" dynamically along with the date calculated. for the input control, previous, next, and current would be the visible field, while the date would be used as the value column.

thanks!

Alex K
  • 22,315
  • 19
  • 108
  • 236
Matt
  • 377
  • 1
  • 4
  • 14
  • We hade some sort of the same requirement. The input controls weren't that flexible. In the end we created a small helper class which calculates dates on given parameters. So there would be a value for "Last Month" with a -1, "-2 Months" with a -2 and so on and our functions will provide the first and last day of the month with some Java code. – tobi6 Sep 09 '16 at 11:52

1 Answers1

1

You could enable the query log in the jasperreportserver settings and check what is going on in the server.

Best regards

Iceey
  • 63
  • 2
  • 6