<asp:Chart ID="chat_events" runat="server" BorderlineColor="black" CssClass="table table-bordered table-condensed table-responsive" Height="410px" Width="650px" BorderlineDashStyle="Solid" BorderlineWidth="10" BorderSkin-SkinStyle="FrameThin5"> <%--class="img-responsive center-block" style="width:100%"--%>
<Titles>
<asp:Title Font="Times New Roman, 15pt, style=Bold, Italic" Name="Title1" Text="Cadre Distribution">
</asp:Title>
</Titles>
<Series>
<asp:Series Name="Scientist" Color="Darkblue" ChartType="Column">
</asp:Series>
<asp:Series Name="Senior Scientist" Color="Darkorange" ChartType="Column">
</asp:Series>
<asp:Series Name="Principal Scientist" Color="Darkgreen" ChartType="Column">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1" BackColor="white" ShadowColor="Aqua">
<AxisX>
<MajorGrid Enabled="false" />
</AxisX>
<AxisY>
<MajorGrid Enabled="true" />
</AxisY>
</asp:ChartArea>
</ChartAreas>
</asp:Chart>
-------------------------
aspx.cs page
protected void Page_Load(object sender, EventArgs e)
{
DGMConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DGMConn"].ConnectionString);
if (!Page.IsPostBack)
{
bindcount();
}
}
private void bindcount()
{
// string str = "select COUNT (trainingApprovedID)as event_count, 'month_name'=LEFT(datename(MONTH,trainingEndDate),3)+' ('+datename(YEAR,trainingEndDate)+')' from TrainingApproved where DATEPART(YYYY,trainingEndDate)> DATEPART(YYYY, DATEADD(year,-2,GETDATE()) ) group by datename (month,trainingEndDate),datename(YEAR,trainingEndDate) order by max(trainingEndDate)";
// string str = "select t3.CadreType, Case when t3.CadreType= 'Scientist (incl. SS (CAS), PS (CAS))' then 'Scientist (incl. SS(CAS), PS(CAS)'when t3.CadreType='DR-Sr. Scientist (incl. PS (CAS))' then 'DR-SS(incl. PS(CAS)'when t3.CadreType='DR- Pr. Scientist' then 'DR- PS' Else 'Nil' END Cadre ,ISNULL(Scientist, 0) as Scientist,ISNULL(SeniorScientist, 0 ) as SeniorScientist,principalScientist from(SELECT CadreType,count (fmsid) as Scientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and (PresentDesignation =1 or PresentDesignation =3 or PresentDesignation =4) group by CadreType ) t1 left join (SELECT CadreType,count (fmsid) as SeniorScientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and PresentDesignation=2 group by CadreType) t2 on t1.CadreType=t2.cadretype right join (SELECT CadreType,count (fmsid) as principalScientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and PresentDesignation=5 group by CadreType) t3 on t1.CadreType=t3.cadretype order by principalScientist desc";
string str="select t3.CadreType, Case when t3.CadreType= 'Scientist (incl. SS (CAS), PS (CAS))' then 'Scientist (incl. SS(CAS), PS(CAS)'when t3.CadreType='DR-Sr. Scientist (incl. PS (CAS))' then 'DR-SS(incl. PS(CAS)'when t3.CadreType='DR- Pr. Scientist' then 'DR- PS' Else 'Nil' END Cadre ,ISNULL(Scientist, 0) as Scientist,ISNULL(SeniorScientist, 0 ) as SeniorScientist,principalScientist from(SELECT CadreType,count (fmsid) as Scientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and (PresentDesignation =1 or PresentDesignation =3 or PresentDesignation =4) group by CadreType ) t1 full outer join (SELECT CadreType,count (fmsid) as principalScientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and PresentDesignation=5 group by CadreType) t3 on t1.CadreType=t3.cadretype full outer join (SELECT CadreType,count (fmsid) as SeniorScientist FROM icms_employee ie join icms_cadretype ic on ie.postheld=ic.CadreTypeID where IsTenuralPost='0' and cadretypeid!='12' and ie.flag='1' and PresentDesignation=2 group by CadreType) t2 on t3.CadreType=t2.cadretype order by principalScientist desc";
SqlDataAdapter da = new SqlDataAdapter(str, DGMConn);
DataTable dt = new DataTable();
da.Fill(dt);
chat_events.Legends.Add(new Legend("Default") { Docking = Docking.Bottom });
chat_events.Legends["Default"].Alignment = StringAlignment.Center;
chat_events.BackColor = System.Drawing.Color.Gray;
chat_events.Series[0].IsValueShownAsLabel = true;
chat_events.Series[1].IsValueShownAsLabel = true;
chat_events.Series[2].IsValueShownAsLabel = true;
chat_events.DataSource = dt;
chat_events.Series[0].XValueMember = "Cadre";
// this.chat_events.ChartAreas[0].AxisX.LabelStyle.ForeColor = Color.White;
this.chat_events.ChartAreas[0].AxisX.LabelStyle.Font = new System.Drawing.Font("Trebuchet MS", 12.25F, System.Drawing.FontStyle.Bold);
chat_events.Series[0].YValueMembers = "Scientist";
chat_events.Series[1].YValueMembers = "SeniorScientist";
chat_events.Series[2].YValueMembers = "principalScientist";
chat_events.Series[0].ToolTip = "Scientist Count:#VALY{G}";
chat_events.Series[1].ToolTip = "Senior Scientist Count:#VALY{G}";
chat_events.Series[2].ToolTip = "Principal Scientist Count:#VALY{G}";
}
query will give the following results.
No comments:
Post a Comment