Pass Date Formula to SP 2

[cwsstins]

I'm not a programmer, but I play on on TV. I've got a report (CR v11) that uses a formula to change string field to date (@Yesterday). Then selection formula that says {@Yesterday} = CurrentDate -1

The reports run against a SP, which looks like this:

ALTER PROCEDURE [dbo].[_spNumbers]

@Yesterday datetime

AS

SELECT 1,2,3
FROM tbl
where tbl.user = 'abc'
AND tbl.date = @Yesterday

When I run the report, it throws an error that the sp expects a parameter which was not supplied. I'm know I'm doing something (more than likely multiple somethings) wrong, but I don't know what it is. Your help is sincerely appreciated and severely needed.

 

[Turkbear]

Hi,
Be sure that you have a parameter defined in your report ( not a formula) that prompts for a DateTime ( not a string) and that will get passed to the SP.
BUT:

Not sure why that is an ALTER PROCEDURE since CR cannot report on such a object; are you using it as a command instead of connecting to a database that has an SP defined or what?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cwsstins]

Thanks Turkbear, it's connected to a db and hitting that sp as the source. Not sure why the alter proc, this wasn't my sp. Should that statement be removed?

As far as parm, it does work when using a parm, but I was really hoping to not force the user to enter a parm, but rather to always use yesterday's date (hence the CurrentDate -1). Any workaround there?

 

[Turkbear]

Hi,
Yes but the SP needs to be coded to do that when it gets the recordset ( otherwise you would need to pass it on execution of the report). If it is ALWAYS going to be
Today-1
then it can be coded into the SP as :
( Depends on the database used for excact syntax)

Create or Replace PROCEDURE [dbo].[_spNumbers]
AS
SELECT 1,2,3
FROM tbl
where tbl.user = 'abc'
AND tbl.date = @Sysdate-1

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cwsstins]

Thanks...what I wound up doing is hard-coding the getdate()-1 directly into the sp and then removing the date parm from the report entirely. This seems to do the trick.

 

[cwsstins]

Had another question about SPs. Since I modified this SP, I saved it as a new one because there are other reports that still use the original SP.

For my report, I attempted to change the datasource and ran into a problem. I clicked Database >> Set Datasource Location. From there, I click on my original SP and in the lower pane, click on the new one. I click Update and nothing happens.

Any idea what's going on there and how to fix it or another way to change the datasource to the new SP?

 

[demosoc]

Hey there ?s-

If it is using the correct stored procedure, doing what the new SP is set up to do, namely use yesterday, than it did update. Go back into "Set Datasource Location" and(in the upper pane) open the SP that appears to be incorrect. Open up it's properties. It will show you the true data source. If it is indeed pointing at your new SP than all is well! To dis-confuse-ulate your report, you can change the name of the SP to the actual datasource in the Database Expert. Just select the confusingly aliased SP and go all F2 on it, and then you can rename it to the current SP.

If, however, it's not choosing the correct SP than just punch your computer in the neck.

Good luck-

 

[cwsstins]

Man, that had me going crazy for a good hour last night. You're right, it is changed but there is no visible change displayed, you have to check the properties. Thanks much for that tidbit of knowledge...my computer's neck will survive at least one more day!

 

[Turkbear]

Hi,
Glad you got that great tip..Crystal drives folks crazy by not changing the name of the datasource ( as shown in the explorer) when the location has been changed..I imagine this is to avoid having to change all the explicit references to the datasource that may be within the report ( formulas, selection criteria, etc. ).






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cwsstins]

Yeah, I can see what you mean. Actually, after I finished with the Update, I went and changed the name of the SP in Database Expert. I noticed that the parameter had lost its field value after doing that and I had to manually select it again. I'm hoping that there are no other elements that were altered as a result of making that name change.