tag:blogger.com,1999:blog-29041923866928434232024-03-27T12:08:14.376+05:30IT Developer ZoneSandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.comBlogger128125tag:blogger.com,1999:blog-2904192386692843423.post-37755285607218600532017-10-28T09:49:00.001+05:302017-12-25T13:26:59.280+05:30STRING_SPLIT function in SQL Server <div dir="ltr" style="text-align: left;" trbidi="on">
<b>STRING_SPLIT</b> function is an in-built table valued introduced in SQL Server 2016. The purpose of the function is to split a delimited value into multiple values based on delimiter and display result in Tabular Form.<br />
<br />
In earlier versions, we have to create our own user defined split function for the same.<br />
Refer this post for earlier version : <a href="http://www.itdeveloperzone.com/2012/07/split-function-in-sql.html" target="_blank"><b>Split Function in SQL</b></a><br />
<br />
<b>Syntax</b><br />
<pre class="brush:sql">STRING_SPLIT (string, separator)
</pre>
<br />
<b>STRING_SPLIT</b> Function takes two parameters<br />
<ul style="text-align: left;">
<li><b>String as Delimited Value:</b> The delimited string value of any character type to be split in multiple values. </li>
<li><b>Delimiter/Separator:</b> The single character delimiter like comma, colon, semicolon, pipe etc. on the basis of which delimited value to be split.</li>
</ul>
<b>Return Type</b><br />
Returns a single-column table with column name as value.<br />
<br />
Let's take an example to split a comma delimited value into multiple values on the basis of delimiter (comma) using <b>STRING_SPLIT</b> function.<br />
<br />
<pre class="brush:sql">SELECT * FROM dbo.string_split('val1,val2,val3', ',')</pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3Btoqey0jCeqdDso49Tw4QNq9zEdtRbhNJ_P6sGf1SEwZDEwdqEaqhG-9aDtU5F0owNHd1BLTfuqL7Z2LD9-5Oc215Rsky5N-c058Zab6dsJft_yEFoiqQmGr5pLSDnIlO9ZoYVHb0VeT/s1600/string_split1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt= '' border="0" data-original-height="123" data-original-width="171" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3Btoqey0jCeqdDso49Tw4QNq9zEdtRbhNJ_P6sGf1SEwZDEwdqEaqhG-9aDtU5F0owNHd1BLTfuqL7Z2LD9-5Oc215Rsky5N-c058Zab6dsJft_yEFoiqQmGr5pLSDnIlO9ZoYVHb0VeT/s1600/string_split1.PNG" /></a></div>
Now let's take another example where we have multiple delimited value stored in a table against an ID and each value needs to split on the basis of delimiter.<br />
<br />
We will use <a href="http://www.itdeveloperzone.com/2013/05/apply-operator-in-sql-server.html" target="_blank"><b>Cross Apply</b></a> clause in the example.<br />
<br />
<pre class="brush:sql">DECLARE @TAB TABLE(
id int, list varchar(100)
)
INSERT INTO @TAB
SELECT 1, 'apple;banana;grapes;orange'
UNION ALL SELECT 2, 'potato;onion;carrot;brinjal'
SELECT * FROM @TAB
SELECT t.id, s.value as val
FROM @TAB t
CROSS APPLY dbo.string_split(t.list, ';') s</pre>
<br />
<div class="mydivimg" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw8u_qRXZkViwQp328x2x7cx6V8U0IytgCuybNFOs3WMfz94keKpXOADf5bnK_OJwxo71H5TFDFSvRzUMJqvwx4nKnAPDQf9mT2keByjm3qDsjmzIzXJMi8Q04M0V327EYl0XUPOolySqq/s1600/split2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt='' border="0" height="255" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw8u_qRXZkViwQp328x2x7cx6V8U0IytgCuybNFOs3WMfz94keKpXOADf5bnK_OJwxo71H5TFDFSvRzUMJqvwx4nKnAPDQf9mT2keByjm3qDsjmzIzXJMi8Q04M0V327EYl0XUPOolySqq/s400/split2.jpg" width="400" /></a></div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0New Delhi, Delhi, India28.6139391 77.20902120000005228.3907261 76.886297700000057 28.8371521 77.531744700000047tag:blogger.com,1999:blog-2904192386692843423.post-85506607080511508672016-07-05T15:46:00.001+05:302017-12-25T13:27:33.209+05:30Map Json to Class C# using Json.Net<div dir="ltr" style="text-align: left;" trbidi="on">
In this post, I am sharing how to use <b>Json.Net</b> to map <b>Json</b> response with <b>class</b> in <b>C#</b>.<br />
<br />
<b>Json.Net</b> is a popular high-performance <b>JSON framework</b> for <b>.NET</b><br />
<br />
<b>Prerequisite: </b><br />
<br />
<b>Json.Net :</b> Install with <b>Nuget Package Manager</b> in <b>Microsoft Visual Studio</b><br />
<br />
<b>Example 1:</b> Using <b>JsonConvert.DeserializeObject</b> Method to map Json response with class<br />
<br />
One way is to use <b>JsonConvert.DeserializeObject</b> Method which automatically map the data from valid Json response to class. However, in this case the structure and hierarchy of the classes must match with the Json response. You can use JSON C# class generator tools online where you have to pass Json response and it will generate C# classes for you.<br />
<br />
<b>Let's assume we have below Json Response</b><br />
<pre class="brush:csharp">{
"response": {
"status": 1,
"httpStatus": 200,
"products": [
{
"id": "1",
"name": "Product-1",
"price": "100",
"image_url": "~/Images/prod1.jpg"
},
{
"id": "2",
"name": "Prod1",
"price": "200",
"image_url": "~/Images/prod2.jpg"
}
],
"errors": [],
"errorMessage": null
}
}
</pre>
<br />
Now let's generate classes using one of the tools online <a href="http://jsonutils.com/" target="_blank"><b>JSON C# Class Generator Tool</b></a><br />
We get below classes for our above Json response.<br />
<br />
<pre class="brush:csharp">public class Product
{
public string id { get; set; }
public string name { get; set; }
public string price { get; set; }
public string image_url { get; set; }
}
public class Response
{
public int status { get; set; }
public int httpStatus { get; set; }
public IList<product&gt products { get; set; }
}
public class Example
{
public Response response { get; set; }
}</pre>
<br />
Now we have classes mapped with Json response. Now use below code to map the data from Json response to classes.<br />
<br />
<pre class="brush:csharp">static void Main(string[] args)
{
List<Product> products = new List<Product>();
Example res = new Example();
res = getResponse();
foreach (Product p in res.response.products)
{
Console.WriteLine(string.Format("Product Id: {0}", p.id));
Console.WriteLine(string.Format("Product Name: {0}", p.name));
Console.WriteLine(string.Format("Product Price: {0}", p.price));
Console.WriteLine(string.Format("Product Image URL: {0}\n", p.image_url));
}
Console.Read();
}
public static Example getResponse()
{
string json = @"{
""response"": {
""status"": 1,
""httpStatus"": 200,
""products"": [
{
""id"": ""1"",
""name"": ""Product-1"",
""price"": ""100"",
""image_url"": ""~/Images/prod1.jpg""
},
{
""id"": ""2"",
""name"": ""Prod1"",
""price"": ""200"",
""image_url"": ""~/Images/prod2.jpg""
}
]
}
}";
return JsonConvert.DeserializeObject<Example>(json);
}</pre>
OUPTUT
<br />
<div class="mydivimg" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim8kM7hQql9fqyagh5h5kY08qTy6scSVsLwkEDz8b37YYBSYEgG25XtXaukRQXVnkQjbYH0TZfuk-HODIYItfdDd7yYjShJ1KWQqj-wqa-Nz1SHLq12kxlm4L0pmhjxhyCrVc3ZuxNgMZ5/s1600/JsonRespone.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt='' border="0" height="242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim8kM7hQql9fqyagh5h5kY08qTy6scSVsLwkEDz8b37YYBSYEgG25XtXaukRQXVnkQjbYH0TZfuk-HODIYItfdDd7yYjShJ1KWQqj-wqa-Nz1SHLq12kxlm4L0pmhjxhyCrVc3ZuxNgMZ5/s320/JsonRespone.PNG" width="320" /></a></div>
<br />
<b>Example 2:</b> Using <b>JObject</b> & <b>JToken</b> classes<br />
<br />
Now let's assume we have below Json response where parent node for the product data is dynamic which is productid.<br />
<br />
<b>Json Response</b><br />
<pre class="brush:csharp">{
"response": {
"status": 1,
"httpStatus": 200,
"products": {
"1": {
"id": "1",
"name": "Product-1",
"price": "100",
"image_url": "~/Images/prod1.jpg"
},
"2": {
"id": "2",
"name": "Prod1",
"price": "200",
"image_url": "~/Images/prod2.jpg"
}
},
"errors": [],
"errorMessage": null
}
}
</pre>
<br />
In this case we would use <b>JObject </b>& <b>JToken</b> to fetch the product data as we would not able to generate the static classes for this scenario as the parent node has dynamic values which would change on run-time.<br />
<br />
<pre class="brush:csharp">static void Main(string[] args)
{
List<product> products = new List<product>();
products = getProductDetails();
foreach (Product p in products)
{
Console.WriteLine(string.Format("Product Id: {0}", p.id));
Console.WriteLine(string.Format("Product Name: {0}", p.name));
Console.WriteLine(string.Format("Product Price: {0}", p.price));
Console.WriteLine(string.Format("Product Image URL: {0}\n", p.image_url));
}
Console.Read();
}
public static List<product> getProductDetails()
{
List<product> products = new List<product>();
Product product = null;
string json = @"{
""response"": {
""status"": 1,
""httpStatus"": 200,
""products"": {
""1"": {
""id"": ""1"",
""name"": ""Product-1"",
""price"": ""100"",
""image_url"": ""~/Images/prod1.jpg""
},
""2"": {
""id"": ""2"",
""name"": ""Prod1"",
""price"": ""200"",
""image_url"": ""~/Images/prod2.jpg""
}
},
""errors"": [],
""errorMessage"": null
}
}";
JObject data = JObject.Parse(json);
JObject Products = (JObject)data["response"]["products"];
foreach (var x in Products)
{
JToken prod = x.Value;
product = new Product();
product.id = prod["id"].ToString();
product.name = prod["name"].ToString();
product.price = prod["price"].ToString();
product.image_url = prod["image_url"].ToString();
products.Add(product);
}
return products;
}</pre>
OUTPUT
<br />
<div class="mydivimg" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim8kM7hQql9fqyagh5h5kY08qTy6scSVsLwkEDz8b37YYBSYEgG25XtXaukRQXVnkQjbYH0TZfuk-HODIYItfdDd7yYjShJ1KWQqj-wqa-Nz1SHLq12kxlm4L0pmhjxhyCrVc3ZuxNgMZ5/s1600/JsonRespone.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt= '' border="0" height="242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim8kM7hQql9fqyagh5h5kY08qTy6scSVsLwkEDz8b37YYBSYEgG25XtXaukRQXVnkQjbYH0TZfuk-HODIYItfdDd7yYjShJ1KWQqj-wqa-Nz1SHLq12kxlm4L0pmhjxhyCrVc3ZuxNgMZ5/s320/JsonRespone.PNG" width="320" /></a></div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-57928999355717855822016-03-30T12:44:00.002+05:302016-04-06T15:23:04.881+05:30Derived Column Transformation in SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Derived Column Transformation </b>in SSIS is used to generate a new column by applying expression on input columns. An expression contains any combination of functions, variables, operators and Input Columns.<br />
<br />
In one of our previous tutorial, <a href="http://www.itdeveloperzone.com/2016/03/conditional-split-in-ssis.html" target="_blank"><b>Conditional Split in SSIS</b></a>, we used an expression in condition where we identify the bank on the basis of first three characters of credit card column. In this post, we would use the same table and will generate a derived column Bank by applying condition and expression on Credit Card Column to identify the bank<br />
<br />
<b>Prerequisites</b><br />
<ul style="text-align: left;">
<li>SQL Server with SSIS</li>
<li>SQL Server Data Tools</li>
<li>SQL Server Management Studio</li>
</ul>
<br />
If you already have exercised any of the articles from this blog<br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/02/import-flat-file-to-sql-server-ssis.html" target="_blank"><b>Import Flat File to SQL Server with SSIS</b></a>,</li>
<li><a href="http://www.itdeveloperzone.com/2016/03/conditional-split-in-ssis.html" target="_blank"><b>Conditional Split in SSIS</b></a></li>
</ul>
You would already have Project SSIS-Tutorials and Credit Card Details Table in your database. You can skip first 2 steps and start with 3rd.<br />
<br />
<b>Step 1: Create Credit Card Details Table in SQL Server Database</b><br />
<br />
Run <b>SQL Server Management Studio</b>, connect to database and run below script to create Credit Card Detail Table<br />
<br />
<!--INFOLINKS_OFF-->
<pre class="brush:sql">CREATE TABLE [dbo].[CreditCardDetails](
[CustomerId] [varchar](50) NULL,
[CreditCardNo] [varchar](50) NULL,
[TansactionType] [char](2) NULL,
[TransactionDate] [datetime] NULL,
[Amount] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CreditCardDetails] ([CustomerId], [CreditCardNo], [TansactionType], [TransactionDate], [Amount]) VALUES
('C00000001', 'SBI000000001', 'DR', '01/01/2016', 2500.00),
('C00000002', 'CAN000000001', 'DR', '01/01/2016', 2800.00),
('C00000001', 'SBI000000001', 'CR', '02/01/2016', 25.00),
('C00000003', 'SBI000000002', 'DR', '02/01/2016', 1485.00),
('C00000004', 'SBI000000003', 'DR', '03/01/2016', 2528.45),
('C00000002', 'CAN000000001', 'CR', '04/01/2016', 14.00),
('C00000003', 'SBI000000002', 'CR', '04/01/2016', 37.13),
('C00000004', 'SBI000000004', 'DR', '05/01/2016', 1000.00),
('C00000005', 'CAN000000002', 'DR', '05/01/2016', 3000.20)</pre>
<!--INFOLINKS_ON-->
<b>Step 2: Run SQL Server Data Tools</b>
<br />
<ul>
<li><a href="http://www.itdeveloperzone.com/2016/03/shared-connection-manager-ssis.html" target="_blank"><b>Create a new project and add a shared Data Connection Manager</b></a></li>
</ul>
<br />
<b>Step 3: </b>Add new <b>package</b> to the <b>project. </b>Name the package <b>Derived-Column.</b>
<br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8aKkeMopOOA2M1BudQkF3t5CZlBpUcxQAMetM1emSwNy6lTaLJFwTOenzpd_KvX1qta9iPrZOVErrh-8zjcFe-QYDRerXC3GSmPH-N1MwrRImvqzEQ8cR1e_-RttkP56Ih8wa9uuXQSHM/s1600/Derived_Column-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Package" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8aKkeMopOOA2M1BudQkF3t5CZlBpUcxQAMetM1emSwNy6lTaLJFwTOenzpd_KvX1qta9iPrZOVErrh-8zjcFe-QYDRerXC3GSmPH-N1MwrRImvqzEQ8cR1e_-RttkP56Ih8wa9uuXQSHM/s1600/Derived_Column-01.PNG" title="Add Package" /></a></div>
<br />
<b>Step 4: </b>Add <b>Data Flow Task</b> to <b>Control Flow Tab</b>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiA_AqNJyDGUouhGQnZfEu1EggOk1Bj3P0tlf3WW-1AljjaMe2aao_uXsmf0yC1m2toTmmGN8FELmuifxNTpczXEv88hYryyK341A2Rl2vLEksNhYTg2do7F6PlFqI1J5nRyDmSQfD5hcs/s1600/Derived_Column-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Data Flow Task" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiA_AqNJyDGUouhGQnZfEu1EggOk1Bj3P0tlf3WW-1AljjaMe2aao_uXsmf0yC1m2toTmmGN8FELmuifxNTpczXEv88hYryyK341A2Rl2vLEksNhYTg2do7F6PlFqI1J5nRyDmSQfD5hcs/s1600/Derived_Column-02.PNG" title="Add Data Flow Task" /></a></div>
<br />
<br />
<li>Double Click <b>Data Flow Task</b> to switch to <b>Data Flow Task Tab.</b></li>
<br />
<b>Step 5: Add and Configure OLE DB Source</b>
<br />
<ul>
<li>Drag and Drop <b>OLE DB Source</b> from <b>SSIS Toolbox</b> to <b>Data Flow Task.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL-9YfJ751QPVbbXUMEY4xTQMiR7lFZ3TBojmF6sMRl1IWa3Ggtk9LPnBcS6Y9XRyYetj-yXCn-oIGSx7YFAxtUoYMYiK_vdplpA7exqrV_2IzyKliz-6XZedIfsaR-teE10-z1X7itySz/s1600/Derived_Column-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add OLE DB Source" border="0" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL-9YfJ751QPVbbXUMEY4xTQMiR7lFZ3TBojmF6sMRl1IWa3Ggtk9LPnBcS6Y9XRyYetj-yXCn-oIGSx7YFAxtUoYMYiK_vdplpA7exqrV_2IzyKliz-6XZedIfsaR-teE10-z1X7itySz/s320/Derived_Column-03.PNG" title="Add OLE DB Source" width="320" /></a></div>
<br />
<ul>
<li>Double Click <b>OLE DB Source </b>will open <b>OLE DB Source Editor</b> window.</li>
<li>Select the <b>Shared Data Connection</b> if not selected we created in <b>Step 2</b>.</li>
<li>Select Table <b>CreditCardDetails.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv74QwE2xZNC7Lipdx2w49kNIi49-XGD1vDp1P0FRsiGQuy-oFh4x8TYobc3QUKFicA839cclpW5z2OJ489OAddeVDRHlNAK0XbSlg0xDgy282DqPYSgGHECX0AQIIoSknDZDOqXgrGoIb/s1600/Conditional_Split-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure OLE DB Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv74QwE2xZNC7Lipdx2w49kNIi49-XGD1vDp1P0FRsiGQuy-oFh4x8TYobc3QUKFicA839cclpW5z2OJ489OAddeVDRHlNAK0XbSlg0xDgy282DqPYSgGHECX0AQIIoSknDZDOqXgrGoIb/s1600/Conditional_Split-04.PNG" title="Configure OLE DB Source" /></a></div>
<b><br /></b>
<b>Step 6: Add and Configure Derived Column</b><br />
<ul>
<li>Add <b>Derived Column </b>to <b>Data Flow Task.</b></li>
<li>Connect <b>OLE DB Source</b> to <b>Derived Column.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizkdVmhTxkMxH9GzrNcMKGgYf3hK1g_6FnlPe1JfdtKDcuwzswK0LmqYzoHL-COKKvpCW0qXxrUojFmieC69A_5xGMuvB2Hi7b6TgSdLK_A3Gp2Y0jxTsOdnLZsHeLjgg8igtD2tstXKov/s1600/Derived_Column-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Derived Column Transformation" border="0" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizkdVmhTxkMxH9GzrNcMKGgYf3hK1g_6FnlPe1JfdtKDcuwzswK0LmqYzoHL-COKKvpCW0qXxrUojFmieC69A_5xGMuvB2Hi7b6TgSdLK_A3Gp2Y0jxTsOdnLZsHeLjgg8igtD2tstXKov/s320/Derived_Column-04.PNG" title="Add Derived Column Transformation" width="320" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>Derived Column</b> to open <b>Conditional Split Transformation Editor.</b></li>
<li>Conditional Split Transformation Editor is divided into 3 sub windows.</li>
<ul>
<li><b>Columns, variables and parameters</b> are used in expression to generate derived column(s).</li>
<li><b>In-built functions</b> are optionally used in expression like we are using <b>LEFT</b> function in the expression </li>
<li><b>Derived Column:</b> Here we configure derived column(s)</li>
<ul>
<li><b>Derived Column Name:</b> Name of the derived column that would be generate. It is similar to Alias Column in T-SQL.</li>
<li><b>Derived Column:</b> You have two option here. Either generate column as a new column or replace the existing one. </li>
<li><b>Expression:</b> Here we write custom expression using columns,variable and in-built function to generate column</li>
<li><b>Data Type:</b> Data Type of the derivied column that would be generated</li>
<li><b>Length:</b> Display Length of the derivied column for non-mumeric columns</li>
<li><b>Precision:</b> Display precision if the data type of column is numeric</li>
<li><b>Scale:</b> Display scale if the columns is decimal/float.</li>
</ul>
</ul>
</ul>
<br />
<ul>
<li>Let's add a derived column <b>Bank Name a</b>s a new column, where we will identify bank on the basis of first three characters of Credit Card Column</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2BkmnOfxWagNxaIA7F7sFw4tT7Bn2BcTD0o1zWWE7I_bRgm0Oz2A0xIWHxhqXZsxSVkLRsfTOwMu1dVv2iv1uyC7I5AfZS0rekHJPdw81kijP3Fy-L8h9wJ_XMGTCXera-AABPQ-EQJvl/s1600/Derived_Column-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Derived Column Transformation" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2BkmnOfxWagNxaIA7F7sFw4tT7Bn2BcTD0o1zWWE7I_bRgm0Oz2A0xIWHxhqXZsxSVkLRsfTOwMu1dVv2iv1uyC7I5AfZS0rekHJPdw81kijP3Fy-L8h9wJ_XMGTCXera-AABPQ-EQJvl/s1600/Derived_Column-05.PNG" title="Configure Derived Column Transformation" /></a></div>
<br />
<b>Step 7:</b> At this step we are done with adding and configuring Derived Column. Now instead of exporting data to some destination we can preview the data with derived column on the run-time.<br />
<ul>
<li>Add <b>Conditional Split</b> to the <b>Data Flow Task</b></li>
<li>Connect <b>Derived Column</b> to Conditional Split</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvxLWMgdZUJFICRkNRtVxxdQY5mr4PiihVCgr28FUI_okmfxNPBBPUuWRoJ3t9Pu8Z2BMTrah4lzGgrFArQEBoN4utDx3Y_7u-KuJ9IgiSsHL5cmJ_3RMK_dNWu1MjqeHRrUeE_AsNygxF/s1600/Derived_Column-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Conditional Split" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvxLWMgdZUJFICRkNRtVxxdQY5mr4PiihVCgr28FUI_okmfxNPBBPUuWRoJ3t9Pu8Z2BMTrah4lzGgrFArQEBoN4utDx3Y_7u-KuJ9IgiSsHL5cmJ_3RMK_dNWu1MjqeHRrUeE_AsNygxF/s1600/Derived_Column-06.PNG" title="Add Conditional Split" /></a></div>
<br />
<ul>
<li>Right click <b>Connector</b> between <b>Derived Column</b> and <b>Conditional Split</b> and click <b>Enable Data Viewer</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7VKE-29XkPNaykjefXDBlRlgYelpcxoHRtgqjKR-npd2JFkRd10gjeTK9uE_t-WGYT32RlYjurvARVX2VbomTZxlydqXsEZo878vCFn6cSqJDH45nUqGTPP8e70IKnBsr_nFydUZGzAzf/s1600/Derived_Column-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="Add Data Viewer" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7VKE-29XkPNaykjefXDBlRlgYelpcxoHRtgqjKR-npd2JFkRd10gjeTK9uE_t-WGYT32RlYjurvARVX2VbomTZxlydqXsEZo878vCFn6cSqJDH45nUqGTPP8e70IKnBsr_nFydUZGzAzf/s1600/Derived_Column-07.PNG" title="Add Data Viewer" /></a></div>
<br />
<b>We are done with creating the package. Let's execute the package preview the data.
</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX4QPTXZlPfgB6HTH0Tgkz7vI8pPJdSS8XZRfaCfVhOAwk2dI7iLPxRSu4EjjIxNJMVN7DH4kYCxU566yMo6VXMolAIuY4-Ca4vRJEqgh3r6wUSnh9CmdddRqM5M4ePa0MU-9ZIoq_CgUz/s1600/Derived_Column-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Preview" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX4QPTXZlPfgB6HTH0Tgkz7vI8pPJdSS8XZRfaCfVhOAwk2dI7iLPxRSu4EjjIxNJMVN7DH4kYCxU566yMo6VXMolAIuY4-Ca4vRJEqgh3r6wUSnh9CmdddRqM5M4ePa0MU-9ZIoq_CgUz/s1600/Derived_Column-08.PNG" title="Data Preview" /></a></div>
<br />
<b>Look at the output. The derived column Bank Name is generated in Data View Window.</b><br />
<br /></div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-1770050625399217242016-03-22T11:53:00.001+05:302017-12-25T13:29:56.002+05:30Data Conversion Transformation in SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Data Conversion Transformation</b> in SSIS is used to covert the data type of a column. It is very important transformation and is frequently used in packages.<br />
<br />
For Example, in a package we import the data from various sources and the columns have <b>X</b> data type but the data type of destination columns have <b>Y</b> data type, in such cases we need Data Conversion Transformation.<br />
<br />
We place <b>Data Conversion Transformation</b> between <b>Source</b> and <b>Destination</b> so that it converts the data type of Source Columns to make it compatible with Destination Columns.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix17lyegmj0pwfByFPAlzvWO8Bpb8EeTEN1_gWYF1pS41LBV7WFsOt2SGz-KOHiga3MiwyAFvpcfMy23s9XHV6361GQjaJ69KVWgF4hNxQWKT6-SZqUqMnrS3qIfO-1qy5xJnrPZj9UUm5/s320/Import+Flat+File+-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Import File" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix17lyegmj0pwfByFPAlzvWO8Bpb8EeTEN1_gWYF1pS41LBV7WFsOt2SGz-KOHiga3MiwyAFvpcfMy23s9XHV6361GQjaJ69KVWgF4hNxQWKT6-SZqUqMnrS3qIfO-1qy5xJnrPZj9UUm5/s1600/Import+Flat+File+-11.PNG" /></a></div>
<br />
<br />
Let's take a real life example where we will import a Flat file to SQL Server and we would require Data Conversion Transformation<br />
<br />
<b><a href="http://www.itdeveloperzone.com/2016/02/import-flat-file-to-sql-server-ssis.html" target="_blank">Import Flat File to SQL Server using SSIS</a></b><br />
<div>
<br />
In the above example, there are two columns Data and Amount which have incompatible data types and we have used Data Conversion Transformation to make them compatible.</div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-46720989223577490322016-03-10T13:07:00.002+05:302023-02-09T18:11:13.193+05:30Conditional Split Transformation in SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
In this article, you will learn about <b>Conditional Split Transformation</b> in SSIS with an example.<br />
<br />
<b>Conditional Split</b> is used to divide the flow of data to more than one destination depending on the condition(s).<br />
<br />
<b>Example</b><br />
We have a table in SQL Server Database which stores Credit Card Details and we will fetch the data and on the basis of Credit Card No. will find out the the Bank of the transaction and will export the credit card details into flat files for each bank separately.<br />
<br />
<b>Prerequisites</b><br />
<ul style="text-align: left;">
<li>SQL Server with SSIS</li>
<li>SQL Server Data Tools</li>
<li>SQL Server Management Studio</li>
</ul>
<b><br /></b>
If you have already exercised any of the below articles from this blog,<br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/02/import-flat-file-to-sql-server-ssis.html" target="_blank"><b>Import Flat File to SQL Server with SSIS</b></a> </li>
<li><a href="http://www.itdeveloperzone.com/2016/03/derived-column-transformation-ssis.html" target="_blank"><b>Derived Column Transformation in SSIS</b></a>, </li>
</ul>
You would already have Project SSIS-Tutorials and Credit Card Details Table in your database. You can skip first 2 steps and start with 3rd..<br />
<br />
<b>Step 1: Create Credit Card Details Table in SQL Server Database</b><br />
<br />
Run <b>SQL Server Management Studio</b>, connect to database and run below script to create Credit Card Detail Table<br />
<!--INFOLINKS_OFF-->
<pre class="brush:sql">CREATE TABLE [dbo].[CreditCardDetails](
[CustomerId] [varchar](50) NULL,
[CreditCardNo] [varchar](50) NULL,
[TansactionType] [char](2) NULL,
[TransactionDate] [datetime] NULL,
[Amount] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CreditCardDetails] ([CustomerId], [CreditCardNo], [TansactionType], [TransactionDate], [Amount]) VALUES
('C00000001', 'SBI000000001', 'DR', '01/01/2016', 2500.00),
('C00000002', 'CAN000000001', 'DR', '01/01/2016', 2800.00),
('C00000001', 'SBI000000001', 'CR', '02/01/2016', 25.00),
('C00000003', 'SBI000000002', 'DR', '02/01/2016', 1485.00),
('C00000004', 'SBI000000003', 'DR', '03/01/2016', 2528.45),
('C00000002', 'CAN000000001', 'CR', '04/01/2016', 14.00),
('C00000003', 'SBI000000002', 'CR', '04/01/2016', 37.13),
('C00000004', 'SBI000000004', 'DR', '05/01/2016', 1000.00),
('C00000005', 'CAN000000002', 'DR', '05/01/2016', 3000.20)
</pre>
<!--INFOLINKS_ON-->
<br />
<b>Step 2: Run SQL Server Data Tools</b><br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/03/shared-connection-manager-ssis.html" target="_blank"><b>Create a new project and add a shared Data Connection Manager</b></a></li>
</ul>
If already created in previous tutorial(s), open the existing project <b>SSIS-Tutorials.</b><br />
<br />
<b>Step 3: </b>Add new <b>package</b> to the <b>project. </b>Name the package <b>ConditionalSplit.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpfjO_BxVxmjiOK-9fP8ZzLY8eyrNz2rQTsv00j7-0d6WAKfhtoHEmS9fSd_rlf3u-F3LN1mezujDpDPNUnCcNC2Umk4ltCO9n0sC-kdNFoST0l58z4ikrBgMSTUzZGK1AYsw7pnkyF_5O/s1600/Conditional_Split-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add New Package" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpfjO_BxVxmjiOK-9fP8ZzLY8eyrNz2rQTsv00j7-0d6WAKfhtoHEmS9fSd_rlf3u-F3LN1mezujDpDPNUnCcNC2Umk4ltCO9n0sC-kdNFoST0l58z4ikrBgMSTUzZGK1AYsw7pnkyF_5O/s1600/Conditional_Split-01.PNG" title="Add New Package" /></a></div>
<br />
<b>Step 4: </b>Add <b>Data Flow Task</b> to <b>Control Flow Tab</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO10Z_ci4krlnVny-2OuTX57EoN7titM1XVL43E3DVLbE71_tpuzL8lii3Y_c3Nhv7IMK-vrRyr3a9Qtuvp2Cs9RekyvUrl1NlbtEzmusSwyMghrWU0KPfjYseEVDgsUUZOO4UW2qnOLcn/s1600/Conditional_Split-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Flow Task" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO10Z_ci4krlnVny-2OuTX57EoN7titM1XVL43E3DVLbE71_tpuzL8lii3Y_c3Nhv7IMK-vrRyr3a9Qtuvp2Cs9RekyvUrl1NlbtEzmusSwyMghrWU0KPfjYseEVDgsUUZOO4UW2qnOLcn/s1600/Conditional_Split-02.PNG" title="Add Data Flow Task" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b style="font-weight: bold;">Data Flow Task</b> to switch to <b>Data Flow Task</b> Tab<b>.</b></li>
</ul>
<br />
<b>Step 5: Add and Configure OLE DB Source</b><br />
<ul style="text-align: left;">
<li>Drag and Drop <b>OLE DB Source</b> from <b>SSIS Toolbox</b> to <b>Data Flow Task.</b></li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuWdk-9KQ_Nz8CWaRx_gNB-f1FOxNk5EGlFJ7ILECqsAf-i5hU5iXpa-LKsohQ7v-HtLx6Gt49zeYu47Ixubc6D94X_IIMK3hfkfaPfnNF2YaIGfs9NhHSUTFdEqVghm4JAw9RAAXNrMmW/s1600/Conditional_Split-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure OLE DB Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuWdk-9KQ_Nz8CWaRx_gNB-f1FOxNk5EGlFJ7ILECqsAf-i5hU5iXpa-LKsohQ7v-HtLx6Gt49zeYu47Ixubc6D94X_IIMK3hfkfaPfnNF2YaIGfs9NhHSUTFdEqVghm4JAw9RAAXNrMmW/s1600/Conditional_Split-03.PNG" title="Add OLE DB Source" /></a></div>
<ul style="text-align: left;">
<li>Double Click <b>OLE DB Source </b>will open <b>OLE DB Source Editor</b> window.</li>
<li>Select the <b>Shared Data Connection</b> if not selected we created in <b>Step 2</b>.</li>
<li>Select Table <b>CreditCardDetails.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv74QwE2xZNC7Lipdx2w49kNIi49-XGD1vDp1P0FRsiGQuy-oFh4x8TYobc3QUKFicA839cclpW5z2OJ489OAddeVDRHlNAK0XbSlg0xDgy282DqPYSgGHECX0AQIIoSknDZDOqXgrGoIb/s1600/Conditional_Split-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure OLE DB Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv74QwE2xZNC7Lipdx2w49kNIi49-XGD1vDp1P0FRsiGQuy-oFh4x8TYobc3QUKFicA839cclpW5z2OJ489OAddeVDRHlNAK0XbSlg0xDgy282DqPYSgGHECX0AQIIoSknDZDOqXgrGoIb/s1600/Conditional_Split-04.PNG" title="Configure OLE DB Source" /></a></div>
<br />
<b>Step 6: Add and Configure Conditional Split</b>
<br />
<ul style="text-align: left;">
<li>Add <b>Conditional Split </b>to <b>Data Flow Task.</b></li>
<li>Connect <b>OLE DB Source</b> to <b>Conditional Split.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJSrZwaP_B02wlOiMCWl-9pbrHEu6PLxCCgsSwyFfN2nuqAhttuiNzP31YnYp-DUMruuumAnBVxw8dQkvXQTvxYZqwiezEYOTV3gRxmmuHoAP0nRGMCmg_Ro1VSx1lqrFEbQVl5n43_A6P/s1600/Conditional_Split-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Conditional Split" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJSrZwaP_B02wlOiMCWl-9pbrHEu6PLxCCgsSwyFfN2nuqAhttuiNzP31YnYp-DUMruuumAnBVxw8dQkvXQTvxYZqwiezEYOTV3gRxmmuHoAP0nRGMCmg_Ro1VSx1lqrFEbQVl5n43_A6P/s1600/Conditional_Split-05.PNG" title="Add Conditional Split" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>Conditional Split</b> to open <b>Conditional Split Transformation Editor.</b></li>
<li>Conditional Split Transformation Editor is divided into 3 sub windows.</li>
</ul>
<ol style="text-align: left;"><ol>
<li><b>Columns, variables and parameters</b> are used in expression to split the flow of data. Here we are using CreditCard Column in our example</li>
<li><b>In-built functions</b> are optionally used in expression like we are using LEFT function in the expression </li>
<li><b>Condition(s)</b> that define how to split the flow of data. In our example we have two conditions where using LEFT function on CreditCard column we are identifying different banks and accordingly splitting the flow of data in different path. You can add more conditions to split more data flow.</li>
</ol>
</ol>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAUh23pCtl_Jz9BrIOlc_NaMle4bGgnXNbW_7X62HTWQfqUuG7OpTFlp4GUI5Xmobr1cQj1e5pMA90iYq7JFnWnNl0IElwQDnVuQcv71Flh54KjnjX7GhjpzXxtKevvl1f3AmTWBADIGFy/s1600/Conditional_Split-050.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Conditional Split" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAUh23pCtl_Jz9BrIOlc_NaMle4bGgnXNbW_7X62HTWQfqUuG7OpTFlp4GUI5Xmobr1cQj1e5pMA90iYq7JFnWnNl0IElwQDnVuQcv71Flh54KjnjX7GhjpzXxtKevvl1f3AmTWBADIGFy/s1600/Conditional_Split-050.PNG" title="Configure Conditional Split" /></a></div>
<br />
<b>Step 7: Add and Configure Flat File Destination(s)</b>
<br />
<ul style="text-align: left;">
<li>Add two <b>Flat File destinations</b> and name them <b>SBI</b> & <b>Canara Bank</b> respectively.</li>
<li>Connect <b>Conditional Split</b> to <b>SBI - Flat File Destination</b>. This will open <b>Input Output Selection</b> window. Select <b>SBI</b> in the <b>Output Drop-down-list</b> and click <b>OK</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwUhuaMwncUyDH0_-PUSJAbR81v5iLFTJVx0J9nQ3QCKuEFELaR25dMu1KcTphKw8U2OJRf9_ATi97Qp-7elqkWWYJOuzh0Qh0qXmR6CVPCkWuM8x_033DfpMBSQgSncK7HIAULzlPZguA/s1600/Conditional_Split-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Flat File Destination" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwUhuaMwncUyDH0_-PUSJAbR81v5iLFTJVx0J9nQ3QCKuEFELaR25dMu1KcTphKw8U2OJRf9_ATi97Qp-7elqkWWYJOuzh0Qh0qXmR6CVPCkWuM8x_033DfpMBSQgSncK7HIAULzlPZguA/s1600/Conditional_Split-06.PNG" title="Add Flat File Destination" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>SBI - Flat File Destination</b> to open <b>Flat File Destination Editor.</b></li>
<li>Click <b>New</b> will open <b>Flat File Format window. Delimited</b> will be selected by default<b>. </b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpMQs2gT3FKDZ1asD_VtTNFv2L4_4IcesuBJPhms9JaJxCuKDpLpVrtT5VhZOhvBwDbfoZaOf6s13LGUuDJ2wI0SBZ_JXpRCAM6q17H9X0HNCrsb6Pe7vamc0fAaggMzKEMtWRXRhLAhiY/s1600/Conditional_Split-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Flat File Destination" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpMQs2gT3FKDZ1asD_VtTNFv2L4_4IcesuBJPhms9JaJxCuKDpLpVrtT5VhZOhvBwDbfoZaOf6s13LGUuDJ2wI0SBZ_JXpRCAM6q17H9X0HNCrsb6Pe7vamc0fAaggMzKEMtWRXRhLAhiY/s1600/Conditional_Split-07.PNG" title="Configure Flat File Destination" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>OK</b> will close the <b>Flat File Window</b> and will open <b>Flat File Connection Manager Editor window. </b>Click <b>browse</b> to set the <b>Destination Path</b> of the <b>Flat File. </b>Set the File Name as <b>SBI</b> and click <b>Open.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyC06srRhW8Rgxo21roFBhk9RUqIMfIk2YDgZanQoHKMAEjQKAdl6xG8RC814HtgHIIoNruA5SDdf8e3JANvVkv4WV6RgGUJQF829jlrhVrZ5KGVp0pWipWFKN1p3eYXsylL8foxJyaCMs/s1600/Conditional_Split-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Browse File Path" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyC06srRhW8Rgxo21roFBhk9RUqIMfIk2YDgZanQoHKMAEjQKAdl6xG8RC814HtgHIIoNruA5SDdf8e3JANvVkv4WV6RgGUJQF829jlrhVrZ5KGVp0pWipWFKN1p3eYXsylL8foxJyaCMs/s1600/Conditional_Split-08.PNG" title="Browse File Path" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>OK</b> to close the <b>Flat File Connection Manager Editor</b> window.</li>
<li>On <b>Flat File Destination Editor</b> switch to <b>Mapping </b>Tab. <b>Input Column</b> will be auto mapped with D<b>estination Column</b>. In case not, configure mapping like below and click <b>OK</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvuYjfFfVmdq0oKDTSFTQrU-4GG1CWTMrEKS1GjZCOZSYB7Y0bOW4jXy3rDXPsK8VyZTY3CN3EGFOU54ZWr5SjRjwOzLeq66RahPyvTDSC-TAh2rmikmHbO72HDqU4zoyLFQuEL679bvL7/s1600/Conditional_Split-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Mapping" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvuYjfFfVmdq0oKDTSFTQrU-4GG1CWTMrEKS1GjZCOZSYB7Y0bOW4jXy3rDXPsK8VyZTY3CN3EGFOU54ZWr5SjRjwOzLeq66RahPyvTDSC-TAh2rmikmHbO72HDqU4zoyLFQuEL679bvL7/s1600/Conditional_Split-09.PNG" title="Configure Mapping" /></a></div>
<br />
<ul style="text-align: left;">
<li>Repeat the Steps for <b>Canara - Flat File Destination</b> similar to what we have performed for <b>SBI - Flat File Destination</b>. Select Canara as Output and file name</li>
<li>In <b>Flat File Manager Editor</b>, <b>Flat file Connection Manager</b> will be selected which we created for <b>SBI - Flat File Destination</b>. Click on New to create <b>New Flat File Connection</b> for <b>Canara - Flat File Destination.</b></li>
</ul>
<br />
At this stage we are done with creating the package. Package looks like below<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV_0G9SialkbWRu0wManfcKmAkXe3bL2ak7eQqJoNeC_zOeMCRR8CltXvpV2KdWer8zFmjr7GJgCV0-bcbeWss0sJaUESq0AZ8coisF1eH1Tr2pvUm8DAfoghzdMFhFbDMqAS89U5awtro/s1600/Conditional_Split-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Package" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV_0G9SialkbWRu0wManfcKmAkXe3bL2ak7eQqJoNeC_zOeMCRR8CltXvpV2KdWer8zFmjr7GJgCV0-bcbeWss0sJaUESq0AZ8coisF1eH1Tr2pvUm8DAfoghzdMFhFbDMqAS89U5awtro/s1600/Conditional_Split-10.PNG" title="Package" /></a></div>
<br />
Now let's execute the package.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPCyON05L-YnPcsIWPe1CZrFzZr_0DT-imlLKPYWA8a81fdBknAzpm2UN6MYr5zntBOlPg_r3B4ctyYgj6-Cs_h8apYL2J7F4-SS0NnUAtG15_krFYWVUxCH4KoI_3wwoF50h8X55GrV2-/s1600/Conditional_Split-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Package Execution" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPCyON05L-YnPcsIWPe1CZrFzZr_0DT-imlLKPYWA8a81fdBknAzpm2UN6MYr5zntBOlPg_r3B4ctyYgj6-Cs_h8apYL2J7F4-SS0NnUAtG15_krFYWVUxCH4KoI_3wwoF50h8X55GrV2-/s1600/Conditional_Split-11.PNG" title="Package Execution" /></a></div>
<br />
Package execute successfully. You can clearly view in the execution flow, 9 rows imported from the table and conditional split transformation divided the flow of data to 6 and 3 rows on different paths depending on condition.<br />
<br />
Now let's browse the path and check the files and data<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNSdOHBRNEHQQTK9TkGUwgoWz__tkmP0qQaTLo1N7ZqLsZj08i_-tCNyei4I8Km0cKWjozFU5G7aEPmoASXk5LLus5MVE2yR1CD-Q2Zo8uBkyfzxya87prtoU-GJFzoQHx8TvNhaVleLGN/s1600/Conditional_Split-12.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="File Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNSdOHBRNEHQQTK9TkGUwgoWz__tkmP0qQaTLo1N7ZqLsZj08i_-tCNyei4I8Km0cKWjozFU5G7aEPmoASXk5LLus5MVE2yR1CD-Q2Zo8uBkyfzxya87prtoU-GJFzoQHx8TvNhaVleLGN/s1600/Conditional_Split-12.PNG" title="File Output" /></a></div>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-70745314223305819382016-03-05T13:10:00.002+05:302022-10-19T20:37:50.396+05:30Aggregate Transformation in SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
In this tutorial, you will learn about <b>Aggregate Transformation</b> in SSIS with an Example.<br />
<b><br /></b>
<b>Aggregate Transformation</b> is used in <b>Data Flow Task </b>to aggregate the data like Sum, Max, Min, Avg etc.<br />
<br />
<b>Example</b><br />
We have a <b>Table</b> in <b>SQL Server Database</b> which stores Employees Salary. We will first create an <a href="http://www.itdeveloperzone.com/2016/03/shared-connection-manager-ssis.html" target="_blank"><b>OLE DB Connection</b></a> to fetch the data from the database and will perform <b>Aggregate Transformation</b> on the data to calculate Sum, Max & Min salary for each department and Aggregated data will be then exported to a <b>Flat file with pipe {|} delimiter</b>.<br />
<b><br /></b>
<b>Prerequisites</b><br />
<ul style="text-align: left;">
<li>SQL Server with SSIS</li>
<li>SQL Server Data Tools</li>
<li>SQL Server Management Studio</li>
</ul>
<b><br /></b>
<b>Step 1:</b> Run <b>SQL Server Management Studio</b>, connect to database and run below script to create Employee Salary Table with some data<br />
<!--INFOLINKS_OFF-->
<pre class="brush:sql">create table EmpSalary(
EmpId char(6),
DeptId int,
Salary numeric(18,2)
)
insert into EmpSalary values
('EMP001',1,50000),
('EMP002',2,40000),
('EMP003',2,25000),
('EMP004',2,20000),
('EMP005',3,30000),
('EMP006',3,13000),
('EMP007',4,23000),
('EMP008',5,17000)</pre>
<!--INFOLINKS_ON-->
<br />
<b>Step 2: Run SQL Server Data Tools</b><br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/03/shared-connection-manager-ssis.html" target="_blank"><b>Create a new project and add a shared Data Connection Manager</b></a></li>
</ul>
If already created in previous tutorial(s), open the existing project <b>SSIS-Tutorials.</b><br />
<b><br /></b>
<b>Step 3: </b>Add new <b>package</b> to the <b>project. </b>Name the package <b>Aggregate.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7mv5J2IskTIuf99N_qhyphenhyphenhzrH7ZqwRb9blucfRvDXsASY4t7C9tq7WPYByZYP3vQ9ZgVWDwmfBKhqNXSWoQPpxPyQk9vPN0OjEB706BTZ4iZTsnqHmLD3UzarP7WKq_ju7gZQPfzW8lQnX/s1600/Aggregate-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="New Package" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7mv5J2IskTIuf99N_qhyphenhyphenhzrH7ZqwRb9blucfRvDXsASY4t7C9tq7WPYByZYP3vQ9ZgVWDwmfBKhqNXSWoQPpxPyQk9vPN0OjEB706BTZ4iZTsnqHmLD3UzarP7WKq_ju7gZQPfzW8lQnX/s1600/Aggregate-01.PNG" title="New Package" /></a></div>
<br />
<b>Step 4: </b>Add <b>Data Flow Task</b> to <b>Control Flow Tab</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAE7dghyphenhyphen5WhT3L9G_EZAaLKyTVweTukX_eIM7YCrDmMhYWblvLwv60D5tFpro5vMBriJD2D1Ekpjq47lsqyWUkbLrPKcuqVDpyOHaLAgv_6EZ_Ut7cswexXmzA_BY-Z64knN0TO3ITxK-a/s1600/Aggregate-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Flow Task" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAE7dghyphenhyphen5WhT3L9G_EZAaLKyTVweTukX_eIM7YCrDmMhYWblvLwv60D5tFpro5vMBriJD2D1Ekpjq47lsqyWUkbLrPKcuqVDpyOHaLAgv_6EZ_Ut7cswexXmzA_BY-Z64knN0TO3ITxK-a/s1600/Aggregate-02.PNG" title="Add Data Flow Task" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b style="font-weight: bold;">Data Flow Task</b> to switch to <b>Data Flow Task Tab.</b></li>
</ul>
<br />
<b>Step 5: Add and Configure OLE DB Source</b><br />
<ul style="text-align: left;">
<li>Add <b>OLE DB Source.</b></li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwpFEGsoGhjqBDh1uM9h01bJh0RDBM3JbBkQsuSNlEUoYE6AyCCK7PoFBzh6yfvyZQNkYGnGkPC7_bm2g2a_fCsL4RC52dkWmaDPrLceSH7ln8un45_fiCerqDmg25uRkKK9U10mWrp5pU/s1600/Aggregate-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="OLE DB Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwpFEGsoGhjqBDh1uM9h01bJh0RDBM3JbBkQsuSNlEUoYE6AyCCK7PoFBzh6yfvyZQNkYGnGkPC7_bm2g2a_fCsL4RC52dkWmaDPrLceSH7ln8un45_fiCerqDmg25uRkKK9U10mWrp5pU/s1600/Aggregate-03.PNG" title="Add and Configure OLE DB Source" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>OLE DB Source </b>will open <b>OLE DB Source Editor</b> window.</li>
<li>Select the <b>Shared Data Connection</b> if not selected we created in <b>Step 2</b>.</li>
<li>Select Table <b>EmpSalary.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-gGZg3khmxdis99J1bNzrhDvMvUcSgfIak4G09o0V1IJHMxHVpa1hDgIWHIUiLsOTAIq-6NKcTxXWrR7BW7iDomNEW_EojbLmR6YtB2MfOllQ4BWac18ANCMtSbmv6ecITNcXGQ0ZlTXV/s1600/Aggregate-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="OLE DB Source Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-gGZg3khmxdis99J1bNzrhDvMvUcSgfIak4G09o0V1IJHMxHVpa1hDgIWHIUiLsOTAIq-6NKcTxXWrR7BW7iDomNEW_EojbLmR6YtB2MfOllQ4BWac18ANCMtSbmv6ecITNcXGQ0ZlTXV/s1600/Aggregate-04.PNG" title="Configure OLE DB Source Editor" /></a></div>
<b><br /></b>
<b>Step 6: Add and Configure Aggregate Transformation</b>
<br />
<ul style="text-align: left;">
<li>Add <b>Aggregate </b>to <b>Data Flow Task.</b></li>
<li>Connect <b>OLE DB Source</b> to <b>Aggregate.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjptdgmtFr3t-O59gNOidbjRphqjrWiOTCnhgrHwXjdyT8ycFAmo-dyLO2X6cnly0xnOj6z6nM81_62CgYE4nbNr0IBbGs2C9UpuYisibqNRr2ivYKQLmkEiWiGhYApIaeriWOljAl09g9R/s1600/Aggregate-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Aggregate Transformation" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjptdgmtFr3t-O59gNOidbjRphqjrWiOTCnhgrHwXjdyT8ycFAmo-dyLO2X6cnly0xnOj6z6nM81_62CgYE4nbNr0IBbGs2C9UpuYisibqNRr2ivYKQLmkEiWiGhYApIaeriWOljAl09g9R/s1600/Aggregate-05.PNG" title="Add Aggregate Transformation" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>Aggregate</b> to open <b>Aggregate Transformation Editor</b> and configure the columns like below to find Sum, Max & Min of Salary Department-wise.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-RV5G6TVi459vRaPi4zsiy2fUId6TdQ0mVYS3aZwPhAFIA3Z8AMUB-CZD_wA0_VM_36lgqu2UVohRbLcWCq7qdUuqMmDYPa_Qji265AaZ5q8M_9cJgg49Ghv7RAZMrwepsH75ATSBUOmM/s1600/Aggregate-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Aggregate Transformation" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-RV5G6TVi459vRaPi4zsiy2fUId6TdQ0mVYS3aZwPhAFIA3Z8AMUB-CZD_wA0_VM_36lgqu2UVohRbLcWCq7qdUuqMmDYPa_Qji265AaZ5q8M_9cJgg49Ghv7RAZMrwepsH75ATSBUOmM/s1600/Aggregate-06.PNG" title="Configure Aggregate Transformation" /></a></div>
<br />
<b>Step 7: Add and Configure Flat File Destination</b>
<br />
<ul>
<li>Add <b>Flat File Destination.</b></li>
<li>Connect <b>Aggregate</b> to <b>Flat File Destination.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ9wWrf7B6sI0qZ-Bn-RQ3v7by5YgKPn6R3vrzMIgUdjcbGaVm_nATCacALubLcU8lpXRnRCkMiDxpHpHXvHqbxaW5aoQgg-VEEG0ShQUn5CE8fP1LNJ5vHdltaEMHiuueansSJK7WJnRi/s1600/Aggregate-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Flat File Destination" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ9wWrf7B6sI0qZ-Bn-RQ3v7by5YgKPn6R3vrzMIgUdjcbGaVm_nATCacALubLcU8lpXRnRCkMiDxpHpHXvHqbxaW5aoQgg-VEEG0ShQUn5CE8fP1LNJ5vHdltaEMHiuueansSJK7WJnRi/s1600/Aggregate-07.PNG" title="Add Flat File Destination" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>Flat File Destination</b> to open <b>Flat File Destination Editor.</b></li>
<li>Click <b>New</b> to open a <b>Flat File Format</b> window.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7i5iBdxf8vRt3zm5eIxqwwekziCi2rI-m4dTtakuOVjNpfyFAE-kHzAuOea_Ro373TE1fltCivvZdO2mOCmQ6o1PYNhuFMRgJJy-L_kP_1pLFeNQqeu23V46pTzeprMCEojsCInp4QgLr/s1600/Aggregate-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File Destination Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7i5iBdxf8vRt3zm5eIxqwwekziCi2rI-m4dTtakuOVjNpfyFAE-kHzAuOea_Ro373TE1fltCivvZdO2mOCmQ6o1PYNhuFMRgJJy-L_kP_1pLFeNQqeu23V46pTzeprMCEojsCInp4QgLr/s1600/Aggregate-08.PNG" title="Open Flat File Destination Editor" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b style="font-weight: bold;">OK</b> to open <b>Flat File Connection Manager Editor.</b></li>
<li>Click <b style="font-weight: bold;">Browse</b> to configure the <b>Flat File Path</b> and <b>Name. </b>Browse the path and name <b>DeptWiseSalary</b> in the <b>File Name</b>. File will be automatically created.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit02yvb7asYFGl4LauCbF36fw2GbgysWKy7tlZz4swGSIHEqdeEz5vIkobQ_iLzaL4eyCx8PML97VFxiT-flKXBaqMzsxqTBLKrm_25rYaxCWSf-asUIzx9LgHINVAiGwnDKX_FyxNP5_d/s1600/Aggregate-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File Connection Manager Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit02yvb7asYFGl4LauCbF36fw2GbgysWKy7tlZz4swGSIHEqdeEz5vIkobQ_iLzaL4eyCx8PML97VFxiT-flKXBaqMzsxqTBLKrm_25rYaxCWSf-asUIzx9LgHINVAiGwnDKX_FyxNP5_d/s1600/Aggregate-09.PNG" title="Open Flat File Connection Manager Editor" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>Open</b> to close the <b>Browser</b> window</li>
<li>In <b>Flat File Connection Manager Editor</b> tick the check-box <b>Column names in the first data row</b> to export the column names otherwise data will be exported without the column names</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcgCh5klJHZ0XN4GpVTazfDDDZtTnKVHnssAS2s3G8H7A5oCaJ84TFW5j8Owils5S3ykKmnrrd3xBuJQ4N_O82rebiD0Da4PpmGnXnUno_hIOpz_3YKxXnz_fnnYcQjquhE_tESVycUioR/s1600/Aggregate-090.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File Connection Manager Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcgCh5klJHZ0XN4GpVTazfDDDZtTnKVHnssAS2s3G8H7A5oCaJ84TFW5j8Owils5S3ykKmnrrd3xBuJQ4N_O82rebiD0Da4PpmGnXnUno_hIOpz_3YKxXnz_fnnYcQjquhE_tESVycUioR/s1600/Aggregate-090.PNG" title="Configure Flat File Connection Manager Editor" /></a></div>
<br />
<ul style="text-align: left;">
<li>Switch to <b>Columns Tab </b>on <b>Flat File Connection Manager Editor.</b></li>
<li>Select <b>Vertical Bar {|}</b> as <b>Column Delimiter.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjI9O0oPF0dJN3Lusn1f4fEb-iL7_o4mlEGmKghuHZckVnPnOkYOpvyENhSRQ5Yfp3gLVSyj7MzO3fUYi5w2Bh_8u4IoFrj6cQtvgPDNI6IW39RSWd02y937f1pW16LVW0n5Ic8XXoElzk0/s1600/Aggregate-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File Connection Manager Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjI9O0oPF0dJN3Lusn1f4fEb-iL7_o4mlEGmKghuHZckVnPnOkYOpvyENhSRQ5Yfp3gLVSyj7MzO3fUYi5w2Bh_8u4IoFrj6cQtvgPDNI6IW39RSWd02y937f1pW16LVW0n5Ic8XXoElzk0/s1600/Aggregate-10.PNG" title="Switch to Columns Tab on Flat File Connection Manager Editor" /></a></div>
<br/>
<ul style="text-align: left;">
<li>Click <b style="font-weight: bold;">OK</b> to close <b>Flat File Connection Manager Editor.</b></li>
<li>Switch to <b style="font-weight: bold;">Mappings Tab</b> on <b>Flat File Destination Editor</b> and configure like below.</li>
</ul>
<br/>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguy67-D0ibuRbcwDl_HfxOJAIeC4Z68gvjdOul0RgbFYt_FNOESNsCPqmsbXz9zJgtY1yfZ8CqhKm5Qjwf0jGWz0Mn_HgcI6SvvJM57V9QZwPCMRv1sfqkumc9rO9gAeRAESPrxUXxgaPw/s1600/Aggregate-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Mapping" border="0" height="277" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguy67-D0ibuRbcwDl_HfxOJAIeC4Z68gvjdOul0RgbFYt_FNOESNsCPqmsbXz9zJgtY1yfZ8CqhKm5Qjwf0jGWz0Mn_HgcI6SvvJM57V9QZwPCMRv1sfqkumc9rO9gAeRAESPrxUXxgaPw/s320/Aggregate-11.PNG" title="Configure Mapping" width="320" /></a></div>
<br/>
<ul style="text-align: left;">
<li>Click <b style="font-weight: bold;">OK</b> to close <b>Flat File Destination Editor</b><b>. </b></li>
</ul>
<br />
<b>At this step we are done with creating the package. Let's execute the package.</b><br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0pD0fSKK8xCwo_o79r05DgDb0_TQHjKzLMxf4d8bx3noEewjq-kGuoiKPPKOm32wWJRd-aP1XeHGet2DkoTpraisJHK_1M8ZoKtGOxz0a7beOkjS3nKIYKoYFWQTmczvGVU9p7_0yNtvF/s1600/Aggregate-12.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Execute Package" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0pD0fSKK8xCwo_o79r05DgDb0_TQHjKzLMxf4d8bx3noEewjq-kGuoiKPPKOm32wWJRd-aP1XeHGet2DkoTpraisJHK_1M8ZoKtGOxz0a7beOkjS3nKIYKoYFWQTmczvGVU9p7_0yNtvF/s1600/Aggregate-12.PNG" title="Execute Package" /></a></div>
<br />
<b>Package</b> executed successfully<b>. </b>Now let's browse the <b>Flat File Path</b> and check if file is created<b>.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf6sqFcurQjqFBpjouwWyD5Owmekq78AgufY6amZlj4EoggdD_h6fBuPPgncLRG9b0nv81nE31StQVr6BVJlGoCSJ8K5Zulo0GUjdnbxBF1LMOLAXoG_B0ng07hD7fvBfdLMwhofZdTOxE/s1600/Aggregate-13.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="File Path" border="0" height="169" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf6sqFcurQjqFBpjouwWyD5Owmekq78AgufY6amZlj4EoggdD_h6fBuPPgncLRG9b0nv81nE31StQVr6BVJlGoCSJ8K5Zulo0GUjdnbxBF1LMOLAXoG_B0ng07hD7fvBfdLMwhofZdTOxE/s320/Aggregate-13.PNG" title="Browse File Path" width="320" /></a></div>
<br />
Now let's Open the File and check if data is exported.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwpSpHVfaqQd0CqYfzKQBOmYyAETN7wetMnqxMRxobZ1_N9a6ma67sv0yt-waNa8M8tdh3obItXer6sGCcjD5z4H4x6OyqHwgzKmltOk3dWh9SD_Ot1yVFSHOIO4wlqm6MTCZTSwCFRAbE/s1600/Aggregate-14.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Output" border="0" height="175" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwpSpHVfaqQd0CqYfzKQBOmYyAETN7wetMnqxMRxobZ1_N9a6ma67sv0yt-waNa8M8tdh3obItXer6sGCcjD5z4H4x6OyqHwgzKmltOk3dWh9SD_Ot1yVFSHOIO4wlqm6MTCZTSwCFRAbE/s320/Aggregate-14.PNG" title="Data Exported to the Flat File" width="320" /></a></div>
</div>
<br/>
<a href="https://Contactmeasap.com" target="_blank">Contactmeasap</a>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-64814102993027456882016-03-04T12:07:00.003+05:302016-03-24T12:02:19.512+05:30Shared Connection Manager SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Connection Manager</b> in SSIS is used to connect to various data sources such as Relational Databases, Analysis Services Databases, Flat Files and files in CSV and XML formats.<br />
<br />
A Connection Manager can be created at<br />
<ul style="text-align: left;">
<li><b>Project Level:</b> Available to all the packages. That is why it is also called as shared Connection Manager. </li>
<li><b>Package Level:</b> Available to the specific package</li>
</ul>
<br />
In Real Life Projects,<br />
<ul style="text-align: left;">
<li>We create connection manager at project level when we have to connect to same data source for various packages such as OLE DB Connection Manager. We use OLE DB connection Manager to connect to SQL Server which is generally required to connect in various packages. </li>
<li>We create connection manager at package level when we have to connect to a particular data source for a specific package such as Flat File Connection Manager. We use Flat File Connection Manager to import/export data to a flat file which is generally specific to a particular package.</li>
</ul>
<br />
Refer this tutorial where we have used both project/package level Connection Manager. Project level to connect to SQL Server Database and package level to import the data from a flat file.<br />
<br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/02/import-flat-file-to-sql-server-ssis.html" target="_blank"><b>Import flat file to SQL Server</b></a></li>
</ul>
<br />
In this post, we will see how to create a <b>Shared Connection Manager</b> i.e. Connection Manager at Project Level. we will create <b>OLE DB Connection Manager</b> to connect to SQL Server and we will use the same in various packages that we will create/created in various tutorials on the blog.<br />
<br />
<b>Step 1: </b>Run <b>SQL Server Data Tools</b>. Create a New<b> SSIS Project</b> SSIS-Tutorials.<br />
<br />
<b>Step 2:</b> Right Click <b>Connection Managers</b> in the Solution Explorer and <b>Add New Connection Manager</b><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEierHde5vuUhsaNZUgwOfgmw7RCFTPhRCYqTv2JpP1n6lzlOmJNn6SvyAdESwMPZCHE71A7Ax8lw7iL8nUG-1xUVT4ZxSkriFlpp8QbcP9v2NNmBJrSi5DBZUxt-lhvNFVuaQfXiHlQ81Yb/s1600/Con+Mgr+-+01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEierHde5vuUhsaNZUgwOfgmw7RCFTPhRCYqTv2JpP1n6lzlOmJNn6SvyAdESwMPZCHE71A7Ax8lw7iL8nUG-1xUVT4ZxSkriFlpp8QbcP9v2NNmBJrSi5DBZUxt-lhvNFVuaQfXiHlQ81Yb/s1600/Con+Mgr+-+01.PNG" /></a></div>
<br />
This will open <b>SSIS Connection Manager</b>. Select <b>OLEDB</b> from the available sources and click Add<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5yQWFzzU1L8T6gkh100d889-067g5KziFYGR2FKx3zuTdArhxqwIW0xZdk1A4UIeeZSwRwUNafI3ogj8H_9p9MmpQ4QoO2gqT5jS4lDS7DMAjcd61K6_5O9bP0dPzHbveWOLMUTdK75rN/s1600/Con+Mgr+-+02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5yQWFzzU1L8T6gkh100d889-067g5KziFYGR2FKx3zuTdArhxqwIW0xZdk1A4UIeeZSwRwUNafI3ogj8H_9p9MmpQ4QoO2gqT5jS4lDS7DMAjcd61K6_5O9bP0dPzHbveWOLMUTdK75rN/s1600/Con+Mgr+-+02.PNG" /></a></div>
<br />
This will open <b>Configure OLE DB Connection Manager</b> Window. Click <b>New</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDA_mDGKxaBZTEZsl4XvSyk1Kz9KrcXR_JCIFPOHk1bNzpmeM0c_XZGVc62j9yLSEEaPI2k_20couAB8qsoK3KqaRvxpe7JJF1-nMqZ0PxfEDmdQqtD48gAU_g86I7gLLKBHQvlzXSN6dj/s1600/Con+Mgr+-+03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDA_mDGKxaBZTEZsl4XvSyk1Kz9KrcXR_JCIFPOHk1bNzpmeM0c_XZGVc62j9yLSEEaPI2k_20couAB8qsoK3KqaRvxpe7JJF1-nMqZ0PxfEDmdQqtD48gAU_g86I7gLLKBHQvlzXSN6dj/s1600/Con+Mgr+-+03.PNG" /></a></div>
<br />
This will open <b>Connection Manager</b> window. Select SQL Server and provide Authentication to logon to SQL Server and select Database and click Ok<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9wuHxiWMXuPYOEAlB1euPGJ0LvFxLUs3BdIz1aZJXNR6lLBSGzW7SmNpkYxco26rcseC9HR2knk1ZMdpatwayRbrUTWNxYM0BUk60Yc9Po-aUYqSpEcS2jdFyCCdTR0fG2ln2roxs7973/s1600/Con+Mgr+-+04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9wuHxiWMXuPYOEAlB1euPGJ0LvFxLUs3BdIz1aZJXNR6lLBSGzW7SmNpkYxco26rcseC9HR2knk1ZMdpatwayRbrUTWNxYM0BUk60Yc9Po-aUYqSpEcS2jdFyCCdTR0fG2ln2roxs7973/s1600/Con+Mgr+-+04.PNG" /></a></div>
<br />
At this step we are done with creating the shared Connection Manager which would start appearing under <b>Connection Managers</b> like below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0JTMPkf_itE3QWZXvU17MK6VIGRDHU5a1LEOGQUxe196uuDd_scsVZ6HUWcV0Ocm2paRvJKVLKCgvNaqujesaQvNBMk6OlMzziJVN9JA3BRvsO2tDBQuDH25-R67elIXMw7VxBAmMjrQ7/s1600/Con+Mgr+-+05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0JTMPkf_itE3QWZXvU17MK6VIGRDHU5a1LEOGQUxe196uuDd_scsVZ6HUWcV0Ocm2paRvJKVLKCgvNaqujesaQvNBMk6OlMzziJVN9JA3BRvsO2tDBQuDH25-R67elIXMw7VxBAmMjrQ7/s1600/Con+Mgr+-+05.PNG" /></a></div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-56167971713524549682016-02-27T11:02:00.000+05:302016-04-06T15:24:41.404+05:30Import Flat File to SQL Server with SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
In this tutorial, you will learn how to import a Flat File data to SQL Server Database using SSIS.<br />
<br />
<b>Example:</b><br />
We have a Text File which contains Credit Card Details with Pipe Delimiter and we will import this data in SQL Server Database Table.<br />
<br />
<b>Prerequisite:</b><br />
<ul style="text-align: left;">
<li>SQL Server 2012 with SSIS installed:</li>
<li>SQL Server Data Tools</li>
<li>SQL Server Management Studio</li>
<li>Text file with Credit Card Details.</li>
</ul>
<b><br /></b><b>Step 1: </b>Open <b>Notepad</b> and copy the below Credit Card Details to the notepad file and save the file as <b>CreditCardDetails.txt</b><br />
<div calss="mydivimg">CustomerId|CreditCardNo|TansactionType|Date|Amount
C00000001|SBI000000001|DR|1/1/2016|2500.00
C00000002|CAN000000001|DR|1/1/2016|2800.00
C00000001|SBI000000001|CR|2/1/2016|25.00
C00000003|SBI000000002|DR|2/1/2016|1485.00
C00000004|SBI000000003|DR|3/1/2016|2528.45
C00000002|CAN000000001|CR|4/1/2016|14.00
C00000003|SBI000000002|CR|4/1/2016|37.13
C00000004|SBI000000004|DR|5/1/2016|1000.00
C00000005|CAN000000002|DR|5/1/2016|3000.20</div>
<b><br />Step 2: </b>Run <b>SQL Server Management Studio, </b> connect with database and run below script to create Credit Card Detail Table.<br />
<!--INFOLINKS_OFF-->
<pre class="brush:sql">CREATE TABLE CreditCardDetails(
CustomerId VARCHAR(50),
CreditCardNo VARCHAR(50),
TansactionType CHAR(2),
TransactionDate DATETIME,
Amount NUMERIC(18,2)
)</pre>
<br />
<!--INFOLINKS_ON-->
<b>Step 3: </b>Run <b>SQL Server Data Tools</b>.<br />
<ul style="text-align: left;">
<li><a href="http://www.itdeveloperzone.com/2016/03/shared-connection-manager-ssis.html" style="font-weight: bold;" target="_blank">Create a new Project and a shared Data Connection Manager</a></li>
</ul>
If already created in previous tutorial(s), open the existing project <b>SSIS-Tutorials.</b><br />
<b><br /></b>
<b>Step 4:</b> Add <b>new package </b>to the Project. Name the package <b>ImportFlatFile</b><br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieEgZQFGZ69QM7b74pIb91oW9GEgp2QL6e4eiSdotQLn2Jx54ArNSTjUI57b8lB4uWr5GEOAxeRVzXuQrchVUlNUF0a7L_UAQhypI6uY3NSMwzmB8trXlcMY7LK3r8lUWP-xqpFlMBqD5M/s1600/Import+Flat+File+-+02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieEgZQFGZ69QM7b74pIb91oW9GEgp2QL6e4eiSdotQLn2Jx54ArNSTjUI57b8lB4uWr5GEOAxeRVzXuQrchVUlNUF0a7L_UAQhypI6uY3NSMwzmB8trXlcMY7LK3r8lUWP-xqpFlMBqD5M/s1600/Import+Flat+File+-+02.PNG" /></a></div>
<b><br /></b>
<b>Step 5: </b>Add <b>Data Flow Task </b>to<b> Control Flow</b> Tab<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjETQbDGeO_k1WXu4hWNkrFDYX3fTcuuhlx0Lynx9-0JKKWoUpTZ168e3ndu5RP2IGqCT8nJgsdgJpl9Hk5qrsjwOFanznR29Hv9ekunc7neWSrDwUTFcfw0fKw6Zq5Swj0Skx94u9qjrsv/s1600/Import+Flat+File+-+03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Flow Task" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjETQbDGeO_k1WXu4hWNkrFDYX3fTcuuhlx0Lynx9-0JKKWoUpTZ168e3ndu5RP2IGqCT8nJgsdgJpl9Hk5qrsjwOFanznR29Hv9ekunc7neWSrDwUTFcfw0fKw6Zq5Swj0Skx94u9qjrsv/s1600/Import+Flat+File+-+03.PNG" title="Add Data Flow Task to Control Flow" /></a></div>
<br />
Double Click <b>Data Flow Task</b> to switch to <b>Data Flow Tab.</b><br />
<b><br /></b>
<b>Step 6: Add and Configure Flat File Source</b><br />
<ul style="text-align: left;">
<li>Add <b>Flat File Source </b>to the <b>Data Flow Task </b>from the <b>Toolbox</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfI7p4pNQX93CWxwWoB6V11ZsqqHvr1InKV1y5ZU3gDR5Pcz9vFdSBNTo3VX6iwe2itggg47U7bfMeSJgTtOIS6vtDVwxri1t_Fqoxadt2cj46dCTAMMxgAh-IrxNYSsHVc3t4lFElXNCz/s1600/Import+Flat+File+-+04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfI7p4pNQX93CWxwWoB6V11ZsqqHvr1InKV1y5ZU3gDR5Pcz9vFdSBNTo3VX6iwe2itggg47U7bfMeSJgTtOIS6vtDVwxri1t_Fqoxadt2cj46dCTAMMxgAh-IrxNYSsHVc3t4lFElXNCz/s1600/Import+Flat+File+-+04.PNG" title="Add Flat File Source" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click on <b>Flat File Source. </b>This will open a pop up window <b>Flat File Source Editor</b>.</li>
<li>Click New Button will pop up another window<b> Flat File Connection Manager Editor.</b></li>
<li>In <b>General </b>Tab<b> </b>Browse the file <b>CreditCardDetails.txt</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUiV9XjyUeMV5IC2Pihqti1sEGBE6ENe_ezwtQXDYrCNR68dpCfOVj-_cDqi-j7V_Zd39kWKbfV3MFFJXvItWQOwQB72cHDeIkI75cJ89nikKd46S3VBFsnDvhdMX9zxzTJLL2Cti2QbkO/s1600/Import+Flat+File+-+05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Flat File" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUiV9XjyUeMV5IC2Pihqti1sEGBE6ENe_ezwtQXDYrCNR68dpCfOVj-_cDqi-j7V_Zd39kWKbfV3MFFJXvItWQOwQB72cHDeIkI75cJ89nikKd46S3VBFsnDvhdMX9zxzTJLL2Cti2QbkO/s1600/Import+Flat+File+-+05.PNG" title="Browse the Flat File" /></a></div>
<br />
<ul style="text-align: left;">
<li>In <b>Columns</b> Tab<b>, </b>select <b>Vertical Bar {|} </b>as <b>Column delimiter </b>and click <b>Refresh. </b>This will preview the data like below</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj20w6BNO792h_prXxip1loYY_SupZdXRStZoC1bp6R1tE8qJZWoA7s2J8gpsTk-HMEL-EXdKA1aFpf3BqwADDUy8_tj5igi-nhBR7V473gkDJM_P1QlG0V8YIhsZXZHiSz_887QEOUrFtG/s1600/Import+Flat+File+-+06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Column Delimiter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj20w6BNO792h_prXxip1loYY_SupZdXRStZoC1bp6R1tE8qJZWoA7s2J8gpsTk-HMEL-EXdKA1aFpf3BqwADDUy8_tj5igi-nhBR7V473gkDJM_P1QlG0V8YIhsZXZHiSz_887QEOUrFtG/s1600/Import+Flat+File+-+06.PNG" title="Configure Column Delimiter" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>OK</b> twice to close the pop up windows.</li>
</ul>
<br />
<b>Step 7: Add and Configure SQL Server Destination</b><br />
<ul style="text-align: left;">
<li>Add <b>SQL Server Destination.</b></li>
<li>Connect <b>Flat File Source</b> to <b>SQL Server Destination.</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7lLl2t0a5EAO_MShyNOahJCITIAcKFwvdMU27Fuqa04w-b4QIcbBbHAALI3ASBFP4FUmUfxqcDT3boD2KIQQrcpo50wF3f91xbKzNCSes834tdLujRnrY8zpO59t4X0uUuNiyR9nA4jE5/s1600/Import+Flat+File+-+061.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="SQL Server Desitnation" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7lLl2t0a5EAO_MShyNOahJCITIAcKFwvdMU27Fuqa04w-b4QIcbBbHAALI3ASBFP4FUmUfxqcDT3boD2KIQQrcpo50wF3f91xbKzNCSes834tdLujRnrY8zpO59t4X0uUuNiyR9nA4jE5/s1600/Import+Flat+File+-+061.PNG" title="Add SQL Server Desitnation" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>SQL Server Destination</b>. This will open <b>SQL Destination Editor</b> window.</li>
<li>Select the <b>Shared Data Connection</b> if not selected we created in <b>Step 2</b>.</li>
<li>Select Table <b>CreditCardDetails</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik9lwwVrBePsogmVCsWPf_HOk8-YvrNt16bECuQT4VQ2zYqwBgFTlXS_ZoT09-QcJW7gK03Eg8Z_2a0EX5qHQV8GySFYR3Hc-fTa1fZNG4SjorHyrF6IYyPPONhvovIn8noTXg0lEk1kfQ/s1600/Import+Flat+File+-+07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="SQL Destination Editor" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik9lwwVrBePsogmVCsWPf_HOk8-YvrNt16bECuQT4VQ2zYqwBgFTlXS_ZoT09-QcJW7gK03Eg8Z_2a0EX5qHQV8GySFYR3Hc-fTa1fZNG4SjorHyrF6IYyPPONhvovIn8noTXg0lEk1kfQ/s1600/Import+Flat+File+-+07.PNG" title="Configure SQL Destination" /></a></div>
<br />
<ul style="text-align: left;">
<li>On <b>Mapping </b>Tab Map <b>Input Column </b>with <b>Destination Column </b>like below<b>. Input Column </b>and <b>Destination column</b> with same names are automatically mapped, otherwise you have to manually map. Here we have manually map <b>Data</b> and <b>Transaction Date</b> as the names are different</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhar_G3ehVxJmLHzhd-5-i6NzifwS_-rHl3ToWfzHA8FzukKy4VTpLXbw_Xm1MSvTTy0tthJiequ7JTYPgbBRBGDX7-HtOo8kL-zOHOd_8xe9OzM1hTCahFJODwd5C8qQ7_ZqMOmVYVdNHM/s1600/Import+Flat+File+-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Mapping" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhar_G3ehVxJmLHzhd-5-i6NzifwS_-rHl3ToWfzHA8FzukKy4VTpLXbw_Xm1MSvTTy0tthJiequ7JTYPgbBRBGDX7-HtOo8kL-zOHOd_8xe9OzM1hTCahFJODwd5C8qQ7_ZqMOmVYVdNHM/s1600/Import+Flat+File+-09.PNG" title="Map Input Column with Destination Column" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>OK</b> to close the <b>Destination Editor</b> window. At this stage we are done with configuration of <b>SQL Server Destination</b> but there is a data conversion error between the source and destination data types.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkqwxR_CL7jpUqdUZrG0_8jOnoY2pQ2V53UeYAwVKMhx706nVeYM_WV9fU2wWyE1GaJjhZRu8incoxfvJ3TqHFqQotsZhkNs4Y0QEkjX1H-YzvaVGIfoj7vlPgjjhI43H5uJOpJyg30sd1/s1600/Import+Flat+File+-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Conversion Error" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkqwxR_CL7jpUqdUZrG0_8jOnoY2pQ2V53UeYAwVKMhx706nVeYM_WV9fU2wWyE1GaJjhZRu8incoxfvJ3TqHFqQotsZhkNs4Y0QEkjX1H-YzvaVGIfoj7vlPgjjhI43H5uJOpJyg30sd1/s1600/Import+Flat+File+-10.PNG" title="Data Conversion Error" /></a></div>
<br />
<ul style="text-align: left;">
<li>All the columns that we are loading from Flat file have string type but in SQL Server Table there are two columns which have <b>DateTime</b> and <b>Numeric</b> Data Type. For this we need to place <b>Data Conversion Transformation</b> between the <b>Source</b> and <b>Destination</b> to make it compatible</li>
</ul>
<br />
<b>Step 8: Add and Configure Data Conversion Transformation</b><br />
<ul style="text-align: left;">
<li>Delete the <b>connector</b> between <b>Flat File Source</b> and <b>SQL Server Destination</b></li>
<li>Add <b>Data Conversion</b> Transformation</li>
<li>Connect <b>Flat File Source</b> to <b>Data Conversion</b></li>
<li>Connect <b>Data Conversion</b> to <b>SQL Server Destination</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix17lyegmj0pwfByFPAlzvWO8Bpb8EeTEN1_gWYF1pS41LBV7WFsOt2SGz-KOHiga3MiwyAFvpcfMy23s9XHV6361GQjaJ69KVWgF4hNxQWKT6-SZqUqMnrS3qIfO-1qy5xJnrPZj9UUm5/s1600/Import+Flat+File+-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Conversion" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix17lyegmj0pwfByFPAlzvWO8Bpb8EeTEN1_gWYF1pS41LBV7WFsOt2SGz-KOHiga3MiwyAFvpcfMy23s9XHV6361GQjaJ69KVWgF4hNxQWKT6-SZqUqMnrS3qIfO-1qy5xJnrPZj9UUm5/s1600/Import+Flat+File+-11.PNG" title="Add Data Conversion Transformation" /></a></div>
<br />
<ul style="text-align: left;">
<li>Double Click <b>Data Conversion</b> and configure like below.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigE92RrzgzzApVrTjhKtnJ5ioYp9F1X8s_mKbgOAYrTcBsfNtv9ZAcjc9bSoyvKo-u5FsNKK71ZfEN4RoymGpk15XSYfhH8bDWBIz6DNTGLWsYExxOd8Cx7Iv4V1hnMn-J1VSKayYLvFNd/s1600/Import+Flat+File+-12.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Data Conversion" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigE92RrzgzzApVrTjhKtnJ5ioYp9F1X8s_mKbgOAYrTcBsfNtv9ZAcjc9bSoyvKo-u5FsNKK71ZfEN4RoymGpk15XSYfhH8bDWBIz6DNTGLWsYExxOd8Cx7Iv4V1hnMn-J1VSKayYLvFNd/s1600/Import+Flat+File+-12.PNG" title="Configure Data Conversion" /></a></div>
<br />
<b>Step 9: Update mapping in SQL Server Destination </b><br />
<ul style="text-align: left;">
<li>Double Click <b>SQL Server Destination</b> and select <b>Mapping</b> Tab</li>
<li>Update the mapping for <b>Date</b> and <b>Amount</b> Columns like below</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXNdcwB63pn-qdbiykBjyWURoqHb2ArG7iFLfgKEpnoJ2D6XqW4tf6tiiHX7AqEkT69cO_su5uSVjopPdMcjb5YyQ_mzBfPzk4hQ9o4ryFSWGt_T-POhMsqxht_sKKcVQELeiGVZ901nM/s1600/Import+Flat+File+-13.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Update Mapping" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXNdcwB63pn-qdbiykBjyWURoqHb2ArG7iFLfgKEpnoJ2D6XqW4tf6tiiHX7AqEkT69cO_su5uSVjopPdMcjb5YyQ_mzBfPzk4hQ9o4ryFSWGt_T-POhMsqxht_sKKcVQELeiGVZ901nM/s1600/Import+Flat+File+-13.PNG" title="Update Mappin" /></a></div>
<br />
We are done with creating the <b>package</b>. Now let's run and test the <b>package</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBcu4jErwoaoag8TXI5cc5VGMmGo4dNWn8AD9EUBsjNi4eA5TCsRKSJbOeimjb4rjhtvIzWSubQgcFDp8T6r9hpI8-Fo7ZSuKxZOwHuqyNm3dlF-masjQRIttsn-gq87S-fsLYW8NlblTv/s1600/Import+Flat+File+-14.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Package Execution" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBcu4jErwoaoag8TXI5cc5VGMmGo4dNWn8AD9EUBsjNi4eA5TCsRKSJbOeimjb4rjhtvIzWSubQgcFDp8T6r9hpI8-Fo7ZSuKxZOwHuqyNm3dlF-masjQRIttsn-gq87S-fsLYW8NlblTv/s1600/Import+Flat+File+-14.PNG" title="Package Execution" /></a></div>
<br />
<b>Package</b> executed successfully and transmitted 9 rows to <b>SQL Server Destination Table</b>.<br />
<br />
Now let's run a query in <b>SQL Server Management Studio</b> and check the table<br />
<!--INFOLINKS_OFF-->
<pre class="brush:sql">SELECT * FROM CreditCardDetails</pre>
<!--INFOLINKS_ON-->
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnvtOsAHc25VqTsDsdDPGjF5cWPdBfzn7Rs3qhL_pWMlSf0BLR2OulAaLIsMw2o3YytqTAOm4loanx-boAlMuyoO2NtcY9-4dFvu_aSAOfH1m9AI9hk3pFzLVC4fJleHoasICZHJWkhbf1/s1600/Import+Flat+File+-15.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-left: 1em;"><img alt="Table Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnvtOsAHc25VqTsDsdDPGjF5cWPdBfzn7Rs3qhL_pWMlSf0BLR2OulAaLIsMw2o3YytqTAOm4loanx-boAlMuyoO2NtcY9-4dFvu_aSAOfH1m9AI9hk3pFzLVC4fJleHoasICZHJWkhbf1/s1600/Import+Flat+File+-15.PNG" title="Table Output" /></a></div>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com1tag:blogger.com,1999:blog-2904192386692843423.post-1660401503285233062016-02-18T11:31:00.002+05:302016-04-06T15:25:07.608+05:30Sum up Time Field in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Yesterday, one of a member <a href="https://www.facebook.com/mritkrish?fref=nf" target="_blank">Murali Krishna Rayudu</a> in a Facebook Group <b>MSBI Query Cracker's</b> post the requirement to <b>calculate Total Working Hours</b>. In the scenario there was a column with Time Data Type and had the values for hours worked and the requirement was to sum up the time to calculate Total Hours worked. At first go, it looks straight forward as we have aggregate function SUM to sum up column values, but it's not because sum of a column with TIME data type is not supported in SQL Server<br />
<br />
<div class="mydivimg">
<b>NOTE:</b> Time Data Type was introduced in SQL Server 2008</div>
<br />
Let's try to sum up the values of a column with Time Data Type with SUM<br />
<!--INFOLINKS_OFF-->
<pre class="brush: sql">declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')
select SUM(col) from @tab</pre>
<!--INFOLINKS_ON-->
<div class="mydivimg" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1NqPatmiNGcFvB4esk77F3tH-eKbyQX3Ago3WEX2erhl_oTk7tJWPgYNuGRTUSnIxJYkgmHKjdw9JWolA5I2nCF1y97whOv_SFHUEr6_60K9wGc8JdBsNEQ9whc6vkA8dw0PoqLHO0yY8/s1600/Time-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Error with SUM for Time datatype" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1NqPatmiNGcFvB4esk77F3tH-eKbyQX3Ago3WEX2erhl_oTk7tJWPgYNuGRTUSnIxJYkgmHKjdw9JWolA5I2nCF1y97whOv_SFHUEr6_60K9wGc8JdBsNEQ9whc6vkA8dw0PoqLHO0yY8/s1600/Time-01.PNG" title="Error with SUM for Time datatype" /></a></div>
<br />
Look, we get the above error. So, it is concluded that aggregate functions not work on Time Data Type in SQL Server.<br />
<br />
Solution to above I provided the following script to calculate the same on the post itself and thought of sharing the same on by blog. In the below script I have provided two ways.<br />
<!--INFOLINKS_OFF-->
<pre class="brush: sql">declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')
--Solution-1
select right('0'+cast(sum(DATEPART(hh,col)) + (sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))/60 as varchar),2)
+':'+right('0'+ cast((sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))%60 as varchar),2)
+':'+ right('0'+ cast(sum(DATEPART(s,col))%60 as varchar),2)
from @tab
--Solution-2
select cast(secs/3600 as varchar)
+':'+cast((secs-(secs/3600)*3600)/60 as varchar)
+':'+cast(secs%60 as varchar)
from(
select sum((DATEPART(HH,col)*3600)+(DATEPART(mi,col)*60)+DATEPART(S,col)) secs
from @tab
) t</pre>
<!--INFOLINKS_ON-->
<div class="mydivimg" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS2bVN5cqgmzxNr4jYRsuPuxgNyMJebIF8jL2t1Q7FRHhCyHYvrKddKo3soVfsXJqJ7RpM-3cjfvJ068tNr_OoSONw5kXx7MwJ-ByG1wPSv935QmTaiXuCMxOYunqReq-pMB1zxoj98v8C/s1600/Time-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Total working Hours" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS2bVN5cqgmzxNr4jYRsuPuxgNyMJebIF8jL2t1Q7FRHhCyHYvrKddKo3soVfsXJqJ7RpM-3cjfvJ068tNr_OoSONw5kXx7MwJ-ByG1wPSv935QmTaiXuCMxOYunqReq-pMB1zxoj98v8C/s1600/Time-02.PNG" title="Total working Hours" /></a></div>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-36954761526961335412016-02-01T12:59:00.002+05:302016-03-24T13:53:32.567+05:30Filter Parameter in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
In this article you will lean what is <b>Filter Parameter</b> in SSRS and how it works with an example.<br />
<b><br /></b>
<b>Filter Parameter(s)</b> are used to filter the result on the report once the data is fetched from the database. It is alternate to <a href="http://www.itdeveloperzone.com/2015/11/query-parameter-in-ssrs.html" target="_blank"><b>Query Parameter</b></a> to filter the result, but not efficient as it filters the data on the front end which lead to fetching all the data from the database and increasing the network flow<br />
<br />
Let's create a report to see how <b>Filter Parameter</b> works.<br />
<br />
<b>Step 1: </b><a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank"><b>Create a simple Tabular Report</b></a><br />
<ul style="text-align: left;">
<li>First Create a simple Tabular Report using above link. Once report is created we would apply <b>Filter Parameter</b> on the report.</li>
</ul>
<br />
<b>Step 2: Add and configure Parameter</b><br />
<ul style="text-align: left;">
<li>On <b>Report Data</b> Tab, Click <b>Parameters </b>and <b>Add Parameters</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgG-4j5V9mTPjauTj5ASL2GAeUyoyzvmnmz3Ab78vCjfTP4tkv0xCKAoX-Ds3XZNCLZIr9-o880Oytn7VixOZ2-YMT5sP7tL2QMT2U-EwyfFQUNsOv6ASxxWGdYJ4873YWHI9sS6dH6HjNi/s1600/Query+Parameter-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Filter Parameter-01" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgG-4j5V9mTPjauTj5ASL2GAeUyoyzvmnmz3Ab78vCjfTP4tkv0xCKAoX-Ds3XZNCLZIr9-o880Oytn7VixOZ2-YMT5sP7tL2QMT2U-EwyfFQUNsOv6ASxxWGdYJ4873YWHI9sS6dH6HjNi/s1600/Query+Parameter-01.PNG" title="Filter Parameter-01" /></a></div>
<br />
<ul style="text-align: left;">
<li>Configure <b>Report Parameter</b> properties as below and click <b>OK</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn3IXvFzXv_DE8OURIOK7MlEOBTTBptPMZnUyjIRV1XX87cUBPGEohE3sL0mePrOFDcvEN6JoLfOBLWD6n5rAxGn0Pp7833SwesN7D0ffnpjpQjm9YYxa6xowmlu_qCGhVSPlbp94RbTyt/s1600/Query+Parameter-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report_Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn3IXvFzXv_DE8OURIOK7MlEOBTTBptPMZnUyjIRV1XX87cUBPGEohE3sL0mePrOFDcvEN6JoLfOBLWD6n5rAxGn0Pp7833SwesN7D0ffnpjpQjm9YYxa6xowmlu_qCGhVSPlbp94RbTyt/s1600/Query+Parameter-02.PNG" title="Report Parameter" /></a></div>
<br />
<ul style="text-align: left;">
<li>This will add <b>GroupName Parameter</b> under <b>Parameters</b> like below.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpF7Oei579gaeHGSqI246b-Dvc-v7ryQnELTYILzrw8RsyuKQ-OhpQBH2MprhG0zSF8ukvNxcKx3rZGs7SLVuysuMsDa8TKX6sUXII8uLDMmAVNmS6snbLgIbZBiwlSm8uHGOuwDTphglw/s1600/Query+Parameter-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Parameter Added" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpF7Oei579gaeHGSqI246b-Dvc-v7ryQnELTYILzrw8RsyuKQ-OhpQBH2MprhG0zSF8ukvNxcKx3rZGs7SLVuysuMsDa8TKX6sUXII8uLDMmAVNmS6snbLgIbZBiwlSm8uHGOuwDTphglw/s1600/Query+Parameter-03.PNG" title="Parameter Added" /></a></div>
<br />
<b>Step 3: Add Filter Parameter</b><br />
<ul style="text-align: left;">
<li>In <b>Report Data</b> Tab right click <b>DataSet1</b> under <b>Datasets</b> and then click <b>Dataset Properties</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPc-FtzSMn3AYivhVHb6l0DyAoIRFSjnEHUjTWuQB6cOFTXmGeVKCXpvoVyYxJTtr3MHMhxCFPdfBXZpMFYtUNuq2XlzNi528ZqECUXPJF2vUVTLayUdlCBX_VWszZR0sMOdNpu2jADbDS/s1600/Query+Parameter-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="ds_prop" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPc-FtzSMn3AYivhVHb6l0DyAoIRFSjnEHUjTWuQB6cOFTXmGeVKCXpvoVyYxJTtr3MHMhxCFPdfBXZpMFYtUNuq2XlzNi528ZqECUXPJF2vUVTLayUdlCBX_VWszZR0sMOdNpu2jADbDS/s1600/Query+Parameter-04.PNG" title="Dataset Prop" /></a></div>
<br />
<ul style="text-align: left;">
<li>Select <b>Filter</b> Tab and <b>Add Filter</b>. Click <b>fx</b> and select <b>Parameter GroupName</b> as <b>value</b> in the <b>pop up window</b>.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWX7jh0TDprtil11utrgCNW8r8VyIFNsOPdN7Aq_dFkGLjl3UByGZVxAKsBiiaLiX6xgG8MSPzEBSnhFlQC3XjIsLV1OnrwbzeS4p92wO2FwqlySqqb_gT-qHxcHUbcrDtgu-cTGagZjaa/s1600/Filter-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Filter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWX7jh0TDprtil11utrgCNW8r8VyIFNsOPdN7Aq_dFkGLjl3UByGZVxAKsBiiaLiX6xgG8MSPzEBSnhFlQC3XjIsLV1OnrwbzeS4p92wO2FwqlySqqb_gT-qHxcHUbcrDtgu-cTGagZjaa/s1600/Filter-01.PNG" title="Configure Filter" /></a></div>
<br />
<br />
We are done with adding <b>Filter Parameter</b> to the <b>Report</b>.<br />
<br />
Now let's build the Solution and run the report. The <b>Parameter GroupName</b> that we created in <b>step 2</b> is appearing in the report to filter the result set.<br />
<br />
Enter <b>Manufacturing</b> in the <b>text-box</b> and click <b>View Report</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUf8pzcN3Y296Egv-cEqaLsnUj8Recw-sxyyAcrfm0qs-eFq3YnfjSXdFLkIAM4kiDqOk8ubRb1DGgldLOGyT47L0OEFJrWeU0iQzIE7HsS-rFFyxeS-hWn4tJEI7XVbgpX-iUX_p4uMyd/s1600/Query+Parameter-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUf8pzcN3Y296Egv-cEqaLsnUj8Recw-sxyyAcrfm0qs-eFq3YnfjSXdFLkIAM4kiDqOk8ubRb1DGgldLOGyT47L0OEFJrWeU0iQzIE7HsS-rFFyxeS-hWn4tJEI7XVbgpX-iUX_p4uMyd/s1600/Query+Parameter-08.PNG" title="Report Output" /></a></div>
<b><br /></b>
<b>Look at the output, the report data is filtered with Group Name as Manufacturing
</b></div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-23285077864570547672016-01-01T13:36:00.000+05:302016-03-24T18:06:02.763+05:30Paging in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Paging</b> is used to display <b>N</b> no. of records per page. In this post, you will see step by step example of implementing paging in SSRS.<br />
<br />
<b>Basic steps to implement paging in SSRS are:</b><br />
<ul style="text-align: left;">
<li>Create a simple Tabular Report</li>
<li>Create a column S.No which generates an incremental number using <a href="http://www.itdeveloperzone.com/2015/12/rownumber-in-ssrs.html" target="_blank"><b>RowNumber</b></a> function</li>
<li>Grouping using function to display N records on each page.</li>
</ul>
<br />
<b>Let's create a report to see</b><br />
For this, we will continue on a <a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank"><b>Tabular Report</b></a> we created in an earlier article.<br />
<br />
<b>Step 1:</b> <a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank"><b>Create a simple Tabular Report</b></a><br />
<br />
First Create a simple Tabular Report using above link. Report looks like below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkoHf6b6i8cSzLoIggnz41ckNLEvdwnf7orz5QMqW5ah4roLLHkp1B34K8Z8cP2qTbZbC2_33M_Gg5eW7J42-HZbbRwzdkiksG3bXvZpGIZz6B5MkI96vEB47J3lAIMGFNj6jJnKdENO3y/s1600/Paging-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-01" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkoHf6b6i8cSzLoIggnz41ckNLEvdwnf7orz5QMqW5ah4roLLHkp1B34K8Z8cP2qTbZbC2_33M_Gg5eW7J42-HZbbRwzdkiksG3bXvZpGIZz6B5MkI96vEB47J3lAIMGFNj6jJnKdENO3y/s1600/Paging-01.PNG" title="Simple Tabular Report" /></a></div>
<br />
<b>Step 2: <a href="http://www.itdeveloperzone.com/2015/12/rownumber-in-ssrs.html" target="_blank">Create a column S.No with RowNumber</a></b><br />
<ul style="text-align: left;">
<li><b>S.No</b> column would generate an incremental number.</li>
<li>Expression : <b>RowNumber(Nothing)</b></li>
</ul>
<b><a href="http://www.itdeveloperzone.com/2015/12/rownumber-in-ssrs.html" target="_blank">How to add S.No Column using RowNumber</a></b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTS7Nc_Sr6sJS2mzgM-Zz6xJWT1KuCcZ6fhnue0zE8A0zqU0T95tBqhe1S0NIHn7iy12oKcxEexjS6ZfUs1kqLa-zJKRuDMBhBqQYhKWYJwy2qPJfmGpN7d2JJaR4anGJr0N_2-3VxfrkV/s1600/Paging-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-02" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTS7Nc_Sr6sJS2mzgM-Zz6xJWT1KuCcZ6fhnue0zE8A0zqU0T95tBqhe1S0NIHn7iy12oKcxEexjS6ZfUs1kqLa-zJKRuDMBhBqQYhKWYJwy2qPJfmGpN7d2JJaR4anGJr0N_2-3VxfrkV/s1600/Paging-02.PNG" title="Create a S.No column with RowNumber" /></a></div>
<br />
<b>Step 3:</b> Grouping using function to display N records on each page.<br />
<ul style="text-align: left;">
<li>Right click <b>Data Region</b>, Then Add <b>Group>Parent Group</b></li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFy6fOaf6BOZAGmyd3skzzh8Wc7YZnw624MBf2sdd9wIVtHUjNEbFd5PlZE-FySx4-uFAezXJa186r2ozFah6Ydnjl-O8xzcWWuMx-CoqDmuqhdAv3HTqwhw3hJUGaC39Dr4JCaJ_KunI_/s1600/Paging-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-03" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFy6fOaf6BOZAGmyd3skzzh8Wc7YZnw624MBf2sdd9wIVtHUjNEbFd5PlZE-FySx4-uFAezXJa186r2ozFah6Ydnjl-O8xzcWWuMx-CoqDmuqhdAv3HTqwhw3hJUGaC39Dr4JCaJ_KunI_/s1600/Paging-03.PNG" title="Add Group" /></a></div>
<br />
<ul style="text-align: left;">
<li>Click <b>fx</b> and write below <b>Formula</b> in <b>Expression</b> window</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-0AoSC_Js73JKUS7EbEbchuJu1lxHbls559DfUWaefNBMRoA13r_akeohsAUDoSHUqKWDIOc5NfXYKvUxevu4GWW4vxEUWnefsE4TXx-zlk8hAHxxptIYXwJNRQmahmD7RWQ5K1lYZ6Hi/s1600/Paging-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-04" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-0AoSC_Js73JKUS7EbEbchuJu1lxHbls559DfUWaefNBMRoA13r_akeohsAUDoSHUqKWDIOc5NfXYKvUxevu4GWW4vxEUWnefsE4TXx-zlk8hAHxxptIYXwJNRQmahmD7RWQ5K1lYZ6Hi/s1600/Paging-04.PNG" title="Expression" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzb9e42hCeN4whnDZQMfuw7aYH1RXcw5DeEUkh2smUDcpjSzZqFjFjizXJ5UGQSYuoqY3sGklpcqTjKoNbiIKWxS-nEqweAcoVyWXNr8-hagHa9buPbdUjjPg5T9K5w2f9GykWqVJpVcxY/s1600/Paging-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-05" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzb9e42hCeN4whnDZQMfuw7aYH1RXcw5DeEUkh2smUDcpjSzZqFjFjizXJ5UGQSYuoqY3sGklpcqTjKoNbiIKWxS-nEqweAcoVyWXNr8-hagHa9buPbdUjjPg5T9K5w2f9GykWqVJpVcxY/s1600/Paging-05.PNG" /></a></div>
<b><br /></b>
<b>Note:</b> At this state if you will run the report you will get below error
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRmS2d94OQzfRChH495f7nDP8UBB8vbQJDeR9NL7Idz258BNllEO-gq8ic6AWSVa2zup49jopPoHgBBGFmzGkdseygyMGNxNLA7jVWU6ZTVshAI9Qna0Im9I4lsI8N0Lz-Fnqf6jz-v-5W/s1600/Paging-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-06" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRmS2d94OQzfRChH495f7nDP8UBB8vbQJDeR9NL7Idz258BNllEO-gq8ic6AWSVa2zup49jopPoHgBBGFmzGkdseygyMGNxNLA7jVWU6ZTVshAI9Qna0Im9I4lsI8N0Lz-Fnqf6jz-v-5W/s1600/Paging-06.PNG" title="Error in Output" /></a></div>
To overcome this error, we need to delete <b>Sort Expression</b> in <b>Group Properties</b><br />
<br />
Right <b>Click Group > Group Properties</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihR-tDNy2CdFludXrI4sLNbUjoTyAwCq4x-l_nviyXC8YHcxSws72Upqux0zIpMjtnEeQ0qddnR5lFOqd1r320BMdsvzkH2vLJhDDPxq-Oqc-3w7FS73kqp8dKcU9F4JNZ2Op7JzZyMdcJ/s1600/Paging-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-07" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihR-tDNy2CdFludXrI4sLNbUjoTyAwCq4x-l_nviyXC8YHcxSws72Upqux0zIpMjtnEeQ0qddnR5lFOqd1r320BMdsvzkH2vLJhDDPxq-Oqc-3w7FS73kqp8dKcU9F4JNZ2Op7JzZyMdcJ/s1600/Paging-07.PNG" title="Group Properties" /></a></div>
<br />
Select <b>Sorting Tab</b> and delete the existing Sort Expression appearing in the below screen
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglD1t8SkLOx7eWr8nT69EC5I11W4MXfnWXYarW9xW-CzXXHfVuG8FwwJlcvbzdWV0xoZI-gdf3N6n3AQznYMEq2IZqWVEUFmXkae4KriXC9ULaPChYBtgMZk7E0HHF2lxal0JveEdglbkx/s1600/Paging-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-08" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglD1t8SkLOx7eWr8nT69EC5I11W4MXfnWXYarW9xW-CzXXHfVuG8FwwJlcvbzdWV0xoZI-gdf3N6n3AQznYMEq2IZqWVEUFmXkae4KriXC9ULaPChYBtgMZk7E0HHF2lxal0JveEdglbkx/s1600/Paging-08.PNG" title="Delete Sort Expression" /></a></div>
<br />
Select <b>Page Breaks</b> Tab and select the option <b>Between each instance of a group
</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz0ZVpgRXJRuXAGZz5inijzxtPqxrLlUdKe15MYtJr-Pf4edhKt4G2248ZDctX7gDoGuHSNAhtjd-0GsbiyfWZLbf8RSaUiwt5lSK6HPHPA7ExJRd6-zRiy8hcpGrcVyBKeDYMB_Q7hPnh/s1600/Paging-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-09" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz0ZVpgRXJRuXAGZz5inijzxtPqxrLlUdKe15MYtJr-Pf4edhKt4G2248ZDctX7gDoGuHSNAhtjd-0GsbiyfWZLbf8RSaUiwt5lSK6HPHPA7ExJRd6-zRiy8hcpGrcVyBKeDYMB_Q7hPnh/s1600/Paging-09.PNG" title="Configure Page Break" /></a></div>
<br />
We are done with implementing paging at this state. Let's run the report and check the output<br />
<br />
<b>Output of the Report</b><br />
<div class="separator" style="clear: both; text-align: center;">
<b>Page 1</b></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyzyjHhal1OzIQNajbD2LZaU4h21A49InfUK3tKg1yg0dGw3AA3JxaZ_i04gj8y_kdJ5ME8o1XZFNYHg_QgvyMjcnsgsy-CUFuDX2SJQRknDsa47UoVMnBatQ5UBAvKdW9uIBvPFMefT2y/s1600/Paging-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-10" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyzyjHhal1OzIQNajbD2LZaU4h21A49InfUK3tKg1yg0dGw3AA3JxaZ_i04gj8y_kdJ5ME8o1XZFNYHg_QgvyMjcnsgsy-CUFuDX2SJQRknDsa47UoVMnBatQ5UBAvKdW9uIBvPFMefT2y/s1600/Paging-10.PNG" title="Report Output - Page 1" /></a>
<br />
<div style="text-align: center;">
<b>Page 2</b></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNubnJZKb5HYrosmV0nncDS5LWtslQT4t6gWFwL16C2RN4OQdxG18YAidxw9xpcbGg6oOLzatDAGUD_NfAMx9HcKpAwqB87MH1Qes65Pp6vmI2-0AtqGCqoULxKhEnPz_X0fZLnA2X3dOu/s1600/Paging-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-11" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNubnJZKb5HYrosmV0nncDS5LWtslQT4t6gWFwL16C2RN4OQdxG18YAidxw9xpcbGg6oOLzatDAGUD_NfAMx9HcKpAwqB87MH1Qes65Pp6vmI2-0AtqGCqoULxKhEnPz_X0fZLnA2X3dOu/s1600/Paging-11.PNG" title="Report Output - Page 2" /></a><br />
<b><br /></b>
<b>Group1</b> column is not required in the ouptut, so you can right Click and delete this column using <b>Delete Columns only</b> option<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkrClYbTTPoEBVacGwtXujtuCFuzyVPwEMVyCeWkRX-4oTM5cn5pRWtx4rHZd0BeyIf7c2P3Bvh7VQJVpRZ_Tyvqx4oYiT6XlLRlGQfDgcGfBoydejCHAeFwL1bPMLC42JHkj19j5wukhA/s1600/Paging-12.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Paging-12" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkrClYbTTPoEBVacGwtXujtuCFuzyVPwEMVyCeWkRX-4oTM5cn5pRWtx4rHZd0BeyIf7c2P3Bvh7VQJVpRZ_Tyvqx4oYiT6XlLRlGQfDgcGfBoydejCHAeFwL1bPMLC42JHkj19j5wukhA/s1600/Paging-12.PNG" title="Delete Column" /></a></div>
<span style="color: #333333; font-family: "verdana" , sans-serif; font-size: 13px; line-height: 17.6px;"><br /></span>
<span style="color: #333333; font-family: "verdana" , sans-serif; font-size: 13px; line-height: 17.6px;"><br /></span></div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-81350412063568751662015-12-23T13:26:00.005+05:302022-12-26T15:35:21.225+05:30RowNumber in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>RowNumber</b> returns a sequential number to every row within the specified scope.<br />
<br />
<b>Synatax</b>
<br />
<!--INFOLINKS_OFF-->
<pre class="brush: sql">RowNumber(Scope)</pre>
<!--INFOLINKS_ON-->
<br />
<b><u>Parameter</u></b><br />
<b>Scope:</b> The name of dataset, data region or group or null/nothing. Based on scope it resets the row count to 1. If scope is defined as nothing it will assign each row a unique number and will be equal to the number of rows.<br />
<br />
<b>Similar function in SQL Server: <a href="http://www.itdeveloperzone.com/2013/10/rownumber-in-sql.html" target="_blank">Row_Number in SQL</a></b><br />
<br />
<b>Example</b><br />
Let's create a report and see how RowNumber works. For this, we will continue on a <b><a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank">Tabular Report </a></b>we created in an earlier article.<br />
<br />
<b>Step 1: <a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank">Create a simple Tabular Report</a></b><br />
First Create a simple Tabular Report using above link. Reports look like this<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUc3N4iYz5M62lff2T8-4Kn1BAkKRUS4IXfQIcwRu3GX5CLAkptBxLBfbZW2ezNDI-xcNf-aQwZX0kxDHizavthydgFjhHNsQgq_1NX3GM42-VFIRMxrrwg_40PQE9cWC3hbPAlTV2QF5/s1600/Tabular+Report-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Tabular Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUc3N4iYz5M62lff2T8-4Kn1BAkKRUS4IXfQIcwRu3GX5CLAkptBxLBfbZW2ezNDI-xcNf-aQwZX0kxDHizavthydgFjhHNsQgq_1NX3GM42-VFIRMxrrwg_40PQE9cWC3hbPAlTV2QF5/s1600/Tabular+Report-09.PNG" title="Tabular Report" /></a></div>
<br />
<a href="https://www.blogger.com/"></a>
Once report is created we would generate <b>Row Number</b> to the report.<br />
<br />
<b>Step 2:</b> Right Click on <b>Department Column Header</b> and add a column to its Left and name the Column Header as <b>S.No</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxBMGHoFiiu6GzIZFvz3kK6YMv-vrU4PYRl0kCQqZrZA2BmJDeOgUHe05H_7UEdYFQo13TmaQz4tX0oWKbEqnrkMJNjd2uhaYoQHroopEpGsVGR4Z8U4f_2Uu-11OOCFigCovFwWJZQCXJ/s1600/RowNum-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Column" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxBMGHoFiiu6GzIZFvz3kK6YMv-vrU4PYRl0kCQqZrZA2BmJDeOgUHe05H_7UEdYFQo13TmaQz4tX0oWKbEqnrkMJNjd2uhaYoQHroopEpGsVGR4Z8U4f_2Uu-11OOCFigCovFwWJZQCXJ/s1600/RowNum-01.PNG" title="Add Column" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0DZg3XXNBTstdhWfSeZhQEZizfivwEuhMrFNX7tfE8uOqXTOqPy9L_oLAIaIvH2sCMxiyV0J1ltNCr371fII5gjXe-PjpPX4ZEj42A6L2QDdkZYCCmh38Kd8vnLZseBfirtQveJ89QpDC/s1600/RowNum-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="S.No Column" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0DZg3XXNBTstdhWfSeZhQEZizfivwEuhMrFNX7tfE8uOqXTOqPy9L_oLAIaIvH2sCMxiyV0J1ltNCr371fII5gjXe-PjpPX4ZEj42A6L2QDdkZYCCmh38Kd8vnLZseBfirtQveJ89QpDC/s1600/RowNum-02.PNG" title="S.No Column" /></a></div>
<br />
<b>Step 3:</b> Right Click on <b>S.No</b> Data Column and click <b>Expression.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrZKfrXTivA5-HV9njKb6EEbiBr_dUJWAk54VFpeetVmpIMILD2Np7dIA9DFxUoxU9UsXTAu28o84KUHs02q17z83T_35z1BOqoZke9Lo_cnRftMWn6HaxULKbwwF4gl0oHRMc-JNcRusM/s1600/RowNum-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Expression" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrZKfrXTivA5-HV9njKb6EEbiBr_dUJWAk54VFpeetVmpIMILD2Np7dIA9DFxUoxU9UsXTAu28o84KUHs02q17z83T_35z1BOqoZke9Lo_cnRftMWn6HaxULKbwwF4gl0oHRMc-JNcRusM/s1600/RowNum-03.PNG" title="Add Expression" /></a></div>
<br />
<b>Step 4:</b> In the <b>Expression</b> window, set expression value as <b>=RowNumber(Nothing)</b> and click Ok<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpI9lM6kHMEVEfeAvltzgBbqEaoP2fD7ZVIfMYm9cUGmP91wE_ecLe9XM_X7NRotLH_eC_ieOr6xjFtcGKwXXZIJQ86GIZu4Rhcbe5_OwHD8Ty0mqHaRxIlVWx8sUDaUg6w8Es2EEVmJYl/s1600/RowNum-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Set Expression" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpI9lM6kHMEVEfeAvltzgBbqEaoP2fD7ZVIfMYm9cUGmP91wE_ecLe9XM_X7NRotLH_eC_ieOr6xjFtcGKwXXZIJQ86GIZu4Rhcbe5_OwHD8Ty0mqHaRxIlVWx8sUDaUg6w8Es2EEVmJYl/s1600/RowNum-04.PNG" title="Set Expression" /></a></div>
<br />
<b>Step 5:</b> Here we have defined the scope as Nothing, so it will generate a unique <b>RowNumber</b> to the result-set. Let's run the report and see the output.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjizAZ6QGJ81YFs8tAA0d8LkMKyrMLCi3zXKc-GK6CJPV_Cggbb13fAAKdogI9r5wlJf-UNsqQs-KFogstFLaVWadpTcvZVs-uL66OMhqKSXOXTZMvnKXGcjarBaQHBwnTJoPK_VSfd9Pa0/s1600/RowNum-05.PNG" imageanchor="1"><img alt="Report Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjizAZ6QGJ81YFs8tAA0d8LkMKyrMLCi3zXKc-GK6CJPV_Cggbb13fAAKdogI9r5wlJf-UNsqQs-KFogstFLaVWadpTcvZVs-uL66OMhqKSXOXTZMvnKXGcjarBaQHBwnTJoPK_VSfd9Pa0/s1600/RowNum-05.PNG" title="Report Output" /></a></div>
<br />
Check the output, we have a column <b>S.No</b> with unique <b>Row Number</b> (S.No) in the output.<br />
<br />
Now let's change the scope of RowNumber to a Group.<br />
<br />
<b>Step 6:</b> Right click <b>Data Region</b>, Then <b>Add Group>Parent Group</b> and add <b>GroupName</b> as Group<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBVB0uiXv6tQIdAK9rRv1PSu6na9UPYnGTH1w1cAToibF8syTvBIYwogX8cI9Qp63mZtiMW71XSjNC-g4aaFQZXcz-qUkCUZcCfXd9An8uW-LG3STT-uG0qrBgPFkQQDig3O9PHwdujcoK/s1600/RowNum-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Group" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBVB0uiXv6tQIdAK9rRv1PSu6na9UPYnGTH1w1cAToibF8syTvBIYwogX8cI9Qp63mZtiMW71XSjNC-g4aaFQZXcz-qUkCUZcCfXd9An8uW-LG3STT-uG0qrBgPFkQQDig3O9PHwdujcoK/s1600/RowNum-06.PNG" title="Add Group" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb3J4LbLTBdb3TbLXF3d9FMiFKfLPg6rQ68aZ-IdAhFK6EqQO3M7A98AfsxTHRmdQe-WO81igtGLz77-8JRsqrs1TGJxnq9CTUIWIXgItMNhmy62h7tKXFLBRYnxs_A03UCtrTx5pei58n/s1600/RowNum-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="GroupName" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb3J4LbLTBdb3TbLXF3d9FMiFKfLPg6rQ68aZ-IdAhFK6EqQO3M7A98AfsxTHRmdQe-WO81igtGLz77-8JRsqrs1TGJxnq9CTUIWIXgItMNhmy62h7tKXFLBRYnxs_A03UCtrTx5pei58n/s1600/RowNum-07.PNG" title="GroupName" /></a></div>
<br />
This will add a new column <b>Group Name</b> to the report.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-LfkIrkflLxYlPCiYm0eCuDkK459SbJC6aqJLLpN8L1cIV7_zlig5LRT8TTa4rnH9MxFPeA1U1mQ_SLUdhzjtZ4SmsNgBNYzi07crLshm4GB_7MbKA4DDASJgj_CtYeSfzv3YTkkFhWQ8/s1600/RowNum-080.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Group Name Column" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-LfkIrkflLxYlPCiYm0eCuDkK459SbJC6aqJLLpN8L1cIV7_zlig5LRT8TTa4rnH9MxFPeA1U1mQ_SLUdhzjtZ4SmsNgBNYzi07crLshm4GB_7MbKA4DDASJgj_CtYeSfzv3YTkkFhWQ8/s1600/RowNum-080.PNG" title="Group Name Column" /></a></div>
<br />
Right click and Delete the newly created <b>Group Name</b> Column. While deleting it will prompt two options. Select <b>Delete columns only. </b>Add a new Column <b>S.No by GroupName</b> to the right of <b>S No</b> Column<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUarReI0sgczXulgQ6q59XHl9rxC4bNFgV_0k630Bs6dX4zLiCbmOy5RJUROaQoDgc5-wY8ZoLKzFXaN3Y7c_a_PNCywlPRCnW1_ZYp2b9YDzWopW-rqrBqvFdDj88GTnd3F4byO4OL5iW/s1600/RowNum-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Column" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUarReI0sgczXulgQ6q59XHl9rxC4bNFgV_0k630Bs6dX4zLiCbmOy5RJUROaQoDgc5-wY8ZoLKzFXaN3Y7c_a_PNCywlPRCnW1_ZYp2b9YDzWopW-rqrBqvFdDj88GTnd3F4byO4OL5iW/s1600/RowNum-08.PNG" title="Add Column" /></a></div>
<b>Step 7:</b> Right click the new Data Column <b>S No By GroupName</b> and click <b>Expression</b>. Set the expression to <b>=RowNumber("GroupName")</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQXYnwW2tPTBrkC16KkLNJm-Jk0wdfU8BWiMRQ5M4rMskL0EE9QNKJsm5g85rtiWyft7ane482sVq2IC8z9qGnxlIboeKboJPkQ4xifKbKuh2q4qeqyk5kYUFofV7c5PKyrKRWDzMLdDCR/s1600/RowNum-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Set Expression" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQXYnwW2tPTBrkC16KkLNJm-Jk0wdfU8BWiMRQ5M4rMskL0EE9QNKJsm5g85rtiWyft7ane482sVq2IC8z9qGnxlIboeKboJPkQ4xifKbKuh2q4qeqyk5kYUFofV7c5PKyrKRWDzMLdDCR/s1600/RowNum-09.PNG" title="Set Expression" /></a></div>
<br />
<br />
<b>Step 8:</b> Now we have defined the scope as <b>GroupName</b>, so <b>S.No by GroupName</b> should be reset to 1 for each <b>GroupName</b>. Let's run the report and check the output<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPyIh4ODI-s7jaZ7FYZ4UErQQfARGmela-9bHbm5K4YYixWNIvjPMuFI_wDqnUmHMMQ4JmJ5DXUhIFu-CzVc_kEqUvYm7NGHVdY76hWJEID366LKDbk8sXeVv10mdJxmSoriPoEQ0etpBV/s1600/RowNum-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPyIh4ODI-s7jaZ7FYZ4UErQQfARGmela-9bHbm5K4YYixWNIvjPMuFI_wDqnUmHMMQ4JmJ5DXUhIFu-CzVc_kEqUvYm7NGHVdY76hWJEID366LKDbk8sXeVv10mdJxmSoriPoEQ0etpBV/s1600/RowNum-10.PNG" title="Report Output" /></a></div>
<br />
Check <b>S.No by GroupName</b> column where <b>RowNumber</b> is starting with 1 for each <b>GroupName</b>.<br />
<br />
<b>Similar function in T-SQL: <a href="http://www.itdeveloperzone.com/2013/10/rownumber-in-sql.html" target="_blank">Row_Number in SQL</a></b></div>
<br/>
Ever created art by using an AI? Now you can with tools like Disco Diffusion, an AI that generates art. <a href="https://runpod.io" target="_blank">Rent GPU from RunPod</a> and start your journey on learning and managing AI models.
<br/>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-88400910383438414372015-12-15T16:09:00.000+05:302016-04-06T15:26:10.479+05:30Sub Report in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
In this article, I am going to demonstrate step by step example of creating <b>Sub Report</b> in <b>SSRS</b>.<br />
<br />
Sub Reports are generally used when we need to show detail data with respect to summary data.<br />
<br />
<b>Example</b><br />
Show Detail Data of Sales with respect to its Sales Header Data<br />
<ul style="text-align: left;">
<li>Main Report will display Sales Header Data</li>
<li>Sub Report will display Sales Detail Data</li>
</ul>
<b>Prerequisites</b><br />
<ul style="text-align: left;">
<li>SQL Server 2012 Data Tools with Reporting Services</li>
<li><b><a href="http://www.itdeveloperzone.com/2015/11/adventureworks-database-for-sql-server.html" target="_blank">AdventureWorks Database</a></b></li>
</ul>
<b>Basic steps to create Sub Report</b><br />
<ul style="text-align: left;">
<li>Create a Main Report</li>
<li>Create a Sub report </li>
<li>Apply Filter Parameter on Sub Report</li>
<li>Insert Sub Report into Main Report</li>
<li>Connect reports using Parameter(s)</li>
</ul>
<b><br /></b>
<b>Now lets have step by step example</b><br />
<br />
<u><b>Step 1: Create a Main Report</b></u><br />
<ul style="text-align: left;">
<li>Create a connection with SQL Server. We would be using shared data source to fetch the data from the database for the report we created in an earlier article. <b><a href="http://www.itdeveloperzone.com/2015/11/create-shared-dataset-ssrs.html" target="_blank">Create a Shared Data Source</a></b></li>
</ul>
<ul style="text-align: left;">
<li>Right Click <b>Reports>Add New Report</b>, Report Wizard will appear to configure the report. Click Next</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add New Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" title="Add New Report" /></a></div>
<br />
<ul style="text-align: left;">
<li>Configure <b>Data Source</b>. Select the <b>Shared Data Source</b> we created in <b>Step 1</b> and Click <b>Next</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Select the Data Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" title="Select the Data Source" /></a></div>
<br />
<ul style="text-align: left;">
<li><b>Design Query</b> either thru <b>Query Builder</b> or write your own query and Click <b>Next</b>.</li>
</ul>
<!--INFOLINKS_OFF-->
<pre class="brush:sql">SELECT SalesOrderID, OrderDate, SOH.CustomerID, P.FirstName+' '+P.LastName as CustomerName
, SOH.SubTotal, SOH.TaxAmt, SOH.Freight, SOH.TotalDue
FROM Sales.SalesOrderHeader SOH
inner join Sales.Customer C on C.CustomerID = SOH.CustomerID
inner join Person.Person P on P.BusinessEntityID = C.PersonID
WHERE SOH.OrderDate = @OrderDate</pre>
<!--INFOLINKS_ON-->
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZRTSbtyDqOIOYe4zQJJ8GOw5rXPCeLen4YiJmN_Acg0kmXATEfGf232ha-Nn8_Taggt6NDefI9BV75LDbS213LrbMXsAyxGcxvdS6v8sYe5bsfa0pdBXN5Jz_f717IP5CCg6pR_5IXAE/s1600/SubReport-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Design the Query" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZRTSbtyDqOIOYe4zQJJ8GOw5rXPCeLen4YiJmN_Acg0kmXATEfGf232ha-Nn8_Taggt6NDefI9BV75LDbS213LrbMXsAyxGcxvdS6v8sYe5bsfa0pdBXN5Jz_f717IP5CCg6pR_5IXAE/s1600/SubReport-01.PNG" title="Design the Query" /></a></div>
<br />
<ul style="text-align: left;">
<li>Select <b>Tabular</b> as <b>Report Type</b> and click <b>Next</b>.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTLfT1CjxCiItS8MNYrjsNcBgJSAO4GMlywmkOCnXmlVrx-QCHB6DL_WsXbG3kFhRo0AUEGcDpEv45wjFwVa_Yd5wst3U6KwEQebBc9HJWsbaf9NIsFCsgdbcc4k_GZc_BDspTkuE9E06l/s1600/Tabular+Report-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Select the Report Type" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTLfT1CjxCiItS8MNYrjsNcBgJSAO4GMlywmkOCnXmlVrx-QCHB6DL_WsXbG3kFhRo0AUEGcDpEv45wjFwVa_Yd5wst3U6KwEQebBc9HJWsbaf9NIsFCsgdbcc4k_GZc_BDspTkuE9E06l/s1600/Tabular+Report-05.PNG" title="Select the Report Type" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<ul style="text-align: left;">
<li>You can configure to display the data based on <b>grouping</b> but as of now just click <b>Finish</b>.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgX1Y7k5FTugEQkz71V2w_NFSJJOwxZV8HTLkVKNwLJcfyfjqyLQMsK9ewfYqBNJ-7x342ZTIyjiA7fmwzanDw-JiTIT3jA6TIKpYJNUlIC6-0ayCMmX3NCJer5RE_ntUDBpdOpEfofwaUV/s1600/SubReport-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Design the Table" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgX1Y7k5FTugEQkz71V2w_NFSJJOwxZV8HTLkVKNwLJcfyfjqyLQMsK9ewfYqBNJ-7x342ZTIyjiA7fmwzanDw-JiTIT3jA6TIKpYJNUlIC6-0ayCMmX3NCJer5RE_ntUDBpdOpEfofwaUV/s1600/SubReport-02.PNG" title="Design the Table" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<ul style="text-align: left;">
<li>Here, you can select <b>Table Style</b>. Default Style is Slate. Click <b>Next</b></li>
<br />
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Choose the Table Style" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" title="Choose the Table Style" /></a></div>
<br />
<ul style="text-align: left;">
<li>At this stage, we are done with configuration of <b>Main Report</b>. Change the <b>Report Name</b> to <b>DailySalesReport</b> and Click <b>Finish</b>.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimKmIkynBbg2vBIy9As5fFQFtuoTcPynofcTtiifOVcEg-fkyNCd81M1KAOMMowve4moFTPb3u69bhwbxHThTQ_ZkaOd0CVDrCZ3vaf2Z905OEXQxd1MEMmwnQR5YaS89mxRhCBDsNt91l/s1600/SubReport-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Set Report Name" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimKmIkynBbg2vBIy9As5fFQFtuoTcPynofcTtiifOVcEg-fkyNCd81M1KAOMMowve4moFTPb3u69bhwbxHThTQ_ZkaOd0CVDrCZ3vaf2Z905OEXQxd1MEMmwnQR5YaS89mxRhCBDsNt91l/s1600/SubReport-03.PNG" title="Set Report Name" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilh-3-HyhLHTFU2ZqBP6DHCIp8ODcDXPGoqaO9Wk_FWcOvOEn1up7vKKcBhQUTgERmcP2H1sD2KFl-yEhTAYYCBi3u6zw3VleyOXl_xQn-rDcKi9EYIcEG2_hSqfeFB5QK2nRHh1IFn5VM/s1600/SubReport-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Main Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilh-3-HyhLHTFU2ZqBP6DHCIp8ODcDXPGoqaO9Wk_FWcOvOEn1up7vKKcBhQUTgERmcP2H1sD2KFl-yEhTAYYCBi3u6zw3VleyOXl_xQn-rDcKi9EYIcEG2_hSqfeFB5QK2nRHh1IFn5VM/s1600/SubReport-06.PNG" title="Main Report" /></a></div>
<br />
<ul style="text-align: left;">
<li>Query Parameter OrderDate that we used in the Query in <b>Step 4</b>. will be created automatically. Change the <b>Data Type</b> to <b>Date/Time</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhF5xqFLXqSeMeT0XI73Q8OiLSSzi6Ypvz0J7kTXe18UB00hBVlVggWaiUaUTyJ80ohQ_VpKXUWTlQGBuZ83ysQVZR9zQ2D-Xb4Rauca5YQac4DLN7Yo4bOdi2a8CZQ_4A81pUgl2IHyO0t/s1600/SubReport-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Query Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhF5xqFLXqSeMeT0XI73Q8OiLSSzi6Ypvz0J7kTXe18UB00hBVlVggWaiUaUTyJ80ohQ_VpKXUWTlQGBuZ83ysQVZR9zQ2D-Xb4Rauca5YQac4DLN7Yo4bOdi2a8CZQ_4A81pUgl2IHyO0t/s1600/SubReport-05.PNG" title="Add Query Parameter" /></a></div>
<br />
<ul style="text-align: left;">
<li><b>To manually add Query Parameter, refer <a href="http://www.itdeveloperzone.com/2015/11/query-parameter-in-ssrs.html" target="_blank">Query Parameter in SSRS</a></b></li>
</ul>
<br />
<b><u>Step 2: Create a Sub Report</u></b><br />
<br />
<ul style="text-align: left;">
<li>Create another report that would be used as <b>Sub Report</b> using the same step we used to create Main Report using below Query. Name the Report to <b>SalesDeails</b>.</li>
</ul>
<!--INFOLINKS_OFF-->
<pre class="brush: sql">SELECT SOD.SalesOrderID, SalesOrderDetailID, P.Name
, P.ProductNumber, SOD.UnitPrice, SOD.OrderQty
FROM Sales.SalesOrderHeader SOH
inner join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
inner join Production.Product P on SOD.ProductID = P.ProductID</pre>
<!--INFOLINKS_ON-->
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinhkmHOuJjejp_6EjJ8ddJrylWO8XZ7YsfTwK8OJEsiM3II5oPVGQyPfiTnaeY6NOYUJlLcytFO5TCXjo4XwHACD2pYBIuvasW2qdbBwjonqi9vbfaPN1NPlFvXezNpn95gxEke5X0Avrt/s1600/SubReport-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Sub Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinhkmHOuJjejp_6EjJ8ddJrylWO8XZ7YsfTwK8OJEsiM3II5oPVGQyPfiTnaeY6NOYUJlLcytFO5TCXjo4XwHACD2pYBIuvasW2qdbBwjonqi9vbfaPN1NPlFvXezNpn95gxEke5X0Avrt/s1600/SubReport-06.PNG" title="Sub Report" /></a></div>
<br />
<br />
<b><u>Step 3: Add Filter Parameter to Sub Report</u></b><br />
<br />
<ul style="text-align: left;">
<li>Add <b>Parameter</b> SalesOrderId to Sub Report.</li>
<li>Set <b>Data type</b> as <b>Integer</b> and <b>Parameter visibility</b> to <b>Hidden</b> as this would be automatically passed from <b>Main Report</b> to <b>Sub Report</b>.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnyL9R-Ggke5yMD4_KRRbPIEZkrFpQ05vV8lSCU4gp59iOF6n7-_2N8Xth-RVfogUReSzyIo-W2c1pKUbcGaqQm1KC_HlZ1BZtZzTZqV4ofzS8u-dReQiGLItWD2frKtMq1-EqhY44eeVD/s1600/SubReport-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnyL9R-Ggke5yMD4_KRRbPIEZkrFpQ05vV8lSCU4gp59iOF6n7-_2N8Xth-RVfogUReSzyIo-W2c1pKUbcGaqQm1KC_HlZ1BZtZzTZqV4ofzS8u-dReQiGLItWD2frKtMq1-EqhY44eeVD/s1600/SubReport-07.PNG" title="Add Parameter" /></a></div>
<br />
<ul style="text-align: left;">
<li>Right Click on <b>Dataset>Datset Properties</b> under <b>Report Data</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjboWDfVG4aS6nFdW1Rdph0v1RMGYXW_ocv-TLaCvJ2KcUs98IPGA6KUY7CH5fwXQs-Zqgc8u-xxgitSWWqPn50h4NBl4fmj7oDaesjKO3DApPAX0WGQwpvO-fiMTPRzjNXfD6QvUDKnrUR/s1600/SubReport-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Dataset Properties" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjboWDfVG4aS6nFdW1Rdph0v1RMGYXW_ocv-TLaCvJ2KcUs98IPGA6KUY7CH5fwXQs-Zqgc8u-xxgitSWWqPn50h4NBl4fmj7oDaesjKO3DApPAX0WGQwpvO-fiMTPRzjNXfD6QvUDKnrUR/s1600/SubReport-08.PNG" title="Dataset Properties" /></a></div>
<br />
<ul style="text-align: left;">
<li>In <b>Dataset Properties</b> Windows select <b>Filters Tab</b> and <b>Add Filter</b> SalesOrderDetailId</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP7BZ5ollu81IIo9w-DNb2_HezAToeghZcf9yjbl-l8Bh2e4tt-LuHmK_r22_7RoKJrFaHzjglz-egLzANiYjsv_s5NhUTKpynskFKG_cULjLTrpja5ebv2TL_Bv5aQd0-4NsHBB0P8SPl/s1600/SubReport-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Filter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP7BZ5ollu81IIo9w-DNb2_HezAToeghZcf9yjbl-l8Bh2e4tt-LuHmK_r22_7RoKJrFaHzjglz-egLzANiYjsv_s5NhUTKpynskFKG_cULjLTrpja5ebv2TL_Bv5aQd0-4NsHBB0P8SPl/s1600/SubReport-09.PNG" title="Add Filter" /></a></div>
<br />
<br />
<b><u>Step 4: Insert Sub Report into Main Report</u></b><br />
<br />
<ul style="text-align: left;">
<li>Add a <b>group</b> to the <b>Data Section</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjroQNZgNglRLrLw1IervuuNhVRAI9GdJl1VkHmMVcJwJQBngmxinmecDKHN64uedQpS1NuvYk6pRoQxlsRtcz1Eq7D_VTWZ562S2zinqdQ1oqHwVl0XVYJsedy4yx4CopJqM54pfgHaVXL/s1600/SubReport-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Group" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjroQNZgNglRLrLw1IervuuNhVRAI9GdJl1VkHmMVcJwJQBngmxinmecDKHN64uedQpS1NuvYk6pRoQxlsRtcz1Eq7D_VTWZ562S2zinqdQ1oqHwVl0XVYJsedy4yx4CopJqM54pfgHaVXL/s1600/SubReport-10.PNG" title="Add Group" /></a></div>
<br />
<ul style="text-align: left;">
<li>Merge all the cells</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8vR9g927gQJ3DL32T8SYoqKRBqnZPRmWZ8ssRXp5oPn6sLDCtL8vVSSI5fmhOdp046oMXN0PW7Sw-8SDU4Q3QQbFd5bUKmhAquEyBMYb9aK0rLxrKX9ZsFpbvS4xyFZoAz_AFJCDl6fq3/s1600/SubReport-11.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8vR9g927gQJ3DL32T8SYoqKRBqnZPRmWZ8ssRXp5oPn6sLDCtL8vVSSI5fmhOdp046oMXN0PW7Sw-8SDU4Q3QQbFd5bUKmhAquEyBMYb9aK0rLxrKX9ZsFpbvS4xyFZoAz_AFJCDl6fq3/s1600/SubReport-11.PNG" /></a></div>
<br />
<ul style="text-align: left;">
<li>Right Click on the <b>Group</b> and select <b>Insert>Subreport</b></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs1z3VGMBl3Bbt1xxKK8jsEgWjNGcCTH4kycVUgr-yjSz-A9ylsO53netL-mXUAyWM_3eVaNiPk_C7sHB3Ef_eaBu9Rf_5k0vxoKA9rbmStXrWZ3UEDYoYvNqKL8Dz-REOZgK0Hlx_IobT/s1600/SubReport-12.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Insert Sub Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs1z3VGMBl3Bbt1xxKK8jsEgWjNGcCTH4kycVUgr-yjSz-A9ylsO53netL-mXUAyWM_3eVaNiPk_C7sHB3Ef_eaBu9Rf_5k0vxoKA9rbmStXrWZ3UEDYoYvNqKL8Dz-REOZgK0Hlx_IobT/s1600/SubReport-12.PNG" title="Insert Sub Report" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxD27fDMDltiMNlGDYFCGL2KeZmfsXipyQm359XD0RsLclvIxLAvbARpAdhSKj2afH5dGLjMdM6TCusErxUAMnvlVMWP2S_NUoBRXnur58_zzDrhzf8qt2gkonRFdQ2i7j1s6FeOJln5T-/s1600/SubReport-13.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Insert Sub Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxD27fDMDltiMNlGDYFCGL2KeZmfsXipyQm359XD0RsLclvIxLAvbARpAdhSKj2afH5dGLjMdM6TCusErxUAMnvlVMWP2S_NUoBRXnur58_zzDrhzf8qt2gkonRFdQ2i7j1s6FeOJln5T-/s1600/SubReport-13.PNG" title="Insert Sub Report" /></a></div>
<br />
<ul style="text-align: left;">
<li>Right Click <b>SubReport Section</b> > <b>Sub Report Properties</b>. In the pop window change the Name of <b>Sub Report</b> to <b>SalesDetails</b> and select <b>DailySalesReport</b> as below.</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYYKqp44aYIUJr40rEwYUL1VGusoWzKKJ3KTd89c8xKPLs1u5KuFYJTWOb4GQuDhwEQY9eSUvhwu2fRaPkxawciYcsCDMuzMEoOUjjjm8bB_0VsbzLDoXCBm_I5XO3Wp1RRVCinSukOSWg/s1600/SubReport-14.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Name the Sub Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYYKqp44aYIUJr40rEwYUL1VGusoWzKKJ3KTd89c8xKPLs1u5KuFYJTWOb4GQuDhwEQY9eSUvhwu2fRaPkxawciYcsCDMuzMEoOUjjjm8bB_0VsbzLDoXCBm_I5XO3Wp1RRVCinSukOSWg/s1600/SubReport-14.PNG" title="Name the Sub Report" /></a></div>
<br />
<b><u>Step 5: Connect reports using Parameter(s)</u></b><br />
<br />
<ul style="text-align: left;">
<li>Select <b>Parameters</b> Tab and <b>Add Parameter</b> as below</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMLc7i3LrEvJ41erZJOYLTlOp5nofdzttrraWBGhVnuGXfjK985N7gyRs2ZWR6JbmpLfOBrRpNyuhFvbEYtcDFP-pbObWydKKygd7l65oMJCxCBc2dnnloyh7fJpup4LKkGLmTSGFGigtp/s1600/SubReport-15.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMLc7i3LrEvJ41erZJOYLTlOp5nofdzttrraWBGhVnuGXfjK985N7gyRs2ZWR6JbmpLfOBrRpNyuhFvbEYtcDFP-pbObWydKKygd7l65oMJCxCBc2dnnloyh7fJpup4LKkGLmTSGFGigtp/s1600/SubReport-15.PNG" title="Add Parameter" /></a></div>
<br />
We are done with creating the <b>Sub Report</b>. Now lets run the report and see the output.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZnGzbUi2RC_6gFMtBOgZjeJ9GNjvgumqmudATAgpc4yhRxbm-lfil_5dDaf3ob6efh04NpV34RRzi6NCZhOrlBMOVyXHe8_24XNSso-PVrRsvq4hRgOzLKHDCHzsWwzDX9-H-6ilYNZq/s1600/SubReport-16.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZnGzbUi2RC_6gFMtBOgZjeJ9GNjvgumqmudATAgpc4yhRxbm-lfil_5dDaf3ob6efh04NpV34RRzi6NCZhOrlBMOVyXHe8_24XNSso-PVrRsvq4hRgOzLKHDCHzsWwzDX9-H-6ilYNZq/s1600/SubReport-16.PNG" /></a></div>
<br /></div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-21915495196837010892015-11-24T11:34:00.000+05:302016-02-16T13:53:49.648+05:30New Features in SQL Server 2016<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Always Encrypted</b><br />
<br />
<b>Always Encrypted</b> feature is designed to protect the data. With this feature enabled data is always be encrypted within SQL Server. Access to encrypted data is only available to the applications calling SQL Server. This enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. Encryption and decryption of data happens transparently inside the application. This means the data stored in SQL Server will be encrypted which can secure it from DBA and administrators but that also has considerations for ad-hoc queries, reporting and exporting the data.<br />
<br />
<b>PolyBase</b><br />
<br />
<b>PolyBase</b> allows you to query distributed data sets. With this feature you will be able to use T-SQL statements to query Hadoop or SQL Azure blob storage. By using PolyBase you can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage. This allows you to get data from Hadoop without knowing the internals of Hadoop. Additionally you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data. As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data.<br />
<br />
<b>JSON Support</b><br />
<br />
<b>JSON</b> stands for Java Script Object Notation. JSON (JavaScript Object Notation) is a standardized data exchange format that is currently not supported natively by SQL Server. With JSON Support now you can interchange JSON data between applications and the SQL Server database engine. By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format. Additionally, with JSON support you can take relational data, and turn it into JSON formatted data. Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server. Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server<br />
<br />
<b>Multiple TempDB Database Files</b><br />
<br />
It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine. In the past, up through SQL Server 2014, you always had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server. Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.<br />
<br />
<b>Query Store</b><br />
<br />
One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.<br />
<br />
<b>Row Level Security</b><br />
<br />
A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID. I've seen some customized implementations of RLS at clients in the past, and they weren't pretty. It is hard to execute at scale. The implementation of RLS in SQL 2016 still has it limits (updates and inserts are not covered), but it is good start on a much-needed feature.<br />
<b><br /></b>
<b>Temporal Table</b><br />
<br />
A temporal table is table that holds old versions of rows within a base table. By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated. The temporal table is physically a different table then the base table, but is linked to the base table. If you’ve been building or plan to build your own method to managing row versioning then you might want to check out the new temporal tables support in SQL server 2016 before you go forth and build your own row versioning solution.<br />
<div>
<br /></div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-2656007440717446752015-11-18T15:05:00.003+05:302023-02-09T18:18:29.468+05:30Matrix Report in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
In this tutorial, we are demonstrating step by step example of creating a simple <b>Matrix Report</b> in <b>SSRS</b>.<br />
<br />
<b>Need of Matrix Report?</b><br />
Matrix Reports are required where you have to display some aggregated data and at least one of grouping column need to displayed as column in the report. It is something similar to Pivot we use in normal T-SQL where we convert <a href="http://www.itdeveloperzone.com/2011/11/dynamic-pivot-in-sql-server.html" target="_blank"><b>rows to columns</b></a> using Pivot.<br />
<br />
Now let's have a walk-through of a Matrix Report<br />
<br />
<b>Example:</b><br />
We will create a matrix report where sum of sales will be displayed Year and Product wise in a matrix form and Year will be displayed as columns (similar to pivot in SQL)<br />
<br />
We would be using Shared Data source we created in previous article: <b>Shared Data Source</b><br />
<br />
<b>Step 1: <a href="http://www.itdeveloperzone.com/2015/11/create-shared-dataset-ssrs.html" target="_blank">Create a Shared Data Source</a></b><br />
We would be using this shared data source to fetch the data from the database for the report.<br />
<br />
<b>Step 2:</b> Right Click <b>Reports>Add New Report</b>, Report Wizard will appear to configure the report. Click <b>Next</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add New Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" title="Add New Report" /></a></div>
<br />
<b>Step 3:</b> Configure <b>Data Source</b>. Select the Shared Data Source we created in <b>Step 1</b> and Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Data Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" title="Configure Data Source" /></a></div>
<br />
<b>Step 4: Design Query</b> either thru <b>Query Builder</b> or write your own query and Click <b>Next</b>.<br />
<!--INFOLINKS_OFF-->
<pre class="brush :sql">select P.ProductNumber, P.Name as ProductName
, YEAR(SOH.OrderDate) as Year, SOD.LineTotal as SaleAmount
from Sales.SalesOrderDetail SOD
inner join Sales.SalesOrderHeader SOH on SOH.SalesOrderID = SOD.SalesOrderID
inner join Production.Product P on P.ProductID = SOD.ProductID</pre>
<!--INFOLINKS_ON-->
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYguqd-t1p7EQC-7kMkj059y_FvnwV2M4caRwQSpoCP334iU1gsG8-lJK_K6G0u48CbbSf5mEfdDyiBMsiyJ_Wx8niXP-Vz3rNWbg_FZbDwxOe9KStvqrFs94uOf3iSuqe6-NhOvscV7dk/s1600/Matrix_Report_01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Design Query" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYguqd-t1p7EQC-7kMkj059y_FvnwV2M4caRwQSpoCP334iU1gsG8-lJK_K6G0u48CbbSf5mEfdDyiBMsiyJ_Wx8niXP-Vz3rNWbg_FZbDwxOe9KStvqrFs94uOf3iSuqe6-NhOvscV7dk/s1600/Matrix_Report_01.PNG" title="Design Query" /></a></div>
<br />
<b>Step 5:</b> Select <b>Matrix</b> as <b>Report Type</b> and click <b>Next</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY-AbefQgMf2GJB1me5aF38T_C5UeO-KcfI7geHT1yr8rO2YpAUW7HYFNvOSUDuWebVtyFVWUO1qxRgRPfxX1pn14OZUCDgkt3sol6U8oBANWHIWjRgv32aSR8bkOBJ-j1rk9JSHym2wb9/s1600/Matrix_Report_02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Type" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY-AbefQgMf2GJB1me5aF38T_C5UeO-KcfI7geHT1yr8rO2YpAUW7HYFNvOSUDuWebVtyFVWUO1qxRgRPfxX1pn14OZUCDgkt3sol6U8oBANWHIWjRgv32aSR8bkOBJ-j1rk9JSHym2wb9/s1600/Matrix_Report_02.PNG" title="Select the Report Type" /></a></div>
<br />
<br />
<b>Step 6:</b> All available field will be displayed in Available fields Section. Configure the fields like below by clicking on Columns, Rows & Details buttons respectively.<br />
<ul style="text-align: left;">
<li>Year Field moved to Columns as it is to be displayed as Column </li>
<li>ProductNumber & ProductName Fields moved to Rows as they are to be displayed as Rows </li>
<li>SalesAmount to Details as it is to be aggregated based on Year and Product. </li>
</ul>
Once Configured Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXXpjJqTOlAhbM7_aphmmu9UQcZ-9kfVCG_PgxRgD0DdI10L_p5jpAFzxLOrJIOYvCGsOuRPy3sBkKcGYxCdCXz5P8q36DkwlFkEPcZh363zyKMX_H1P8rooKCnZVIqIGP1Z-DWRDxS62Y/s1600/Matrix_Report_03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Matrix" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXXpjJqTOlAhbM7_aphmmu9UQcZ-9kfVCG_PgxRgD0DdI10L_p5jpAFzxLOrJIOYvCGsOuRPy3sBkKcGYxCdCXz5P8q36DkwlFkEPcZh363zyKMX_H1P8rooKCnZVIqIGP1Z-DWRDxS62Y/s1600/Matrix_Report_03.PNG" title="Design the Matrix" /></a></div>
<br />
<b>Step 7:</b> Here, you can select <b>Table Style</b>. Default Style is Slate. Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Table Style" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" title="Choose the Table Style" /></a></div>
<br />
<b>Step 8:</b> At this stage, we are done with configuration of reports. Change the Report Name to YearProductWiseSale and Click <b>Finish</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjC9KByWe-H2uS80gAFPDqy7_3rhdfgrgvljz32qv34ED2k-G4vRyswu-uNV9CJVlAidytekgdyGiJbVKI9YPRTSCAPX-3QVJiItAf7vb70rc9xoIGYxIBrKmr14ZTkcmWOy4kDdEdyz0TL/s1600/Matrix_Report_04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Name" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjC9KByWe-H2uS80gAFPDqy7_3rhdfgrgvljz32qv34ED2k-G4vRyswu-uNV9CJVlAidytekgdyGiJbVKI9YPRTSCAPX-3QVJiItAf7vb70rc9xoIGYxIBrKmr14ZTkcmWOy4kDdEdyz0TL/s1600/Matrix_Report_04.PNG" title="Set Report Name" /></a></div>
<br />
<b>Step 9:</b> Click Finish will create a report. You can configure design part like color, height, width etc..manually<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEij4OsaQGOk3RAB3Hn5ExbqmcNY-8n8YzrBfPz59Xmh00K3MPxoTYwLeYHfmkGNjmFr4BTXZRfwamR-RWMsyl3cC0FvTqlS-WWNz7J4EVNZgmYGtB2LhZE2D_TOqObDr9xM0174-6W3atkR/s1600/Matrix_Report_05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Design Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEij4OsaQGOk3RAB3Hn5ExbqmcNY-8n8YzrBfPz59Xmh00K3MPxoTYwLeYHfmkGNjmFr4BTXZRfwamR-RWMsyl3cC0FvTqlS-WWNz7J4EVNZgmYGtB2LhZE2D_TOqObDr9xM0174-6W3atkR/s1600/Matrix_Report_05.PNG" title="Design Report" /></a></div>
<br />
We are done with creating a Simple <b>Matrix Report</b>. Let's run the report and see the output.<br />
<br />
To run the report, set this report as <b>StartItem</b> in <b>Project Properties</b>.<br />
<br />
Right Click <b>Project>Properties</b>. Select the report we just created as <b>StartItem</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi3Grarqm-fzCCYZSwgZHeEEm4bKo0WIsVTIf0jJANMJrKCSIMlawPHyGoEqTWiYweEenFw_OfEgLhBML_d9YsPSLkkPS7ulXsvGUwX23SjNl-yGa8cPC5F3i5iZwITatDo2iag8WAU54P/s1600/Matrix_Report_06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="StartItem" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi3Grarqm-fzCCYZSwgZHeEEm4bKo0WIsVTIf0jJANMJrKCSIMlawPHyGoEqTWiYweEenFw_OfEgLhBML_d9YsPSLkkPS7ulXsvGUwX23SjNl-yGa8cPC5F3i5iZwITatDo2iag8WAU54P/s1600/Matrix_Report_06.PNG" title="Set Report as StartIem" /></a></div>
<br />
<b>Now let's run the project and see the output of the report</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzm7AjNuJVuFBpNS1eQW2QfxeRBtVCeFJCL-3giV4ciK88h4-Gbi0r3uxdMPuRJYnc8QXEYeVfeiN-fs0J4aPOxf1jD-Q4zFDumeefHADgzng1ynDuQJ3mA2NShEaZVCE2pLWngEypv1vv/s1600/Matrix_Report_07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzm7AjNuJVuFBpNS1eQW2QfxeRBtVCeFJCL-3giV4ciK88h4-Gbi0r3uxdMPuRJYnc8QXEYeVfeiN-fs0J4aPOxf1jD-Q4zFDumeefHADgzng1ynDuQJ3mA2NShEaZVCE2pLWngEypv1vv/s1600/Matrix_Report_07.PNG" title="Report Output" /></a></div>
<br />
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-80394106838429777662015-11-15T12:55:00.005+05:302023-07-19T19:15:48.427+05:30Query Parameter in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Query Parameters</b> are used to filter the result set return from the database similar to where condition applied in a T-SQL statement. It limits the number of rows return back from the database to the report, thus improving the performance.<br />
<br />
Let's create a report to see how Query Parameters work.<br />
<br />
For this, we will apply <b>Query Parameter</b> on a <b><a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank">Tabular Report</a></b> we created in an earlier article.<br />
<br />
<b>Step 1: <a href="http://www.itdeveloperzone.com/2015/11/creating-tabular-report-in-ssrs.html" target="_blank">Create a simple Tabular Report</a></b><br />
First Create a simple Tabular Report using above link. Once report is created we would apply <b>Query Parameters</b> on the report<br />
<br />
<b>Step 2:</b> On <b>Report Data</b> Tab, Click <b>Parameters>Add Parameter</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXwmPLD56hdoZKavP002Pp_Clf-ZpMbFpIuZCWpjiCMU-SZqyhuUMWGyGiYj8_6fPA5NbiEjT_JqwT70CK6bIHaHzc96PmFCjE6sg-Wsw7Zr896q2ur2mnFXssRLSC4yjiMrP0eEw_8ql/s1600/Query+Parameter-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibXwmPLD56hdoZKavP002Pp_Clf-ZpMbFpIuZCWpjiCMU-SZqyhuUMWGyGiYj8_6fPA5NbiEjT_JqwT70CK6bIHaHzc96PmFCjE6sg-Wsw7Zr896q2ur2mnFXssRLSC4yjiMrP0eEw_8ql/s1600/Query+Parameter-01.PNG" title="Add Parameter" /></a></div>
<br />
Configure <b>Report Parameter Properties</b> as below and click <b>OK</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_eXTMtBMORo-jYKwHOPTstqm1ORWeX5MMIhK3938ORKyN8sP4GjFbBoiizBERYJa0znCsnCZs4u-RhoovqK7ySpo7xpvC8LNcewxFNEsbGh3lY4jHajT8YzYyUABsHs1bhJbc9FFs8qie/s1600/Query+Parameter-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Parameter Properties" border="0" height="264" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_eXTMtBMORo-jYKwHOPTstqm1ORWeX5MMIhK3938ORKyN8sP4GjFbBoiizBERYJa0znCsnCZs4u-RhoovqK7ySpo7xpvC8LNcewxFNEsbGh3lY4jHajT8YzYyUABsHs1bhJbc9FFs8qie/s320/Query+Parameter-02.PNG" title="Configure Report Parameter Properties" width="320" /></a></div>
<br />
<b>Step 3:</b> This will add <b>GroupName Parameter</b> under <b>Parameters</b> like below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3J9zBBsJIj1uon8pQ09mbWiR_khCD5dXZ4o4No25tmFpWkVLVyUxBbz0nw9Pemq2hJSKlHeRBCKzlMG9uBzras_bEY0z0RdT2JC7VeiH8pcqIrc1odeXKvWPfhUpe1ObVCgH0NmN6SN_I/s1600/Query+Parameter-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Parameter" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3J9zBBsJIj1uon8pQ09mbWiR_khCD5dXZ4o4No25tmFpWkVLVyUxBbz0nw9Pemq2hJSKlHeRBCKzlMG9uBzras_bEY0z0RdT2JC7VeiH8pcqIrc1odeXKvWPfhUpe1ObVCgH0NmN6SN_I/s1600/Query+Parameter-03.PNG" title="Parameter" /></a></div>
<b>Step 4:</b> Now let's apply the <b>Parameter</b> on <b>DataSet</b> to filter the result-set. In <b>Report Data</b> Tab right click <b>DataSet1</b> under <b>Datasets</b> and then click <b>Dataset Properties</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1NN8HI1Ttc9XUHo4co-gJetFSFVeXqEbo8xH_vCvBC-hqwg-pSUJzyC_5EYlg2ob_pqd7r5ggNhe_sruH8MiudQ0UA08pBnJn03laWE__Vx-ufR2uCzlHP43CIBBWf3KSm1LuneezChIV/s1600/Query+Parameter-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Apply Parameter on Dataset" border="0" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1NN8HI1Ttc9XUHo4co-gJetFSFVeXqEbo8xH_vCvBC-hqwg-pSUJzyC_5EYlg2ob_pqd7r5ggNhe_sruH8MiudQ0UA08pBnJn03laWE__Vx-ufR2uCzlHP43CIBBWf3KSm1LuneezChIV/s320/Query+Parameter-04.PNG" title="Apply Parameter on Dataset" width="320" /></a></div>
<br />
<b>Step 5:</b> In <b>Query</b> Tab under <b>Dataset Properties</b>, change the Query to
<br />
<pre class="brush :sql">select * from HumanResources.Department where GroupName = @GroupName</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNvofHJabtwk5VR0u15mXnD97j-1RG2lJNT9CDkwocwpgM8NWN0d8sUfRsj3UmoprS44vzFYI0h30nW7zbfZ2jtuKCG8KSn0Z64ngz4RFcd4I3zH0MQYbLqTu22k44f9jQUmHAun-lwlYF/s1600/Query+Parameter-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Set Query" border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNvofHJabtwk5VR0u15mXnD97j-1RG2lJNT9CDkwocwpgM8NWN0d8sUfRsj3UmoprS44vzFYI0h30nW7zbfZ2jtuKCG8KSn0Z64ngz4RFcd4I3zH0MQYbLqTu22k44f9jQUmHAun-lwlYF/s320/Query+Parameter-05.PNG" title="Set Query" width="312" /></a></div>
<br />
<b>Step 6:</b> Select <b>Parameters</b> Tab, you will find <b>Parameter Name @GroupName</b> that we added in the query in Where clause in previous Step. Select <b>Parameter Value</b> as <b>@GroupName</b> from the list (<b>created in step 3</b>) and click OK<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgP5T5cXMMhYs82CnsMWfWzoJiVrGQSum60E9rKv-8KrNWupzXAr5gaKDEs2V6RscBZlPighRwBLDF3PwiIWQMVGlc5N53E1XXXzq7jzN7bpu349F9ZyHynaMQS9hyOcDjh2K_V2KtjRZ8u/s1600/Query+Parameter-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Set Parameter" border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgP5T5cXMMhYs82CnsMWfWzoJiVrGQSum60E9rKv-8KrNWupzXAr5gaKDEs2V6RscBZlPighRwBLDF3PwiIWQMVGlc5N53E1XXXzq7jzN7bpu349F9ZyHynaMQS9hyOcDjh2K_V2KtjRZ8u/s320/Query+Parameter-06.PNG" title="Set Parameter" width="312" /></a></div>
<br />
<b>Step 7:</b> At this step we are done with adding <b>Query Parameter</b> to the <b>Report</b>. Now let's build the Solution and run the report. The Parameter <b>Group Name</b> that we created started appearing in the report to filter the result set. Enter <b>Manufacturing</b> in the text-box and click <b>View Report</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEX8_rTs8GWWHwAjPzpM64xxRTcSxGHhIGjeBtOGt4bWKBB-_8y0eeWy8hzUYExx5T1AYFWRAYgS1rPpaA6Mbzkv2Jv2xmYw92vu5_JMcCOQMoKrHLA9ttKbCoMQEXzrHzewr6_JeKjStn/s1600/Query+Parameter-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Output" border="0" height="164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEX8_rTs8GWWHwAjPzpM64xxRTcSxGHhIGjeBtOGt4bWKBB-_8y0eeWy8hzUYExx5T1AYFWRAYgS1rPpaA6Mbzkv2Jv2xmYw92vu5_JMcCOQMoKrHLA9ttKbCoMQEXzrHzewr6_JeKjStn/s400/Query+Parameter-07.PNG" title="Report Output" width="400" /></a></div>
<br />
Look at the output, the report is filtered with <b>Group Name</b> as <b>Manufacturing</b></div>
<br/>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-54124302584365677372015-11-14T13:51:00.004+05:302016-04-06T15:22:29.863+05:30Creating Tabular Report in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
In this tutorial, we will create a Simple <b>Tabular Report</b> in <b>SSRS</b>.<br />
<br />
We would be using Shared Data source we created in previous article: <b><a href="http://www.itdeveloperzone.com/2015/11/create-shared-dataset-ssrs.html" target="_blank">Shared Data Source</a></b><br />
<br />
<b>Step 1:</b> <b><a href="http://www.itdeveloperzone.com/2015/11/create-shared-dataset-ssrs.html" target="_blank">Create a Shared Data Source</a></b><br />
We would be using this shared data source to fetch the data from the database for the report.<br />
<br />
<b>Step 2:</b> Right Click <b>Reports</b> and then <b>Add New Report</b>, Report Wizard will appear to configure the report. Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8wkmzZg_iFi-NnHrdfRFYrrRFpdvGiuZi3weMz_uQ_VUDK7GWknmh0Ys1tVCBH0EatJIBCj2zRobkLd5fZlArmNiatuztX3CxFYPxCeXFnC8L1cyjdDY8QL8eHSBISEL7xgz_aWWuvAOO/s1600/Tabular+Report-02.PNG" title="Add Report" /></a></div>
<br />
<b>Step 3:</b> Configure <b>Data Source</b>. Select the Shared Data Source we created in <b>Step 1</b> and Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Configure Data Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEsrze_7zEUnurO4n9Of1Rad3hGugdb2QR40ESyprz2cTkjpm95sz1gHvwj1Naj59ve0aY7PQzssIyqJBgQkQqoXJdXsE_NdDK22oy5_j1OeJZmDZkj8zyCzVgMDabuVhTKWvaDjhM-nRQ/s1600/Tabular+Report-03.PNG" title="Configure Data Source" /></a></div>
<br />
<b>Step 4:</b> <b>Design Query</b> either thru <b>Query Builder</b> or write your own query and Click <b>Next</b>.
<br />
<!--INFOLINKS_OFF-->
<pre class="brush :sql">select * from HumanResources.Department</pre>
<!--INFOLINKS_ON-->
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKRTcsFxv1nhydHSTQJ5P8sxu-eimkCQYwbUI2J57EyLneMZ_DLlUuBWP3WYT7OGWc_57O6TiAioEKE4xqvEjgGEoyXZ-UxlckrcVmJIHUwOapQ_JQKMdEyMV44MPmrN_0huU6LneI2O4U/s1600/Tabular+Report-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Design Query" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKRTcsFxv1nhydHSTQJ5P8sxu-eimkCQYwbUI2J57EyLneMZ_DLlUuBWP3WYT7OGWc_57O6TiAioEKE4xqvEjgGEoyXZ-UxlckrcVmJIHUwOapQ_JQKMdEyMV44MPmrN_0huU6LneI2O4U/s1600/Tabular+Report-04.PNG" title="Design Query" /></a></div>
<br />
<b>Step 5:</b> Select <b>Tabular</b> as <b>Report Type</b> and click <b>Next</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTLfT1CjxCiItS8MNYrjsNcBgJSAO4GMlywmkOCnXmlVrx-QCHB6DL_WsXbG3kFhRo0AUEGcDpEv45wjFwVa_Yd5wst3U6KwEQebBc9HJWsbaf9NIsFCsgdbcc4k_GZc_BDspTkuE9E06l/s1600/Tabular+Report-05.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Type" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTLfT1CjxCiItS8MNYrjsNcBgJSAO4GMlywmkOCnXmlVrx-QCHB6DL_WsXbG3kFhRo0AUEGcDpEv45wjFwVa_Yd5wst3U6KwEQebBc9HJWsbaf9NIsFCsgdbcc4k_GZc_BDspTkuE9E06l/s1600/Tabular+Report-05.PNG" title="Select the Report Type" /></a></div>
<br />
<b>Step 6:</b> You can configure to display the data based on grouping but as of now we are working on a simple tabular report, so just click Finish.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJu-hKIy3JCxQIdLAT0lllWTxexuWYL5iPIFMYxpLMPX3rVWr-g9wzN6LEk_LfbbZMPwa6yMdQQdmggm8WZKukmR97XuTWAr_WAlsDBhzQAix5DU7FkfrvVz6B4f7kBHeJieU5FG2B_HrW/s1600/Tabular+Report-06.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Table" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJu-hKIy3JCxQIdLAT0lllWTxexuWYL5iPIFMYxpLMPX3rVWr-g9wzN6LEk_LfbbZMPwa6yMdQQdmggm8WZKukmR97XuTWAr_WAlsDBhzQAix5DU7FkfrvVz6B4f7kBHeJieU5FG2B_HrW/s1600/Tabular+Report-06.PNG" title="Design the Table" /></a></div>
<br />
<b>Step 7:</b> Here, you can select <b>Table Style</b>. Default Style is Slate. Click <b>Next</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Table Style" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixnLJ3MqBP9wGcEkOeyHYrqwEnQPoEPBbbM3NqZhnw5hChCeO61ZjLSZCerTHUgbEd3xmlB3WPeIv7vXNamKhLbs5byUsLi2jP8V5GG1erEXu5mqrpbEHixglLhHinV_dYsaur6nJ1HHyK/s1600/Tabular+Report-07.PNG" title="Choose the Table Style" /></a></div>
<br />
<b>Step 8:</b> At this stage, we are done with configuration of reports. Click <b>Finish</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg93wIB7JNzXLGpP_9qhyphenhyphenX-Vm0e0u27WvU_wrHr2Ucjaxg71ASLYTGLnovAT3WECbu6JGSv5KT6dGc8Lp6pzFda0yWySUBP3AC5LIWGO6Jb1Y6kbJOI1oUDinlpUdWrTXD0WavvfxVXf8gY/s1600/Tabular+Report-08.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Name" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg93wIB7JNzXLGpP_9qhyphenhyphenX-Vm0e0u27WvU_wrHr2Ucjaxg71ASLYTGLnovAT3WECbu6JGSv5KT6dGc8Lp6pzFda0yWySUBP3AC5LIWGO6Jb1Y6kbJOI1oUDinlpUdWrTXD0WavvfxVXf8gY/s1600/Tabular+Report-08.PNG" title="Set Report Name" /></a></div>
<br />
<b>Step 9:</b> Click <b>Finish</b> will create a report. You can also configure color, height, width etc..manually<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUc3N4iYz5M62lff2T8-4Kn1BAkKRUS4IXfQIcwRu3GX5CLAkptBxLBfbZW2ezNDI-xcNf-aQwZX0kxDHizavthydgFjhHNsQgq_1NX3GM42-VFIRMxrrwg_40PQE9cWC3hbPAlTV2QF5/s1600/Tabular+Report-09.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Desing Report" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUc3N4iYz5M62lff2T8-4Kn1BAkKRUS4IXfQIcwRu3GX5CLAkptBxLBfbZW2ezNDI-xcNf-aQwZX0kxDHizavthydgFjhHNsQgq_1NX3GM42-VFIRMxrrwg_40PQE9cWC3hbPAlTV2QF5/s1600/Tabular+Report-09.PNG" title="Desing Report" /></a></div>
<br />
We are done with creating a Simple <b>Tabular Report.</b><br />
<br />
To run the report, set this report as <b>StartItem</b> in <b>Project Properties</b>.<br />
<br />
Right Click <b>Project>Properties</b>. Select the <b>Report</b> we just created as <b>StartItem</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh68baRNzqmOXLEZyhi8FUrAneh84pHIshKFxH65PhUvcjh9bVmPlnOqkeu2ybLGdik5rZr-YAszzBT0OR5b2gOes1KwMWJ6fZW8Xg0V83Cxfp1yQSCQY51XlJ807NOcyE3aJOhctpke-c4/s1600/Tabular+Report-10.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="StartItem" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh68baRNzqmOXLEZyhi8FUrAneh84pHIshKFxH65PhUvcjh9bVmPlnOqkeu2ybLGdik5rZr-YAszzBT0OR5b2gOes1KwMWJ6fZW8Xg0V83Cxfp1yQSCQY51XlJ807NOcyE3aJOhctpke-c4/s1600/Tabular+Report-10.PNG" title="Set Report as StartItem" /></a></div>
<br />
<b>Now let's run the project and see the output of the report</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpca4SbaEAHOTjfu8Nfl5iunzQla1dFqnaB5ded9ghyphenhyphend50ZGH4S8QGa0_-XZKkXrerjUZLsOLa61sz0NC9ItTMJIh21NjbN0kL3h0qi96o_2FXv7RbIt02GU6zEvfarEmVqsxsW3Uwumb-/s1600/Tabular+Report-11.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Report Output" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpca4SbaEAHOTjfu8Nfl5iunzQla1dFqnaB5ded9ghyphenhyphend50ZGH4S8QGa0_-XZKkXrerjUZLsOLa61sz0NC9ItTMJIh21NjbN0kL3h0qi96o_2FXv7RbIt02GU6zEvfarEmVqsxsW3Uwumb-/s1600/Tabular+Report-11.PNG" title="Report Output" /></a></div>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-88957090505605259962015-11-11T16:23:00.002+05:302018-01-20T12:21:18.976+05:30Create Shared Datasource SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
In this post, we will see how to create a <b>Shared Data Source</b> in <b>SSRS</b>.<br />
<br />
<b>Data Source</b> contain the information necessary to retrieve the data from the relational database to render the reports. If a data source is not shared, it is stored inside a report and can not be used by other reports.<br />
<br />
In general, we need to have a data source that can be used in all the reports in the same project. Such data sources are called as shared data source.<br />
<br />
Now let's walk through how to create a shared data source<br />
<br />
<b>Prerequisites</b><br />
<ul style="text-align: left;">
<li>SQL Server 2012 with Reporting Services</li>
<li>AdventureWorks Database</li>
</ul>
<br />
<div class="separator">
<b>NOTE:</b> We would be using <b>AdventureWorks</b> Sample Database which is shipped with SQL Server 2012 Enterprise Edition. If you have Express Edition you have to download it. <a href="http://www.itdeveloperzone.com/2015/11/adventureworks-database-for-sql-server.html" target="_blank"><b>Download here</b></a>.</div>
<br />
<br />
<b>Step 1: </b> Open <b>SQL Server Data Tools</b> and create a new Project <b>Business Intelligence > Report Server Project</b> and name it as <b>SSRS-Tutorials</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6anjdH3C31-rDSg_rDvqP0haLFkVambS-Ixwq2LiYr6dgijyTBAZjJzalu1j3k0gy9MZ4z2_ZgKKzbG9MwoI2t_UN1HVWaM_ERb0iO43m-LmHQlaO_67v7yiszsqJluiwU-XGiLxwrpx0/s800-Ic42/SharedDatasource-01.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Create Project" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6anjdH3C31-rDSg_rDvqP0haLFkVambS-Ixwq2LiYr6dgijyTBAZjJzalu1j3k0gy9MZ4z2_ZgKKzbG9MwoI2t_UN1HVWaM_ERb0iO43m-LmHQlaO_67v7yiszsqJluiwU-XGiLxwrpx0/s800-Ic42/SharedDatasource-01.PNG" title="Create Project" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<b>Step 2:</b> Right Click <b>Shared Data Source</b>, then <b>Add New Data Source</b>. A new pop pup window will appear to configure <b>Data Source Properties</b>. Change the name of <b>Data Source</b> to AdventureWorks<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkptbs0J1_y0eO1cpOwzTQ7Zb_0-gqt0O3054eprDZ0rL-UyxbyTEHrGjadDrNCbm-iRCb5sHFtpO8CmfT26bNkmr33XC3pAUXkZlg4QqX0zc_Wun-1Huk_n65tHnKOU0rU5JoaAFf8XTW/s1600/SharedDatasource-02.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Data Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkptbs0J1_y0eO1cpOwzTQ7Zb_0-gqt0O3054eprDZ0rL-UyxbyTEHrGjadDrNCbm-iRCb5sHFtpO8CmfT26bNkmr33XC3pAUXkZlg4QqX0zc_Wun-1Huk_n65tHnKOU0rU5JoaAFf8XTW/s1600/SharedDatasource-02.PNG" title="Add Data Source" /></a></div>
<br />
<b>Step 3:</b> Click on <b>Edit</b>, a new pop up window will appear to configure <b>Connection Properties</b> like Server Name, Authentication and Database<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXGVh-wFblvoZo8xOFxEQwQKxkILWmA6m_FRRkup-1NEtpmboO-H3oxkWosMafsbBQ_NsCvmmfdydmxaZK6uIAuGrTaKymcti8rmNi5465N1Yxsx5bh8EBiCBjVOcchVDDSFs70J0AUraX/s1600/SharedDatasource-03.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Connection Properties" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXGVh-wFblvoZo8xOFxEQwQKxkILWmA6m_FRRkup-1NEtpmboO-H3oxkWosMafsbBQ_NsCvmmfdydmxaZK6uIAuGrTaKymcti8rmNi5465N1Yxsx5bh8EBiCBjVOcchVDDSFs70J0AUraX/s1600/SharedDatasource-03.PNG" title="Configure Connection Properties" /></a></div>
<br />
<b>Step 4:</b> Click <b>OK</b> twice to close the popup windows and <b>Shared Data Source</b> AdventureWorks is created.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg44vdAdINatsBrd9f6TRkFtQlsOSZcdLNv6A7MpuV6MnHN4YgY-i_VVKW9QyFN26xZNYWB9gWJtKFVmNnkGEWcHHE4rkDIsARvOqqEdra1h2rJl-NNT9-QidbWCvwbJC94Iwbng9ax1K5m/s1600/SharedDatasource-04.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Shared Data Source" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg44vdAdINatsBrd9f6TRkFtQlsOSZcdLNv6A7MpuV6MnHN4YgY-i_VVKW9QyFN26xZNYWB9gWJtKFVmNnkGEWcHHE4rkDIsARvOqqEdra1h2rJl-NNT9-QidbWCvwbJC94Iwbng9ax1K5m/s1600/SharedDatasource-04.PNG" title="Shared Data Source" /></a></div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-86610607140342372492015-11-11T15:40:00.002+05:302016-02-21T16:33:27.532+05:30AdventureWorks Database for SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
AdventureWorks Sample Database for SQL Server 2012 can be downloaded from CodePlex site using below link.<br />
<br />
<b><a href="http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=353205" target="_blank">Download AdventureWorks2012 Data File (Case Sensitive)</a></b><br />
<br />
The data file name is <b>AdventureWorks2012_CS_Data.mdf</b> as the <b><a href="http://www.itdeveloperzone.com/2013/06/collation-in-sql-server.html" target="_blank">collation</a></b> of database is case sensitive.<br />
<br />
you can download case insensitive version from the below link:<br />
<br />
<b><a href="http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=165399" target="_blank">Download AdventureWorks2012 Data File (Case Insensitive)</a></b><br />
<br />
Once the data file is downloaded, run below script to attach the downloaded AdventureWorks Data File to SQL Server 2012<br />
<pre class="brush :sql">CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\SampleDatabases\AdventureWorks2012_CS_Data.mdf')
FOR ATTACH_REBUILD_LOG ;</pre>
<br />
In the script change, you need to change your path in <b>FILENAME</b> where you have placed the AdventureWorks Data File.</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-11848952764509207932015-11-10T13:10:00.000+05:302016-04-13T11:35:55.219+05:30SQL Server 2016 CTP 3.0 download<div dir="ltr" style="text-align: left;" trbidi="on">
Microsoft has recently announced the Community Technology Preview 3.0 (CTP 3.0) update of SQL Server 2016.<br />
<br />
Using Microsoft Live Account, you can login and download the CTP 3.0 Evaluation version using any of the below links. If you don't have Live account, you can register one.<br />
<br />
<ul style="text-align: left;">
<li><a href="https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016" target="_blank">Download the CTP 3 Evaluation version</a></li>
<li><a href="https://technet.microsoft.com/en-in/evalcenter/mt130694.aspx" target="_blank">Download the CTP 3 Evaluation version</a></li>
</ul>
<br />
Alternatively, you can download directly using below link<br />
<br />
<b>Direct download link: </b><a href="http://care.dlservice.microsoft.com/dl/download/2/5/0/250DA919-7FD9-44AA-9E57-A53E3E076B34/SQLServer2016CTP3.0-x64-ENU.iso">SQLServer2016CTP3.0-x64-ENU.iso</a><br />
<br />
Using DVD burning software, burn this ISO file on to DVD. Then, run setup.exe file to start the installation.<br />
<br />
Alternatively, you can install software that can mount your ISO file. Once mounted, a new drive will start appearing in "My Computer" (similar to when you plugin Pen Drive)<br />
<br />
<b>Note :</b> You must have administrative rights on the computer to install SQL Server 2016 CTP 3.0.<br />
<b><br /></b>
<b>Key Notes :</b><br />
<br />
<ul style="text-align: left;">
<li>The release is not supported by Microsoft Customer Services and Support (CSS).</li>
<li>The release is available for testing purposes only and should not be installed and used in production environments.</li>
<li>Side-by-Side installation with down-level production SQL Server instances as well as in-place upgrades of down-level production SQL Server instances, is supported for SQL Server 2008 and higher.</li>
</ul>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-38682790292661132302015-11-08T16:07:00.000+05:302016-10-31T13:31:26.171+05:30SQL Server Interview Questions (Part-4)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="clear: both; height: 0px; text-align: center; width: 0px;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" width="0" /></a></div>
<b>What is a transaction?</b><br />
A <b><a href="http://www.itdeveloperzone.com/2013/07/transaction-in-sql-server.html" target="_blank">transaction</a></b> is a logical unit of work in which, all the steps must be performed or none.<br />
<a href="http://www.itdeveloperzone.com/2013/07/transaction-in-sql-server.html" target="_blank"><b>Read more in detail...</b></a><br />
<br />
<b>What are ACID properties</b><br />
ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.<br />
<br />
<b>Difference Between Implict Transaction And Explict Transaction</b><br />
Implicit Transaction is auto commit and there is no beginning or ending of the transaction.<br />
Explicit Transaction has the beginning, ending and rollback of transactions with the command<br />
Begin Transaction, Commit Transaction and Rollback Transation<br />
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.<br />
<b><br /></b>
<b>What is the use of @@TRANCOUNT in SQL Server?</b><br />
@@TRANCOUNT returns the number of active transactions for the current connection.<br />
<br />
<b>What is a deadlock?</b><br />
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.<br />
<br />
<b>What is NOLOCK?</b><br />
NOLOCK is used to query data which is affected by the transaction but yet committed. Querying uncommited data using NOLOCK is called "dirty read"<br />
<br />
<b>How to Get nth Record in a Table?</b><br />
Refer this post : <a href="http://www.itdeveloperzone.com/2012/01/find-nth-highest-salary-in-sql.html" target="_blank"><b>find nth highest salary</b></a><br />
<br />
<b>How to generate Random number in SQL Server?</b><br />
Refer this post : <b><a href="http://www.itdeveloperzone.com/2011/11/generate-random-row-in-sql.html" target="_blank">generate random number for each row in SQL</a></b><br />
<br />
<b>How to delete duplicate record in SQL?</b><br />
Refer this <b><a href="http://www.itdeveloperzone.com/2011/05/delete-duplicate-records-in-sql-server.html" target="_blank">post : delete duplicate records in SQL</a></b><br />
<br />
<b>How do we find the last date of current month?</b><br />
SQL Server 2012 introduced function <a href="http://www.itdeveloperzone.com/2015/10/eomonth-last-day-of-month-in-sql-server.html" target="_blank"><b>EOMONTH</b></a> to find the last date of the month<br />
In prior versions, we have to write our own logical script<br />
Refer this post : <a href="http://www.itdeveloperzone.com/2015/10/eomonth-last-day-of-month-in-sql-server.html" target="_blank"><b>Last day of Month</b></a><br />
<br />
<b>What is computed column in SQL Server?</b><br />
<b><a href="http://www.itdeveloperzone.com/2013/04/computed-column-in-sql-server.html" target="_blank">Computed Column</a></b> is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators. Computed columns can be used in select list, where clause, order by clause.<br />
<b><a href="http://www.itdeveloperzone.com/2013/04/computed-column-in-sql-server.html" target="_blank">Read more in detail with example...</a></b><br />
<br />
<b>What is the use of @@ROWCOUNT?</b><br />
@@ROWCOUNT returns the number of row(s) affected as a result of DML statement like Insert/Update/Delete.<br />
<br />
<div style="width: 100%;">
<span>
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-3.html"><< Prev</a>
</span>
</div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-84384856639074194082015-11-08T16:06:00.005+05:302016-10-31T13:30:32.678+05:30SQL Server Interview Questions (Part-3)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="clear: both; height: 0px; text-align: center; width: 0px;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" width="0" /></a></div>
<b>What is user-defined function</b><br />
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.<br />
<br />
<b>What is the use of COALESCE in SQL Server?</b><br />
<b><a href="http://www.itdeveloperzone.com/2013/04/coalesce-in-sql-server.html" target="_blank">COALESCE</a></b> accepts "n" no of parameters and returns first not null values from the parameters passed to the function.<br />
<b><a href="http://www.itdeveloperzone.com/2013/04/coalesce-in-sql-server.html" target="_blank">Read more in detail with example... </a></b><br />
<br />
<b>What is Row_Number()?</b><br />
<b><a href="http://www.itdeveloperzone.com/2013/10/rownumber-in-sql.html" target="_blank">ROW_NUMBER()</a></b> is used to generate a unique row number in the result set.<br />
<a href="http://www.itdeveloperzone.com/2013/10/rownumber-in-sql.html" target="_blank">Read more in detail with example...</a><br />
<br />
<b>What is Apply Operator?</b><br />
The <b><a href="http://www.itdeveloperzone.com/2013/05/apply-operator-in-sql-server.html" target="_blank">Apply</a></b> operator acts like a Join without the ON clause. The <a href="http://www.itdeveloperzone.com/2013/05/apply-operator-in-sql-server.html" target="_blank">Apply</a> function in a query allows you to join a table to a table-valued function in such a way that function is invoked for each row returned from the table<br />
<b><a href="http://www.itdeveloperzone.com/2013/05/apply-operator-in-sql-server.html" target="_blank">Read more in detail with example...</a></b><br />
<br />
<b>What is the STUFF function and how does it differ from the REPLACE function?</b><br />
<b>STUFF</b> function is used to overwrite existing characters.<br />
<br />
<b>Syntax</b>
<br />
<pre class="brush :sql">STUFF (string_expression, start, length, replacement_characters)</pre>
<ul style="text-align: left;">
<li>string_expression is the string that will have characters substituted</li>
<li>start is the starting position</li>
<li>length is the number of characters in the string that are substituted</li>
<li>replacement_characters are the new characters interjected into the string.</li>
<li>REPLACE function is used to replace existing characters of all occurrences. </li>
</ul>
<b>Syntax </b><br />
<pre class="brush :sql">REPLACE (string_expression, search_string, replacement_string)</pre>
Every incidence of search_string found in the string_expression will be replaced with replacement_string.<br />
<br />
<b>What is a Stored Procedure?</b><br />
Stored Procedure in nothing but a set of T-SQL statements combined to perform several set of statements as a single task.<br />
<br />
<b>What are the advantages of using Stored Procedures?</b><br />
<br />
<ul style="text-align: left;">
<li>Stored procedure can reduced network traffic and latency, boosting application performance.</li>
<li>Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.</li>
<li>Stored procedures help promote code reuse.</li>
<li>Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.</li>
<li>Stored procedures provide better security to your data.</li>
</ul>
<br />
<b>Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?</b><br />
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.<br />
<br />
<b>What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?</b><br />
1024<br />
<br />
<b>What is an execution plan? When would you use it? How would you view the execution plan?</b><br />
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.<br />
From within Query Analyzer is an option called "Show Execution Plan"<br />
(located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.<br />
<br />
<b>What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?</b><br />
<b><a href="http://www.itdeveloperzone.com/2013/07/encrypt-stored-procedure.html" target="_blank">WITH ENCRYPTION</a></b> keyword is used to encrypt the text of the Stored Procedure.<br />
<a href="http://www.itdeveloperzone.com/2013/07/encrypt-stored-procedure.html" target="_blank"><b>Read more in detail with example..</b></a><br />
<br />
<b>How to pass data table to stored procedure as parameter in SQL Server 2005 (Write stored procedure) ?</b><br />
Refer this post : <a href="http://www.itdeveloperzone.com/2012/01/passing-datatable-to-stored-procedure.html" target="_blank"><b>Passing Data Table to Stored Procedure</b></a><br />
<br />
<b>How to achieve paging from SQL side.</b><br />
Refer this post : <b><a href="http://www.itdeveloperzone.com/2011/01/custom-paging-in-grid-view.html" target="_blank">Custom Paging</a></b><br />
<br />
<div style="width: 100%;">
<span>
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-2.html"><< Prev</a>
</span>
<span style="float: right;">
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-4.html">Next >></a>
</span>
</div>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-52790796177628794342015-11-08T16:06:00.004+05:302016-10-31T13:30:02.305+05:30SQL Server Interview Questions (Part-2)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="clear: both; height: 0px; text-align: center; width: 0px;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" width="0" /></a></div>
<b>What is the difference between a Local and a Global temporary table?</b><br />
Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.<br />
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.<br />
<b><br /></b>
<b>What is #temp table and @table variable in SQL Server?</b><br />
Both temp table and table variable are used to store data temporarily for a particular session.<br />
Both have some similarities and differences.<br />
<a href="http://www.itdeveloperzone.com/2015/11/temp-table-vs-table-variable-in-sql.html" target="_blank"><b>Read More in detail...</b></a><br />
<br />
<b>What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?</b><br />
It is called Heap. A heap is a table that does not have any index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data.<br />
<br />
<b>What is BCP? When does it used?</b><br />
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.<br />
<br />
<b>What is difference between Delete and Truncate commands?</b><br />
<table border="1" style="width: 100%;">
<tbody>
<tr><td>Delete is a DML Command</td><td>Truncate is a DDL Command</td></tr>
<tr><td>"Where" clause can be used to remove partial rows</td><td>Can't use "Where" clause</td></tr>
<tr><td>Slow as maintain transaction logs</td><td>Faster as transnational log is not maintained</td></tr>
<tr><td>Rollback is possible from log files</td><td>Rollback is not possible from log files.</td></tr>
<tr><td>Delete does not reset identity of the table.</td><td>Truncate resets the identity of the table</td></tr>
<tr><td>Delete activates Trigger because the operations are logged</td><td>Does not activate trigger because operations are not logged.</td></tr>
</tbody></table>
<br />
<b><a href="http://www.itdeveloperzone.com/2013/06/triggers-in-sql-server-part-1.html" target="_blank">What is Trigger?</a></b><br />
Triggers are special types of stored procedures which are automatically executes when an event occur in database.<br />
<a href="http://www.itdeveloperzone.com/2013/06/triggers-in-sql-server-part-1.html" target="_blank"><b>Read more in Detail...</b></a><br />
<br />
<b>How many types of triggers are there?</b><br />
There are four types of triggers.<br />
<ol style="text-align: left;">
<li>Insert</li>
<li>Delete</li>
<li>Update</li>
<li>Instead of</li>
</ol>
<a href="http://www.itdeveloperzone.com/2013/06/triggers-in-sql-server-part-1.html" target="_blank"><b>Read more in Detail...</b></a><br />
<br />
<b><a href="http://www.itdeveloperzone.com/2013/06/triggers-in-sql-server-part-2.html" target="_blank">What are Magic Tables?</a></b><br />
Whenever DML operations insert/update/delete are performed "INSERTED" and "DELETED" are created automatically. These tables are called Magic Tables.<br />
<b><a href="http://www.itdeveloperzone.com/2013/06/triggers-in-sql-server-part-2.html" target="_blank">Read more in detail...</a></b><br />
<br />
<div style="width: 100%;">
<span>
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-1.html"><< Prev</a>
</span>
<span style="float: right;">
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-3.html">Next >></a>
</span>
</div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-22656984431665276412015-11-08T16:06:00.002+05:302016-10-31T13:23:54.187+05:30SQL Server Interview Questions (Part-1)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="clear: both; height: 0px; text-align: center; width: 0px;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3Yx0gXyKDaPbueGcP8RPSyjKbTT3wqUtXuzq0aRcbIy1mTtToIbvpbKV5YVsNrIOjUp_cPxPczhvHAu5a7SbXt92VWIXQrjWyhIuLBvUc14VC-BhD3Bun4ue1IQA6furDdi_noAVduDZK/s1600/sql_interview_ques.png" width="0" /></a></div>
<b>Which TCP/IP port does SQL Server run on?</b><br />
SQL Server by default runs on port 1433. However it can be changed from the Network Utility TCP/IP properties.<br />
<br />
<b>What are the authentication modes available in SQL Server?</b><br />
<ul style="text-align: left;">
<li>Windows mode </li>
<li>Mixed Mode - SQL and Windows. </li>
</ul>
<br />
<b>When UPDATE_STATISTICS command is used?</b><br />
The indexes need to be updated when a large no. of DML operations like insertion, deletion and modifications are done.UPDATE_STATISTICS command performs this job.<br />
<br />
<b>What is Collation?</b><br />
<b><a href="http://www.itdeveloperzone.com/2013/06/collation-in-sql-server.html" target="_blank">Collation</a></b> refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.<br />
<b><a href="http://www.itdeveloperzone.com/2013/06/collation-in-sql-server.html" target="_blank">Read more in detail...</a></b><br />
<br />
<b>Which command using Query Analyzer will give you the version of SQL server and operating system?</b><br />
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').<br />
<br />
<b><a href="http://www.itdeveloperzone.com/2012/11/difference-between-char-and-varchar.html" target="_blank">Difference between char and varchar?</a></b><br />
Char takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n" where as Varchar takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data.<br />
<b><a href="http://www.itdeveloperzone.com/2012/11/difference-between-char-and-varchar.html" target="_blank">Read more in detail...</a></b><br />
<br />
<b>What is the use of Set NOCOUNT ON?</b><br />
By Default, When a command is executed it returns the number of affected record as result of the command. UsingSET NOCOUNT ON commands will no longer returns the number of affected records in the current scope.<br />
<br />
<b>What is an index?</b><br />
An index is used to speed up the performance of queries. They are created for faster retrieval of data.<br />
<br />
<b>What are the difference between clustered and a non-clustered index?</b><br />
A <b>Clustered index</b> is an index that sorts the data physically on the basis of columns used in clustered index and therefore only one clustered index is possible. The leaf nodes of a clustered index contain the data pages.<br />
A <b>Non Clustered index</b> is an index in which the logical order of the index does not match the physical stored order of the rows. The leaf node of a non clustered index does not consist of the data pages, instead, the leaf nodes contain index rows.<br />
<br />
<b>What are Constraint?</b><br />
SQL Server users constraints restricts only valid data to be inserted into column of a table.<br />
There are following types of constraints.<br />
Primary Key, Foreign Key, Unique, Default, Check, Not Null.<br />
<br />
<b>What is PRIMARY KEY?</b><br />
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.<br />
<br />
<b>What is FOREIGN KEY?</b><br />
A FOREIGN KEY constraint allows to create relationship between tables. It ensures that data in FOREIGN KEY column should exists in the referring PRIMARY KEY column thus mainlining the referential integrity<br />
<br />
<b>What is UNIQUE KEY constraint?</b><br />
A UNIQUE constraint enforces the uniqueness of the data in a set of columns, so no duplicate values are entered.<br />
<br />
<b>What is CHECK Constraint?</b><br />
A CHECK constraint is used to restrict the set of values that can be entered in a column. The check constraints are used to enforce domain integrity.<br />
<br />
<b>What is NOT NULL Constraint?</b><br />
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity.<br />
<br />
<b>WHAT is the difference between a Primary Key and a Unique Key?</b><br />
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where as unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULL, but unique key allows one NULL.<br />
<br />
<div style="width: 100%; text-align: right;">
<a href="http://www.itdeveloperzone.com/2015/11/sql-server-interview-questions-part-2.html">Next >></a>
</div>
</div>
Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0tag:blogger.com,1999:blog-2904192386692843423.post-24188064257541934002015-11-08T14:02:00.002+05:302016-04-06T15:22:00.358+05:30Temp Table vs Table Variable in SQL<div dir="ltr" style="text-align: left;" trbidi="on">
Both temp table and table variable are used to store data temporarily for a particular scope. You can perform all DML operations on both like you perform in a normal table. In terms of querying data they are similar to normal tables except the scope which is limited to a particular session.<br />
<br />
Name of temp table starts with "#" and name of table variable starts with "@"<br />
<br />
<b>Syntax for Temp Table
</b><br />
<!--INFOLINKS_OFF-->
<pre class="brush :sql">CREATE TABLE #temp (id int, name VARCHAR(100))</pre>
<!--INFOLINKS_ON-->
<br />
<b>Syntax for Table Variable
</b><br />
<!--INFOLINKS_OFF-->
<pre class="brush :sql">DECLARE @tab TABLE (id int, name VARCHAR(100))</pre>
<!--INFOLINKS_ON-->
<br />
<b>Both have some similarities and differences.</b><br />
<br />
<b>Similarities</b><br />
<ul style="text-align: left;">
<li>Both are instantiated in TempDB (System Database)</li>
<li>Clustered index can be created on both Temp Table and Table Variable</li>
<li>Perform any DML operations like Insert/Update/Delete.</li>
</ul>
<b>Differences</b><br />
<ul style="text-align: left;">
<li>Transaction logs are logged for Temp tables but not for Table Variable</li>
<li>Tables variables can not have non-clustered index</li>
<li>Indexes can be explicitly created on Temp tables but not on table variable i.e index can be created while creating table only</li>
<li>Scope of table variable is limited to the Object only in which it is declared while scope of temp variable is the session in which it is created.</li>
<li>Temp table can be created using "SELECT INTO" but not Table variables.</li>
<li>Table variables can be used as parameter.</li>
<li>Default collation for table variable is collation of the current database where as temp tables takes the default collation of tempdb.</li>
</ul>
</div>Sandeep Mittalhttp://www.blogger.com/profile/02880471130771695348noreply@blogger.com0