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.
Fill in Code, Group and Title like this.
Then click Edit Query button at the bottom to start designing a query.
Query for Parent Report
This query will show the list of Documents and how many invoices each document has.
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.
Let's preview the result by clicking Preview button.
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.
Click OK to confirm the query.
Move to Reports tab and set Table Grid for Default Type option.
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.
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.
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.
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.
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.
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.
Thank you for reading this article.