NEED HELP WITH SP PARAMETERS

[4programming]

Using crystal reports XI with oracle 9.2 stored procedure. I have one parameter in the stored procedure called status, that is an IN parameter. Now when I run a crystal report with that stored procedure I get prompted for that parameter. In my stored procedure I also have function that will allow the user to enter multiple values separated by a comma. So for this situation when a user runs this report he gets a prompt to enter status and the user can enter just 1 value or multiple values separated by a comma. So for this situation let’s say he/she entered 1,2,4. Here is my issue



1) How do I get those integer values to show description equivalents? What I mean is 1 should be defined as “New”, 2 should be defined as “Open”, and 4 should be defined as “Closed”. The value for status is stored as an integer in the database so this is why I have to translate it. How would I do this?



2) I need to place this parameter in the Page Footer of the report. So if I place this parameter right now I get 1 value which in this case is number 1. Without answering question 1, I want the page footer to not only show all the values (1,2,4) but there descriptor equivalent. So it should show, New, Open and Closed if the user selects 1,2,4. Any ideas?



Thanks very much.

 

[lauriesamh]

Not sure if this is going to help but regarding #2. I have a report where the user can enter multiple codes in a parameter. I have it display in the report via 2 formulas. Hope it helps. First one:

@Studycodes
local numbervar i;
local stringvar s;
for i:=1 to ubound({?Study Codes}) do
if (minimum({?Study Codes})<>maximum({?Study Codes})) then
s:=s & minimum({?Study Codes}) & " to " & maximum({?Study Codes}) &", "
else
s:=s & minimum({?Study Codes}) ;
s;

Second one displays the string of codes
@Studycodelabel
" Codes to review"&"- "&{@Study codes}

 

[4programming]

Lauriesamh,



Thanks for the response. I don’t think your code will help me but I appreciate the effort. Just looking at the code, it looks like it will only display the values that I enter in the parameter and not the actual description. We have to remember that my parameters are bare and all that I have done is used the parameter in the procedure. I have not modified the parameter properties (i.e. allowing multiple values etc). I have just tapped my stored procedure to the crystal report. Just out of curiosity though I have tried your code and did get an “array is required message”. I am thinking because I didn’t specify the parameter properties as I am not sure how to with Stored procedures. Thanks again



The answer to these questions will be the basis of all my reports. If I can’t get 2 correct answers then I will have to think of a different framework. Can you think of any other ideas??

 

[lbass]

1- Assuming your stored procedure is returning an integer field {table.status}, you can just write a formula in CR:

select {table.status}
case 1 : "New"
case 2 : "Open"
case 4 : "Closed"

...if you want to display the status description in the detail section. For the parameter description, try this:

stringvar array status := split({?Parm},",");
numbervar i;
numbervar j:= ubound(status);
stringvar display;

for i := 1 to j do(
display := display + (
select val(status)
case 1 : "New"
case 2 : "Open"
case 4 : "Closed"
) + ", "
);
left(display, len(display)-2)

-LB

 

[4programming]

LBass,

Thanks for the help. Sorry I may have miscommunicated. My parameter is not an Integer but a VARCHAR(2). The reason is because I have a function in the database that allows the user to enter multiple values separated by a comma. So for this situation I would enter 1,2,4. This list can change but I wanted the stored procedure to be easy to work with if I had to run multiple valued data. The function will parse each comma. So it will say to the report give me all values where status = 1 and then where 2 and then where 4. Now also in my stored procedure I have a case statement that translates the 1,2,4 to text like the case statement you said. I want to display the status description in the page header and at the same time give the user the ability to choose the status description or multiple values on the report. So basically how could I do this with a stringed comma separated parameter. Let me know if that is clear. Thanks again.

 

[lbass]

Not quite following you. My #1 above was just to respond to your #1. I'm unclear what you are looking for, if my response was inappropriate.

I forgot to number part 2 above, which starts with "For the parameter description..." If you placed the parameter directly into the report header, what would we see? I'm assuming it would be: 1,2,4 (string). The second formula would then break out and convert that string into integers that are then translated into the text descriptions. Did you try this?

-LB

 

[4programming]

LBass,



I will first tell you what happened when I put your formula’s in my report and then I will explain more of what I want to clear up any confusion.



So if I add a formula and call it @test and add this as you asked

stringvar array status := split({?STATUSSELECT},",");
numbervar i;
numbervar j:= ubound(status);
stringvar display;

 

for i := 1 to j do(

display := display + (

select val(status[i])

case 1 : "New"

case 2 : "Open"

case 4 : "Closed"

) + ", "

);

left(display, len(display)-2)

I get the error “ARRAY MUST BE SUSCRIPTED. FOR EXAMPLE ARRAY[1].”



Not sure what is happening here.





Now for the confusion you may have.



Let’s take a step back ….when I finish creating and oracle package/Stored procedure and package this is what I do in crystal reports XI. I go to file new/standard report/then I choose the native oracle server(or log on)/look for my schema/then under qualifiers I find the pack package/stored procedure. So I then click on this. Crystal then prompts you for the parameters in the package/stored procedure. The parameter in question is status as you know. I enter 1,2,4 (a string) to this VARCHAR(2) Parameter the report generates. Now I know this is pulling the right information because if I go to Database Show SQL, for the status parameter, it show’s properly 1,2,4. Thus, because I have a function on my oracle side that allows the user to enter multiple values, (the 1,2,4) instead of just single, it is able to look at these values individually and add them to the search of the report.



Now what I think I need to do to get this to work is firstly break up the 1,2,4 into individual values and associate these values to the there associated words i.e. New, Open Closed and have this displayed on the page footer. Thus, what you were trying to do in the formula above, but remember my parameter variable is a varchar(2) not an integer like you assumed.



Secondly, this part could come first or second, since I haven’t touched the parameter properties at this point, I need somehow to have the value associated with the description and allow for the user to select multiple description values. I.E only show the user New, Closed for there selection. I don’t want them to have to enter 1,2



Below is what I should do in a perfect world to get this parameter working in crystal but if I run my Database show SQL I get NULL for the status parameter because I think it doesn’t know how to parse the values on the crystal side.



This is what I have tried and it does allow the user to enter multiple descriptions but if I go to Database Show SQL I get NULL meaning crystal doesn’t know what to do with this parameter.

1. In the field explorer right click on your parameter and choose 
edit. 
2. next to List of values, make sure the button selected is 
"Static" 
3. Click on the listbox, under the column VALUE enter the first choice 
you want to be displayed. Lets say 1. Then Click on the column 
Description and enter your desired description you want to show 
example "Open". 
4. Repeat step 3 for as many values as you want to display in the 
list. 
5. Make sure that "Prompt with description only" is set to TRUE if you want users to see the descriptions of the values.

6. Set Allow MULTIPLE values to TRUE if you want the users to 
choose more then 1 value.

Sorry for the long email but I don’t want any confusion. Let me know if this is better LBass as maybe I need to do something on the oracle side that I might not be aware of. Thanks.

 

[lbass]

The problem is that I don't know how parameters in stored procedures work, which was why I asked what would display if you selected multiple values (1,2,4) and then placed the parameter itself on the report (which you didn't answer).

Please note that I understood it was a string, and my formula translates it from a string to an integer and then connects it with a description.

But now it sounds like you want to change the way the parameter selection is done by adding a description field. If the parameter is set up in the SP, I don't think there's a way to change how it is set up and selected in CR--although I might be wrong about that. Maybe someone else will jump in. Again it would help if you would show how the parm displays when placed in the report header and if you explained how you currently enter the selections in CR--do you add one value at a time, or are you adding one string, as in (1,2,4).

-LB

 

[4programming]

You may have missed the answers in the original message, but I understand as it is a long thread In my original Question I said

"So if I place this parameter right now I get 1 value which in this case is number 1."

So if I enter 1,2,4 I get 1 returned on the report. It cuts off 2,4.

How I currently enter selections in CR was also missed but here we go. I choose the whole string 1,2,4 and not one value at a time. The whole string signifies multiple values. Hope this helps.

 

[lbass]

The problem is that if you entered a string '(1,2,4)' in response to a parameter prompt within CR, then if you placed that parameter directly on the report, you would see exactly that: (1,2,4). If you entered the numbers individually: 1, then 2, then 4, you would see only "1" if you placed the parameter on the report. What I'm missing is how that string is getting translated into separate values (the function you mentioned?). Anyway, if we back into this, then you should be able to use the following to attach descriptions for purposes of displaying the descriptions related to the selected values:

stringvar array status := {?Parm};
numbervar i;
numbervar j:= ubound(status);
stringvar display;

for i := 1 to j do(
display := display + (
select status
case "1" : "New"
case "2" : "Open"
case "4" : "Closed"
) + ", "
);
left(display, len(display)-2)

-LB

 

[4programming]

Thanks Lbass,



My apologies as you were correct on the what I should see if I entered the parameter directly on the report. I do see what you said but because I have been playing around with different variations on my own I gave you incorrect informationJ Anyways, I do see as below.



“would see exactly that: (1,2,4)”



Now if I put your formula in the report I get blank. Ay ideas? Thanks again.

 

[lbass]

Please copy the formula and paste it into the thread. Also, please test it by adding the following to the end (one at a time) and then report back with the values you get:

;j

;display

-LB