BQL in N4/AX

Introduction

BQL is an incredibly useful language that can be used within all version of Niagara. BQL stands for Baja Query Language; It’s made to search for specific data within your station, and return groups of whatever objects you need. A working knowledge of BQL is very helpful for working with many more advanced techniques and modules, from the many Vykon Pro modules that depend upon BQL queries, to quickly grabbing device summaries and basic data tables during commissioning.

At any point within a Niagara station, you can use ctrl+L to pull up a text field that allows you to navigate to any manually input Ord (Object Resolution Descriptor, a sort of internal Niagara URL). These Ords are also what you get when you copy an individual component inside of a station. Below is a quick overview of the station data we’ll be working with:

For our first example, let’s say we want a fast, easy report of all of our zone temperatures. We can hit ctrl+L, and then enter the query shown below:

There are quite a few pieces to these query to explain, but this should give a basic idea of what’s happening. Our base ord just defines the “scope” of our query; In our case, the base ord is “station:|slot:/DemoSite/BuildingA”. This ord can be copied by just right clicking any component and choosing “copy”. If this is pasted in anything other than a text field, it will duplicate the component. However, if we paste it in a text field, we get the ord of that component. The “|” signifies that we are using a separate ord scheme. This is common within Niagara for many things; For example, if you use ctrl+L while looking at a slot sheet view, you’ll see “|view:workbench:SlotSheet“ at the end of your ord. “view:” is another ord scheme that tells Niagara how to visualize the component you’re looking at.

BQL Select Statement Breakdown

Let’s look at a more complex example to break down the BQL query itself. The example below uses every single optional parameter a BQL query can use:

I’ve split each section of the query with red, to easily call out each parameter being used.

A) This is the base ord being applied, as mentioned above. The query just won’t return any components that are not children of the base ord.

B) This is our basic select statement, as well as our “projection”. Every BQL query will start with a base ord, followed by “|bql:select”. The following text defines what data we want to receive. In our case, we used “parent.name, name, out.value”. These are used as our columns, and resolved and populated by each result’s info when we run our query. This is basically BFormat, like what’s used in PX text animations, so you can access nearly any information this way by using the names of slots under the target. You can even run some specific BQL “scalar functions”, such as “SUM(out.value)“, which can combine multiple results or multiple separate data types.

C) This is one half of our “predicate”. This is where we start to filter down to the results we want. In this case, we use “from control:NumericPoint”, which tells the station to ignore all components that aren’t of the type control:NumericPoint. The same can be done for any types, such as niagaraDriver:NiagaraStation to return only station connections.

D) This is the second half of our “predicate”. This is where we start to get far more specific with our filtering. Here, we used “where name = ‘ZN_T‘“. This will remove any results that don’t match this statement. In this case, we grabbed only our ZN_T (Zone Temp) points. This section will most commonly require the most thought put into it, and has the most varied functionality, but ultimately, just remember that this is just a statement that ends up as true or false, to filter out results, and it becomes a bit simpler to figure out. The specific syntax here is well-documented within Niagara, and can be access by using ctrl+L and entering “module://docDeveloper/doc/bqlExpressions.html”.

E) Our final section can re-order our results, which can be very helpful for certain use-cases. In this example, we sorted our rows by zone temp, highest to lowest by using “order by out.value DESC”. Much like in our projection and predicate, we can use any specific property in mostly the same way as BFormat in PX graphic text animation. We could go lowest to highest instead with “order by out.value ASC“, we could alphabetize with “order by name DESC”, or even group by ord location with “order by slotPath DESC“

Use-Cases

As we’ve seen above, we can use BQL for some fast, easy data summaries and reports, and this is very useful for everything from quickly assessing a station you’ve never seen, to checking many values during commissioning. However, within a station, BQL can be used for many powerful features.

One of the first uses of this is with some components in the VykonPro modules. These are often-underappreciated modules from Tridium with many very useful components. One particular component is the “MinMaxAvgBQL” component. The property sheet of this component is shown below:

As the name implies, this will use BQL to find a number of matching components, then continuously update with their minimum, maximum, and average values. The “Point Query” slot is where our (hopeful) newfound BQL knowledge comes in handy! However, this has a way to edit BQL that can be easier for some (Although it’s a bit limited). If you choose the arrow next to the folder icon in Point Query and choose BQL Query Builder, it gives the UI shown below:

Much of this will be rather self-explanatory with a working understanding of BQL. Find→In is where you define the base ord, Find→Of type is where you can specify your type like in your “from” statement, and the Match box is where you define your predicate conditions. This same UI is present in several places, such as the batch editor.

Another powerful use of BQL actually involves another simple ord scheme, the “cell” schema. This allows you to grab a single value from a resulting table. This is pretty simple, but it’s deceptively powerful. For example, if you have a PX graphic with an overview of a group of VAVs, you can grab the highest zone temperature dynamically with a properly formatted BQL query and this cell ord schema.

For example, let’s say we point a BoundLabel at “station:|slot:/DemoSite/BuildingA|bql:select name, out.value from control:NumericPoint where name = 'ZN_T' order by out.value DESC|cell:1,0”

This will create our result table as usual from our BQL query, and then we add another “|” to denote a schema change, and then use cell:1,0. This just says to discard the rest of the results and return the value at column 1, row 0.

As you can see, this will bind the label to a value of 79.20. This will re-evaluate each time the page is loaded, and since we’re sorting our results high to low, this will always give us the highest value.

Additional Resources

The above information is a lot to take in at once, but fortunately, BQL is very easy to just play with in a station; After all, it’s making no changes to the database, and can be used anytime by just using ctrl+L and adding your query to your current ord. Tridium also documents BQL (mostly) quite well, and as I mentioned in D), you can use ctrl+L and paste documentation ords to go directly to these docs. Below are several pages with some very helpful information to refer back to in the future:

Basic BQL Summary: module://docDeveloper/doc/bql.html

Official BQL Examples: module://docDeveloper/doc/bqlExamples.html

BQL Expressions (The syntax in the later half of your predicate): module://docDeveloper/doc/bqlExpressions.html