【Query Builder】Prompt Filter: Used as a determinant of the value for another normal filter

This is an article related to the article called [【Query Builder】Try using Prompt Only Filter](https://qiita.com/Miki_Yokohata/items/c73263cf8d02406a82b3).
Here's an example of using a Prompt Only Filter. This use case may not be practical, but I hope it gives you the basic idea.

The use case uses the SAP Business One datamodel and filters on the **Document Date** column based on the value passed to the Prompt Only Filter.

Create a report in the [Sharperlight](https://www.sharperlight.com/) Publisher and try using the Prompt Only Filter in the report's query definition.
First, launch **Publisher** from the [Sharperlight](https://www.sharperlight.com/) application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb134456-c311-5bd9-b770-59ad09524c43.png)
Once the **Publisher** is running, start creating a new report with the `New` button.
# Edit query #
The `Edit Query` button launches the **Query Builder**.

Select `Product`, `Company`, and `Table` as shown below. The default mandatory filter associated with the selected table is automatically populated. Use them as they are.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4e352485-63d5-767e-bf69-ef7ec5bbc03f.png)
## Filter definition ##
In this example, we pass `Year` and `Season` as arguments. Therefore, prepare two Prompt Only Filters.
Add two types, Number type and Text type.
Open the **Other** submenu from the right-click menu in the filter area of the query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9c241809-d8bc-46d8-8225-04efdb74845d.png)
The submenu allows you to select different data types.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f47fbefc-e154-5ffe-9ac3-7da969ea1200.png)
### Prompt Only Filter: Year ###
First, select **Numbers** for adding a number type filter to the filter area. In the filter description dialog that appears, enter **Year**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0981353a-c0a7-15f3-b6cc-41c4991fefdf.png)
Open the Filter Options dialog and set the `Filter Name`, `Description` and `Single Values Only` options as shown below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/899f23b5-3f55-599f-0d2f-96068606375f.png)
### Prompt Only Filter: Season ###
Then add a text type filter to the filter area. In the filter description dialog that appears, enter **Season**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e455fbf0-fd92-c56c-581a-9b6852a37eb8.png)
Open the Filter Options dialog and set the `Filter Name`, `Description` and `Single Values Only` options as shown below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/263d6d68-0521-aafc-af43-25575157ba34.png)
It should look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/307f5d22-60df-00cd-44c3-b9f02b9055d5.png)
### Normal Filter: Document Date ###
Sets the normal filter used in the WHERE clause of the query. And use the Prompt Only Filter defined here.
Drag and drop the **Document Date** column from the selection list to the filter area.
Begin by opening the Filter Options dialog and setting the options.
On the `Functions` tab, set this **Year/Month** option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/820b7493-4d0d-2606-1586-84892339f09b.png)
Next, set the From value and To value to use the value of the Prompt Only Filter using an expression.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f22188f1-55d1-aace-8f6f-4fedbd368faf.png)
#### Operators ####
For Operators, select **Between**.
#### Operator or Value (From) ####
For `Operator or Value` (From), set the Switch statement using an expression. The button on the right opens the editor.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b690c71d-e05b-5612-6d9a-27bcf779cba9.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6b858ff0-f58f-a99a-c1ba-7eed5370b10a.png)
If **2006** is passed to `Prompt Only Filter: Year` and **Spring** is passed to `Prompt Only Filter: Season`, then the evaluated result `2006/03, which is the start month of spring, will be 2006/06 for Summer and so on.
```
_Expression(
    Switch( "{@Season}"
        ,"Spring","{@Year}" + "/03"
        ,"Summer","{@Year}" + "/06"
        ,"Autumn","{@Year}" + "/09"
        ,"Winter","{@Year}" + "/12"
        ,"<ALL>"
    )
)
```
#### Value (To) ####
`Value` (To) is also set to a Switch statement using an expression.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5052429d-7175-c56d-a896-1dec6e3bdaa3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/feed8db7-0afa-aecc-47d8-871879b1b463.png)
If **2006** is passed to `Prompt Only Filter: Year` and **Spring** is passed to `Prompt Only Filter: Season`, the evaluated result 2006/05, which is the last month of spring, will be is 2006/08 for Summer, and so on.
In the case of **Winter**, add 1 to `Prompt Only filter: Year` since the month ends in February of the following year.
```
_Expression(
    Switch( "{@Season}"
        ,"Spring","{@Year}" + "/05"
        ,"Summer","{@Year}" + "/08"
        ,"Autumn","{@Year}" + "/11"
        ,"Winter", CStr({@Year}+1) + "/02"
        ,"<ALL>"
    )
)
```
## Output item definition ##
Set the following columns as output items from the selection list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2a730b78-32ee-c6ce-cfa9-69c7295d8595.png)
## Query preview ##
Enter **2006** and **Spring** in `Prompt Only Filter: Year` and `Prompt Only Filter: Season` respectively, and press the `Preview` button.
Check the value of the Document Date column in the output results. If everything is in the range 2006/03 to 2006/05 then the filter is working fine.
Let's check **Summer**, **Autumn** and **Winter** in the same way. Especially in the case of **Winter**, it is OK if everything is in the range from 2006/12 to <font color="Red">**2007**</font>/02.
<br>
Finally, go back to your query and set all filters except `Year` and `Season` to **Hide** and save the query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c1b24ef0-172d-dc2e-ce86-fe3992daf7a5.png)
# Validation from report #
When you return to the published report dialog, fill in the **Code**, **Group** and **Title** and save.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/321a26b9-58c1-5056-af5b-24ddfc07bb28.png)
Start the Sharperlight service and try to display it in a browser.
It should look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/738478d1-3ae7-2f4b-93ed-11dd0ca01533.png)
<br>
This time, I wrote this article with a non-practical example, but I think it was able to introduce how to use the **Prompt Only Filter** and how it works.