---
title: "Create an xyArea chart using the SQL macro"
canonical: "https://support.appfire.com/space/SUPPORT/89134832/Create%20an%20xyArea%20chart%20using%20the%20SQL%20macro"
format: markdown
---
## Scenario
> Macro (div)
>
> > Macro (inline-media-image)
>
> Estimated Time: 9** min**
As an example, let's create an xyArea chart to display "Publishing Revenue vs. Expense" per quarter in 2017.
## Background
Our scenario uses a data source profile, *BookWarehouse,* to query a rollup database table, *PUBLICATION_RVE*. The table contains aggregate rollup revenue and expense data by book, by quarter.
For this scenario, we are focusing on charting 2017 revenue and expense data for a single publisher.
```sql
SQL > explain publication_rve;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| publisher_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | varchar(255) | NO | | NULL | |
| Q1 | decimal(10,2) | NO | | NULL | |
| Q2 | decimal(10,2) | NO | | NULL | |
| Q3 | decimal(10,2) | NO | | NULL | |
| Q4 | decimal(10,2) | NO | | NULL | |
| year | varchar(255) | NO | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
```
> ℹ️ For more information about data source profiles, click the appropriate version:
> ℹ️
> ℹ️ - [Data source profiles for 10.x version](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321547/Configure+Data+Source+Profiles+-+10.x)
> ℹ️ - [Data source profiles for 8.x version](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74322923/Data+Source+Profiles+-+8.x)
The query against the *PUBLICATION_RVE* table looks like:
```sql
SELECT type as "Statement Type"
,format(Q1,0) as "Q1"
,format(Q2,0) as "Q2"
,format(Q2,0) as "Q3"
,format(Q2,0) as "Q4"
FROM publication_rve where year='2017'
AND type='Revenue'
UNION
SELECT type as "Statement Type"
,format(Q1,0) as "Q1"
,format(Q2,0) as "Q2"
,format(Q2,0) as "Q3"
,format(Q2,0) as "Q4"
FROM publication_rve where year='2017'
AND type='Expense';
```
## Steps
The following steps demonstrate how to use Markup to insert Confluence's native [Chart macro](https://confluence.atlassian.com/doc/chart-macro-163415075.html) and our [SQL macro](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321601/SQL+-+10.x) on a Confluence page:
> Macro (table-plus)
>
> | | | |
> | --- | --- | --- |
> |  | ## Create a Confluence page
Create a Confluence page to create the macros needed to produce the chart.
1. Create a page named *Publication Revenue vs. Expense* in a space accessible to the desired audience.
> 2. Click *Save* to close the *Page Restrictions* screen.
> 3. Click *Save* to save to publish the new page. | |
> | --- |
> |  | ## Configure the Chart macro
Edit the *Publication Revenue vs. Expense* page and do the following:
1. Insert the *Chart* macro.
> 2. Click the *Chart* macro container and click *Edit* so you can adjust its parameters as shown on the right.
> - *Type* - type of a chart.
> - *Width* - width of the chart in pixels.
> - *Height* - height of the chart in pixels.
> - *Column* - column name or order of mapping from the query result to the chart.
> - *Chart Title* - title for the chart.
> - *Chart Subtitle*- subtitle for the chart.
> - *Show legend* - displays the name associated with the colored bars rendered.
> 3. Click *Save* to save your changes to the macro's parameters. | **Chart macro parameters:**
> Macro (nested-table) |
> | --- |
> |  | ## Configure the SQL Query macro
This step uses the [SQL macro](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74322843) to retrieve the data to be displayed in the chart. To configure the *SQL* macro, do the following:
1. Edit your *Publication Revenue vs. Expense* page.
> 2. Add a line below your *Chart* macro.
> 3. Using Markup, insert a new *SQL* macro on the page (more on [Markup here](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74321497/Using+the+SQL+Macro+with+Markup+-+10.x)).
> 4. Adjust its parameters as shown on the right. The following parameters indicate:
> - *Data source profile*- name of the data source profile that you set up.
> - *Use database column labels *- usage of the column names defined within the SELECT statement (e.g., Publisher, M Rev.) rather than the columns names defined within the database table(s) themselves.
> - *Show error if there are no rows* - displays an error if no rows are returned in the result set of the SQL query.
> - *Text to display when there are no rows* - text of the error message to be displayed when no rows are returned in the result set of the SQL query.
> 5. Click *Save* to save your changes to the *SQL* macro's parameters.
> 6. With the cursor positioned inside the *SQL* macro container, paste in this SQL statement:
> 7. Using your mouse, "drag" the *SQL* Macro container into your *Chart* Macro container (see right). | **SQL macro parameters:**
> Macro (nested-table)
**Moving the SQL macro inside the Chart macro:**
 |
> | --- |
> |  | ## Save and test the page
Now, go back to the *Publication Revenue vs. Expense* page and click *Save*. You should now see a page that appears as shown on the right. | **Final xyArea Chart:**
 |