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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
LVL 23

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 101

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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 101

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.

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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