【Query Builder : Expression 】ValueStoreSet and ValueStoreGet

Introduction

I am going to see how ValueStoreSet and ValueStoreGet expressions work in this post.
With these expressions, we can store a value into memory and retrieve the value from memory to use in a query session.
It is similar to the concept of preparing a variable in a general programming language, assigning a value to it, and then extracting and using the value.

Preparing Resources

When writing this article, I couldn't think of a good practical example.
Therefore, although it's not practical, I have prepared an example using these expressions to display the following results.

I will use a UNION query.
In the first query of the UNION (Parent Query), I will calculate the total amount of items in yellow, blue, and red from this dataset, and store them in memory using ValueStoreSet expression.
The results will then be displayed using ValueStoreGet expression in the second and subsequent queries.

The dataset is;

These rows are by UNION queries;

Query Definition

The parent query

"Code::String","Key Colour::String","Amount::Number"
"A100","RED",200
"A200","BLUE",159
"A300","BLUE",356
"A400","RED",211
"A500","YELLOW",255
"A600","RED",1200
"A700","BLUE",2100
"A800","RED",1940
"A800","BLUE",100
"A900","RED",320
"A100","RED",89
"A110","BLUE",20
"A120","YELLOW",12569
"A130","RED",568
"A140","RED",400
"A150","YELLOW",550
"A160","RED",600
"A170","BLUE",570
"A180","YELLOW",420

1. A comma separated list shown above is used as the data source

2. These 3 expressions actually calculate the total amount for each colour
   In case of YELLOW, when it is the first row and the row says "YELLOW" shows its Amount, otherwise 0
   From the second row, if the current row says "YELLOW", add its amount to the result of previous row of this column, otherwise just returns what the previous row of this column has

   When it comes to the last row, this column will have the total amount of the target colour

   IIF(StartOfReport
       ,IIF({%KeyCol}="YELLOW"
       	, {%Amount}
       	,0
   	)
       ,IIF({%KeyCol}="YELLOW"
       	,{%Previous:TotalforYELLOW}+{%Amount}
       	,{%Previous:TotalforYELLOW}
   	)
   )
   

3. They store the calculated total amount for each colour into memory with ValueStoreSet expression
   In case of YELLOW, this expression column does storing the current value of the above expression column for YELLOW by using the expression
   When it comes to the last row, the total amount for each colour is in memory with the given code YELLOWTOTAL.

   ValueStoreSet("YELLOWTOTAL", {%TotalforYELLOW})
   

The UNION queries

There are 3 unions defined.

Each union query displays the total amount for each colour stored in memory.
These queries are designed to return only one row as it just needs to display the total.
This is the example of "YELLOW".

The expressions (Code) and (Key Colour) show these titles.

The Result

As the result, the total amounts were well calculated and stored in memory by the parent query, and the total amounts were retrieved from memory by the union queries well.

Afterword

The solution mentioned here doesn't have a particular meaning, but I hope that this can show you how these expressions store and retrieve value in/out from memory.