?
Solved

Including Parameters in SQL Reports

Posted on 2007-11-29
7
Medium Priority
?
171 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
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

593 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