Passing multiple value parameters through ASP

[simonthompson]

I'm trying to call a report from an ASP page that is filtered based on a set of checkboxes. The call to the report goes fine, but Crystal (ver 8.0) only picks up on the first value in each of the prompts:
i.e.

<A HREF=&quot;//localhost/ProductReport.rpt?init=actx&prompt0=1+2+3&prompt1=1+2&prompt2=1+2+3+4+5&quot;>[code]
results in report only finding prompt0=1, prompt1=1, and prompt2=1.
I've tried many different separators (instead of the +),but none of them seem to work. The report is set up so that it is expecting multiple values for each parameter, and if I call the report with no prompts, it displays the dialogue asking for them, and then shows what it's supposed to... I really need it to be in the URL though.

 

[GolfBoy]

We have a ASP page that has check boxes for which days of the week you wish to see in your report (multi parameters)
The way we filter them out is a follows.

In Crystal Report we have the following :
1. Parameter Field called Days as Sting
2. Formula Field Call DayCriteria with the formula :
IF Mid ({?Days},DayOfWeek ({ReportDate}),1) = "1" THEN
TRUE
ELSE
FALSE
3. In our Select Expert we set @DayCriteria = True.
Then from our URL we pass the prompt as Prompt0=0101010, which will show days Monday,Wednesday & Friday only.

So your formula would be something like:
IF InString({?firstprompt},{YourReportField}) > 0 THEN
TRUE
ELSE
FALSE

Hope this helps.

 

[simonthompson]

Yeah, that's the way I've had to do it. I couldn't find any way of making Crystal accept multi-value Number parameters, so I had to parse a string using formulas, much the same as you've done here.

Thanks anyway.

 

[amutinu]

Hi,
thanks for your suggestions, I have a similar approach i am trying for my reports. I have checkboxes on which fields to sort by. I will use the above mentioned method but first i am having problems loading the crystal reports from ASP. Can you please show me some code that will get me started here? I am trying to run the report from ASP. I am new to crystal reports and ASP so your help would be greatly appreciated

thanks,

 

[simonthompson]

To run the report, all you do is pass in the name of the report as a URL. At the end of the report name, put in the way you want it initialised (activex, html, html frames etc), and then for each of the parameters you have defined in your report, put in a prompt. They start at prompt0, and go as high as you like. Make sure that the prompts are put in in the same order as the parameters are defined in your report. e.g.:

<a href=&quot;[URL unfurl="true"]http://yoursitehere.com/reportname.rpt?init=actx&prompt0=1,2,3&prompt1=1,2,4&prompt2=5,4,7&quot;>[/URL]

(you can alternatively use a relative url).
The init=actx means use ActiveX as the viewer. There are other viewers you can use (actx is an IE thing only I think) such as HTML, HTML w/ frames etc. I'm not sure what the codes are though. Check out:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr8_urlcmds.pdf

for a full list.

I just built the URL bit by bit with JavaScript, using

Response.Write

statements
Each of the 1,2,3's etc corresponds to a check box in my groups, and then in the report, I have the parameters set up as strings. There are then formulas which pull out the numbers and put them into arrays which are then used for SQL 'in' statements. I can give you an example of the formulas too if you want.

 

[amutinu]

hi Simonthompson
thanks for your feedback. i used the href to view the report but then how do you actually setup the sort parameters in crystal. Can you please describe the steps involved in doing this.

Also if you can show me some sample code in building your querystring from checkboxes, i would really appreciate it.

thanks,

 

[simonthompson]

To get the parameters into your report, you have to first build an array of which checkboxes have been ticked. The checkboxes were build with the normal input tag:

<INPUT id=theFilter name=theFilter type=checkbox value=&quot; + Filter.getValue() + selected + &quot;>

where
-

theFilter

is the name of all the checkboxes,
-

Filter.getValue

is the number of the checkbox, and
-

selected

is whether it should start off selected or not.

In my code, this is controlled by a dll, but if you just want to start with them all selected, you'd put in something like:

for ( int i=0; i<numYouWant; i++ )
    Response.Write(&quot;<INPUT id=theFilter name=theFilter type=checkbox value= &quot; + i + &quot; selected>&quot;);

Then, when it came to reading them, you would put in:

var filterArray;
for (i=1; i<=Request.Form( &quot;theFilter&quot; ).count; i++)
    filterArray[i] = Request.Form( &quot;theFilter&quot;)(i);

Request.Form( &quot;theFilter&quot; )

is an array which asp builds of the checkboxes that are named theFilter (just my name, you can call it what you want) that are checked. So, if you had 5 checkboxes called theFilter, and numbered 0-4, then your filterArray would come out as [0, 1, 4], if they only checked the first, second, and fifth boxes.

The only problem with doing it this way, is that before building your filterArray, you must refresh the page so the asp can pick up the changes. In my case that's fine, because the user hits a button which updates all the data on the screen, and at the same time, builds the URL to run the report. If you wanted to run the report without updating though, you'd have to put all this into a button or something.

To build the string, I have the following:

Response.Write<A target=new HREF=\&quot;DetailReport.rpt?init=actx&quot;);
// this bit loops for the number of prompts I have
var hasBeen = false; 
Response.Write(&quot;&prompt0=&quot;);
for ( int i=0; i<filterArray.length; i++ )
{
   if (hasBeen == true)    // need to know if we want a comma
        Response.Write(&quot;,&quot;);
   Response.Write(filterArray[i]);  // Put in the values
   hasBeen = true;
}

// when it's  all done, close the tag
Response.Write(&quot;\&quot;>View Detail Report</A>&quot;);

This produces a URL like so:

/DetailReport.rpt?init=actx&prompt0=0&prompt1=1,2,3&prompt2=1,2&prompt3=1,2,3,4

Now, to get these values out in the report, you have to use fomulas. I have 4 parameters defined in my report, all strings, which correspond to the prompts in the URL. The formulas are then of the form:

Global NumberVar Array statArray;
Local StringVar Array statStrArray;

statStrArray := Split({?status}, ",");

Local numbervar arrayCount := Count(statStrArray);

redim statArray [arrayCount];

Local numbervar nCount;

for nCount:=1 to arrayCount step 1 do
(
    statArray[nCount] := tonumber(statStrArray[nCount]);
);

This declares my status Array globally, meaning that the selection formula can access it.

?status

is the parameter, which will contain "1,2,3" when it comes in (or 1,3 or something similar, depending on the boxes that were checked). I then split that out to a string array, removing the commas, and step through that, converting each one to a number and storing it in my statArray.
Then, in the selection formula, I can have:

Global NumberVar Array statArray; // declare it again so I have access to it
{STATUS_TABLE.Value} in statArray // the sql part

I also have parameters and formulas set up for the other prompts, which are all included with AND statements in the selection formula.

Hope this has been of some assisstance