List Comparison for Queries Using Hierarchical Meta Data in InetSoft's Business Intelligence Software

If you want to get a list of all employees with C++ programming skill, you can add a condition to the employee node to select only people with C++ on their skill list. However, since each employee may have multiple skills linked in the employee record, a simple string comparison does not give the correct result.

skill = 'C++ programming'

This condition would work if all employees only had one skill listed. If the C++ programming were listed as the second skill of an employee, the comparison would return false because a list of skills is converted to a scalar value by using the value of the first child.

You need an expression to compare the skill list with a value, and the comparison should be true if any value on the list matches the skill. The following list comparison expression returns the correct result.

dashboard demo
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

List Pattern Matching for Queries using Hierarchical Meta Data

Here we introduce a slightly more difficult problem. Suppose you want to get a list of all employees with any programming skill, regardless of the particular programming language. Since there are no fields in the XML structure that specify this information, it will have to be deduced from the skill description. Assuming all programming skills end with ‘Programming', you can find employees with programming skills by matching the skills against a regular expression pattern, ‘.programming'. The following is a list pattern matching expression:

any skill match '.*programming'


Follow the following steps to create an ‘All Programmers’ query.

1. Create an XML query as before, with the name “All Programmers”.

2. Select the ‘Employee’ node and click on ‘Select Record’ to make it the selection tree path.

3. Enter the condition expression in the condition text area: ANY skill match '.*programming'

4. Select ‘Save’ (on the main toolbar) to save the expression.

5. Select the ‘Map to Table’ option, then follow the same procedures to map the result sub-tree to a table.

Read the top 10 reasons for selecting InetSoft as your BI partner.