Tag Archives: data

Importing Flat File data into SQL Server with SSIS

Posted on by
Reply

Ok, so one of the problems that we come across in the development field is the fact that data is not always consumable in formats that we would wish to get, it may come in tab delimited, pipe delimited, in CSV files, whatever – the main thing here is we need to automate a process that imports the data into your database that has as few moving parts as possible (so when not if you have to dig back in the code you are not left wondering how the hell you built the thing in the first place.)

Turns out it is very doable but a long drawn out process in SQL Server to build a .dtsx package that accomplishes the entire task! I have detailed the steps below as I just know that this will come back and be of use later.

 Creating a Job in SQL Server to Import Flat Files

What You Need:

Microsoft SQL Server Management Studio

MSFT SQL server

Overview:

When data comes in from outside sources in flat files that are delimited you need a way to open the files, import the data into the database, and archive the files just in case you have a need to rerun the job, or to take a look at the raw data files in the future. The script will archive the files that have already been processed in folders bearing the month and the year that corresponds with the date the job ran that kicks off the .dtsx package.

Tables:

  • processed_files – This table will hold the files that have been processed so that 1) you have a record of what has been done, and 2) if the job fails you at least know where it failed.
  • data_table – This is the table that you are importing data into and will change depending on the files that you are importing.

Steps:

  • Set up Variables
  • Set up data connections
  • Displaying and using the toolbox
  • Add Foreach Loop Container
  • Add Execute SQL Task (Check Processed)
  • Add data Flow Task
    • Add Flat File Source
    • Add Multicast
    • Add OLE DB Destination
    • Add Row Count
    • Insert Constraint between Check processed and the Import file Data Flow Task
  • Add Execute SQL Task (Register Processing)
  • Add Script Task
  • Add File System Task (Move Files)
  • Add Execute SQL Task (Update processed table)

Create processed files table:

CREATE TABLE [dbo].[file_control]

(

[ImportedFilename] [varchar](100)   NOT NULL,

[RecordsImported]  [int]            NOT NULL,

[ProcessDate]      [datetime]       NOT NULL

)

Set up Variables:

To display the variables window in Microsoft SQL Server Management Studio hover over the ‘View’ menu item then scroll down to the ‘Other Windows’ option, the resulting fly-out menu should display the variables display option.

image001

Figure 1 – View Menu Bar

The table below lists all of the variables needed to set up the file import solution on the server. These variables will be manipulated later by scripts as well as used as output and input parameters in several of the steps we are about to perform.

Name

Scope

Data Type

Value

Example
Destination Folder DTS Package Level String Archive folder. However, you will need to insert the fully qualified name to the source folder or you will get the error that is visible in Figure 2 – FilePath error is variable is left empty. Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
Destination Root DTS Package Level String Path to the root of the archive folder. Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
FilePath DTS Package Level String The path and name of the current file in the Foreach loop container. You will need to insert the fully qualified name to the source folder or you will get the error that is visible in Figure 2 – FilePath error is variable is left empty. Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
FilePattern DTS Package Level String The extension or name that you want to use as a filter when determining which files the Foreach loop container will process. *.txt, or SALES*.txt
MonthNameFormat DTS Package Level String The Visual basic or C# format specifier for the month part of the date. More information can be found in the script section of this document. MMMM or MMM
Processed DTS Package Level Int32 Will hold a flag that will be set to true if the file has already been processed. 0
Records DTS Package Level Int32 Will hold the record count, or the number of rows that are present on the file. 0
SourceFolder DTS Package Level String The source folder where the files that you will be processing reside. will be Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.

image004

Figure 2 – FilePath error is variable is left empty

A word about scope:

                  Scope is very important in the processing of the files, you may be tempted to go with the thought that a variable.

Set up data connections

image006

Figure 3 – Connection Managers tab with context menu.

                  This .dtsx package will need three connections to add a data connection you will need to right click on the blank area in the ‘Connection Managers’ tab. You will need three connections:

  • New Flat File Connection
  • New File Connection
  • New OLE DB Connection

New Flat File Connection

The connection to the file is where you will have to set up the column mappings in order to get the data from the file to the table in the database. In this example give the connection the name ‘TextFile_Connection’, the rows are separated by a carriage return {CR}{LF}, the format is delimited, and there is one header row that we have to skip before we get to the actual data. See Figure 4 – Flat File Connection General Tab for a view of these settings.

The ‘Columns’ tab is a bit counterintuitive as it is not where we define the actual columns, it is where we set the column delimiter and the row delimiter for the rows that follow the header. In this instance we are dealing with a tab delimited file and each record is separated from the next by a carriage return. See Figure 5 – Flat File Connection Columns Tab for a view of these settings. You may be tempted to skip the next step, especially if the preview pane shows actual data from your input file; if you do you will miss one of the most important steps. Conversely, if you do not see any data in the preview grid after you navigate to the file you want to import then don’t worry, this is actually pretty common, you can add the columns manually in the next tab. 

The ‘Advanced’ tab, allows you to change the name of the columns as well as change the data type of the columns. If you are dealing with a data file that does not include column headers, or for some other reason you are unable to get the flat file wizard to recognize the  file you have selected you can press the ‘new’ button on the bottom of the screen to add columns to the connection manager. Make sure you rename these columns and match the data type and length to the destination database table. The Advanced tab is displayed in Figure 7 – Flat File Connection Advanced Tab.

 image008

Figure 4 – Flat File Connection General

image010 Figure 5 – Flat File Connection Columns Tab

image012

Figure 6 – A test tab delimited text file with one header row

image014

Figure 7 – Flat File Connection Advanced Tab 

New File Connection (A connection that points to the Processed Folder)

image016

Figure 8 – New File Connection dialog box

                  To set up a connection to the folder that will contain all of the archived files (in their dated folders) you will need to add a file connection. This is pretty straight forward, just change the usage type to ‘Existing Folder’ and browse to the folder on the server. Note that if you are using mapped drives you will need to type the location in the Folder textbox using the server name on this format \\servername\somefolder\destfolder.  The folder you are pointing to must exist before you complete this step.  The File Connection Manager Editor can be seen in Figure 8 – New File Connection dialog box.

New OLE DB Connection

image018

Figure 9 – New OLEDB Connection manager  window

Displaying and using the toolbox

 image019

Figure 10 – Show / Hide the Toolbox

                  The toolbox provides drag and drop elements that can be added to the control flow and data flow tabs in SQL Servers Management Studio. It is important to note that the options available within the toolbox change depending on the tab that you are working in. This distinction is usually ignored in many of the online tutorials that you will find, which in leaves the reader wondering why they do not see those options.

To display the Toolbox pane click on the view menu item then press the toolbox icon, or just use the shortcut keys Ctrl+Alt+X.

image022

Figure 11 – Toolbox when Control Flow is displayed

image024

Figure 12 – Toolbox when Data Flow is shown.

The figures above illustrate the different toolboxes. The next section will focus on the items necessary to complete the data import task.

Add Foreach Loop Container

 image026

Figure 13 – The Foreach loop Container

The Foreach loop container allows you to loop through a collection of objects. To configure the container double click on it to display the Foreach loop editor which is shown in Figure 14 – Foreach Loop Editor General Tab.

In the General tab change the Name to ‘Loop thru files’.

The next tab is the Collection tab; this is where you will be able to let the container know which directory it is supposed to loop through, as well as which files it should process. In this tab you will have to enter a value in the Folder textbox in the Enumerator configuration section of the application. The Files textbox works just like a file search in windows explorer, so if you wanted to loop through all the text files in a folder you would have to add ‘*.txt’.

The third tab is the ‘Variable Mappings’ tab, and is where we will connect the variable that is specified in the ‘Collection’ tab under the Enumerator configuration section. The variable in this instance will be set to the fully qualified file name.

Finally, in the expressions tab we will set up the variables that will hold the Directory and FileSpec values, this way we can go in later and change them by simply updating the variable’s value. To map the variables just click on the ellipsis in the right hand column of the expressions line of the Foreach Loop editor. Once the Property Expression Editor is displayed you can select a value in the right hand box that you would like to assign the variables value to.  Figure 17 – The Expressions tab of the Foreach Loop Editor shows both the Expressions tab and the Property Expression Editor.

image028

Figure 14 – Foreach Loop Editor General Tab

image030

Figure 15 – The Collection tab of the Foreach Loop Editor

image032

Figure 16 – The Variable Mappings tab of the Foreach Loop Editor

image034

Figure 17 – The Expressions tab of the Foreach Loop Editor

Add Execute SQL Task

                  When looping through the files the application will expect that the incoming data conform to the specifications that are set later in the data flow task. If for some reason the data does not conform to the specifications it will cause the entire job to stop executing. In order to determine where the job stopped running and to provide a means of restarting the job without deleting the data that was successfully input before the job failed it is important to log the file that has been processed and check that this file is not imported twice. This task will check the file log and determine if the file has been run already.

                  First drop the Execute SQL Task Control Flow Item into the Foreach loop container.  Next double click on the container to view the Execute SQL Task Editor, shown in the figure below.

image036

Figure 18 The General tab of the Execute SQL Task Editor

                  In the tab above change the name to read ‘Check Processed’. Change the result set from None to Single Row, then drop down the connection and if you have not added a connection to the database yet click on <New Connection> and add a connection to the database. Given the table structure for the processed files table found on page one place the following SQL in the SQL Statement area.

SELECT COUNT(*) AS Processed FROM file_control WHERE ImportedFilename = ?

                  Notice the question mark in the sql statement, this will receive the value in the parameter mapping tab that we will set up now. In the ‘Variable Name’ column drop down the box and select the FilePath variable that was set up earlier. Note that all variables that are user created will be prefixed with the word User and two colons (::). The directions should be Input, the Data Type is VARCHAR, and of course, this is the 0th parameter so place 0 in the Parameter Name column.  Adjust Parameter Size according to your file path size; here 200 will cover anything that is thrown at it.

 image038

Figure 19 – The Parameter Mapping tab of the Execute SQL Task Editon

                  Next click on the ‘Result Set’ tab and in the Result Name column type in ‘Processed’, and in the variable column drop down the box and select the user defined variable named User::Processed.

 image040

Figure 20 – The Result Set tab of the Execute SQL Task Editor

Add data Flow Task

 image042

Figure 21 – Data Flow Task with Context Menu visible as well as the data Flow tab visible

Next we will add the data flow task by dragging it inside the Loop thru files container. First, drag the green arrow that comes from the Check processed task that you just created to the new data Flow Task and connect the two.

Double clicking on the data Flow Task will take you to the data Flow tab pictured in the image above, which will allow you to drag and drop additional elements inside of the data flow task. The elements that are available will differ when inside the data flow tab. To rename the Data Flow Task you will have to right click on it and select Rename, at this point give the task the name ‘Import file’.

                  Next , double click the Import file task to change your view to the data Flow tab. Once inside the data flow tab we will need to drag four elements from the toolbox to the tab.         

  • Flat File Source element
  • Multicast element
  • OLE DB Destination element
  • Row Count element

After adding these elements connect them by dragging the green lines as shown in the figure below.

 image044

Figure 22 – Arraignment of elements in the Import file task 

Add Flat File Source

 image046

Figure 23 – The Connection Manager  tab of Flat File Source Editor

                  Double click on the Flat File source element to display the editor window. In the Connection Manager tab you should have the option of selecting the connection manager that points to the flat file you would like to import. You can refer to the file in Figure 6 – A test tab delimited text file with one header row for the file that we are using in this case. Once you have selected the connection move on to the ‘Columns’ section of the editor (pictured in the figure below). The Columns tab will allow you to rename the output from the file into names that actually mean something to you. Here I would suggest using the actual names in the destination table as it will make it easier to map the columns when you get to the OLE DB Destination part of this task. To change the names just click in the Output Column cell and type the name that you would like to be sent to the Multicast element. Once you complete these steps the small red X in the top right hand corner of the element should disappear. You can think of this X as an error in the compiler if you were writing code.

image048

Figure 24 – The Columns  tab of Flat File Source Editor

Add Multicast

                  The Multicast element is one of the few that does not need any changes once placed. This element will simply take the input from the Flat File Source and route it to two locations. In this instance those locations are the DB destination, to actually insert the data into the database, and the row count element to determine how many rows were inserted for logging purposes.

Add OLE DB Destination

                  The OLE DB Destination will point to the table that will receive the data from the file. The SQL to create the test table that corresponds with the test data file in Figure 6 – A test tab delimited text file with one header row is below:

CREATE TABLE [dbo].[file_import_test](

[TestColumn1] [int] NULL,

[TestColumn2] [varchar](200) NULL,

[TestColumn3] [float] NULL,

[TestColumn4] [float] NULL

)

                  First, double click on the DB connection to display the OLE DB Destination Editor (pictured in the image below).  Next, drop down the connection manager and select the connection that points to your database, which should have been set up earlier. You will want to select ‘table or View’ for the data Access Mode, and finally select the actual table where you want the file data to be routed to in the name of the table or the view dropdown box. Finally, we will map the columns from the file to the table columns. Click on the Mappings tab in the left window pane.

 image050

Figure 25 – The Connection Manager tab of the OLE DB Destination Editor

If you configured your flat file column names to match the table column names then this part will be pretty easy, just verify that they link up. If not you are left with the task of connecting the file column outputs with the table columns and then connecting them by dragging and dropping the lines between them to the correct columns. Once you complete this task the small red X in the OLE DB Destination element should disappear.

image052

Figure 26 – The Mappings tab of the OLE DB Destination Editor

Add Row Count Element

                  The row count element will count the rows that have been read from the file and store them in the variable that has been designated in the custom properties section of the Component properties tab pictured below. All that needs to be done here is the variable User::Records should be selected from the drop down box in the column next to variable name.

image054

Figure 27 – The Advanced Editor for Row Count

Insert Constraint between Check processed and the Import file Data Flow Task

The final step in the addition of the Import file data flow task is to set up a constraint between the check processed SQL task and the data flow itself. The constraint will make sure that duplicate records do not get inserted into the table. To accomplish this double click the green arrow that connects the two elements to display the Precedence Constraint Editor.

image056

Figure 28 – Precedence Constraint Editor

                  In the window pictured above change the drop down box from the default setting of Constraint to Expression and Constraint. Once this has been completed the Expression text box will open where you enter @Processed == 0, which corresponds to the variable being passed by the Check processed SQL task. Leave the default setting in Multiple Constrains where it is and press OK. 

Add Execute SQL Task (Register Processing)

Next , drag another Execute SQL task element to the Foreach Loop Container and drop it inside the container directly under the data flow task. Right click on the task and rename it ‘Register Processing’.

image054

Figure 29 – The General tab of the Execute SQL Task Editor

                  In the General tab of the Execute SQL task Editor select the database connection from the connection dropdown box. In the SQL Statement text area insert the following SQL:

INSERT INTO file_control (ImportedFilename, RecordsImported) VALUES (?, ?)

image060

Figure 30 – The Parameter Mapping tab of the Execute SQL Task Editor

                  Next click on the parameter mapping tab and add the following parameters to the window on the right hand side, 1) User::FilePath with a direction of Input and a datatype of VARCHAR and a size of 200, and 2) User::Records with adirection of Input and a type of LONG and a size of -1. The parameter name of the first variable is 0 and the parameter name of the second variable is 1.

Add Script Task

Next drag the Script Task element from the toolbox and drop it directly under the Register Processing SQL task.  Right click on the element and give it the name Set Destination. Double click on the element to display the Script Task Editor window.  Make sure the Script language is set to Microsoft Visual Basic 2008 then press the Edit Script … button at the bottom of the window to bring up the code editor. The script that will create archive directories is below and will need to replace the code that is between the following lines:

Public Sub Main()

…..

End Sub

<b>Microsoft Visual Basic 2008 Code</b>

Dim varCollection As Variables = Nothing

Dts.VariableDispenser.LockForRead("User::FilePath")

Dts.VariableDispenser.LockForRead("User::DestinationRoot")

Dts.VariableDispenser.LockForRead("User::MonthStartPosition")

Dts.VariableDispenser.LockForRead("User::MonthNameFormat")

Dts.VariableDispenser.LockForWrite("User::DestinationFolder")

Dts.VariableDispenser.GetVariables(varCollection)

Dim SourceFilePath As String = varCollection("User::FilePath").Value.ToString()

Dim FileName As String = SourceFilePath.Substring(SourceFilePath.LastIndexOf("\") + 1)

Dim DestinationRoot As String = varCollection("User::DestinationRoot").Value.ToString()

Dim MonthStartPosition As Integer = Convert.ToInt32(varCollection("User::MonthStartPosition").Value)

Dim MonthValue As Integer = 0

Dim Yearvalue As Integer

Dim MonthNameFormat As String = varCollection("User::MonthNameFormat").Value.ToString()

Dim FolderName As String = String.Empty

Dim MonthwiseDirectory As String = String.Empty

MonthValue = Month(Now())

Yearvalue = Year(Now())

If FileName.Length > 0 AndAlso MonthValue > 0 Then

FolderName = New DateTime(1, MonthValue, 1).ToString(MonthNameFormat) & " " & Yearvalue

End If

MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName)

If Not System.IO.Directory.Exists(MonthwiseDirectory) Then

System.IO.Directory.CreateDirectory(MonthwiseDirectory)

End If

varCollection("User::DestinationFolder").Value = MonthwiseDirectory

Dts.TaskResult = ScriptResults.Success

The code above will create a folder structure for archiving the files that have been processed using a month / year naming convention as shown in Figure 31 – Example of the Archived File Structure.

image062

Figure 31 – Example of the Archived File Structure

                  The parent folder for these monthly folders is determined by the value in the variable DestinationFolder.

Add File System Task (Move Files)

 image064

Figure 32 – File System Task Editor General Settings Tab

                  Next drag a File System Task element from the toolbox and drop it inside the Foreach Loop Container under the Script Task then connect the greed arrow from the Script Task to the File System Task. Right click on the element and rename it Move Files. This will be the element that physically moves the files to the archive location.

To move the processed files change the following settings in this order, or it will not work:

Destination Connection Section

  • IsDestinationPathVariable = true, this in turn changes the text in the box below it from Destination Connection to DestinationVariable
  • DestinationVariable = User::DestinationFolder
  • OverwriteDestination  =false

Source Connection Section

  • IsSourcePathVariable = true, this in turn changes the text in the box below it from SourceConnection to SourceVariable
  • Sourcevariable = User::FilePath

Add Execute SQL Task (Update processed table) 

                  The last task that you have to complete before setting up the job that will kick off this entire process is to drag an Execute SQL Task element to the Foreach Loop Container and drop the element directly below the Move Files element that you just completed. Right click on the object and rename it ‘Update processed table’, as the name suggests this task will actually go out and rename the files appending the word processed to the imported file name so that the application will not skip the file when it is dropped in the new file location tomorrow (this assumes that you are working on files that will be coming in using the same name every day which is very common!).  You should be getting pretty good at the SQL Task object by now so here are the steps required to update the file names.

 image065

Figure 33 – Your completed .dtsx package should look like this

Visual Basic and C# date Time Format Specifiers:

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

What’s the difference between UNION and UNION ALL

Posted on by
Reply

I recently ran into a situation where I had to provide a summary of items ordered in a table and needed to union the products table in order to get all of the lines that also contained a zero as I had a requirement to show a summary of all the quantities ordered as well as line for each product on the products table that did not have any orders in the orders table yet.

Let’s try to solve this using the tables below, and in turn learn a little about the differences between UNION and UNION ALL:

Products

id product description
1 ajax clean anything
2 glo-brite mopping solution
3 steel wool scrub anything
4 soap plain and good

Orders

fk_product_id total
1 200
2 300
1 200
1 50

Query

The query below

Select orders.total AS ordered,products.product
FROM orders INNER JOIN products ON orders.fk_product_id = products.ID
UNION
SELECT        '0' AS ordered, product
FROM            products AS products_1

Returns

ordered product
0 ajax
0 glo-brite
0 soap
0 steel wool
200 ajax
300 glo-brite
50 ajax

 

Notice that all of the orders for ajax did not get returned in the query. Since we used union it discarded the duplicates in this instance. Now let’s see what happens when we replace UNION with UNION ALL in the query.

ordered product
200 ajax
300 glo-brite
200 ajax
50 ajax
0 ajax
0 glo-brite
0 steel wool
0 soap

When using all we tell the query to get every record from both queries without removing the duplicates as you can see in the results displayed above. This is important because if we were to finish the query to the specifications in the requirement in the first paragraph we would expand the query to get the totals using the SQL below, which would result in a missing order count of 200 for ajax when using UNION inside our inner query.

SELECT SUM(ordered) as orders, product
FROM(
Select orders.total AS ordered,products.product
FROM orders INNER JOIN products ON orders.fk_product_id = products.ID
UNION ALL
SELECT        '0' AS ordered, product
FROM            products AS products_1) as source_table
Group by product
orders product
450 ajax
300 glo-brite
0 soap
0 steel wool

Adding a checkbox column to your extjs grid and using it to pass a comma delimited set of primary keys to your server side page

Posted on by
2

The checkbox column below refers to an user extension written for Extjs 3.x please see this post for the simpler 4.x way to do things!

Whenever I have an application that allows the user to delete lines from an extjs grid, the inevitable question that comes up after a few weeks or months of use is, “Could I get something that will let me delete multiple lines at once?” Of course, you can, and to make things even better adding this feature is really a five minute task since your sql for the database deletion probably looks like:

 delete  from some_table where pk_id = N'#some_value#'

To add the checkbox column to your extjs data grid insert the following code above the grid configuration. It is critical to replace the text ‘YOUR_PRIMARY_KEY’ in the snippet below with the actual id of the primary key in your store, as this will be the way that this method passes the values back to the server side, if you get an error message like this: ‘undefined,undefined,undefined’ after implementing this solution, it is likely that you overlooked this step.

Ext.ux.GridRowChecker = Ext.extend(Object, {
	    header: "Delete",
	    width: 50,
	    sortable: false,
	    fixed: true,
	    menuDisabled: true,
	    dataIndex: 'YOUR_PRIMARY_KEY',
   		id: 'selection-checkbox',
	    rowspan: undefined,
    	init: function(grid) {
    	   this.grid = grid;
    	  this.gridSelModel = this.grid.getSelectionModel();
    	  this.gridSelModel.originalMouseDown = this.gridSelModel.handleMouseDown;
	  this.gridSelModel.handleMouseDown = this.onGridMouseDown;
    	  grid.getColumnModel().config.unshift(this);
    	  grid.getChecked = this.getChecked.createDelegate(this);
    	  grid.checkAll = this.checkAll.createDelegate(this);
    	  grid.uncheckAll = this.uncheckAll.createDelegate(this);
    	},
	renderer: function() {
	  return '<input class="x-row-checkbox" style="height: 12px;" type="checkbox" />';
	},
	getChecked: function() {
	var result = '';
	var cb = this.grid.getEl().query("div.x-grid3-col-selection-checkbox > input[type=checkbox]");
	var idx = 0;
	this.grid.store.each(function(rec) {
		if (cb[idx++].checked) {
			result = result + rec.get(' YOUR_PRIMARY_KEY') + ',';
			}
		});
		delete cb;
		return result;
		},
	checkAll: function() {
	this.grid.getEl().select("div.x-grid3-col-selection-checkbox > input[type='checkbox']").each(function(e){
	e.dom.checked = true;
	});
},
uncheckAll: function() {
	this.grid.getEl().select("div.x-grid3-col-selection-checkbox > input[type='checkbox']").each(function(e){
	e.dom.checked = false;
	});
},
onGridMouseDown: function(g, rowIndex, e) {
   this.originalMouseDown.apply(this, arguments);
}
});

Now you just have to add the checkbox column that you created above to your extjs data grid like this:

plugins:[new Ext.ux.GridRowChecker()]

Finally, the last piece of the puzzle, you probably want an example of how to use this in real life right? The code below will create a button that will send the comma delimited string to your existing deletion sql on the backend; just place it in the toolbar of your grid.

{
    xtype: 'button',
    id:'my_delete_button',
    text: 'Delete Selected Records',
    listeners : {click : function() {
         // grid is the name of the grid
         // that contains the checkbox column
        var theKeys = grid.getChecked();
        Ext.Ajax.request({
           url: 'delete_data.php',
           params:{theKeys:theKeys},
           success: function(action){
                grid.store.reload();
            },
            failure: function(action){
            // you could put an error message here
            }
        });
    }
   }
}

The beauty of open data one year after the post that started it all

Posted on by
Reply

Extjs 4.0 Chart Click Event tips, tricks, and at least one hack to identify which grouped bar instance has been clicked in a grouped bar chart

Posted on by
4

For the past week I have been working on Extjs 4. The switch from 3 occurred mainly for the charting aspect of it. I would like to share what I have discovered that might be of some use to those that are trying to implement the item click on a bar chart, pie chart, etc:

The series of the chart is where you place the listener. Unlike Ext 3.x the event that gets fired on a bar chart click is ‘itemmousedown’ (or up), this is important because ‘itemclick’ will not work on the new charts.

For those that want to repeat the discovery part of this article on widgets other than the ‘itemmousedown’ on the charts, place the following code in the handler just as the example shows below.

listeners: {
         itemmousedown: function(a,b,c,d,e){
         for(var x in a){
                                 alert(x);
                              }
         }
}

The documentation that comes with ext states:

itemmousedown( Ext.view.View this, Ext.data.Model record, HTMLElement item, Number index, Ext.EventObject e)

Fires when there is a mouse down on an item
Parameters

this : Ext.view.View
record : Ext.data.Model
item : HTMLElement
index : Number
e : Ext.EventObject
What you really get when you use itemmousedown and loop through the result set of a from the example code above is:

a.series =
a.storeItem.data. {name of field in your store}= This will return the value associated with this chart item, it is great for retrieving the name of the categories to the left of the grid to aide you returning more details.
a.value = stores the actual number that is plotted on the chart, this is really helpful if you want to use it to alert the value to the user or place it in a tooltip etc.
a.attr = This will return the attributes of the bar that you clicked on, one way to differentiate the actual bar in an example like the one above id to get the color as it is a constant, but this would require a sloppy bit of code that would equate a color in hex code to a category. This is definitely not a best practice!
a.point
a.shadows
a.sprite

b.itemmousedown = This is the event

Imagine you have a chart much like the chart below. (Go ahead click on a piece, it will do the for loop mentioned above.)

Now let’s change the scenario, say you want to use what you learned above to create a series of charts that you can drill down into. If you happen to be taking an Information Systems management course, this is what we mean when we say Executive Dashboard, it is a system designed to give a current snapshot of the organization, usually built on top of an Enterprise System, but I digress. If the chart is based on the same data store, just queried differently, you may use the .refresh() function after reloading the data store. If you are switching data sets you may use a card layout to switch the view from one chart to another on a series click. We will explore the second option as the code required for the first option will be contained in the second option. One of the things that struck me right away with Extjs 4 was the speed at which you could click through a series of charts that are inside of a card layout.

First we have to build a panel with a card layout so that we can stack our deck.

chart_card_deck =  Ext.create('widget.panel', {
             layout:'card',
             title:'Regional Chart',
             width:800,
             height:400,
             bodyPadding: 5,
             bodyStyle:'background:#D8E4F2',
             renderTo: Ext.getBody(),
             border:false,
             id:'card_deck',
             listeners:{
            afterrender:function(){
               Ext.getCmp('card_deck').add(sales_by_region_chart);
               Ext.getCmp('card_deck').add(sales_by_region_detail_chart);
                 }
              }
   });

Notice that the afterrender function adds the chart “sales_by_region_chart” to the card deck. I don’t care what the index (or position in the deck) is as I can switch cards in Extjs 4 by passing the id attribute in setActiveItem method, so I will omit any code that references the card index.

Ext.getCmp('west_side_').layout.setActiveItem('current_status_chart');

OR

Ext.getCmp('west_side_').layout.setActiveItem('sales_by_region_detail_chart');