When Completed

Your NetCharts Designer perspective will appear like this:

And after you execute a drill down operation:

Getting Started

Start by closing any open editors. Go to NetCharts Designer’s File menu and select the Close All option. Next, create a new project named DrilldownProject.

Creating A Summary NDS Using Pivot Function

Start by selecting the New NDS wizard icon in the taskbar.

The NDS wizard will appear. Type in data as the NDS Name, and select Hypersonic SQL as the Data Source.

Select Next. The second panel will appear. Click the Use Query Builder button.

Select REVENUES from the Table dropdown menu. Select the fields for LOCATION, CAFFEINATED, COFFEETYPE, and ALLACTUAL. (Note that ALLACTUAL is near the bottom of the list).

Click Next and then Finish to put the query into the Data Wizard SQL text. Click Finish on the Named Data Set Wizard.

We will need to summarize this data. Select the Functions tab at the bottom of the data editor. In the Data Pivot group, select the Pivot On Columns checkbox, and select ALLACTUAL from the Data Column(s) list.

Create the Summary Pie Chart

This chart will show all revenues broken into caffeinated and decaffeinated coffees.

Select the New Chart wizard icon from the tool bar. After the Chart Wizard appears type in summarychart as the Chart Name, and select Pie as the type.

Click the Finish button. A default pie chart appears in the editor.

Next, we need to bind the data into the pie chart. Select the Data Source Binding tab at the bottom of the pie chart editor. Click on the Slices tree node. Select data.ndx from the Data Source: menu.

Set the menu for the last column to Slices, then select Row 1 and Row 2.

Now, select the Slice Labels node in the tree control. Select data.ndx from the Data Source: menu. Set the menu for the first column to Slice Labels, then select Row 1 and Row 2.

Now let’s customize the appearance of this pie chart. Select the Presentation tab at the bottom of the editor.

In the General group, set the Default Colors: to ambers. Leave the width W: at 500 and leave the height H: to 300. In the Titles group, set the Text to be Revenues. Then, in the Title group, select  Bottom from the Title menu and set the text to be Click on slice to show distribution by location.

Select the Pie & Slices node on the tree control. In the Label Content group set the Number Format to dollars.

Create Variable-Driven Detail Data (First Level)

In the Project Navigator, right click the data.ndx file, and, using the pop up menu, Copy the file. Then right click the DrilldownProject name, and Paste the file back into the project. Rename the file detaildata.ndx. Double click on detaildata.ndx to display the NDS in the editor.

In the SQL area, add the following: WHERE Caffeinated = 'CAFFTYPE'

In the Variables area, click the Add button.

Then, in the Name textbox add the variable CAFFTYPE. In the Default textbox, set the default value as Caffeinated and click OK.

The preview browser will re-display with just one row of data.

Create Detail Pareto Chart

This Pareto chart will show revenues for each location for either decaffeinated or caffeinated coffees.

Select the New Chart wizard icon from the tool bar. After the Chart Wizard appears type in detailpareto as the Chart Name, and select Pareto as the type. Click Finish to close the Chart Wizard.

In the Designer Navigator window, double click on detailpareto.cdx to display it in the editor.

Change the top title to use CAFFTYPE Revenue Source. The CAFFTYPE variable in the title will be replaced during the drilldown action.

Next, we’ll change the bar colors to be to match the pie. Click to highlight Bar Sets. In the Color Table: dropdown in the Bar Set Properties area, select ambers.

Now let’s map in the detail data. Select the Data Source Binding tab. Select the Bar Values node from the tree control, and select the detaildata.ndx file from the Data Source menu. Set the menu for each column, except for the first and last, to Bar Sets. Check the Process Row-Major checkbox, and then check the Row 1 checkbox.

Repeat the same steps as above for the Bar Labels, but select Row 0 instead. Make sure that the Process Row‐Major checkbox is checked.

Create Variable-Driven Detail Data (Second Level)

Using the Project Navigator’s Copy/Paste function, copy the detaildata.ndx to a new file called productdata.ndx. Double click on productdata.ndx to display it in the editor.

In the SQL area, add the following: AND Location = 'THELOCATION' at the end of the SQL statement. Then, in the Variables area, click the Add button to add a variable THELOCATION in the Name textbox. In the Default textbox, set the default value to North American. Click OK to close the Add Variables window. The preview browser will redisplay with just one column of data.

For this data set, we no longer want to have the aggregated data. Select the Functions tab at the bottom of the data editor. In the Data Pivot group, uncheck the Pivot On Columns checkbox. Your data should appear as below:

Create Drilldown Bar Chart

This bar chart will display coffee revenues by product for a given caffeinated type, and location.

Using the Chart Wizard, create a new bar chart named bar.cdx. Click to highlight the Bar Chart tree node, in the General group, select ambers from the Default Colors dropdown list. In the Barcharts Properties area, set the Layout to Horizontal.

Click to highlight the Bars node. In the Bar Highlights: dropdown list, select Undefined.

Now, let’s bind in the data. Select the Data Source Binding tab. Select the Bar Values node in the tree control. Select the productdata.ndx file from the Data Sources menu. Set the menu above the last column to Bar Set. Select the checkboxes from Row 0 to Row 6.

Repeat the above steps for the Bar Labels, but select the third column (instead of the last).

Select the Presentation tab at the bottom of the editor. Click the Bar Chart tree node, in the Titles area, set the Text: to Product Sales For THELOCATION (CAFFTYPE). Increase the Width W: to 600.

Expand the Axes tree node group, select Bottom Axes and in the Tic Labels area set the Number Format to dollars. Additionally, in the Layout area, set the Right Margin: to be 15.

Create Pages

Usually when designing drilldown reports, it is best to start with the lowest level and work upwards.

Using the Page Wizard, create a new page called popuppage.

Click Finish to close the Page Wizard.

Drag the bar.cdx from the Designer Navigator onto the newly created page. Click OK to close the Edit Dynamic Chart Properties dialog box.

Again use the Page Wizard and create a new page called detailpage. Drag the detailpareto file onto the detailpage layout. Click OK when the Edit Dynamic Chart Properties dialog appears.

Available from the set of HTML Form Field controls is a Back Button. To include a back button on the detailpage, drag the HTML Form Element from the palette on the left over the chart icon representing detailpareto.cdx on the page layout. When the detailpareto.cdx label turns pink, release the mouse button. Select Vertical in the Layout Orientation window. Click OK to close the window.

In the drop down menu list, select the Back Button object from the Select an HTML Form Element dialog. Click OK to close the window.

And click OK to the Edit Back Button Properties dialog.

The NetCharts Designer Preview Browser should now display a Pareto chart with a Back button.

Finally, using the Page Wizard, create a new page called toppage. Drag the summarychart file onto the page layout. Click OK when the Edit Dynamic Chart Properties dialog appears.

Incorporating Drill Behaviors

We will use the summarychart chart to refresh the detailpareto chart with either Caffeinated or Decaffeinated data. Double click on the summarychart.cdx icon in the Page Layout of the toppage.pgl. In the Edit Dynamic Chart Properties window, click on the Drilldown tab to view the Drilldown Properties. Since we are drilling down to a different page, we will select the Drilldown Target Page to be detailpage. We will also need to pass in the slice label for the item that got selected. This will be used by the detailpareto chart.

Select the checkbox next to Slice Label. Select CAFFTYPE from the variable menu. This means that when the chart is clicked on, the slice label is passed back to this page to be used as the value for the CAFFTYPE variable.

Select the OK button. You can test the drilldown by clicking on the Caffeinated or Decaffeinated pie slices of the summarychart in the toppage.pgl. The detailpareto in the detailpage.pgl should display and change data and titles to correspond with the selected slice.

Now, double click on the detailpareto.cdx that’s located on the detailpage.pgl and select the Drilldown tab. In this case, when a bar is clicked, we want to drilldown to the popup page. Select popuppage from the Drilldown Target Page menu. Select Default Popup Window in the Drilldown Target Window dropdown. We want to pass along the location which is in the slice label, so check the X Axis Label checkbox, and select THELOCATION as the Variable.

Finally, we want the first piechart’s selections to get passed to the detail page as well, so check the Pass Page Variables checkbox. The Pass Page Variables box tells the code to look for the form fields on the page, so the hidden will store the value to be picked up for the next level drilldown.

From the Page Elements in the bottom Layout tab of the detailpage.pgl, drag an HTML Form Element and drop it onto the page next to the detailpareto.cdx box. In the HTML Form Element dropdown, select Hidden Form Fields and click OK.

In the Edit Hidden Form Fields window, click the Add button.

In the Edit Hidden Form Field window, select CAFFTYPE in the Variable dropdown. In the Value text box, enter Caffeinated and click OK.

The Edit Hidden Form Fields window should now appear like this. Click OK.

The detailpage.pgl should now include both the Back Button and Hidden Fields HTML Form Elements.

You can now test the entire example by clicking on the Caffeinated or Decaffeinated pie slices of the toppage.pgl to get to the Revenue Source details.

Then, click the bars of the different countries in the detail page to trigger the popuppage.pgl that contains the horizontal bar.

You can get back to the original view by selecting the right mouse button and choosing “Back” or clicking the Back button under the Pareto chart in the NetCharts Designer Preview Browser.