Pivot In SQL Server 2008

PIVOT is a new feature included in SQL Server 2008 which can be used to rotate rows into columns and columns into rows adn create cross tab query.

This is one very interesting thing in SQL Server 2008. If we want to switch the rows and columns in a table, we can use Pivot.

Let us see this with an example..



Now the way I want to see this is a little different.



If you observe the above result set, I am interchanging the rows and columns in the original result set. This can be achieved by using PIVOT.

Below query will do this for me..

The FROM clause create the values that you want ot see in rows of the newly created columns. we used the for operator to list the values that we wanted to pivot in the 'Element' column.

Hide/UnHide Columns In SSRS Report while rendering

We know how to add filters in a report which filter the result set according to the filter expression.

Now, we will see how to Hide and UnHide the columns in a report by using a filter (Parameter).

For this, we need some Custom coding.

Let us see the report..

Now, I want to see only the columns "Sales OrderID", "OrderDate" and "CustomerID"

For this, we need to create a parameter in the report.

The name of the parameter can be anything, and I am naming it "Column"

we will have to First give all the column names as available values.

Then default the parameter to show all these columns.

Now, in the Code section in report properties, add the following custom Code.

Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean

Return 0 <>

End Function

Now, in the column group section in the report, when you click on the small arrow, shown in figure, you will see advanced Mode. Click on that, and you will see all the hidden Column Groups. One Group for every column in the report.

Click on each group and in the "Hidden" property of the group, add this code..

For 1st Column Group,

=Not Code.IsColumnSelected(Parameters!Column.Value, "SalesOrderID")

For 2nd Column Group,

=Not Code.IsColumnSelected(Parameters!Column.Value, "OrderDate")

And so on....

Now the report will look like this..

When you select the values in the parameter, the report will only show those columns which you selected in the parameter.

Example,

Export SSRS Report data to PDF in Landscape

It is quite a common situation that users request the developers to make a report in such a way that when they export the report to an excel or PDF, they should not have to change the print properties, but automatically, it should export in landscape.

The solution for this is very simple.
In the below Figure,

you see the highlighted property for page size.

(You will find this in report properties). you just have to change those according to the size of paper they are printing on.

In this case, the paper size is regular 'letter'. So what I will do is just change the width to 11 inches, and Height to 8.5 inches.

That is it... now, if the user exports the report to excel or PDF, the file will be in landscape.

Difference Between Stored Proc's and Functions

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not return value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a funtion or another sstored proc similarly a function can call another function and a stored proc.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.

8. Joins can be made by calling table-values functions where as joins cannot be made by stored procs as cannot return table datatype.

Sometimes you'll want to use a SP for performance reasons. It used to be the case that MS SQL Server precompiled execution plans forstored procedures, however, it only caches recently used execution plans now.

NOLOCK and READPAST table hints

When data in a Database is being read or modified, the database engine uses 'Locks' to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.

NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

READPAST: Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Examples:

NOLOCK:

With out any locks specified on the select query, the table 'sales' in schema 'Cars' has 150 records.

SELECT COUNT(*) from Cars.sales

Result : 150

Now, I will insert 2 more rows into this table.

BEGIN TRAN

INSERT INTO Cars.Sales (ID, Make, Model, Price, Color)

VALUES (151, 'Audi', 'R8', 114000.00, 'Red')

INSERT INTO Cars.Sales (ID, Make, Model, Price, Color)

VALUES (152, 'BMW', 'M5', 70000.00, 'Black')

Note: Remember, I did not commit the transaction yet.

Now, I will issue the same select Statement with NOLOCK

SELECT COUNT(*) from Cars.sales (NOLOCK)

RESULT: 152

Now, I do not want these rows to be in the table and I issued a ROLLBACK command..

ROLLBACK TRAN

and all the new rows that I have inserted will be rolled back and if the execute my same SELECT statement, I will get 150 records.

SELECT COUNT(*) from Cars.sales (NOLOCK)

RESULT: 150

So NOLOCK reads uncommited data from the database.

READPAST

In this example, I will Update one record from the 'sales' table ..

BEGIN TRAN

UPDATE Cars.Sales

SET COLOR = 'Red'

WHERE ID = 150

Again, I did not commit the transaction.

Now, If I issue the SELECT statement with READPAST Keyword..

SELECT COUNT(*) FROM Cars.Sales (READPAST)

Result: 149

So as we see, the select statement will ignore the records which are locked in any transaction.

Expand All Drill Downs In SSRS Report With A Parameter

Sample Report:

Now, the user can expand each sales Order and view the details of that particular order.

But if the user needs to see all the details of all the sales orders, then it would be extremely difficult to expand each of the sales order individually.

The solution for this is pretty simple.

1. First Create a Parameter "Expand All" With Boolean DataType.

2. Give the available labels as Yes and No with values True and False respectively.

3. Set the default value to be No (False).

4. Set the Details row "Row Visibility" Property as shown..

and the expression should be.. =IIF(Parameters!ExpandAll.Value="False",True,False)

5. Now the important thing not to forget is to change the "InitialToggleState" property based on an expression.

And this Expression should be.. =IIF(Parameters!ExpandAll.Value="True",True,False)

Now lets see the report Functionality..

(With "Expand All" Parameter set to 'Yes')

ROW_NUMBER Function in T-SQL.

A Sample query with out a Row Number function:

The result set for the above query:

Now, lets see the query with Row Number Function..

And the Result Set..

Now When we use joins, we can use the query with Row Number Function and get the most recent Lock for any particular loan.

Query with joins..

And the Result..

Now If we did not use the Row Function in the Join Statement, we would have end up with multiple LoanID's with all the locks that were ever placed on that Loan. But now, using this Row Number Function, we were able to get only the most recent lock which we need.