Skip to Content

If you ever wanted to show N records per group (e.g. the first group of the report will show 10 records, then the second group will show the next 10 records) then this blog post will show you how.

You can use this technique to show N records per page, by adding one more step of using the Group Option of New Page After 1 Visible Group.

You can limit the number of columns in a cross-tab to N, or limit the number of rows in your cross-tab to N. The next cross-tab will show the next N columns or rows. You can also limit the number of row column combinations to N (e.g. 6×4=24) using the technique at the bottom of this blog post.

You can also limit the number of values shown in each chart to N where the next chart will show the next N values.

Untitled.jpg

Steps:

1) In a new report, create a new formula field with syntax like the following:

whilereadingrecords;

numbervar r; numbervar g;

// r represents the record number in the current group

// r will be between 1 and N…in this case N=6

// g represents the current group number

// g will be displayed on the report as the Group Name

if remainder(r, 6) = 0

// you can change the above number 6 to any integer or a parameter if you wish

then (r := r + 1; g := g + 1;)

else (r := r + 1; g;)

2) Insert a Group on this new formula.

3) Add some fields to your report…note that there will be N (in the example, 6) records per group.

To see this technique working please download the GroupingOnCertainNumberOfRecords attachment and extract the contents. Change the .txt extension of the file to .rpt.  Note that in the cross-tab objects on the report that the Grouper formula (syntax from above) is used in either the cross-tab Columns dialogue or the cross-tab Rows dialogue.

Formatting a Cross-tab to Have N Rows & M Columns

This technique is a bit more advanced than the above method and you must ensure that you have the proper data coming into your report and it must be sorted properly.

I have also attached another report CrossTabColumnsLimitedWithCommandAndWRRGoup where I use the grouping formula to limit the number of records per group to 30. This is because I want to have a cross-tab that has 3 columns (3 months) with one row for each product (10 rows) for a total of 30 unique rows per group.

This report is based on a Command object that ensures that for each month of the data set, that there are 10 product records as well. The syntax of this Command object is below. Also note that the Record Sort Expert uses the calendar month number first and then the product name. This is important to ensure that the proper records are in each group.

Untitled.jpg

The syntax below is for SAP HANA. Your Command syntax may vary. The top part of the Command syntax ensures that every month and product name combination are brought into the report. Then a sub-query is used to ensure that a value for every month and product name combination is in the record set. Without a sub-query and using regular table joins we would have product and month combinations drop off of the record set when there were no sales for that combination.

SELECT DISTINCT

  COMPANYNAME,

  PRODUCTNAME,

  CALENDARMONTHNUMBER,

  CALENDARMONTHDESCRIPTION,

  (SELECT SUM(FCO1.UNITSALES)

   FROM ((STS.DIMCUSTOMER DC1

   INNER JOIN STS.FCTCUSTOMERORDER FCO1 ON DC1.CUSTOMERID=FCO1.CUSTOMERID)

   INNER JOIN STS.DIMPRODUCT DP1 ON FCO1.PRODUCTID=DP1.PRODUCTID)

   INNER JOIN STS.DIMPERIOD DPE1 ON FCO1.ORDERPERIODID=DPE1.PERIODID

   WHERE DC.COMPANYNAME = DC1.COMPANYNAME

   AND  DP.PRODUCTNAME = DP1.PRODUCTNAME

   AND DPE.CALENDARMONTHNUMBER = DPE1.CALENDARMONTHNUMBER

   AND DPE1.YEARNUMBER = 2005

   AND DC1.COMPANYNAME = ‘ABC Gmbh’

  ) AS UNITSALES

FROM

  STS.DIMCUSTOMER DC

  INNER JOIN STS.DIMPRODUCT DP

  ON DC.REGIONNAME<>DP.PRODUCTNAME

  INNER JOIN STS.DIMPERIOD DPE

  ON DC.CUSTOMERID<>DPE.YEARNUMBE

WHERE

  DPE.YEARNUMBER = 2005

  AND DC.COMPANYNAME = ‘ABC Gmbh’

AND PRODUCTNAME <= ‘C 155’

ORDER BY    CALENDARMONTHNUMBER, PRODUCTNAME



If you’re interested in learning more about SAP HANA and HANA SQL, please visit the SAP HANA Academy site.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply