Unique_ID : Passing a HUGE list of parameter values

[MJRBIM]

BOE-XI (R2)
CR-XI (R2)
MS-SQL 2000 database
Java SDK
____________________

Working with an old database that doesn't have a single Unique_ID for users.

REGION STORE USER
=======================================
EAST 001 SMITHJ
EAST 025 SMITHJ
WEST 001 SMITHJ
=======================================

In the example above, the three "SMITHJ" records are different people, in different stores or regions.

There isn't a single Unique_ID in the database, so we were considering creating a new table/trigger to give us a real PRIMARY KEY on the USER table above.

UNIQUE_ID
====================
EAST_001_SMITHJ
EAST_025_SMITHJ
WEST_001_SMITHJ
====================

We would then link that UNIQUE_ID to the rest of the tables in the report based on the three parts that make it up. That would allow us to use the UNIQUE_ID value as a parameter within the reports - and ensure that someone looking for a report on "SMITHJ" only got the "SMITHJ" in the store or region they are responsible for when the parameter-values are passed from the Java SDK application.

This seems to work OK from a logical POV, but some of the senior managers want to be able to run the reports for ALL the employees that are below them.

This is usually under 1,000 Unique_ID parameter values, but in a worst-case it can be over 11,000.

The parameter appears to die if we try to pass it around 6,000 Unique_ID parameter values - it is fine below that number.

Anyone solved an issue like this before and have any smart ideas...?

If you have a better idea for handling the Unique-User problem that would be welcome also.

Thanks in advance!

 

[cjlarue]

why not add a supervisorID field to your table and then use that param to pull all the users under thart supervisor. One param rather than 11K.

 

[MJRBIM]

That's not the way it works within the ORG.

More than one Senior Manager may have access to the same users, and the relationships are not always REGIONAL.

 

[lbass]

I think cjlarue made a good point though. You should be able to segment this more, by supervisor, department, or something. No manager is going to want to scroll through ten thousand IDs. Maybe you could implement a cascading parameter that would better limit results.

-LB

 

[MJRBIM]

I will investigate futher with the ORG's "Business Users" to see if they truly need this functionality for that large a set of parameters.

The end-users wouldn't be scrolling through the LIST - it would be passed from the Java SDK application.

Thanks for the input.

 

[chelseatech]

If you want an ALL option, why nnot encode that as a unique value for the parameter....
{?parameter} = "ALL" or
{Field} = {?Parameter}

A lot easier to code the report that way.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[MJRBIM]

chelseatech -

Unfortuantely, it's not an "ALL" UserAccounts the the database - it's an ALL UserAccounts the application user has "rights" to see.

These RIGHTS are calculated "on the fly" by the Java application.

We are trying to use the Huge Array of Parameter values as kind of a "poor man's" row-level DB security.

It's not pretty, but it's the best we can do for now...

 

[chelseatech]

What about getting your application to build a temporary table with the values you want. Then inside your report, link to that table to only get the values you want.

If you need multiple user access, then a working table, with the Unique id's and the current User ID (or equivalent) to restrict the records you want.

Look at your problem for an innovative solution. Brute force is going to hit too many large obstacles.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm