Dynamic Gridview

I am trying to design a gridview that has a couple requirements that seem to need dynamic generation:
- The first column lists a product line pulled from the gridview datasource, no big deal there...
- All other columns and their headings need to be generated by results of a query to a table that lists metrics the customer wants (avg time to close, etc).
- I need to specifically perform calculations on each cell under each metric name using the product line given in the first column.

How can this be done?
dyn-gv.jpg
devo00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anurag ThakurTechnical ManagerCommented:
as liine one's items are coming from the database why dont you use the same value to perform the calculations in the database and then send only those columns which are required and then just bind the datatable/dataset to the gridview
0
devo00Author Commented:
That would be my first guess, but I have a query building section for criteria in the SQL statement based on user input  to delimit results on the same page, so this can't be automated at the database.
0
Anurag ThakurTechnical ManagerCommented:
you can create a dynamic sql statement based on the inputs provided - means (assumption here) if the user says that out of 10 show me only 4 columns along with the calculations then i will make a dynamic query in the sql stored procedure in which i will add only the required columns to be processed and sent back to the caller so that when the datatable/dataset is bound then only 4 + 1 = 5 columns are rendered on the grid
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

devo00Author Commented:
BTW as mentioned in the question, the # of columns are based upon another query. Row data from a metrics title table need to be the headings for this gridview.
0
Anurag ThakurTechnical ManagerCommented:
even of your columns are based upon another query what you can do return a predefined set of columns from the stored procedure and before you databind the datatable to the gridview delete the columns which are not required. this approach will be a little slower than what i suggested before i.e. doing it dynamically in the stored procedure
0
devo00Author Commented:
Thanks, I figured it out on my own, see below. Columns and cells are auto-generated on the fly.

    protected void gv1_rowDataBound(object sender, GridViewRowEventArgs e)
    {
        string metric;

        //For calculations below, grab the value displayed in the first row
        //which was included in the default gridview datasource:
        Label productlbl = (Label)e.Row.Cells[0].FindControl("productlbl");

        Service creSvc = new Service();
        DataSet DS = new DataSet();
        DS = creSvc.rtnDataSetOra("select goal_name, req_status from cre_goal_targets");
       
        if (e.Row.RowType == DataControlRowType.Header)
        {
            //Provide data-driven headers
            foreach (DataRow row in DS.Tables[0].Rows)
            {
                    Label l = new Label();
                    l.ID = "L1";
                    l.Text = row["goal_name"].ToString();
                    TableCell tc = new TableCell();
                    tc.Controls.Add(l);
                    e.Row.Cells.Add(tc);
            }
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
 
            //Insert data from dataset and / or perform calculations
            foreach (DataRow row in DS.Tables[0].Rows)
            {
                Label l = new Label();
                l.ID = "L1";

                metric = row["goal_name"].ToString();
                l.Text = "Some Value, perform calcuations here";

                TableCell tc = new TableCell();
                tc.Controls.Add(l);
                e.Row.Cells.Add(tc);
            }
        }
    }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.