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:
- The report should be created at runtime.
- 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