Crystal doesnt show paramers in Subreport links:  Data Types Mismatched

Posted on 2004-11-30
Last Modified: 2008-02-01
I am using Crystal Reports 9.0 and have created a report with a subreport.  The main report passes several paramaters to the sub-report and then runs a stored procedure.  I am passing the values via Subreport linking integrated in Crystal.

My problem is that when I try to link the main report fields with the Sub-Report parameters I cannot get the date/time parameters to show up.  This means that they are incompatible data/types.  My main report is talking to an Access XP database and the datatypes are listed at date/time in design view of Access and the stored procedure paramters are set to datetime.  Why would they not be compatible?  When I look at the table structure of the Access Database in Crystal it shows the datatype as Date and the SQL as DateTime.  

How do I go about fixing this situation....any help or suggestions would be greatly appreciated.


Question by:vmrfadmin
    LVL 13

    Accepted Solution

    You can convert the Date field to a DateTime in a formula, and use the formula to link the subreport parameters:


    Then, right click the subreport > Change subreport links > pull your formula over to the 'Fields to link to' listbox, and then you should be able to get to your stored proc's DateTime parameters.


    Author Comment


    Thanks for the suggestion.  The formula seems to work great.  I have both formulas created and tested them.  They now show the full DateTime information.  I have looked at both datasources thru the Crystal browse feature and they are now show the Date/Time.  
    However, when I choose the change sub-report linking and select the formula, the parameter from the Stored Procedure still doesnt show.  

    I am sure I am stating the parameter correctly in the stored procedure.  I have listed the sp below.

    create Procedure sp_test
          @InputProjectNumber varchar (5),
          @Startdate datetime,
          @Enddate datetime
    SELECT A_GLA, A_Sub_GLA, A_Detail, [A_Account Description], A_Project, Sum([Actual Post Amount_GLTran]) AS [SumOfActual Post Amount]
    FROM dbo.vwGLTransaction
    WHERE ((([Post Date]) Between @Startdate And @Enddate))
    and [Actual Post Amount_GLTran]>'00' and [A_Project]= @InputProjectNumber
    GROUP BY A_GLA, A_Sub_GLA, A_Detail, [A_Account Description], A_Project
    HAVING (((A_GLA)> '4') AND ((A_Sub_GLA) >= '0') AND ((A_Detail) >= '00'))
    ORDER BY A_GLA, A_Sub_GLA, A_Detail, [A_Account Description], [SumOfActual Post Amount];

    The formulas I created are listed below.



    Not sure what else to do.....................

    Any suggestion would be greatly appreciated.....

    Thanks Thanks Thanks,

    Author Comment

    I think I have solved this problem.  I am not sure why, but when I create a stored procedure with a few parameters ...such as

    @param1 varchar (5)
    @param2 datetime

    These both show up in the parameter list automatically within Crystal, however the @param2 with a datetime datatype in the sp shows as a string in Crystal.  I couldnt understand why it doesn this.  I played around a little and created the parameter within Crystal as Datetime with a completely different name....@paramtest with a date/time.  Then when I added the stored procedure via add database, magically the @param2 then showed up as a datetime datatype.  Seems like a bug or something.  I cant figure out why crystal doesnt bring the parameter in as a date/time datatype.  Maybe its a some kind of setting or requirement that you specifiy a date/time datatype first in the stored procedure.  

    I will probably never know because you cant get a real answer from Business Objects.

    Thanks again Dave...your help is much appreciated!!!!!

    LVL 13

    Expert Comment

    Just ran a test using Access 2000/CR 9/ subreport w/ SQL Server stored proc.

    I had no problems linking the DateTime field from my Access db to the stored proc parameter, or if I defined a parameter as DateTime and used it for the main report and the 'link' for the subreport.

    However, if I converted a Date to DateTime in a formula, it didn't see the stored proc parameters when trying to link it to the subreport.

    The workaround I found, was to alter the proc parameters from DATETIME to SMALLDATETIME.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 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

    9 Experts available now in Live!

    Get 1:1 Help Now