---
title: "Union Sub Query"
canonical: "https://wiki.yellowfinbi.com/space/USER72/2833009/Union%20Sub%20Query"
format: markdown
---
> Macro (anchor)



> Macro (toc)

## Overview


> Macro (html-bobswift)

 

## Introduction

In this tutorial, we will walk through setting up a **Union** sub query. A good way to think about **Unions** is - use them if you need to combine multiple fields into the one column, either from the same or different views or sources.

In this scenario our main report will have a list of invoicing figures by region, we will then use the Union query to display negative cost figures in the same column, and then add sub totals to see the total income for each region.

 

### Master Query

The Master Query is going to include:

1. Camp Region
2. Calculated Field: "Invoiced" text label
3. Sum Invoiced Amount

 

### Union Query

The Union Query will include:

1. Camp Region
2. Calculated Field: "Cost" text label
3. Calculated Field: Sum Cost * -1 (to convert the Cost figures to a negative value)

 

## Create


> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

 

## Master Query


> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

### Label Field

To complete the master query, a label field is required. This will be used to identify the rows from the master query as "Invoiced" rows. There will be a similar calculation in the union query to identify rows as "Cost".

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

## Union Query


### Create Union

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

### Calculations

To complete the union query, two calculations will be required;

1. Label: this will be used to identify the rows from the union query as "Cost" rows. There will be a similar calculation in the master query to identify rows as "Invoiced".
2. Cost: this will be used to convert the cost figure to a negative value. This will mean that when a sub total is added to the report, the cost amount will be subtracted from the invoiced amount.

 

#### Label

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

#### Cost

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

### Configure Union

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

### Edit Settings

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

## Formatting


> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

 

## Save


> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)