Solved

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

Posted on 2008-06-15
3
618 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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