9/19/2014

SSRS Tutorials

SQL Server Reporting Services
SSRS Delete Dataset Cache Files
http://www.allaboutmssql.com/2014/02/ssrs-delete-dataset-cache-files.html

SSRS - Sorting in Tables , Matrices and Charts -
http://www.allaboutmssql.com/2013/11/ssrs-sorting-in-tables-matrices-and.html

SSRS - PIE chart
http://www.allaboutmssql.com/2013/10/ssrs-pie-chart.html

SSRS - How to remove space (because of no data) between Bars in Column Chart
http://www.allaboutmssql.com/2013/10/ssrs-how-to-remove-space-because-of-no.html

SSRS - How to make copy of RDL file
http://www.allaboutmssql.com/2013/10/ssrs-how-to-make-copy-of-rdl-file.html

SSRS - How to calculate total of dynamic columns -
http://www.allaboutmssql.com/2013/10/ssrs-how-to-calculate-total-of-dynamic.html

SSRS - Divide by Zero error - Myth of IIF -
http://www.allaboutmssql.com/2013/09/ssrs-divide-by-zero-error-myth-of-iif.html

SSRS - SUM based on condition -
http://www.allaboutmssql.com/2013/09/ssrs-sum-based-on-condition.html

SSRS - Multi-valued Parameter as Stored procedure Input
http://www.allaboutmssql.com/2013/09/ssrs-multi-valued-parameter-as-stored.html

SSRS - Compare two versions of the same report
http://www.allaboutmssql.com/2013/09/ssrs-compare-two-versions-of-same-report.html

SSRS (Matrix) - How to Repeat Headers on Each Page and Keep Headers Fixed while Scrolling
http://social.technet.microsoft.com/wiki/contents/articles/19505.ssrs-matrix-how-to-repeat-headers-on-each-page-and-keep-headers-fixed-while-scrolling.aspx

SSRS - No data message for report items
http://www.allaboutmssql.com/2013/08/ssrs-no-data-message-for-report-items.html

SSRS - How to display table after clicking on chart -
http://www.allaboutmssql.com/2013/08/ssrs-how-to-display-table-after.html

SSRS - How to repeat headers on each page
http://social.technet.microsoft.com/wiki/contents/articles/19398.ssrs-how-to-repeat-headers-on-each-page.aspx

SSRS - How to display multiple columns (header and value) horizontally stacked in one row cell - http://social.technet.microsoft.com/wiki/contents/articles/19363.ssrs-how-to-display-multiple-columns-header-and-value-horizontally-stacked-in-one-row-cell.aspx

SSRS - How to set Column Visibility property for many columns based on Parameter value -
http://social.technet.microsoft.com/wiki/contents/articles/19333.ssrs-how-to-set-column-visibility-property-for-many-columns-based-on-parameter-value.aspx

SSRS - To group same row data with one column having varying data
- http://www.allaboutmssql.com/2013/08/ssrs-to-group-same-row-data-with-one.html

SSRS - How to add variables -
http://www.allaboutmssql.com/2013/08/ssrs-how-to-add-variables.html

SSRS - Example for Conditional formatting -
http://www.allaboutmssql.com/2013/08/ssrs-example-for-conditional-formatting.html

SSRS - How to add Custom Code and example for using Custom Code -
http://www.allaboutmssql.com/2013/08/ssrs-how-to-add-custom-code-and-example.html

SSRS - Example for Tablix with Sparkline / Bar Chart / Indicator -
http://www.allaboutmssql.com/2013/08/ssrs-example-for-tablix-with-sparkline.html

SSRS - Example for Lookup , LookUpSet and MultiLookup functions -
http://www.allaboutmssql.com/2013/08/ssrs-example-for-lookup-lookupset-and.html

SSRS - Stacked Column (bar) Chart -
http://www.allaboutmssql.com/2013/08/ssrs-stacked-column-bar-chart.html

SSRS - Chart with two Vertical (Y) axes - Primary and secondary Vertical axes -
http://www.allaboutmssql.com/2013/08/ssrs-chart-with-two-vertical-y-axes.html

SSRS - Multiple Sparklines chart -
http://www.allaboutmssql.com/2013/08/ssrs-multiple-sparklines-chart.html

SSRS - Bar chart with line -
http://www.allaboutmssql.com/2013/08/ssrs-bar-chart-with-line.html

SSRS - How to repeat headers for each group -
http://www.allaboutmssql.com/2013/08/ssrs-how-to-repeat-headers-for-each.html

SSRS - Multiple ways to split a string into multiple lines -
http://www.allaboutmssql.com/2013/07/ssrs-multiple-ways-to-split-string-into.html

SSRS - Multi Font Color / Multi Font Size within Single Field / Textbox
- http://www.allaboutmssql.com/2013/07/ssrs-multi-font-color-multi-font-size.html

SSRS - In Bar Charts , how to adjust the size of the bars -
http://www.allaboutmssql.com/2013/06/ssrs-in-bar-charts-how-to-adjust-size.html

SSRS - In Charts , how to sort labels on X - axis (Horizontal axis) -
http://www.allaboutmssql.com/2013/06/ssrs-in-charts-how-to-sort-labels-on-x.html

SSRS - In Charts , how to display all labels on the X-axis (Horizontal axis) -
http://www.allaboutmssql.com/2013/06/ssrs-in-charts-how-to-display-all.html

SSRS - [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Month' is not a valid value - http://www.allaboutmssql.com/2013/05/ssrs-rsruntimeerrorinexpression-value.html

SSRS - Examples for using report functions in expressions - http://www.allaboutmssql.com/2013/04/ssrs-examples-for-using-report.html

SQL Server Reporting Services - Example for creating report - http://www.allaboutmssql.com/2012/08/sql-server-reporting-services-example.html

SQL Server Reporting Services - Example for Subreport and Drill-down report - http://www.allaboutmssql.com/2012/09/sql-server-reporting-services-example.html

SSRS - IsNothing - Decision Function - http://www.allaboutmssql.com/2013/01/ssrs-isnothing-decision-function.html

SSRS - IsMissing - Visibility Function - http://www.allaboutmssql.com/2013/01/ssrs-ismissing-visibility-function.html

SQL Server Reporting Services - Lookup Expression http://www.allaboutmssql.com/2012/09/ssrs-lookup-expression.html

SSRS - Report Deployment - http://www.allaboutmssql.com/2013/02/ssrs-report-deployment.html

SQL Server Reporting Services - DATAFILE(rdl.data) - http://www.allaboutmssql.com/2013/03/sql-server-reporting-services.html

Passing comma separated values (SSRS - multivalued parameter) as input to the stored procedure -
http://social.technet.microsoft.com/wiki/contents/articles/17104.passing-comma-separated-values-ssrs-multivalued-parameter-as-input-to-the-stored-procedure.aspx

SSRS tutorial videos


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

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:
  1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  2. 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.
  3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  4. 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:
  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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)
  6. 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.
  7. 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.
  8. 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.
  9. 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

How SQL Server stores data on a binary level and SQL server Internals

Implementing a Data Warehouse with SQL Server

Designing Data Models for Microsoft BI Solutions

Here is another good link for Designing Data Models for Microsoft BI Solutions :

http://channel9.msdn.com/Blogs/rdoherty/Presentation-Designing-Data-Models-for-Microsoft-BI-Solutions

Introducing Microsoft BI Solution Development

Here are good links to learn about microsoft BI solution architecture and tools:

MS BI solution:

Understanding Business Intelligence

Introducing Microsoft BI Solution Development

Introduction to BIDS & MSBI


Report deployment process:

Publishing SQL Server 2008 R2 Reporting Services Reports