【Report Rows】WEB Report : Designing per row

# Introduction #
This time, I will try to use **Report Rows** functionality in Sharperlight.
We would be able to see this at the end.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d6dde7d2-651d-03de-d317-1cab6d48e90f.png)
Their explanation about Report Rows is here.
`
Reporting Rows allows reports to be designed where each row has their own unique list of codes or ranges for a given Row Description. Rows in the Report may are given Line Numbers or Names that can be used in Formulas to calculate value in other parts of the Report. 
`
# Designing a Report Rows and a Query #
I use SAP Business One as the datasource and designed a classic Profit and Loss style report, but this time I only created the Profit section. Each row in the report is based on different Account Code criteria. There is an expanded section that automatically generates rows based on an Account Code range, as well as various totals, such as Gross Profit.


### Definition of Report Rows ###
To access Report Rows through the Sharperlight Application Menu, select "Report Rows." This will open the Report Rows dialog, where you can view a list of existing Report Rows definitions.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a0dfb5e2-9e02-6fb7-2498-9792bb9551e6.png)
Start with `New` button to create a new Report Rows.

Once the new Report Rows definition dialog open, set its **Code** and **Description** on `Header` tab.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5eda5f67-4e06-a26f-b6b7-9d17ac77abe3.png)

Move to `Rows` tab
I’ve prepared a sample definition here. Normally, this section would be empty, and you would need to create the definitions from scratch. As you can see, the report is designed using the Report Rows definition. Each row in the report corresponds to a row in the Report Rows definition.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6c46b32f-c37b-cf4b-a646-aca0cae9cce3.png)

Let's take a look at the definition, row by row.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f4bc9738-d5a5-11d6-f5ca-5124f55a9a25.png)

1. Header row
   Set the definition as "Revenue"
   Select `HEADER` as the Line Category
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4de27291-6628-8ed3-1107-b56867a80733.png)
   A line border format is set. 
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33207296-8220-6fd8-67cb-f4f753a883d2.png)<br><br>

2. Revenue account rows
   Set the appropriate descriptions
   Specify the account code in a range to the Filter Criteria A
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c32e45b1-ce59-71b4-d1ee-ba09d719ac6b.png)
   `Sales Revenue (Domestic)`: <<350003..400040, the account code from 350003 to 410040 
   `Sales Revenue (Foreign)`: <<400041..410030, the account code from 400041 to 410030
   `Other Revenue`: <<410031..450040, the account code from 410031 to 450040<br><br>

3. Sub Total row for Revenue
   Give a name for it as `REVENUE` so that can be refer from other place
   Select `SUBTOTAL` for Line Category
   Leave blank for Description 
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b715dc3b-00b9-fd36-b320-894bac461566.png)
   Apply formats such as line border, font colour and background colour.
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/990070dd-1eae-c808-111b-df5e0ebc6175.png)<br><br>

4. A blank row
   Place a blank row between Revenue section and Cost of Sales section
   Select `BLANK` for Line Category
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/662e8830-26f4-5b5d-5261-2280c8f3826b.png)<br><br>

5. Header row again
   Set the definition as `Cost of Sale`
   Select `HEADER` as the Line Category
   A line border format is set<br><br>

6. Cost of Sales account rows
   Set the appropriate descriptions
   Specify the account code in a range to the Filter Criteria A
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/545a92cc-37c0-12f3-38ed-59718f0e8c79.png)
   `Raw Materials`: <<500002..500010, the account code from 500002 to 500010 
   `Finished Goods`: <<500025..500030, the account code from 500025 to 500030
   `Packing Materials`: <<500045..500050, the account code from 500045 to 500050
   `Spare Parts`: <<500085..500090, the account code from 500085 to 500090
   `Component Materials`: <<500105..500110, the account code from 500105 to 500110
   `Manufacturing Materials`: <<500135..500140, the account code from 500135 to 500140
   `Stock Differences`: <<500160..505000, the account code from 500160 to 505000<br><br>
   
7. Sub Total row for Revenue
   Give a name for it as `COS` so that can be refer from other place
   Select `SUBTOTAL` for Line Category
   Leave blank for Description 
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6c27d780-3b9f-305e-2a50-2cdb15887588.png)
   Apply formats such as line border, font colour and background colour
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57bd9a6c-357b-a899-0cc7-f21d2574dfb2.png)

8. A blank row
   Place a blank row below SUBTOTAL row
   Select `BLANK` for Line Category
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/175f7a67-e4da-3bfa-7b7e-3eb42dc02897.png)

9. Gross Profit
   Add a calculated row so a formula needs to be set into Formula option
   The name of rows can be used in the formula: Revenue - Cost of Sales
    ```
    REVENUE - COS
    ```
   Set `TOTAL` for Line Category
   Give a name for it as `GROSSPROFIT`
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0b08d79b-217d-ce50-e3c2-cb3aaf983ae9.png)
   Apply formats such as bold font, line border, font colour and background colour
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/794431b1-7578-d2ee-dfcf-a5e2cb232d69.png)


10. Gross Profit%
   Add another calculated row which shows the percentage of the gross profit
   Set the formula: Gross Profit / Revenue, 
   Use the expression **DivSafe( )** to prevent an error when the value is divided by zero
    ```
    DivSafe( GROSSPROFIT,REVENUE )
    ```
    Set `TOTAL` for Line Category
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a07a8543-13a2-59bd-5ab7-ec57c8c34961.png)
   Set a special format to display a decimal place for this row only
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e3be991e-76b2-cd2f-b940-97f515e7d4da.png)
 

### Designing a query with the Report Rows definition ###
I am looking into the query which uses the Report Rows definition created above.
This is a correlation diagram between a query definition and the report that is created by that query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/20a0ac84-767b-47cd-1951-ad625168ef9c.png)

#### Filters ####
Let’s start with the filters.

**System** is selected as the product to use the Report Rows definition.
The Table must be **Report Rows**.
The code for the target Report Rows definition is chosen, and all related attributes are displayed in the `Selection` pane on the left side of the bottom pane of the Query Builder.

As you can see, there are three filters with a yellow background. These prompt filters are manually added to be used in the subqueries for retrieving data from SAP Business One.
They specify a target company, a target period, and a target Budget Scenario.
**Prompt Filters** mentioned here are not translated into a WHERE clause in the query statement but are used as variables.

The `Company` and `Period` filters are shown in the report as prompts. In this example, the company **OEC Computers UK** is used, and **2007/006** is set as the current period.

#### Outpus ####
`Line No.`
This is chosen to keep the order of rows right. Therefore, the sorting option is set.<br><br> 

`Description`
This shows the description of each row.<br><br>

`Report Logic`
This has all defined attributes applied to the row, and needs as an output item so that sharperlight engine can popultae the report with those attributes.
It is set as hidden item because of not required to be shown.<br><br>

`Line Category`
This shows the characteristic of the row defined in the Report Row definition.
This shows the characteristic of the row defined in the Report Row definition. Such as HEADER, TOTAL, SUBTOTAL etc.
It is set as hidden item because of not required to be shown.<br><br>

`Filter Criteria A`
This is configured in the Report Row definition, and it is used to get appropriate amount for the each column of the row.
The value such as **<<350003..400040** is used by the subqueries defined below to get their amounts.
e.g. In case of the row `Sales Revenue (Domestic)`, its criteria **<<350003..400040** is passed to the each subqueries such as Actula and Budget and get the amounts for them.
This is not required to be shown so set as hidden or obscured item.<br><br>

`Filter Criteria B`
This is the same as `Filter Criteria A`, and it is the 2nd criteria.
I didn't use this so it is never used but just set as an output item.<br><br> 

`Current Month Actual`
This is a subquery to get actual amount in the current month with the Criteria define by the Report Rows.<br><br>

`Current Month Budget`
This is a subquery to get budget amount with the Criteria define by the Report Rows.<br><br> 

`Current Month Variance`
This is an expression, and its formula is `=Actual - Budget` for Yeat To Date<br><br>

`A Blank Column`
An expression with "" in Text data type for making a gap between Current Month group and Year To Date group.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f5d79c8-d621-f379-269b-8b62f266cd52.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/379ea460-4d17-912c-66c0-7469bc80d76f.png)<br><br>

`Year To Date Actual`
This is a subquery to get Year To Date actual amount with the Criteria define by the Report Rows.<br><br>

`Year To Date Budget`
This is a subquery to get Year To Date budget amount with the Criteria define by the Report Rows.<br><br>

`Year To Date Variance`
This is an expression, and its formula is `=Actual - Budget` for Yeat To Date<br><br>

#### Preview ####
By clicking `Preview` button, we can check the result.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9bf41ac8-63b0-64f9-abc6-9d99156a67cb.png)

# Conclusion #
This time, I explored the basic usage of the `Sharperlight Report Rows` function. If you set up such definitions within your company, you can generate a report in the same format simply by entering the target period, date, and other parameters. This functionality seems very useful.
Please see additional post [here](https://qiita.com/Miki_Yokohata/items/c006b67f5f6885a8b217)