Below scripts can be used to find the names of Stored Procedures that use XQUERY :
SQL Server - Policy based management - System Views
Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :
USE msdb GO
SQL Server - Policy based management - Enforcing naming convention for Database and its objects
This post is about enforcing naming convention for database and its objects through Policy based management in SQL Server .
Naming convention can be enforced for below list of objects :
Note : But Policy evaluation mode differs for each object .
In this post , let us see an example for enforcing naming convention while creating Stored procedure .
Naming convention can be enforced for below list of objects :
Note : But Policy evaluation mode differs for each object .
APPLICATION ROLE |
ASYMMETRIC KEY |
CERTIFICATE |
DATABASE ROLE |
DEFAULT |
INDEX |
RULE |
SCHEMA |
SQL ASSEMBLY |
STORED PROCEDURE |
SYMMETRIC KEY |
SYNONYM |
TABLE |
TRIGGER |
USER |
USER DEFINED FUNCTION |
USER DEFINED TYPE |
VIEW |
XML SCHEMA COLLECTION |
In this post , let us see an example for enforcing naming convention while creating Stored procedure .
SQL Server - Capturing Missing Join Predicate for queries using Extended Events
missing_join_predicate , one of the event - " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .
--Create Session to track missing_join_predicate event with below mentioned Actions
SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'
--Create Session to track missing_join_predicate event with below mentioned Actions
TSQL Script to Generate Create ColumnStore Index Script
This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .
SET CONCAT_NULL_YIELDS_NULL OFF;
SET CONCAT_NULL_YIELDS_NULL OFF;
SQL Server Data Quality Services
This article is about cleansing and formatting data using SQL Server Data Quality Services .
In this post , I am going to show on how to cleanse & format data in the columns of SQL Server table . To do this , first we have to create Knowledge base and then Data quality project .
Creating Knowledge base is nothing but giving hint of source data to DQS and then setting rules on how data should be formatted by creating Domains for each columns .
After creating Knowledge base , create Data quality project making use of knowledge base on our source data and then after deciding on the recommendations provided by DQS , we can export the cleansed (corrected) & formatted data to new table .
Note : click on images to get enlarged view
In this post , I am going to show on how to cleanse & format data in the columns of SQL Server table . To do this , first we have to create Knowledge base and then Data quality project .
Creating Knowledge base is nothing but giving hint of source data to DQS and then setting rules on how data should be formatted by creating Domains for each columns .
After creating Knowledge base , create Data quality project making use of knowledge base on our source data and then after deciding on the recommendations provided by DQS , we can export the cleansed (corrected) & formatted data to new table .
Note : click on images to get enlarged view
Handling XML data in SQL Server
XML :
Extensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation.
Before getting deep into XML, a little introduction to the structure of XML.
Sample XML fragment:
<?xml version = "1.0" encoding = "UTF-16"?> <!—Student Information --> <Studentinfo> <Student> <StudentID>1</StudentID> <StudentName>Sathya</StudentName> <CourseID>1</CourseID> </Student> <Student> <StudentID>2</StudentID> <StudentName>Deepak</StudentName> <CourseID>2</CourseID> </Student> <Student> <StudentID>3</StudentID> <StudentName>sathish</StudentName> <CourseID>3</CourseID> </Student> </Studentinfo>
In the above sample XML fragment ,
<Studentinfo> is the root node
<Student> is the element node,so in the above XML fragment,we havee 3 element nodes.
<StudentID> ,
<StudentName> ,
<CourseID> .. are the attribute nodes
1,sathya,1,.. are the attribute values.
<Student> is the start tag & </Student> is the end tag of the element node.
Similarly,
<Studentinfo> is the start tag & </Studentinfo> is the end tag of root node.
To put it simple in database terminologies,consider
root node as the database name,element node as the table name,attribute node as column name & attribute values as column values.
<!—Student Information --> are XML comments denoted by <!-- and --> delimiters
<?xml version = "1.0" encoding = "UTF-16"?> are XML processing instructions marked by <? and ?> delimiters.
A processing instruction is a means to provide additional metadata to a processing application.
FOR XML clause:
For getting relational data in the form of XML, FOR XML clause was introduced.
Using FOR XML clause,we can represent relational data in the form of XML in two ways:
1.) Attribute Centric
2.) Element Centric
1.) Attribute Centric
2.) Element Centric
/*******************************Sample data ***********************************/
Let us create sample datas for all below XML examples:
SSIS - Merge Transformation
As stated on MSDN BOL :
The Merge transformation combines two sorted datasets into a single dataset .
By including the Merge transformation in a data flow, you can perform the following tasks:
In this article , let us see an example for Merge Transformation .
The Merge transformation combines two sorted datasets into a single dataset .
By including the Merge transformation in a data flow, you can perform the following tasks:
- Merge data from two data sources, such as tables and files.
- Create complex datasets by nesting Merge transformations.
- Remerge rows after correcting errors in the data.
In this article , let us see an example for Merge Transformation .
SSIS - WMI Event Watcher Task
As Stated here , we can use the WMI Event Watcher task for the following purposes:
- Wait for notification that files have been added to a folder and then initiate the processing of the file.
- Run a package that deletes files when the available memory on a server drops lower than a specified percentage.
- Watch for installation of an application, and then run a package that uses the application.
Getting Started with SQL Server Analysis Services
What is a Data warehouse ?
What is Dimensional Fact Model ?
Star schema :
What is Dimensional Fact Model ?
Star schema :
SQL Server - FOR XML clause can be used within CTE
In SQL Server - FOR XML clause can be used within CTE . But it is stated in MSDN BOL as
The following clauses cannot be used in the CTE_query_definition:
To make it more clear , I raised a question on MSDN T-SQL forum - http://social.technet.microsoft.com/Forums/en-US/1b5d3c1a-0083-4f47-b6b0-35f86cdc2291/for-xml-clause-with-cte?forum=transactsql
Here is the Connect case [Feedback] for change in MSDN BOL documentation .
The following clauses cannot be used in the CTE_query_definition:
- ORDER BY (except when a TOP clause is specified)
- INTO
- OPTION clause with query hints
- FOR XML
- FOR BROWSE
To make it more clear , I raised a question on MSDN T-SQL forum - http://social.technet.microsoft.com/Forums/en-US/1b5d3c1a-0083-4f47-b6b0-35f86cdc2291/for-xml-clause-with-cte?forum=transactsql
Here is the Connect case [Feedback] for change in MSDN BOL documentation .
SSRS - Sorting in Tables , Matrices and Charts
This article is about SSRS reports - Sorting in Tables , Matrices and Charts .
Refer my post on TechNet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/20587.ssrs-sorting.aspx
Refer my post on TechNet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/20587.ssrs-sorting.aspx
Excel - How to Sort rows in the Pivoted table
In my previous post , I explained about adding Pivot table and chart .
If you click on the drop-down arrow of Row Label , you will find the sort options as shown below :
If you click on the drop-down arrow of Row Label , you will find the sort options as shown below :
T-SQL - INSERT Statement with Columnslist inside Stored procedure
This article shares the information about what happens when INSERT Statement is used inside Stored procedure with & without Columns list .
--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure
--Create Sample table CREATE TABLE Test (Id INT , Name VARCHAR(20)) GO
--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure
SSRS - PIE chart
In this article , let us see an example for creating SSRS PIE chart .
Let us try to create Pie chart like in MSDN forum users profile .
Drag & drop , Pie chart on to the report body :
From Toolbox - > Report Items - > Chart - > Shape - > Pie
or Under Design pane - > right click on report body - > Insert - > Chart - > Shape - > Pie

Let us try to create Pie chart like in MSDN forum users profile .
Drag & drop , Pie chart on to the report body :
From Toolbox - > Report Items - > Chart - > Shape - > Pie
or Under Design pane - > right click on report body - > Insert - > Chart - > Shape - > Pie
SSRS - How to remove space (because of no data) between Bars in Column Chart
In SSRS , because of no data there can be space between bars in Column Chart .
Try this MSDN SSRS forum discussion for clear explanation on how to remove space (because of no data) between bars in Column Chart .
Try this MSDN SSRS forum discussion for clear explanation on how to remove space (because of no data) between bars in Column Chart .
SQL Server - Tracking Object deleted informations using Extended Events
This article is about tracking Object deleted informations using Extended Events .
USE AdventureWorks2012 GO --Create sample table to test object_deleted event CREATE TABLE TEST_EE_object_deleted (Id INT) GO
SQL Server - Getting started with Extended events
This article is about SQL Server Extended events . In this article , I have provided MSDN links which will provide complete & accurate information about each topics related to Extended events .
SQL Server Extended Events (Extended Events)
is a general event-handling system for server systems.
Introducing SQL Server Extended Events
We can manage & control Extended events in SQL Server using T-SQL ,
also we have GUI option - > Under Object Explorer - > Management - > Extended Events
SQL Server Extended Events (Extended Events)
is a general event-handling system for server systems.
Introducing SQL Server Extended Events
Overview on Extended Events :
We can manage & control Extended events in SQL Server using T-SQL ,
also we have GUI option - > Under Object Explorer - > Management - > Extended Events
SSRS - How to make copy of RDL file
In some cases, we might need copy of RDL file, say .,
For example : you had completed developing your report and only alignment & formatting changes are pending and you don't want your final version of the report to be disturbed because of these changes .
Remember we do have the undo/redo option with CTRL+Z .
But still if you need to make a copy of RDL file, it is simple and easy
For example : you had completed developing your report and only alignment & formatting changes are pending and you don't want your final version of the report to be disturbed because of these changes .
Remember we do have the undo/redo option with CTRL+Z .
But still if you need to make a copy of RDL file, it is simple and easy
SQL Server ORDER BY clause
As stated here ,
ORDER BY clause -
SQL Server ORDER BY using Alias name of the column mentioned in the SELECT statement
SQL Server - ORDER BY with CASE statement - Example
T-SQL - Using Order By clause to return resultset with exact matches first and then partial matches
SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)
Reference link :
ORDER BY clause -
- Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
SQL Server ORDER BY using Alias name of the column mentioned in the SELECT statement
SQL Server - ORDER BY with CASE statement - Example
T-SQL - Using Order By clause to return resultset with exact matches first and then partial matches
SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)
Reference link :
See Also:
SQL Server ORDER BY using Alias name of the column mentioned in the SELECT statement
This article shares information about sorting resultset of T-SQL query using ORDER BY clause with alias name of the column mentioned in the SELECT statement .
The reason that ORDER BY clause works with alias name of the column mentioned in the SELECT statement where as WHERE , GROUP BY & HAVING clause cannot is ORDER BY comes after SELECT statement in the logical query processing phase .
Example :
DECLARE @Tmp TABLE (Id INT) INSERT @Tmp SELECT 3 INSERT @Tmp SELECT 2 INSERT @Tmp SELECT 1 --Without ORDER BY SELECT * FROM @Tmp --With ORDER BY AliasName SELECT Id AliasName FROM @Tmp ORDER BY AliasName
The reason that ORDER BY clause works with alias name of the column mentioned in the SELECT statement where as WHERE , GROUP BY & HAVING clause cannot is ORDER BY comes after SELECT statement in the logical query processing phase .
See Also:
SQL Server - Different ways to shred XML into table
SQL Server - Different ways to shred XML into table :
1.) Using OPENXML or nodes()
2.) SSIS - XML Source task
3.) Powershell
4.) Using VB/C# script (Script task in SSIS)
5.) Using SQL XML Bulkload API
Reference links :
SQL Server XML Bulk load examples
http://www.allaboutmssql.com/2013/08/sql-server-xml-bulk-load-examples.html
SQL Server - Import/Shred XML from XML file placed in a folder into table
http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-ImportShred-XML-7f3baa40#content
SSIS - Shred data from XML file in folder into columns of a table
http://www.allaboutmssql.com/2013/08/ssis-shred-data-from-xml-file-in-folder.html
Best Way to Shred XML document and insert the result set into a table in SQL Server
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/52769628-58e3-4d3f-869b-519f61d86da5/best-way-to-shred-xml-document-and-insert-the-result-set-into-a-table-in-sql-server
SSRS - How to calculate total of dynamic columns
Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/20031.ssrs-how-to-calculate-total-of-dynamic-columns.aspx
See Also:
SSIS Error code 0x80131537 / 2146233033
When I was trying to shred XML into table using SSIS - Data Flow task with XML Source task and OLE DB destination task , I ended up with error code 0x80131537 / 2146233033 because input XML data fails the XSD validation (data type mismatch) .
I searched for the error description on MSDN site - Integration Services Error and Message Reference , but i did not find the error description for this particular error code .
So I started a discussion on MSDN SSIS forumn , refer this thread for explanation on error code 0x80131537 / 2146233033 .
I searched for the error description on MSDN site - Integration Services Error and Message Reference , but i did not find the error description for this particular error code .
So I started a discussion on MSDN SSIS forumn , refer this thread for explanation on error code 0x80131537 / 2146233033 .
See Also:
SQL Server - ORDER BY with CASE statement - Example
In this article , let us see an example for ORDER BY with CASE statement
Consider below sample data :
If we are required to sort based on column and if the name of the column is passed as input , then we can make use of ORDER BY with CASE statement
Consider below sample data :
DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5)) INSERT @Tmp SELECT 1,'C' INSERT @Tmp SELECT 2,'B' INSERT @Tmp SELECT 3,'A'
If we are required to sort based on column and if the name of the column is passed as input , then we can make use of ORDER BY with CASE statement
--Sort Input DECLARE @Sortby VARCHAR(20) = 'Name' SELECT * FROM @Tmp ORDER BY CASE WHEN @Sortby = 'Name' THEN Name ELSE Id END --Sort Input SET @Sortby = 'Id' SELECT * FROM @Tmp ORDER BY CASE WHEN @Sortby = 'Name' THEN Name ELSE Id END
Reference links :
- http://technet.microsoft.com/en-us/library/ms188385.aspx
- http://technet.microsoft.com/en-us/library/ms188723%28v=sql.105%29.aspx
See Also:
SSRS - Divide by Zero error - Myth of IIF
In SSRS , IIF evaluates both True and False part irrespective of condition imposed .
Because of that , expressions using IIF will lead us to error in some cases .
Two common scenarios where IIF causes problem :
i. ) Divide by zero .
ii ) Argument 'Month' is not a valid value .
Refer below two MSDN blogs for explanations on how IIF causes divide by zero error and workaround for the same.
http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-why-does-the-attempted-to-divide-by-zero-error-still-happen.aspx
http://blogs.msdn.com/b/bwelcker/archive/2006/09/26/end-of-amnesia-_2800_avoiding-divide-by-zero-errors_2900_.aspx
Because of that , expressions using IIF will lead us to error in some cases .
Two common scenarios where IIF causes problem :
i. ) Divide by zero .
ii ) Argument 'Month' is not a valid value .
i . ) Divide by zero :
Refer below two MSDN blogs for explanations on how IIF causes divide by zero error and workaround for the same.
http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-why-does-the-attempted-to-divide-by-zero-error-still-happen.aspx
http://blogs.msdn.com/b/bwelcker/archive/2006/09/26/end-of-amnesia-_2800_avoiding-divide-by-zero-errors_2900_.aspx
SQL Server - Concatenating string / integer with NULL
In SQL Server , SET statement - CONCAT_NULL_YIELDS_NULL -
Controls whether concatenation results are treated as null or empty string values.
Concatenation of string with NULL (Example) :
Controls whether concatenation results are treated as null or empty string values.
Concatenation of string with NULL (Example) :
DECLARE @a VARCHAR(5) = 100 SET CONCAT_NULL_YIELDS_NULL ON; SELECT @a + NULL; SET CONCAT_NULL_YIELDS_NULL OFF; SELECT @a + NULL;
SQL Server - How to find environment difference on SSMS Query window
This article is about finding environment difference on SSMS query window .
Suppose If we have access to all environments (Production,QA and Development), we should be really careful in executing DML scripts on respective environments.
On query window at the bottom, we can find the server name, user name, SPID and database details as shown in below image :
Suppose If we have access to all environments (Production,QA and Development), we should be really careful in executing DML scripts on respective environments.
On query window at the bottom, we can find the server name, user name, SPID and database details as shown in below image :
SSMS - How to connect to particular database when connecting to SQL Server database engine
After connecting into SQL Server Database engine and opening Query window , you will notice master database in the Available Databases list by default .
We can choose the required database from the Available Databases list.
To connect to particular database when connecting to SQL Server database engine :
We can choose the required database from the Available Databases list.
To connect to particular database when connecting to SQL Server database engine :
SSRS - SUM based on condition
In this article , let us see an example on how to SUM based on condition in SSRS tablix report .
Consider below query is my report dataset :
Consider, I have tablix report as shown in below image :
In the above shown report, If we are required to SUM values in the Columns Level1 and Level2, only for Code = "IND" and Code = "US", then we need to write expression as shown below :
Consider below query is my report dataset :
DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT) INSERT @tmp SELECT 'CHN',5,10 INSERT @tmp SELECT 'KOL',5,15 INSERT @tmp SELECT 'IND',10,25 INSERT @tmp SELECT 'NY',5,10 INSERT @tmp SELECT 'TX',5,15 INSERT @tmp SELECT 'US',10,25 SELECT * FROM @tmp
Consider, I have tablix report as shown in below image :
In the above shown report, If we are required to SUM values in the Columns Level1 and Level2, only for Code = "IND" and Code = "US", then we need to write expression as shown below :
Won Microsoft TechNet Guru Awards
Won Gold medal - Microsoft TechNet Guru Awards - Under Category -
SQL Server Reporting Services, Power View Technical Guru - July 2013
Gold medal article - SSRS: Multi Font Color / Multi Font Size within a Single Field / Textbox
Announced as Featured article on TechNet Wiki
SQL Server Reporting Services, Power View Technical Guru - July 2013
Gold medal article - SSRS: Multi Font Color / Multi Font Size within a Single Field / Textbox
Announced as Featured article on TechNet Wiki
Awarded as Microsoft Community Contributor
What is a Microsoft Community Contributor?
It is a badge offered by Microsoft which is designed to showcase those who make notable contributions to Microsoft online communities such as TechNet, MSDN and Microsoft Community.
Microsoft Community Contributor (FAQ's) - https://www.microsoftcommunitycontributor.com/faq.aspx
Current recipients list of the Microsoft Community Contributor badge - https://www.microsoftcommunitycontributor.com/current-recipients.aspx
It is a badge offered by Microsoft which is designed to showcase those who make notable contributions to Microsoft online communities such as TechNet, MSDN and Microsoft Community.
Microsoft Community Contributor (FAQ's) - https://www.microsoftcommunitycontributor.com/faq.aspx
Current recipients list of the Microsoft Community Contributor badge - https://www.microsoftcommunitycontributor.com/current-recipients.aspx
SQL Server Technet Wiki Articles
SQL Server Developer Resources
T-SQL Useful Links
SQL Server Query Language - Transact-SQL
SQL Server Reporting Services - List of TechNet Wiki Articles
SQL Server Integration Services - List of TechNet Wiki Articles
SQL Server Analysis Services - List of TechNet Wiki Articles
TechNet Guru Contributions
T-SQL Useful Links
SQL Server Query Language - Transact-SQL
SQL Server Reporting Services - List of TechNet Wiki Articles
SQL Server Integration Services - List of TechNet Wiki Articles
SQL Server Analysis Services - List of TechNet Wiki Articles
TechNet Guru Contributions
See Also:
SQL Server Performance tuning with Indexing
SQL Server - ISNUMERIC
ISNUMERIC - Determines whether an expression is a valid numeric type .
Returns 1 if it is a valid numeric type else 0 .
Returns 1 if it is a valid numeric type else 0 .
--Valid Scenarios (ISNUMERIC returns 1) --valid numbers SELECT ISNUMERIC(12345) --valid numbers inside quotes SELECT ISNUMERIC('12345') --valid numbers with comma separated SELECT ISNUMERIC('1,23,456') --decimal type SELECT ISNUMERIC(12345.) --decimal type SELECT ISNUMERIC(123.45) --decimal type SELECT ISNUMERIC(.12345) --decimal type SELECT ISNUMERIC('.')
SQL Server - Script to find particular column from all tables/views except system databases
Below script can be used to find particular column from all tables/views in all databases except system databases
IF OBJECT_ID('tempdb..##SearchColumn') IS NOT NULL BEGIN DROP TABLE ##SearchColumn END CREATE TABLE ##SearchColumn (ObjectName NVARCHAR(200)) EXECUTE sp_MSforeachdb ' IF ''?'' NOT IN (''master'',''model'',''Msdb'',''tempdb'') --system databases BEGIN USE [?] INSERT ##SearchColumn SELECT Object_name(object_id) FROM Sys.Columns WHERE name LIKE ''%BusinessEntityID%'' --Pass ColumnName END' SELECT * FROM ##SearchColumn
SQL Server - Display dynamically horizontal rows vertically
Refer my post on Technet gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-display-602afad6
Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19698.sql-server-display-horizontal-rows-vertically.aspx
Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19698.sql-server-display-horizontal-rows-vertically.aspx
See Also:
SSRS - Multi-valued Parameter as Stored procedure Input
In this post, I have shared about how to handle when SSRS dataset gets delimited string as input from multi valued parameter.
Refer my post on Technet Wiki
Refer my post on Technet Wiki ( for single character multi-valued parameter )
Refer my post on Technet Wiki
Refer my post on Technet Wiki ( for single character multi-valued parameter )
See Also:
SSRS - Compare two versions of the same report
Refer my post on Technet gallery - SSRS - Compare two versions of the same report
http://gallery.technet.microsoft.com/scriptcenter/SSRS-Compare-two-versions-44be98e5
Refer my post on Technet Wiki - SSRS - RDL Compare -
http://social.technet.microsoft.com/wiki/contents/articles/19611.ssrs-rdl-compare.aspx
http://gallery.technet.microsoft.com/scriptcenter/SSRS-Compare-two-versions-44be98e5
Refer my post on Technet Wiki - SSRS - RDL Compare -
http://social.technet.microsoft.com/wiki/contents/articles/19611.ssrs-rdl-compare.aspx
See Also:
T-SQL Script to generate Index Creation Scripts for all tables in a database
Refer my post on Technet gallery :
SQL Server - Generate Index Scripthttp://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441
Refer my post on Technet Wiki :
T-SQL Script to generate Index Creation Scripts for all tables in a database
http://social.technet.microsoft.com/wiki/contents/articles/19598.t-sql-script-to-generate-index-creation-scripts-for-all-tables-in-a-database.aspx
See Also:
T-SQL Script to get information about Index settings
Refer my post on Technet gallery :
T-SQL Script to get detailed information about Index settings for all tableshttp://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-get-03d0fee7
Refer my post on Technet Wiki :
T-SQL Script to get detailed information about Index settings
http://social.technet.microsoft.com/wiki/contents/articles/19593.t-sql-script-to-get-detailed-information-about-index-settings.aspx
See Also:
SSRS - No data message for report items
This article is about handling display of report items like chart , table or matrix when there is no data
returned from the dataset .
For example :
Consider below query is my dataset :
I have table & column chart on the report body , when I preview the report and enter MonthNo =12 and click on View report
returned from the dataset .
For example :
Consider below query is my dataset :
DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT) INSERT @SalesbyMonth SELECT 'January',1000,1 INSERT @SalesbyMonth SELECT 'February',100,2 INSERT @SalesbyMonth SELECT 'March',100,3 INSERT @SalesbyMonth SELECT 'April',200,4 INSERT @SalesbyMonth SELECT 'May',500,5 INSERT @SalesbyMonth SELECT 'June',800,6 INSERT @SalesbyMonth SELECT 'July',500,7 INSERT @SalesbyMonth SELECT 'August',100,8 INSERT @SalesbyMonth SELECT 'September',900,9 INSERT @SalesbyMonth SELECT 'October',600,110 INSERT @SalesbyMonth SELECT 'November',100,11 INSERT @SalesbyMonth SELECT 'December',100,12 SELECT * FROM @SalesbyMonth WHERE MonthNo = @MonthNo
I have table & column chart on the report body , when I preview the report and enter MonthNo =12 and click on View report
SSRS - How to display table after clicking on chart
If we have a requirement to display table only after clicking on chart then follow below steps :
Consider below query is my dataset :
I have Column chart and table on the report body as shown below :
To display all labels on the X-axis (Horizontal axis)
To sort labels on X - axis (Horizontal axis)
Parameter settings is as shown in below images :
Consider below query is my dataset :
DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT) INSERT @SalesbyMonth SELECT 'January',1000,1 INSERT @SalesbyMonth SELECT 'February',100,2 INSERT @SalesbyMonth SELECT 'March',100,3 INSERT @SalesbyMonth SELECT 'April',200,4 INSERT @SalesbyMonth SELECT 'May',500,5 INSERT @SalesbyMonth SELECT 'June',800,6 INSERT @SalesbyMonth SELECT 'July',500,7 INSERT @SalesbyMonth SELECT 'August',100,8 INSERT @SalesbyMonth SELECT 'September',900,9 INSERT @SalesbyMonth SELECT 'October',600,110 INSERT @SalesbyMonth SELECT 'November',100,11 INSERT @SalesbyMonth SELECT 'December',100,12 SELECT * FROM @SalesbyMonth
I have Column chart and table on the report body as shown below :
To display all labels on the X-axis (Horizontal axis)
To sort labels on X - axis (Horizontal axis)
Parameter settings is as shown in below images :
SSIS - Extract filename from file path using TOKEN and TOKENCOUNT
TOKEN and TOKENCOUNT are new string functions introduced from SSIS 2012 .
As mentioned in MSDN :
TOKEN - Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned.
Syntax :
Example : (To extract filename from file path using TOKEN and TOKENCOUNT )
Below expression returns 3 , because delimiter_string "\\" splits the character_expression
"G:\\GAMES\\fifa.exe" into three parts .
TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\")
Below expression returns fifa.exe , because delimiter_string "\\" splits the character_expression "G:\\GAMES\\fifa.exe" into three parts, Occurrence is specified as 3, so the third token in the string is returned.
TOKEN("G:\\GAMES\\fifa.exe", "\\",3)

To extract filename from file path using TOKEN and TOKENCOUNT
TOKEN("G:\\GAMES\\fifa.exe", "\\", TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\"))

For more info & examples :
Token - http://technet.microsoft.com/en-us/library/hh213216.aspx
TokenCount - http://technet.microsoft.com/en-us/library/hh213135.aspx
As mentioned in MSDN :
TOKEN - Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned.
Syntax :
TOKEN(character_expression, delimiter_string, occurrence)
TOKENCOUNT - Returns the number of tokens in a string that contains tokens
separated by the specified delimiters.
Syntax :
TOKENCOUNT(character_expression, delimiter_string)
Example : (To extract filename from file path using TOKEN and TOKENCOUNT )
Below expression returns 3 , because delimiter_string "\\" splits the character_expression
"G:\\GAMES\\fifa.exe" into three parts .
TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\")
Below expression returns fifa.exe , because delimiter_string "\\" splits the character_expression "G:\\GAMES\\fifa.exe" into three parts, Occurrence is specified as 3, so the third token in the string is returned.
TOKEN("G:\\GAMES\\fifa.exe", "\\",3)
To extract filename from file path using TOKEN and TOKENCOUNT
TOKEN("G:\\GAMES\\fifa.exe", "\\", TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\"))
For more info & examples :
Token - http://technet.microsoft.com/en-us/library/hh213216.aspx
TokenCount - http://technet.microsoft.com/en-us/library/hh213135.aspx
How to check the syntax of dynamic SQL before execution
Refer my post on Technet Gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-check-the-935002e7
SSRS - Example for Conditional formatting
In this post - http://www.allaboutmssql.com/2013/08/ssrs-example-for-tablix-with-sparkline.html
Under section TablewithIndicators , Indicators color are displayed based on Indicator column value .
In similar way we can do conditional formatting on the report .
Under section TablewithIndicators , Indicators color are displayed based on Indicator column value .
In similar way we can do conditional formatting on the report .
SSRS - Example for Tablix with Sparkline / Bar Chart / Indicator
This article is about an example for creating table with
i) Sparkline
ii) Bar Chart
iii) Indicator
Create Data source and then Datasets
Dataset name : TablixWithGraph
Dataset Query :
DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000
SELECT * FROM (
SELECT Products,Q1,Q2,Q3,Q1 AS Q11,Q2 AS Q22,Q3 AS Q33 FROM @FY) tmp
UNPIVOT
(QData FOR Qs IN (Q11,Q22,Q33)
) AS Unpvt
Dataset name : TablixWithIndicators
Dataset Query :
i) Sparkline
ii) Bar Chart
iii) Indicator
Create Data source and then Datasets
Dataset name : TablixWithGraph
Dataset Query :
DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000
SELECT * FROM (
SELECT Products,Q1,Q2,Q3,Q1 AS Q11,Q2 AS Q22,Q3 AS Q33 FROM @FY) tmp
UNPIVOT
(QData FOR Qs IN (Q11,Q22,Q33)
) AS Unpvt
Dataset name : TablixWithIndicators
Dataset Query :
SSRS - Example for Lookup , LookUpSet and MultiLookup functions
In this article, let us see examples for Lookup, LookUpSet and MultiLookup functions .
As mentioned in MSDN:
Lookup -
Returns the first matching value for the specified name from a dataset that contains name/value pairs.
LookUpSet -
Returns the set of matching values for the specified name from a dataset that contains name/value pairs.
MultiLookup -
Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.
Examples:
Create Data source and then Datasets
Dataset name : LookUp_Parameter
Dataset Query :
As mentioned in MSDN:
Lookup -
Returns the first matching value for the specified name from a dataset that contains name/value pairs.
LookUpSet -
Returns the set of matching values for the specified name from a dataset that contains name/value pairs.
MultiLookup -
Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.
Examples:
Create Data source and then Datasets
Dataset name : LookUp_Parameter
Dataset Query :
DECLARE @Country_Master TABLE(Id INT,Country_Code VARCHAR(10),Country_Name VARCHAR(10)) INSERT INTO @Country_Master SELECT 1,'IND','INDIA' INSERT INTO @Country_Master SELECT 2,'US','USA' INSERT INTO @Country_Master SELECT 3,'CN','CHINA' SELECT * FROM @Country_Master
Subscribe to:
Posts (Atom)