Butterfly2
asked on
Crystal Report using 2 store procedures or 1 stored procedure to exec stored procs based on a parameter
I have to make a report using crystal reports 2008. The report has a parameter in which the end user can choose between 2 options - Admitting or Attending.
Base on what is chosen a stored procedure will execute.
I was trying to make one stored proc, that would execute one of 2 stored proc based on a parameter but the problemsis both stored procs have paremeters.
See my SP below it doesnt work.
The last 3 parameters are the ones that are in the stored procedure
ALTER PROCEDURE [Customer].[bhsf_rpt_RunPh
@PatRel varchar(20),
@ProvCode VARCHAR(10),
@StartDate Date ,
@EndDate Date
as
Begin
SET NOCOUNT ON;
If @PatRel = 'Admitting '
Execute Customer.bhsf_rpt_Admittin
Else Execute Customer.bhsf_rpt_Attendin
End
So basically what I am asking is how can I make a stored procudure execute other stored procedures that have paremeters or how can I add to stored procedures to run in crystal reports and have 1 run based on the paremeter that is chosen?
Base on what is chosen a stored procedure will execute.
I was trying to make one stored proc, that would execute one of 2 stored proc based on a parameter but the problemsis both stored procs have paremeters.
See my SP below it doesnt work.
The last 3 parameters are the ones that are in the stored procedure
ALTER PROCEDURE [Customer].[bhsf_rpt_RunPh
@PatRel varchar(20),
@ProvCode VARCHAR(10),
@StartDate Date ,
@EndDate Date
as
Begin
SET NOCOUNT ON;
If @PatRel = 'Admitting '
Execute Customer.bhsf_rpt_Admittin
Else Execute Customer.bhsf_rpt_Attendin
End
So basically what I am asking is how can I make a stored procudure execute other stored procedures that have paremeters or how can I add to stored procedures to run in crystal reports and have 1 run based on the paremeter that is chosen?
ASKER
Will the report have a subreport? I am trying to avoid it, if possible
Do both SPs use the same parameters? Yes
I am not sure how to pass the parameters.
Do both SPs use the same parameters? Yes
I am not sure how to pass the parameters.
What is the reason you want to avoid sub reports? I think that would be a good solution for you.
Have a main report with a parameter. If it wants you to have a data connection, just make a command with a fake select. I.e.: on SQL Server, you could say select 'x' and on Oracle you can say select 'x' from dual .
The parameter determines which sub report/stored proc is called.
Create two detail sections on the main report and drop a sub report in each.
Conditionally suppress the sections based on parameter selection:
for the one for Admitting, if {?parameter} = 'Attending' then yes else no
for the one for Attending, if {?parameter} = 'Admitting' then yes else no
Have a main report with a parameter. If it wants you to have a data connection, just make a command with a fake select. I.e.: on SQL Server, you could say select 'x' and on Oracle you can say select 'x' from dual .
The parameter determines which sub report/stored proc is called.
Create two detail sections on the main report and drop a sub report in each.
Conditionally suppress the sections based on parameter selection:
for the one for Admitting, if {?parameter} = 'Attending' then yes else no
for the one for Attending, if {?parameter} = 'Admitting' then yes else no
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me try to do the sub reports and see what I come up with.
I liked your stored procedure architecture and would definitely be my choice. Sub-reports are always a last resort for me.
ASKER
So I gave up on trying to do the sub reports, the format is not coming out correctly, and my page footer is only showing up on the last page.
So Now I am back to the stored procedure
So I tried putting the paremeters after the stored procedure
ALTER PROCEDURE [Customer].[bhsf_rpt_RunPh
@PatRel varchar(20),
@ProvCode VARCHAR(10),
@StartDate Date,
@EndDate Date
as
Begin
SET NOCOUNT ON;
If @PatRel = 'Admitting '
Execute Customer.bhsf_rpt_Admittin
Else Execute Customer.bhsf_rpt_Attendin
End
It is saying that I have too many arguements specified
So Now I am back to the stored procedure
So I tried putting the paremeters after the stored procedure
ALTER PROCEDURE [Customer].[bhsf_rpt_RunPh
@PatRel varchar(20),
@ProvCode VARCHAR(10),
@StartDate Date,
@EndDate Date
as
Begin
SET NOCOUNT ON;
If @PatRel = 'Admitting '
Execute Customer.bhsf_rpt_Admittin
Else Execute Customer.bhsf_rpt_Attendin
End
It is saying that I have too many arguements specified
I'd assume @PatRel is not a a parameter in your sub procs since that value is used to determine which sub proc to call.
Only pass the parameters that the sub proc requires in your execute statement, maybe like this:
Execute Customer.bhsf_rpt_Admittin
bhsf_rpt_AdmittingOnly expects 3 paramaters (a code and 2 dates)?
Only pass the parameters that the sub proc requires in your execute statement, maybe like this:
Execute Customer.bhsf_rpt_Admittin
bhsf_rpt_AdmittingOnly expects 3 paramaters (a code and 2 dates)?
For sub reports to display page footers correctly, you can probably just put the page footers on the main report instead of trying to put them inside the sub reports.
For your other attempt, do you have the report pointing only to bhsf_rpt_RunPhyRelSP_FO? If so, it should prompt you for all 4 parameters automatically.
Do your other two stored procs really have all 4 variables (@PatRel,@ProvCode , @StartDate, @EndDate) or are there only 3 (maybe not having @PatRel)?
For your other attempt, do you have the report pointing only to bhsf_rpt_RunPhyRelSP_FO? If so, it should prompt you for all 4 parameters automatically.
Do your other two stored procs really have all 4 variables (@PatRel,@ProvCode , @StartDate, @EndDate) or are there only 3 (maybe not having @PatRel)?
ASKER
UnifiedIS, I cought that after I posted it, once I removed that parameter it worked fine. I was able to use it in my crystal report, which is working fine.
LinInDenver,
I really appreciate your input and assistance but I just couldn't get the sub report to work they way I wanted it.
LinInDenver,
I really appreciate your input and assistance but I just couldn't get the sub report to work they way I wanted it.
Subreports by definition do NOT have page footers. There are tricks you can use to simulate them. If an existing report is used for the subreport, the page header/footer becomes the subreport report header/footer.
mlmcc
mlmcc
Do both SPs use the same parameters?
I believe when you call another SP you have to pass the parameters.
mlmcc