【Query Builder：Expression】Try creating Slicer

This time, I will create a slicer like the one below using the `Expression` of [Sharperlight](https://sharperlight.com/).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5dad4e30-e528-5bf6-47a4-c50876ba67aa.png)
### Creating Year Slicer ###
Launch **Publisher** from the [Sharperlight](https://sharperlight.com/) application menu and create a new report. Launch the **Query Builder** with `Edit Query` button.
Then create a query like the one below.
A query that returns a list of years from 15 years ago to next year.
Specify a descending sort for year.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b47c7efa-5ca5-0a21-dd87-e645b4be099d.png)
Right-click in the output area and select **Add Expression**.
Select `HTML -> Slicer -> Checkbox Filter Slicer -Multiple Filter values as URL parameters` from the list of functions in the `Expression` tab.
The template will appear in the syntax editor.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c7a74623-f7e9-369e-3560-235c774f2496.png)
This time, I will only use the Year filter, so edit it as shown in the figure below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/25247919-99fe-646f-47b8-2f0578225b9e.png)
The result of editing is as follows.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/211bc9fb-29d6-e8be-bbb2-8041a0e726e2.png)
The Description for this expression is **YearCheck**.
The actual Year field should be hidden as it is for reference.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/959180fb-214a-c62b-3fd1-96616ab1336d.png)
Click `OK` to save the query and click `Apply` or `OK` to save the report as well.
Make sure Sharperlight service is running.
Let's see the query result in the browser.
It is like this so far.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/82d132bc-4238-1ff4-3b8c-39ac28e05ffa.png)

Next, add an `All` checkbox to the above query results so that all years can be selected.
Right-click the output area again and select **Add Expression**.
Search the template which is under Functions -> Table Rows.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7cb5e3f4-39cf-25e6-c815-fe299ac19912.png)
Amend it and create the syntax shown below.
This is the syntax to create a checkbox called `All` at the top of the query result.
Hide this expression because it is processed internally.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b74db18-c2a1-721c-c0f7-ca99687ca048.png)
```
RowHeader( StartOfReport, "Col:1",
"<input type='checkbox' select='all' id='CVAll" + DataRowCount() + "'"
+ " value='" 
+ "&fltYear=" + UrlEncode("<ALL>")
+ "'>"
+ "<label for='CVAll" + DataRowCount() + "'>"
+ HtmlEncode("All")
+ "</label>"
,"")
```
Now the output items should look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4c0298e6-de39-8783-ad8b-19b2b97c684c.png)
Preview it. It works fine.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/98649123-641b-96d3-9672-d36f4d95c454.png)
Click `OK` to save the query and click `Apply` or `OK` to save the report as well.
Make sure Sharperlight service is running.
Let's see the query result in the browser again.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/149a38ac-0552-303f-0d13-9914413442e8.png)
### Creating a report that work together ###
Let's create a report using the SAP Business One data model and work with the slicer we just created.
Select the `New` button in **Publisher** to start creating a report.
Launch **Query Builder** with the `Edit Query` button.
Configure the filters like this;
- `Mode`: **Summary Report**
- `Product`: **SAP Business One**
- `Company`: **OEC Computers Australia**
- `Table`: **G/L Transactions** 
- `G/L Account Code`: **\<ALL>**
- `Year/Period Code`: **\<=** and **PERIOD** 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/82100756-5946-b6b5-6985-1094cf4d8f86.png)
Set `Posting Date` field as a filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3c4b387c-7c22-452d-fb8c-34143c2b3d91.png)
It should use its Year part only.
The option for `Posting Date` filter should be;
- User its Year part only.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5d6dcc0b-4436-f612-2196-492c60bb599b.png)
- The name should be **Year** and must be a Single Values only.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6d960a4e-b20a-8183-d854-4943caa1f3be.png)
The filter name **Year** is bound by this code in the slicer.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/68021e1b-0bfd-9b4f-2384-4c38570a6c02.png)

The output items of this report can be like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33e55b98-a79f-286a-c09b-76bdaecd9691.png)
Save the query and the report.
Update the Code for the slicer and the report works with the slicer.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0ca59cf4-eae6-cf58-aee4-def08ab59ba0.png)

### Putting the Slicer and the Report together ###
Now that I have a slicer and a report linked to it, I can combine the two.
Create a new report in **Publisher**.
Add a query and the query should be a simple query as it does nothing this time.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/982fb343-9b04-8bd5-a4b5-5322caf5f20a.png)
Enable the `Layout` option on the `Options` tab.
Open the Layout dialog with the `Edit` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/00664632-6e0b-49d9-f9ea-77447978c0ba.png)
In the layout dialog, specify 2 columns display.
Then specify the slicer and the report which works with the slicer.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/23650304-fc3e-0e06-c219-1881ae240e5e.png)
For the slicer, the following options should be selected.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e82f7d4d-2748-ee55-f9c2-b63d65b8a283.png)
For the report, the oprions are like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f265cf50-403f-3228-399f-284941cbdb11.png)
Click `OK` to save the layout, and save the container report.
### Verification ###
Open the container report in the browser.
In this way, the slicer is displayed on the left and the linked report is displayed on the right.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bbd484ff-66ad-2d9f-3f73-ae1ebfb4e759.png)
Select 2013 with the slicer. The linked report is refreshed and shows data for 2013 only.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0b3dad8d-3326-7a22-14fb-5d6b67997bf4.png)

This is an example of creating a simple slicer.
I hope this will help your work.