create vbscript query

[vilaine]

Ok, from what I understand I think should be able to use vbscript to create a SQL query to get my information from my db. But I can't find anything that tells me how or where to write this code. It would start out with the user selecting the particular parameter values they want to search on, state, zip code, or area code and optionally restrict the sales amount. My query needs to have conditional code (if..then statements) determining which selection the user picked and then create the SQL query based on those parameters. This is how I created the asp version, but now the client wants Crystal Reports. Is there a better way to do this for CR?

Thanks,
Jessica Hill

 

[balves]

I'm not sure what you're really trying to ask but...

You can create a Crystal Report and just before you run the report you cando any of the following:

- pass parameter values
- replace the entire WHERE clause
- replace the entire SQL query string

So if you build an ASP page and prompt the user for inputs for whatever (parameters, WHERE clause), you can make changes to the Crystal Report based upon what you asked the user to provide. Then execute the report and have it display in the Active-X report viewer. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[vilaine]

Hmmm...sure! Sounds like a decent way to accomplish it. I've already created the page in ASP but it times out (2.4 million records) and CR seems to be able to handle all the records and we can export the data to ACT!. The only way I've done CR stuff is to create the report and then do the 'get data' when it asks you to enter the parameters. How do I do what you're describing where it interacts with an ASP page? I need exact procedures because I've only worked with CR & ASP for a month.

Here's the basis of my page and you can tell me if this can be done. The search page has a state dropdown, zip code box, area code box, and a sales amount dropdown. The user can select one of the first three location parameters and optionally select to narrow it down by sales amount. The second page needs to determine which of the location parameters they picked so the SQL query can be written accordingly.
So it essentially says,

SELECT ID, CompanyName, Phone
If the state is selected, write "WHERE State=Request.item("state&quot
otherwise, if the zipcode is selected, write "WHERE Zip=Request.item("zip%&quot
otherwise, if the areacode is selected, write "WHERE Area=Request.item("area&quot
End If
If sales is selected, write "AND Sales=Request.item("sales&quot
End If
ORDER BY CompanyName


Thanks,
Jessica Hill

 

[balves]

You should probably make your Crystal report work first, with 4 parameters: state, zip, area code and sales. After you create the 4 parameters, then you build the record selection formula in Crystal and test the report.

Once the report works with the 4 parameters, then you pass those parameters from the ASP page to the Crystal report. I don't see any need to replace the whole SQL or even the WHERE clause from ASP. Just pass the parameter values. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[vilaine]

Ok, but how do I get my "If...Then" statements in there? I know the query won't run if I don't have them in there, it has to be structured like that. We've tried everything and it really won't work written any other way. Not unless you can have CR recognize what items weren't filled/selected on the ASP page and won't use them in the query.

And how do you pass the parameters from ASP to CR and then make CR recognize them? As you can see, I know nothing about CR.

Thanks for all your help so far, I really haven't found anything else that explains Crystal Reports.
Jessica Hill

 

[balves]

There are no IF...THEN statements. It's one query.

Make sure one thing works before moving on.

1. Create a parameter in the report called p_State (I always name the parameters p_xxx). Edit the record selection for the report (via the Report Selection dialog box) and create the WHERE clause State = {?p_State}. Run the report, enter the state and make sure it works.

2. Add the other 3 parameters to the report.

3. Then edit the record selection manually and make each clause in the WHERE into (e.g., for State):

WHERE (State = {?p_State} OR isnull({p_State}))
...

4. When all of that works, then pass the parameters from an ASP page. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

Hi there.

I've done the first step and part of the second step. Whenever I add a second parameter(area or zip code) it doesn't use that for the query. Just no results even though I'm searching for something that I'm positive is in the db. I wrote this in the Record Selection Formula Editor window:
({Dirusa2.Col004}={?State} OR isnull({?State}))
OR
({Dirusa2.Col004}={?area} OR isnull({?area}))

In step 3, you say to edit the record selection manually, where might this be at? The Edit Selection Formula window or the Show SQL query window? There's a big difference because in the Edit Selection Formula window you can't add the WHERE statement that you indicated.

Jessica Hill

 

[balves]

It should be AND between the WHERE clauses:

({Dirusa2.Col004}={?State} OR isnull({?State}))
AND
({Dirusa2.Col004}={?area} OR isnull({?area}))
AND...

I use the Edit Selection Formula window. I try not to edit the query with Show SQL Query. With the Edit Selection Formula, you're typing/editing the WHERE clause, but the WHERE is implied...you don't put it in there.
Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

Thank you Brian, I really appreciate the help you're giving me, plus the quick responses.

Unfortunately I don't want a compounding query. The intention is that the user is going to pick only 1 of the location parameters (state, area code, zip code) so some sort of OR function is what I need. It's not helpful for them to have to pick more than one parameter, they want to just search in a particular area. Plus they're searching in several million records and anything to limit the number or records returned is good.

I've even entered % as a state value in hopes that if you select it and an area code, it'll request all states, and then narrow it down with the area code. Nope, no results there either. Just selecting a state works, but whenever I choose a second parameter, it gives me no results.

Can I have futher clarification on the isnull() function? How does the page really process it? Does it essentially say "if it's null, don't do anything"? Is that the same thing as saying that the variable has no value assigned to it?

Jessica Hill

 

[balves]

isnull() returns TRUE or FALSE

You must have AND between the clauses. The isnull() takes care of the parameters that you don't pass a value. If you put OR, you'll get far too many records.

Or, you can go another way. Put absolutely no WHERE clause in the Crystal Report. Thus, you get ALL records fom the query if you run it in the Designer. Then, from the ASP page, add the WHERE clause to the report based upon what the user enters on the page.

How you add the WHERE clause in ASP depends upon whether you are using the RDC technology or the old .rpt/OCX technology. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[balves]

Okay, there's a difference between passing NULL from ASP or VB and testing inside of Crystal Reports Designer. Inside of the Crystal Report designer, if you define a parameter, run the report, and just click OK (don't fill the parameter), you get the empty string (''), not NULL. You can pass NULL from ASP or VB, but testing inside of the Designer, you get the empty string. Thus, the Record Selection would be slightly different (assuming Member.State is your field and p_State is your report parameter, etc.):

({Member.State} = {?p_State} or {?p_State} = '')
AND
({Member.AreaCode} = {?p_AreaCode} or {?p_AreaCode} = '')
AND...

From ASP or VB, you could pass '' for the parameter instead of NULL so that you could test in the Designer and from ASP and get the same results. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

Thank you Brian. So far I've got it working in CR, getting it passed from the ASP page is a bit trickier. We've got the actual details worked out, but it't not always running the report, which is something on our end, some sort of server problem or something.

Our tiny little bug is that I can't figure out how to incorporate a wildcard. We've got the zip+4 and want to be able to enter just the first five digits and do a partial match for everything within that area code. So a search for 71601 would give results for 71601 and 71601-5644, etc.
({Dirusa2.Col005} = {?zip_s} or {?zip_s}='')

Do we put a LIKE statement in instead of the equals? I've tried that and to put a wildcard %/* and neither works. If it becomes too much of a problem to deal with, we'll just trim the zip codes to 5 characters, but it would be nice to figure it out.

Jessica Hill

 

[vilaine]

Oh wait, I found it! Hopefully this is decent performance-wise, but I'm using "startswith". If it's not so good to use with about 2 million records, the please tell me what else to do.

Rock on. I might actually finish this project, that once seemed overwhelming.

Jessica Hill

 

[balves]

Well, not to go down a totally different path, but...

You can point a Crystal Report at a stored procedure as the data source.

Assuming that you would only pass ONE of the 4 parameters (and '' otherwise), you could write the stored procedure to take 4 parameters and do the following:

CREATE PROC sp_GetDataForReport(strState char(2), strZip varchar(10), strAreaCode char(3), intSales int)
AS

IF strState <> ''
SELECT .... FROM ...
ELSE
IF strZip <> ''
SELECT .... FROM ...
ELSE
IF strAreaCode <> ''
SELECT .... FROM ....
ELSE
IF intSales <> 0
SELECT ..... FROM

RETURN
END

When you have the stored procedure written and compiled, you can run it against the query analyzer to make sure that you're hitting the keys in the database and not doing 1 or more table scans on large tables. If you have millions of records, you want to make sure you don't do a table scan on any query.

Then you can make a stored procedure the data source for the Crystal Report. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

Oh my gosh, don't confuse me!

Your example is how my original all-ASP pages were working, with If..Then statements.
We're working with only one table and it's suppose to be properly indexed, aside from the current space issues. So in theory, when we're done, it'll be done properly.

Now my big problem is that we were doing all of this because we want to export individual company records to ACT!. Unfortunately we find out now that subreports can't be exported!!! AHHH! We've got a Crystal knowledge base article telling a solution for HTML linking the subreport to it's actual file, resulting in loading it as its own main report, which you can export. Not exactly the most elegant solution, are there any other options?

Jessica Hill

 

[balves]

Isn't the issue that you have to go to each sub-report and export it separately? That just exporting from the main report only gets you the main report?

Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

Ok, now you've got me questioning our method. Does the following description make sense?

The main page returns the full search results, which is many various sales leads. If they contact a lead and decide that they want to be a client, then they want to put this contact into ACT. We didn't want to export the whole report because it would include a lot of unwanted records. The only way my partner knew (I know nothing about CR) to get just a single record that's always uniform for exporting was to put it in a subreport. The whole report goes like this:

Company name (Grouped)
company details, address, phone, contact, type of business (this is the subeport)

When you double-click on the subreport, it takes you to a window that has just the single company details, which is always the same to export. Then we can create a .map file that tells ACT what information goes where. Of course when we were testing this, it was always within CR, not on the web so we didn't find out until late yesterday that we couldn't export the subreport information from the web.

I attempted the knowledge base article solution (detailed in previous post) but it says to check out the document on url commands to pass the parameters to the subreport, which will now be its own main report. Unfortunately, the document is not helpful at all. I need it to pass the ID and then figure out how to make the report request the ID instead of the previous parameters (zip code, area code, sales) from the main report since that's what it was linked to. It seems like it would be easier to make a whole new report that just looks for ID, yes?

Jessica Hill

 

[balves]

Yikes, I didn't know that it couldn't export the sub-report at all...I thought the export just ended up in a separate file.

The document that they reference is pretty undecipherable, at least to me.

It appears that they want you to create a new main .rpt file (to replace the subreport). That report would take 1 parameter, the id, and show whatever data you want.

In your original report, it looks like you delete the sub-report entirely and replace it with a text label (on the detail line). You make the text label say something like &quot;Export This Record&quot; and format it as a hyperlink, giving the full web url of the new .rpt file. At that point, you somehow have to pass the company id value of the detail line to the url of the hyperlink. There must be a way to do this in the main report...in the formatting of the text label that's a hyperlink. Unfortunately, I don't have a current version of Crystal at my current customer. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[vilaine]

ok, I'm slightly cheered by the fact that you, a very advanced user, also don't understand their description for hyperlinking to a new report. I guess I'll keep searching the Internet for an answer and tell you if I find something.

Thanks for all your help,
Jessica Hill

 

[vilaine]

YES! *does the joyous arm motion*

We figured it out! Here's our solution.
Moved the subreport information to the main details section (since we no longer needed it to be a subreport). Right click on the appropriate field (mine is company name) and choose Format Field option. Select the Hyperlink tab. Pick the &quot;A website on the Internet&quot; type and don't put anything in the website address box. Click the formula button (x+2 button) and write the code for the place you want it to go.

&quot;

http://slink.milease.com/nationalsales/export.rpt?prompt0=&quot;

& {Dirusa2.id}

Now we're essentially linking to another main report instead of a subreport and passing the ID field along as the parameter. My export.rpt page is a copy of what was on my subreport (a single company record), but now I can export the information. I also made the company name blue and underlined to indicate a link. For some reason the underline doesn't show up on the web, but it's not a big deal. You can also use this method if you wanted to link out to another file, say a PDF. Just be sure to use the correct formatting for putting the string together.

rock on!
Jessica Hill