At BO Report Level the other side of WHERE Clause should be a constant… So what you can do is have a Flag Variable kinda stuff with IF THEN ELSE and check with the user entered date and have 1… Next in the Variable condition use = 1…
first create dimension and save the value of prompt in this and then put in condition
e.g of userresponse
dimensionVar=UserResponse(“Calls” ,“1. Please select start of date range dd/mm/yyyy)”)
“Calls” is the DP name and “1. Please select start of date range dd/mm/yyyy)” is the prompt name.
SELECT * FROM TableA
WHERE (TableA.WCDate BETWEEN cast(@variable('WCDate - mm/dd/yy') as datetime)-14 AND cast(@variable('WCDate - mm/dd/yy') as datetime))
So its casting the response as a datetime and retrieving the last 3 weeks worth of data based on user input.
So when I get the =UserResponse() its automatically a measure because its already casted as date and unfortunately I cant use this in the where part of the variable because it expects a dimension object and not a measure.
e.g. MaxDate=If (Max()=UserResponse(DataProvider() ,“WCDate - mm/dd/yy”)) Then “Yes” Else “No”
MaxDate is a measure and cant use in (sum(measure) where (MaxDate=“Yes”) returns syntax error.
What I’m trying to do is produce a crosstab report like this:
WCDateA WCDateB WCDateC
DimA DimB measure measure measure
and highlight via alerter where measure > 3 in all of the dates.
e.g. Highlight in red the following:
4 5 6
7 6 5
and not
5 2 8
2 9 3
1 8 7 etc.
This is already working if I hard code the dates in the report but I want this to be dynamic and its really messy if I use 3 dataproviders for this.
Just imagine if the user want to see the previous 6 or 8 weeks worth of data.
I think you are not using the prompt in Qurey use the
where Date Dim=@prompt() this will return the required result. then no need of if condition. Control from Qurey.
Unfortunately @prompt() doesnt work in FHSQL.
Nonetheless I have completed this task with a simple t-sql query using case statements coupled with union and having criteria on the grouped list.
Now I can use this query as my Dataprovider.
But If anybody can get it to work that would be greatly appreciated.
SELECT * FROM TableA
WHERE (TableA.WCDate BETWEEN @prompt('WCDate - mm/dd/yy','D',{'01-JAN-2001'},MONO,FREE)-14 AND @prompt('WCDate - mm/dd/yy','D',{'01-JAN-2001'},MONO,FREE))
However when I enter “04/05/04” I get the following error msg:
[Microsoft OLE DB Provider for SQL Server] : Syntax error converting the varchar value ‘04/05/2004 00:0:0’ to a column of data type int.245
So I casted the @prompt input value to a datetime and it goes through and
I capture the =UserResponse() to a var say and now its a detailed object (no associated dim).
But when I do this =sum() In (,) where ( = ) I get a syntax error.
hi,
use this in your qurey to cast the date in string
e.g
select convert(varchar(20),dob,101) Date_of_birth from Person
which mm/dd/yyyy
and then check it.
(BOB member since 2002-11-08)
(BOB member since 2002-06-20)
(BOB member since 2003-08-01)
I dont think its possible cause:
(BOB member since 2002-06-20)
(BOB member since 2003-08-01)