Brio SQL Syntax

[Tony Tillyer]

I've come a long way with my Brio Queries!

However, I am still not able to get an SQL statement correct, to be able to produce a variable on which I can base a Broadcast Query, so that it reports on the previous day's activity.

The current days activity can be filtered using the following statement:

CASE WHEN Time_Dim.Query_Date = CONVERT (varchar, GETDATE() , 112) THEN 1 ELSE 0 END

...then I would set up a Variable Limit (to "1") for the Time_Dim.Query_Date, to produce today's data, which works OK.

What I need to do is produce a report for *yesterday's* data, not today's data. I thought this might work:

CASE WHEN Time_Dim.Query_Date = CONVERT (varchar, GETDATE()1, 112) THEN 1 ELSE 0 END

...but it doesn't.

Can anyone tell me where I am going wrong? Perhaps my syntax is not correct for SQL, or do I perhaps need to add in more conditional/variable formatting?

I'm really confused now because I thought that it would be very straightforward to get a Query to Broadcast, based on yesterday's data but it does not seem that easy, anymore!

TIA