【Query Builder】Prompt Filter: Used in output items

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 uses the value passed to the Prompt Only Filter in the output item.

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 `Season` as argument. Therefore, prepare a Prompt Only Filter.
Add a 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: 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/0db8d3b7-00e2-83db-2eaa-8077a185acb8.png)
## 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/26f57ce2-5873-9b8a-cc2b-438510c08d05.png)
I have set two **Document Date** columns to output items. The upper one is for output (display) and I set the **Year/Month** option for it. The bottom one is for use in the expression added below, and I set the **Month** option for it.
- **Year/Month** option: 03/28/2023 -> **2023/03**.
- **Month** option: 03/28/2023 -> **3**.

### Expression: Season ###
Calculate the season for each row.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/98d9baf5-4396-aa90-c61e-3dce18a06d54.png)
```
Switch( {%DocDat}
,1,"Winter"
,2,"Winter"
,3,"Spring"
,4,"Spring"
,5,"Spring"
,6,"Summer"
,7,"Summer"
,8,"Summer"
,9,"Autumn"
,10,"Autumn"
,11,"Autumn"
,12,"Winter"
,"n/a" )
```
### Expression: Season Color ###
Fills the background color of rows where the value passed to **Prompt Only Filter:Season** matches the value of the output item **Season** with the specified color.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0ada516f-d5ab-392a-e7d6-af714d934920.png)
```
IIF({@Season}={%Season}
	,IIF({%Season}="Winter"
		,"_format:bcolor[0,0]=#D3DBE2"
		,IIF({%Season}="Spring"
			,"_format:bcolor[0,0]=#6CBB5A"
			,IIF({%Season}="Summer"
				,"_format:bcolor[0,0]=#73B8E2"
				,IIF({%Season}="Autumn"
					,"_format:bcolor[0,0]=#C37854"
					,"-"
				)
			)
		)
	)
	,""
)
```
`Document Date`, `Expression: Season`, and `Expression: Season Color` are for reference and are for coloring only, so they are set as hidden items.
The final output item looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/42831170-feae-ca83-e640-c6aa667669f3.png)
## Query preview ##
Enter **Spring** in `Prompt Only Filter:Season` and press the `Preview` button.
Check the background color of the output result and the value of the Document Date column. If only the rows with the same season as the season specified in `Prompt Only Filter:Season` have the specified background color, it means that they are working properly.
<font color="Red">The thing to notice here is that `Prompt Only Filter:Season` is not working as a filter for the query. </font>
Please check **Summer**, **Autumn** and **Winter** in the same way.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7c1fc5f0-2177-9b07-0a5a-0003675ebbf2.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/68752057-fba8-4532-5ca9-d81bc9bd40fb.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/fe938ebf-a178-9a39-21b2-def77a671594.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/891330b6-2ca6-ee3a-fa1a-d1c6bc9893e5.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.