Monday, July 27, 2015

SSRS – Combine Chart Report

In this article, we will learn how to combine two different charts within each other and represent as a single chart report. Integration a line-chart into a column-chart makes our SSRS report more elegant and insightful to display the meaningful data.

For example; sales admin wants to see the performance of their products in the different regions such as France, England and USA etc. within two different views in a single chart as we did in the excel.  To meet the requirement, Chart Graph reports are the best option to represent the sales data to state or cities level.

We are going to assume that we have Sales Summary data table in our database having the following information –
CountryName
SalesYear
TotalSales
TotalTax
Germany
2008
1981811.89
172758.34
Germany
2008
2981811.89
272758.34
Germany
2008
2581811.89
342758.34
France
2008
3298789.76
306620.72
France
2008
4298789.76
346620.72
France
2008
1298789.76
106620.72
France
2008
1598789.76
166620.72
United Kingdom
2008
2487391.50
220058.34
United States
2008
1181754.12
113381.69
United States
2008
1323769.09
128255.76
United States
2008
1059022.59
101832.09
Virgin Islands, U.S.
2008
1181754.12
113381.69


We need to display the above information in our SSRS report and Report header should be like as “Regional - Sales Summary Report” as shown below:

To create Combined Chart report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Regional - Sales Summary Report” as shown below:


2) Now, we need to create a dataset for the report as shown below:


3) Add Dataset will launch Dataset properties window. We need to set the name as SalesSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:

SELECT
      [CountryRegionName]
      ,[SalesYear]
      ,[TotalSales]
      ,[TotalTax]
FROM [dbo].[SalesSummary]
  




4) After click on the OK button, a dataset has been added in the report data also as shown below:


5) Now right click on the report, click on Chart from Insert as shown below:


6) From Chart Type window, choose Column Chart and click OK button as shown below:


7) Now change the Chart Title as “Regional – Sales Summary” and set the dataset to the column chart in the Chart properties window as shown below:


8) Now, we need to set the Total Sales Amount for Y-Axis and Country Region Name for Category Groups to the column chart as shown below:



9) Click on the preview and the report will be look like as shown below-



10) Now, we need to add another chart type (Line Chart) to the report. For this we need to add another value (Total Sales value again) as shown below:



11) Now right click on the second added TotalSales values and click on the Chart Type as shown below:



12) Now, we need to choose the Line Chart as the secondary chart as shown below:


13) Click OK button and preview of the report as shown below:


This completes the tutorial section of the marge column and line chart into a single chart report.

Other Drill Reports in SSRS
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Column based Drill Down Report, Click here.
4) To learn the Chart based Drill Down Report, Click here.

How can you create your first sub report. Click here.

Friday, July 24, 2015

SQL - DML Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. 
A DML (Data Manipulation Language) trigger fires when an Insert, Update, or Delete statement is performed on the table, view, schema, or database with which the event is associated. They are used to evaluate data after data manipulation using DML statements.
To understand their functionality, we need to create a Department Table in our database and will see the each action of this trigger on this table.  

Use tempdb
Go

---- Create an Example Table
Create Table Department
(
DeptId Int Identity (1,1),
DeptName Varchar(15) Not Null,
DeptBudget Decimal(16,2) Not Null
)

----- Insert Values into Department
Insert into Department (DeptName, DeptBudget) Values
('IT', '1200000.00'),
('Finance', '40020000.00')

---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00

---- Create Audit Department Table which will be used to store the data
---- Whenever trigger will fire
Create Table AuditDepartment
(
DeptId Int,
DeptName Varchar(15) Not Null,
DeptBudget Decimal(16,2) Not Null,
AuditAction Varchar(200) Not Null,
AuditDate Datetime default(Getdate())
)

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate






Now, we have two tables. One is our primary table (Department) to store the values and another one is our secondary Audit table (AuditDepartment) to store all the audit information which is based on our primary table. DML Triggers are the following types of DML triggers:

A)    Instead of Trigger: Instead of trigger are fired in place of the triggering action such as an insert, update, or delete. In ‘Instead of Trigger’ we insert the data into the virtual tables prior to checking the constraints.  As far as ‘After Trigger’ constraints are concerned, they are checked in the first place.  Data is then inserted into the virtual tables ( inserted and deleted tables).

Instead of Insert Trigger - First of all, we will create a Trigger on the Department table which will be called after the insert the record into Department table and fill these new records with the help of magic table Inserted based on the matching condition to validate the data as shown below:
CREATE Trigger tri_InsteadOfInsert_department On Department
INSTEAD OF INSERT
AS
Begin

-- Set Transaction
BEGIN TRAN
-- Set Nocount on
SET NOCOUNT ON

-- check the department budget.
-- If Budget value less than 100000 then raise an error
-- otherwise insert value into Department
IF (Select DeptBudget from Inserted)<100000
begin
RAISERROR('Invalid Budget Value. Budget value should be greater than 100000',16,1);
ROLLBACK;
end
Else
Begin

---- Insert data into main table
Insert into Department (DeptName, DeptBudget)
Select I.DeptName,
I.DeptBudget From Inserted I;
COMMIT;
PRINT 'Record Inserted -- Instead Of Insert Trigger.'
End
End

---- Insert new records into Department
Insert into Department (DeptName, DeptBudget) Values
('Sales-Marketing', '95200000.00')


---- Insert new records into Department
Insert into Department (DeptName, DeptBudget) Values
('Admin', '99999.00')

---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
95200000.00

B) After Trigger: They always fire after SQL Server completes the execution of the action successfully such as an insert, update, or delete.

After Insert Trigger:  First of all, we will create a Trigger on the Department table which will be called after the insert the record into Department table and fill these new records into AuditDepartment table with the magic table Inserted as shown below:

---- create trigger to fire after the insert command executes successfully
CREATE Trigger tri_afterinsert_department On Department
FOR INSERT
AS
Begin
---- Insert Records from Inserted table into AuditDepartment
Insert into AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select I.DeptId,
I.DeptName,
I.DeptBudget,
AuditAction='Record Inserted Successfully- Calling After Insert Trigger',
AuditDate =Getdate()
From Inserted I

Print 'Trigger has been fired successfully after insert new records'
End

---- Insert new records into Department after trigger is created
Insert into Department (DeptName, DeptBudget) Values
('Sales-Marketing', '95200000.00')



---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
95200000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150

After Update Trigger:  Now, we will create a Trigger on the Department table which will be called after the update the existing record in Department table and fill these records into AuditDepartment table with the magic table Inserted as shown below:

---- create trigger to fire after the update command executes successfully
CREATE Trigger tri_afterupdate_department On Department
FOR Update
AS
Begin
---- Insert Records from Inserted table into AuditDepartment
Insert into AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select I.DeptId,
I.DeptName,
I.DeptBudget,
AuditAction='Record Updated Successfully- Calling After Update Trigger',
AuditDate =Getdate()
From Inserted I

Print 'Trigger has been fired successfully after update the existing records'
End

---- Update the existing record
Update Department
set DeptBudget ='145000000.00'
where DeptId=3



---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
145000000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150
3
Sales-Marketing
145000000.00
Record Updated Successfully- Calling After Update Trigger
2015-07-24 15:11:37.630

After Delete Trigger:  Now, we will create a Trigger on the Department table which will be called after  delete the existing record from Department table and fill these records into AuditDepartment table with the magic table Deleted as shown below:

---- create trigger to fire after delete command executes successfully
CREATE Trigger tri_afterdelete_department On Department
FOR Update
AS
Begin
---- Insert Records from Inserted table into AuditDepartment
Insert into AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select D.DeptId,
D.DeptName,
D.DeptBudget,
AuditAction='Record Deleted Successfully- Calling After Delete Trigger',
AuditDate =Getdate()
From Deleted D

Print 'Trigger has been fired successfully after delete the existing record!'
End

---- delete record from the Department table
Delete Department where DeptId=2


---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
3
Sales-Marketing
145000000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150
3
Sales-Marketing
145000000.00
Record Updated Successfully- Calling After Update Trigger
2015-07-24 15:11:37.630
2
Finance
40020000.00
Record Deleted Successfully- Calling After Delete Trigger
2015-07-24 15:19:32.420

Popular Posts