Displaying Cross-Tab in C1TrueDBGrid

Significance of Cross-Tab View

A Cross-Tab display shows the relationship between two or more fields in an organized way, similar to that in reports. It provides a side-by-side comparison of different groups or fields. Cross-Tab is generally used in reporting to provide a comparison between surveys and a summary of data. The Cross-Tab display can be used when two or more surveys are to be compared.

A Much Wider Perspective

What if there is some requirement that involves the evaluation of the amount of sales for a salesman for various products. Such evaluation can be troublesome when the data is displayed in a simple tabular form. However, a much better option would be displaying the sales in a Cross-Tab manner which makes the data more organized. Generally, the Cross-Tab is accomplished by pivoting the rows and columns i.e. inverting the display, changing the rows to columns and the columns to rows. This change in the display provides the flexibility to compare the sales carried out by two salesmen.

Getting a Sneak Peak of the Logic

Cross-Tab in C1TrueDBGrid can be implemented at the DataSource level. The DataTable that is supposed to be bound to the grid is worked upon, modified and stored in another instance such that the newly generated table displays the Cross-Tab.

Connecting the Duo: Logic + Code

Below is the code snippet that would help us in converting a DataTable according to the requirement.

public DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues)  
{  
//Create a DataTable to Return  
DataTable returnTable = new DataTable();  

if (string.IsNullOrEmpty(columnX))  
{  
columnX = table.Columns[0].ColumnName;  
}  

//Add a Column at the beginning of the table  
returnTable.Columns.Add(columnY);  

//Read all DISTINCT values from columnX Column in the provided DataTale  
List columnXValues = new List();  

foreach (DataRow dr in table.Rows)  
{  

string columnXTemp = dr[columnX].ToString();  
if (!columnXValues.Contains(columnXTemp))  
{  
//Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.  
columnXValues.Add(columnXTemp);  
returnTable.Columns.Add(columnXTemp);  
}  
}  

//Verify if Y and Z Axis columns re provided  
if (!string.IsNullOrEmpty(columnY) && !string.IsNullOrEmpty(columnZ))  
{  
//Read DISTINCT Values for Y Axis Column  
List columnYValues = new List();  

foreach (DataRow dr in table.Rows)  
{  
if (!columnYValues.Contains(dr[columnY].ToString()))  
{  
columnYValues.Add(dr[columnY].ToString());  
}  
}  

//Loop all Column Y Distinct Value  
foreach (string columnYValue in columnYValues)  
{  
//Creates a new Row  
DataRow drReturn = returnTable.NewRow();  
drReturn[0] = columnYValue;  
//foreach column Y value, The rows are selected distincted  
DataRow[] rows = table.Select((columnY + "='") + columnYValue + "'");  

//Read each row to fill the DataTable  
foreach (DataRow dr in rows)  
{  
string rowColumnTitle = dr[columnX].ToString();  

//Read each column to fill the DataTable  
foreach (DataColumn dc in returnTable.Columns)  
{  
if (dc.ColumnName == rowColumnTitle)  
{  
//If Sum of Values is True it try to perform a Sum  
//If sum is not possible due to value types, the value displayed is the last one read  
if (sumValues)  
{  
try  
{  
drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]);  
}  
catch  
{  
drReturn[rowColumnTitle] = dr[columnZ];  
}  
}  
else  
{  
drReturn[rowColumnTitle] = dr[columnZ];  

}  
}  
}  
}  

returnTable.Rows.Add(drReturn);  

}  
}  
else  
{  
throw new Exception("The columns to perform inversion are not provided");  
}  

//if a nullValue is provided, fill the datable with it  
if (!string.IsNullOrEmpty(nullValue))  
{  
foreach (DataRow dr in returnTable.Rows)  
{  
foreach (DataColumn dc in returnTable.Columns)  
{  
if (string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))  
{  
dr[dc.ColumnName] = nullValue;  
}  
}  
}  
}  

return returnTable;  
}  

Finally with the above implementation, Cross-Tab can be displayed in a C1TrueDBGrid. It can be modified further according to the requirement, i.e. which field needs to be evaluated in an organized way.