【Publisher】Cascading Report

I am introducing how to create cascading reports with SAP Business One as the data source.
To do this, create parent and child reports that make up the cascading report.
## Parent Report ##
Launch **Publisher** from Sharperlight application menu.
Click `New` button to start creating a new published report.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/67a37dd3-4b6d-9cd8-b6fc-2cb3ea8453b1.png)
Fill in **Code**, **Group** and **Title** like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/331d6ffa-7b50-ab32-8612-d50c5f66168c.png)
Then click `Edit Query` button at the bottom to start designing a query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/862d0dd8-b09e-49c1-7e5d-ec1a848b1f41.png)
### Query for Parent Report ###
This query will show the list of Documents and how many invoices each document has.  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/821f64e3-f5fe-becf-1a39-69b59823da73.png)
#### Filters ####
**Mode:** Choose `Summary Report`
**Product:** Of course, `SAP Business One`
**Company:** Choose `OEC Computers UK`
**Table:** Select `Payment Draft`
#### Outputs ####
At the point when `Payment Draft` table is selected, the Selection tree view is populated. It shows all fields from Payment Draft table and some joins to other tables from it.
This time, choose **Numerator** as `Id`, **Document Number** as `Document Number - Parent`.
Then select **Document Number** in joining table **PDF2\Payment Draft - Invoices** and set `Count` to its attribiute.
Add an expression and define a text message like this. As you can see, it refers to the counting field above. It is the reason why the field is hidden.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/05657300-43ed-2391-8e80-0d8f14c4d5e5.png)
Let's preview the result by clicking `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c24b78c5-8beb-335c-da7e-fb8fc0aa8dcc.png)
Go back to Query, then we set a key which is making a link to the child report.
We use the value of `Id` as the key and set its name as **pdf2DocNum**. This name will be used by the query in the child report.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/43cc88cf-5383-b5b4-8deb-58a51c1708a1.png)
Click OK to confirm the query.
Move to `Reports` tab and set **Table Grid** for `Default Type` option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c63975ac-125a-9d32-2686-9eab8d9818f9.png)
Click OK to save the published report.
## Child Report ##
Click `New` button to start creating a new published report.
Fill in **Code**, **Group** and **Title** like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7741010-b095-d788-8895-4579e6bbcdf1.png)
Then click `Edit Query` button at the bottom to start designing a query.
### Query for Child Report ###
This query will show the list of Invoices for a parent document number.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5fbba0bf-bac2-3927-67e5-05c4ab247389.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/774e3734-4bee-2e99-6077-c321b1ff881d.png)
#### Filters ####
**Mode:** Choose `Summary Report`
**Product:** Of course, `SAP Business One`
**Company:** Choose `OEC Computers UK`
**Table:** Select `Payment Draft - Invoices`
This query has to be passed the document number from the parent so set **Document Number** field as a filter.
This is the key so its name needs to be the same name as the key in the parent query. i.e. **pdf2DocNum**.     
#### Outputs ####
Choose some fields you want to display as the lits of invoices.
Run the query with `Preview` button. You have to type any number into **Document Number** filter to see the result.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f14acbb-e742-f427-f040-9fec4964a3a0.png)
Once you confirme the query runs fine. Click `OK` to save the query.
Move to `Reports` tab and set **Table Grid** for `Default Type` option.
Click OK to save the published report.
## Making the link between Parent and Child ##
Open the parent report again, and go for `Table` tab.
Tick `Details Report` option and type the name of the child report by it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/34ac3ab4-8342-1f59-70f8-b7d786c5170c.png)
Click `OK` to save it.
## Showing the report in the browser ##
Start Sharperlight RESTful Serive from Sharperlight application menu or Windows Service console.
Select `View Report` from the right-click menu of the Publisher. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7f05e1d8-866e-198c-8b44-58211b7fa250.png)
The report should look like this.
The parent query displays a list of documents. 
When the right arrow icon of each row is clicked, the detail of invoices is shown by the child query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f71a6a09-c259-4e90-bd20-8afb41e442bc.png)
Thank you for reading this article.