note: the datasource is a SQL Server stored procedure
Thanks,
SteveO
public DataTable PivotTable(DataTable source)
{
DataTable dest = new DataTable("Pivoted" + source.TableName);
dest.Columns.Add(
" ");
foreach (DataRow r in source.Rows)
dest.Columns.Add(r[0].ToString());
for (int i = 0; i < source.Columns.Count - 1; i++)
{
dest.Rows.Add(dest.NewRow());
}
for (int r = 0; r < dest.Rows.Count; r++)
{
for (int c = 0; c < dest.Columns.Count; c++)
{
if (c == 0)
dest.Rows[r][0] = source.Columns[r + 1].ColumnName;
else
dest.Rows[r][c] = source.Rows[c - 1][r + 1];
}
}
dest.AcceptChanges();
return dest;
}
protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
RadGrid1.DataSource = PivotTable(GetDataTable(
"SELECT CONVERT(varchar,StatDate,101) StatDate,FelonyArrests,MisdArrests,NTA,FSAO,TrafficArrests,WarrantArrests,DispCalls,SelfInitActivities,[BackUp] BackUps,FIR," +
"VehRecov,TrafficCitations,CourtesyNotices,TrafficStops,WarrantsServed,WarrantsObtained,NewGangFiles,GangFileUpd,Presentations,InvestHours," +
"IntelRpts,PropValRecov,GunsRecov,IntelBullitens,SearchWarrantHours,TasksAssigned,TasksTrans,TasksCleared,AssignedActive,Transfered," +
"ClearedByArrest,EC,Unfounded,Closed,Inactive,Surveillance,Callouts,SearchWarrants FROM Gang_Stats ORDER BY StatDate asc"));
}
protected void RadGrid1_ColumnCreated(object sender, Telerik.Web.UI.GridColumnCreatedEventArgs e)
{
if (e.Column.UniqueName == "StatDate")
{
GridBoundColumn boundColumn = e.Column as GridBoundColumn;
boundColumn.DataFormatString =
"{0:d}";
boundColumn.ItemStyle.Font.Bold =
true;
}
}
25 Answers, 1 is accepted
The unique name of the autogenerated columns depends on the data field of the table that is bound to the grid. You should check what is the name of the columns in the datatable that is datasource of the grid. If the data column names are numbers then it is expected the column unique names to be numbers as well.
All the best,Marin
the Telerik team

In the Telerik example, you are instructed to accomplish this in the OnColumnCreated handler - which I have attempted to do without success.
(example below)
Thanks,
SteveO
| 09/01/2012 | 09/02/2012 | 09/03/2012 | 09/04/2012 | 09/05/2012 | 09/06/2012 | <<
------------------------------------------------------------------------------------------------------------------------------
Status Header 1 | 8 | 0 | 1 | 0 | 7 | 3 |
--------------------------|---------------|-----------------|----------------|----------------|-----------------|---------------|
Status Header 2 | 0 | 1 | 9 | 2 | 1 | 0 |
protected void RadGrid1_ColumnCreated(object sender, Telerik.Web.UI.GridColumnCreatedEventArgs e)
{
GridBoundColumn boundColumn = e.Column as GridBoundColumn;
if (e.Column is GridBoundColumn)
{
if (e.Column.UniqueName == "StatDate")
{
boundColumn.DataFormatString =
"{0:dd/MM/yyyy}";
boundColumn.ItemStyle.Font.Bold =
true;
}
if (e.Column.UniqueName == "DrugMoneySeized")
{
boundColumn.DataFormatString =
"{0:c2}";
boundColumn.FooterAggregateFormatString =
"{0:c2}";
}
if ((e.Column.UniqueName == "PopStatsId") || (e.Column.UniqueName == "DeputyId") || (e.Column.UniqueName == "EID") || (e.Column.UniqueName == "InsertedBy") || (e.Column.UniqueName == "InsertedDate") || (e.Column.UniqueName == "UpdatedBy") || (e.Column.UniqueName == "UpdatedDate"))
{
boundColumn.Visible =
false;
}
}
}

The remainder of the column types returned are "GridExpandColumn" types and have no discernible unique properties to determine what item it is.
I basically need to be able to adjust properties of autogeneratedcolumn's with datasource names - that are then formatted into a pivot table.
SteveO

Thanks,
SteveO
I checked your code in the ColumnCreated event and it is correct. You are using the right approach. The columns with unique name "ExpandColumn" are service columns and are automatically generated by the control. You do not need to modify them. The columns that have unique name as number are the GridBoundColumns that you need to access but their UniqueName property is obviously not generated correctly. If the name of the field in the database is "StartDate" then the UniqueName of the autogenerated column should also be "StartDate". I suppose the problem in this case is in the structure of the datasource that you pass to the control.
Note that RadGrid does not support pivoting out of the box and is not designed to display data in pivot structure.
For this purpose you can use the new PivotGrid control which should handle this properly.
If you still need to use RadGrid please let us know what is the exact structure of the datasource as well as how and where you bind the control so we can check whether this case is supported and what is causing the problem.
Also if you need faster response time I suggest you open a formal support ticket which will be handled within 24 hours (you can reference this forum thread there).
Marin
the Telerik team

Thanks,
SteveO

Note: This is my first experience with RadPivotGrid, so not certain I defined it correctly. I tries with and without <field> definitions.
Any examples would be helpful.
SteveO
protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.PivotGridNeedDataSourceEventArgs e)
{
DateTime mthyr = DateTime.MinValue;
DateTime fdate = DateTime.MinValue;
DateTime tdate = DateTime.MinValue;
DataSet ds;
if (!Page.IsPostBack)
{
// default to todays date if none passed in
if ((string.IsNullOrEmpty(rmyp.SelectedDate.ToString()) && string.IsNullOrEmpty(frdp.SelectedDate.ToString())))
{
mthyr =
DateTime.Today;
ds =
SPs.Get_Pops_Stats(dname, mthyr, null, null).GetDataSet();
RadGrid1.DataSource = ds;
RadGrid1.DataBind();
}
}
}-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<telerik:RadPivotGrid ID="RadGrid1"
Runat="Server"
OnNeedDataSource="RadGrid1_NeedDataSource"
OnCellDataBound="RadGrid1_CellDataBound"
OnPreRender="RadGrid1_PreRender"
AllowSorting="true"
AllowPaging="true"
PageSize="20">
<ClientSettings Scrolling-AllowVerticalScroll="true">
<Scrolling AllowVerticalScroll="true" ScrollHeight="520px"></Scrolling>
</ClientSettings>
<fields>
<telerik:PivotGridColumnField DataField="StatDate" DataFormatString="{0:mm/dd/yyyy}"></telerik:PivotGridColumnField>
<telerik:PivotGridRowField DataField="Surveillance"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="Training"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="BikePatrol"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="DrugMoneySeized" DataFormatString="{0:c2}"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="SpecialDetail"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="SearchWarrantHours"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="SearchWarrantExec"></telerik:PivotGridRowField>
<telerik:PivotGridRowField DataField="CommEvents"></telerik:PivotGridRowField>
<telerik:PivotGridAggregateField DataField="Surveillance" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="Training" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="BikePatrol" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="DrugMoneySeized" Aggregate="Sum" DataFormatString="{0:c2}"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="SpecialDetail" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="SearchWarrantHours" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="SearchWarrantExec" Aggregate="Sum"></telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="CommEvents" Aggregate="Sum"></telerik:PivotGridAggregateField>
</fields>
</telerik:RadPivotGrid>
You should not call the DataBind method from inside the NeedDataSource event. More information about databinding the control can be found in this help topic. Also please make sure you set the DataSource of the control on every postback not only when the page is loaded initially.
I hope this helps.
Marin
the Telerik team

My question is then, what is needed to accomplish this - not about anything else.
I am attaching a sample page based on your code snippets. It renders the data properly.
Greetings,Marin
the Telerik team

Thank you very much for all your help. The only other question I have is; how and where do I incorporate the call to my stored procedure in the code behind (GetData) that you provided. I do not see any reference to it. Obviously, it would come after the DataSet declaration, that I have tried with no results.
Thank you,
SteveO
* ds =
SPs.Get_Pops_Stats(dname, mthyr, null, null).GetDataSet(); (see attachment)
DataSet GetData()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("StatDate", typeof(DateTime));
dt.Columns.Add("Surveillance", typeof(int));
dt.Columns.Add("Training", typeof(int));
dt.Columns.Add("BikePatrol", typeof(int));
dt.Columns.Add("DrugMoneySeized", typeof(decimal));
dt.Columns.Add("SpecialDetail", typeof(int));
dt.Columns.Add("SearchWarrantHours", typeof(int));
dt.Columns.Add("SearchWarrantExec", typeof(int));
dt.Columns.Add("CommEvents", typeof(int));
for (int i = 0; i < 100; i++)
{
var dr = dt.NewRow();
dr["StatDate"] = DateTime.Now.AddDays(i % 4);
dr["Surveillance"] = i % 6;
dr["Training"] = i % 3 + 1;
dr["BikePatrol"] = i % 7 + 2;
dr["DrugMoneySeized"] = i % 4 + 3;
dr["SpecialDetail"] = 10 + (i % 3);
dr["SearchWarrantHours"] = 16 + (i % 3 + 1);
dr["SearchWarrantExec"] = 126 + (i % 3 + 1);
dr["CommEvents"] = i % 4 + 2;
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
return ds;
}

In the GetData method in the page that I sent I generate a sample DataSet that holds dummy data just for the case of the example. In your case you do not need to generate dataset with columns and data. You can simply call the Stored procedure in the GetData method, and then pass the returned object from the Select method of the datasource control to the pivot grid:
sqlDataSource1.SelectCommand =
"myStoredProcedure"
;
sqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
var data = sqlDataSource1.Select(DataSourceSelectArguments.Empty);
// the data variable is of a type that implements IEnumerable (for example DataView)
// you can simply return this object from the GetData method and pass it as a datasource to the PivotGrid
// of course make sure the object holds the necessary data
I hope this helps. All the best,
Marin
the Telerik team

What I am trying to accomplish is a pivot table with "Dates" in columns and "Statistics" in the rows. The issue I see, is due to the denormalized table data. A Stats table contains a field for each individual stat, when I only need one "Stat" column in the grid. My problem is, not knowing how to handle this.
I apologize for being such a pain about this. Your help is very much appreciated.
Thanks,
SteveO
| 09/01/2012 | 09/03/2012 | 09/06/2012 | 09/12/2012 | 09/13/2012 |
Stat1 | 0 | 3 | 0 | 0 | 2 |
Stat2 | 5 | 0 | 10 | 1 | 0 |
Stat3 | 1 | 4 | 4 | 8 | 0 |
Stat4 | 0 | 7 | 1 | 3 | 3 |
Stat5 | 9 | 0 | 0 | 0 | 1 |
Totals | 15 | 14 | 15 | 12 | 6 |
I am attaching a sample page which shows RadPivotGrid configured to show the data in the same way as in the screenshot that you posted.
I hope this helps.
Marin
the Telerik team

Thank you, ut it appears that this control is not up to speed yet so I will roll back to using the PivotTable hack.
Thanks,
SteveO

Thanks,
SteveO
You can do this by setting the DataFormatString property of the respective column or in the ItemDataBound event you can modify the text that will be shown in the cell. You can can access a cell in the following way:
protected
void
RadGrid1_ItemDataBound(
object
sender, GridItemEventArgs e)
{
if
(e.Item
is
GridDataItem)
{
(e.Item
as
GridDataItem)[
"column unique name"
].Text =
"formatted text"
;
}
}
Marin
the Telerik team

Thank you for your quick response.
Using the ItemDataBound event option you provided, and after entering the column's name (see attached) - I receive the message "Cannot find a cell bound to column name"
I am again back to the original issue I had using the PivotTable format of RadGrid. I do not have a "Unique Name" for any column, thus have no access to the column.
In the PivotTable method, I added column names in index 0 of each row as I was instructed earlier. (see attached)
In a previous post you stated;
"The unique name of the autogenerated columns depends on the data field of the table that is bound to the grid. You should check what is the name of the columns in the datatable that is datasource of the grid. If the data column names are numbers then it is expected the column unique names to be numbers as well."
The items returned by the stored procedure do have alphabetic name's, yet they do not appear - opposed to what you said. (see attached)
Thanks,
SteveO
Yes, we are back at the original issue.
I think the problem here might be in the name of the column that you are trying to create in the grid. The column unique name must be a valid identifier in .NET - meaning that it cannot contain spaces, or special symbols (such as: /, :, "," etc.).
We have a demo that shows a very similar approach for showing pivot data in the grid, but as you can see in this case both the names of the columns and the rows are simple strings and do not contain special symbols.
So I am afraid that this might be a limitation of the RadGrid control since it is not initially designed to show pivot data.
Marin
the Telerik team

Okay, I have now found the UniqueNames of each AutoGeneratedColumn. My question is now; RadGrid1.MasterTableView.AutoGeneratedColumns[0] contains the field's UniqueName. I need to loop through the value fields for each of these columns to format, but can find no way to determine how many columns are in each row for a specific field name.
I have tried RadGrid1.MasterTableView.AutoGeneratedColumns.Count() but see no value.
SteveO
if (e.Item is GridDataItem)
{
string ucol = (e.Item as GridDataItem)[(RadGrid1.MasterTableView.AutoGeneratedColumns[0] as GridBoundColumn).UniqueName].Text;
if (ucol == "PropValRecov")
{
int i = 1;
string stat = ((e.Item as GridDataItem)[(RadGrid1.MasterTableView.AutoGeneratedColumns[i] as GridBoundColumn).UniqueName].Text);
for (int i=0; RadGrid1.MasterTableView.AutoGeneratedColumns.Count(); i++)
{
string.Format((e.Item as GridDataItem)[(RadGrid1.MasterTableView.AutoGeneratedColumns[i] as GridBoundColumn).DataField, "{0:c2}");
}
}
}
}
SteveO
After you transform the original datatable to a pivot data table the new columns may get names that were previously values for some of the rows (for example datatime values which are not valid identifiers). Although the grid is able to display the pivoted data correctly in this case it will be a read-only view since most features (such as sorting, filtering, grouping etc.) will not work properly in this case.
I am attaching a sample page which shows RadGrid databound to a pivot table, and in the ItemDataBound event I have shown how you can format the values, and change the color of the cells.
Let me know how this works in your case and if you have any other questions.
Marin
the Telerik team

For each row in the pivottable, I need to loop through each value and apply a format {0:c2}, but cannot determine how to loop through each column of autogeneratedcolumns/PivotTbale columns
Yes, I read it and I have shown how to do this in the attached sample page.
I am attaching the page again. Let me know if this suits your requirement.
Marin
the Telerik team

Thank you very much. I apologize for being difficult, just under a lot of pressure and stress to get this done.
Thank you,
SteveO