Parameter Formula 1

[O2BNSV]

Using v2008, I would like to create a cascading parameter. The first tier is {address.state}. I need the second to be {address.zipcode}. The problem is that in the database from which I am reporting, we have addresses that have a standard 5 character zip and there are those that have the 5 + 4 zip code configuration. I need to eliminate the need to add potentially hundreds of zip codes when the root is the same just because the zip code field has the +4 on the end. In the parameter, is it possible to use a formula
left({address.zipcode}) as the dynamic field in the cascading parameter so that when the report is run, when the state is selected, the user will only see the base values for zip codes within that state? Please advise.

 

[beltmanjr]

If this were not possible, create a view doing this for you and base your parameter on that.

 

[O2BNSV]

Good idea with the view. Unfortunately we don't have a version of SQL that would allow me to do this. Any other thoughts?

 

[beltmanjr]

You could use the 'Add command' option in the database expert to create that view.

How weird that you dont have a version of sql to be able to do this.. Any version of sql should be able to as long as you got the rights

 

[O2BNSV]

We have an msde version that came with the software we use. I forgot that it wouldn't matter because how our software integrates with Crystal is that data is exported into an access database, from which reports are generated. Anyway, I'm not good with SQL commands. Would it be something like:

select
left(zipcode,5)

from
{address}

I hate to do this piecemeal like this, but this is a cool feature I've never heard of. Please help!

 

[beltmanjr]

that is almost correct.

SELECT DISTINCT LEFT(zipcode, 5)
FROM tablename

Will get you the left 5 digits from the zipcode. I'm not aware of how the zipcodes work where you're from....

I guess that crystal will want you to make a link between the command view and the other tables. And perhaps you are better off using a more extensive view to make sure you wont get any weird results.

SELECT *, LEFT(zipcode, 5) as fiveDigitZip
FROM tablename

And use this instead of the table where the information is coming from. be warned that I have been told that commands linked to tables in crystal will slow down the performance of your report.

I'm off... Good luck

 

[O2BNSV]

it worked...when I run the report directly from crystal. However, I'm limited by our database software. When I try to run it out of our software, it won't run it because the view table, while generated from data in the export, is not present in the export. Anyway, thanks for the tip. That's cool stuff.

 

[beltmanjr]

Ok, I dont entirely get the environment you're working on.
I kind of bolted it down to you having an access database (who cares how it is populated) and crystal is taking its reporting data from that access db?

Please enlighten me on the missing link?

 

[O2BNSV]

It works like this: I create a data export in our software. It exports into an access database (which it overwrites evertime it exports). I create the report based on this export. Then, so that all database users can run these reports, I create a "custom report" within the software that does nothing more than pair the export with the report. Hitting refresh runs the export and report simultaneously. This allows our users to run custom reports without actually needing a copy of crystal installed on their machines. I hate reporting this way but it makes more sense for all users then me running every report every time someone needs something. Unfortunately, I don't think there is a way around this. Thanks again for making me aware of the Add Command feature. That will come in handy!

 

[beltmanjr]

So, you are using Crystal which should then allow you to use the Command option. Or am I missing something?

If this doesnt work you could consider having the view build in a second access datasource so it isnot deleted when exporting the data. Or simply get someone to implement it into your main db.