• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Crystal doesnt show paramers in Subreport links: Data Types Mismatched

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.


  • 2
  • 2
1 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.

vmrfadminAuthor Commented:

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,
vmrfadminAuthor Commented:
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!!!!!

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.


Featured Post

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now