?
Solved

Including Parameters in SQL Reports

Posted on 2007-11-29
7
Medium Priority
?
165 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.
Suggested Courses

764 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