After a long time, I am going to discuss about creating a  dynamic visual web part in SharePoint 2010. Suppose, a company needs to create a sales forecasting web part on monthly basis. They want to display information in a web part of a page where the data will come from different lists from a web site and hence the result will be shown visually to highlight information based on conditions.

Objective:

Select a month from the drop down list of all months and based on the selected month, the grid view should change automatically. A LINQ will be executed in the background whenever you change a month from the drop down list and the data displayed inside the grid view should match the query results.

Solution:

First of all, Create a  visual studio project and select Visual Web Part from the SharePoint 2010 templates. This automatically adds a Feature with a Default Visual Web Part in your solution. Please note that, while creating a visual web part, you must choose as Farm Solution as the files of visual web parts (ASCX Files) need to be deployed physically inside 14 hive.

You will need to write LINQ to query your SharePoint Lists. So, create a Entity Class using SPMetal.exe. To know more about how to use SPMetal, go here. Create and add the entity class in your project. For my example; I named the class as MyEntity.cs.

To start off with the solution, let us drag and drop a drop down box and create SPGridView object. The ASCX code is given below:

If you look inside the above code, you will find that I have created four columns inside the grid view to display Employee (Consultant) Name, Total Revenue, Total Revenue Quota and Status and a drop down box containing all months. Now, create the entity object for the lists inside your ascx.cs file. For my scenario, I had to query two different lists and I created the entity and data context object as below:

EntityList<SCTXPSITSVCsForecastingItem> SCTXPSITSVCsForecasting;

EntityList<SolnConsultantsItem> SolnConsultants;

protected void Page_Load(object sender, EventArgs e)

{

    string spWebUrl = SPContext.Current.Web.Url;

    MyEntitiesDataContext dc = new MyEntitiesDataContext(spWebUrl);

    SCTXPSITSVCsForecasting = dc.GetList<SCTXPSITSVCsForecastingItem>(@"SCTX PS / ITSVCs Forecasting");

    SolnConsultants = dc.GetList<SolnConsultantsItem>("SolnConsultants");

}

From the above code, you can see I created two data context object where my entity class was “MyEntities”. I then called the two lists in the page load event by the GetList() Method.

Inside the SelectedIndexChanged() method of the drop down list for month, I called a method to create the LINQ and passed the selected item as the parameter. If you notice the LINQ below, you will see that I have used a join query to select data from two different lists. For my scenario, I had a list called “SCTXPSITSVCsForecasting” where there was a column named “ITSRicohLaborRevenue” in which all the employees (consultants) monthly forecast had to be summed up. So, I used an aggregated function (SUM) to sum up all the employees monthly forecast and divided by the standard monthly forecast to determine their status (if greater than 90%, “Green”, if greater than or equal to 75%, “Yellow” and if below 75%, “Red”).

To display the status of the forecast visually, I had to add another method for the grid view on RowDataBound():

You can see from the above code that I am changing the Background color , Foreground color of the row based on the Status column’s value.

After deploying and adding the web part on a page, you will get a following screen:

You can see from the above screenshots that the gird view is changing dynamically based on the selected month. Following this basic example, you can create more complex dynamic visual web parts in SharePoint.