Solved

Including Parameters in SQL Reports

Posted on 2007-11-29
7
156 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
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

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

 = User!UserID  
0
 

Author Comment

by:abhijitm00
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:abhijitm00
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

13 Experts available now in Live!

Get 1:1 Help Now