Technical

Three ways to query Azure DevOps using Power BI

Michael Grove

Gathering and analysing data from Azure DevOps and presenting it in different, visual, ways within Power BI can assist scrum masters, project managers and development teams in planning and monitoring project progress.

What are some of the tools available to analyse and visualise Azure DevOps data in Power BI?

Built-in Power BI connector

Power BI offers two built-in connectors for Azure DevOps: Azure DevOps (Boards Only) and Azure DevOps Server (Boards Only). These services, formerly called Visual Studio Team Services (VSTS) and Team Foundation Server (TFS) respectively offer the simplest method to import data into Power BI for reporting.

This connector requires the Organization name and Team project name to connect to the correct boards for import. The data returned is dependent on the default analytics views built into Azure DevOps, so there may be some pre-work required.

The default Analytics data returned is high-level per Bug, Story, Task or Work item, so while this approach is the quickest and easiest to get Azure DevOps data into Power BI, it may not be the beast approach to develop rich analytical reports on your project as is.

More information on connecting and importing the data using this approach can be found on these Microsoft learn pages:

OData Feed connector

Azure DevOps contains a built-in reporting platform called Analytics, providing fast read-access and server based integrations to answer questions about your project data. This is the same tool providing the data to the Built-in Power BI connector mentioned above.

To import this data into Power BI you use the OData Feed connector. Have the name of your organization and project as they appear in DevOps on hand to connect to your DevOps boards, Pipelines, Test plans, Repos and Artifacts.

The query is slightly different between the cloud-based Azure DevOps and the on-premises (Azure DevOps Server) instances. Below is the breakdown of the Azure DevOps Service (cloud) structure.

While this data does use the same Analytics views as the built-in Power BI connector, it does offer a more customizability in the initial URL query to get a finer grain of data to develop your DevOps report in Power BI. This added customisability does come with a slight drawback in the slightly more technical skill required in data extraction.

Additional information on connecting and importing the data using the OData connector can be found on these Microsoft learn pages:

REST API

As a brief introduction, an API is an intermediate software agent that allows two applications to communicate with each other. APIs provide a set of protocols, rules, and developer tools that enable software developers to extract and share information and create applications that can interact with each other.

Using the Azure DevOps REST API in Power BI is the most technical of the approaches to extract data out of Azure DevOps, but it can provide a level of detail not available through other methods.

The Azure DevOps API supports Microsoft Authentication Library, OAuth, and Session tokens as ways of authentication to accommodate various developer languages and scenarios. The simplest approach for Power BI is to create a Personal Access Token (PAT) in Azure DevOps to use for your report authentication.

Customising the query and making it more dynamic for your requirements can be done once an initial connection has been made and you have confirmed that you can get data. The authentication method in Power BI to the REST API will be Basic, using the PAT you have created as the username and password for the connection.

Additional information on REST API can be found on these Microsoft learn pages:

Which method should you use?

The approaches to connect to your DevOps board data in this article has been listed from simplest to most complex.

For most requirements (and for general Power BI development sanity) using the built in connector or OData feed connector are ideal. Although the API can possibly provide a smidgen more information over the other two approaches, using API’s in Power BI Desktop (and the Power BI service) adds large levels of complexity to your project. Only you can decide if the extra data you can pull is worth the additional effort.

Happy developing.

Four tools to help you become a better data analyst

Michael Grove

Four Tools to Help you Become a Data Analyst

Gathering and analysing data has changed how business decisions are made and how resources are allocated. Through a better use and understanding of data, business managers can plan and enable better strategies with increased confidence. When understood, the companies data can increase administrative efficiency and add competitive advantage – data analytics provides new insight and actionable intelligence.

Data analytics is the process of examining raw data, understanding the processes underlying that data, discovering the patterns, and drawing conclusions about the information and finally communicating the results in a way that has the biggest possible impact.

So what are some of the tools available that will help you analyse your businesses data and become the data analyst that makes the correct data-driven decisions?

Microsoft Excel

Microsoft Excel is the industry standard for spreadsheets and is the first of the list of tools available for the budding data analyst. Microsoft Excel offers an easily accessible and familiar tool to users to perform financial modelling, put together a quick proof of concept or for that one-time analysis to get a quick conclusion for a new business question.

With the powerful importing tool PowerQuery and the compression capabilities of PowerPivot that extend the capabilities of the normal pivot table data, allow more advanced calculations, and  add the ability to import data from multiple sources. Excel is the perfect first tool for any new data analysts’ tool belt.

SQL Server

Microsoft SQL Server (or other relational database) will likely be powering your business’s transactional ERP system.  As a future high flying data analyst, knowing how to query your source system using SQL queries will quickly allow you to answer some basic questions. Including; “How many sales did we make last month?” or “How many stock items do we have on-hand?”

Writing a small SQL query can provide you with a quick answer to a question and many results can be exported to Excel for further analysis.

Just be warned, executing any SQL query could have unintended consequences. Especially when querying large data sets; the larger the data set, the more complex the query, the longer the wait to get your results.

This powerful tool can be the utility knife on your data analysis tool belt, it is versatile, but it does require some care to use.

Analytics Tools

Analytics tools such as Business Analytics, TimeXtender and Jet Enterprise are purpose built to collect the data from multiple data sources and rapidly combined this data into meaningful groups that you can then analyse. If you need to gather data, do not do it manually yourself, get a tool to help.

Analytics tools give you the ability to answer a specific set of commonly-asked questions such as “How much profit have we made Year-on-Year over the last three years by region?”, or “What was our best selling over the last quarter?” Analytics tools are a big step into the area of Business Intelligence (BI) reporting and data manipulation.

Analytics tools can provide these answers in easy to understand, fast acting, template type, reports that can assist a business in identifying underlying trends or gaps in the business and improving profitability of the business when understood.

Adding one of these analytics tools to your arsenal is like adding a backpack to your utility belt. It can be a slightly larger than originally expected but will provide nearly any answer to any question – provided it was packed correctly beforehand.

Dashboards

All these tools can mean little to business unless they are presented in a way that is meaningful to the end business users. Sometimes this is done in a simple spreadsheet report with baseline numbers, but more likely this requires a little more glamour and shine.

A powerful in-memory BI tool such as Power BI from Microsoft not only offers a powerful data importing and data manipulation tool all in one (with easy to learn features that allows non-technical users the ability to import and understand their own data) Power BI also offers great visualisation options to present the business data in a meaningful, visual manner. These help to keep critical metrics in the line of sight at all times, helping the members of your team to stay focused on the numbers.

Having a dashboard or visualisation tool (particularly a strong BI focused tool) will finish off your utility belt with the required survival kit. This will get you through the harshest of data values and make a big impact fast.

Just a note of caution; No matter what tool you add to your belt or how you utilise it to understand your data – be aware that a tool is only as good as the data you provide it. If you provide incorrect data to any tool you will get incorrect information out, if you provide insufficient data to a tool you will likely end up with the wrong conclusions.

What does this mean for you?

The business world has changed. There is more data available and more being generated in every aspect of business at a more rapid rate than ever before; there are many different methods and tools to analyse this data. Businesses need to understand this data and require data analysts to guide them through the unknown paths that are this new and scary data jungle.

With the above guide, a fully prepared tool belt will assist you in being that data analyst that guides your business through it all.

[Originally Published on the Karabina/IS Partners company blog]

When testing ETL tools here are 5 things to bear in mind

Michael Grove

As it is officially braai (barbeque, BBQ) season in South Africa, I decided to compare the elements involved in testing ETL tools with that of a braai. After all, you would not simply light a fire and throw your carefully selected steak onto the flames.

There are several steps you take in the process to ensure that your steak is cooked to your satisfaction. As a data warehouse is not a specific type of technology and there are many types of ETL tools available, proper testing is essential to ensure that your ETL tool is servicing your data warehouse correctly.

So, in the below, we compare the testing of an ETL tool to the testing of your braai skills.

1. When should testing happen and how often?

o ETL: ETL Testing should take place during development of a new system and when any changes occur to that system (source system changes (upgrades) or manual customizations to the reporting solution).

o Braai: Once you have successfully built your braai, and the lit the fire, is important to regularly test the heat of the braai to ensure that you cook your steaks at an optimal temperature. Make the necessary changes to the braai, stoke the coals, add additional fire wood, and change the location of the Weber if necessary.

o ETL: Occasional testing beyond implementation that can usually be accomplished through managed reporting (if you get an error report from the system this is an indication that something is wrong).

o Braai: Occasional testing is necessary now that you have adjusted your fire. High flames are an indication that it is nowhere near ready; take caution around the open flames.

2. Who is responsible for testing?

o ETL: During implementation – the implementation consultant/developer.

o Braai: During initiation of the braai / fire – the braai master (developer who is usually pedantic about the fire they have lit) is responsible for testing.

o ETL: During Customizations to the report tool – the implementation consultant/developer performs these changes.

o Braai: On occasion the braai master may send a braai assistant (consultant) to check or adjust the braai.

o ETL: During daily automated runs – System administrator (SQL Administrator).

o Braai: Advanced braai masters (developers) have automated tools such as a braai thermometer that will sound an alarm when the optimal temperature is reached reducing manual intervention.

3. What are the benefits of testing ETL?

o ETL: Faults are eliminated early – before the reports get to Business Users.

o Braai: Faults are eliminated early – having a braai with sub-standard charcoal or braai wood will affect the end result.

o ETL: The system ends up being more stable and reliable

o Braai: Your guests at your braai can trust that they will have a phenomenally cooked steak.

o ETL: Users can trust the system (proof can be shown that it balances).

4. Types of ETL Tests

o Constraint Testing (Developer checks the following constraints (NOT NULL, UNIQUE, Primary Key, Foreign Key, Default, & NULL)

o Source to Target Count and Data Validation

o Duplicate Checking

o Incremental and/or Historical load processes

o Regression Testing (Does a fix on one field break any other fields)

5. Phases of ETL testing:

o Requirements/Design Review

o Test Planning (What tests to perform)

o Test Designing (Build the tests)

o Test Environment setup

o Test Execution

o Test Reporting (results of the tests)

Most braai masters will tell you that there is a very specific method that each one of them uses to create the best braai. The same is relevant to ETL developers and consultants when it comes to testing ETL Tools on the data warehouse.

Our approach is to use data warehouse automation tools such as TimeXtender​, as they reduce the dependency on senior BI technical skills as well as reducing development time by an estimated 80%.

Image credit: rapgenius

[Originally appeared on the Karabina/IS Partners company blog 18 September 2014]

Quick Windows Server 2008 R2 – SQL Server Firewall setup

Michael Grove

Setting up SQL Server 2012 I received the same warning I have always received when setting up a SQL instance on a new Windows server:

SQL Setup Support Rules – Firewall Warning

So I executed my handy Windows Server Firewall batch file for SQL to set up the default ports on the Firewall, which executed successfully, but I noticed the following warning during execution:

Warning – netsh firewall is deprecated

After a quick check I updated the file with the new correct script options (I have left the original script lines in, just commented them out for reference:

@echo =========  SSRS Ports  ===================
@echo Enabling SQLServer default instance port 1433
REM Deprecated: netsh firewall set portopening TCP 1433 "SQLServer" 
netsh advfirewall firewall add rule name="SQLServer" dir=in action=allow protocol=TCP localport=1433

@echo Enabling Dedicated Admin Connection port 1434
REM Deprecated: netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434

@echo Enabling conventional SQL Server Service Broker port 4022  
REM Deprecated: netsh firewall set portopening TCP 4022 "SQL Service Broker" 
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022

@echo Enabling Transact-SQL Debugger/RPC port 135 
REM Deprecated: netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135

@echo =========  SSAS Ports  ==============
@echo Enabling SSAS Default Instance port 2383
REM Deprecated: netsh firewall set portopening TCP 2383 "Analysis Services" 
netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383

@echo Enabling SQL Server Browser Service port 2382
REM Deprecated: netsh firewall set portopening TCP 2382 "SQL Browser" 
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
REM Deprecated: netsh firewall set portopening TCP 80 "HTTP" 
netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80

@echo Enabling SSL port 443
REM Deprecated: netsh firewall set portopening TCP 443 "SSL" 
netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443

@echo Enabling port for SQL Server Browser Service 'Browse' Button
REM Deprecated: netsh firewall set portopening UDP 1434 "SQL Browser" 
netsh advfirewall firewall add rule name="SQL Browse" dir=in action=allow protocol=TCP localport=1434

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
REM Deprecated:netsh firewall set multicastbroadcastresponse ENABLE
netsh advfirewall set currentprofile settings unicastresponsetomulticast enable

@echo Complete, Check your results.
pause

Hope it make your future installs easier.

Tested on:

  • Windows Server 2008 R2 Sp1 while installing SQL Server 2012
  • Windows Server 2008 R2 while installing SQL Server 2008 R2

Sources:

http://technet.microsoft.com/en-us/library/cc771046(v=ws.10).aspx

http://support.microsoft.com/kb/947709

http://support.microsoft.com/kb/949543

Note: This script is provided “as is” without any representations or warranties, express or implied – use of this script is at your own risk.

HTC Desire S – Unable to download Google Play application updates over wireless

Michael Grove

I recently had an issue where I was unable to download application updates on my Android phone.

Error Message:

  • Error: Update for “<application>” could not be downloaded due to an error. (495)

Phone details:

  • HTC Desire S
  • Android 2.3.5
  • HTC Sense 3.0

After much searching I eventually used these two different options to resolve my issues:

Option 1 – Clear phone download manager cache:

  1. Settings > applications > all applications > Download manager > Clear cache
  2. Settings > applications > all applications > Google Play > Clear cache
  3. Restart phone
  4. Attempt application download again.

This resolved some of my update issues (3 of 6 outstanding updates downloaded successfully over wireless)

Option 2 – Wireless network is blocking a required port for the download:

  1. Disable phones wireless connection
  2. Switch on your 2G/3G connection
  3. Attempt application download again.

This resolved the balance of my download issues and once all the current updates were successfully downloaded and installed the error with wireless downloads has corrected itself (future updates downloaded successfully over wireless).

Hope this helps if you have similar issues on your android device.

Large Prints – My Epson Stylus Photo 1410 Experience

Michael Grove

A couple of months back my wife completed a photography course and following the course she purchased a Canon 450D digital camera.

Taking photos, getting the right light, getting that perfect shot, is all well and good, but looking at the results on a computer screen does not always do the photo justice.

The normal process to print the brilliant shot was to save it onto a flash drive, take that drive to the nearest (half decent) print lab and have them print the photo to the size needed; this process is all well and good if the photo lab gets the print right, the lab is open, the lab is currently able to print and you have the right amount of money. Photos are sometimes ridiculously expensive (especially for the A3 size)

So how do you avoid the above troubles?

This large printer is widely available and although not super cheap – the printer comes in around R 3500 (~$ 450) it does the job superbly. (Even if it does eat through R 200 ($15) cartridges at an alarming rate when printing full A3 borderless prints)

This printer has a few drawbacks, with the first and most obvious one is the sheer size of an A3+ printer. You will need a large desk to accommodate this baby. This isn’t really all that bad though, as you would have a similar issue with almost any A3 printer.

As the name implies the Epson Stylus Photo 1410 is a photo only printer so it is not recommended as your only printer and definitely should not be used for normal text on plain white paper printing- although it can do that too.

You should spend the extra money on the Epson Photo Glossy Paper (between R 400 and R 800 for a 10 pack A3), in my tests printing on HP photo paper was a complete failure with the Epson ink not drying and in places flaking and cracking.
Generic photo paper did work and the prints were superb, but in comparison to the Epson photo paper, the generic paper prints were bland.

Drawbacks aside, this printer is amazing. It is simple to setup and install and with the built in PictBridge support you can even print directly from your camera without setting up the PC.

Printing borderless images was easy, almost silent and extremely quick (to print the image on A3 and for the ink to dry on the page)

The printer has 6 different ink cartridges (one for each colour) so it does help with keeping costs down as you only need to replace the depleted colour, and not the whole set when you run out on one.

The Epson Stylus Photo 1410 is not going to be the printer for everyone but for any photo enthusiasts, if you’re looking for a good photo printer, I believe this one is it.

Three Level XML to Javascript Drop Down List

Michael Grove

Following a post on an Internet forum, here is a small expansion on my Country / State dropdown selection script to enable a third level drop down. This additional level would allow for a country / state / city drop down selection on an XML file as apposed to just a country / state drop down selection.

I have broken the code in this post into its three sections; the HTML, the XML and an external JavaScript file.

A section of the HTML code:

...

Country slection – with State/Province select



The XML code:





 
     New York
     New Jersey    
 
 
     Sanfransico
     Hollywood    
 


 
     Johannesburg
     Pretoria    
 
 
     Durban
     Pietermaritsburg    
 




A section of the Javascript code:


...
function fillStateList()
{
 var stateList = document.getElementById("cboState")
 
 for (var x = stateList.options.length-1; x >-1; x--)
 {
     stateList.options[x] = null;
 }
 
 var countryListSelected = 
document.getElementById("cboCountry").selectedIndex;
 var numberStates = 
xmlDoc.getElementsByTagName("country")[countryListSelected]
.getElementsByTagName("state").length;
 
 for (var i=0; i<=numberStates-1; i++)
 {
      var currentState =  
xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName
("state").getAttribute("name");
     fillList(stateList,currentState,currentState);
 }
 
}

function fillCityList()
{
 var CityList = document.getElementById("cboCity")
 
     for (var x = CityList.options.length-1; x >-1; x--)
 {
     CityList.options[x] = null;
 }
 var countryListSelected = document.getElementById
("cboCountry").selectedIndex;
 var StateListSelected = document.getElementById
("cboState").selectedIndex;
 var numberCities = xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName("state")
[StateListSelected].getElementsByTagName("city").length;
 
     for (var i=0; i<=numberCities-1; i++)
 {
       var currentCity =  xmlDoc.getElementsByTagName("country")
[countryListSelected].getElementsByTagName("state")
[StateListSelected].getElementsByTagName("city").firstChild.nodeValue;
     fillList(CityList,currentCity,currentCity);
 }
}



In a real world situation a full world wide city level dissection of data should not be stored in an single XML file as the amount of entries would place too much load on the XML file -More than likely the XML file would not load. To store all the city names across the world the use of a database to hold the data, and call the required data as needed, would better suit the needs.


This script can be used for any XML file in a similar format to handle multiple levels of data and placing the data into drop down lists.


The complete code for this example can be downloaded here: [download#2]


Have fun and happy coding.

					

	
	



			
				



	
	

		
Social Networking Images and Links


					

				Michael Grove			

			


			

			

Getting your blog (and blog posts) out there into the Big Wide Internet can be difficult at the best of times, but trying to get people that do visit your website to help you out shouldn’t be.





Social Networking websites like Digg, Reddit, StumbleUpon and Facebook all have methods to allow their users to submit links to web pages that they find interesting, funny or just worth sharing. Allowing these users to quickly and easily add your blog post to their favorite website is a must for any blogger.





Below I have started making available the images I made for use on darwinshome (both the current size of 20px X 20px and the previous incarnation of 45px X 45px). Listed with the images are the links used to submit your website page to that Social Networking website.





This is not currently the full list of all the social networking websites available, as the weeks progress I will add more to the list until complete.

After a few weeks I will also design a wordpress plugin, to join the ranks of many like it out there, using the images and links listed.





[YOUR LINK URL] means the full URL of your blog post on yout website (eg: http://example.com/post/ )

[YOUR LINK TITLE] means the full title of the submitted page (eg: My Blog Post)





In alphabetical order:





Bloglines

Bloglines image small Bloglines image medium

http://www.bloglines.com/sub/[YOUR LINK URL]





De.licio.us

de.licio.us image small de.licio.us image medium

http://del.icio.us/post?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Digg

Digg image small Digg image medium

http://digg.com/submit?phase=2&url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





facebook

facebook image small facebook image medium

http://www.facebook.com/share.php?u=[YOUR LINK URL]





furl

furl image small furl image medium

http://furl.net/storeIt.jsp?u=[YOUR LINK URL]&t=[YOUR LINK TITLE]





GoGuide

GoGuide image small GoGuide image medium

http://www.goguide.co.za/submit.php?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Google Bookmarks

Google Bookmark image small Google Bookmark image medium



http://www.google.com/bookmarks/mark?op=edit&bkmk=[YOUR LINK URL]&title=[YOUR LINK TITLE]





ma.gnolia

magnolia image small magnolia image medium

http://ma.gnolia.com/bookmarklet/add?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Muti

Muti image small Muti image medium

http://muti.co.za/submit?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Newsvine

Newsvine image small Newsvine image medium

http://www.newsvine.com/_wine/save?u=[YOUR LINK URL]&h=[YOUR LINK TITLE]





Reddit

Reddit  image small Reddit image medium

http://reddit.com/submit?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Sphere

Sphere image small Sphere image medium

http://www.sphere.com/search?q=sphereit:[YOUR LINK URL]&title=[YOUR LINK TITLE]





Squidoo

Squidoo image small Squidoo image medium

http://www.squidoo.com/lensmaster/bookmark?[YOUR LINK URL]





StumbleUpon

StumbleUpon image small StumbleUpon image medium

http://www.stumbleupon.com/submit?url=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Tailrank

Tailrank image small Tailrank image medium

http://tailrank.com/share/?link_href=[YOUR LINK URL]&title=[YOUR LINK TITLE]





Technorati

Technorati image small Technorati image medium

http://technorati.com/faves?add=[YOUR LINK URL]





Instructions for downloading single images:

Internet Explorer:

Right click on the image you want and select “Save Picture As…” to save the image you want.





Firefox and Safari:

Right click on the image you want and select “Save Image As…” to save the image you want.





Opera:

Right click on the image you want and select “Save image…” to save the image you want.





[EDIT 11/03/2008] – Added Sphere, Squidoo, ma.gnolia, Bloglines, Tailrank and furl





License and Copyright:

All trademarks and logos are the property of their respective owners.

Logo designs in their current format are licensed to Michael John Grove under the Creative Commons Attribution 2.5 South Africa License. 

Should you be the owner of the original logo and wish your logo to be removed please contact me

					

	
	



			
				



	
	

		
JavaScript / XML based Country, State Selection Script


					

				Michael Grove			

			


			

			
JavaScript Country Selection list[UPDATE] The download is no longer supported or updated and has been removed


UPDATE 26 January 2009: Fixed the XML file load Bug that was affecting Chrome and Safari 


Making use of my XML based Country and State/Province file I have put together a small script to populate two drop down lists with the complete Country and the selected countries Province or State list.


The code is ALL client-side based, so it will suffer from browsers without JavaScript enabled, but it is a very fast and light weight script to use if you want to quickly add a Country and State selection box to your registration forms. This script does not require any database to function.


Code Explanation:

The initial XML Parser script:


if (window.ActiveXObject) 
{
    xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
    xmlDoc.async=false;
    xmlDoc.load("country_state.xml");
}
else if (document.implementation && 
document.implementation.createDocument) 
{
    var xmlhttp = new window.XMLHttpRequest();
    xmlhttp.open("GET","country_state.xml",false);
    xmlhttp.send(null);
    xmlDoc = xmlhttp.responseXML.documentElement;
}

xmlDoc.async=false;

xmlDoc.load("country_state.xml");



Internet Explorer has had a built in XML parser since version 5, but this parser opens XML files differently to other browsers (Firefox, Opera, etc), so to load an XML file you need to first check which browser is running on the client, For IE:


window.ActiveXObject


and a double check for other browsers:


document.implementation && 
document.implementation.createDocument


If IE exists, create an empty Microsoft XML document object:


xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async=false;
    xmlDoc.load("country_state.xml");




Or for Other browsers:


    var xmlhttp = new window.XMLHttpRequest();
    xmlhttp.open("GET","country_state.xml",false);
    xmlhttp.send(null);
    xmlDoc = xmlhttp.responseXML.documentElement;



Once the browser check is done it loads the XML document, from here on the code works the same in IE as in other browsers.


On a side note, there is an easy way to test that your XML file has been loaded correctly during development. Just add the document.write or alert function to return the number of nodes in the XML file (in my country_state XML example I would return the number of <country> nodes – with the expected result being 252):


document.write(xmlDoc.getElementsByTagName("country").length);



Once the XML file has loaded correctly, you can perform functions using the file. The next part of my code calls the fillCountryList() function that populates the first select box. I call the fillCountryList() function in my onload() event handler:

HTML:




JavaScript:


function fillCountryList ()
{
    var countryList = document.getElementById("cboCountry");

    for (var x = countryList.options.length-1; x >-1; x--)
    {
        countryList.options[x] = null;
    }
    var countryNames = xmlDoc.getElementsByTagName("country");
    var numberOfCountries = countryNames.length;

    for (var i=0; i<=numberOfCountries-1; i++)
    {
        var currentCountry =  countryNames[i].getAttribute("name");
        fillList(countryList,currentCountry,currentCountry);
    }
}



The fillCountryList() function identifies the first selection box with the specified ID (in my example cboCountry), the function then clears all current entries in the selection box. Clearing entries is good way to prevent duplicate information from being in your selection list prior to processing. On another side note, having VALID data in your selection box is an excellent way to still have your web page form display correctly if JavaScript is disabled on the client; this data can act as an alternative to the JavaScript data; an example of alternative data for the country selection box is listed.

Alternative HTML example:






The above code example is NOT a requirement, and my current example does not have any alternative data, but it can help your website to degrade well under different client environments.


The next part of the fillCountryList() function is getting the country names from loaded XML file:


var countryNames = xmlDoc.getElementsByTagName("country");


This will return an object collection for all nodes with the <country> tag to an array in the countryNames variable. A loop then processes the array and adds each country to the cboCountry select box using the fillList() function.


The state select box is only populated once a new country has been selected using the onChange event handler. Note: you should also be able to use the onClick event handler rather than the onChange event handler.


The fillStateList() function populates the States in the same way as the fillCountryList() function does, but first gets the selected countries array ID out of the country array before looping through THAT countries State/Province array therby filling the cboState select box with valid states.


Hopefully this code and explanation will assist beginners and experts alike. Download and enjoy.


This script has been tested under Firefox, Opera, and Internet Explorer 6 & Internet Explorer 7, Google Chrome and Safari 3.

Currently NOT working in Safari. Safari processes the “document. getElementsByTagName” function differently to other browsers, I will find a fix and release an updated version of the script soon.

UPDATE 26 January 2009: Fixed XML file load for Chrome and Safari :- Please drop me a line if you find any bugs that I may have missed.


Questions and comments are welcome.


License: Creative Commons Attribution 2.5 South Africa License.

					

	
	



			
				



	
	

		
Country Selection list with State and Province selections


					

				Michael Grove			

			


			

			
[UPDATE] Comments are closed on this topic – The download is no longer supported or updated and has been removed


Country State XML exampleI have been looking all over the Internet for an AJAX or similar dynamic drop down tool for Country and Province/State selection (You select a country from a drop down list and the country/state drop down selection automatically populates with that countries list of provinces or states)


The good news is I found various versions, from ASP.NET controls, to simple JavaScript files, but the catch was that although I had the tool, the tool, didn’t have all the counties of the world, never mind their respective states or provinces.


So after much trawling, and searching, I have a complete world country list (including Antarctica) with their respective states and I have placed them all together into an XML file for ease of development use:


– [download#4] - [29.3 KB ZIP file]


Please leave a comment should any errors exist in the file, from broken XML, to incorrect spelling, to having a province not listed, I will fix it if it checks out.


Hope it helps.


[EDIT1 11/03/2008] – Removed Yugoslavia. Changed “Macedonia, Former Yugoslav Republic of” to “Macedonia”. Added Serbia and Montenagro, with states. Thank you omBRE for pointing out my error on the NoBox Media blog.


[EDIT2 24/04/2008] – Corrected spelling of Antarctica (Thanks Ryan) and updated the “Regions” in New Zealand (Thanks David for pointing that out over on the NoBox Media blog).


[EDIT2 08/05/2008] – Corrected spelling of Czech Republic, Thanks Tim.


License and Copyright:

The country and province names in the file are public domain.

XML file in its current format is licensed to Michael John Grove under the Creative Commons Attribution 2.5 South Africa License.