Creating Bespoke Reports

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 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:

   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:

   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

---