Extract the Lookup Table data from the PricingTable and present in Crystal for viewing and printing

The rows and columns of lookup tables are stored in xml format in the Pricing Table. We use an SQL query for the extraction which brings the data into Crystal with each column listed separately for each row and row value. We then insert the Crystal Cross-Tab report to pivot the table on the column representing the row labels. To include the Table's Notes, we an SQL sub-report into the report header.

The SQL code is extracted from Brandon's WIKI posting, lookup_table_sql He notes in that posting that if not constructed properly, SQL code can damage the database. For the Crystal portion the code is Read Only.

1. Create and test the SQL code. The example shown here has the Table Name hard-coded but this can be converted easily to code with a parameter to pass the table name to Sequel to have this able to pull any Lookup Table. The code below is extracted from Brandon's posting, lookup_table_sqlwith the table name changed to “PaintProcesses”. [For a more detailed explanation of extracting XML Values see the posting how-to_write_sql_to_extract_an_xml_value_from_a_database_field .]The only thing it needs for your table is the table name and it should work well. Change the table name to the name of your table and embed the code in the Cyrstal command box.

DECLARE @PricingTable INT = (SELECT id FROM PricingTable WHERE PricingTableName = 'PaintProcesses')
 
IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb.dbo.#columns', 'U') IS NOT NULL DROP TABLE #columns;
DECLARE @XMLData XML  = (SELECT TableElementStr FROM PricingTable WHERE ID = @PricingTable )
DECLARE @RowVariable VARCHAR(50) = (SELECT v.VariableName FROM PricingTable pt JOIN variable v ON v.id = pt.RowParam1ID WHERE pt.id = @PricingTable)
DECLARE @ColVariable VARCHAR(50) = (SELECT v.VariableName FROM PricingTable pt JOIN variable v ON v.id = pt.ColParamID WHERE pt.id = @PricingTable)
 
SELECT * INTO #Temp FROM (
SELECT
    Row1ParamValue = Node.Data.value('(Row1ParamValue)[1]', 'varchar(200)')
    , ColParamValue = ISNULL(Node.Data.value('(ColParamValue)[1]', 'VARCHAR(200)'),'Value')
    , ElementValue = ISNULL(CAST(Node.Data.value('(ElementValue)[1]', 'VARCHAR(100)') AS DECIMAL(10,4)),0)
FROM @XMLData.nodes('/TableElements/TableElement') Node(DATA)
) T
 
SELECT DISTINCT ColParamValue
INTO #Columns
FROM #Temp
ORDER BY ColParamValue DESC;
SELECT*
FROM #temp
ORDER BY ColParamValue

1. Pull the column and row fields into Crystal and then suppress the Detail.
2. In the Report Footer section, insert a Pivot Table.
3. Using the Pivot Table Expert, Set the Pivot Table Column and Rows to the table selected.
4. Format the Pivot Table to your liking.
5. To enable the selection of specific rows, create a Parameter for selecting the desired row. In this implementation you can not use the Parameter in the Select Expert; so then create a Formula to return a True if the row is selected and insert that in the Select Expert.
6. To include the Table notes in the report, create a sub-report querying the PricingTable data as shown in the example below.

DECLARE @TableName VARCHAR(100)
SET @TableName = '{?TableName}';
--Set @TableName = 'PaintProcesses';
SELECT
PT.ID,
PT.PricingTableName,
PT.Notes
FROM PricingTable PT WITH(nolock)
WHERE PricingTableName = @TableName

Contributor: Steve Gillispie

Date: 12/23/2018

Version: Control 6.10.1811.1501