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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.