Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing Date Parameters to SQL Server 2000 via ODBC in Crystal Reports 9

Posted on 2004-08-01
7
Medium Priority
?
1,072 Views
Last Modified: 2008-01-09
I am having problem with getting data back when passing date parameters to SQL Server 2000 in Crystal Reports 9. When I pass two date parameters (in MM/DD/YYYY format), SQL Server doesn't seem to accept these parameters as valid values. But when I replace the parameters with actual date values in qoutes (e.g. '01/01/2004' & '07/29/2004'), the query works fine. What could be the problem? I have 2 date parameter (dt_bgn & dt_end) fields in my Crystal Report. The user is prompted to enter the values for these fields. Below is my query, which I added as command query in Crystal Report. The query is against a SQL Server view to get specific rows that falls between dt_bgn & dt_end.

Below is SQL for SQL Server Veiw:
CREATE  VIEW REPORT_ALEG (TX_CDESC_TYPE2, CD_CAN, TX_OFC_DIV, TX_TOWN, DT_RFRD)
AS
      SELECT TX_CDESC_TYPE2,
               CD_CAN,
               TX_OFC_DIV,
               TX_TOWN,
               DT_RFRD  
          FROM ALLEGATION ALEG,
               CPS_REPORT CPSR,
               WORKER     WRKR,
               LOCATION   LOC,
               CODE_DESC  CDES
         WHERE CPSR.CD_RTM_DSGNTN   IN (1,2,3)
           AND (CPSR.CD_SCRNIN      = 1
                OR CPSR.CD_SCRNOUT  IN (1,6,7,8,9))
           AND CPSR.CD_SPVR_STAT    IN (1,2)
           AND ALEG.ID_CPS          = CPSR.ID_CPS
           AND ALEG.CD_CAN          IN (1,2,3,4)
           AND WRKR.ID_PRSN         = CPSR.ID_UP
           AND LOC.CD_LCTN          = WRKR.CD_LCTN
           AND LOC.CD_OFC_DIV       = WRKR.CD_OFC_DIV
           AND LOC.CD_DEPT_UNIT     = WRKR.CD_DEPT_UNIT
           AND CDES.ID_GRP          = 'REGION'
           AND CDES.ID_GRPI         = WRKR.CD_OFC_DIV
      GROUP BY TX_CDESC_TYPE2,
             LOC.TX_OFC_DIV,      
               LOC.TX_TOWN,
               ALEG.CD_CAN,
               CPSR.DT_RFRD

Below is my query, which I added to the report using add command in the database connection in Crystal Report. The query is used to get specific rows that falls between dt_bgn & dt_end:  
SELECT TX_CDESC_TYPE2
      ,TX_OFC_DIV
      ,TX_TOWN
      ,Sum(Case CD_CAN When 1 then 1 Else 0  End)                            AS NUM_PHYS
      ,Sum(Case CD_CAN When 2 then 1 Else 0 End)                            AS NUM_ABUS
      ,Sum(Case CD_CAN When 3 then 1 Else 0 End)                           AS NUM_MNTL
      ,Sum(Case CD_CAN When 4 then 1 Else 0 End)                           AS NUM_NGLT
   FROM REPORT_ALEG
WHERE CONVERT(VARCHAR(10),DT_RFRD,101)
BETWEEN '{?dt_bgn}' AND '{?dt_end}'
GROUP BY TX_CDESC_TYPE2,
                    TX_OFC_DIV,
                    TX_TOWN

I have tried different format of the input parameter by using Crystal Datepart and Concatenation to converting the input MM/DD/YYYY dates into YYYYMMDD. After that I replaced the " CONVERT(VARCHAR(10),DT_RFRD,101)
BETWEEN '{?dt_bgn}' AND '{?dt_end}' " in the above SQL with "CONVERT(VARCHAR(08),DT_RFRD,112)
BETWEEN '{?dt_bgn}' AND '{?dt_end}' ". But no luck :-(. Nothing seem to work other than using actual values for begin and end dates, which kills the whole purpose of the report.
Thanks in advance for your help.
Baluch.

0
Comment
Question by:baluch
  • 3
  • 3
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 11690208
I suspect the problem is that Crystal doesn't recognize the dates as dates.  When you put them in quotes then it does a conversion to a date format for you.

Are you prompting from Crystal or an application?

mlmcc
0
 
LVL 1

Expert Comment

by:bobsterboy
ID: 11691014
In my testing, I didn't add the parameters to the actual command, I added the parameters as a record selection formula.   That way, you let Crystal handle the conversion. One way to check what Crystal is actually sending to the database is to check the Database - Show SQL Query.  It'll actually show the dates being passed in the format the Crystal is sending them.

My parameter fields were both datetime format, you might use date, it didn't matter in my report.  

Here's what my SQL Query looked like (I've cut out some of the fields, but the WHERE clause is the same)

SELECT "CM20301"."CMRECNUM" FROM   "TWO"."dbo"."CM20301" "CM20301"
 WHERE  ("CM20301"."MODIFDT">={ts '1999-01-01 00:00:00'} AND "CM20301"."MODIFDT"<{ts '2004-08-02 01:21:12'})

Weird little function 'ts', huh?
0
 

Author Comment

by:baluch
ID: 11697783
I am prompting for parameter values in Crystal Reports. We are not using any application to generate reports. Basically, there are few users that have Crystal Reports 9 installed on their PC and they execute the reports on request from other users. All they do is input parameters sent by other users and generate the reports. After generation they email or fax the report back to the requestors. Pretty simple deployment. Just couldn't figure out why the parameter values are not being accepted by Crsytal Reports 9.
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 

Author Comment

by:baluch
ID: 11698806
bobsterboy:
I can't use the record selction formula since I am counting number of rows per CD_CAN within a given date range (which is my where clause). I could have used the report selection if all the report required is to display the row returned as it is. Instead of the row count. I have used the Crystal Data Tool's SQL Expert to run the same SQL and it ran fine. It's just not running in the Crystal Reports 9.

SELECT REPORT_ALEG."TX_CDESC_TYPE2"
              ,REPORT_ALEG."TX_OFC_DIV"
              ,REPORT_ALEG."TX_TOWN"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 1 then 1 Else 0 End)  AS "NUM_PHYS"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 2 then 1 Else 0 End)  AS "NUM_SEXL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 3 then 1 Else 0 End)  AS "NUM_MNTL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 4 then 1 Else 0 End)  AS "NUM_NGLT"
   FROM "databasename"."dbo"."REPORT_ALEG" REPORT_ALEG
WHERE REPORT_ALEG."DT_RFRD" BETWEEN '{?dt_bgn}' AND '{?dt_end}'
GROUP BY REPORT_ALEG."TX_CDESC_TYPE2"
               ,REPORT_ALEG."TX_OFC_DIV"      
               ,REPORT_ALEG."TX_TOWN"
0
 
LVL 1

Expert Comment

by:bobsterboy
ID: 11698945
So what you're saying is that my answer would work.  If you use the record selection formula, it'll just add the where clause and it will work as you would like.  The where clause is used prior to any grouping or aggregate clauses.  It just sends the text to SQL Server and SQL Server does the work.  Did you try to use it this way, or are you just unsure that it'll work?   I can't see any reason why this shouldn't work as you'd expect.  

SELECT REPORT_ALEG."TX_CDESC_TYPE2"
              ,REPORT_ALEG."TX_OFC_DIV"
              ,REPORT_ALEG."TX_TOWN"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 1 then 1 Else 0 End)  AS "NUM_PHYS"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 2 then 1 Else 0 End)  AS "NUM_SEXL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 3 then 1 Else 0 End)  AS "NUM_MNTL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 4 then 1 Else 0 End)  AS "NUM_NGLT"
   FROM "databasename"."dbo"."REPORT_ALEG" REPORT_ALEG
GROUP BY REPORT_ALEG."TX_CDESC_TYPE2"
               ,REPORT_ALEG."TX_OFC_DIV"    
               ,REPORT_ALEG."TX_TOWN"
0
 

Author Comment

by:baluch
ID: 11699074
bobsterboy:
It will not work since the DT_RFRD column is not part of the SELECT. U have to have DT_RFRD in ur SELECT to be shown in the Crystal Reports under database fields that u can use in the Record Selection Expert. I have tried that this soultion will require additional grouping to an alrady complex report...
0
 
LVL 1

Accepted Solution

by:
bobsterboy earned 1500 total points
ID: 11699304

If you can get to the SQL Server, how about using a stored proc instead?  Crystal looks at stored procs just like tables.
That way, you can pass your parameters to the stored proc and it doesn't change the view.

Here's the code:
Create Procedure spcViewCounts
@BeginDate as smalldatetime,
@EndDate as smalldatetime
AS
SELECT REPORT_ALEG."TX_CDESC_TYPE2"
              ,REPORT_ALEG."TX_OFC_DIV"
              ,REPORT_ALEG."TX_TOWN"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 1 then 1 Else 0 End)  AS "NUM_PHYS"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 2 then 1 Else 0 End)  AS "NUM_SEXL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 3 then 1 Else 0 End)  AS "NUM_MNTL"
              ,Sum(Case REPORT_ALEG."CD_CAN"
                       When 4 then 1 Else 0 End)  AS "NUM_NGLT"
   FROM "databasename"."dbo"."REPORT_ALEG" REPORT_ALEG
WHERE REPORT_ALEG."DT_RFRD" BETWEEN @BeginDate and @EndDate
GROUP BY REPORT_ALEG."TX_CDESC_TYPE2"
               ,REPORT_ALEG."TX_OFC_DIV"    
               ,REPORT_ALEG."TX_TOWN"

Add this procedure to Crystal and it automatically adds these two parameters to your report:
@BeginDate, @EndDate.  
Drag these two parameters onto the report, you can use them or hide them, doesn't matter.
When the report is refreshed, it asks for values for these fields and when supplied, returns the proc results as a table.

Hope this works


0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

972 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