Using crosstabs

To perform certain kinds of data analysis, you might want to design DataWindow objects in the Crosstab presentation style. The basic steps for using crosstabs in an application are the same ones you follow for the other DataWindow types, but there are some additional topics concerning crosstabs that you should know about.

To learn about designing crosstabs, see Working with Crosstabs in Users Guide.

Viewing the underlying data

If you want users to be able to see the raw data as well as the cross-tabulated data, you can do one of two things:

  • Place two DataWindow controls on the window or form: one that is associated with the crosstab and one that is associated with a DataWindow object that displays the retrieved rows.

  • Create a composite DataWindow object that contains two reports: one that shows the raw data and one that shows the crosstab.

Do not share data between the two DataWindow objects or reports

They have the same SQL SELECT data definition, but they have different result sets.

For more about composite DataWindows, see Users Guide.

Letting users redefine the crosstab

With the CrosstabDialog method, you can allow users to redefine which columns in the retrieved data are associated with the crosstab's columns, rows, and values during execution.

The CrossTabDialog method displays the Crosstab Definition dialog box for the user to define the data for the crosstab's columns, rows, and values (using the same techniques you use in the DataWindow painter). When the user clicks OK in the dialog box, the DataWindow control rebuilds the crosstab with the new specifications.

Displaying informational messages

You can display informational messages when a crosstab is rebuilt during execution as a result of the call to CrosstabDialog. (The messages are the same ones you see when building a crosstab in the DataWindow painter, such as Retrieving data and Building crosstab.) You might want to do this if you are working with a very large number of rows and rebuilding the crosstab could take a long time.

PowerBuilder

In PowerBuilder, you use a user event to display the crosstab's informational messages.

To display informational messages when a crosstab is rebuilt:

  1. Define a user event for the DataWindow control containing the crosstab. Associate it with the event ID pbm_dwnmessagetext.

  2. In the script for the user event, get the value of the text argument (which holds the message that PowerBuilder would display when building the crosstab in the DataWindow painter) and display it to the user.

Examples

PowerBuilder

In the example, code for the DataWindow control's user event for pbm_dwnmessagetext displays informational messages in a static text control in the window containing the crosstab:

st_message.Text = text

With that script in place, after CrosstabDialog has been called and the user has redefined the crosstab, as the crosstab is being rebuilt, your application dynamically displays the informational messages in the static text control st_message. (You might want to reset st_message.Text to be the empty string in the line following the CrosstabDialog call.)

In this example, code in the user event for pbm_dwnmessagetext displays informational messages as MicroHelp in an MDI application (w_crosstab is an MDI frame window):

w_crosstab.SetMicroHelp(text)

The informational messages are displayed in the MDI application's MicroHelp as the crosstab is rebuilt.

For more information

For more about user events in PowerBuilder, see Working with User Events in Users Guide.

For more about the CrosstabDialog method and MessageText event, see the section called “CrosstabDialog” in DataWindow Reference and the section called “MessageText” in DataWindow Reference.

Modifying the crosstab's properties during execution

As with other DataWindow objects, you can modify the properties of a crosstab during execution using the Modify method. Some changes require the DataWindow control to dynamically rebuild the crosstab; others do not. (If the original crosstab was static, it becomes a dynamic crosstab when it is rebuilt.)

Changes that do not force a rebuild

You can change the following properties without forcing the DataWindow control to rebuild the crosstab:

Properties

Objects

Alignment

Column, Compute, Text

Background

Column, Compute, Line, Oval, Rectangle, RoundRectangle, Text

Border

Column, Compute, Text

Brush

Line, Oval, Rectangle, RoundRectangle

Color

Column, Compute, Text

Edit styles (dddw, ddlb, checkbox, edit, editmask, radiobutton, richtext)

Column

Font

Column, Compute, Text

Format

Column, Compute

Pen

Line, Oval, Rectangle, RoundRectangle

Pointer

Column, Compute, Line, Oval, Rectangle, RoundRectangle, Text


Changes that force a rebuild

If you change any other properties, the DataWindow control rebuilds the structure of the crosstab when Modify is called. You should combine all needed expressions into one Modify call so that the DataWindow control has to rebuild the crosstab only once.

Default values for properties

For computations derived from existing columns, the DataWindow control by default uses the properties from the existing columns. For completely new columns, properties (such as font, color, and so on) default to the first column of the preexisting crosstab. Properties for text in headers default to the properties of the first text control in the preexisting crosstab's first header line.

For more about the Modify method, see Dynamically Changing DataWindow Objects. For details on the DataWindow object properties, see DataWindow Object Properties in DataWindow Reference.