Solved

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

Posted on 2008-06-15
3
616 Views
Last Modified: 2008-06-17
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
0
Comment
Question by:david8080
  • 2
3 Comments
 
LVL 18

Expert Comment

by:chrismc
ID: 21789369
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.

0
 

Author Comment

by:david8080
ID: 21790051
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.
0
 
LVL 18

Accepted Solution

by:
chrismc earned 50 total points
ID: 21790906
If it's always based on todays date, you could move all this processing into SQL. It really depends on how advanced your SQL skills are compared to your VB.Net skills.

The formatting of the date does need further code but it can all be done as an expression either in SQL or VB. Personally I'd do it in SQL and maybe write a specific function.

Create Function SpecialDate(pDate As DateTime)
    Returns Char(9)
Begin
    Declare @String As VarChar(10)

    Set @String = Convert(VarChar(8), pDate, 12)

    If Convert(Int, Left(@String, 2)) Between 0 And 79
        Set @String = '1' + @String

    Return @String
End

Then use that function in your main query.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to increase the row limit in Jasper Server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question