AutoGeneratedColumn's UniqueName, returns column value?

[JSON]

I have created a Pivot Table in RadGrid with AutoGeneratedColumns using a published example. As a specific column "StatDate" is created, I would like to format the date value in the OnColumnsCreated handler. As the example shows, I am checking for the column's "UniqueName" property but have determined that the column's "value" not name is returned. I have since seen either a single digit number or the string "ExpandColumn"

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;

 

}

}

[Marin]

Hi,

 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

[JSON]

Yes, that is obvious - but not the case. The fields in the data source have alphaetic names - thus my question as to why then number name values are assigned to these columns. I need access to the data field properties that are now a column heading in the pivot table layout.
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;

 

}

}

}

[JSON]

In the OnColumnCreated event, the item I am trying to reference is a "GridBoundColumn" type that contains the item's value in the UniqueName property, not a name or number, why?.
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

[JSON]

Can someone please help me, its been two days now. I have deadlines and this is holding me up. I am attempting to understand and resolve this myself but not having any luck.

Thanks,

SteveO

[Marin]

Hello,

 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).

Kind regards,
Marin
the Telerik team

[JSON]

Upgraded my controls and will use the RadPivotGrid and se how it goes.

Thanks,

SteveO

[JSON]

I am populating the RadPivotGrid's data source from a SQL Server stored procedure. I validated the results but see no databound values rendered.

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>

 

[Marin]

Hello,

 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.

All the best,
Marin
the Telerik team

[JSON]

Yes, I am aware that NeedDataSource implies binding, that was not meant to be there. Anyway, on the inital bind, I still do not see any data after needdatasource. What is the problem? I have tried many different versions, yet by implicit binding, I render no data.

My question is then, what is needed to accomplish this - not about anything else.

[Marin]

Hello,

 I am attaching a sample page based on your code snippets. It renders the data properly.

Greetings,
Marin
the Telerik team

[JSON]

Marin,

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;
    }

[JSON]

Attached is an example of what I am trying to accomplish

[Marin]

Hi,

 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

[JSON]

Marin,

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        |

[Marin]

Hello Steve,

 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.

All the best,
Marin
the Telerik team

[JSON]

Marin,

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

[JSON]

Again, I am back using a pivottable on a RadGrid. I need to format an item for a currency  value in the ItemCreated event, but am having no success. 

Thanks,

SteveO

[Marin]

Hello,

 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";
        }
    }

Kind regards,
Marin
the Telerik team

[JSON]

Marin,

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

[Marin]

Hi Steve,

 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.

Kind regards,
Marin
the Telerik team

[JSON]

Marin,

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

[Marin]

Hi Steve,

 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.

Regards,
Marin
the Telerik team

[JSON]

Did you not read my previous post? Anyway, the example url is not found.

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

[Marin]

Hi,

 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.

All the best,
Marin
the Telerik team

[JSON]

Marin,

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

Thank you,

SteveO