Solved

Crystal Reports XI command parameters revert to string

Posted on 2009-07-08
12
366 Views
Last Modified: 2012-08-13
I have a crystal report that has a subreport that uses a stored procedure. I close the RPT file and when I reopen it the parameters in the command section go from being a date (which is correct) to being a string (which is incorrect).

This immediatly unlinks the parameter fields on my main report because my parameters are set as a date so I get the proper calander box.  You cannot link a string param in a command to a date (or date/time) in a report.

Anyone know what could be causing this?  It is driving me nuts.
Stored Procedure Parameters:
 

@p_network_id varchar(20),				

@p_ActvStatus int,				

@p_startdate datetime,			

@p_enddate datetime

Open in new window

0
Comment
Question by:eparmpd
  • 6
  • 4
  • 2
12 Comments
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24806906
I've never called a stored procedure directly from crystal, so maybe this should be obvious to me, but when you say "the parameters in the command section", do you mean the parameters being passed to the stored procedure, or the parameters being passed from the main report to the sub?
0
 

Author Comment

by:eparmpd
ID: 24806928
I went into the database expert and added the command:
Exec [SP_CSR-701_CallMemo]  '{?CurrentCeUser}','{?Scope}','{?Status}','{?Start Date}', '{?End Date}'

It also requires you to define the above parameters...  I defined CurrentCeUser, scope and status as a string.  Start and end dates are defined as datetime
0
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24809973
I really don't know, I can tell you we have stored procs we call from .net, and for the datetime parameters we pass them into sql as strings, then CONVERT them to datetime in the proc.  I'm not sure why it was done that way, but if you have access to edit the proc and it is not used by a bunch of other processes that still expect dates, it could work for you.

If you choose Verify Database before you close the report, does it complain about the parameters at that point?
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 24810332
This doesn't really answer your question, but if all that the command does is execute a stored procedure, why not just execute the procedure directly from your report?  When you reference the procedure directly, CR automatically adds the parameters that the procedure requires, with the correct type for each parameter.

 James
0
 

Author Comment

by:eparmpd
ID: 24813035
I know there is a reason why we did not directly add the stored procedure to the report.  I believe it had something to do with it being a subreport that relies on the main report to call CurrentCEUser...
0
 

Author Comment

by:eparmpd
ID: 24813063
When I verify the database it is fine.  It was when i closed it and reopened it I had a problem.  I also have a problem randomly when I run it.

The reason that I am using a datetime parameter is because if you use a string you cannot use the calendar to select the days in the report parameter.  Even if you set the parameter as a string and set up a virtual field to convert it to a string Crystal does not recognize the virtual field as a string you can link the report and subreport with.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:eparmpd
ID: 24852060
I just gave up the boat on this... I brought the stored procedure in directly instead of calling it in a command.  Does anyone know if this is more/less efficent?

Thanks

Mike
0
 
LVL 34

Expert Comment

by:James0628
ID: 24856581
You mean that the report is now just executing the stored procedure, instead of a command that executes the procedure?  I don't actually know which is more efficient, but I would _expect_ executing the procedure directly to be more efficient than executing a command that executes the procedure.  That just seems like common sense (the fewer steps the better).  But I could be wrong.

 However, if you mean that you copied the stored procedure into a command, so now the report is executing a command that contains the stored procedure code, instead of a command that executes the stored procedure, I'd have to guess that the command that executes a stored procedure would probably be more efficient, because of the way the server can optimize stored procedures.  But that's also really just a guess, and I'm sure it would depend on your stored procedure.  If it's very simple, it may not matter much either way.

 James
0
 

Author Comment

by:eparmpd
ID: 24858738
I actually moved the stored procedure in using the database expert.  I no longer have any code in the command.

0
 
LVL 34

Expert Comment

by:James0628
ID: 24865663
OK.  That seems like the best way to use them to me, but the fact is that I've always done it that way, so I don't really have anything to compare it against.

 James
0
 

Author Comment

by:eparmpd
ID: 24868845
Then I have a question for you... If we make an update that it is code only.... Do I have to verify the database again?  Or does that happen every time I run it?
0
 
LVL 34

Expert Comment

by:James0628
ID: 24875212
I don't think you need to do a "verify database" unless the form of the output from the procedure is changed (a column is added or removed, a column size is changed, etc.).  That's the only time that I worry about doing one.

 If you want to try to force the report to look for changes, you can try checking the "Verify Stored Procedures on First Refresh" option under File > "Report Options".  I really can't say what effect it will have, since I don't normally use it.

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

21 Experts available now in Live!

Get 1:1 Help Now