## Prerequisites

Ensure that you have the most recent version of ProteanCMS and your project downloaded.

## Creating the Reports Directory

1. Make sure the directory `Admin/xForms/Reports` exists in your project.

2. Copy an existing report xform to use as a template into the newly created directory. For example, you can use the `EmailOptOuts` file.

3. If you don't have a template to use, you can start with this example:

    ```xml
    <?xml version="1.0" encoding="utf-8"?>
    <Content type="xform" name="EditContent">
        <model>
            <instance>
                <Query name="[ReportName]" storedProcedure="[FileName]" logActivity="true" output="csv" filePrefix="" fileUID="log"/>
            </instance>
            <submission id="[SubmissionName]" action="/ewcommon/tools/export.ashx?ewcmd=Reports&amp;ewCmd2=[ReportName]" method="post" event="return form_check(this)"/>
            <bind nodeset="Query">
                <bind id="format" nodeset="@output" required="true()" type="string"/>
            </bind>
        </model>
        <group ref="OrderDownload" class="2col">
            <label>Email Opt Outs</label>
            <group>
                <select1 bind="format" appearance="minimal" class="required">
                    <label>Output Format</label>
                    <item>
                        <label>CSV</label>
                        <value>csv</value>
                    </item>
                    <item>
                        <label>Excel</label>
                        <value>xls</value>
                    </item>
                    <item>
                        <label>Raw XML</label>
                        <value>rawxml</value>
                    </item>
                    <value/>
                </select1>
            </group>
        </group>
        <group ref="SubmitDownload" class="inline">
            <submit submission="" ref="ewSubmitOptOuts" class="principle">
                <label>Download OptOuts</label>
            </submit>
        </group>
    </Content>
    ```

    > **Note:** If you use the template above, remember to replace placeholder names with appropriate values.

## Creating the Stored Procedure

1. Navigate to the SQL database for the current project.

2. Create a new stored procedure. This procedure will retrieve all the columns needed for the export.

    Example: 

    ```sql
    CREATE PROCEDURE dbo.spProductExport
        -- Add the parameters for the stored procedure here
    
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT c.nContentKey as ID,
               c.cContentName as ProductName,
               a.nStatus as Active 
        FROM tblContent c 
        INNER JOIN tblAudit a ON c.nAuditId = a.nAuditKey
        WHERE cContentSchemaName = 'Product'
    END
    GO
    ```

## Editing the Stored Procedure

1. Modify the stored procedure to extract specific elements from the XML that you want to display in the export. 

    Example:

    ```sql
    ALTER PROCEDURE dbo.spProductExport
        -- Add the parameters for the stored procedure here
    
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT c.nContentKey as ID,
               c.cContentName as ProductName,
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="StockCode"][1]', 'nvarchar(50)')  as StockCode,			
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="Manufacturer"][1]', 'nvarchar(50)')  as Manufacturer,
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="Prices"][1]/*[local-name()="Price" and @type="rrp"][1]', 'nvarchar(50)')  as RRP,
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="Prices"][1]/*[local-name()="Price" and @type="sale"][1]', 'nvarchar(50)')  as Sale,
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="ShortDescription"][1]', 'nvarchar(800)')  as Summary,
               cast(c.cContentXmlDetail as xml).value('/*[local-name()="Content"][1]/*[local-name()="Body"][1]', 'nvarchar(max)')  as Description,
               a.nStatus as Active 
        FROM tblContent c 
        INNER JOIN tblAudit a ON c.nAuditId = a.nAuditKey
        WHERE cContentSchemaName = 'Product'
    END
    GO
    ```

## Testing the Report

1. Run your project and navigate to the admin page.

2. Select **Reports** and then the report you just created.

3. Choose the desired file type and click **Download**.

Congratulations! You’ve successfully created your report!

---

- [Back to Full Stack Developer Guide](/Full-Stack-Developer-Guide)

---