How to Create Graph And Drill Downgraph Using Telerik Graph Using StoreProcedure And Wpf ?

Here Sample code of Drill Down Chart using store Procedure and WPF

XAML Code

<ScrollViewer VerticalScrollBarVisibility="Auto" Grid.Column="1">

                           <ScrollViewer.Background>

                                         <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">

                                                <GradientStop Color="Black" Offset="0"/>

                                                <GradientStop Color="White" Offset="1"/>

                                         </LinearGradientBrush>

                                </ScrollViewer.Background> 

                            <Grid x:Name="chartgrid" ShowGridLines="True" Grid.Column="1"  />

    </ScrollViewer>

Code Behind

public partial class SharvanGraph : Window

    {

        HTBookEntities proentity = new HTBookEntities();

        public SharvanGraph()

        {

            InitializeComponent();

            radChart1.DefaultView.ChartArea.ItemClick += new EventHandler<ChartItemClickEventArgs>(ChartArea_ItemClick);

        }

        SeriesMapping sm = new SeriesMapping();

        RadChart radChart1 = new RadChart();

        RadChart radchart2 = new RadChart();

        SeriesMapping sm2 = new SeriesMapping();

     

       

        private void cmbkhojiname_SelectionChanged(object sender, SelectionChangedEventArgs e)

        {

            try

            {

                if (datePicker1.SelectedDate == null || datePicker2.SelectedDate == null)

                {

                    MessageBox.Show("Please Select First Date");

                }

                else

                {

                    int kid = (int)cmbkhojiname.SelectedValue;

                    string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();

                    var data = proentity.AllMonthdataGraph((DateTime)datePicker1.SelectedDate, (DateTime)datePicker2.SelectedDate, null, kid).ToList();

                  

                    if (chartgrid.RowDefinitions.Count <= 2)

                    {

                        RowDefinition rd2 = new RowDefinition();

                        rd2.Height = new GridLength(0, GridUnitType.Star);

                        chartgrid.RowDefinitions.Add(rd2);

                        for (int i = 0; i <= 1; i++)

                        {

                            RowDefinition rd = new RowDefinition();

                            rd.Height = new GridLength(1, GridUnitType.Auto);

                            chartgrid.RowDefinitions.Add(rd);

                        }

                    }

                    radChart1.SetValue(Grid.RowProperty, 1);

                    radChart1.Height = 350;

                    if (sm.SeriesDefinition == null)

                    {

                        chartgrid.Children.Clear();

                        chartgrid.Children.Add(radChart1);

                        sm.SeriesDefinition = new BarSeriesDefinition();

                        sm.LegendLabel = "Pravachan" + "\n" + "Count" + "\n" + "Each" + "\n" + "Month";

                        sm.ItemMappings.Add(new ItemMapping("Monthyear", DataPointMember.XCategory));

                        sm.ItemMappings.Add(new ItemMapping("pcount", DataPointMember.YValue));

                        radChart1.SeriesMappings.Add(sm);

                    }

                    else

                    {

                        radChart1.ItemsSource = data;

                    }

                    radChart1.ItemsSource = data;

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

           

        }

         ------------------------------------------------------------------
        private void Window_Loaded(object sender, RoutedEventArgs e)

        {

            try

            {

                cmbkhojiname.ItemsSource = (from k in proentity.KhojiMasters

                                            orderby k.KhojiName

                                            select new

                                            {

                                                k.KhojiId,

                                                k.KhojiName

                                            });

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

          

        }

        private void expCentredetail_Expanded(object sender, Telerik.Windows.RadRoutedEventArgs e)

        {

        }

        #region code Sharvanthanwise Chart

        private void All_Checked(object sender, RoutedEventArgs e)

        {

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            var data = (from k in proentity.KhojiMasters

                        select new

                        {

                            k.KhojiId

                        });

            WrapPanel wrap = new WrapPanel();

            chartgrid.Children.Add(wrap);

            foreach (var id in data)

            {

                #region scollviewINCode

                //ScrollViewer scroll = new ScrollViewer();

                //scroll.SetValue(Grid.ColumnProperty, 1);

                //ScrollViewer.SetVerticalScrollBarVisibility(wrap, ScrollBarVisibility.Auto);

                //ScrollViewer.SetHorizontalScrollBarVisibility(wrap, ScrollBarVisibility.Auto);

                #endregion

                int kid = (Int32)id.KhojiId;

                var chartdata = proentity.MonthNameWiseCount(kid).ToList();

                string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();

                Telerik.Windows.Controls.RadChart radchart1 = new Telerik.Windows.Controls.RadChart();

                radchart1.HorizontalAlignment = HorizontalAlignment.Left;

                radchart1.Margin = new Thickness(10, 10.076, 10, 7.01);

                radchart1.FontFamily = new FontFamily("Comic Sans MS");

                radchart1.Width = 321.07;

                radchart1.Height = 250;

                wrap.Children.Add(radchart1);

                SeriesMapping sm = new SeriesMapping();

                sm.SeriesDefinition = new BarSeriesDefinition();

                sm.LegendLabel = "P";

                sm.ItemMappings.Add(new ItemMapping("Adate", DataPointMember.XCategory));

                sm.ItemMappings.Add(new ItemMapping("Pcount", DataPointMember.YValue));

                radchart1.DefaultView.ChartArea.AxisX.LabelRotationAngle = 50;

                radchart1.SeriesMappings.Add(sm);

                radchart1.Content = kname;

                radchart1.ItemsSource = chartdata;

            }

            this.Cursor = savecursor;

        }

        private void Happyshop_Checked(object sender, RoutedEventArgs e)

        {

     

      chartgrid.Children.Clear();

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)Happyshop.Content);

            this.Cursor = savecursor;

            //gridname.IsBusy = false;

        }

        private void katargam_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)katargam.Content);

            this.Cursor = savecursor;

        }

        private void HSmorning_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)HSmorning.Content);

            this.Cursor = savecursor;

        }

        private void LH_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)LH.Content);

            this.Cursor = savecursor;

        }

        private void valamnagar_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)valamnagar.Content);

            this.Cursor = savecursor;

        }

        private void Udhana_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)Udhana.Content);

            this.Cursor = savecursor;

        }

        private void sachin_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            Rishikesh.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)sachin.Content);

            this.Cursor = savecursor;

        }

        private void Rishikesh_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Citylight.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)Rishikesh.Content);

            this.Cursor = savecursor;

        }

        private void Citylight_Checked(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

            Happyshop.IsChecked = false;

            katargam.IsChecked = false;

            HSmorning.IsChecked = false;

            LH.IsChecked = false;

            valamnagar.IsChecked = false;

            Udhana.IsChecked = false;

            sachin.IsChecked = false;

            Rishikesh.IsChecked = false;

            System.Windows.Input.Cursor savecursor = this.Cursor;

            this.Cursor = Cursors.Wait;

            Multiplechart((string)Citylight.Content);

            this.Cursor = savecursor;

        }

        #endregion

        private void Happyshop_Unchecked(object sender, RoutedEventArgs e)

        {

        }

        private void btndetail_Click(object sender, RoutedEventArgs e)

        {

            chartgrid.Children.Clear();

        }

        private void ChartArea_ItemClick(object sender, ChartItemClickEventArgs e)

        {

            try

            {

                //MonthNameWiseCount_Result p = (e.DataPoint.DataItem as MonthNameWiseCount_Result);

            //string month = p.Adate;

                AllMonthdataGraph_Result p = (e.DataPoint.DataItem as AllMonthdataGraph_Result);

           

            Int32 month = (Int32)p.PMonth;

            Int32 year = (Int32)p.Pyear;

            Int32 id = (Int32)p.kid;

            var pravachan = proentity.GetPravachanInMonth(id, month,year).ToList();

            radchart2.SetValue(Grid.RowProperty, 2);

            radchart2.Height = 350;

            {

                if (sm2.SeriesDefinition == null)

                {

                    chartgrid.Children.Add(radchart2);

                    sm2.SeriesDefinition = new BarSeriesDefinition();

                    sm2.LegendLabel = "Pravachan" + "\n" + "Name";

                    sm2.ItemMappings.Add(new ItemMapping("PravachanName", DataPointMember.XCategory));

                    sm2.ItemMappings.Add(new ItemMapping("Pcount", DataPointMember.YValue));

                    radchart2.DefaultView.ChartArea.AxisX.LabelRotationAngle = 65;

                    radchart2.SeriesMappings.Add(sm2);

                }

            }

            //radChart1.Content = kname;

            this.radchart2.ItemsSource = pravachan;

    

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        private void Multiplechart(string sharvanthanName)

        {

            try

            {

                if (datePicker1.SelectedDate == null || datePicker2.SelectedDate == null)

                {

                    MessageBox.Show("Please Select First Date");

                }

                else

                {

                    WrapPanel wrap = new WrapPanel();

                    //gridname.IsBusy = true;

                    int sid = (from s in proentity.SharvanthanMasters

                               where s.SharvanthanName == sharvanthanName

                               select s.SharvanthanId).FirstOrDefault();

                    var data = (from s in proentity.KhojiMasters

                                where s.SharvanthanId == sid

                                select new

                                {

                                    s.KhojiId

                                });

                    chartgrid.Children.Add(wrap);

                    foreach (var id in data)

                    {

                        int kid = (Int32)id.KhojiId;

                        var chartdata = proentity.AllMonthdataGraph((DateTime)datePicker1.SelectedDate, (DateTime)datePicker2.SelectedDate, null, kid).ToList();

                        string kname = (from k in proentity.KhojiMasters where k.KhojiId == kid select k.KhojiName).FirstOrDefault();

                        Telerik.Windows.Controls.RadChart radchart1 = new Telerik.Windows.Controls.RadChart();

                        radchart1.HorizontalAlignment = HorizontalAlignment.Left;

                        radchart1.Margin = new Thickness(10, 10.076, 10, 7.01);

                        radchart1.FontFamily = new FontFamily("Comic Sans MS");

                        radchart1.Width = 321.07;

                        radchart1.Height = 250;

                        wrap.Children.Add(radchart1);

                        SeriesMapping sm = new SeriesMapping();

                        sm.SeriesDefinition = new SplineAreaSeriesDefinition();

                        sm.LegendLabel = "P";

                        sm.ItemMappings.Add(new ItemMapping("Monthyear", DataPointMember.XCategory));

                        sm.ItemMappings.Add(new ItemMapping("pcount", DataPointMember.YValue));

                        radchart1.DefaultView.ChartArea.AxisX.LabelRotationAngle = 90;

                        radchart1.SeriesMappings.Add(sm);

                        radchart1.Content = kname;

                        radchart1.ItemsSource = chartdata;

                        //Yearmonth

                        

                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

           

        }

    }

Store Procedure
----------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[AllMonthdataGraph]

      -- Add the parameters for the stored procedure here

     

 @fromdate datetime=null,

  @todate datetime=null,

  @startdate datetime=null,

  @khojiid int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

       

Create table #temp5

(

kid int,

Monthyear varchar(20),

PMonth int,

Pyear int,

pcount int)

set @startdate=@fromdate

while (@startdate<=@todate)

begin

declare @M int

 set @M=Month(@startdate)

declare @y int

set @y=Year(@startdate)

declare @mName varchar(20)

set @mName=Convert(varchar(3),Datename(Month,@startdate))+ '-' + Right(YEAR(@startdate)+0,2)

Insert into #temp5 values(@khojiid,@mName,@M,@y,0)

set @startdate= DATEADD(Month,1,@startdate)

End

declare @id int

declare @monthyear Varchar(20)

declare @Year int

Declare @Month int

declare @pcount int

--

declare UpdateCursor Cursor

For Select kid,PMonth,pyear,pcount from #temp5

Open UpdateCursor

Fetch Next from UpdateCursor Into @id,@month,@Year,@pcount

WHILE @@FETCH_STATUS = 0

BEGIN

select @id=khojiid ,@Year=year(attendencedate)

,@Month=month(attendencedate)

,@pcount=count(pravachanid)

 from Attendencedetail ad

left outer join Attendence a

on ad.attendenceid = a.attendenceid

where ad.khojiid=@khojiid And month(attendencedate)=@Month and year(attendencedate)=@Year

group by khojiid,year(attendencedate),month(attendencedate)

Update #temp5 Set pcount=@pcount Where pyear=@Year And PMonth=@Month

Fetch Next from UpdateCursor Into @id,@month,@Year,@pcount

End

CLOSE UpdateCursor

DEALLOCATE UpdateCursor

--insert into temp5

Select * from #temp5

 END

Drill Down StoreProcedure

ALTER PROCEDURE  [dbo].[PravachanInMonth]

      -- Add the parameters for the stored procedure here

     

       

        @khoji int=null,

        @month int=null,

        @year int=null

AS

BEGIN

       

      SET NOCOUNT ON;

 select ad.khojiid,MONTH(a.attendencedate) as m, Datename(MONTH,a.AttendenceDate) as Adate,count(*)as Pcount,pm.PravachanName  from Attendencedetail ad

Left outer join Attendence a on a.attendenceid=ad.attendenceid

left outer join PravachanMaster pm on a.pravachanid=pm.pravachanid

where ad.KhojiId=@khoji And MONTH(a.attendencedate)=@month And Year(a.attendencedate)=@year

group by ad.khojiid,datename(month,a.attendencedate),MONTH(a.attendencedate),pm.PravachanName

 order by  MONTH(a.attendencedate)

END