Using Parameters and Multiple Data Sources in FlexReport

When creating reports with FlexReport, there are a few things to consider when it comes to accessing your data. There are cases where you want to provide a full database or table of information on a report but there are others where you want to limit that information. For example, if you’re creating an invoice report, you wouldn’t want an invoice for every order in your database. Just a specific order.

There are two ways you can restrict the amount of information loaded in FlexReport: parameters and filtering. The primary difference between the two is when the manipulation occurs. Parameters are included in a WHERE clause in your SQL command and, therefore, only pull the records from the server you need. Filters require you to load all the data and then only include the specified records in the report. There are definitive scenarios where each is useful.

To give a practical example of both methods, I have created a demo application that produces an invoice report based on the orders table in the sample NorthWind AccessDB provided with our samples.

Please note: Since I’m using an AccessDB for this sample, I have to use the 32bit version of FlexReportDesigner because the Microsoft JET provider is only 32bit. If you want to modify the InvoiceFlexReport.flxr in the sample, you will need to open it with the 32-bit version of FlexReportDesigner.

You can find that version here:

C:\Program Files (x86)\ComponentOne\Apps\v4.5.2\C1FlexReportDesigner32.4.5.2.exe

If you're using a SQL database, you can use either a 32bit or 64bit FlexReportDesigner as there are SQL data providers for both.

Establishing the Main Data Source

First, we need to create a new FlexReport report definition. Open FlexReportDesigner and click “New Report." Now we need to establish the data for our report. I am using the C1NWind.mdb that we provide in the Documents/ComponentOne Samples/Common folder.

I am using the following Connection String:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=?(SpecialFolder.MyDocuments)\ComponentOne Samples\Common\C1NWind.mdb;Persist Security Info=False

Note: I’m using “?(SpecialFolder.MyDocuments)” since the documents’ folder path is dependent on the login of the machine (IE C:\Users\andrew.paxinos\Documents)

Now we need to figure out what data we need. The orders table includes the OrderID I want to report on, as well as most of the details. However, it only includes a CustomerID foreign key reference. To get both the orders and customer table in the same Data Source, I’m going to have to use a join.

SELECT Orders.OrderID, Orders.OrderDate, Orders.ShippedDate, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID

Creating a Parameter

Next, I need to establish a parameter so I can provide a specific OrderID to report on. Right-click on “Parameters” in the “Data” tab and click “Add Parameter."

Name the new parameter: pOrderID.

Now, right-click on the main data source we created earlier and select “Edit."

We need to add a WHERE clause to the SQL SELECT statement that uses the parameter we just created. In our case, we will add WHERE OrderID = pOrderID to the end.

SELECT Orders.OrderID, Orders.OrderDate, Orders.ShippedDate, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID WHERE OrderID = pOrderID

Desigining the Report

Next, we will lay out the report header using the fields from both tables in a Sold To/Shipped To arrangement.

For the Detail section of the report, we need to pull the individual line items for each order from the order details table related to the OrderID. In this case, it will make sense to utilize a subreport so let’s create a new empty report and name it "InvoiceSubReport."

Click over the “Data” tab, right-click on “Main”, and select “Edit." Use the same connection string. In this case, we will need all the fields from the order details table. However, it only includes a ProductID foreign key so we will use another join to pull in the ProductName from the products table.

SELECT [Order Details].OrderID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID;

Please note: I am including the order details ProductID column even though I’m not displaying it in the subreport. I’m doing this so I can link the main and subreport via that field. Also, you do not need to include a parameter or WHERE clause in the subreport. Then, I laid out the fields in the subreport (see below).

Click back to the “InvoiceReport” and click the “Insert" tab in the ribbon at the top and select "SubReport > InvoicesSubReport." Then, draw the subreport into the details section of the report.

Right-click on the “Subreport” and select “Link Subreport."

Ensure the “OrderID” column is selected for both master and child report and click “OK."

For this sample, I will be modifying the subreport’s datasource at runtime (for the filtering example) so I will also rename the field “subInvoiceSubReport” to more easily identify it later.

Using FlexReport and FlexViewer in a .NET Application for Display

Since the report is finished, let's build the application. I’m going to be doing a Winforms app for this sample, but the same concepts will work on any platform.

I’m going to emulate a common design I’ve seen our customers use. There will be a main form with a grid displaying all the orders from the orders table. A user can select one of the orders in the grid and there will be a button that loads a new form with a report version of the invoice.

First, I’m going to drop a C1FlexGrid on the form and 2 .NET buttons. One button will generate a FlexReport via the parameter, and the other will use filtering and an XML/DataTable datasource.

Next, I’m going to fetch the orders table from the AccessDB we used for the report, place that data in a .NET DataTable, and bind the FlexGrid to it. I will also disable editing for FlexGrid and add the following code for the button click events:

        public Form1()  
        {  
            InitializeComponent();  
            // Find the current users Documents folder  
            string documentsfolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);  
            // Establish connection string for Northwind Access DB in the ComponentOne Samples/Common folder  
            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ documentsfolder +@"\ComponentOne Samples\Common\C1NWind.mdb";

            // Create a DataTable and fill it with the contents of the Orders table  
            DataTable dt = new DataTable();  
            using (OleDbConnection conn = new OleDbConnection(connString))  
            {  
                OleDbCommand cmd = new OleDbCommand("Select * FROM Orders", conn);  
                conn.Open();  
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);  
                adapter.Fill(dt);  
            }  
            // Bind the FlexGrid to the Orders table  
            c1FlexGrid1.DataSource = dt;  
            // Disable editing (grid being used a selector only)  
            c1FlexGrid1.AllowEditing = false;  
        }

        private void btnParameter_Click(object sender, EventArgs e)  
        {  
            // Get the value of the cell in the currently selected row and column OrderID  
            int OrderID = (int)c1FlexGrid1[c1FlexGrid1.Row, c1FlexGrid1.Cols["OrderID"].Index];  
            // Create an instance of frmFlexReport and pass OrderID  
            frmFlexReport Report = new frmFlexReport(OrderID);  
            Report.Show();  
        }

        private void btnFilter_Click(object sender, EventArgs e)  
        {  
            // Get the value of the cell in the currently selected row and column OrderID  
            int OrderID = (int)c1FlexGrid1[c1FlexGrid1.Row, c1FlexGrid1.Cols["OrderID"].Index];  
            // Create an instance of frmFlexReportFilter and pass OrderID  
            frmFlexReportFilter Report = new frmFlexReportFilter(OrderID);  
            Report.Show();  
        }  

Using Parameters

For the parameter version, I created an additional Windows form named frmFlexReport and modified it’s constructor to accept an int value being passed (the OrderID from the FlexGrid’s selected row OrderID column). Next, I load the Invoice.flxr file in the reports folder and the InvoiceReport report, set the parameter pOrderID to the value passed in, and load the FlexViewer.

    public partial class frmFlexReport : Form  
    {  
        // Note, modified constructor to accept int to be passed  
        public frmFlexReport(int pOrderID)  
        {  
            InitializeComponent();  
            // Load the Report  
            c1FlexReport1.Load(."./../Reports/Invoice.flxr", "InvoiceReport");  
            // Set the parameter with the value selected in the grid  
            c1FlexReport1.Parameters["pOrderID"].Value = pOrderID;  
            // Load the FlexViewer  
            c1FlexViewer1.DocumentSource = c1FlexReport1;  
        }  
    }

Using Filters

For the filter version, I have created 2 XML files in the /Data folder of the sample that contain the same columns and data that were included in the AccessDB. That data is loaded into two .NET DataTables. I load the same FLXR and InvoiceReport. Next, I modify the loaded report’s main DataSource.RecordSet to point to the appropriate DataTable. Then I capture the FlexReport’s.Fields[“subInvoiceSubReport”] field as a SubReportField object and that allows me to set the subreport’s DataSource.RecordSet to the other DataTable.

Then, clear the parameter collection because we no longer have a datasource with a WHERE clause so we cannot use parameters. Finally, we set the DataSource.Filter property to a string of OrderID, equal the value passed in the constructor, and load the FlexViewer control,which renders the report.

    public partial class frmFlexReportFilter : Form  
    {  
        // Note, modified constructor to accept int to be passed  
        public frmFlexReportFilter(int pOrder)  
        {  
            InitializeComponent();

            // Load dataTables from .xml files in the /Data folder  
            DataTable dtOrdersCustomer = new DataTable();  
            dtOrdersCustomer.ReadXml(."./../Data/OrdersCustomer.xml");  
            DataTable dtOrderDetailsProducts = new DataTable();  
            dtOrderDetailsProducts.ReadXml(."./../Data/OrderDetailsProduct.xml");

            // Load the Invoice Report  
            c1FlexReport1.Load(."./../Reports/Invoice.flxr", "InvoiceReport");

            // Replace the existing SQL data source by pointing directly to the DataTable  
            c1FlexReport1.DataSources["Main"].Recordset = dtOrdersCustomer;

            // Capture the SubReport field from the InvoiceReport and create a .NET object named subInvoiceSubReport  
            C1.Win.FlexReport.SubreportField subInvoiceSubReport = c1FlexReport1.Fields["subInvoiceSubReport"] as C1.Win.FlexReport.SubreportField;  
            // Set the SubReport's DataSource RecordSet to the other DataTable  
            subInvoiceSubReport.Subreport.DataSources["Main"].Recordset = dtOrderDetailsProducts;

            // Clear the Parameter collection (since we're not using it anymore)  
            c1FlexReport1.Parameters.Clear();  
            // Set the Filter String  
            c1FlexReport1.DataSources["Main"].Filter = "OrderID = " + pOrder.ToString();  
            // Load the FlexViewer  
            c1FlexViewer1.DocumentSource = c1FlexReport1;  
        }  
    }

Hopefully, this will give you a better understanding of how you can use and manipulate data when working with FlexReport. Depending on the size and access speed of your data source using parameters will normally result in better performance because you are only pulling down the data you need for the report. However, if you’re in a situation where you can’t utilize a WHERE clause or if you need to manipulate the data before presenting it on the report (but don’t want to push those changes to the data source), filter may be a good option.