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 :


Excel - How to add Pivot Table and chart

In Excel , We can find PivotTable  and PivotChart options - > Under Insert tab - > PivotTable


How to add Pivot Table ?


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 .


--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



Excel - How to pull data from SQL Server

This article is about pulling data from SQL Server into Excel sheet .

Open Excel - > Under Data tab - > From Other Sources - > click the drop-down - >  click on From SQL Server .





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 .

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

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

A post about me (Sathyanarrayanan S) on TechNet Blogs -> WikiNinjas - Official Blog of TechNet Wiki

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

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 .

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

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 .

See Also: