Remove From My Forums

Default values for parameters specific to each user in SSRS Reports

Question
0

Sign in to vote

Hi All,

Can we set default values for parameters specific to each user in ssrs?

For example: we have ssrs report "Report 1" where it contains "Project" filter which gets data from SQL Server.

My requirement is when "user 1" runs the report by selecting "Project 1" from "project" filter and "User 2" logins in and run the same by selecting "Project 2" from the "Project" Filter.

So when "User 1" logins in again, the report should pick previous value selected by User from "Project" filter i.e., "Project 1" and report should run without asking for any value from "Project" Filter.

Same with "User 2" when he logins in report should automatically pick "Project 2" from the "Project" Filter and report should run.

Is it possible to do in SSRS?

Wednesday, October 24, 2012 2:07 PM

Answers

Hi Aravinda,

Yes this can be done in ssrs, you need to built a logic around it

For example

1.) Create new table that holds project and user id in it.

2.) Set the default value for the parameter "projects" filter from this table based on the userid passed.

3.) Create a stored procedure which will update the new selected value in the datebase on the view report button click and return the new project value in the stored procedure and assign it the some hidden textbox.

I have attached a sample code (SQL and rdl) along with this (Report created in ssrs 2008 r2).

SQL Code:

USE [master]
GO

/****** Object:  Database [TestRpt]    Script Date: 10/24/2012 8:18:40 PM ******/
CREATE DATABASE [TestRpt]
GO

USE [TestRpt]
GO

/****** Object:  StoredProcedure [dbo].[rptReports]    Script Date: 10/24/2012 8:18:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[rptReports] @ProjectName NVARCHAR(100)
	,@UserId NVARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE [dbo].[ProjectsMember]
	SET [ProjectName] = @ProjectName
	WHERE UserId = @UserId

	SELECT MAX(ProjectName) AS ProjectName
	FROM [dbo].[ProjectsMember]
	WHERE UserId = @UserId

	SET NOCOUNT OFF
END
GO

/****** Object:  Table [dbo].[Projects]    Script Date: 10/24/2012 8:18:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Projects] ([ProjectName] [nvarchar](100) NULL) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[ProjectsMember]    Script Date: 10/24/2012 8:18:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProjectsMember] (
	[ProjectName] [nvarchar](100) NULL
	,[UserId] [nvarchar](100) NULL
	) ON [PRIMARY]
GO

RDL File (SSRS 2008 r2)

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Textbox Name="ProjectName">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>=First(Fields!ProjectName.Value, "DataSet3")</Value>
                <Style />
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>ProjectName</rd:DefaultName>
        <Top>1.04042in</Top>
        <Left>1.32167in</Left>
        <Height>0.25in</Height>
        <Width>1.97917in</Width>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
    </ReportItems>
    <Height>2in</Height>
    <Style />
  </Body>
  <Width>6.5in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>dsReport</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>15c2aaa3-93b0-4934-b1c4-a67f0840803f</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>SELECT * FROM PROJECTS</CommandText>
      </Query>
      <Fields>
        <Field Name="ProjectName">
          <DataField>ProjectName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>="select MAX(ProjectName) AS ProjectName from ProjectsMember where UserId = '" &amp; User!UserID &amp; "'"</CommandText>
      </Query>
      <Fields>
        <Field Name="ProjectName">
          <DataField>ProjectName</DataField>
          <rd:UserDefined>true</rd:UserDefined>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet3">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@ProjectName">
            <Value>=Parameters!Projects.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@UserId">
            <Value>=User!UserID</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>rptReports</CommandText>
      </Query>
      <Fields>
        <Field Name="ProjectName">
          <DataField>ProjectName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportParameters>
    <ReportParameter Name="Projects">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DataSet2</DataSetName>
          <ValueField>ProjectName</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Projects</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DataSet1</DataSetName>
          <ValueField>ProjectName</ValueField>
          <LabelField>ProjectName</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>c6b23c45-32b1-4d68-aa25-58c66fe58fab</rd:ReportID>
</Report>

Regards Harsh

Proposed as answer by Charlie Liao Wednesday, October 31, 2012 2:36 AM
Marked as answer by Charlie Liao Thursday, November 1, 2012 1:26 AM

Wednesday, October 24, 2012 2:51 PM

0

Sign in to vote
Hi Aravinda,

We can create a parameter to get the UserID, and then use this UserID to get the default value in the table. The steps below are for your reference.

    1. Create new table that holds project and user id in it.
    2. Create a parameter named UserID, and use the expression below to set the default of it.
        =User!UserID
    3. Create a dataset named Project using the query below.
        select ProjectName from tablename where UserID=@UserID
    4. Create a parameter named ProjectName, and then using ProjectName which we create in step 3 as the default.

I have tested it on my test environment, it works fine. The screenshots below are for your reference. When I login using FAREAST\v-caliao user, the default value is Charlie, and when I login using Liaocaizhi-MSFT\Liaocaizhi, the default value is Leo.

If you have any questions, please feel free to ask.

Regards,
Charlie Liao

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
- Proposed as answer by Charlie Liao Wednesday, October 31, 2012 2:36 AM
- Marked as answer by Charlie Liao Thursday, November 1, 2012 1:26 AM
Thursday, October 25, 2012 2:01 AM

All replies