impromptu admin

828
IBM (R) COGNOS (R) Business Intelligence Series 7 IBM (R) Cognos (R) Impromptu (R) ADMINISTRATION GUIDE Type the text for the HTML TOC entry Type the text for the HTML TOC entry Type the text for the HTML TOC entry Impromptu Administration Guide ADMINISTRATION GUIDE

Upload: ashok-kumar-pathakanuru

Post on 02-Oct-2014

563 views

Category:

Documents


7 download

TRANSCRIPT

Type the text for the HTML TOC entry Type the text for the HTML TOC entry Type the text for the HTML TOC entry Impromptu Administration Guide

ADMINISTRATION GUIDE

IBM(R) COGNOS(R) Business Intelligence Series 7IBM(R) Cognos(R) Impromptu(R)

ADMINISTRATION GUIDE

Product InformationThis document applies to IBM Cognos Impromptu Version 7.4 and may also apply to subsequent releases. To check for newer versions of this document, visit the Cognos Software Services Web site (http://support.cognos.com).(R) (R) (R)

CopyrightCopyright 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. Portions of Cognos ULC software products are protected by one or more of the following U.S. Patents: 6,609,123 B1; 6,611,838 B1; 6,662,188 B1; 6,728,697 B2; 6,741,982 B2; 6,763,520 B1; 6,768,995 B2; 6,782,378 B2; 6,847,973 B2; 6,907,428 B2; 6,853,375 B2; 6,986,135 B2; 6,995,768 B2; 7,062,479 B2; 7,072,822 B2; 7,111,007 B2; 7,130,822 B1; 7,155,398 B2; 7,171,425 B2; 7,185,016 B1; 7,213,199 B2; 7,243,106 B2; 7,257,612 B2; 7,275,211 B2; 7,281,047 B2; 7,293,008 B2; 7,296,040 B2, 7,318,058 B2; 7,325,003 B2; 7,333,995 B2. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries or both. Other company, product, or service names may be trademarks or service marks of others. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to the information contained in this document will be documented in subsequent editions. U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, or disclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer clause at DFARS 252.227-7013, or subparagraphs (C) (1) and (2) of the Commercial Computer Software - Restricted Rights at 48CFR52.227 as applicable. The Contractor is Cognos Corporation, 15 Wayside Road, Burlington, MA 01803. This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos.

Table of ContentsIntroduction 21 PART 1: Administering Impromptu 23 Chapter 1: Managing Catalogs 25 Create a Catalog 27 Change the Catalog Type 28 What is a Personal Catalog? 28 What is a Distributed Catalog? 28 What is a Shared Catalog? 28 What is a Secured Catalog? 29 Catalog Attributes 29 Managing Catalog Access 30 Controlling Catalog Access Using a Catalog Lock 30 Open a Master Distributed Catalog 30 Add Tables to a Catalog 31 Add Columns to a Catalog 31 Add the Same Table Twice to a Catalog 32 Sort Tables in a Catalog 32 Verify and Update Tables 33 Remove Tables and Columns 33 Qualify Catalog Content 34 Generate a Content Overview Report 34 Generate Default Reports 35 Chapter 2: Work with Folders and Folder Items 37 Create a New Folder 37 Copy and Paste Folders 38 Move Folders and Items in Folders 38 Remove Folders and Items in Folders 39 Rename Folders and Items in Folders 39 Sort Folders and Items in Folders 39 Generate a Folder from a Report 40 Synchronize Catalog Changes to Reports 40 Chapter 3: Work with Joins 41 Create an Equijoin 42 Create a Non-equijoin 42 Create an Outer Join 43 Create a Self Join 44 Create a Compound Join 44 Create a Complex Join 44 Create Joins Automatically When Creating a Catalog 45 Create Joins Automatically After Creating a Catalog 46 Test and Troubleshoot Joins 47 Change Joins 49 Remove Joins 49 Chapter 4: Working with Stored Conditions and Calculations 51 Building Expressions 51 Store a Condition or Calculation in a Catalog 53 Store a Summary in the Catalog 53 Administration Guide 3

Use a Stored Condition in a Calculation 54 Use a Stored Calculation in a Report 54 Use a Stored Condition as a Filter 55 Use a Stored Condition in Conditional Formatting 55 Change a Stored Condition or Calculation 55 Chapter 5: Working with Prompts 57 Create a Catalog Prompt 57 Modify a Catalog Prompt 59 Use a Catalog Prompt in a Report 59 Remove a Catalog Prompt from a Report 59 Create a Report Prompt 59 Modify a Report Prompt 60 Chapter 6: User Profiles and Data Access Requirements 63 Analyze User Requirements 63 Create a User Class 65 Edit a User Class 66 Remove a User Class 67 Use Access Manager to Control Security 67 Chapter 7: Techniques for Controlling User Access 69 Control Access Using Password Authentication 69 Assign or Change a Password for a User Class 69 Remove a Password from a User Class 70 Store a Database Password for a User Class 70 Prompt a User Class for a Database Password 70 Test User Access 71 Control Access to Objects 71 Control Access to Tables or Columns 71 Control Access to Folders and Items in Folders 72 Control Data Access Using Filters 73 Control Access Using a Catalog Lock 73 Control Access to Impromptu Functionality 74 Control Editing of the Catalog Structure 75 Control Creating and Editing Reports 75 Control the Ability to Enter SQL Directly 76 Control the Ability to Define User Classes 76 Chapter 8: Manage the Impact on the Network and Database 77 Use the Bulk Fetch Capability 77 Control the Retrieval of Large Text Items 77 Control the Number of Rows Retrieved 78 Control the Number of Tables Retrieved 78 Control Sorting on Non-Indexed Columns 79 Control the Creation of Outer Joins 79 Suppress Duplicate Rows of Data 79 Control the Creation of Cross-Product Queries 80 Control the Time a User Class Can Run Queries 80 Restrict the Time a User Class Is Connected to the Database 81 Adjust the Client/Server Balance 81 Assign Weights to Tables 82 Use Snapshots or Thumbnails 82 Chapter 9: Audit Impromptu Performance 85 Chapter 10: Work with HotFiles 89 Create a HotFile 90 Change or Update a HotFile 90 Add a HotFile to Your Catalog 91

4 IBM(R) Cognos(R) Impromptu(R)

Create a Catalog of Only HotFiles 91 Use a HotFile in Your Report 91 Use HotFiles as Lookup Tables 92 Share HotFiles by Including Them in Your Catalog 92 Join Multiple Databases Using HotFiles 92 Create and Use a HotFile for a Multiple Query Report 92 Chapter 11: Work with Stored Procedures 95 Create a Stored Procedure Report 96 Insert Parameters in a Stored Procedure Call 96 Hide Prompts for Stored Procedure Values 97 Chapter 12: Use SQL 99 View the Existing SQL for the Query 99 Create a Query By Writing Your Own SQL 99 Change a Query By Writing Your Own SQL 99 Generate Efficient SQL for Summary Level Reports 100 Chapter 13: Automating and Updating Impromptu 103 Standardizing Reports and Templates 103 Using Inheritance 104 Automating Tasks with Macros 105 Using the Impromptu Type Library 107 Manage the Size of Impromptu Reports 107 Create a Database Definition 109 Create a Database Definition for DB2 109 Create a Database Definition for a HotFile 111 Create a Database Definition for Informix 111 Create a Database Definition for Microsoft SQL Server via OLE DB 112 Create a Database Definition for ODBC 112 Create a Database Definition for Oracle 114 Create a Database Definition for Sybase Adaptive Server Enterprise 114 Copy a Database Definition 117 Remove a Database Definition 117 Select a Different Database for a Catalog 118 Connect to the Database 118 Chapter 14: Create and Customize a Chart 119 Choosing a Chart Type 119 Create a Chart 121 Change the Chart Type 123 Format a Chart 123 Add Chart Titles 124 Format Chart Titles, Axis Titles, Data Labels, and Legend Text 125 Format the Chart, Plot, and Legend Area 126 Change the Axis Labels and Scale Settings 126 Change the Grids Settings 128 Format Data Items 128 Hide or Show Data Labels in a Chart 129 Add and Format a Chart Legend 129 Show TrendLines 131 Correlation Chart 132 Chapter 15: Impromptu Chart Changes 135 Chapter 16: Impromptu Chart Controls 141 Formatting Dialog Box Controls 141 Grids and Scales Dialog Box Controls 142 Properties Dialog Box Controls 148 Titles Dialog Box Controls 155

Administration Guide 5

Trendlines Dialog Box Controls 155 Chapter 17: Product Sample Listing 157 Catalogs 157 Description 157 Location 157 Main Reports 157 Description 157 Location 160 Secondary Reports 161 Description 161 Location 161 Discovering Impromptu Reports 161 Location 162 Standard Templates 162 Description 162 Location 162 PowerPrompts Applications 162 Description 163 Location 163 OLE Automation (Macros) 164 Description 164 Location 164 User Defined Functions 164 Description 164 Location 165 Chapter 18: Integrating Impromptu with Other IBM Cognos Products 167 Use Architect with Impromptu 167 Import Impromptu Metadata into Architect 168 Export Catalogs from Architect 168 Integrate with Impromptu Web Reports 168 Preparing Your Reports for the Web 169 Security Interaction With Impromptu Web Reports 169 Integrate with IBM Cognos Query 169 Drilling Through to IBM Cognos Query 169 Importing Queries From IBM Cognos Query 170 Integrate with PowerPlay 170 Drilling Through from PowerPlay 171 Creating PowerCubes 172 Publish Reports to Upfront 172 Create NewsBox on Publish to Upfront 173 Data Formats and Locale Settings 174 Creating Accessible Reports 174 Enabling Accessibility Support 174 Design Considerations for Accessible Reports 175 Chapter 19: Troubleshooting and Best Practices 177 Unable to Identify Catalog Contents 177 PART 2: Creating Reports with Impromptu 179 Chapter 20: View a Report 181 View an Existing Report 181 View the Details of a Value in a Report 182 Change the Page Layout View of a Report 183 Chapter 21: View a Report with a Web Browser 185 Chapter 22: Print a Report 187 Preview a Report 188 6 IBM(R) Cognos(R) Impromptu(R)

Set Up the Printer 188 Print a Wide List Report on a Single Page 189 Print a List Report With Repeating Columns 190 Chapter 23: Export a Report 193 Export a Report to Other Applications 194 Add Report Navigation 195 Render a Report in Excel Format 196 Attach a Report to Email 196 CSV File Format 196 Chapter 24: Use Annotations 199 Chapter 25: Create a Report 201 Open a Catalog 202 Upgrade a Catalog or Report 203 Use the Report Wizard to Quickly Create a List Report 203 Use the Report Wizard to Quickly Create a Crosstab Report 204 Use a Template to Quickly Create a Report 205 Import a Query from IBM Cognos Query 207 Use a Report in Another Report 207 Create a Crosstab Sub-Report 208 Create Accessible Reports 208 Retrieve Data from the Database 209 Multiple Instances and Versions of Impromptu 209 Change the Number of Instances of Impromptu 210 Chapter 26: Add or Remove Data 211 Add Data to an Existing Report 211 Remove Data from a Report 212 Chapter 27: Group, Sort, or Associate Data in a Report 215 Group Data 217 Sort Data 218 Associate Data to a Grouped Data Item 219 Chapter 28: Filter or Find Data 221 Quickly Filter Data Using the Filter Button 222 Quickly Filter Data Using the Filter Drop-down Button 223 Filter a Report Using a Filter Stored in the Catalog 224 Filter Using Crosstab Titles 225 Find Specific Data in a Report 226 Copy, Cut, and Paste in the Expression Editor 227 Chapter 29: Work with Summaries 229 Create a Summary 230 Add a Total to a List Report 232 Use a Summary Stored in the Catalog 233 Move a Summary to Another Location 233 Chapter 30: Insert Text, Pictures, and Other Report Variables 235 Insert Text 235 Insert a Static Picture 236 Insert Page Numbers 238 Insert a Report Variable 239 Chapter 31: Hide, Show, Group or Resize Parts of a Report 241 Hide Part of a Report 242 Show Part of a Report 242 Unhide Part of a Report 242 Group Parts of a Report 243 Resize Part of a Report 244 Administration Guide 7

Stack Parts of a Report 244 Chapter 32: Format a Report 247 Apply a Template to an Existing Report 247 Add a Page Header or Footer 248 Adjust Margins 249 Align Parts of a Report 249 Position Part of a Report Using Snap to Grid 250 Chapter 33: Format Data 253 Justify Data 258 Enhance Data Display 259 Using the Currency Symbol List 259 Maintain Currency Symbols 260 Data Formats and Locale Settings 262 Number and Currency Data Type Formatting 262 Chapter 34: Add a Border Around Part of a Report 263 Add Color or a Pattern to Part of a Report 263 Change the Font 264 Apply a Style to Part of a Report 265 Chapter 35: Format a List or Crosstab Report 267 Hide or Show Grid Lines 267 Select Parts of a List Report 268 Move a Column 269 Change a Column Title 270 Change the Width of a Column 270 Change the Height of a Row 271 Add a Group or List Header or Footer 272 Change the Height or Width of a List Header or Footer 273 Keep Details and Group Headers (or Footers) Together 273 Insert a Page Break in a List Report 274 Select and Format Parts of a Nested Crosstab Report 274 Set the Default Format for Crosstab Columns 276 Chapter 36: Reporting While Disconnected From the Database 277 Create a Local Copy of the Report Data 278 Refresh a Local Copy of the Report Data 279 Remove a Local Copy of the Report Data 280 Chapter 37: Drill Through to IBM Cognos Query 281 Select the IBM Cognos Query Server 281 Set Up Drill-Through Access to IBM Cognos Query 282 Chapter 38: Prepare a Report for Impromptu Web Reports 283 Prepare a Report for Impromptu Web Reports 283 Create Burst Reports 285 Package HotFiles 285 Notify the Report Administrator 286 Chapter 39: PowerPrompts Developer Studio 287 Chapter 40: Report Specifications 289 File Searching 290 Command Line Switches 291 -m macro-filename 291 macro-filename 291 report-name 291 Chapter 41: Configuration Files 293 ini File Format 293 Function Definition Table (FDT) Files 294 8 IBM(R) Cognos(R) Impromptu(R)

Cognos.ini 294 Cern.ini 294 [IBM Cognos Locations] 294 Impromptu.ini 295 [Data Attributes] 296 [Default Directories] 297 [Startup Options] 298 [Query Options] 302 [Object Attributes] 304 [Styles] 304 [Graph Default Colors] 306 [Query Statistics] 306 [MRU Files] 307 Use Type Libraries 307 Chapter 42: Get Help on Basic and Advanced Topics 309 Chapter 43: Troubleshooting 311 Cannot Access the Datasource 311 Cannot Open a Catalog Created with Architect 312 Cannot Open a Catalog with Current User Class or Version of Impromptu 312 Cannot Connect to the SQL Server Database 313 Cannot Connect to the MS SQL Server 313 Cannot Find the Report or One of Its Components 313 Unable to Connect to the Database 314 PART 3: Mastering Impromptu Reports 315 Chapter 44: Report Data 317 Getting the Results You Want 317 Specify the Catalog for Your Reports 317 Group Automatically 318 Define Filters 318 Detail and Summary Filters 318 Create Filters 319 Use Filter Expressions 319 Filter Without Changing Your Summaries 320 What You Can Do Using Filters 321 Tips for Filters 325 Use Prompts to Select Report Data 325 Type-in Prompts 325 Picklist Prompts 326 When to Use the Types of Prompts 329 Using the Prompt Manager 329 What You Can Do Using Prompts 333 Format Report and Catalog Prompts 334 Summaries 336 Summarize Data 337 Create a Summary 340 Changing a Summarys Association 341 Create or Copy a Summary 342 Change a Detailed Data Item into a Summary 343 Calculated Fields 344 Create Calculated Fields 344 What You Can Do Using Calculations 346 Highlight Data 352 Create Styles 352 Conditionally Format Data 354 Conditionally Hide and Show Report Objects 356 Administration Guide 9

Chapter 45: Report Formats 359 Formatting Techniques 359 Data Formats and Locale Settings 359 Customize Data and Objects 359 Define Report Layouts with Templates 361 Set a Default Template 362 Customize the Templates Preview Image 362 Set the View for Templates 363 Create Effective Templates 363 Create Placeholders 365 Modifying Existing Placeholders 367 Create Calculated Placeholders 367 Insert Placeholders 368 Create Complex Layouts with Frames 369 The Parent/Child Relationship 370 Designate a Primary Frame 371 Insert a Frame 371 Select a Frame 373 Assign Names to Frames 373 List Frames 374 Form Frames 375 Chart Frames 378 Text Frames 379 Picture Frames 380 Chapter 46: Complex Reports 383 Present Complex Data 383 Crosstab Reports 383 Quickly Change a List Report into a Crosstab Report 384 Build Crosstabs from a List Query 384 Add Summaries to Crosstabs 386 Create Calculated Columns 388 Filter Data in Crosstab Reports 389 Swap Rows and Columns 390 Change a Crosstab to a List Report 390 Work With Sub-Reports 390 Create Reports Containing Sub-Reports 392 Edit Sub-Report Queries 392 Link Sub-Reports 393 Use Drill-Through Reports 394 Test Drill-Through Reports 397 Present Results with Other Applications 398 Using OLE to Present Impromptu Reports 398 Embed Objects In Impromptu 401 Embed Impromptu Objects in Other Applications 403 Link Impromptu Reports in Other Applications 404 Using HTML to Create Reports on the Web 405 Add Report Navigation 405 Align Report Objects in Impromptu 406 Eliminate Overlapping Objects in a Report 407 Avoid Multiple Scrollbars in HTML Report Output 408 Improving Performance 408 Save Reports as HTML 408 HTML Files 409 Distributing HTML Report Output 410 Export a Report in Excel Format 410 Exporting Reports with Multiple List Frames to Excel 411

10 IBM(R) Cognos(R) Impromptu(R)

Avoiding Multiple Worksheets on Export to Excel 411 Align Report Objects Before Export to Excel 411 Eliminate Overlapping Objects in Excel Reports 412 Add Worksheet Names Before Export to Excel 412 Change the Worksheet Order Before Export to Excel 412 Group Report Objects to Export Them to the Same Excel Worksheet 413 Rendering Headers and Footers in Excel Report Output 413 Export a Report to Excel with Images in the Header and Footer 413 Using Summaries in Excel Report Output 414 Using Data Scoping to Enhance Excel Report Output 414 Export a Text Field with Leading Zeroes to Excel 414 Export a Numeric Field with Null or Missing Values to Excel 415 Export a Form Frame within a List Frame To Excel 415 Chapter 47: Data and Databases 419 Choose Your Data Source 419 How Impromptu Retrieves Data 419 Take Advantage of a Cache Query 420 How Databases are Attached to Catalogs 420 Your Copy of the Database 420 Local Snapshots 421 Thumbnails 421 Use HotFiles 421 Chapter 48: About Catalogs 425 What Can You Do with Catalogs? 425 Use Catalogs 425 How Catalogs Open 427 Create a Personal Distributed Catalog 427 What Can You Change? 428 Rename Folders and their Contents 429 Create Catalog Folders 429 Move Folders and their Contents 430 Store Filters in Catalogs 430 Change a Predefined Filter Condition 431 Store Calculated Data Items and Summaries in Catalogs 431 Create and Store Prompts in Catalogs 432 Add User Classes 433 Chapter 49: Optimize Performance in Impromptu 435 Create Efficient Reports 435 The Difference Between Queries and Reports 435 Client/Server Balancing 436 Which Client/Server Options are Most Efficient? 437 Who Sets the Client/Server Balancing Option? 437 Summaries 437 Retrieve Summary Data Items 437 Put Summaries in Footers 437 Sorting on Database Summaries 438 Associating Summaries with Data Items 438 Choosing an Optimal Summary Strategy 438 Filters 442 Changes to the Filter 442 Database Summaries 442 Impromptu Functions 443 Non-Indexed Columns 443 Impromptu Functions 443 If/Then/Else and Lookup Statements 443

Administration Guide 11

Functions 443 Report Formatting Commands 444 Scan Data for Best Fit 444 Fit to Page 444 Use Alternative Data Sources 444 Test Your Queries Using Thumbnails 444 Improve Performance Using Snapshots 444 Improve Performance Using HotFiles 445 How Outer Joins Affect Your Work 445 What is an Outer Join? 446 When Not to Use Outer Joins 446 Outer Joins Examples 446 Limit the Data 447 Minimize Connection Time 447 Create Separate Accessible Reports 447 Manage the Size of Impromptu Reports 448 Chapter 50: Customize Impromptu 449 Customize Menus 449 Set Up a Launch Menu Command 450 Customize Toolbars and Buttons 450 Show or Hide Toolbars 450 Create a Custom Toolbar 450 Customize Toolbar Buttons 451 Set Up a Launch Button 451 Distribute Custom Menus and Toolbars 452 PART 4: Using Macros with Impromptu 453 Chapter 51: Introduction to Impromptu OLE Automation 455 Rendition ID 456 OLE Automation and Impromptu Registry Keys 457 Chapter 52: Run a Macro 459 Run a Macro in Impromptu Using a Command, Menu Command, or Toolbar Button 459 Run a Macro in Impromptu at Startup 459 Run a Macro in Impromptu Using a Command Line Option 460 Run a Macro in Impromptu Before Opening a Report 460 Chapter 53: Objects 461 Application Object 462 Catalog Object 464 CatalogLevel Object 465 Column Object 466 Database Object 467 DatabaseConnection Object 469 DatabaseDefinition Object 469 Expression Object 470 FolderItem Object 471 PublishExcel Object 472 PublishHTML Object 473 PublishPDF Object 474 QueryItem Object 475 ReportDocument Object 476 SchemaLevel Object 479 SelectedFrame Object 480 StoredProcedure Object 481 Table Object 482 TableLink Object 483 12 IBM(R) Cognos(R) Impromptu(R)

UserClass Object 484 Chapter 54: Collections 487 CatalogLevels Collection 488 Columns Collection 489 Databases Collection 490 DatabaseConnections Collection 490 DatabaseDefinitions Collection 491 DeniedCatalogs Collection 492 DeniedColumns Collection 493 DeniedFolderItems Collection 494 DeniedSchemas Collection 495 DeniedSelectValues Collection 496 DeniedTables Collection 497 FilteredColumns Collection 498 FilteredTables Collection 498 Folders Collection 499 Items Collection 500 QueryItems Collection 501 SchemaLevels Collection 501 SelectedFrames Collection 502 Tables Collection 503 TableLinks Collection 504 UserClasses Collection 505 Chapter 55: Methods 507 Activate Method 510 Add Method (CatalogLevels, SchemaLevels, UserClasses) 511 Add Method (Tables) 512 Add Method (Columns) 513 Add Method (Databases) 515 Add Method (TableLinks) 516 Add Method (DeniedCatalogs, DeniedColumns, DeniedFolderItems, DeniedSchemas, DeniedSelectedValues, DeniedTables) 517 AddExternalHotfile Method 518 AddFolder Method 519 AddItem Method (Use parameter) 520 AddItem Method (Column parameter) 522 AddTable Method 523 AppendEx Method (No Prompt) 524 Token Table 526 Prompt Syntax 529 ApplyTemplate Method 533 ChangeUserClass Method 534 Clear Method 535 ClearTOCColumns Method 536 Close Method 536 CloseCatalog Method 536 CloseReport Method 537 Commit Method 538 ConnectDatabase Method 539 CopySpecialToClipboard Method 540 CopyTo Method 541 CopyToClipboard Method 542 CreateAlias Method 542 CreateCatalog Method 543 CreateEmptyCatalog Method 544 CreateFilterFor Method 545 Administration Guide 13

CreateSnapshot Method 546 CreateStoredProcedure Method 547 DatabaseConnected Method 547 Delete Method 548 DisconnectDatabase Method 548 DistributedUpdate Method 549 Execute Method 549 Export Method 550 ExportASCII Method 551 ExportData Method 552 ExportdBASE Method 553 ExportExcelWithFormat Method 554 ExportHotFile Method 555 ExportSQL Method 555 ExportText Method 556 ExportTransformer Method 557 FileSetPrinter Method 558 GenerateReport Method 559 GetAppVersionInfo Method 560 GetDataValue Method 561 GetErrorNumber Method 562 GetFilterFor Method 569 GetNextQueryError Method 569 GetParameter Method 576 GetSelectedText Method 576 InsertQualificationLevel Method 577 Interactive Method 578 MoveTo Method 579 OpenCatalog Method 579 OpenCatalogEx Method 581 OpenDrillDownReport Method 583 OpenReport Method 584 OpenReportExA Method 585 OpenReportNoExecute Method 586 Print Method, PrintOut Method 587 Publish Method 588 PublishXML Method 589 QueryDialog Method 590 QueryItems Method 591 Quit Method 592 ReExecute Method 592 Remove Method 593 RemoveFilterFor Method 594 RemoveQualificationLevel Method 595 Reset Method 596 RetrieveAll Method 597 RetrieveRows Method 597 Save Method (Catalogs) 598 Save Method (Reports) 598 SaveAs Method 599 SetProcedure Method 600 Title Method 602 TOCAddColumn Method 603 UpdateCatalog Method 604 UseDatabase Method 604 Visible Method 605

14 IBM(R) Cognos(R) Impromptu(R)

Chapter 56: Properties 607 ActiveCatalog Property 611 ActiveDocument Property 612 ActiveUserClass Property 612 AllSelectedFrames Property 613 Application Property 614 CanAddOrModifyFolders Property 616 CanAddOrModifyUserClasses Property 617 CanCreateNewReports Property 617 CanDirectEnterSQL Property 618 CatalogLevels Property 619 CatalogName Property 620 CatalogOpened Property 620 Columns Property 621 Condition Property 622 ConnectionString Property 623 Count Property 623 CreatorUserClass Property 624 CrossProductPermission Property 625 CSVExportOptions Property 626 DatabaseConnected Property 627 DatabaseConnections Property 628 DatabaseDefinitions Property 629 DatabaseItem Property 629 Databases Property 630 DBObject Property 631 Definition Property 632 DeniedCatalogs Property 632 DeniedColumns Property 633 DeniedFolderItems Property 634 DeniedSchemas Property 635 DeniedSelectValues Property 635 DeniedTables Property 636 Description Property 637 EncryptedPassword Property 638 ErrorDocument Property 639 ExportMetaTags Property 639 ExportOptions Property 640 Filename Property 641 FilteredColumns Property 642 FilteredTables Property 643 FolderItem Property 644 FolderPath Property 645 Folders Property 646 Formula Property 647 FormulaEx Property 647 FormulaExB Property 652 FromPage Property 655 FullName Property 656 HasTextBlobLimit Property 656 IsDistributed Property 657 IsExternal Property 658 IsKey Property 659 IsLocked Property 660 IsSynonym Property 661 Item Property 662 Items Property 663 Administration Guide 15

LeftTable Property 663 MasterCatalogFilename Property 664 MasterName Property 665 MasterTable Property 666 MaxQueryExecutionTime Property 667 MaxRowsRetrieved Property 668 MaxTablesPerReport Property 669 MaxTextBlobCharacters Property 670 MetaDataItem Property 670 MetaDataPath Property 672 MinimizeConnectTime Property 673 Modified Property 674 Name Property 675 NameSpace Property 676 NonIndexSortingPermission Property 677 OuterJoinPermission Property 678 Parent Property 678 Path Property 680 PlainTextPassword Property 680 PublishHTML Property 681 PublishPDF Property 682 QualificationLevel Property 683 QueryExecutionTimeWarnAfter Property 684 QueryItem Property 685 QueryProcessing Property 686 ReadIsolationLevel Property 687 ResultType Property 687 RightTable Property 688 RowsRetrievedWarnAfter Property 689 SchemaLevels Property 690 SelectDistinctPermission Property 691 SelectedFrames Property 692 SQL Property 693 SuppressTOCDuplicates Property 693 TableLinks Property 694 Tables Property 695 TablesPerReportWarnAfter Property 696 TOC Property 697 TOCByPageNumber Property 697 ToPage Property 698 Type Property 698 UniqueID Property 700 UseQueryWarnings Property 701 UserClasses Property 702 UserName Property 703 Value Property 703 Version Property 704 Weight Property 705 WindowState Property 706 Chapter 57: Sample Macros 709 Annotation Sample 709 Add Catalog Prompts Sample 711 Drill-Through Report Sample 712 Open Report Sample 713 Scheduled Job Sample 714 Catalog Content Sample 715

16 IBM(R) Cognos(R) Impromptu(R)

Copy Rows To Notepad Sample 716 Create Catalog Sample 717 Create Empty Catalog Sample 718 Excel Options Upgrade Sample 719 Insert Qualification Levels Sample 720 Products (Publish as HTML) Sample 721 Sales By Country (Publish as HTML) Sample 722 PART 5: Using Expressions with Impromptu 723 Chapter 58: Expressions 725 Building Expressions 725 Functions 726 Summaries 726 Values 726 Operators 727 How to Create Prompts 727 Chapter 59: Functions 729 Access Security Functions 729 Catalog-User-Profile 729 User-Classes 730 User-Name 730 Date and Time Functions 731 Add-Days 732 Add-Months 733 Add-Years 733 Age 733 DateDayDiff 734 Datetime-to-Date 734 Date-to-Datetime 734 Date-to-Days-from-1900 734 Date-to-String 735 Day 735 Day-of-Ymd-Interval 735 Daysint-to-Ymdint 735 Days-from-1900-to-Datetime 736 Days-to-End-of-Month 736 First-of-Month 736 Hour 736 Last-of-Month 737 Make-Datetime 737 Minute 737 Month 738 Month-of-Ymd-Interval 738 Months-Between 738 Number-to-Date 739 Number-to-Datetime 739 Phdate-to-Date 739 Pre50-Months-Between 739 Second 740 Time-to-Zero 740 Today 740 Year-of-Ymd-Interval 740 Year 741 Years-Between 741 Ymdint-Between 741 Ymdint-to-Daysint 742

Administration Guide 17

Numeric Functions 742 Absolute 742 Ceiling 743 Floor 743 Integer-Divide 743 Mod 744 Number-to-Characters 744 Number-to-String 744 Number-to-String-Padded 744 Power 745 Round-Down 745 Round-Near 746 Round-Up 746 Round-Zero 747 Sqrt 747 Text Functions 748 Char_Length 749 Characters-to-Integer 749 Decrypt 749 Encrypt 750 First-Word 750 Left 750 Lower 750 Match-Pattern 751 Octet_Length 752 Pack 752 Position 752 Reverse 753 Right 753 Sound-of 753 Spread 753 String-to-Integer 754 String-to-Number 754 Substitute 754 Substring 754 Trim-Leading 755 Trim-Trailing 755 Upper 755 Chapter 60: Summary Functions 757 Average 758 Count 758 Maximum 758 Minimum 759 Moving-Average 759 Moving-Total 759 Percentage 760 Percentile 761 Quantile 761 Quartile 762 Rank 762 Running-Average 763 Running-Count 763 Running-Difference 764 Running-Maximum 765 Running-Minimum 765 Running-Total 766

18 IBM(R) Cognos(R) Impromptu(R)

Standard-Deviation 766 Tertile 767 Total 768 Variance 768 Chapter 61: Constants and Operators 769 Arithmetic, Logical, and String Operators 769 Arithmetic Operators (+, -, *, /, Div, -(x) ) 770 And Logical Operator 770 Or Logical Operator 770 Not Logical Operator 770 String Operator (+) 771 Comparison Operators 771 Between 771 Comparison Operator Symbols 771 Contains 772 Exists 772 In 772 Is Missing 772 Is Not Missing 773 Like 773 Starts With 773 Chapter 62: Components 775 All 775 Any 776 Auto 776 Dataset 776 Default 776 Descending 777 Distinct 777 For 777 If / Then / Else 777 Lookup 778 Null 778 Prefilter 778 Prompt 778 Replace With (->) 778 Report 779 Sort-by 779 Glossary 781 Index 799

Administration Guide 19

20 IBM(R) Cognos(R) Impromptu(R)

IntroductionThe Impromptu Administration Guide shows you how to design, create, and maintain an Impromptu environment that meets the needs of your users. It explains the importance of analyzing user requirements creating catalogs organizing folders making joins creating calculations, conditions, and prompts setting up user classes and security auditing and optimizing performance automating tasks The last part of the document explains how to use Database Definition Manager to set up database access. Additional reference chapters list the samples included with Impromptu, tips for integrating with other IBM Cognos products, and troubleshooting information. The document concludes with a glossary of terms and an index. For more information about using this product, visit the Cognos Software Services Web site (http://support.cognos.com). For information about education and training, click the Education link from this site. This document is available as online help and an online book. Our documentation includes user guides, tutorial guides, reference books, and other materials to meet the needs of our varied audience.

Online HelpAll information is available in online help. Online help is available from the help button in a Web browser, or the Help menu and help button in Windows products. You can also download the online help from the Cognos Software Services Web site (http://support.cognos.com). The environments.html file accessible from the /support/products part of this Web site lists the operating systems, browsers, Web servers, directory servers, database servers, and OLAP servers currently supported by IBM Cognos products.

Books for PrintingThe information in each online help system is available in online book format (PDF). However, the information from a given help system may be divided into more than one online book. Use online books when you want to print a document or when you want to search the whole document. You can print selected pages, a section, or the whole book. You are granted a non-exclusive, non-transferable license to use, copy, and reproduce the copyright materials, in printed or electronic format, solely for the purpose of providing internal training on, operating, and maintaining the IBM Cognos software. An annotated list of other documentation, the Documentation Roadmap, is available from the Windows Start menu or the Impromptu Help menu. You can also read the product readme files and the installation guides directly from IBM Cognos product CDs.

Administration Guide 21

Introduction

22 IBM(R) Cognos(R) Impromptu(R)

PART 1: Administering Impromptu

Administration Guide 23

24 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing CatalogsA catalog is a file containing the information that Impromptu users need to create reports. Catalogs are central to the operation of Impromptu, and creating a catalog is one of your key tasks as an administrator. A properly-created and maintained catalog results in a reporting environment that makes sense to your users, and makes administering Impromptu easier for you. Catalogs store metadata associated with the database structure including the name and location of the database names of the columns in the selected tables join relationships between tables In addition, a catalog contains folders, conditions, and calculations that you design and create. The folders, conditions, and calculations that you define in your catalog provide report users with a logical view of the physical data (in the database) that the catalog accesses.

What You Can DoAs an administrator, you can create different types of catalogs: personal distributed shared secured As the creator of a catalog, you can change the catalog type select the tables to include define the join relationships between the tables change the names or locations of the folders You can also open a master distributed catalog add tables and columns to a catalog add the same table twice to a catalog sort tables verify and update tables remove tables and columns limit the scope of data for the catalog; see "Control Access to Objects" (p. 71) use keys; see "Control Editing of the Catalog Structure" (p. 75) and "Create Joins Automatically When Creating a Catalog" (p. 45) qualify catalog content; see "Qualify Catalog Content" (p. 34) add calculations, conditions, or prompts; see "Store a Condition or Calculation in a Catalog" (p. 53) and "Create a Catalog Prompt" (p. 57) generate content overview reports generate default reports

How Many Catalogs Should You Create?Impromptu can handle many catalogs. If you have an extremely large amount of data and many tables in your database, then defining more than one catalog might make sense. In this case, a single catalog could be difficult to use. It might take too long to load and navigate the catalog, and join maintenance might become involved.

Administration Guide 25

Chapter 1: Managing Catalogs More catalogs mean more administration and maintenance, though, and as a rule it is advisable to have only one catalog per application per physical location. Impromptus user classes and security features make it easy to manage a wide variety of user requirements with a single catalog.

What Effect Does a Catalogs Size Have?As you include more tables, folders, and security restrictions in a catalog, it becomes larger. The size of a catalog can determine the type of catalog you choose to create. With a distributed catalog, the larger the master distributed catalog the longer it takes to copy the personal distributed catalog to the user machine, the more traffic there is on the network, and the longer it takes to synchronize the master and personal catalogs. There are no internal limits to the size of a catalog; that is, it can contain any number of tables. But large catalogsanything approaching 1 MB in sizecan be inefficient, especially on slower machines. As a catalog increases in size: maintenance time increases it takes longer to load the catalog, which increases the minimum time taken to run a report organizing folders for easy use by users becomes more difficult user class security becomes more involved join definition becomes more complex, with more chance for join loops distributed catalog synchronization takes longer It is difficult to provide an ideal size for a catalog, or a maximum number of tables in a catalog. It is best to add incrementally and check performance at each step, testing it on a typical end user machine for a real world feel.

Things to Consider Before Creating a CatalogBefore starting to create a catalog, you should be able to answer these questions: 1. What information do you want to include in the catalog? For example, do you want to see data for a particular division of your company? If you are interested in creating reports that present only marketing information, then you probably need to access only the tables in the database that contain marketing data. 2. Do you want to create a catalog that contains all the tables from the database, or only selected tables? Including all the tables is easier, because you do not have to specify the individual tables to include. But if you include tables that arent needed, processing efficiency might decrease, and it might be more difficult for users to find information they need. As a rule, include only those tables that must be accessible to users. Some database tables are used strictly for linking other tables. They should be included in the catalog for join purposes, but excluded from the folders. The range of tables to include in any one catalog should always be based on user and reporting requirements. 3. What table joins do you want to set up? When you create a catalog, you can join tables so users can retrieve data from more than one table at a time. Do you want Impromptu to create the joins for you automatically? Do you want to use keys and/or same-named columns for the automatic joins? If you do not want Impromptu to create the joins for you, do you know the joins you want to create? Although you can edit joins at any time, before starting to create a catalog you should have a join strategy in mind.

Some Catalog TipsHere are some tips to keep in mind when creating and maintaining catalogs: Advise users to save their personal distributed catalogs with the same name as the master. This will minimize confusion that can arise when users have many catalogs in their directory and need to determine which master catalog is being referenced.

26 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing Catalogs The more folders a catalog has, the larger it is. Avoid repeating folders as subfolders in many different folders. Each copy of the folder increases the size of the catalog. This must be balanced with the need to make the catalog valuable to the user and the need to ease the administrative burden. Denying access to a lot of folders for a large number of user classes increases the size of the catalog. When a user has access to a folder, no overhead is added, but denying access increases catalog size. You should monitor this feature closely.

Create a CatalogBefore you can create a catalog, you must have access to the data in the database, and you must define a logical database so that Impromptu can access the data. For more information about defining database connections, see "Create a Database Definition" (p. 109). Once you create a database definition, you can create the type of catalog that best meets the needs of your intended users: personal, distributed, shared, or secured. During catalog creation, you must add tables from the database and define the join relationships. Impromptu automatically creates a default set of folders based on the tables that you add from the database. You can also define Impromptu keys that provide additional information for joining tables. Once you have done this, you must define folders, folder items, and any calculations and conditions that you require.

Steps1. From the Catalog menu, click New. 2. In the File name box, type a file name for the catalog. Note: Impromptu automatically adds the extension .cat to catalog names and stores catalog files in the same folder as your Impromptu application. Specify your own default catalog folder using the File Locations tab (Options command, Tools menu), to override this setting. 3. In the Description box, type a description of the catalog. Note: A catalog description is optional. 4. In the Catalog type box, click the type of catalog you want to create: personal, distributed, shared, or secured. 5. In the Name box, select the database the catalog will access. Note: If the database definition does not exist, then you must create it. Click Databases if you want to add a new database definition, and then click OK to return to the New Catalog dialog box. 6. Select the tables you want to include in the new catalog by using one of the following options: Click Select tables and select the database tables that you want to include in the new catalog. Click Include all tables. Impromptu creates your catalog with all the database tables, builds, default joins, and creates two user classes: Creator and User. For more information, see "Add Tables to a Catalog" (p. 31). 7. Click OK. 8. If you are using a database that requires a user ID and password, in the Database User ID and Database Password boxes (Catalog Login dialog box), type your user ID and password. 9. In the Joins dialog box, define any necessary table joins, and then click OK. For more information, see "Work with Joins" (p. 41). 10. In the User class box (Catalog Login dialog box), select the Creator user class, and then click OK. Note: You should make a backup copy of the catalog after you create your catalog, and before you change it further. 11. Continue to develop your catalog by adding folders; see "Create a New Folder" (p. 37) and "Copy and Paste Folders" (p. 38) moving folder items; see "Move Folders and Items in Folders" (p. 38)

Administration Guide 27

Chapter 1: Managing Catalogs adding conditions or calculations; see "Working with Stored Conditions and Calculations" (p. 51) 12. If you created a distributed, shared, or secured catalog, then place the catalog in a physical location accessible by your users.

Change the Catalog TypeWhen designing catalogs, it is best to set the catalog type based on users reporting needs. For example, if youre creating a catalog for users accessing canned reports and who will never change the reports, create a secured catalog. If youre creating a catalog for users who want to add their own custom folders, create a distributed catalog and set up the appropriate user classes. If necessary, you can change one catalog type to another by changing certain attributes within the catalog. For example, if youve developed a personal catalog and later want to enable access by other users, you can change the personal catalog into a shared or distributed catalog.

What is a Personal Catalog?A personal catalog is a catalog that you do not intend to make accessible to other Impromptu users. You can create personal catalogs to access databases from which you require personal reports. Personal catalogs are for individual use only and there are no security considerations. Only the creator of the catalog can access the data referenced by the catalog.

What is a Distributed Catalog?A distributed catalog consists of an original master distributed catalog that the administrator controls and maintains, and one or more local catalogs (personal distributed catalogs) that are created when users open the master distributed catalog. Distributed catalogs are useful to maintain a certain level of control over the catalog, but allow catalog users to create their own local copies of the catalog. Once users have created their own personal distributed catalog, they can modify it to meet their own specific reporting needs. You can enable your users to modify their personal distributed catalogs by assigning them privileges to modify folder structures and add conditions and calculations. With a distributed catalog, you can enable the Folders and User Profiles commands so that your users can organize the contents of their personal distributed catalog by creating new folders create conditions and calculations to store in their catalog disconnect from the LAN and continue to work with their personal distributed catalog connect and disconnect from the database create user classes, set passwords, and control the user environment manage the impact on the network and database Impromptu maintains a link with the master distributed catalog and automatically updates the linked personal distributed catalogs with changes to the master. This enables users to change the contents of their personal distributed catalog, while ensuring that changes to the master are applied to their personal distributed catalogs. Note: Users cannot change the names or locations of the folders that the administrator adds to the catalog.

What is a Shared Catalog?A shared catalog is generally stored on a LAN to provide access for all users. When you open a shared catalog, you are working with the same copy of the catalog as all other users. The creator of a shared catalog can change and grant privileges to specific users that enable them to organize the contents of the catalog by creating new folders, editing folders you've created, and changing their location work with conditions and calculations stored in the catalog 28 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing Catalogs connect and disconnect from the database

Note: No more than 20 users can be simultaneously connected to a read-write shared catalog. This is a limitation of Microsoft's implementation of compound files. As a workaround, you could use a distributed catalog. However, there are situations where distributed catalogs will not work well (for example, getting an updated copy of a large master distributed catalog with a slow dial-up access). You could also make the shared catalog read-only as another workaround. You may negate some user privileges if you do this. The same problem occurs for reports for the same reason.

What is a Secured Catalog?A secured catalog is a read-only catalog that only the Creator can change. Use secured catalogs to deploy "canned" reports for audiences that have no need to customize their reports. Users cannot change a secured catalog, and they cannot create or change any of the reports based on the catalog. Users can only view, run, export, and print reports. In addition, users can connect to and disconnect from the database.

Catalog AttributesThe following table outlines the default attributes of each catalog type. You can add new user classes and modify existing ones. Use the descriptions in this table to determine the catalog type that meets your user's needs: Catalog Type Personal Attributes Has only the Creator user class with full privileges. You can: create and edit reports edit folders add and modify user classes create type-in SQL reports Has a Creator and a User user class. The Creator user class has full privileges. The User user class can: create and edit reports add and modify user classes Distributed Has a Creator and a User user class. The Creator user class has full privileges. The User user class can: create and edit reports edit folders add and modify user classes Only this type is designated as a Distributed catalog in the Catalog Properties dialog box. Secured Has a Creator and a User user class. The Creator user class has full privileges. The User user class can view reports, but it can't create reports, change reports, or make changes to the catalog.

Shared

Administration Guide 29

Chapter 1: Managing Catalogs

Managing Catalog AccessIf you change a personal catalog to any other catalog type, it is likely that it will contain only the Creator user class (although Personal catalogs can contain multiple user classes). To grant others access to the catalog, you must add other user classes. By default, Impromptu adds a user class named User automatically. For more information, see "Create a User Class" (p. 65). It is unusual to change a shared, distributed, or secured catalog to a personal catalog. You can do so only by deleting all user classes other than the Creator user class. If you change a shared, secured, or distributed catalog to another catalog type, you must change the governor settings for all user classes other than the Creator. For more information, see "Control Editing of the Catalog Structure" (p. 75) and "Control Creating and Editing Reports" (p. 75).

Steps1. To change a distributed catalog, click the Properties command from the Catalog menu, and then click or clear the Make this a distributed catalog check box. This change is in addition to any changes required for user classes. 2. If you change a distributed catalog into either a shared or a secured catalog, you must inform the catalog users. Their local copies of the distributed catalog are no longer linked to a master distributed catalog. 3. If you change a shared or secured catalog into a distributed catalog, inform the catalog users that they will be prompted to save their own local copy of the catalog.

Controlling Catalog Access Using a Catalog LockLocking a catalog is another method of controlling catalog access. A locked catalog restricts access to the Creator user class. When a member of the catalog Creator user class attempts to open a locked catalog, they must provide password authentication before opening it. A catalog lock is specified by the administrator and exists in addition to the common logon authentication required by IBM Cognos products and the catalog logon authentication. If a user who is not a member of the Creator user class attempts to open a locked catalog, they are prompted for the usual catalog logon credentials only. For more informaton, see "Control Access Using a Catalog Lock" (p. 73).

Open a Master Distributed CatalogOnly the Creator user class can change the master distributed catalog. When any user class other than the Creator user class opens a master distributed catalog, Impromptu makes a copy of the catalog. This copy is known as a personal distributed catalog. By default, every time a personal distributed catalog is opened, Impromptu automatically updates it with changes to the master distributed catalog. Catalog users can change their default settings so that they must manually update their personal distributed catalogs. You can have only one catalog open at any one time. To open a different catalog, you must first close the current catalog. You should disconnect personal distributed catalogs used in Impromptu Web Reports from the master distributed catalog. For more information about preparing reports for Impromptu Web Reports, see the Impromptu User online help.

Steps1. 2. 3. 4. From the Catalog menu, click Open. Click the catalog and click Open. From the User class box, click the user class to which you have access. If required, type your catalog password. Note: Impromptu asks you to name your copy of the catalog.

30 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing Catalogs 5. In the Database User ID and Database Password boxes (Catalog Login dialog box), type your user ID and password if you are using a database requiring a user ID and password. 6. In the File name box, type a name for your personal distributed catalog and click OK.

Add Tables to a CatalogA table in a database is a portion of the database containing one kind of information organized into rows and columns. You can add one or more tables from the active database to a catalog. When you add a table, a folder is automatically created. When the database metadata changes, you can update tables in the catalog instead of re-creating the catalog. You can attach filters to tables or columns to filter out data before showing the report. These filters are automatically added to the report's own filter when the report is executed. You cannot have any reports open when you add tables to a catalog. Adding and removing tables and columns can cause existing reports to fail. Validate existing reports and regenerate them to ensure they work correctly.

Steps1. From the Catalog menu, click Tables, and then click the Edit tab. Note: If you add tables when you create a new catalog, the Tables dialog box appears automatically. 2. In the Database tables box, select the table or tables to add to the catalog. 3. Click Add. 4. In the Create joins box, select one of the following options to specify how to create table joins: To open the Edit tab (Joins dialog box) where you can create the table joins, click Manually. This is the default. To join the tables using the columns that are defined as keys, click Using key only. To create no table joins, click None. You can manually create them later. To join the tables using the columns that are defined as keys or the first column that is named the same in each table, click Using key or First matching column. Note: Impromptu matches tables using keys. If one table has a key and the other table does not, Impromptu searches for a column with the same name and data type as the key. If there are no keys in either table, Impromptu matches columns based on the first matching name and data type that it finds. For more information, see "Work with Joins" (p. 41). 5. If you are using keys, define keys for the tables. Note: You do not need to define keys if your database has indexed columns. 6. Qualify the tables to control how tables are qualified for SQL queries, and click OK.

Add Columns to a CatalogYou can add one or more columns from the active database to a catalog. You can add columns when you create a new catalog to an existing catalog if changes to the database affect the catalog if your catalog requirements change

Steps1. From the Catalog menu, click Tables, and then click the Edit tab. 2. In the Database tables box, double-click the database owner and table containing the column or columns to add. Administration Guide 31

Chapter 1: Managing Catalogs Note: Your database may not have a database owner. 3. Select the column or columns to add to the catalog. 4. Click Add. A folder for the table is created in the Catalog tables box. The folder contains only the column or columns you selected. 5. In the Create joins box, select one of the following options to specify how to create the table joins. To open the Edit tab (Joins dialog box) where you can create the table joins, click Manually. This is the default. To join the tables using the columns defined as key, click Using key only. To create no table joins, click None. You can manually create them later. To join the tables using the columns defined as keys or the first column that is named the same in each table, click Using key or First matching key. Note: Impromptu matches tables using columns that have been defined as keys. If one table has a key and the other table does not, then Impromptu looks for a column with the same name and data type as the key. If there are no keys in either table, Impromptu matches columns with the same name and data type. For more information, see "Work with Joins" (p. 41). 6. If you are using keys, define keys for the tables. Note: You do not need to define keys if your database has indexed columns. 7. Qualify the tables if you want to control how tables are qualified for SQL queries, and click OK.

Add the Same Table Twice to a CatalogYou can create a table alias to add a second copy of a table to the same catalog. The second copy of the table is added with a different name. You can create table aliases to relate values within a single table. For example, if all the employee data is in one table in the database, you can find out which employee works for which manager by adding the table, then adding an alias of the table, and finally creating a self-join between the table and alias. use in a filter expression. This enables you to create a view of part of the data in a table. For example, you can create a view for managers of the Employee table by aliasing the Employee table under the name of Managers, and then use a filter expression on the Managers table to create the view.

Steps1. 2. 3. 4. 5. From the Catalog menu, click Tables, and then click the Edit tab. In the Database tables box, click the table to add to the catalog. Click Alias. Type the alias name and click OK. Click OK.

Sort Tables in a CatalogYou can sort tables to better manage large catalogs. Tables can be sorted alphabetically in ascending or descending order or restored to the original sort order.

Steps1. From the Catalog menu, click Tables. 2. Click a table, then click the sort icon at the top of the appropriate list box. To sort the database tables, click a table in the Database tables box. To sort the catalog tables, click a table in the Catalog tables box. 32 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing Catalogs 3. From the drop-down list, click to sort the items in ascending order (a to z) to sort the items in descending order (z to a) to restore the default order 4. Click OK.

Verify and Update TablesIf the database has changed, you can ensure that the catalog contains the most up-to-date information by: Verifying that the owners, tables, and columns in the open catalog still exist in the database. If an owner, table, or column is not in the database, you can rename or remove it from the catalog so the catalog matches the new database information. Updating a table in a master distributed catalog with the most current database information. You need to update the tables in the master distributed catalogs if the table definition in the database changes. You cannot have any reports open when you verify or update tables.

Steps to Verify the Tables in a Catalog1. From the Catalog menu, click Tables. 2. In the Catalog tables box, click the table or tables to verify. 3. Click Verify. Note: If the table or a column within the table is no longer in the database, an X appears over the table icon. 4. Remove or rename the table or column in the table to fix the error, and click OK. For more information, see "Remove Tables and Columns" (p. 33).

Step to Update a Table with the Current Database Information From the Catalog menu, click Update Catalog. Note: If Impromptu cannot find the source catalog, you are be prompted to locate and select the source catalog in the Update: Source Catalog Not Found dialog box.

Remove Tables and ColumnsA table is a portion of the database containing a set of information systematically arranged into rows and columns. A catalog identifies the tables in the database you can access using Impromptu. You remove tables and columns from an existing catalog if changes to the database affect it, or if your catalog requirements change. When you remove a table or column, Impromptu prevents you from using invalid data by automatically removing all associated folders, folder items, conditions, joins, and calculations. You cannot have any reports open when you remove tables and columns.

Steps to Remove a Table from a Catalog1. From the Catalog menu, click Tables, and then click the Edit tab. 2. In the Catalog tables box, click the table or tables to remove. 3. Click Remove. Note: When you click Remove, Impromptu lists the columns and governor restrictions for the table you are removing as well as the tables that are joined to the table that you are removing. 4. Click OK to remove the selected items, and click OK again.

Steps to Remove a Column from a Catalog1. From the Catalog menu, click Tables, and click the Edit tab. Administration Guide 33

Chapter 1: Managing Catalogs 2. In the Catalog tables box, double-click the database owner and the table containing the column or columns to remove. Note: Your database may not have a database owner. 3. In the Catalog tables box, select the column or columns to remove. 4. Click Remove. Note: When you click Remove, Impromptu lists the columns you are removing, their governor restrictions, and any joins for those columns. 5. Click OK to remove the column, and click OK again.

Qualify Catalog ContentYou can qualify tables in SQL queries to ensure your users access the correct tables. You can use unqualified table names to control how selected tables are qualified in SQL queries. You can qualify tables with information on database, owner, table, and column. By default, tables are fully qualified. You can set up an annotation server to help users identify their tables and columns. For more information, see "Unable to Identify Catalog Contents" (p. 177). You can remove the qualifiers for table names at any time.

ExamplesYou have a catalog of sales data, containing one table called sales, that you are testing before releasing it to your end users. You qualify the table as .., identifying the table as belonging to the test database and to the yourname owner. When you are ready to release the catalog, you remove the database and owner qualifiers to identify the table only as sales. The Great Outdoors Company has acquired a small operation that manufactures one of its product lines. When the two sets of data are combined at the database level, the data will contain many of the same names as your data at headquarters. You can maintain one catalog for the two regions by qualifying the tables for the different locations, so that the users will access the correct tables even though they are named the same.

Steps1. From the Catalog menu, click Tables, and then click the Qualification tab. Note: If your database does not support qualified tables, the Qualification tab is unavailable. This tab is enabled for only the Creator. 2. In the Catalog tables box, click the table to qualify. 3. To add qualifiers to the table, click Qualify more. Note: Each time you click Qualify more, another qualifier is added to the table, starting with the lowest level available to the selected table. The levels of qualifiers from the lowest to highest level are column (lowest level), table, owner, and database (highest level). 4. To remove qualifiers from the tables, click Qualify less. Note: Every time you click Qualify less, another qualifier is removed from that table, starting with the highest level applied to the selected table. 5. Click OK when you finish qualifying tables.

Generate a Content Overview ReportYou can generate a content overview report on the contents of the current catalog. The report is saved as an ASCII text file. A content overview report describes catalog information, such as the catalog name and location information about the database, such as the database's logical name, the physical name, and the type of database

34 IBM(R) Cognos(R) Impromptu(R)

Chapter 1: Managing Catalogs database tables and columns folders in the catalog and the items in each folder table joins information about user classes, such as the name and access privileges of each user class

You can use this report to track your catalog as you create or edit it. You can also give users a hard copy so that they can see changes to the catalog. You can only generate a content overview report for a catalog that you created.

Steps1. From the Catalog menu, click Content Overview. 2. In the Files name box, type a name for the report. Note: If you do not specify an extension, Impromptu automatically adds the extension .icr. 3. From the Save as type box, do one of the following: To save a content report, click Content Report (*.ICR). To save a different file type, click All files (*.*). 4. Click the drive and directory where you want to store the report. 5. Click Options, and click one of the following: To include information about the table joins, click Table join expressions. To include the tables and columns, and their associated conditions, click Tables and columns. To include folders and folder items, including conditions and calculations, click Folder hierarchy. To include information about user classes, click User class information. 6. Click OK, and then click Save.

Generate Default ReportsYou can generate default reports for selected tables, and then use the reports as the starting point for creating custom reports.

Steps1. 2. 3. 4. 5. 6. 7. From the Catalog menu, click Generate Reports. In the Tables box, click a table. Click Generate. If the template on which you want to base the report appears in the FastFind tab, double-click the template. Click Browse to find a template in another location. Repeat steps 2 to 5 to generate a report for another table. Click Close. The report is assigned the name of the table from which it was generated, and an .imr extension is added to the name. From the File menu, click Open to view the report.

8.

Administration Guide 35

Chapter 1: Managing Catalogs

36 IBM(R) Cognos(R) Impromptu(R)

Chapter 2: Work with Folders and Folder ItemsWhen you create a catalog, each table in the catalog appears as a folder containing the table's columns. You can also store conditions, calculations, and catalog prompts in folders. Folders enable you to organize data items logically, according to users needs, rather than based on how tables and views are physically stored in the database. This logical rendering of information enables report users to access information without knowing about the tables, indexes, and joins that comprise a typical corporate database.

What You Can DoYou can modify and organize the folders and items in the folders. You can create new folders and show the contents of a folder copy folders and folder items and paste them in new locations move and remove folders and folder items rename folders and folder items sort folders and folder items generate a folder from a report use folder items to synchronize catalog changes to reports

Who Can Modify and Organize Folders and Folder Items?If the user is using a Catalog that they created Personal distributed catalog, and you have enabled the user to use the Folders command Then the user Can create, modify, and organize folders and folder items in the catalog. Can create, modify, and organize folders and folder items in the catalog. The user cannot modify and organize folders you create. Personal distributed catalog, and you have prevented the user from using the Folders command Shared catalog, and you have enabled the user to use the Folders command Shared catalog, and you have prevented the user from using the Folders command Secured catalog Cannot create, modify, and organize folders and folder items. Can create, modify, and organize folders and folder items in the catalog. Cannot create, modify, and organize folders. Cannot create, modify, or organize folders.

Create a New FolderFolders are an important administrative tool because they enable you to logically organize data items based on users needs, rather than on how physical tables are structured in the database. You can reorganize the contents of your catalog by creating new folders that reflect the different needs of your business.

Administration Guide 37

Chapter 2: Work with Folders and Folder Items For example, while the GOSales database contains tables such as OrderDetails, SalesStaff, and Sales_Branch, your sales managers are interested in Sales Orders and Locations. To make it easier for users to find the data they need, you can reorganize the folders into a "business view" that is meaningful. You create a Locations folder to hold the SalesStaff and SalesBranch information. You can generate a folder containing a table that you want added from the database to your catalog. When you select a table, Impromptu creates a folder with the name of the table and adds it to your folders list. If a folder exists with the same name, Impromptu adds a number to distinguish it from the existing folder. There should be a balance between the number of levels of subfolders in a catalog and the number of items in any one folder. If there are too many items in a folder, users must scroll through lists of items to find what they need.

Steps1. Close any open reports. 2. From the Catalog menu, click Folders. 3. Click Folder. To create a new folder inside an existing one, open and select the existing folder before clicking Folder. 4. In the Name box, type the name of the new folder, and click OK.

Copy and Paste FoldersYou can copy folders and paste them in new locations. You can reorganize the contents of your catalog to make it more intuitive. Changes to the original folder do not affect copies of the folder.

Steps1. Close any open reports. 2. From the Catalog menu, click Folders. 3. To copy a folder or item in a folder, do one of the following: To copy a folder, click the folder and click Copy. Click the folder that you want the copied folder to appear above, click Paste, and then click OK. To copy an item in a folder, select the item and click Copy. Click the folder into which you want to copy the item. Click Paste, and click OK.

Move Folders and Items in FoldersYou can move folders and items to new locations that are more intuitive. To move folders or folder items, cut and then paste them. Your reports should still work. If you copy and paste folders or folder items and then delete the original items, your reports based on that catalog may fail. You can control how reports are synchronized to a changed catalog. For more information, see "Synchronize Catalog Changes to Reports" (p. 40).

Steps to Move Folders1. 2. 3. 4. 5. 6. Close any open reports. From the Catalog menu, click Folders. Click the folder you want to move. Click Cut. Click the folder you want the folder to appear above. Click Paste and click OK.

Steps to Move Items in Folders1. Close any open reports.

38 IBM(R) Cognos(R) Impromptu(R)

Chapter 2: Work with Folders and Folder Items 2. 3. 4. 5. From the Catalog menu, click Folders. In the Catalog folders box, select the item you want to move, and click Cut. Select the folder into which you want to move the cut item. Click Paste and click OK.

Remove Folders and Items in FoldersYou can remove folders and items in folders. Folder items can include conditions, calculations and prompts. Removing tables or columns can cause existing reports to fail. You can control how reports are synchronized to a changed catalog. For more information, see "Synchronize Catalog Changes to Reports" (p. 40).

StepsClose any open reports From the Catalog menu, click Folders. Click the folder or item you want to remove. Click Cut. Note: If you remove something by mistake, click Undo to reverse the Cut command. 5. Click OK. 1. 2. 3. 4.

Rename Folders and Items in FoldersYou can rename folders and items to make their names more intuitive. For example, suppose you have a Date column name in every table, but it means something different in each case. It might be the Order Date in the Orders table, the Product Release Date in the Products table, and the First Order Date in the Customers table. In this case, to eliminate the possibility of confusion, use different names in the various folders to identify exactly what each date represents. Renaming tables or columns can cause existing reports to fail. You can control how reports are synchronized to a changed catalog. For more information, see "Synchronize Catalog Changes to Reports" (p. 40).

Steps1. 2. 3. 4. 5. 6. Close any open reports. From the Catalog menu, click Folders. Click the folder or item you want to rename. Click Edit. In the Name box, type a new name or edit the current name and click OK. Click OK and click OK again.

Sort Folders and Items in FoldersYou can sort folders and folder items to better manage large catalogs. Folder and folder items can be sorted in ascending or descending alphabetical order or restored to the original sort order.

Steps1. 2. 3. 4. Close any open reports. From the Catalog menu, click Folders. Click a folder or folder item, then click the sort icon at the top of the Catalog folders list box. From the drop-down list, click to sort the items in ascending order (a to z) to sort the items in descending order (z to a) Administration Guide 39

Chapter 2: Work with Folders and Folder Items to restore the default sort order When you select a folder and the folder is open, Impromptu sorts only the folders and folder items immediately beneath the selected folder. It does not sort items in any nested folders. the folder is closed, Impromptu sorts the folder and folder items at the same level in the folder hierarchy. 5. Click OK.

Generate a Folder from a ReportYou can generate a folder with a set of columns and conditions from a report. This is a quick way to create and test calculations. For information about creating conditions and calculations, see "Working with Stored Conditions and Calculations" (p. 51).

Steps1. Close any open reports. 2. From the Catalog menu, click Folders. Note: If you want to store the new folder in a specific folder, open the folder. 3. Click Generate. 4. In the Generate Folder dialog box, click Report, and then click OK. 5. Click the report containing the columns and conditions you want, and click Open. Note: To add more reports, repeat steps 3 to 5. 6. Click OK.

Synchronize Catalog Changes to ReportsWhen you open an Impromptu report using a modified or different catalog than the one the report was created with, Impromptu attempts to update the report item references to match the folder items. Matching occurs as follows: match on the unique, internal ID if the internal ID is not found, match on the full folder path and folder item name Matching on the first folder item with the same name as the report item is not attempted. It can be useful to match on the first folder item with the same name as the report item if you reorganized the catalog folder structure and want the report to use the new references. To enable matching in this way, you must add the Allow Lazy Path Matching=T setting to the [Startup Options] section of the Impromptu.ini file. If you add this setting, ensure that all folder items in your catalog have unique names.

40 IBM(R) Cognos(R) Impromptu(R)

Chapter 3: Work with JoinsJoins relate the data in one table to the data in another table in the same database, enabling you to retrieve data from more than one table at a time. For example, you may want to see the purchases made by each customer, but the information about purchases is stored in the Sales table and the information about customers is stored in the Order table. You create joins between these two tables so you can create reports that use columns from each of these tables. A join can have several columns linked together. The relationship between the columns is called a link. You can create different types of joins manually. This is the default setting. create joins automatically when creating a catalog by using keys or the first matching column; see "Create a Catalog" (p. 27). create joins automatically after a catalog has been created. To sort a list of joins, click a join, then click the Sort icon at the top of the list box. Select one of the Sort commands. You can sort joins in ascending or descending alphabetical order or you can restore the original sort order. When deciding what joins to make, you might want to consider alternatives to some joins. For example, instead of trying to manipulate joins, you might want to create more simple reports that users can drill between, or to create sub-reports. If you create an incorrect join, reports show unexpected or incorrect results. Correct joins ensure that no cross-product queries are created. Once you specify all the joins, all reports automatically reflect these joinseven reports created before the joins were specified.

Choosing the Appropriate Joint TypeWhen you create a catalog, Impromptu prompts you for join information. Impromptu creates equijoins by default. You can create these types of joins: Type of Join Equijoin Non-equijoin Operator = Equal Not Equal To < Less Than > Greater Than = Greater Than or Equal To Self join Outer Join Compound Join Complex Join Any of the above Not applicable Not applicable Not applicable "Create a Self Join" (p. 44) "Create an Outer Join" (p. 43) "Create a Compound Join" (p. 44) "Create a Complex Join" (p. 44) See this description "Create an Equijoin" (p. 42) "Create a Non-equijoin" (p. 42)

Administration Guide 41

Chapter 3: Work with Joins

Create an EquijoinAn equijoin retrieves all the rows from one table that have matching rows in another table. For example, the linked columns of a databases Products and Orders tables are the two columns for Product Number. An equijoin between these tables would match up equal values in the two Product Number columns, then retrieve rows from both tables that contain these values. All of the possible combinations of these rows could then be reported on. Impromptu creates equijoins by default when Impromptu automatically creates joins. You can also create equijoins manually.

StepsFrom the Catalog menu, click Joins, and then click the Edit tab. In the Join paths box, click the table to which you want to add a join. In the Available tables box, click the other table in the join. Click Add. Decide whether you are going to create the equijoin as a diagram or expression. In the View as box, click Diagram. Or, to create an equijoin by defining an SQL expression, click Definition and create the join expression using the Join components and Join definition boxes. 7. Click the column in each table you want to link. 8. Click Add Link. Note: If you link more than four columns in a table join, Impromptu automatically shows the links as an expression. 9. Click OK when you have completed adding the links. 1. 2. 3. 4. 5. 6.

Create a Non-equijoinA non-equijoin retrieves all the rows from one table that meet the criteria in another table. The criteria are based on expressions you define using the following operators: < > = Not Equal To Less Than Greater Than Less Than or Equal To Greater Than or Equal To

ExampleTo create a Sales Representative report showing which representatives sold more than a given representative, you could compare the values in the sales amount columns with a non-equijoin. You would use the greater than (>) operator, together with a self join (see "Create a Self Join" (p. 44).)

Steps1. 2. 3. 4. 5. 6. 7. From the Catalog menu, click Joins, and then click the Edit tab. In the Join paths box, click the table to which you want to add a join. In the Available tables box, click the other table in the join. Click Add. Decide whether you are going to create the non-equijoin as a diagram or expression. In the View as box, click Diagram. Click the column in each table you want to link.

42 IBM(R) Cognos(R) Impromptu(R)

Chapter 3: Work with Joins 8. Click Add Link. Note: If you link more than four columns in a table join, Impromptu automatically shows the links as an expression. 9. From the box between the two columns, select a non-equijoin operator to make the table join a non-equijoin, and then click OK. You can create a non-equijoin by defining an SQL expression. Follow the same procedure as creating a non-equijoin by diagram except click Definition instead of Diagram to create the join expression using the Join components and Join definition boxes. Impromptu show joins as definitions for joins with more than four columns linked together.

Create an Outer JoinAn outer join retrieves rows from two or more tables even if there are no matching rows. An outer join can be a left, right, or full outer join. The side of the outer join determines which rows are reported on, even if there is no match. To determine whether to create a left or right outer join, you must know the relationship between the two tables you are joining. The outer join goes on the master side (which returns all rows), not the detail side. A full outer join retrieves all the rows from both tables, as if you combined a left outer join with a right outer join. To enable outer join processing, in the Access tab of an open report (Query dialog box, Report menu), ensure that the Include Missing Table Join Values (Outer Join) check box is selected. If Impromptu is required to assist a database that provides less than complete support for the SQL-92 definition of outer join processing, this can slow performance. When defining outer joins, please refer to your vendor documentation.

ExampleFor each customer there may not always be a sale. Sales information is stored in one table and Customer information in another table. When Impromptu retrieves the data from both tables, it shows the customer record even if a customer has not bought an item.

Steps1. 2. 3. 4. 5. From the Catalog menu, click Joins, and then click the Edit tab. In the Join paths box, click the table to which you want to add a join. In the Available tables box, click the other table in the join. Click Add. In the View as box, click Diagram or Definition, and do one of the following: Goal Action

click both Outer Join check boxes To retrieve all records from both tables and merge records where matches are found (a full outer join) To retrieve all records from the table on the left, matched or not, and the matching values from the table on the right (a left outer join) as a diagram, click the Outer join check box on the left as a definition, click the Outer Join check box on the top as a diagram, click the Outer join check box on the right as a definition, click the Outer join check box on the bottom

To retrieve all records from the table on the right, matched or not, plus the matching values from the table on the left (a right outer join) 6. Click OK.

Administration Guide 43

Chapter 3: Work with Joins

Create a Self JoinUse self joins to add two or more references to the same table in a catalog, so you can compare data in a column to other data in the same column, and thereby understand how they are related. To create a self join, you create an alias table based on a source table, so you have two tables with the same contents but different names. You then join the alias table to its source, either with an equijoin, a non-equijoin, or an outer join.

ExampleYour employee data is in one database table called Employees, and you want to find out which employees work for which manager. You add the Employees table to the catalog and then alias the Employees table to add it a second time. You call the aliased table Managers and create a join between the Employees table and the Managers table. You create a report showing which employees work for each manager.

Steps1. Add a table to the catalog, and then add the same table again using an alias. For more information, see "Add the Same Table Twice to a Catalog" (p. 32). 2. From the Catalog menu, click Joins, and then click the Edit tab. 3. In the Join paths box, click the table to which you want to add a join. 4. In the Available tables box, click the table alias. 5. Click Add. 6. Create an equijoin, non-equijoin, or outer join for the tables, and click OK. To create an equijoin, see "Create an Equijoin" (p. 42). To create a non-equijoin, see "Create a Non-equijoin" (p. 42). To create an outer join, see "Create an Outer Join" (p. 43).

Create a Compound JoinA compound join is a join containing several columns joining two tables. Compound joins are useful where multiple columns generate a unique key to join to another table. Sometimes a relational database has a unique key, which is a combination of two or more columns. A compound join can be used in combination with a non-equijoin, a self join, or an outer join.

ExampleYou join the Customer table and the Outlet table by linking city and state. You type the following:CUSTSITE.CUST_NO=ORDER.CUST_NO and CUSTSITE.SITE_NO=ORDER.SITE_NO

Steps1. 2. 3. 4. 5. 6. From the Catalog menu, click Joins, and then click the Edit tab. In the Join paths box, click the table to which you want to add a join. In the Available tables box, click the other table in the join. Click Add. In the View as box, click Definition. In the Join component box, double-click the join components to add them to the join expression. Separate each table join with "and" or "or". Note: In the Join components box, double-click the Database component to add columns to your join expression. 7. Click OK.

Create a Complex JoinA complex join uses an expression to join two tables. 44 IBM(R) Cognos(R) Impromptu(R)

Chapter 3: Work with Joins They are not used frequently, although they can be used in conjunction with a non-equijoin or outer join. Use a complex join, for example, when your organization acquires another organization, and you must consolidate information for the two organizations.

ExampleYou need to join the product code in the Sales table to the product code in the Products table. However, the product code in the Sales table is four digits long while the product code in the Products table is the same four digits plus two more digits for the branch code. You use a complex join to link these columns.Sales.prod_code = Substring (Products.prod_code, 1,4)

Limitations Associated with Complex JoinsComplex joins cannot use summary calculations. You can use only simple expressions within a complex join. When defining complex joins, be aware of the interaction between Impromptu and the database. If you use internal expressions and functions that are calculated by Impromptu rather than the database, this could slow computer performance.

Steps1. 2. 3. 4. 5. 6. From the Catalog menu, click Joins, and then click the Edit tab. In the Join paths box, click the table to which you want to add a join. In the Available tables box, click the other table in the join. Click Add. In the View as box, click Definition. In the Join components box, double-click the join components to add them to the join expression. Note: In the Join components box, double-click the Database component to add columns to your join expression. 7. Click OK.

Create Joins Automatically When Creating a CatalogWhen you create a catalog, you can create joins automatically using keys or the first matching column. Impromptu keys do not have to correspond to database keys. Define Impromptu keys for the tables if you want to use keys to join tables, and if the columns are not indexed in the database.

ExampleYou define Cust-id as the key of the Customer table because there can be only one customer number for each customer.

Steps to Define a Join Using a Specific Key or Column1. 2. 3. 4. From the Catalog menu, click Tables, and then click the Edit tab. In the Database tables box, click the tables to add to the catalog. Click Add. In the Create joins box, click one of the following options to specify how to create the table joins: To join tables using the keys you define, click Using key only. To join tables using the keys you define or the first column with the same name and data type, click Using keys or first matching column.

Administration Guide 45

Chapter 3: Work with Joins If both tables have keys defined, Impromptu joins the tables using keys. If one table has a key, Impromptu looks for the first column with the same name and data type in the second table. If neither table has a key, Impromptu looks for the first column in each table with the same name and data type. 5. Click OK. 6. Ensure that the joins created by Impromptu are valid.

Steps to Define New Keys for a Table1. From the Catalog menu, click Tables, and then click the Edit tab. Note: If you are defining keys while creating a catalog, the Tables dialog box appears as part of creating a catalog. 2. In the Catalog tables box, double-click a table to open it. 3. Click the column or columns you want to define as keys. Note: Impromptu shows a key icon next to the selected column name. 4. Double-click the table to close it. 5. Repeat steps 2 to 4 to define keys for other tables. 6. Click OK when you have defined all of the keys.

Steps to Change or Remove a Key1. From the Catalog menu, click Tables, and then click the Edit tab. 2. In the Edit tab (Table dialog box), do one of the following: To remove a key for the table, click the column that is defined as a key, click Key and then click OK. To change a key for the table, remove the current key, then select the new column to be defined as a key, click Key, and then click OK. Note: Impromptu inserts a key next to the column name.

Create Joins Automatically After Creating a CatalogYou can create a catalog without defining any table joins, and then later create joins automatically. At any point, you can see exactly which joins are already defined for an existing table.