【General】Try switching Excel based business data management over to a relational database

## Introduction ##
Currently, there are still many cases where data is managed using Microsoft Excel.
Many companies, large and small, are still stuck in the hustle and bustle of their day-to-day work and are stuck in the familiar Excel-based data entry and management.
It seems that the data accumulated on a daily basis contains a lot of important information that determines the direction of the company's business, but how to read such important information from the data divided into multiple Excel files , it seems that it is necessary to spend a certain amount of effort and time.
In general, by accumulating and sharing data in a database server located on the internal network, each department in the company can view the data they need at any time, helping them to make quick decisions. That would be nice.

This time, I would like to use [Sharperlight](https://sharperlight.com/) and take a look at the first step to realize it.
Let's say we have a sales ledger created in Excel like this. Let's use this sample to see how we can do.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/17f434b8-2a43-26b9-7f8a-4dc8f3b03f7f.png)
## Preparing the database ##
Prepare to save sales ledger data created in Excel to a Microsoft SQL Server database.
### An empty database ###
Using MS SQL Server Management Studio, create a new database for data storage.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/253c1714-1620-ea41-f5fe-4b319a696f30.png)
### Tables ###
Prepare new tables in the created database.
I read the sales ledger in Excel carefully.
It looks like it's divided into a header and its details.
Looking at the first record, it seems that the green column is the header information and the red column is the details.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3eb79b0c-98a0-b379-df86-9c6a36ca739a.png)
Now, create a `header table` and a `details table`.
Use the column names on the Excel sheet.
```
--Sales Header Table
USE [ABC_Company]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SalesHeader](
	[Row_ID] [int] IDENTITY(1,1) NOT NULL,
	[DocumentNumber] [int] NOT NULL,
	[NumberOfUnits] [int] NOT NULL,
	[SalesCategory] [nvarchar](100) NOT NULL,
	[DocumentDate] [date] NOT NULL,
	[SalesPerson] [int] NOT NULL,
	[StoreCode] [nvarchar](50) NOT NULL,
	[CustomerCode] [nvarchar](50) NOT NULL,
	[CustomerName] [nvarchar](200) NOT NULL,
	[CustomerTel] [nvarchar](50) NOT NULL,
	[CustomerEmail] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_dbo_SalesHeader_Row_ID] PRIMARY KEY CLUSTERED 
(
	[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
```
```
--Sale Detail Table
USE [ABC_Company]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SalesDetail](
	[Row_ID] [int] IDENTITY(1,1) NOT NULL,
	[DocumentNumber] [int] NOT NULL,
	[LineNo] [int] NOT NULL,
	[ItemCode] [nvarchar](50) NOT NULL,
	[Qty] [int] NOT NULL,
	[Unit] [nvarchar](20) NOT NULL,
	[Price] [int] NOT NULL,
	[Amount] [int] NOT NULL,
	[Group] [nvarchar](10) NOT NULL,
	[ItemNumber] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_dbo_SalesDetail_Row_ID] PRIMARY KEY CLUSTERED 
(
	[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/83c3b537-9dc6-5169-82b9-39f465a3ba91.png)
Now the sales data container is ready.

## Creating Sharperlight data model ##
Here we prepare for using Sharperlight.
We create a connector : **data model** for establishing a conversation between Sharperlight and the database.
Datamodel creation is done in Sharperlight Studio. 
Launch **Studio** from the Sharperlight application menu.![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/04cceb80-a928-5f2a-3754-54dccaa2516b.png)
### Database Connection ###
Connect Sharperlight Studio to the database and load the database schema required for conversation.
Click the `New` icon from the menu bar.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2d167a42-6d03-a4ee-d1df-aa706cb03a41.png)
Enter the required information for the datamodel (here called **Product**). Proceed with `OK`button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cbc78141-1883-6463-f820-42f0432a06f2.png)
Establish a database connection.
Enter the connection information for the database you created earlier. Check the connection with the `Test` button. If the connection is successful, press the `OK` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/68e90109-4324-f272-bd30-8044171b116c.png)
### Getting Schema ###
A confirmation message asking if you want to load the schema will be displayed, so click the `Yes` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3abe331c-d233-4278-56fb-03900c7f5b81.png)
A dialog for loading the schema will appear. Start loading with the `Get Tables` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/be66d97e-9816-f6e9-1cd9-96535d728f07.png)
When the schema information is displayed, load it with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f1b3b65-7f98-f2f3-bb69-72286ca95e26.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c35b2cdf-5d2b-b9bb-0184-029bd48a3cea.png)
Schema loaded.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a545bca0-5eb8-2130-b1f5-d58782e13391.png)
### Editting table definition ###
By default, the conversation between this datamodel and the target database is **read-only**.
This time, we want to write the data from the Excel file to the database, so set the table to **Writable**.
Set the `Allow Table Writeback` option to **True** for both tables. The table icon turns red.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ee95c3d8-d883-7b1f-0761-244b7cb6a695.png)
### Saving Data Models and establishing the database connection for Sharperlight Applications ###
Save the data model with the save icon in the menu bar.
A warning message will be displayed, so press `OK` to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/637fa5ee-a56e-75aa-658b-e77853936e29.png)
The default save location will be specified, so save with the `Save` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57bcfbf4-3a44-119c-6991-d0bc3cd35c21.png)
A message prompting you to configure the connection settings for the Sharperlight application will be displayed. Press the `Yes` button to move to the connection settings screen.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ff2a0e72-1c04-45be-fcb9-91f7f7984ded.png)
The Sharperlight Client Setup will open. This application manages connections between each installed datamodel and its target database.
Enter the connection information to the target database of the datamodel created this time, check the connection with the `Test` button, and proceed with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f7c3520c-ac55-0bea-e88a-07aca6813358.png)
The creation of the Sharperlight datamodel is completed. Close Sharperlight Studio.

## Writeback data ##
Then, write data using the created datamodel and its connection.
Start MS Excel and login to the Sharperlight add-in.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ec423554-9ab0-1bc5-57a4-7462f3ec2bf7.png)
and open the sales ledger.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/680633c5-891e-e464-626c-80a07ec0f78a.png)
If the sales ledger is not the Excel Table, set it as the Excel Table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/792f64a0-ec80-614b-8815-b6673537c402.png)
### Definition of Sharperlight Writeback ###
To write data, use Sharperlight's Writeback function.
Click the 'Writeback' icon on the Sharperlight ribbon to open the 'Writeback' dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c23915a0-f24f-790e-9980-b028d091e1e9.png)
#### Product selection ####
Specify the product (datamodel). Select from the list with the search button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2050053e-5915-fc75-6c7c-c0d7ecfd337a.png)
#### Table selection ####
If you click the search button, a list of writable tables within the selected product (datamodel) will be displayed, so select the target table to be written from the list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/78cc053b-bc69-2a2c-2a84-e9c7cd51082c.png)
A list of fields to be written is displayed on the Field tab.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/98576440-79fe-7b67-5c8d-5a89e15cbcb0.png)
#### Enter writeback name ####
This will be the `Name` when saving this writeback definition to the sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/aca1810d-7b31-5b55-1a6f-cbd63a2dc161.png)
#### Field mapping ####
Connect the columns on the Excel worksheet with the columns on the database table via the datamodel.
Select any field on the Fields tab. Then double-click the table header on the Excel worksheet. The mapping function will start and you will see a message calling your attention. Confirm with the `Yes` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/31f60dd2-6a28-3a14-78da-ed6b91a999e0.png)
If the column name on the Excel worksheet and the column description defined in the datamodel are the same, this function will create a mapping.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/17f8b781-7195-b0a0-ee1e-f019413f9b49.png)
The **Number Of Units** field mapping is not complete. This is due to a column name mismatch. Let's do the mapping manually.
After selecting **Number Of Units** on the Fields tab, double-click the header of the Number Of units column on the Excel sheet. The same message as before will be displayed, so press the `No` button this time.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/72d149d0-1047-5224-1f34-407ba83a103a.png)
The mapping is set like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6702fcf8-3e55-78c5-5a57-97fe53235129.png)
The mapping is completed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19b4eadf-f8bf-78ac-501e-9809681b0eeb.png)
#### Specifying Driver Column ####
Specifies which column to refer to when reading data from an Excel sheet.
Let the `document number` column be the driver.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b6a328ad-2ed2-28a0-b15d-f60d48ed340f.png)
#### Validating the Writeback definition ####
Now that the definition is complete, let's verify the operation. Click the `Validate` button. It is normal to see such a message.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/970c8e69-0771-e2f6-4ff6-0c93169228c8.png)
#### Saving Writeback definition ####
When you close the dialog, a message prompting you to save will be displayed, so save with the `Yes` button. It will be saved in this worksheet with the `Writeback Name` entered above.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ba3b84a8-dcf7-63ce-0ae5-779107ae1483.png)
### Performing Sharperlight Writeback ###
Let's run it now. Reopen the Writeback dialog with the Writeback icon on the Sharperlight ribbon, and view the list of saved writeback definitions with the Search button. Select the definition `Sales Header` you want to open.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/97415e0d-4bea-ef45-97bf-e48bd6751b53.png)
Once the definitions are loaded, click the `Execute` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/686303b4-ea1f-b1fe-03a1-271eda2d7f7f.png)
If you see a log like this, the execution was successful.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9f434738-3614-7ced-a570-691bb3139841.png)
Let's check the database table.
Oops! The writeback was successful, but the header information is duplicated.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/311b86df-62ba-4004-ceae-b43d70e54b9c.png)
That needs fixing, doesn't it?
### Unique key setting ###
Set the unique key in the datamodel.
Open the **ABC Company Business Management** datamodel in Sharperlight Studio.
Open the **SalesHeader** table definition in the Tables tab.
Set the `Is Unique` option to **True** for the `DocumentNumber` field.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8798a63c-cfc3-881c-d82d-94f55a55058c.png)
Save the datamodel. Log off from the Excel add-in and log on again for the changes to take effect.
Empty the database table with the Truncate table command and write back again.
Writeback completed successfully.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5c3ee576-79ad-92b1-a93a-f10acaedbf8a.png)
On the database table side, the duplicates seem to have been resolved.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8fae9c89-fa2f-8601-f412-b855e24b5682.png)
Let's check exactly.
```
SELECT DocumentNumber, COUNT(DocumentNumber)
FROM  [ABC_Company].[dbo].[SalesHeader]
GROUP BY DocumentNumber
HAVING COUNT(DocumentNumber) > 1
```
No duplicates.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/578719a5-53db-0a6e-b916-8e946ec4196b.png)

Write back data to the **SalesDetail** table in a similar manner.
The **SalesDetail** table seems to have the same duplication issue, so update the table definition in the datamodel and then perform the writeback.
In the SalesDetail table, the `Documentnumber`, `LineNo` and `ItemCode` form a unique key.
Save the datamodel with these settings.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dd68d30a-f489-8c2c-1338-bde50d959997.png)
After that, create and execute the writeback definition in the same way as for the SalesHeader table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4c7286da-5e3b-4057-3249-35e2d8654ba8.png)
### Convert the Excel sales ledger into a dedicated write-back sheet ###
Now that the basic write-back definition has been created and the data on the Excel sales ledger has been written back to the database, we will improve the Excel sales ledger into a dedicated write-back sheet based on that data.
By repeating the steps below, you will be able to manage the input of sales data.
    1. Synchronize the data in the database and the Excel sales ledger using queries.
    2. Enter the new data into the Excel sales ledger.
    3. Update the database using the write-back definition of the Excel sales ledger.

`Take a back up of the Excel sales ledger before updating it. `
#### Query definition ####
Add a table JOIN to the **SalesDetail** table definition in the datamodel before creating the query. When creating a query, it is very convenient.
This is a JOIN that is created on Sharperlight's datamodel, not on the actual database.
Select the `DocumentNumber` field and click **New Join** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d2e72a8f-2db8-4213-e41a-20e8c272e41f.png)
A JOIN creation support dialog will be displayed, so create a JOIN to the **SalesHeader** table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/99178da8-856e-84a7-9020-fc371c511466.png)
A table join has been created. Save the datamodel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/711fdef3-c18f-455f-3c2d-04a9797ed794.png)
Back in the Excel Sales Ledger, log off Sharperlight and log in for the changes to the datamodel to take effect.
Add a row to the top. After selecting cell **A1**, click the `Table` icon from the Sharperlight ribbon to open the **Query Builder**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3da7ca11-eafc-0513-f102-541bf12e6714.png)
In Query Builder, make a query as shown below.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cdee427c-b19c-2390-fcdc-eb819024c11b.png)
It is a good idea to match the field descriptions of the output items with the table header names of the Excel sales ledger.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ff177813-3544-e78f-f5e4-a2096dfd801e.png)
When click `OK` button on the query builder, a dialog prompting you to enter a query name will be displayed. Enter the query name and press OK.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c216672e-ac6f-8e6b-60e2-2f2bf45b2e87.png)
A Sharperlight table formula is set in cell **A1** and the query is executed immediately, but the results are not displayed as they are blocked by the existing Excel table.
Delete an existing table on the sheet. As soon as the existing table is dropped, the Sharperlight table formula will be executed again and a new table will be created.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/619dff0c-c86a-a127-0c27-08de80703532.png)
#### Updating writeback definition ####
Now that the query has recreated the table, let's review the writeback definition we created earlier.
Open the saved `Sales Header writeback definition` and clear all mapping information.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c483a77f-1226-155e-de8f-76de49aa16f9.png)
Select the `DocumentNumber` of the writeback definition and double-click the header of the Excel table. A new mapping is created.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/676b28cf-c83c-f652-656f-00ce5bf4a022.png)
Save with the `Close` button.
In the same way the `Sales Detail writeback definition` also updates the mapping.
### Executing the query and the writeback with buttons ###
Query and two writeback operations can be done with buttons.
Add 3 more rows to the top of the Excel sheet. Place the buttons here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6730a0af-22cd-6868-8eac-d61d5c92595f.png)
#### Button for executing the query ####
From the Sharperlight ribbon, select **Button**->**Recalculate**->**mdRecalcActiveWorksheet**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/414617d7-20c3-7a83-58d7-a8dc30b1c7fe.png)
A button to run the query is added like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cd38f6b0-f46b-50b6-183b-9b5c011b4f42.png)
Link the query formula defined in cell A4 to this button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fa5f5f06-ed63-571b-e915-a82f2940300f.png)
Select cell A4 and select **Sharperlight XL** -> **Edit Button/Table Bindings** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ed110820-51f2-3768-1c07-a6f38cdfb943.png)
When Button Table Binding dialog open, set the target button's name, the target quert's name, the cell reference where the Sharperlight formula is and the corresponding table's name.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fcc8ab15-1e6b-181e-5868-c36f8867da78.png)
Press `OK` button to make the link.
Delete the value in the cell **A6** and **A7**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/894f8c9f-9ae8-9bda-0122-c92840ac922d.png)
Then click the recalculate button to see it works fine. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1346a287-45cf-452f-4523-5da3b0bf4a0b.png)
Now we can remove the formula from the cell **A4**.
#### Button for executing the writebacks ####
Then add buttons to execute the writeback definitions.
From the Sharperlight ribbon, select **Buttons**->**Writeback**->**mdWriteBack**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9c3c96fa-7a92-7600-1576-dfccbe4ea00a.png)
A list of write-back definitions will be displayed. Select a definition to be associated with this button to create a write-back button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/273d746c-405d-e011-f412-c39dfaf94815.png)
After adding two writeback buttons, edit the caption.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d19fadd1-ee24-2673-881c-a91334da8fb8.png)
This saves the trouble of calling the writeback definition each time and executing it with the execute button on the writeback definition dialog.
### Showing writeback log on the sheet ###
Finally, since the log information dialog that opens whenever the writeback is executed, change the writeback definition so that it is displayed in the specified location on the sheet.
Open each writeback definition and go to the `Processing Options` tab.
Specify a cell reference in the `Logging Cell` option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/717b554a-8cc9-9694-eda4-b9b26d9ade33.png)
Adjust the cell attributes on the specified sheet so that the log information is displayed well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4248b37a-f6d7-701f-2fc6-d9ff7b133a55.png)
Now it is possible to write data back to the database using this sheet.
After synchronizing the sales ledger excel sheet with the database using the query button, add new sales records to it.
Then use the `WB: Sales Header` button to write the new record's header information back to the database first. Finally, use the `WB: Sales Detail` button to write its details of the new record back to the database.
For the time being, it is now possible to writeback the daily sales data to the database by following these steps.

## Creating reports ##
With the above, the data in the Excel sales ledger has been migrated to the database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/250efe95-76a0-7981-dd19-234c56a594ef.png)
After that, I will try to visualize the data using Sharperlight.
### Reports ###
Of course, we can use the Sharperlight Excel add-in to create reports in Excel, but we can also create web reports with the Sharperlight Publisher and share them in our organization network using the Sharperlight service. It is also possible to publish the reports to the outside of the internal network after performing various security settings.
Start Sharperlight **Publisher**. Start creating a new report with the `New` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7a467a91-1157-6bd8-20e0-0666bb2908a9.png)
When the web report dialog opens, fill in the **Code**, **Group**, **Title, etc.** and start building the query with the `Edit Query` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ce39b2a-9d8c-248a-3d2e-ebfc6aa051aa.png)
Specify **Summary Report** for `Mode`, **ABC Company Business Management** for `Product`, and **Sales Detail** for `Table`.
A list of selectable fields is displayed in the `selection` area, so use them to set `filters` and `output items`.
I set a filter on the `Sales Category` and `Document Date` fields. The value **DATE -2m** in the `Document Date` filter is a Sharperlight-specific symbol that means 2 months ago (minus 2 months) from the current date. Also, the Year/Month option is set to the filter.
So if the query is executed on 15 May 2023, the generated WHERE clause will look like this:
```
CAST(YEAR(J002.[DocumentDate]) AS varchar(20)) + '/' + RIGHT('0' + CAST(MONTH(J002.[DocumentDate]]) AS varchar(20)),2) BETWEEN N'2023/03' AND N'2023/03'

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/62dd2e3d-4efb-e035-453f-6d8525e91909.png)
Now, save the query with the `OK` button, and save the web report with the `OK` button as well.
Start the Sharperlight service and open the report we just created in the browser. The report should be displayed with the URL below.
```
http://localhost/DemoRest/Report/?query=QiitaSample.ExcelToDatabaseE
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a3ad0a5b-2fdd-a2c9-7707-4e06486312bb.png)
Let's edit it some more.
Edit the title of the report to appear on the left. It can be changed with the `Alignment` option on the `General` tab.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57ab1b2b-04e3-11a3-fce5-6ca9b4ae58dd.png)
Displays prompts (filters) aligned vertically (over two lines). On the `Options` tab, set the `Items per Row` attribute of the `Prompt` options to **1**. Display only one filter per line, this time there are 2 filters, so it will be displayed on 2 lines.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5805690a-ca98-1984-28f0-dae25364c6fa.png)
Now save the changes and refresh the report in the browser with F5! How about that!?
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0007af3f-60e5-1c79-846e-21db4e13d8da.png)
In addition, try grouping by document number.
Before do it, let's rename the description of the some output columns.
- \SalHea\Document Date
- \SalHea\Customer Name
- \SalHea\Store

Open the query definition.
Select `Change Description` from the right click menu on each column and rename them.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/abf2ce6b-7b38-d7a9-66cb-e74f637070f2.png)
For grouping, open `Output Options` for **Document Number** output column.
On the `Sorting and Grouping` tab of the Output Options dialog, specify **Document Number** and set the `Grouping Options` **Basic**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6a24c0df-f39c-907e-1e40-a5d6de832db9.png)
Now save the changes and refresh the report in the browser with F5! How about it!?
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5c582640-6b51-3f63-3696-16b71220a3d4.png)
### Chart ###
Let's display the transition of sales slips and return slips in a chart.
Create a new report. Fill in **code**, **group** and **title etc.**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d3dde6b7-139e-c088-0548-002dfeb2b368.png)
Start creating a query with the `Edit Query` button.
Just like the report we created earlier, we set the `Mode`, `Product`, `Table`, some filters, and also specify the output items.
This time, we prepared two `Sales Category` filters for **Sales** and **Returns**. This will be used for the output items described below.
First set the `Document Date` for the output item. In addition, set two `Document Number` columns. One counts sales slips and the other counts return slips. Rewrite their descriptions as **Sales Document** and **Return Document**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f402bfc7-6bf8-9a69-718a-8636757365d7.png)
For the option to count sales documents, select **Count** for the `Aggregations` attribute, and select the Sales Category: **SALES** filter for the `Apply Filters to this Output` attribute.
For the option to count returns documents, select **Count** for the `Aggregations` attribute, and select the Sales Category: **RETURNS** filter for the `Apply Filters to this Output` attribute.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/46482fa9-0227-5416-7281-78945f094dd0.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8182b903-8309-14ff-8cd1-c86e898b2c0c.png)
This setting will generate a SQL statement like this:
```
,COUNT( CASE WHEN (J002.[SalesCategory]=N'Sales'
) THEN J001.[DocumentNumber] ELSE NULL END )
,COUNT( CASE WHEN (J002.[SalesCategory]=N'Returns'
) THEN J001.[DocumentNumber] ELSE NULL END )
```
Check the query with the preview function.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/61f4c9b2-eb56-ea10-b243-2665173d6f60.png)
Save the query and return to the report dialog.
On the `Options` tab set the `Default Type` to **Chart** and disable Prompts.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/de0bfaba-96ac-63be-33e7-52b700ffe330.png)
In the `Chart` tab, set the `Chart Type` to **Area**, `Size: Auto Width` to **1300**, `Rotation Title - Degrees` to **Minus 45 degrees**, and `Custom Title` should be **# Documents**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4754f3b7-c2e6-915b-3947-a615f141a143.png)
Now save the report and view it in the browser.
```
http://localhost/DemoRest/Report/?query=QiitaSample.ExcelToDatabaseE2
```
A chart like this was displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0ff8102d-0942-1c1a-1a17-b345b725c17e.png)
### Dashboard Page ###
I will use the Sharperlight Dashboard Pages to tile the two reports created earlier.
Launch the `Dashboard Pages` from the application menu.
Start creating a new dashboard page with the `New` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/71cc01b7-39e6-cbfa-30ff-3dd038fd5e09.png)
The dashboard page definition dialog is displayed.
#### Dashboard Page Details ####
In the `General` tab, fill in the information for this dashboard page. **Code**, **Group** and **Title** should be filled in at a minimum.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f8e3002-dadb-f642-9be4-8ae4c9e3225b.png)
#### Tile Definition ####
The `Tiles` tab is where you actually define the tiles.
There are 4 tiles by default, but 2 is enough this time, so delete 2 with the `Delete` icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e83a097c-22ca-42ed-b023-dff2982fcc81.png)
For the first tile, specify **The List of Sales Documents** for the `Title`, and specify the report created first for the `Content URL`. It can be searched using the search button on the right.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9c54c7c6-1793-b091-747d-7cf87448c42b.png)
Turn on the option `Hide Title` attribute.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ca5d35f5-4ecb-a499-6fed-5361d84ddf57.png)
Finally, adjust the size and position in the `Edit Layout` grid.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/09f84639-236d-b28f-a1ca-ed3cff1c1a16.png)
Do the same for the chart tile. Specify **The Transition of Sales slips and Return slips** for `Title`, and specify the previously created chart report for `Content URL`. Specify the option `&width=1000` to specify the width of the chart at the end of the specified content URL as follows
```
/Report?query=QiitaSample.ExcelToDatabaseE2&rtype=chart&ctype=area&width=1000
```
Also turn on the optional `Hide Title` attribute.
Finally, adjust the size and position in the `edit layout` grid.
Then save it with `OK` button. 
#### Validating and Editting Dashboard Pages ####
Select `View Report` from the right click menu on the Dashboard Pages dialog to display it in the browser.
At first, it seems that incomplete tiles that are partially not displayed well like this will be displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/894e287d-8ad2-2d58-25b7-444959a550a4.png)
Pull the corners of the tiles to adjust their size, or drag the tiles themselves to align them so that the content looks right.
After making adjustments, click the `Gear` icon that appears in the upper right corner of any tile to open the options page.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/83503131-6149-67c9-3820-5bc3c8f1369c.png)
When the option page opens, save the adjusted size and position with the `Save` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cf24a777-a801-a427-8163-ebf0e5c79ab9.png)
Well, what do you think?
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7b60f8c-726e-0cd5-4846-e4c3d278efdd.png)

## Postscript ##
It's a long article, but I hope that you can find something useful in this article and help you.