Solved

Including Parameters in SQL Reports

Posted on 2007-11-29
7
158 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
SSRS - Image from DB has ugly blue border !? 8 31
Simple SQL query from two tables 13 51
Inserting oldest record into new table. 5 22
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

786 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