Solved

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

Posted on 2008-06-15
3
612 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now