Answered
Crosstab Conversion
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
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:
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.