BI Learning Hub: 2014

9/19/2014

SSRS Tutorials

SQL Server Reporting Services

SSRS tutorial videos

		SSRS TUTORIAL View files This album has 22 files.

9/18/2014

Divide by Zero error in SSRS reports

This is a common error when we divide one field's value with another fields value or if second field contains 0 or null then we will be getting #Error issue in our reprot so this error can be handeled in two ways:

1. use IIF condition to avoid this divide by 0 issue:

-> we can use IIF function of SSRS in expression to avoid this issue as suppose we have tow fields which we are using for calculation as Fields!Hours.Value / Fields!Qty.Value so if Fields!Qty.Value contains 0 then it will give #Error so write your expression as:

IIf(Fields!Qty.Value = 0, nothing,Fields!Hours.Value/Fields!Qty.Value)

Or Fields!Qty.Value contains Null vlaue then use below expression:

IIf(ISNothing(Fields!Qty.Value), 0,Fields!Hours.Value/Fields!Qty.Value)

2. if both (0 or null) scenarios can be occured in all reports so we can write a custom code to use in the expression

To add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double

        If second = 0 Then

            Return 0

        Else

            Return first / second

        End If

    End Function

Then, modify the expression accordingly:

= IIF(Fields!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

This will help us to get resolve #Error issue while dividing by zero.

9/16/2014

Lookup in SSRS

Today I use LookUp function of SSRS in one scenario as: I have two different amount (Total Amount and Total Volume) based on the different date values so i have created two different dataset as dataset1 and dataset2 and in both dataset we have a date column which is similar in both dataset so in the report I have created a tablix to show data from first dataset based on the Logged date and in one column I wrote an expression to get value from second dataset based on the transactionDate which is similar as Loggeddate in first dataset so I used below expression in the textbox :

=LookUp(Fields!LoggedFirstDayOfMonth.Value,Fields!TransDateFirstDayOfMonth.Value,Fields!TotalAmount.Value, "Dataset2")

So here "Fields!LoggedFirstDayOfMonth.Value" is coming from first dataset and second argument "Fields!TransDateFirstDayOfMonth.Value" is coming from second dataset and last third argument is that column which will be used for display amount in that textbox.

Logged Month	Total Volume on Logged date	Total Amount on Transaction Date
Sep 2013	$13,850	$1,007
Oct 2013	$14,903	$8,214
Nov 2013	$13,632	$2,747
Dec 2013	$14,405	$1,711

Below is the good link for Lookup and LookUpset function in SSRS:

LookUp and LookUpSet Functions New in SSRS 2008 R2

9/15/2014

good videos about SQL server, SSRS, SSIS

SQL Server Webcasts

ProgrammingInterview

SQL SERVER – Correlated and Noncorrelated – SubQuery

SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example

Sub Query - SubQuery in SQL is a query inside another query.
subQuery is a way to combine or join them in single query. SQL query which is on inner part of main query is called inner query while outer part of main query is called outer query. for example in below sql query section not highlighted is OUTER query while section highlighted with grey is INNER query.

SELECT name FROM City WHERE pincode IN(SELECT pincode FROM pin WHERE zone='west')

SubQuery Rules in SQL

Like any other concept in SQL, subquery also has some rules and you can only embed one query inside another by following rules :

1. subquery can be used in insert statement.

2. subquery can be used in select statement as column.

3. subquery should always return either a scaler value if used with where clause or value from a column if used with IN or NOT IN clause.

Correlated SubQuery - Correlated Subquery is a sub-query that uses values from the outer query or Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query.

NonCorrelated SubQuery - In non correlated subquery, inner query doesn't depend on outer query and can run as stand alone query.

http://blog.sqlauthority.com/2007/09/07/sql-server-correlated-and-noncorrelated-subquery-introduction-explanation-and-example/

http://www.programmerinterview.com/index.php/database-sql/correlated-vs-uncorrelated-subquery/

http://beginner-sql-tutorial.com/sql-subquery.htm

Different strategies for removing duplicate records in SQL Server

Different strategies for removing duplicate records in SQL Server

http://www.mssqltips.com/sqlservertip/1918/different-strategies-for-removing-duplicate-records-in-sql-server/

SQL Server CROSS APPLY and OUTER APPLY

Differnces between Cross Apply and Outer Apply:

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

9/08/2014

Difference between CTE and Temp Table and Table Variable

Funny Videos: FB Fun4you

CTE - A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.

Temp Tables - There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with a '#' sign.

Table Varirable - Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:
DECLARE @ProductTotals TABLE( ProductID int, Revenue money)
A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Differences between temp tbale and table variable:

Temp table: A Temp table is easy to create and back up data.
Table variable: But the table variable involves the effort when we usually create the normal tables.
Temp table: Temp table result can be used by multiple users.
Table variable: But the table variable can be used by the current user only.
Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist.
Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.
Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,
Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table.
Table variable: But the table variable can be used up to that program. (Stored procedure)
Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions.
Table variable: But we cannot do it for table variable.
Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions .
Table variable: But the function allows us to use the table variable. But using the table variable we can do that.
Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls.
Table variable: Whereas the table variable won't do like that.
Performance - A table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Here are some good links about differences between CTE and Temp Table and Table Variable:

Difference between CTE and Temp Table and Table Variable

Temp Tables and types of temp tables

9/05/2014