Dynamic tables with dynamic columns

18 posts, 0 answers
  1. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 23 Mar 2014 Link to this post

    Hi,

    I have a database table report. One of it's columns is query which can be any valid SQL statement which gives a single output after execution. It might even contain declare statements. Query also has some parameters for which values must be assigned at runtime. In all the rows the only thing that will be common is query parameters. Number of columns given by the query is not known at design time. Code decides which row from the table should be selected and gets value for query. How do I create a report to show result of such a query? One of the parameters in the report should be report_id.Will this be possible?
  2. Petio Petkov
    Admin
    Petio Petkov avatar
    825 posts

    Posted 26 Mar 2014 Link to this post

    Hello Soumya,

    You need to create your Report Definition programatically.
    The best approach to start with your task is to use test data and create a report definition by using the integrated Visual Studio Report Designer. The auto-generated code in the report's designer.cs(vb) will serve you as a template to create your own methods to build the report and bind it to data. 


    Regards,
    Petio Petkov
    Telerik
     

    Build cross-platform mobile apps using Visual Studio and .NET. Register for the online webinar on 03/27/2014, 11:00AM US ET.. Seats are limited.

     
  3. DevCraft banner
  4. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 16 Apr 2014 Link to this post

    Hello Petio Petkov,

    I do not see integrated report designer in webforms that I create. Could you please send me references or guide me on how I must go about? I googled a lot but could not understand what I must do.
  5. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 16 Apr 2014 Link to this post

    Hello Petio Petkov,

    After a lot of research, now I see integrated report designer in web project but I don't see it in website. How do I add programmatically created report to my website?
  6. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 18 Apr 2014 Link to this post

    Hi Soumya,

    Due to some Web Site project peculiarities, the report designer has limited functionality when the report definition is created in App_Code. This is why we have stopped providing the report item template for web site projects. In such cases we highly recommend the best practices solution structure. First to create a class library that contains your reports and then reference that class library in applications that view the reports. 

    You may find useful the ASP.NET Web Application Projects vs. Web Site Projects in Visual Studio and Converting a Web Site Project to a Web Application Project MSDN blogs. 

    Once the report definition is created you can display it in your web site/application as elaborated in the Web Application help articles.

    Regards,
    Peter
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  7. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 23 Apr 2014 in reply to Peter Link to this post

    Hi Peter,

    I created a cs file in App_Code folder in my asp.net website. Is there anyway to align and format table's column value based on column's datatype? Say column's datatype is decimal then value should be right aligned and should be in the format #,###.00. If column's datatype is datetime then value should be left align and format should be dd-MMM-yyyy hh:ss tt. Following is the cs code which is called to createreport dynamically.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using Telerik.Reporting;
    using Telerik.Web.UI;
    using Telerik.Reporting.Drawing;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.Text;
    using System.Collections;
    using System.Configuration;
    //using Microsoft.VisualBasic.Strings;

    /// <summary>
    /// Summary description for dynamicReport
    /// </summary>
    public class dynamicReport : Telerik.Reporting.Report
    {
        public static int report_id;
        public static bool HideBreakColumn;
        public static int BreakColumn = -1;
        public static string report_name = "DynamicReport";
        public static DateTime from, to;
        public static string sites;
        public static DateTime start_time, end_time;
        public static ParafaitUtils.Utilities Utilities;

        String connectionString = ConfigurationManager.ConnectionStrings["ParafaitConnectionString"].ConnectionString;
        public dynamicReport(string reportID, string name)
    {
    //
    // TODO: Add constructor logic here
    //
            report_id = Convert.ToInt32(reportID);
            report_name = name;
            InitializeComponent();
    }
        public void InitializeComponent()
        {
            Telerik.Reporting.TableGroup tableGroup5 = new Telerik.Reporting.TableGroup();
            Telerik.Reporting.TableGroup tableGroup6 = new Telerik.Reporting.TableGroup();
            Telerik.Reporting.TableGroup tableGroup7 = new Telerik.Reporting.TableGroup();
            Telerik.Reporting.TableGroup tableGroup8 = new Telerik.Reporting.TableGroup();
            Telerik.Reporting.Drawing.FormattingRule formattingRule2 = new Telerik.Reporting.Drawing.FormattingRule();
            Telerik.Reporting.Drawing.FormattingRule formattingRule1 = new Telerik.Reporting.Drawing.FormattingRule();
            Telerik.Reporting.Drawing.StyleRule styleRule1 = new Telerik.Reporting.Drawing.StyleRule();
            this.dsAccessibleSites = new Telerik.Reporting.SqlDataSource();

            // 
            // dsAccessibleSites
            // 
            this.dsAccessibleSites.ConnectionString = ParafaitCommon.changeConnectionString(connectionString, ParafaitCommon.getDbName());
            this.dsAccessibleSites.Name = "dsAccessibleSites";
            this.dsAccessibleSites.CommandTimeout = 600;
            this.dsAccessibleSites.Parameters.AddRange(new Telerik.Reporting.SqlDataSourceParameter[] {
                new Telerik.Reporting.SqlDataSourceParameter("@isCorporate", System.Data.DbType.String, "=Parameters.isCorporate.Value"),
                new Telerik.Reporting.SqlDataSourceParameter("@loggedInUserId", System.Data.DbType.String, "=Parameters.loggedInUserId.Value")});
            this.dsAccessibleSites.ProviderName = "System.Data.SqlClient";
            this.dsAccessibleSites.SelectCommand = @"IF (@isCorporate = 'Y')
                                                   BEGIN
                                                   if(select top 1 FunctionId as functionId from managementformaccess where FunctionGroup = 'Data Access' 
                                                   and access_allowed = 'Y' and main_menu = 'Sites'
                                                   and role_id = (select role_id from users where user_id = @loggedInUserId)) is not null
                                                   Select site_name as SiteName, convert(varchar, site_id) as Id from site where site_id IN (select FunctionId as functionId from managementformaccess where FunctionGroup = 'Data Access' 
                                                   and access_allowed = 'Y' and main_menu = 'Sites'
                                                   and role_id = (select role_id from users where user_id = @loggedInUserId))
                                                   ELSE
                                                   Select 'All' as SiteName, '-1' as Id
                                                   END
                                                    ELSE
                                                   Select 'All' as SiteName, '-1' as Id";

            user = new Telerik.Reporting.ReportParameter();
            user.Name = "user";
            user.Text = "User";
            user.Type = ReportParameterType.String;
            user.AllowBlank = false;
            user.AllowNull = true;
            user.Visible = false;
            user.Value = ParafaitCommon.getUserName();
            this.ReportParameters.Add(user);

            fromdate = new Telerik.Reporting.ReportParameter();
            fromdate.Name = "fromdate";
            fromdate.Text = "From Date";
            fromdate.Type = Telerik.Reporting.ReportParameterType.DateTime;
            fromdate.AllowBlank = false;
            fromdate.AllowNull = false;
            fromdate.Visible = true;
            fromdate.Value = Convert.ToDateTime(ParafaitTimezone.getLocalTime(DateTime.Now.AddDays(-1)).ToString("yyyy/MM/dd") + " 06:00:00 AM");
            this.ReportParameters.Add(fromdate);

            todate = new Telerik.Reporting.ReportParameter();
            todate.Name = "todate";
            todate.Text = "To Date";
            todate.Type = Telerik.Reporting.ReportParameterType.DateTime;
            todate.AllowBlank = false;
            todate.AllowNull = false;
            todate.Visible = true;
            todate.Value = Convert.ToDateTime(ParafaitTimezone.getLocalTime(DateTime.Now).ToString("yyyy/MM/dd") + " 06:00:00 AM");
            this.ReportParameters.Add(todate);

            offset = new Telerik.Reporting.ReportParameter();
            offset.Name = "offset";
            offset.Text = "offset";
            offset.Type = Telerik.Reporting.ReportParameterType.Integer;
            offset.AllowBlank = false;
            offset.AllowNull = false;
            offset.Value = ParafaitTimezone.FinalOffset();
            offset.Visible = false;
            this.ReportParameters.Add(offset);

            loggedInUserId = new Telerik.Reporting.ReportParameter();
            loggedInUserId.Name = "loggedInUserId";
            loggedInUserId.Text = "loggedInUser Id";
            loggedInUserId.Type = ReportParameterType.String;
            loggedInUserId.AllowBlank = false;
            loggedInUserId.AllowNull = false;
            loggedInUserId.Visible = false;
            loggedInUserId.Value = ParafaitCommon.getUserId();
            this.ReportParameters.Add(loggedInUserId);

            isCorporate = new Telerik.Reporting.ReportParameter();
            isCorporate.Name = "isCorporate";
            isCorporate.Text = "isCorporate";
            isCorporate.Type = ReportParameterType.String;
            isCorporate.AllowBlank = false;
            isCorporate.AllowNull = false;
            isCorporate.Visible = false;
            if (ParafaitCommon.checkIsCorporate())
                isCorporate.Value =  "Y";
            else
                isCorporate.Value = "N";
            this.ReportParameters.Add(isCorporate);

            SiteId = new ReportParameter();
            SiteId.Name = "SiteId";
            SiteId.Text = "SiteId";
            SiteId.Type = ReportParameterType.String;
            SiteId.AllowBlank = false;
            SiteId.AllowNull = false;
            SiteId.Visible = true;
            SiteId.AvailableValues.DataSource = dsAccessibleSites;
            SiteId.AvailableValues.DisplayMember = "= Fields.SiteName";
            SiteId.AvailableValues.ValueMember = "= Fields.Id";
            SiteId.MultiValue = true;
            SiteId.Value = "= AllValues(Fields.Id)";
            this.ReportParameters.Add(SiteId);

            site = new ReportParameter();
            site.Name = "site";
            site.Text = "site";
            site.Type = ReportParameterType.String;
            site.AllowBlank = false;
            site.AllowNull = false;
            site.Visible = false;
            site.MultiValue = false;
            site.Value = "= Join(\",\", Parameters.SiteId.Value)";
            this.ReportParameters.Add(site);        
            

            this.ItemDataBinding += new System.EventHandler(this.report_ItemDataBinding);
            this.ItemDataBound += new System.EventHandler(this.report_ItemDataBound);
            this.textBox1 = new Telerik.Reporting.TextBox();
            this.textBox3 = new Telerik.Reporting.TextBox();
            this.textBox5 = new Telerik.Reporting.TextBox();
            this.pageHeaderSection1 = new Telerik.Reporting.PageHeaderSection();
            this.detail = new Telerik.Reporting.DetailSection();
            this.table1 = new Telerik.Reporting.Table();
            this.textBox2 = new Telerik.Reporting.TextBox();
            this.textBox4 = new Telerik.Reporting.TextBox();
            this.textBox6 = new Telerik.Reporting.TextBox();
            this.pageFooterSection1 = new Telerik.Reporting.PageFooterSection();
            this.dsQuery = new Telerik.Reporting.SqlDataSource();
            
            this.textBox7 = new Telerik.Reporting.TextBox();
            this.textBox8 = new Telerik.Reporting.TextBox();
            this.textBox9 = new Telerik.Reporting.TextBox();
            ((System.ComponentModel.ISupportInitialize)(this)).BeginInit();
            // 
            // textBox1
            // 
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.2604166567325592D));
            this.textBox1.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(113)))), ((int)(((byte)(140)))), ((int)(((byte)(161)))));
            this.textBox1.Style.Font.Bold = true;
            this.textBox1.Style.Font.Name = "Microsoft Tai Le";
            this.textBox1.Style.Font.Size = Telerik.Reporting.Drawing.Unit.Point(9D);
            // 
            // textBox3
            // 
            this.textBox3.Name = "textBox3";
            this.textBox3.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.2604166567325592D));
            this.textBox3.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(113)))), ((int)(((byte)(140)))), ((int)(((byte)(161)))));
            this.textBox3.Style.Font.Bold = true;
            this.textBox3.Style.Font.Name = "Microsoft Tai Le";
            this.textBox3.Style.Font.Size = Telerik.Reporting.Drawing.Unit.Point(9D);
            // 
            // textBox5
            // 
            this.textBox5.Name = "textBox5";
            this.textBox5.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.2604166567325592D));
            this.textBox5.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(113)))), ((int)(((byte)(140)))), ((int)(((byte)(161)))));
            this.textBox5.Style.Font.Bold = true;
            this.textBox5.Style.Font.Name = "Microsoft Tai Le";
            this.textBox5.Style.Font.Size = Telerik.Reporting.Drawing.Unit.Point(9D);
            // 
            // panel1
            // 
            //this.panel1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
            //    this.textBox48,
            //    this.textBox77,
            //    this.textBox78,
            //    this.textBox79,
            //    this.table3,
            //    this.textBox47});
            //this.panel1.Location = new Telerik.Reporting.Drawing.PointU(Telerik.Reporting.Drawing.Unit.Inch(0D), Telerik.Reporting.Drawing.Unit.Inch(1.3000001907348633D));
            //this.panel1.Name = "panel1";
            //this.panel1.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(7.30000114440918D), Telerik.Reporting.Drawing.Unit.Inch(1.4000000953674316D));
            //this.panel1.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid;
            //this.panel1.Style.Font.Name = "Microsoft Tai Le";
            //this.panel1.Style.TextAlign = Telerik.Reporting.Drawing.HorizontalAlign.Left;
            // 
            // pageHeaderSection1
            // 
            this.pageHeaderSection1.Height = Telerik.Reporting.Drawing.Unit.Inch(0.7000001072883606D);
            this.pageHeaderSection1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
                this.textBox7});
            this.pageHeaderSection1.Name = "pageHeaderSection1";
            // 
            // detail
            // 
            this.detail.Height = Telerik.Reporting.Drawing.Unit.Inch(2D);
            this.detail.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
                this.table1});
            this.detail.Name = "detail";
            //this.detail.ItemDataBinding += new System.EventHandler(this.detail_ItemDataBinding);
            // 
            // table1
            // 
            this.table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Telerik.Reporting.Drawing.Unit.Inch(1D)));
            this.table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Telerik.Reporting.Drawing.Unit.Inch(1D)));
            this.table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Telerik.Reporting.Drawing.Unit.Inch(1D)));
            this.table1.Body.Rows.Add(new Telerik.Reporting.TableBodyRow(Telerik.Reporting.Drawing.Unit.Inch(0.24999989569187164D)));
            this.table1.Body.SetCellContent(0, 0, this.textBox2);
            this.table1.Body.SetCellContent(0, 1, this.textBox4);
            this.table1.Body.SetCellContent(0, 2, this.textBox6);
            tableGroup5.Name = "tableGroup";
            tableGroup5.ReportItem = this.textBox1;
            tableGroup6.Name = "tableGroup1";
            tableGroup6.ReportItem = this.textBox3;
            tableGroup7.Name = "tableGroup2";
            tableGroup7.ReportItem = this.textBox5;
            this.table1.ColumnGroups.Add(tableGroup5);
            this.table1.ColumnGroups.Add(tableGroup6);
            this.table1.ColumnGroups.Add(tableGroup7);
            this.table1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
                this.textBox2,
                this.textBox4,
                this.textBox6,
                this.textBox1,
                this.textBox3,
                this.textBox5});
            this.table1.Location = new Telerik.Reporting.Drawing.PointU(Telerik.Reporting.Drawing.Unit.Inch(0D), Telerik.Reporting.Drawing.Unit.Inch(0.19999997317790985D));
            this.table1.Name = "table1";
            tableGroup8.Groupings.Add(new Telerik.Reporting.Grouping(null));
            tableGroup8.Name = "detailTableGroup";
            this.table1.RowGroups.Add(tableGroup8);
            this.table1.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(3D), Telerik.Reporting.Drawing.Unit.Inch(0.510416567325592D));
            this.table1.ItemDataBinding += new System.EventHandler(this.table1_ItemDataBinding);
            // 
            // textBox2
            // 
            formattingRule2.Filters.Add(new Telerik.Reporting.Filter("= RowNumber()%2", Telerik.Reporting.FilterOperator.Equal, "1"));
            formattingRule2.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(218)))), ((int)(((byte)(226)))), ((int)(((byte)(232)))));
            this.textBox2.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] {
                formattingRule2});
            this.textBox2.Name = "textBox2";
            this.textBox2.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.24999989569187164D));
            // 
            // textBox4
            // 
            //this.textBox4.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] {formattingRule1});
            this.textBox4.Name = "textBox4";
            this.textBox4.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.24999989569187164D));
            // 
            // textBox6
            // 
            //this.textBox6.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] { formattingRule1});
            this.textBox6.Name = "textBox6";
            this.textBox6.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.24999989569187164D));
            // 
            // pageFooterSection1
            // 
            this.pageFooterSection1.Height = Telerik.Reporting.Drawing.Unit.Inch(0.5D);
            this.pageFooterSection1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
                this.textBox8,
                this.textBox9});
            this.pageFooterSection1.Name = "pageFooterSection1";
            // 
            // dsQuery
            // 
            this.dsQuery.ConnectionString = "Data Source=.\\sqlexpress;Initial Catalog=Parafait;Integrated Security=True";
            this.dsQuery.Name = "dsQuery";
            this.dsQuery.CommandTimeout = 600;
            this.dsQuery.Parameters.AddRange(new Telerik.Reporting.SqlDataSourceParameter[] {
                new Telerik.Reporting.SqlDataSourceParameter("@reportid", System.Data.DbType.Int32, "=Parameters.reportid.Value")});
            this.dsQuery.ProviderName = "System.Data.SqlClient";
            this.dsQuery.SelectCommand = "select * from reports where report_id = @reportid";
            // 
            // textBox7
            // 
            this.textBox7.Location = new Telerik.Reporting.Drawing.PointU(Telerik.Reporting.Drawing.Unit.Inch(0D), Telerik.Reporting.Drawing.Unit.Inch(0D));
            this.textBox7.Name = "textBox7";
            this.textBox7.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(6.2999610900878906D), Telerik.Reporting.Drawing.Unit.Inch(0.69996076822280884D));
            this.textBox7.Style.Font.Bold = true;
            this.textBox7.Style.Font.Name = "Microsoft Tai Le";
            this.textBox7.Style.Font.Size = Telerik.Reporting.Drawing.Unit.Point(12D);
            this.textBox7.Style.TextAlign = Telerik.Reporting.Drawing.HorizontalAlign.Center;
            this.textBox7.Style.VerticalAlign = Telerik.Reporting.Drawing.VerticalAlign.Middle;
            this.textBox7.Value = "";
            // 
            // textBox8
            // 
            this.textBox8.Format = "{0:F}";
            this.textBox8.Location = new Telerik.Reporting.Drawing.PointU(Telerik.Reporting.Drawing.Unit.Inch(0D), Telerik.Reporting.Drawing.Unit.Inch(0.1000000610947609D));
            this.textBox8.Name = "textBox8";
            this.textBox8.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1.9000000953674316D), Telerik.Reporting.Drawing.Unit.Inch(0.20000012218952179D));
            this.textBox8.Value = "= ExecutionTime";
            // 
            // textBox9
            // 
            this.textBox9.Location = new Telerik.Reporting.Drawing.PointU(Telerik.Reporting.Drawing.Unit.Inch(3.9000003337860107D), Telerik.Reporting.Drawing.Unit.Inch(0.1000000610947609D));
            this.textBox9.Name = "textBox9";
            this.textBox9.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(2.4000003337860107D), Telerik.Reporting.Drawing.Unit.Inch(0.20000012218952179D));
            this.textBox9.Value = "=\'Page \' + PageNumber + \' of \' + PageCount";
            // 
            // TestReport
            // 
            this.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
                this.pageHeaderSection1,
                this.detail,
                this.pageFooterSection1});
            this.Name = report_name;
            //this.PageSettings.Margins = new Telerik.Reporting.Drawing.MarginsU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(1D));
            //this.PageSettings.PaperKind = System.Drawing.Printing.PaperKind.Letter;

            //set report page setting
            this.PageSettings.Landscape = true;
            this.PageSettings.PaperKind = System.Drawing.Printing.PaperKind.A3;
            //set report width
            Telerik.Reporting.Drawing.Unit w;
            if (this.PageSettings.Landscape)
                w = this.PageSettings.PaperSize.Height - this.PageSettings.Margins.Top - this.PageSettings.Margins.Bottom;
            else
                w = this.PageSettings.PaperSize.Width - this.PageSettings.Margins.Left - this.PageSettings.Margins.Right;
            this.Width = w;
            
            this.Style.BackgroundColor = System.Drawing.Color.White;
            styleRule1.Selectors.AddRange(new Telerik.Reporting.Drawing.ISelector[] {
                new Telerik.Reporting.Drawing.TypeSelector(typeof(Telerik.Reporting.TextItemBase)),
                new Telerik.Reporting.Drawing.TypeSelector(typeof(Telerik.Reporting.HtmlTextBox))});
            styleRule1.Style.Padding.Left = Telerik.Reporting.Drawing.Unit.Point(2D);
            styleRule1.Style.Padding.Right = Telerik.Reporting.Drawing.Unit.Point(2D);
            this.StyleSheet.AddRange(new Telerik.Reporting.Drawing.StyleRule[] {
                styleRule1});
            this.Width = Telerik.Reporting.Drawing.Unit.Inch(6.3000006675720215D);
            ((System.ComponentModel.ISupportInitialize)(this)).EndInit();
        }

        public Telerik.Reporting.PageHeaderSection pageHeaderSection1;
        public Telerik.Reporting.DetailSection detail;
        public Telerik.Reporting.PageFooterSection pageFooterSection1;
        public Telerik.Reporting.SqlDataSource dsQuery;
        public Telerik.Reporting.ReportParameter fromdate;
        public Telerik.Reporting.ReportParameter todate;
        public Telerik.Reporting.ReportParameter offset;
        public Telerik.Reporting.ReportParameter user;
        public Telerik.Reporting.ReportParameter loggedInUserId;
        public Telerik.Reporting.ReportParameter isCorporate;
        public Telerik.Reporting.ReportParameter SiteId;
        public Telerik.Reporting.ReportParameter site;
        public Telerik.Reporting.Table table1;
        public Telerik.Reporting.TextBox textBox2;
        public Telerik.Reporting.TextBox textBox4;
        public Telerik.Reporting.TextBox textBox6;
        public Telerik.Reporting.TextBox textBox1;
        public Telerik.Reporting.TextBox textBox3;
        public Telerik.Reporting.TextBox textBox5;
        //private Telerik.Reporting.Panel panel1;
        //private Telerik.Reporting.TextBox textBox48;
        //private Telerik.Reporting.TextBox textBox77;
        //private Telerik.Reporting.TextBox textBox78;
        //private Telerik.Reporting.TextBox textBox79;
        //private Telerik.Reporting.Table table3;
        //private Telerik.Reporting.TextBox textBox83;
        //private Telerik.Reporting.TextBox textBox82;
        //private Telerik.Reporting.SqlDataSource siteNameSelection;
        //private Telerik.Reporting.TextBox textBox47;
        public Telerik.Reporting.TextBox textBox7;
        public Telerik.Reporting.TextBox textBox8;
        public Telerik.Reporting.TextBox textBox9;
        private Telerik.Reporting.SqlDataSource dsAccessibleSites;

        public void report_ItemDataBound(object sender, EventArgs e)
        {
            start_time = DateTime.Now;
            Telerik.Reporting.Processing.Report rpt = (Telerik.Reporting.Processing.Report)sender;
            var obj = rpt.Parameters;

            string s = "";//list of parameters in the running report
            int cnt = 0;
            foreach (var v in obj)
            {
                cnt += 1;
                if (v.Value.AvailableValues == null)
                    s += "&" + v.Key + "=" + v.Value.Value;
                if (v.Value.AvailableValues != null)
                {
                    s += "&" + v.Key + "=" + v.Value.Value;
                    for (int i = 0; i < v.Value.AvailableValues.Count; i++)
                        s += "&" + v.Key + "[" + i + "]=" + v.Value.Value;
                }
            }
            s = s.Substring(1);
            end_time = DateTime.Now;

            addReportAudit(report_id, start_time, end_time, s, ParafaitCommon.getUserName()); 
        }

        void addReportAudit(int reportID, DateTime start_time, DateTime end_time, string param_list, string username)
        {
            SqlConnection conn = new SqlConnection(ParafaitCommon.changeConnectionString(connectionString, ParafaitCommon.getDbName()));
            SqlCommand cmd = new SqlCommand();
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = @"INSERT INTO [dbo].[run_report_audit] ([report_id],[start_time],[end_time],[parameter_list], creation_date, created_by, last_update_date, last_updated_by) 
                               VALUES (@report_id,@start_time,@end_time,@parameter_list, getdate(), @user, getdate(), @user)";
            cmd.Parameters.AddWithValue("@report_id", reportID);
            cmd.Parameters.AddWithValue("@start_time", start_time);
            cmd.Parameters.AddWithValue("@end_time", end_time);
            cmd.Parameters.AddWithValue("@parameter_list", param_list);
            cmd.Parameters.AddWithValue("@user", username);

            cmd.ExecuteNonQuery();
        }

        public void report_ItemDataBinding(object sender, EventArgs e)
        {
            string header = "";
            Telerik.Reporting.Processing.Report rpt = (Telerik.Reporting.Processing.Report)sender;
            header = "=\"" + report_name
                     + " \" +\r\n \"From " + Convert.ToDateTime(rpt.Parameters["fromdate"].Value).ToString("dd-MMM-yyyy hh:mm tt") + " To " + Convert.ToDateTime(rpt.Parameters["todate"].Value).ToString("dd-MMM-yyyy hh:mm tt") 
                     + "\" +\r\n \" Header \"";
                //report_name + 
                //     " From " + Convert.ToDateTime(rpt.Parameters["fromdate"].Value).ToString("dd-MMM-yyyy hh:mm tt") +
                //     " To " + Convert.ToDateTime(rpt.Parameters["todate"].Value).ToString("dd-MMM-yyyy hh:mm tt");
            textBox7.Value = header;
            from = ParafaitTimezone.getServerTime(Convert.ToDateTime(rpt.Parameters["fromdate"].Value));
            to = ParafaitTimezone.getServerTime(Convert.ToDateTime(rpt.Parameters["todate"].Value));
            sites = rpt.Parameters["site"].Value.ToString();
        }

        public void table1_ItemDataBinding(object sender, EventArgs e)
        {
            connectionString = ParafaitCommon.changeConnectionString(connectionString, ParafaitCommon.getDbName());

            //get the processing table object since we're in the context of event
            Telerik.Reporting.Processing.Table processingTable = (sender as Telerik.Reporting.Processing.Table);
            //textBox7.Value = report_name;
            //construct the select statement based on the selected report parameters
            string selectString = dsQuery.SelectCommand;
            //bool IsCorporate = false;
            DateTime fromdate = DateTime.Now.AddYears(-1);
            DateTime todate = DateTime.Now;
            //string selectString = "SELECT " + FormatArray((object[])processingTable.Report.Parameters["TableColumns"].Value) + " FROM " + processingTable.Report.Parameters["TableName"].Label;
            //SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectString, connectionString);
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            cmd.Connection = connection;
            cmd.CommandText = selectString;
            if (selectString.ToLower().Contains("@reportid"))
                cmd.Parameters.AddWithValue("@reportid", report_id);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            da.Fill(dt);

            selectString = dt.Rows[0]["dbquery"].ToString();
            if (dt.Rows[0]["HidebreakColumn"].ToString() == "Y")
                HideBreakColumn = true;
            else
                HideBreakColumn = false;
            if (dt.Rows[0]["BreakColumn"] != DBNull.Value)
                BreakColumn = Convert.ToInt32(dt.Rows[0]["BreakColumn"]); 

            report_name = dt.Rows[0]["report_name"].ToString();
            cmd.CommandText = selectString;
            cmd.CommandTimeout = 600;

            dt = new DataTable();

            if (selectString.ToLower().Contains("@fromdate"))
            {
                cmd.Parameters.AddWithValue("@FromDate", from);
            }

            if (selectString.ToLower().Contains("@todate"))
                cmd.Parameters.AddWithValue("@ToDate", to);

            if (isCorporate.Value == "Y")
                cmd.CommandText = System.Text.RegularExpressions.Regex.Replace(cmd.CommandText, "[@][Ss][Ii][Tt][Ee][Ii][Dd]", "(" + sites + ")");
            else
                cmd.CommandText = System.Text.RegularExpressions.Regex.Replace(cmd.CommandText, "[@][Ss][Ii][Tt][Ee][Ii][Dd]", "(-1)");

            SqlDataAdapter da1 = new SqlDataAdapter(cmd);
            da1.Fill(dt);

            //int NumericColumnCount = 0;
            //int[] Columns = new int[50];
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    if (dt.Columns[i].DataType.Name != "String" && dt.Columns[i].DataType.Name != "DateTime" && !dt.Columns[i].ColumnName.EndsWith("id", StringComparison.CurrentCultureIgnoreCase))
            //    {
            //        Columns[NumericColumnCount++] = i;
            //    }
            //}

            processingTable.DataSource = dt;

            //create two HtmlTextBox items (one for header and one for data) which would be added to the items collection of the table
            Telerik.Reporting.HtmlTextBox textboxGroup;
            Telerik.Reporting.HtmlTextBox textBoxTable;

            //we do not clear the Rows collection, since we have a details row group and need to create columns only
            this.table1.ColumnGroups.Clear();
            this.table1.Body.Columns.Clear();
            this.table1.Body.Rows.Clear();
            int i = 0;
            this.table1.ColumnHeadersPrintOnEveryPage = true;
            foreach (DataColumn dc in dt.Columns)
            {
                Telerik.Reporting.TableGroup tableGroupColumn = new Telerik.Reporting.TableGroup();
                tableGroupColumn.Name = i.ToString();
                
                this.table1.ColumnGroups.Add(tableGroupColumn);
                this.table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Unit.Inch(1)));
                
                if (HideBreakColumn && i + 1 == BreakColumn)
                    tableGroupColumn.Visible = false;
                textboxGroup = new Telerik.Reporting.HtmlTextBox();
                textboxGroup.Style.BorderColor.Default = System.Drawing.Color.Black;
                textboxGroup.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(113)))), ((int)(((byte)(140)))), ((int)(((byte)(161)))));
                textboxGroup.Style.Font.Bold = true;
                textboxGroup.Style.Color = System.Drawing.Color.White;
                textboxGroup.Style.BorderStyle.Default = BorderType.Solid;
                //textboxGroup.Value = Microsoft.VisualBasic.Strings.StrConv(dc.ColumnName, Microsoft.VisualBasic.VbStrConv.vbProperCase); //dc.ColumnName.ToString().Replace("_", " ");
                //textboxGroup.Value = dc.ColumnName.ToString().Replace("_", " ");
                string heading = dc.ColumnName.ToString().Replace("_", " ");
                textboxGroup.Value = char.ToUpper(heading[0]) + heading.Substring(1);
                textboxGroup.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
                tableGroupColumn.ReportItem = textboxGroup;

                textBoxTable = new Telerik.Reporting.HtmlTextBox();
                textBoxTable.Style.BorderColor.Default = System.Drawing.Color.Black;
                textBoxTable.Style.BorderStyle.Default = BorderType.Solid;
                textBoxTable.Value = "=Fields.[" + dc.ColumnName + "]";

                Telerik.Reporting.Drawing.FormattingRule formattingRule2 = new Telerik.Reporting.Drawing.FormattingRule();
                formattingRule2.Filters.Add(new Telerik.Reporting.Filter("= RowNumber()%2", Telerik.Reporting.FilterOperator.Equal, "1"));
                formattingRule2.Style.BackgroundColor = System.Drawing.Color.FromArgb(((int)(((byte)(218)))), ((int)(((byte)(226)))), ((int)(((byte)(232)))));
                textBoxTable.ConditionalFormatting.AddRange(new Telerik.Reporting.Drawing.FormattingRule[] {
                formattingRule2});
                textBoxTable.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
                this.table1.Body.SetCellContent(0, i++, textBoxTable);
                this.table1.Items.AddRange(new ReportItemBase[] { textBoxTable, textboxGroup });
            }
        }
    }
  8. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 24 Apr 2014 in reply to Soumya Link to this post

    Hi,

    I have done a few changes to the code in my previous post so that the datasource to the table has an additional row which shows sum of columns which are of numeric type. In addition to query in my previous post is there anyway I could capture last row in the table and apply some style to it?
  9. Hinata
    Hinata avatar
    146 posts
    Member since:
    Dec 2013

    Posted 28 Apr 2014 in reply to Soumya Link to this post

    Hi Soumya,

    You can use Conditional Formatting and User Functions in order to change the format of the fields based on their type.
    Another approach is to determine what is the field data type before generating the table and then set the desired styles manually.

    In Telerik Reporting there is the Last() aggregate function which returns the last value from a specified expression.

    I hope this helps you.
  10. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 30 Apr 2014 in reply to Hinata Link to this post

    Hi Hardik,

    Is there any piece of code or reference that you could post?
  11. Hinata
    Hinata avatar
    146 posts
    Member since:
    Dec 2013

    Posted 05 May 2014 in reply to Soumya Link to this post

    Hi Soumya,

    The user function should check the data type of the field. For more information check this: http://stackoverflow.com/questions/11634079/c-sharp-how-to-get-variable-data-type.

    The Conditional Formatting and Aggregate Functions are explained in the reporting online documentation:
    http://www.telerik.com/help/reporting/styling-conditional-formatting.html
    http://www.telerik.com/help/reporting/expressions-aggregate-functions.html
  12. kelvin
    kelvin avatar
    1 posts
    Member since:
    May 2014

    Posted 05 May 2014 Link to this post

    Hi All,

    I have being having trouble generating dynamic columns using entity framework,  Attached is a sample picture of what i am trying to achieve and i have tried all i can but it seems to be given me headache. The grades and GPA appearing are all computed, this means i have to get some values from the db then carry out some computation before presenting the final result. Please any useful idea as to achieving this would really be appreciated.
  13. Nasko
    Admin
    Nasko avatar
    909 posts

    Posted 08 May 2014 Link to this post

    Hello Kelvin,

    Calculated fields can be used in Telerik Reporting to create new values that do not exist in the data source. For example, a calculated field can represent a new value, a constant, an expression value, user defined function or even a report parameter, or an existing field that is converted to a different datatype.
    You specify a calculated field manually by creating an expression.
    The Edit Calculated Fields dialog is available in the property grid of the EntityDataSource Component. For more information, please check the Calculated Fields help article.

    If you need to perform more complex operations to modify the data we can suggest using the ObjectDataSource Component which can be bound to a custom business object and provides data retrieval capabilities.

    Regards,
    Nasko
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  14. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 09 May 2014 in reply to Hinata Link to this post

    Hi,

    I have been able to align value in column based on it's type. I could not apply conditional formatting nor binding to get number or date formatting based on column's value. I added the following binding.

    Telerik.Reporting.HtmlTextBox textBoxTable;
    ......
    ...
    ...
    textBoxTable.Bindings.Add(new Telerik.Reporting.Binding("Format", "=iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%int%\", \"{0:d}\", iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%decimal%\" or ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%double%\", \"{0:f}\", iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%date%\", \"{0:dd-MMM-yyyy}\", \"\")))"));

    I got error "An error has occurred while processing HtmlTextBox '': Bindings error - Property 'Format' does not exist."
    Could anybody please send me a sample formatting rule or bindings to get my code working. I have a variable 'type' which returns the type of the column.


  15. Nasko
    Admin
    Nasko avatar
    909 posts

    Posted 13 May 2014 Link to this post

    Hello Soumya,

    The HtmlTextBox does not have a Format property, and trying to add such binding results in the described error message.
    You can add a binding for the Format property of the TextBox item instead.

    Regards,
    Nasko
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  16. Umesh
    Umesh avatar
    7 posts
    Member since:
    May 2014

    Posted 23 May 2014 in reply to Nasko Link to this post

    Hi,

    Could you please post a piece of code explaining how a binding for Format property of Textbox item can be added based on datatype of the column. I tried the following 

    Telerik.Reporting.TextBox textBoxTable;
    Telerik.Reporting.Binding bind = new Binding("Format",  "=iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%int%\", \"{0:d}\", iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%decimal%\" or ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%double%\", \"{0:f}\", iif(ToLower(CStr(Fields.[" + dc.ColumnName + "].GetType())) like \"%date%\", \"{0:dd-MMM-yyyy}\", \"\")))");
    textBoxTable.Bindings.Add(bind);

    The above code showed no effect. 

  17. Nasko
    Admin
    Nasko avatar
    909 posts

    Posted 27 May 2014 Link to this post

    Hello Umesh,

    First you need to create a User Function similar to this one:
    public static string GetFormatBasedOnType(object field)
    {
        string format = string.Empty;
     
        if (field.GetType() == typeof(DateTime))
        {
            format = "{0:dd-MMM-yy}";
        }
     
        return format;
    }

    Then use the user function in the following binding for the TextBox item:

    PropertyPath: Format
    Expression:​ = GetFormatBasedOnType(Fields.ColumnName)

    You can modify this approach in order to fit your scenario.

    Regards,
    Nasko
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  18. saravana
    saravana avatar
    12 posts
    Member since:
    Oct 2016

    Posted 03 Oct in reply to Soumya Link to this post

    Hi Friends, 
    I need to create a telerik report table with dynamically with grouping and subtotal under the table. I succeed but the achive the dynamic table with dynamic coloumns and also in grouping. but grouping came as this format only. How i bring my desired output and how to add subtotal under each groupings. pls help
  19. Stef
    Admin
    Stef avatar
    3050 posts

    Posted 04 Oct Link to this post

    Hi Saravana,

    Please check my post in your forum thread on the same question - Create Dynamic Table with grouping and subtotal .
    We will appreciate it to continue the discussion in the above linked thread in order to keep a better track on the exchanged information.

    Thank you for your understanding.

    Regards,
    Stef
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
DevCraft banner