Avatar of james_imagemation
 asked on

How to: Getting Limited Dropdown in Viewer after URL Input

Hi there,

I have a small security issue with a report I'm attempting to write.

I use CR 12 and use the CR viewer as User Frontend.

I have a report which has a subreport that requires a businessunit to be selected. I would like to give the user a personalised dropdown of the selectable businessunits that that person belongs to.

as part of our solution we pass in a few parameters in the URL.
The URL has the Parameter &UserID={X} so the report knows who the User is. We do not pass in the business unit unfortunately.

Sample dataset.

UserID       BU
0	     All Business Units
0	     Metro City
0	     South Coast
0	     Central West
0	     Public
7	     Metro City
55	     Central West
55         Metro City
110	     Public
110	     South Coast
1149     Central West
1149     Metro City
1149     Home Delivered

Open in new window

How do I get the end user to see his personal subset of businessunits without them being able to change the Userid.
I have tried a cascading parameter (p1: UserID, P2:Businessunit)

when I check this cascading parameter I see the correct UserID has been chosen and the dropdown is showing. However the user can change the UserID and a different dropdown shows. Which is a bit of a security issue.

Also I have tried to create a procedure where I pass in a UserID and the correct subset displays. However in this case the &UserID={X} does not pass in the UserID to the procedure. so that failed as well.

Any Suggestions?
Crystal ReportsSSRS

Avatar of undefined
Last Comment

8/22/2022 - Mon
Ido Millet

Would a solution that automatically detects the Windows User ID and passes that into the report work for you?
Mike McCracken

If you base the business unit dropdown on a COMMAND that uses the user id as a parameter you should be able to limit the list appropriately


If you are using Business View Manager, you can create dynamic parameters there which can be filtered based on UserID. I typically create a table or view that contains a pick list for specific people or business units, and then use it as the source of the pick list, with a filter applied to limit it by CEUserID.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

My guess is that you have a report with 2 parameters : UserID and BusinessUnit and you are using it in a Desktop application.

I can see 2 possible solutions:
1st solution - this will work also for a Web application
 Inside the report create a command, which will be used to get the BusinessUnit list. the command should be something like :
Select BusinessUnit , .... From  <table> WHERE UserId = {?UserID}

Assign this command as a datasource for the BusinessUnit dynamic parameter.

When you run the report you will see 2 parameters dialogs from Crystal Reports : the first one will ask you just for UserID and will use it to retrieve the values for the command, then the second one will ask you to select just Businessunit from the list.

From your application set the UserID with the current user. Crystal reports will be able to use the provided value and will ask just for BusnessUnit.

2nd solution
Use 3rd party viewer , which supports default values and hidden parameters.
Set the UserID as a hidden parameter (the end user will be not able to change it ) and set the default value to the current user. Set the BusinessUnit parameter as a parameter dependent by UserID and provide the SQL to retrieve the LOV. When the user selects the report UserID will be set to the current user and BusinessUnit  will be refreshed to show values available for this user.

Sample video, which shows a viewer processing similar tasks is available here: http://www.r-tag.com/Pages/Preview_Demo.aspx
 - jump to 6:28 min to see how to set the default value and hide the parameter (or click "Default values and hidden parameters" from the menu in left)
 - jump to 5:20 min to see how to set dependent cascading parameter (or click "Cascading parameters" from the menu in left)

Using Command looks promising.

I'm getting an issue with the web application though as when I use command it prompts the user for Server name and username and Password, which it normally doesn't.

So I will need to talk to the developer and see if there is a different way they need to logon for it to understand what server the Command is for.
Mike McCracken

A command probably does require different code since it runs before the report is opened.  It will in this case use the same database as the report but because it is run before the report, it doesn't get the same database updates as the report does.

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

When you create the command use the same connection as the report. I guess you developer is replacing the report connection in code and if you have the command connected to  a different connection this might create problems.

Hi Vasto,

this is indeed the case, but also necessary as the same report is being used by different customers on different systems (same views and tables though).

So a command might not be working.

What I mean is to create the command from the already crated connection used for the report. In order to create a command you need a connection . If you create a new connection and then create the command you will have 2 different connections inside the report (even if they are pointing to the same database there will be 2 different objects)  - 1 for the command and another one to get the data. My guess is that your developer is setting correctly the second one but not setting the first one and that is why the report is asking for connection info. You need to create the command using the existing connection - in such way the same connection will be used for both command and data.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Can you upload the report ?

Please find attached a test report.

I cannot see a command. Is this the right report ? Or this is the original report ?
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

None of the other solutions provided an acceptable workable solution for deploying as an application to external clients - all assumed total and complete control of the command, report and location of the setup which is not feasible in an ISV model.