C1OLAP Quick Start

The C1Olap Web Part Quick Start shows you how to create an OLAP Grid and Chart from demographic data stored in a SharePoint list.

This Quick Start uses the Demographics list. If you have not created this list, see Quick Start for instructions.

How it works:

The Demographics list contains detailed data about 1,700 individuals: their birth date, yearly income, marital status, gender, how much they spent, number of children, highest level of education, occupation, number of cars they own, and whether or not they own a home. This data can be examined a number of ways in the C1Olap Web Part to learn about the earning, spending, and lifestyle choices of this group.

• We will use four of fields to examine a subset of the purchasing habits of men: Gender, Occupation, BirthDate, and TotalPurchases
• On the Grid, we will see how much money men born after 1977 have spent on our product, based on their year of birth and broken down by their occupation level. Any totals that exceed $80,000 will be highlighted in blue. Double-clicking on any number in the Grid will display all the detailed data behind the number, specifically, all of the men born in that year and how much money they spent (as well as the number of cars they own, number of children, etc.)
• On the Chart, we will see the same data displayed in a stacked column chart with a legend and title. 

To create a custom view of data with C1Olap

1. Add the C1Olap Web Part to the page. (See Adding a Web Part to a Page.)
2. Open the On-Board Designer. (See Using the On-Board Designer.)
3. Click the Data Source tab. Choose the Demographics List.                  

    

4. Click the Save Web Part button.

Now we will customize the appropriate fields in the C1Olap Data Panel. (See the Tour of C1OLAP for more about the Data Panel.)

Note: Make sure the Defer Updates check box in the lower left of the Web Part is selected before starting. This will prevent the data from being recalculated every time we make a change to a field. 

Customizing the Gender filter

1. First, click on the Gender field, then drag the field to the Filter area.

    

2. Right-click on Gender, and choose Field Settings from the menu.

   

3. In the Filter tab, Clear the F (female) check box and click OK. Now only the data about men will be used in the Grid and Chart.

    

Customizing the Spending Amounts

The field TotalPurchase contains the total amount spent by each of the men on our product. We'd like it to display as dollar amount in the Grid, we'd also like values above $80,000 to have blue shading in the Grid.

1. Select the TotalPurchase check box; since it is a numeric field it will automatically be added to the Values area. (Of course, we can drag fields to any area.)

2. Right-click on TotalPurchase, and choose Field Settings from the menu.

3. In the Format tab, click Currency on the left.

4. In the High Values tab, enter 80000 in the Apply to Values Above text box. Change the Not Set drop-down to Absolute. From the Background drop-down, choose a blue shade from the color picker. Click OK.

Setting Up the BirthDate field to display the Spending Data by Year of Birth

We have the Birth Dates for all of the men, and we'd like use that information to aggregate our data — instead of the rows being the birthday of each man in the database, we'd like to see the spending data for each year of birth, starting with 1978. Then we can see how someone's year of birth may affect their spending habits.

1. Select the BirthDate check box; it will automatically be added to the Row Fields area.

2. Right-click on BirthDate, and choose Field Settings from the menu.

3. In the Filter tab, click the Date/Time Filter button and set it to Greater Than 12/31/1977 12:00:00 AM. Click OK.

    

4. In the Format tab, choose Custom from the left and enter yyyy in the Custom Format check box. This will set the display for each row to the year only; the month and date will not appear. Click OK.

5. In the Display tab, change the Caption from BirthDate to Year of Birth. This will change the display name of the field in the C1Olap Data Panel, as well as the title displayed on the row in the Grid.

    

6. Click OK. You may want to clear the Defer Updates check box and take a look at what we've done so far. You'll see that specifying Row and Value fields is sufficient; you don't have to specify Column fields unless you need them. Select the Defer Updates check box when you are done.

Displaying the Spending Data by Occupation

Since we would like the spending information to appear in columns, segmented by occupation, we use the Occupation data for our columns.

1. Click on the Occupation field then, drag it to the Column Fields area.
2. Click OK.

We want the information for all the occupational levels to display, so that's it.

Setting the Grid Totals

In this Quick Start, we'd like each row to display a Grand Total, and we'd also like each column to have one. This is easy to customize using the Grid button in the C1Olap Toolbar. You can also choose to display a Subtotal, or no total at all.

1. Click the Grid button in the C1Olap Toolbar.
2. Choose Total Rows, then Grand Totals.

    

3. Choose Total Columns, then Grand Totals. Note that Show Zeros is selected by default, this means that any cells that contain no data will display zeros.

Customizing the Chart

Now we need to choose our chart type and palette. These are changed using the Chart button on the C1Olap toolbar.

1. Click the Chart button on the C1Olap toolbar.
2. Choose Chart Type, then Column.

   

3. Choose Palette, then Aspect.
4. Leave Show Title, Show Gridlines, and Stacked selected.

The result:

Now clear the Defer Updates check box, and see what you've created.

• Click the Olap Grid tab. A grid is created that displays data about the total spending on our product by men born after 1977, broken down into their occupational categories. Both the rows and the columns are totaled. Any dollar amounts over $80,000 are highlighted in blue.
• Double-click on any cell in the grid to see the detailed data about the number displayed.

• Click on the Olap Chart tab. A stacked column chart is displayed that uses the Aspect palette, and contains a title and legend.

Bonus! You can check your work:

The zip file ftp://publicfiles.componentone.com/C1StudioforSharePointDataSources.zip that contains the Demographics list also contains an .olapx file named C1OlapQuickStart.olapx. Save this file when you download the Demographics data.

You can check your work by adding a C1Olap Web Part to a page, pointing to the Demographics data source in the On-Board Designer, then clicking the Open button on the C1Olap Toolbar and opening this .olapx file. Compare your Grid and Chart with the view in this file. (See Saving Your Grid and Chart for information about opening .olapx files.)