Default Value of a Parameter on a SSRS Report will not be updated after it was deployed
Posted: July 28, 2016 Filed under: SQL Server Reporting Services (SSRS) Leave a comment
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.
Retrieve Report Definition (rdl file) from Report Server (SSRS).
Posted: June 8, 2014 Filed under: SQL Server Reporting Services (SSRS) Leave a commentYou 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)
Posted: April 2, 2014 Filed under: SQL Server Reporting Services (SSRS) Leave a comment--Expression =IIF(IsNothing(Fields!expireDate.Value), "",FormatDatetime(Fields!expireDate.Value,DateFormat.ShortDate))
Reporting Services (SSRS) Multi Value Parameter –2
Posted: January 16, 2014 Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) Leave a commentEdit:
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
Posted: October 23, 2013 Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) Leave a commentI came across this question from this thread at MSDN forum:
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
Posted: November 5, 2012 Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) Leave a commenthttp://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 )
Posted: August 8, 2012 Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) 1 CommentThere 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).
Jingyang Li 