C1Report : Selecting Fields at Run Time

C1Reports have not just made reporting easy; the customizations and features it offers have also added it in the list of user-friendly controls. Generally, the reports are designed in the designer and then either the complete data is shown or the filtered records are fetched on the basis of the parameters passed by the users. What if the designing of the reports could also be done as per the user's discretion? That is, the users can decide which field to add in the report and which one to leave out. This blog deals with the implementation of the same. In order to allow the user to choose the fields that are to be added in the report, please note the following:

1. The report should be created at runtime.
2. The list of all the columns present in the data source of the report should be available at the time of report designing.

In our implementation we have used a couple of reports bound to different data sources. When the user selects any report the list of columns from the data source is shown in the listbox. The user can select which fields he wants to render on the report and add them in the second listbox. Now, when the choices have been made, the selected columns are queried using the SQL query and set as the RecordSource of the report. The following is the code that is used to create the desired report:

 C1Report1.DataSource.RecordSource = ""  
 Dim field_count As Integer = C1Report1.Fields.Count  
 For c = 0 To field_count - 1  
    C1Report1.Fields.RemoveAt(c)  
 Next  
 Dim col As String = ""  
 Dim items = (From i In ListBox2.Items).ToArray()  

 'Get the list of all selected columns  
 For i = 0 To ListBox2.Items.Count - 1  
    If ListBox2.Items.Count > 1 Then  
       If i < ListBox2.Items.Count - 1 Then  
           col = col & items(i).ToString & " , "  
       Else  
           col = col & items(i).ToString  
       End If  
    Else  
       col = items(i).ToString  
    End If  
 Next  

 'Setting the recordsource on the basis of the selected column names  
 C1Report1.DataSource.RecordSource = "select " & col & " from " & dt.TableName  

 C1Report1.Layout.Orientation = OrientationEnum.Portrait  

 Dim f As Field  
 With C1Report1.Sections(SectionTypeEnum.Header)  
    .Height = 1440  
    .Visible = True  
    .BackColor = Color.FromArgb(200, 200, 200)  
    f = .Fields.Add("FldTitle", "New Report", 0, 0, 8000, 1440)  
    f.Font.Size = 24  
    f.Font.Bold = True  
    f.ForeColor = Color.FromArgb(0, 0, 100)  
 End With  

 l = 0  
 With C1Report1.Sections(SectionTypeEnum.PageHeader)  
    .Height = 300  
    .Visible = True  
    .CanGrow = False  
    C1Report1.Font.Bold = True  

    For i As Integer = 0 To ListBox2.Items.Count - 1  
       If i = 0 Then  
          f = .Fields.Add(ListBox2.Items(i).ToString, ListBox2.Items(i).ToString, 0, t, w, h)  
       Else  
          l = l + 1500  
          f = .Fields.Add(ListBox2.Items(i).ToString, ListBox2.Items(i).ToString, l, t, w, h)  
       End If  
       f.Align = FieldAlignEnum.CenterMiddle  
       f.CanGrow = True  
    Next  
 End With  

 l = 0  
 With C1Report1.Sections(SectionTypeEnum.Detail)  
    .Height = 300  
    .Visible = True  
    .CanGrow = True  
    C1Report1.Font.Bold = False  

 For i As Integer = 0 To ListBox2.Items.Count - 1  
    If i = 0 Then  
       f = .Fields.Add("D" & ListBox2.Items(i).ToString, ListBox2.Items(i).ToString, 0, t, w, h)  
    Else  
       l = l + 1500  
       f = .Fields.Add("D" & ListBox2.Items(i).ToString, ListBox2.Items(i).ToString, l, t, w, h)  
    End If  
       f.Align = FieldAlignEnum.CenterMiddle  
       f.Calculated = True  
       f.CanGrow = True  
    Next  

 End With  
 C1PrintPreviewControl1.Document = C1Report1

Please refer to the attached Sample for the detailed implementation. The following are the two outputs generated on the basis of the different fields selected by the user for the same report: A. Shows the fields: ProductID, ProductName, CategoryID B. Shows the fields: ProductId, QuantityPerUnit, UnitPrice, UnitsInStock