I want to pass a formula result to a stored procedure 1

[ajaeger]

Crystal 8.5 and SQL Server 2000

I have a formula in my report that returns a comma delimited list of states (@paramSTATES). It is based on a variable coming from a subreport (strSTATES). For example, it might be 'VA','MD','DC'

The datasource for my report will be this stored proc (sp_Emergency). I need to pass the value from this formula (or variable) into the stored procedure so I can use it in a select statement, such as, Select ID, LAST_NAME from Customers where STATE in @paramSTATES which would actually read as Select ID, LAST_NAME from Customers where STATE in ('VA','MD','DC')

I'm sure it's simple, but what's the syntax to do this?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[IdoMillet]

Only if you reverse the logic. The main report should establish the value and then pass it as a link into a subreport that uses the SP as the data source and the linked value as a parameter.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ajaeger]

OK, that makes more sense. Now I have the main report with a formula @varSTATES which I link into my subreport. It shows up under the parameter list in my subreport as PM-@varSTATES. The datasource for the subreport is sp_Emergency.

I have the stored proc syntax as something like:

Create procedure rpt_Emergency
AS
Create table #Emergency...
Insert into #Emergency...
Select ....
From ....
Where MyTable.STATE in ('VA','MD','DC')

How do I reference the PM-@varSTATES in the syntax so that I can replace ('VA','MD','DC') with the parameter PM-@varSTATES?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[IdoMillet]

Create a stored procedure parameter...

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ajaeger]

Sorry, I'm missing a simple connection here. In the main report I have a formula that returns the string of state codes that I captured from the Crystal Parameter. It's named @StringOfStates. I also have this value saved to a shared stringvar, @varSTATES. Then, I pass @varSTATESto the subreport.

The subreport datasource is the SP below. In the subreport I now have two parameters. One is Pm-@varSTATES from the main report. The other is @STATE_CODE from the SP below.

Create procedure rpt_Emergency
@STATE_CODE varchar(255)
AS
Create table #Emergency...
Insert into #Emergency...
Select ....
From ....
Where MyTable.STATE in ('VA','MD','DC')

I need to change the last line in the SP so that it pulls the value from Pm-@varSTATES instead of having ('VA','MD','DC') hard coded.

If I change the syntax to

Where MyTable.STATE in @varSTATES I get an error stating "Must declare the variable @varSTATES."

I guess what I need to figure out is how do I get the variable into the SP?

Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[IdoMillet]

See:

http://www.tek-tips.com/viewthread.cfm?qid=1290457&page=1

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ajaeger]

I can't figure out how to read/use what's in that thread to help me solve my issue.

I'm not sure if I'm asking this correctly. I need the value from a Crystal formula to be pased into the stored procedure and used in a 'where' statement. I just don't know how to get it into the stored procedure, how to ask the right question or what to google to get relevant results.

When I set up a parameter in the stored procedure, it then causes Crystal to prompt me to enter that parameter value. Normally that's fine, but in this case, the parameter value that I want to have passed to the stored procedure is in a Crystal formula.

Any specific coding help is definitely appreciated. I have lots to write to get this report finished, but this one little issue has me at a complete standstill.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[ajaeger]

Still working on this. Here's what I've done:

1. In the main report, under Edit>>Subreport Links, I linked @varSTATES to my subreport. I did not check "Select data in subreport based on this field" because the SP parameter @STATE_CODES did not show in the drop-down list.

2. Instead, I went into the subreport, under the record selection formula and entered {?@STATE_CODE} = {?Pm-@varSTATES}.

When I run the main report, I'm prompted to enter the Crystal Parameter that generates varSTATES. This is what I expect. I selected VA, MD and DC from my drop down list multi-select list. To confirm varSTATES looks like I expect, I have that field in my main report and it looks like I want it to - 'VA','MD','DC'.

However, I'm also prompted for @STATE_CODE - the parameter set-up in the stored procedure in the subreport. Since I want the value of varSTATES to flow to this, I don't want to be prompted for it as well. Also, the whole reason why I'm jumping through hoops to get it this way is because I need the user to enter multiple value from a drop down list (can only do with a Crystal Parameter) and then have that string sent to a stored procedure.

A bigger issue is that my subreport comes up blank. Somehow the string of states isn't getting from the main report to the subreport via the linking.

I'm using basic syntax in the SP, just to make sure that @STATE_CODE is populating corrected. All the SP should do is return the string of states 'VA',MD',DC'

Create procedure rpt_EmergencyPreparedness
@STATE_CODE varchar(255)
AS
set nocount on

Create table #EmergencyPreparedness (STATE char(255))

Insert into #EmergencyPreparedness (STATE)
Select @STATE_CODE

Select * from #EmergencyPreparedness

What's the flaw in this logic?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[esdad71]

Good afternoon,

I am simply attempting to export to a csv file in Crystal v10. I am accessing one VFP OLEDB database in the main report and another in the sub report where I am drawing simple totals.

Problem is that when i export, it is not including the sub report fields. I am not sure if I need to create a shared variable and if so, how?

Can anyone assist me in anyway. Thanks!

 

[lbass]

Please start a new thread, since your topic is unrelated to the current one.

-LB