Search
Search

# Grid Concept of the Cross Tab

The following scenario presents a typical situation, where only the knowledge of *Grid Concept of the Cross Tab* helps us to formulate the solution.     The following +Crystal Report extract+ is a Cross Tab that reflects the monthly Auto Part Exports in (\$ 000’s) pertaining to the various divisions of the Company for the half year ending 2009.   The Report has to display percentages for the summarized field (Export) for the periods March 2009 and June 2009 in the Cross Tab (+highlighted column labels+) while keeping the summation for the remaining. So the Report needs to be Conditionally Summarized. This can be achieved only with the proper knowledge of the Grid Functions that are available in Crystal Reports 2008.   Let us first understand the Concept of Indices within the Grid of the Cross Tab with the following:      *where-* *(RI) – Row Index* *(CI) – Column Index* *(SI) – Summary Index*

*

N®**-* Number of Rows *N© – *Number of Columns

In the Grid Layout of the Cross Tab, the Row Index starts from 0 and increments by 1 for each subsequent Row. Similarly the Column Index starts from 0 and increments by 1 for  each subsequent Column. The Summary index is 0 for the first Summarized field, 1 for the next and so on. In order to handle the task of Conditional Summarization towards granting the solution the current challenge we have to utilize the Grid Concept within the Advanced Calculations of the Cross Tab.

So let us insert an Embedded Summary in the Cross Tab by performing the following action: Cross Tab  (Right Click)->Advanced Calculations->Embedded Summary. In the ‘Embedded Summaries’ window, we can Add (New), Remove and Edit the Embedded Summary.When we add or insert an Embedded Summary, we can make use of these three Indices so as to help us to refer a particular cell. The interesting point here is the Summarized field shares the same Row Index and Column Index and hence, as it may seem apparently, it is the Summary Index which differs.

So considering the First cell of the above Illustration, the comparative indices are presented below:

*where-* *(RI) – Row Index* *(CI) – Column Index* *(SI) – Summary Index*  Thus, it becomes clear from the above illustration that only the Summary Index varies for the Embedded Summary when compared with the Summarized Field. Again, interestingly it shares the same Column Label of the Summarized field which justifies the name it holds.   We can effectively use the Embedded Summary to skillfully devise formula using the following *function*:Let us utilize this Function within the Calculation (+formula+) of the Embedded Summary as under: If ((CurrentColumnIndex+1) Mod 3 = 0 ) then (IIF ((GridValueAt ( GetNumRows-1 , CurrentColumnIndex , CurrentSummaryIndex-1 ))<>0,100 * (GridValueAt ( CurrentRowIndex , CurrentColumnIndex , CurrentSummaryIndex-1 )/GridValueAt ( GetNumRows-1 , CurrentColumnIndex , CurrentSummaryIndex-1 )),0 )) Else (GridValueAt (CurrentRowIndex , CurrentColumnIndex , CurrentSummaryIndex-1)) After we have included the Embedded Summary in the Cross Tab, we no longer need the Summarized field. So, we suppress this field by performing the following action: Summarized field (Right Click)->Format Field->Common Tab->Suppress (Check the option).

You must be Logged on to comment or reply to a post.
• Could you explain the business reason as to why we should not summarize results for two months (here March 2009 and June 2009) but summarize for rest of the months.
• Amogh P Post author
Hi,

This is a sample Report prepared just to illustrate the functionality that we can vary the nature of Summary conditionally within a Cross Tab- from Summation in few Rows/Columns to Percentages/Averages/Count etc in the remaining ones.

The real life application of this could be in Income Statement/Financial Statement where it requires the figures displayed in some Rows and Percentages displayed in rest of the. e.g.: Net Earnings, Income tax, Net Investment Income etc. is displayed in figures in Rows; Delinquency Rates, Underwriting Expense Ratio, Actuarial Loss Ratio etc is displayed in percentages.

• The example explained is good, shows the possibility , but the problem i am facing is , for ex. there is just 1 column with summarised total, and depending on the value of the column i want to spress a full row.
eg;
Total
row 1    8.5
row2     4
row3     5
i want to suppress the row with value 4 and show only 2 rows in the cross tab row1 and row 3 how do i do that ?.
• Amogh P Post author
Here is something you can try:
Make use of Cross Tab Advanced Calulations and Insert Embedded Summary.

Assign Null value to Embedded summary value where the following condition is true-

(GridValueAt (CurrentRowIndex,
CurrentColumnIndex , CurrentSummaryIndex-1)) = 4

Now suppress the summary field and keep embedded summary visible.

Go to Cross Tab Expert -> Customize Style, and check the option ‘Suppress Empty Rows’.

You can try this out.

• Can anyone suggest a book for Crystal Reports (with advanced features explained).