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. 6x4=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.
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.
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.
- SAP Managed Tags:
1 Comment
