Lightbox – Webinar – Millennial Office of Finance

The purpose of a Union Query in Cognos BI is to take data from two or more different queries and merge or union them into one query.  Sounds simple enough, right?  In order to accomplish this, there are a few rules that must be followed for the union to work correctly.

  • The queries used to form the union query must have the same number of data items
  • The data types must have compatible types and appear in the same order
  • Numeric types that are compatible are integer, float, double, and decimal
  • String types that are compatible are char, varchar, and longvarcahr
  • Binary data types that are compatible are binary and varBinary
  • Date types must match exactly

For instance, you have three queries that you want to report off of; one is actual numbers, one is forecast numbers and one is budget numbers.  Your queries also include Account number and location.  You can union these by ensuring that actual, budget and forecast are included in each query.  You simply create a data item and set it to zero for each of the data points that don’t exist in each query.  Your queries would look like this:

  • Account, Location, Actual, Forecast (set to zero) and Budget (set to zero)
  • Account, Location, Actual (set to zero), Forecast and Budget (set to zero)
  • Account, Location, Actual (set to zero), Forecast (set to zero) and Budget

Union query the above three points together and you can now write a report that contains each data point!

Crosstabs Dimensional Data in Report Studio

​Crosstabs Dimensional Data in Report Studio

Let me first start by saying this is not a how to or even necessarily the right way to do things with Dimensional Data in Report Studio, rather some options to explore learned from hours spent cursing at the computer trying to get the seemingly ‘simple’ things to work in crosstabs. You know, things like adding two columns together, calculate a variance, or filter out crosstab rows with no data. I run into these issues most when I’m building financial reports where we’re comparing multiple forecast/budget versions across specific spans of time.

Dimensional Data in Report Studio

----The Tuple

Love me some Tuple! Whenever I need to pick some data point seemingly out of thin air… the answer usually lies in the form of a tuple. Basically, the tuple function allows you to pinpoint cross sections of dimensions. Note: it only works with data MEMBERS!
Sample: tuple([2009],[Budget])

----Calculating Totals/Variance
For those occasions when clicking the nice little ‘Total’ icon isn’t working for you… don’t be afraid to explore the currentMeasure function and build the total yourself. It will really help you start to understand what the crosstab is trying to do when you click the ‘total’ icon and help you create more robust crosstabs. Some sample syntax:
Sample: total(currentMeasure within set[Forecast]).
If your Forecast is nested in the crosstab you might want to try using within detail.
Sample: total(currentMeasure within detail[Forecast]).
This same logic can be extended to help compute variance.
Sample: total(currentMeasure within detail [Budget]) – total(currentMeasure within detail [Actual])

----Filter crosstab rows.
I recently created a report where I had to show Hours worked by employee by project for the entire company. Many projects had employees that didn’t have any worked hours allocated to a project for a particular time period… yet these employees were still showing up in the report. Naturally, the report ballooned to well over 100 pages. To fix this, I embeded the filter function in a new data item to help filter out the unwanted rows.
Sample: filter([Employee],tuple([2009],[Actual Hours]) >0 or tuple([2009],[Actual/Fcst]) >0)

As a side note to this, in Cognos 8.4 there are options to filter out rows with nulls and zeros, however, I know most of you are still 8.3 or earlier versions. So until you’re on board with 8.4, hopefully this will help you stay afloat!

If you liked this blog on Crosstabs Dimensional Data in Report Studio, then check out this blog on what is Cognos TM1 performance modeler by clicking here.