Solved

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

Posted on 2004-08-01
7
1,062 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 100

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 500 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 run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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