【General】Cont. Example of building a solution using YouTube API

# Introduction #
This is a sequel to the previous article [Example of building a solution using YouTube API](https://qiita.com/Miki_Yokohata/items/e9f1cc9a9f39045055ba).
In the previous article, we were able to obtain such a dataset. We will use these data in this article.
Let's create our original menu.
## Publisher ##
**Publisher** is a Sharperlight application that can be used to create web-based menus.
Start it from the application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dc54b6ee-290e-e444-b903-7d3e7e5998af.png)
### Menu Report ###
#### General Tab ####
Enter the basic information for the report here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b9c81631-4c4d-e91f-e0d3-4979ad1ca936.png)
#### Option ####
In this tab, specify **Menu Panel** as the `Default Type`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33401bb7-60be-e680-3a8f-cefc99cb9a5c.png)
#### Query ####
Start building your query with the `Edit Query` button.
For the product (DataModel), of course, specify the `YouTube` created earlier.
Select `Playlists` for the table.
When creating the menu, prepare the Playlist title, the Video title belonging to each Playlist, and the VideoId required to play the video when the video is selected.
You can also see the JOIN to the Video table created in the DataModel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/17f97cf1-48d6-9e60-bea6-bf0fa8e60f4b.png)
Now save the query and report once.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/97f3ab0a-a6b4-f745-8aaf-5cd5587db419.png)
Start the Sharperlight service to check the operation.
Select the menu report you just created and select `View Report` from the right-click menu.
A menu report will be displayed in your browser.
This image shows a certain Playlist item is expanded.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/beba198a-49c2-fd05-f1ba-b6e6af937fd6.png)
### Report for playing the video ###
Prepare to display the video when the Video menu item is selected.
Create another new report for video playback. And create a method that calls the report for playback from this menu report.
#### General Tab ####
Enter the basic information for the report here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f7f0711a-7b40-5035-06d0-f4e83b1f0848.png)
#### Query ####
Start building your query with the `Edit Query` button.
Specify `System` for the product (DataModel).
The table selects `User Accounts`.
Mode (a mandatory filter for this table) remains CU.
This query only needs to return one row of records, so we use these filter settings.
Then add one filter of type `Text` and make it a variable that receives the VideoId passed from the menu report. This filter is a **prompt** filter and has no effect on the query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f4254365-4680-910e-82ab-e252eb1ec0d3.png)
Since the purpose of this report is to play a video, we will create a definition that uses an  expression to create an ifram tag and embed a YouTube video.
Select `Add Expression` from the right-click menu of the output area.
Enter the following code in the syntax editor. Specify HTML as the Data Type.
```
"<iframe src='https://www.youtube.com/embed/" + {@videoId} + "'"  
+ " width='1200px' height='800px' scrolling='yes' frameborder='0'  allow='accelerometer; autoplay;'></iframe>"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f65fae8-68f9-99b9-1b5c-6addb90c96e3.png)
The tag `{@videoId}` in the syntax is a reference tag for the prompt filter to receive the VideoId passed from the menu report. It will be replaced with the actual value at runtime.
In this way, the output item is only one expression. Leave the `Description` field blank as it is not needed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eef4b534-71fd-368d-817e-6b9233c97ef1.png)
This completes the report for video playback. Save the query and the report.
### Updating the Menu Report ###
Now add code to the menu report to call the video playback report you just created.
Open the query of the menu report and add a new expression.
Enter the following code in the syntax editor and specify **URL** for the Data Type.
```
"{_System.Rest.URL}" + "Report/?query=QiitaSample.YouTubeVideoPlayer&fltvideoId=" + {%VideoId} + "&usid=" + "{_System.Rest.Usid}"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9481afbe-ca32-cd5f-9a48-1d9be2c851a1.png)
The tag `{_System.Rest.URL}` in the syntax is that gets the base URL of the Sharperlight service. This `{_System.Rest.Usid}` is for session ID. Also, `{%VideoId}` is a tag that refers to the VideoId of the current line.
Enter the URL in the Name and Description as well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4fdfe437-ed3d-536c-49d1-6a1d0d5935d7.png)
Also, set **VideoId** to Hidden.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b7e72819-684b-1d4f-38dd-720aa89c82f4.png)
The menu report is now complete. Save the query and the report.
## Operation Check ##
Select `View Report` from the right-click menu of the publisher dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5f935c24-6e6a-32a3-ac6e-a41118037ef5.png)
A menu report will appear in your browser and if you click on a sub-menu, ie a video, the selected video will be displayed like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7c9f323-317c-7ac2-c010-6105ecc6186f.png)
## Extending Menu Panel ##
As it is, the number of characters in the submenu is large and it is difficult to see. I'll edit the query a bit to make it shorter.
Reopen the menu report query and add a new extended expression.
Extract only the characters after **Vol** from the video title.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b615019-ad99-843c-9ec2-182fdfa98fba.png)
Hide the original video title.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0695005c-a064-f836-6c88-a10e5c223881.png)
Also add CSS to increase the width of the panel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ceb4521f-9a8a-af16-9a67-52f52b09a976.png)
```
<style type="text/css">
    #slTreeViewSection{
    	width: 255px !important;
    }
</style>
```
Now save it and run it. It was pretty clear.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0f2aae67-54a6-0089-8de3-703548b59f48.png)
## Adding Default Page ##
As it is now, when the menu report is first displayed, the middle area is blank and ugly.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/025d3dc3-8480-30d1-ec3d-b50d095d154f.png)
Let's set some default pages.
Reopen the menu report definition. Set the `Default Content` property of the Options tab to a URL like this.
The address of the Index page of the Sharperlight service.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c6389c0a-ddd4-6624-8476-8213ebbe71e3.png)
If you save and run it, it will look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7b05b7ea-bbad-f177-ab8e-0b791622bb1c.png)
And if I select a video... it looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3edc3de1-d026-c27f-16a2-e07e06a211e5.png)
# In Conclusion #
This time I tried to create a menu report. I can't do much because the data only has this kind of information, but I think these methods will be useful somewhere.
I hope this article helps you in your work.