How to: Getting Limited Dropdown in Viewer after URL Input

Posted on 2012-12-31
Last Modified: 2013-07-14
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?
Question by:james_imagemation
  • 5
  • 5
  • 2
  • +2
LVL 22

Expert Comment

by:Ido Millet
ID: 38732578
Would a solution that automatically detects the Windows User ID and passes that into the report work for you?
LVL 100

Expert Comment

ID: 38732761
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

LVL 14

Expert Comment

ID: 38732820
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.
LVL 18

Expert Comment

ID: 38732837
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:
 - 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)

Author Comment

ID: 38735342
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.
LVL 100

Expert Comment

ID: 38735355
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.

LVL 18

Expert Comment

ID: 38738747
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.
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


Author Comment

ID: 38738826
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.
LVL 18

Expert Comment

ID: 38738838
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.
LVL 18

Expert Comment

ID: 38738839
Can you upload the report ?

Author Comment

ID: 38738921
Please find attached a test report.
LVL 18

Expert Comment

ID: 38738983
I cannot see a command. Is this the right report ? Or this is the original report ?

Accepted Solution

james_imagemation earned 0 total points
ID: 39312954
In the end we rewrote a Crystal Viewer page to encrypt and decrypt the parameters in the URL to prevent them being changed.

Author Closing Comment

ID: 39324442
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.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports Groups need Hand Cursor on Hover 4 69
Crystal Reports get subgroup count 1 45
SQL query to summarize items per month 5 52
SSRS Highlight Specific Column 3 54
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now