【Query Builder】Mastering Prompt Filters in Query Builder

# Introduction #
I'd like to introduce how to use one of the features of Sharperlight Query Builder, the Prompt Filter. But before we dive into the main topic, let's provide a brief explanation of Sharperlight Excel Add-In and Query Builder. This explanation will be based on the SAP B1 data model.
To get started, open Microsoft Excel and use the Sharperlight Add-In (assuming you have Microsoft Office and Sharperlight installed on your machine). Select the cell where you want to set up a Sharperlight query, and then click the table icon in the Sharperlight ribbon. This is done to define the query that creates the table.
You'll see a user interface like this. This is the Sharperlight Query Builder. Regardless of which Sharperlight product you're using, you'll use this UI to create queries.
It's divided into three main areas:

* Top: Area for specifying filters
* Bottom left: Area for displaying fields and joins belonging to the selected table
* Bottom right: Area for specifying output fields

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fd45bd43-927e-5862-c5cd-8de3457cae65.png)
Also, the generated SQL statement might look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/af7506e9-d554-b847-849a-05ed66e116b1.png)
## Checking Normal Functionality ##
First, let's explain the use of a regular filter before getting into the use of the Prompt Filter. In this example, there are already four mandatory filters set up. This is defined when creating the Sharperlight SAP B1 data model: "If this table is selected, use these fields as mandatory filters."
Now, let's set the Canceled filter to N. We'll select the fields to include in the output from the drop-down list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b42567ab-2957-c5c8-79d6-6c73e2707a81.png)
Click the `OK` button to display the query result on the worksheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7f35e10c-2ffe-3a57-9b0e-f71561caf0a3.png)
The filter specifying `Canceled = N` is working correctly.
Now, let's take a look at the SQL statement generated by the Sharperlight engine. To view the generated SQL statement, open the query again and use the preview function.
Double-click the cell where the Sharperlight Table Formula was created to open the query definition. Click the `Preview` button to test the query. The query result will be displayed in a grid. Select `SQL Statement` from the information icon at the top of the grid to display the generated SQL statement.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5b25efb9-6be7-c25b-d6ed-c1359fb11ff2.png)
This SQL statement was dynamically generated using the Sharperlight engine and the SAP B1 data model.
## Disabling Filters with the Prompt Filter ##
We've seen how to use a simple filter without the Prompt Filter. However, when creating complex filter definitions in the Query Builder and checking the results repeatedly through previews, there may be times when you want to temporarily disable a filter. For example, you might think, **I struggled to create this filter definition, and I don't want to delete it.** In such cases, you can use the Prompt Filter option to temporarily disable the filter, which means excluding it from the WHERE clause.
Let's use an example to see this in action.
First, set the **CustomerCode** filter to **C20000** and preview it. Naturally, only records with Customer Code C20000 will be returned.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f1d03982-2ab2-3d18-711c-5bc4f0ec61f3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b765b296-d55c-6e68-6cfa-7122ced27bdc.png)
The SQL statement also looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/70d72253-51b6-4d84-4a82-d1a80d82ac51.png)
Now, let's say you want to temporarily disable the **CustomerCode** filter using the Prompt Filter option. Double-click on the part of the CustomerCode filter in the filter area to open the Filter Options.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2c8680ef-8146-6c9f-04d7-b8e4bb4f9443.png)
Check the `Prompt Only Filter` checkbox. That's all you need to do.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a547fcd4-df19-c441-e76f-0639d5344d46.png)
Close the Filter Options with the `OK` button. The background color will change to light yellow.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4dd9d4e7-b468-804b-8399-9c013835229c.png)
Use the `Preview` button to check the result. Even though you've set the **CustomerCode** filter to **C20000**, the `Prompt Only Filter` is turned on, so the **CustomerCode** filter is excluded from the WHERE clause, and records other than **C20000** are returned.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb48ad64-c2f3-7aca-978c-422cd74a2365.png)
Let's also check the SQL statement. The code related to the **CustomerCode** filter is not present in the WHERE clause.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8718c8c2-79d7-9e16-a8ba-52534936d465.png)
However, the filter definition is still there in the query.
## Using Prompt Filters as Parameters ##
Using the nature of Prompt Filters, you can also use them as parameters. Sharperlight queries allow you to pass filter values to, for example, subqueries. In this case, you typically pass values from the parent query's filters to the filters of the child query. However, in regular scenarios, the values received from the parent query's filters would affect the behavior of the child query.

In situations where you don't want the values to affect the child query's filters, or if you want to pass the received values to other filters, it can be challenging. This is where you can use the Prompt Filter options to achieve your desired behavior.

Let's edit the query we worked on earlier. Uncheck the `Prompt Only Filter` checkbox for the **CustomerCode** filter to return it to a regular filter. Now, right-click in the output area and select `Add Sub Query`. This will open the Query Builder for the subquery.

### When using an existing field ###
Drag and drop the **CustomerCode** field from the selection list into the filter area.

Next, click on the downward arrow icon next to the filter value input field. Search for and select the name of the CustomerCode filter from the parent query. This action will pass the value of the CustomerCode filter from the parent query to the CustomerCode filter in this query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c9650f6d-3654-e593-39fa-93d254448473.png)
Then, change the reference name of the CustomerCode filter to something distinct, such as **SubQCusCod**. You can change the reference name through the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/606e1f00-a8b1-d981-7953-e1fde676718c.png)
Finally, set this filter as a `Prompt Only Filter`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8486011b-8a83-91a9-4211-dd32c34d4de6.png)
Once again, drag and drop the **CustomerCode** field from the selection list into the filter area. Then, set the reference name of the **CustomerCode** filter, which is a `Prompt Only Filter`.

This mechanism allows you to receive filter values from the parent query in the child query using the Prompt Only Filter's **CustomerCode** filter and simply pass them to the active **CustomerCode** filter. While this example may not be practical, it helps illustrate the concept.
### If you want to use a dedicated field for parameters ###
Add a **Text** type field as a dedicated filter. To do this, open the filter area's right-click menu and select **Other** -> **Text**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e44b4a96-c7ad-8432-2a66-173f06f7260b.png)
This text field will be added as a filter. Set its label to **Parameter 1**, and its reference name to **P1** By default, such a filter is set as a Prompt Filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/43d27f58-a043-0016-2148-2c9b02012c11.png)
In the value field of this **Parameter 1** filter, specify the reference name of the **CustomerCode** filter from the parent query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3497afe2-488d-d78f-88be-89642f21b001.png)
Drag and drop the **CustomerCode** field from the selection list into the filter area. Then, set the reference name of the Parameter 1 filter, which is **P1**, in the value field of the CustomerCode filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4645af6e-fe6c-6b79-9b82-fa291daf0a77.png)
This enables the passing of values from the parent query to the child query.

# In conclusion #
While the example may not be highly practical, it should help you understand the concept. This approach can be useful in more complex report creation scenarios. Feel free to try it out!