calling SP without prompt from CR9 - How please ?

[nice4]

Is there a way that I can call Oracle SP (insert/update/return cursor) with input parameter(s) from CR9 but WITHOUT having CR9 to prompt for the parameters ?

 

[fredp1]

If you have a sp with input parameters, you gota put them in.

The way to default the paramters is to create a subreport and put the sp in the subreport.
Then you can change the link options on the subreport and pass a variable or whatever to the parameters.
Iv'e tried this with 8.5 and SQL server and it works.

Fred

 

fredp1, thanks for your reply. I am having problem of 'LINK' the sub-report parameter to my Oracle SP which expecting two input values. Here is what I have tried.

- When I first create the sub-report, it prompts for SP inputs, which is fine, I just provided as it asked.
- When I select the 'link' tag, a window comes up. In 'Available Fields', I see 2 static variables that I defined under main report for passing values into sub-report, when I choose 1st one, CR9 automatically creates a ?Pm-my_var in drop down 'Subreport parameter field to use:', that seems okay, then to the bottom right, there is another drop down named 'Select data in subreport based on field:', this drop down shown the name of my Oracle SP, as I drop down to what is in there, it shown me a single field underneath my SP, it is a field belongs to my cursor definition BUT it does not seem have anything to do with my SP input parameter at all. Well, I don't see how the parameter in sub-report can automatically be PASSED directly to SP ? I wish you can provide me a little more detail in steps of how can I achieve this from CR9.
Thanks

 

[synapsevampire]

It will prompt for the values, but you can set default values in the SP if nothing is passed.

Oracle SP's are very persnickety, so you may find some complications, and the format is based on the version of Crystal, which you didn't share.

Here's the whitepaper on CR->Oracle SPs:

http://support.businessobjects.com/library/kbase/articles/c2001157.asp

-k

 

[nice4]

I have read the white paper 3 times and that is how I have learned using sub-report but what confused me is the 'LINK', I just can not associate the ?Pm-xxx to the SP's input parameter. I don't see those IN parameters under my SP and why ???

I am using CR9 + W2K + Oracle 8.17 I think. ODBC is from CR and my SP is basically
- accept 2 IN parameter (date), and a IN OUT cursor as per white paper;
- SP will verify the 1st IN date, see if it ever exists, if already existed, then it just select the row based on the IN date and return the result via the cursor.
- If not, Sp will INSERT a new row with IN date as its key, then does some count and update the new inserted row, last step will select the row and return via cursor.

The CURSOR seem works okay because I have call it from CR9 with CR9 prompts for input. What I must do is not to allow user to provide the parameter input, rather I provide them by a function call return value instead.

 

[rhinok]

Ok, so you have a stored procedure with two input parameters and you don't want the users to ever see the parameters or input values? By what logic are you going to default the input paramter values (CurrentDate, for example)? Can't you just apply this logic directly within the stored procedure and do away with the parameters completely?

If you can apply the logic within the report, then you can apply the logic within the procedure. The entire purpose of a parameter is to allow input. If you're going to restrict this ability, then you don't need a parameter.


~Kurt

 

The reason that I setup the IN parameters is because there is a common function which defines all the month-end dates for the whole year and it is used by all related reports. If I rebuild the same logic as what this function does, then we have to maintaining two different logic for the same result, a little drawback.
I agreed that the purpose of a parameter is to allow input - but it should allow directly prompt for and also directly assign without prompt I think.

The original idea suppose to be practical...
- When report is open on a monthly basis, it calls SP to do the insert (one for each month with IN data as its key), then the SP will do the counts for business history requirement, then populates the counts to the newly row;
- At the very last step, the SP will basically pickup all the information from the newly insert row and return to the CR for reporting.

Well, this report runs once a month as assumed, however if more than once is needed, then the SP will also capable of just do the counts and returns the result without any new row inserting.

Question is do I suppose to see two parameters pops up under the SP in drop down window named 'Select data in subreport based on field:' located on right bottom side of the subreport-link window ? BTW, this window seems all talking about the links between the Main-report and the Sub-report and I am not seeing anything related to the SP link.
Can someone whom has done this before share their experience please...Appreciated.

 

[vidru]

Don't use the 'Select data in subreport based on field:'. Instead, click the 'Subreport parameter field to use:' dropdown (left side bottom) to link the field from your main report to the parameter in your SP. That should automatically disable the 'Select data in subreport based on field:' checkbox.

If you don't see your SP parameters there, then make sure the field your linking from the main report is the same datatype as the SP parameter.

-dave

 

[fredp1]

HI Nice4,

Sorry for missing the earlier threads... been off sick.
Vidru is correct. You should see the sp parameters when you untick the 'Select data in subreport based on field:'.
In 8.5 the left hand side drop down box had a bug with realy small scroll down arrows, but in 9 you should see the parameters.

Fred

 

[nice4]

Thanks Fred, I finally able to see the SP's parameters now and link my sub-report's param ?Pm-xxx to the SP's param, and hope the value from the main-report can be directly passed into my SP, and have the sub-report reside on report-footer section (or maybe report header section ? note the main-report has defined the param value). I flash the report, you know the SP reporting NULL value from the input param, CR9 has never complaint about that. This looks like the 'passing' never went through properly.

 

Now I have it works (sometimes). It works if
- refresh sub-report first, so it makes a call to the SP
- refresh main-report. This main-report is the report that I am interesting and can be refreshed with any param values, looks cool.

However, if I open the main-report and just refresh, I never gets what I wanted, meaning the main-report never reflect according to my param. WHY ?

I have tried put the sub-report on different report-sections such as header/footer section, the problem remains.

I assumed by using the sub-report is to work around with the param PROMPT issue and I have no interest what-so-ever with sub-report in my case here.

Please give me the last push here, I know I am close to have my first CR + Oracle SP combo report. Thanks

 

[fredp1]

It all sounds a did wierd.

I'm not experienced with Oracle, but I have read about issues with drivers. I'm not sure whats what, but try changing to ODBC or Native or OLE db etc. and try again. It should be okay.

Also check you Service pack level of CR 9.
Sorry for not been specific.

Maybe you can post specifc versions/drivers for other to help you.

Fred