How can I create an rxjdbc report to export a table instead of a crosstab to be used in Pulse, Card or Squares?
- SAP Roambi Cloud
The default format for the RXJDBC connector is a crosstab, meaning that aggregations will be performed based on the fields defined in the <rowGroup> section. So if you want to generate an output that does not have any aggregations, you need to provide a a value that is unique for each record produced by the query, and place all the column fields of your table in the <values> section of the <columnGroup> of the report.
In the following report, we define a unique value called ID in the <rowGroup> section that is going to represent the record number in the output of the query. Since the value is unique for each record aggregations do not happen:
NOTE: The Syntax in this query is for SQL server, other databases might provide different methods to generate the record number.
<report name="AD-CommerceSalesData" type="CROSSTAB">
<column name="ID" label="Key" dataType="STRING" />
1000000 + ROW_NUMBER() OVER(ORDER BY Date,
p.ProductName ) as ID,Date,p.ProductName as Category,
'Products' as concept,sum(Revenue) as Revenue,
sum(UniqueVisitors) as UniqueVisitors,
sum(cartAdditions) as cartAdditions,
sum(cartRemovals) as cartRemovals,
count(Revenue) as Transactions
from sourceTable as adb
left join AdbProducts as p on adb.Products = p.ProdKey
where Products is not NULL
group by Date, p.ProductName
<column name="Date" dataType="DATE" displayFormat="MM/DD/YYYY" />
<column name="Category" dataType="STRING" />
<column name="concept" dataType="STRING" />
<column name="Revenue" dataType="NUMBER" label="Revenue" displayFormat="#,##0_,_."/>
<column name="Transactions" Label="Num of Transactions" dataType="NUMBER" displayFormat="#,##0_,_."/>
<column name="cartAdditions" dataType="NUMBER" displayFormat="#,##0_,_."/>
<column name="cartRemovals" dataType="NUMBER" displayFormat="#,##0_,_."/>
The output of this query will be a table with a Key column and then all the other fields defined in the <values> section.
If the unique value exist in the database and it's needed as part of the output of the query, use that field instead.
A good example of that is documented in rxjdbc users guide in the SuperList sample.
rxjdbc jdbcrx Roambi cloud , KBA , BI-ROM-CLD-PUB-BI , Roambi Cloud publishing clients – BI, Cognos & SSR , How To