Parameter / formula

[jaguar00]

Hello. Using CR10 & SQL Server 2000. Users sync data down to their machines based upon their TerritoryID.

I need to write a report that will allow the user to choose a customer (parameter) from the list and view that customers data. Now this should be easy enough to do but the problem occurs because when writing the parameter to choose the customer it returns all customers instead of just those for the users' territory. How do I get the parameter to only show theirs? I have all the tables and fields needed to accomplish this but don't know how to handle this. Surely this can be done.

I've been searching for several days so any help is greatly appreciated.

 

[synapsevampire]

Is the "Users sync data down to their machines based upon their TerritoryID" automatic, or should they also have a parameter for the TerritoryID?

How did you try to use this parameter?

If you add in a TerritoryID parameter, and then in the Report->Record Selection Formula->Record

(
{table.customer} = {?MyCustParm}
)
and
(
{table.Territory} = {?MyTerrParm}
)

If you assume that Crystal will always know what the users territory is, this would be dependent upon whether the database has security set up on the user territory level, and you pass in the user. Most databases do not handle security in this fashion. Lawson with OLE DB is one that does, and there are others.

-k

 

[jaguar00]

The users sync data based upon their territoryID automatically.

I wrote the parameter and clicked Distinct Values/Browse table (tblCustomers)/Browse field (CustomerName). The list that comes up includes all customer names no matter what territory they belong to instead of just the users customer names. Unfortunately when the users sync, they get all customer names instead of just their own.

I used your suggestion above but it didn't work. Is there a way to run either a formula or something behind the scenes to retrieve the territory and customer names and then populate the parameter after that ran? Surely there's a way to do this.

 

[synapsevampire]

Sorry, not within Crystal.

Unfortunately, and to the consternation of most, Crystal has continuously disregarded the requests to make parameters dynamic.

The only way to do so is to write a front end.

Your post doesn't make sense, if the users can only see their data in the database, then how can they see other data in this case? Are you speaking about some specific application that only allows them to see their data? Don't assume that this is built into the database, try checking with your technical resources to learn how this is accomplished, it likely isn't the database.

At any rate, if you followed my suggestion of building both parameters, then they will only see their data returned, however if they choose parametrs that aren't within their scope, and you've limited the data being returned to only a certain territory, they will only see that territory data.

The above solution works except with regard to prepopulating parameters.

Your request asked for limiting the data returned, not the parameters.

The above works, except with regards to parameters.

And just in case you're thinking ahead, you can't use hierarchial parameters either <another reeeeeal nuisance>.

-k

 

[jaguar00]

Voila! There is a way around this!!!

I grouped on the customer name first and designated a new page after in the report expert it worked. By doing this, the report viewer will use the data tree drill-down function to display each customer on their own page. I just had to make sure that the Toggle Data Tree button was displayed on their report viewer. This way my users can print the whole report or just a page or two for a specific customer.