2326740 - How can I create an rxjdbc report to export a table instead of a crosstab to be used in Pulse, Card or Squares

SAP Knowledge Base Article - Public

2326740 - How can I create an rxjdbc report to export a table instead of a crosstab to be used in Pulse, Card or Squares

Symptom

How can I create an rxjdbc report to export a table instead of a crosstab to be used in Pulse, Card or Squares?

Environment

  • SAP Roambi Cloud

Resolution

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.

 

For example:

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">
       <rowGroup>
                <column name="ID" label="Key" dataType="STRING" />
       </rowGroup>
       <columnGroups>
             <columnGroup>
                 <query dataSource="DS_1">
                       <![CDATA[

                               SELECT
                                     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

                        ]]>
                  </query>
                 <values>
                        <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_,_."/>
               </values>
           </columnGroup>
       </columnGroups>
</report>

The output of this query will be a table with a Key column and then all the other fields defined in the <values> section.

2016-06-06_16-37-58.png


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.

Keywords

rxjdbc jdbcrx Roambi cloud , KBA , BI-ROM-CLD-PUB-BI , Roambi Cloud publishing clients – BI, Cognos & SSR , How To

Product

SAP Roambi Cloud all versions