OLAP Reports

ComponentOne OLAP for WinForms is a suite of 5 controls used to provide data analysis in the form of pivot tables and charts. With its ad-hoc view creating abilities, you can deliver an endless amount of data views to end-users. This limitless analysis yields less code maintenance for adding new views requested by users down the road. One of the features of C1Olap is its built-in reporting. The C1OlapPrintDocument control creates reports based upon the OLAP view. There are several options you can specify to customize your reports including page setup, adding headers and footers, and specifying which content to include (grid, chart, raw data). The OLAP grid is rendered in the report exactly as you see it on the form. ComponentOne also provides powerful reporting tools for Windows Forms development, C1Report and the C1ReportDesigner application. These are included in Studio for WinForms, but they can be combined with the C1Olap controls for more flexibility and reporting precision. For example, if you want to create a report with further customization of the OLAP grid, whether it be in layout or style, the best solution is to use C1Report. This blog article shows how you can get your OLAP data into C1Report.

Loading OLAP Data into C1Report

In order to get the "pivotized" data (aka OLAP data) into C1Report we have to find a common link between the two. The C1Report component can accept a DataTable as its datasource. The C1OlapEngine can give us the pivotized data as a DataTable through its OlapTable property. Cha-ching! We basically have our solution:

c1Report1.DataSource.Recordset = c1OlapPage1.OlapEngine.OlapTable  

That's the long story short. In reality our code flow looks more like this:

c1Report1.DataSource.Recordset = {Do Work} = c1OlapPage1.OlapEngine.OlapTable  

The work we need to do between includes: 1) Adding row header information to the DataTable, 2) Designing the report layout, and 3) Some necessary formatting. The rest of this blog post details possible solutions to these steps.

1: Adding Row Header Information

Before we get started, we need to examine the DataTable that C1Olap gives us so we are able to properly bind our report fields. For example, here is a C1OlapGrid showing Country in the column fields and ProductName in the row fields. Here we bind a regular DataGridView to C1Olap DataTable (dataGridView1.DataSource = c1OlapPage1.OlapEngine.OlapTable) so you can see what the underlying DataTable looks like: As you can see, the key thing to note here is how the column headers are formatted. The first four column headers have a format like "Field:Value;" and the Total column is unique. We need to know this when we create our report fields as we want them to be bound to these columns. There are two different routes to go: We could actually edit a cloned copy of the DataTable and clean up the column headers. Or we could set up the report expecting these given formats. It really does not matter because either way the user will not have to read the crazy formatted text. The second option actually requires 1 less step. But the biggest problem with the DataTable is that there is no row header information (ie product names). This data is not actually considered part of the OlapTable, but we obviously need it to be displayed in our report. The solution here is to simply add this information to our DataTable before passing it to C1Report. The code below adds the row header information to a clone of the OlapTable.

DataTable GetOlapDataTable()  
{  
    // clone OLAP table  
    var dt = this.c1OlapPage1.OlapEngine.OlapTable.Clone();  
    dt.Merge(this.c1OlapPage1.OlapEngine.OlapTable);  

    // add row information to output table  
    var dc = dt.Columns.Add("{RowInformation}", typeof(string));  
    for (int i = 0; i < dt.Rows.Count; i++)  
    {  
        var dr = this.c1OlapPage1.OlapEngine.OlapTable.Rows[i];  
        var key = this.c1OlapPage1.OlapEngine.GetOlapKey(dr);  
        dt.Rows[i][dc] = key.ToString();  
    }  

    // done  
    return dt;  
}  

Problem solved.

2. Designing the Report Layout

The whole point of using C1Report rather than the built-in OLAP reporting is so that you can customize the report layout and design to your customer's precise requirements. So this isn't just a necessary step, it's where you can have fun and really take advantage of your freedom to do what you want. Maybe you want pictures, watermarks, different color schemes used for the grid, or even a custom, calculated data field - it's all now possible. You're only limited by what C1Report can offer you. A C1Report needs two things: a data source and a report definition. We already have our datasource, the OLAP DataTable. The report definition defines the layout and design aspects of the report including headers, footers, details, etc. There are two approaches to creating the report definition: 1) Using the C1ReportDesigner application. This is a WYSIWYG designer much like Microsoft Access, and it allows you to visually create the report by dragging fields and setting properties. The definition can be saved as XML and loaded into a C1Report at run-time. 2) Creating the report definition entirely in code. Everything you can do in the C1ReportDesigner application you can alternatively do in code. The benefit of doing it in code is that you can change aspects of the report dynamically much easier. You could also create the initial report definition in the designer and then just make adjustments dynamically in code. The best option will really depend on your scenario and what you prefer. We have plenty of samples and documentation on creating reports both ways, so i'm not going to walk-through these steps. I'm going to jump straight to the important part regarding our OLAP DataTable. For each column of the grid we put a Label field in the Details section of the report. We set the text of each label to the name of the column from our data source surrounded by square brackets.

reportLabel1.Text = "[Country:Argentina;]";  
reportLabel2.Text = "[Country:Austria;]";  
...  
reportLabelTotal.Text = "[{total}]";  

Of course, if you are creating this in the C1ReportDesigner you would not include the quotes. If we go back and examine the RowInformation column we added to the DataTable, we see there is some unecessary text there we would like to erase. We can take advantage of the built-in VBScript function Replace to do some simple string formatting on this field.

reportLabelProduct.Text = "Replace([{RowInformation}], "ProductName:", "")";  

If you want to generate dynamic reports where you don't know what columns the user has chosen (since that's one of the key benefits of C1Olap), you just have to do more manipulation with the report labels and the DataTable to get the column header text. Using the C1ReportDesigner is fine if you know exactly what fields will be included. We can display this report using the C1PrintPreviewControl or the C1PrintPreviewDialog controls that are included with Studio for WinForms. From there we have instant printing and export functionality. Here is the final code to display this report up to this point:

c1Report1.DataSource.Recordset = GetOlapDataTable();  
//launch print preview dialog  
C1PrintPreviewDialog dg = new C1PrintPreviewDialog();  
dg.Text = "Print Preview";  
dg.Document = c1Report1;  
dg.Show();  

This code assumes the report definition was loaded at design-time. If you designed your report definition in the C1ReportDesigner, you can load this into C1Report using the Load method. Otherwise, if you created the report entirely in code you don't need to load anything.

3. Some More Formatting

Rather than using the Replace VBScript function, we could simply format the text for the row headers as we feed it into the DataTable. In the GetOlapDataTable method defined above, we can change the following line:

dt.Rows[i][dc] = key.ToString().Replace("ProductName:", "").Replace(";","");  

This also gets rid of the trailing semicolon. Then we can get rid of the Replace function in the report field text. (This part was thought of afterwards so that's why it's a separate step ;-)

Conclusion

It's really not that difficult to use C1Olap with C1Reports. Most of the work involved is customizing the report. You may want to create your reports entirely in code so that it can be completely dynamic. You can also combine OLAP charts into the report by saving the C1OlapChart as an image and adding it to the report document. Check out the C1Reports documentation for more information.

Download this Sample