Default Value of a Parameter on a SSRS Report will not be updated after it was deployed

 

When you modify default value of a parameter for a deployed report of all SSRS versions,
the value will not be updated for the parameter on the reporting server. This is a designed behavior.

The step you need to do is to delete the report through report manager on the server
and redeploy the report and the default value of the parameter will be updated.

Advertisements

Retrieve Report Definition (rdl file) from Report Server (SSRS).

You can use a migration tool from Microsoft to retrieve all report definition in .rdl.
Here is the download link:
http://www.microsoft.com/en-us/download/details.aspx?id=29560


Handle #Error on SSRS Report with Missing Date (null value)


--Expression

=IIF(IsNothing(Fields!expireDate.Value), "",FormatDatetime(Fields!expireDate.Value,DateFormat.ShortDate))



 

Reporting Services (SSRS) Multi Value Parameter –2

Edit:

Actually, you don’t need any functions to make this work. Go ahead to set the parameter with Allow Multiple Value.

It works for both integer and string type parameter.

—————————————————————————————————–

You need to set @state parameter to allow multiple values;

Try these built-in functions


=Split(Join(Parameters!state.Value,","),",")

and your query looks like:

…WHERE state in (@state)
Here is the .rdl for a sample report:

<?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="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Test Project</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>20pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.36in</Height>
        <Width>10in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Tablix Name="Tablix1">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>stateid</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox5">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>name</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox5</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox7">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>state</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox7</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="stateid">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!stateid.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>stateid</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="name">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!name.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>name</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="state">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!state.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>state</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember />
            <TablixMember />
            <TablixMember />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <Group Name="Details" />
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet2</DataSetName>
        <Top>0.91542in</Top>
        <Left>0.56125in</Left>
        <Height>0.5in</Height>
        <Width>3in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Tablix>
    </ReportItems>
    <Height>2.48306in</Height>
    <Style />
  </Body>
  <Width>19in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="myDS">
      <DataSourceReference>myDS</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>f30641da-683e-4eff-96f5-72d180c3dfe3</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>myDS</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@state">
            <Value>=Split(Join(Parameters!state.Value,","),",")</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT [stateid],[name],[state] FROM [dbo].[states] WHERE [state] IN (@state)</CommandText>
      </Query>
      <Fields>
        <Field Name="stateid">
          <DataField>stateid</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="name">
          <DataField>name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="state">
          <DataField>state</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet3">
      <Query>
        <DataSourceName>myDS</DataSourceName>
        <CommandText>SELECT [name],  state  FROM  [dbo].[states]
 </CommandText>
      </Query>
      <Fields>
        <Field Name="name">
          <DataField>name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="state">
          <DataField>state</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportParameters>
    <ReportParameter Name="state">
      <DataType>String</DataType>
      <Prompt>state</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DataSet3</DataSetName>
          <ValueField>state</ValueField>
          <LabelField>name</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
  </ReportParameters>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>c01d05e5-f0de-456c-aaba-651a5200f920</rd:ReportID>
</Report>
 

SSRS Multiple Select Parameter For LIKE Syntax

I came across this question from this thread at MSDN forum:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3e9400ca-cfc3-4f66-8b48-d7e0ee61d969/sql-in-and-like-in-a-sinlge-query-for-ssrs-report?forum=transactsql

After a little research, I came up with a solution to work with a stored procedure. There are two options available for the stored procedure: one is a UDF Split function with a JOIN … Like and the other is to build the query dynamically.

When you use stored procedure for your Datasets, you need to add columns as fileds for the Datasets manually. Of course, you need to set the multiple Select parameter from Parameter list first.

I hope you can follow along with this solution (Two Stored Procedures  in the following)

CREATE PROCEDURE mysp
 @ProjDept nvarchar(2000)
as
Begin
declare @mylike nvarchar(2000)=''
DECLARE @Sql  NVARCHAR(MAX)
SET @mylike=' ProjectDept Like ''%' + REPLACE( @ProjDept,',', '%'' OR ProjectDept Like ''%') +'%'''
SET @Sql = N'SELECT ProjectName,[Owner]  FROM ProjectTable  Where ' + @mylike

EXEC sp_executesql @sql
End
Create PROCEDURE [dbo].[mysp2]
@s nvarchar(2000)
as
Begin
SELECT * from projectTable pt
INNER JOIN dbo.Split(@s,',') fs ON pt.ProjectDept + fs.s + '%'
End
--The s is the column name returned from the SPLIT function I used

Buck Woody’s Full List For SSMS Report Documentation

http://blogs.msdn.com/b/buckwoody/archive/2008/04/17/sql-server-management-studio-standard-reports-the-full-list.aspx


Reporting Services (SSRS) Multi Value Parameter (Integer Type )

There is an easy way to setup a report integer type multiple value parameter without helper function or T-SQL manipulation.
From the Report Parameter Properties:

Check the parameter (ids for example) to allow multiple values

From Dataset Property:

Set the parameter value with this experission:

=Parameters!ids.Value

(The following works with built-in functions)

=Split(CStr(Join(Parameters!ids.Value,”,”)),”,”)

The WHERE clause in query part looks like this

WHERE id IN (@ids)
–other condition …
Of course, you can use Filter from Dataset Property section to do the same with small dataset. (remove the parameter from the WHERE clause).