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.