SQL Geometry Data Type example

Using Multi-Value Parameter in SSRS via Stored Procedure

It has been found that in SSRS 2005-2012, when we use direct query as data set we are able to apply multi-value parameter and the report works when we select multiple values in parameter.

E.g., Let the direct query be as follows:

SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country in (@Country)

When we use the above query in dataset a parameter Country will be created in Parameter list, and we can configure parameter as shown below, the report work well when we select multiple values in parameter.

When the same query is executed as stored procedure, the report will fail when we select multiple values in parameter. To overcome this issue we need to create the given function in our database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MultiParameter]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

And we need to modify the stored procedure as like below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  BabukannanM
-- Create date: September 19, 2012
-- Description: To list of Customer by Countrywise
-- =============================================
CREATE PROCEDURE [dbo].[Rpt_Customer]
 @Country NVARCHAR(MAX)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
  WHERE Country IN (Select * from [dbo].[MultiParameter] (@Country, ',') )

END
GO

 Now configure the report parameter to select multi-values.

How to get customized colors in SSRS chart

While we design SSRS chart, we have to use available colors from SSRS Color pallette or color templates by selecting them from color, background color, etc. Instead we can apply our own defined colors to our chart by following below steps:

Step 1: Create new SSRS report.

Step 2: Go to Report properties of the SSRS report. Open Code section and copy-paste the below code in the Code area:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0

Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String

If mapping.ContainsKey(groupingValue) Then

Return mapping(groupingValue)

End If

Dim c As String = colorPalette(count Mod colorPalette.Length)

count = count + 1

mapping.Add(groupingValue, c)

Return c

End Function

Step 3: Now create a chart (e.g., pie chart); Open Series properties, in Fill expression copy-paste below command:

=code.GetColor(Fields!SalesMonth.Value)


Step 4 (Option): You can use this color in your grid report as a background, by copy-paste the below code in Columns Background properties:

=code.GetColor(Fields!SalesMonth.Value)

The output will be simillar to one below:

Create SSRS Report in Web Page using ASP.NET

The following steps help to create an SSRS report in Web Page using ASP.NET. We should install AJAX toolkit to practice the code.

1. Create a new ASP wep page project.

2. Add a new web form.

3. Add the below scripts in the WEB for designer:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SSRS2.aspx.cs" Inherits="SSRS_WEB.WebForm2" %>

<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<table> 

<tr> 

<td>Start Date: </td> 

<td><asp:TextBox Width="180" runat="server" ID="StartDatePr"/></td>

<td><asp:CalendarExtender runat="server" TargetControlID="StartDatePr"/></td>

<td>End Date: </td> 

<td><asp:TextBox Width="180" runat="server" ID="EndDatePr"/></td>

<td><asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="EndDatePr"/></td>

<td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td> 

</tr> 

</table> 

</div>

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

</asp:ToolkitScriptManager>

<rsweb:ReportViewer ID="MyReportViewer" runat="server" Font-Names="Verdana" 

Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote" 

WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="800px" 

Width="1000px">

<ServerReport ReportServerUrl="" />

</rsweb:ReportViewer>

</form>

</body>

</html>

4. Add below code in respective .CS file:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Microsoft.Reporting.Common;

using Microsoft.Reporting.WebForms;

namespace SSRS_WEB

{

public partial class WebForm2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

//First 

MyReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver_Sathya"); // Report Server URL

MyReportViewer.ServerReport.ReportPath = "/SQLSSRS/Dashboard"; // Report Name

MyReportViewer.ShowParameterPrompts = false;

MyReportViewer.ShowPrintButton = false;

ReportParameter[] parameters = new ReportParameter[2];

parameters[0] = new ReportParameter("StartDate", StartDatePr.Text);

parameters[1] = new ReportParameter("EndDate", EndDatePr.Text);

MyReportViewer.ServerReport.SetParameters(parameters);

MyReportViewer.ServerReport.Refresh();

}

}

}

On executing the page, you can see the SSRS reoport created in Web page.

Using CHARINDEX in SQL Query

CHARINDEX returns a starting position of a string which helps developer to split a data based on the requirement.


DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange

Result:

MinRange         MaxRange
    50                      70

Get Hierarchial Level of Employee Using SQL

Create a new table as shown below:

CREATE TABLE [dbo].[Employee](
                    [EmployeeID] [int] NOT NULL,
                    [EmpName] [varchar](100) NULL,
                    [Department] [varchar](100) NULL,
                    [Designation] [varchar](100) NULL,
                    [Salary] [money] NULL,
                    [ManagerID] [int] NULL
    ) ON [PRIMARY]

Insert valuse to the table as required and apply the below script:

SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
    ,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID

Below is the result:

GPG File encryption Commands

GPG File encryption Commands

First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system:

TO ENCRYPT FILE

C:\Program Files (x86)\GNU\GnuPG>gpg -e -r "name" filepath

Here 'name' represents the username obtained while generating GPG key.

E.g., C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust  --encrypt --recipient Babukannan C:\Users\ Babukannan \Desktop\XML\FOTF.XML

TO DECRYPT FILE

 C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust --passphrase xxxxxx --output C:\Users\ Babukannan  \Desktop\XML\FOTFB.XML -d C:\Users\ Babukannan  \Desktop\XML\FOTF.XML.GPG

Here 'xxxxx' represents the password obtained while generating GPG key.


TO GENERATE GPG KEY

Follow the steps in the below image:

TO VIEW THE GPG KEYS

C:\Program Files (x86)\GNU\GnuPG>gpg --List-keys


TO EDIT THE GPG KEYS

C:\Program Files (x86)\GNU\GnuPG>gpg --Edit-keys

GPG File Encryption in SSIS

Using GPG File Encryption in SSIS

Step 1: First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system.

Step 2: Create a new ssis package.

Step 3: Add ExecuteProcessTask. 

Step 4: To encrypt a file, configure Execute Process task as shown below:

Executables: C:\Program Files (x86)\GNU\GnuPG\gpg.exe

Arguments: --yes --always-trust  --recipient Masterworks --output  Filename.GPG --encrypt Filename.txt
WorkingDirectory: D:\File Path....

Step 4A: To decrypt a file, add below code in arguments:

--yes --always-trust --passphrase xxxxxx --output Filename.txt -d Filenname.GPG

Enter your GPG password in place of xxxxxxx .

Scenario - Updating a column based on the other column's value using Dense_Rank function

Requirement:

There should only be one row for any given TransactionId that has a 1 in the ResponseCount. All other rows should have a 0.

In cases where a response is split among several rows, add a 1 to the row based on the transaction type hierarchy below:
1) Donation
2) Subscription
3) Product
4) Event
5) Non Cash
6) Communcation

The top ranking row will get a 1 while the other(s) will get a 0.

In cases with only one row, it will of course get a 1

If there are multiple rows within any of the 1-6 categories above, select the highest dollar row first to get the ResponseCount 1 value, else select the first row.

Solution:

Create a table "dbo.TransactionDetailGiftTest" as shown below:


CREATE TABLE [dbo].[TransactionDetailGiftTest](
[TransactionHistoryKey] [int] NOT NULL,
[DSTransactionId] [int] NOT NULL,
[Type] [varchar](20) NOT NULL,
[TransactionDate] [date] NULL,
[FiscalYear] [int] NULL,
[FiscalMonth] [int] NULL,
[TotalAmount] [money] NULL,
[ResponseCount] [int] NULL,
 CONSTRAINT [PK_TransactionDetailGiftTest] PRIMARY KEY CLUSTERED
(
[TransactionHistoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Also create a Dimenstion table "dbo.dim" for type hierarchy 

CREATE TABLE [dbo].[Dim](

[ResponseCountKey] [int] IDENTITY(1,1) NOT NULL,

[Type] [varchar](20) NOT NULL,

 CONSTRAINT [PK_Dim] PRIMARY KEY CLUSTERED 

(

[ResponseCountKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now write the below mention script in order to achieve the result

WITH CTE AS (SELECT Responsecount, DENSE_RANK()

OVER (PARTITION BY dstransactionid ORDER BY td.responsecountkey, totalamount DESC) AS Result

FROM dbo.TransactionDetailGiftTest tt

INNER JOIN dbo.dim td on tt.[Type] = td.[type])

UPDATE CTE

SET ResponseCount = Result

UPDATE dbo.TransactionDetailGiftTest

SET ResponseCount = 0

WHERE responsecount > 1 

Now the table should be updated as required: Run the query to check the table:

SELECT * FROM dbo.TransactionDetailGiftTest

This scenario helps to understand a logic for the similar kind of requirement.

Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.