How to hide null values from the "Drill-Down" Report of SSRS?

one of my colleague asked me this, how to hide null values in the "Drill Down" report of SSRS?

For doing this scenario, i have created the table with below script.


CREATE TABLE Test_Report ( Category Varchar(100), SubCategory Varchar(100) Null, Product Varchar(100) Null, OrderQty int Null, OrderYear int Null)
INSERT INTO Test_Report VALUES
 ( 'Bikes',NULL,NULL,NULL,Null)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Bikes',NULL ,NULL,NULL,NULL)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Clothing','ABCDE','EFGHIJ',10,2005)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)           
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Accessories','ABCDE','pqrst',20,2006)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)
,('Components','ZYXW','VUTSRQP',30,2013)

Working with "CHARACTER MAP" transformation in SSIS

Today i will cover "CHARACTER MAP" Transformation.
In this post i will cover,

• what is Character mapping?
• how to use character mapping?
•  why to use character mapping?
• what are the types of conversions we can do using "Character Map" transformation?

CHARACTER MAP:
 Character map transformation is one of basic and less use transformation ,present in Data Flow Tool Box.


Character Mapping will convert string format into required format (UPPER CASE, lower case, Full width, Half width..).

Character map transformation look like below in Data Flow Tool Box

 By using character Mapping we can do following operations .

How to use Character Map transformation:
First add "DATA FLOW TASK" in "CONTROL FLOW".

  

 

Then, double click on "Data Flow" Task, Now you are in "Data Flow" Designer Tab.

Drag "Character Map" Transformation from the available transformations into "Data Flow" designer.

 Right click on "Character Map" and select "Edit" to view available operations.

After Clicking "Edit", it shows available columns, from these select  one column to perform operation.

After selecting column, we can  select "New column" or "In-Place Change" under "Destination".

Click on "Operation", it shows list of available operations what we can perform. Here i selected "UPPER CASE" operation ( it converts all case into UPPER CASE)..

Connect this transform to destination or another transform. Here i connected to sort transformation and added "Data viewer" to view the output.

Execute the package. And here is the required output.

Difference Between "Contol Flow" and "Data Flow" in SSIS

If you are a beginner for SSIS, you should know the difference between "Control Flow" and "Data Flow".
 First we start with Control Flow
CONTROL FLOW:
 As the name suggest, "Control Flow" means controlling the flow of execution of SSIS package. Before designing the SSIS package, you have to know the flow of containers/tasks.
For ex: you want to design SSIS package with following conditions .
            i. Data has to load to SQL server
           ii. Before loading the data , already present data in the table should be delete.

To design above package, first we have to create "Execute SQL task" then  connect it to " Data flow task" by using "precedence containers" to load the data. i will discuss later about "Precedence Constraints".

Control flow consists of many components like... Execute SQL task, For Each loop Container,Data flow task.. and so on. We need to understand whether we have to connect sequential or parallel.
Below image explains parallel and sequential connections.

 In the above package, "Bulk Insert Task" and "Data Flow Task" execute serially . "Sequence Container" and "For each loop Container" execute simultaneously.

Data Flow task:
 Data flow task is heart of SSIS. Major ETL tasks performed in "Data Flow task" only. As the name suggested, this task is used to transfer the data from Source to destination. While transferring the data, we can use transformations like... conditional split, row sampling, derived column.. etc. The entire process is called... Extracting data from Source , Transforming the data into it desired and Load the data into Destination, which is called ETL . In this "Transformation" is optional.

In the above package
Source: OLEDB Source ( From where we are extracting the data)
Transformation: Performing some operations to get required format
Destination: Flat File (Loading the data into Destination).

Precedence Constraints:
 Precedence constraints used in "Control Flow" task. In Control Flow , if you are executing sequentially,  for connecting each task with other we use precedence constraints . These are 3 types
1. Success (Color: Green) : if task1 executes successfully then go to task2
2. Failure (Color: Red): if task1 fails then go to task3
3. Completion  (Color: Blue): if task1 completes (irrespective of success or failure) go to task4.
Below image will help you to understand better.

Creation of precedence Constraint:
When we add any task in control flow designer from tool box, you have noticed one green arrow coming from that task. To create a precedence constraint , you need to connect this arrow with another task/container. If you want to change the constraint from success (Green) to Failure(Red)/ Completion(Blue), you need to double click on that arrow, then following dialogue box will open.

 You can also perform following in precedence constraints:

• Writing Constraints
• Writing Expressions
• For Multiple constraints, you can select "Logical  AND " or "Logical OR".

Working With BULKINSERT Task in SSIS 2008

Hi Today i will start discuss with "BULK INSERT" Task.

BULK INSERT Task used to copy the large amount data from text files to SQL Server Tables.
EX: You have one (1) million records of data present in your text (.txt) file and if you want to import into SQL Server Table, the easiest way to achieve this is by using BULK INSERT Task.

You can configure BULK INSERT Task Like Below:

1. Drag and drop BULK INSERT TASK from Toolbox to CONTROL FLOW

2.Double Click on Bulk Insert Task to open task editor  . After Opening your Task should look like this

3. Click on "Connections" from the left side menu and choose your SQL DATABASE and Related TABLE By configuring OLEDB Connection Manger.

4. Select "source file" connection path under "source" option.

5. click on the "options" in the left tab of the editor, and select Starting row(First row). Also specify actions to perform on the destinations table or view when the task inserts the data.

 6. At last run the package and your source(.txt) data will copy into Destination (SQL Server Table).

Bulk Insert task is easy to use and configure. But it has some limitations. They are:

1.It only allows to append the data into  the table  and you cannot perform truncate and
load.
2. Only FaltFile can be used as source and not any other types
3. Only SQL Server Databases Can be used as destination. It doesn't Support any other
Files/RDBS Systems.
4. A  failure in the Bulk insert task doesnot automatically load back sucessfully loaded
batches
5. only members of SYSADMIN fixed server role can run a package that contains Bulk.

How to Display Current Month VS Previous Month column in Matrix?

Today i will explain how to display (currentmonth- Previous Month ) column in Matrix.

 i Have JAN to DEC column grouping and i want to display currentmonth -Previous Month column out of the group column.

Then

write Below expression

=SUM(iif(Fields!Month.Value = Month(now.Addmonths(- 1)), Fields!Orders.Value, Nothing)) 

- SUM(iif(Fields!Month.Value = now.Month, Fields!Orders.Value, Nothing)) 

Powerpivot ribbon in WIndows XP and Windows 7

While working on Powerpivot in different  Versions (WIndows XP(SP3), WIndows 7& 8). I have found one  Major difference on Powerpivot.

The power pivot ribbon is not appearing in windows XP.

 This article explains how to use powerpivot ribbon on windows XP by using windows 7 powerpivot ribbon

The windows XP Powerpivot ribbon Looks like 

 Menu Items that Map to the Home Tab

The following graphic shows how features in the Home tab relate to features in the Windows XP menus and toolbar.

 Menu Items that Map to the Design Tab

The following graphic shows how features in the Design tab relate to features in the Windows XP menus. 

Menu Items that Map to the Linked Tables Tab

The following graphic shows how features in the Linked Tables tab relate to features in the Windows XP