Answered

Crosstab Conversion

OneCloud Support

started a topic 9 months ago

OneCloud offers the Convert to Cross Tab Tabular Transformation Command. This Command can convert a tabular delimited output with column headers and row headers that are defined as tuples. The command converts these tuples into a proper cross tab report that can be used natively in products like Workiva Wdesk. Typical source data would be executing a MDX report in a product such as Oracle Essbase.

Here is an example of the source input and the converted output.

Sample Configuration

Native Tabular Output with tuples in the column and row headers

(Measures, Product, Market)  (Actual, Qtr1)  (Actual, Qtr2)  (Actual, Qtr3)  (Actual, Qtr4)  (Budget, Qtr1)  (Budget, Qtr2)  (Budget, Qtr3)  (Budget, Qtr4) 
(Sales, 100-10, New York)  1995.0  2358.0  2612.0  1972.0  2249.0  2220.0  2470.0  1720.0 
(Sales, 100-10, Massachusetts)  1456.0  1719.0  1905.0  1438.0  1360.0  1620.0  1800.0  1250.0 
(Sales, 100-10, Florida)  620.0  735.0  821.0  623.0  570.0  690.0  770.0  530.0 
(Sales, 100-10, Connecticut)  944.0  799.0  708.0  927.0  880.0  750.0  660.0  810.0 
(Sales, 100-10, New Hampshire)  353.0  413.0  459.0  345.0  320.0  370.0  430.0  280.0 
(Sales, 100-10, California)  1998.0  2358.0  2612.0  1972.0  2480.0  2940.0  3250.0  2530.0 
(Sales, 100-10, Oregon)  464.0  347.0  345.0  370.0  570.0  420.0  420.0  470.0

Converted Cross Tab Output (tab delimited)

      Actual  Actual  Actual  Actual  Budget  Budget  Budget  Budget 
      Qtr1  Qtr2  Qtr3  Qtr4  Qtr1  Qtr2  Qtr3  Qtr4 
Sales  100-10  New York  1995.0  2358.0  2612.0  1972.0  2249.0  2220.0  2470.0  1720.0 
Sales  100-10  Massachusetts  1456.0  1719.0  1905.0  1438.0  1360.0  1620.0  1800.0  1250.0 
Sales  100-10  Florida  620.0  735.0  821.0  623.0  570.0  690.0  770.0  530.0 
Sales  100-10  Connecticut  944.0  799.0  708.0  927.0  880.0  750.0  660.0  810.0 
Sales  100-10  New Hampshire  353.0  413.0  459.0  345.0  320.0  370.0  430.0  280.0 
Sales  100-10  California  1998.0  2358.0  2612.0  1972.0  2480.0  2940.0  3250.0  2530.0 
Sales  100-10  Oregon  464.0  347.0  345.0  370.0  570.0  420.0  420.0  470.0

Sales 100-10 New York ACTUAL QTR1 1995.0 Sales 100-10 New York ACTUAL QTR2 2358.0 Sales 100-10 New York ACTUAL QTR3 2612.0 Sales 100-10 New York ACTUAL QTR4 1972.0 Sales 100-10 New York BUDGET QTR1 2249.0 Sales 100-10 New York BUDGET QTR2 2220.0 Sales 100-10 New York BUDGET QTR3 2 470.0 Sales 100-10 New York BUDGET QTR4 1720.0 Sales 100-10 Massachusetts Actual QTR1 1456.0 Sales 100-10 Massachusetts Actual QTR2 1719.0 Sales 100-10 Massachusetts Actual QTR3 1905.0 Sales 100-10 Massachusetts Actual QTR4 1438.0 Sales 100-10 Massachusetts BUDGET QTR1 1360.0 Sales 100-10 Massachusetts BUDGET QTR2 1620.0 Sales 100-10 Massachusetts BUDGET QTR3 1800.0 Sales 100-10 Massachusetts BUDGET QTR4 1250.0 Sales 100-10 Florida ACTUAL QTR1 620.0 Sales 100-10 Florida ACTUAL QTR2 735.0 Sales 100-10 Florida ACTUAL QTR3 821.0 Sales 100-10 Florida ACTUAL QTR4 623.0 Sales 100-10 Florida BUDGET QTR1 570.0 Sales 100-10 Florida BUDGET QTR2 690.0 Sales 100-10 Florida BUDGET QTR3 770.0 Sales 100-10 Florida BUDGET QTR4 530.0

Mike Staib

said 8 months ago

Is there a way to remove the columns and create a single data value with the column headers becoming 2 or more columns, essentially one for each of the dimensions?

The new data would look like this given the example above:

O

OneCloud Support
said 8 months ago

Hi Mike - thanks for your question. Assuming your data source is Essbase, the best way to deal with this would be to write your MDX query or report script in a way to create the format that you are needing. The example in the original article is for a cross-tab extract format from an Essbase MDX script that has tuples in the header that need to converted to a proper-cross tab report.

Crosstab Conversion

Topics in this forum

Didn't find what you were looking for?