Solved

Including Parameters in SQL Reports

Posted on 2007-11-29
7
161 Views
Last Modified: 2010-03-19
Hi,

Am new to SQL Reporting and need to add a parametrer to a .rdl which allows only the valid users to see the reports and not see everyone else in that report. The report dumps all the data for all the users but we want the logged in user to see only his/her report. We have the folder structure as follows

"Agents" with Content Browser permissions to the Staff group from the domain

I want to be able to put a report in it and then include the needed parameters for filtering based on login. I am using BI Studio for doing this. Some of the other reports that the DBA did had the following settings under parameters:
ParameterName: UserID
Data Type: String
Has Default: Checked (in box)
Hide: checked
Prompt user: grayed out

How do I incorporate this in future reports? TIA.
0
Comment
Question by:abhijitm00
[X]
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
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20376247
You can identify the user who is connected to reporting services by the following expression:

 = User!UserID  
0
 

Author Comment

by:abhijitm00
ID: 20377531
Hi digital thoughts,

Just tried what you mentioned but it does not work. I get the Parameters tab and then get the required permissions as mentioned in my above post but it does not seem to apply. I rebuilt the. rdl and uploaded it to test it. It rebuilt it in BI Studio without any errors. Any place I can look where it may be throwing errors? Any other suggestions?

Would it matter what the data source is? In the reports that I am creating the source is "A Custom Data Source" with MS SQL selected and a connection string whereas for the older reports where the permissions seem to be sticking have the Data Source pointing to a "Shared Data Source"

Thanks.
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20377951
Did you look at just the output of User!UserID? Its possible, especially in a domain environment, that the UserID will actually be DOMAIN\UserName, so you may have to split up the value to achieve what you are needing.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:abhijitm00
ID: 20382490
The output  of only User!UserID gives me all the data and does not filter based on login. In the above do you mean putting in two parameteres one for User and other for Domain\Username. Is there a KB article I can look at that you are aware of? Thanks.
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20383622
Well not really, I mean just adding a Label/Text Box to the report and set its value to User!UserID and run the report just to see what its value is.
0
 

Author Comment

by:abhijitm00
ID: 20389191
I am putting this code into the report. Do you think it will work? Thanks,

<ReportParameters>
    <ReportParameter Name="UserID">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>=User!UserID</Value>
        </Values>
      </DefaultValue>
      <Prompt>UserID</Prompt>
      <Hidden>true</Hidden>
    </ReportParameter>
  </ReportParameters>

0
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 500 total points
ID: 20395697
I just did a test and have a field in a table that has a user's name without the domain, so I did a replace to remove the "DOMAIN\" from the UserID and it worked perfectly:

  <ReportParameters>
    <ReportParameter Name="UserID">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>=REPLACE(User!UserID,"DOMAIN\","")</Value>
        </Values>
      </DefaultValue>
      <Prompt>UserID</Prompt>
      <Hidden>true</Hidden>
    </ReportParameter>
  </ReportParameters>
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

732 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