You can create reporting views or tables by using the view
or table creation scripts. Reports extract reportable data from
reporting views or tables. Use the SQL Generator to create view
or table creation scripts.
About this task
If you are using eMessage reports,
make sure that you have run the SQL scripts against the Campaign database
to create the staging tables and batch procedures. For more information,
see For eMessage reports, creating stored procedures, staging tables, and indexes.
Procedure
Complete the following tasks to create view or table
creation scripts:
- Log in to HCL®
EMM as a user
that has the ReportsSystem role.
Proceed to step
2 if you did not use default JNDI names for JDBC data sources.
Proceed to step 3 if you used default JNDI names for JDBC data
sources.
- Complete the following steps if you did not use default
JNDI names for JDBC data sources:
- Select .
- Change the default values of the JNDI property to match
the JNDI names that you used for JDBC connections.
- Select .
- In the Product field, select the
appropriate HCL application.
- In the Schema field, select one
or more reporting schemas.
- Select the Database Type.
- In the Generate Type field, select
the appropriate option (views, materialized views, or tables).
Note:
- You cannot select materialized views when the database type is
set to Microsoft™ SQL Server.
- For eMessage only. For Oracle and IBM DB2®, eMessage requires materialized views. For SQL Server, eMessage requires
views.
For more information about creating views or materialized
views for eMessage, see Creating views or materialized views for Campaign or eMessage.
Important: If the JNDI data source names are incorrect or have not been
configured, the SQL Generator cannot validate the SQL scripts that
create tables.
- Ensure that Generate Drop Statement is set to No.
- If you want to examine the SQL script that is generated,
click Generate.
The
SQL Generator creates the script and displays it in the browser
window.
- Click Download.
The SQL Generator creates the script and prompts you to specify
the location for the file to be saved. If you selected a single
reporting schema from the Schema field,
the script name matches the name of schema, for example eMessage_Mailing_Performance.sql. If you selected more
than one reporting schema, the script name uses the product name,
for example Campaign.sql.
Note: When you run a script that creates materialized
views on a DB2 database, your
database might display the following error:
"SQL20059W
The materialized query table-name may not be used to optimize
the processing of queries."
However, the materialized
view is successfully created.
Note: When you run a script
that creates materialized views on a DB2 database, your database might
display the following error:
SQL20059W The materialized query
table-name may not be used to optimize the processing of queries.
- Specify the location where you want to save the script,
and click Save. If you change the name
of the file, make sure to use a name that clearly indicates the
schemas that you selected.
- Repeat steps 5 through 12 for each script that you want
to generate.
Note: The Interact reporting
schemas reference multiple data sources. Generate a separate
SQL script for each data source.
What to do next
When you generate the SQL scripts for Campaign reports, such as Campaign Extended Attributes, the
generator can create single lines that are more than 2499 characters long. Because Oracle has a
maximum line length of 2499 characters, you must fix the script by adding line breaks (CR/LF) in
strategic places within the long lines.