asked on

Need help converting this crystal syntax to work in SQL Reporting Services

I need the to convert this crystal syntax to SRSS to extract from my database , since i have very limited knowledge on both reporting tools . I can basically say that the first what i am trying to achieve is select all client types in my database equal to "CN" from the beginning of the month to current date.

I know that {@StartDate} and {@EndDate} are formula fields in crystal but i am not sure how to create them in SRSS.

{CLHST.CSTXTP} = "CN" and
{CLHST.CSENDT} >= {@StartDate}
{CLHST.CSENDT} <= {@EndDate}

Code for {@StartDate}
==================
DateVar NextWorkDate := DataDate - 1;
StringVar field:= totext((NextWorkDate),"ddMMyy");
StringVar yr;
StringVar mo:= field[3 to 4];
StringVar dt:= '01' ;

if field[5 to 6] = "00" to "79" then
yr:= ToText(100+ ToNumber(field[5 to 6]),0,"")
else
yr:= ToText(00 + tonumber(field[5 to 6]),0,"");
tonumber(totext(yr) + totext(mo) + totext(dt))

Code in formula {@EndDate}
======================
DateVar NextWorkDate := DataDate - 1;
StringVar field:= totext((NextWorkDate),"ddMMyy");
StringVar yr;
StringVar mo:= field[3 to 4];
StringVar dt:= field[1 to 2];
if field[5 to 6] = "00" to "79" then
yr:= ToText(100+ ToNumber(field[5 to 6]),0,"")
else
yr:= ToText(00 + tonumber(field[5 to 6]),0,"");
tonumber(totext(yr) + totext(mo) + totext(dt))

Thanks

Chris McGuigan

firstly, you are better off doing as much of this in the SQL query rather than relying on the report itself to do it. You can do this in the GUI view but I much prefer using straight SQL - it's called the Generic Query Designer in SSRS.

Simply put the query would look something like;
Select * From CLHST
Where CSTXTP = 'CN'
And CSENDT Between @StartDate And @EndDate

Now how we tackle the calculation of the start dates depends on whether this is a parameter that the user can alter at run time or not. I can't tell what 'DataDate' is in your example.

But you could make it a parameter and if the user is not allowed to change it, just mark it as Hidden.
So choose the Report menu and Report Parameters, add a new one called StartDate (you might find it's already be created because you used it in the query).
Make the default value something like;
=DateAdd(Day, -1, Today()
Here you can use all the standard VB.Net expressions to get the date you want.

Clarify the "Parameter" issue a bit more then I can be more specific.

david8080

ASKER

Parameter cannot be altered at run time so it has to be hidden from the user , 'DataDate' in this example refers to today's date. So for this example in in run the report today in has to select all client type equal to 'CN' between 1st June up to yesterday since my back end processing is always up to the previous day.

But there is one slight problem in the formatting , since all dates in the CSENDT field are stored in CYYMMDD format or 1080616 ( 16 June 2008) . I believe some coding is needed otherwise is will not work , right ?

Thanks.

ASKER CERTIFIED SOLUTION

Chris McGuigan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer