Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 18, 2006

BlackBelt Authoring: Conditional Drillthrough to Multiple Reports - Page 7

By William Pearson

Create a Basic Report from Scratch as Our Second Target Report

The last member of our practice report set will be a simple "message" report. This report will exist as a "second option" for drillthrough, and will not even require an underlying dataset. As we stated in our introductory comments, the point is to focus on the linkages between the launch and target reports, and to be distracted by peripheral report authoring, or other considerations, as little as possible. While the business environment would likely demand far more, we will design this report to simply generate a statement that "no data is available" for a given Sales Territory Group selection – a straightforward scenario with which to illustrate conditional drillthrough.

1.  Right-click the Reports folder, once again, within in the Solution Explorer.

2.  Select Add -> New Item ... from the cascading context menus that appear, as depicted in Illustration 54.

Illustration 54: Select Add -> New Item ...

The Add New Item – AdventureWorks Sample Reports dialog appears.

3.  Select Report within the Templates section of the dialog.

4.  Type the following into the Name box at the bottom of the dialog:


The Add New Item – AdventureWorks Sample Reports dialog appears as shown in Illustration 55.

Illustration 55: Add New Item – AdventureWorks Sample Reports Dialog

5.  Click the Add button.

The dialog closes and the report file is created. The Report Designer opens, defaulting to the Data tab.

As we have learned from our client colleagues, only text is needed to support the simple target report requirement. We will add a textbox data region, along with text, to the report canvas on the Layout tab.

6.  Click the Layout tab.

7.  From the Toolbox, drag a textbox data region to the Layout tab, as depicted in Illustration 56.

Illustration 56: Adding a Textbox Data Region ...

The textbox appears on the report canvas, as shown in Illustration 57.

Illustration 57: The Textbox Appears on the Report Canvas

8.  Click inside the Textbox, to ensure the cursor is resident there.

9.  Type the following text into the textbox.

Data is not currently available for selected year.

10.  With the cursor still inside the textbox, click the Bold button in the toolbar.

11.  Click the Italics button next, as depicted in Illustration 58.

Illustration 58: Applying Formatting to the Newly Added Text ...

12.  Click the border of the textbox, to select the textbox itself.

13.  Placing the cursor over the left edge of the textbox (the cursor turns into a multi-directional arrows icon), grab the textbox and drag it to the left edge of the canvas, as shown in Illustration 59.

Illustration 59: Align the New Textbox with the Left Edge of the Canvas

14.  Placing the cursor over the right edge of the textbox (the cursor turns into a double-headed arrows icon), grab the edge of the textbox and widen it until the text inside it is fully visible, as depicted in Illustration 60.

Illustration 60: Widen the Textbox Until the Text Inside is Fully Visible

15.  Select File -> Save All from the main menu, as we did earlier.

We now have a complete set of basic reports – enough to allow us to perform setup of conditional drillthrough in the next section. As an aside, within data regions we can typically use the NoRows property to perform the function of the simple second target report we have created in this procedure. Within the context of our specific example, we could likely have set this property for the launch report matrix; rather than having a "message report," such as our second target report, to return "no data," we might have placed the "data is not ... available" message within the NoRows property.

Our purposes here are simply to create a set of working reports quickly to allow us to focus on conditional drillthrough. The procedures we use to do so with the reports we have created, including this unlikely second target report, would be similar if each target report were a sophisticated, fully formatted report, with obvious report-specific differences in parameters to be passed, and so forth.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM