Q: How do I copy a report from one catalog to another catalog?

Save the report as a new report name:

• Open the new report name attached to the old catalog
• Select Report, General
• For new catalog, browse to find new catalog
• OK
• Save changes
• Open the new catalog
• Open the new report

OR

• Copy the report to a new name
• Open the new catalog to use
• Open the new report name
• <OK>  to re-establish references using this new catalog

Q: How can I tell where a field in my Data Query came from?

Select Report, Query and then the Profile tab. Select Query Columns and you can see the full database column name.  If you do not have a Profile tab go into Tools, Options and check the ‘Show advanced tabs in the Query Dialog box’ and start again.

Q: Can I prompt a user for sort order on a report?

Yes.  You would need to create a file of different sort options.  Then, create a new calculation called Sort Option using a File Picklist prompt.  Then, in your query sort your report by your new Sort Option calculation.

Q: How can I obtain Impromptu manuals?

Electronic manuals are delivered when Impromptu is loaded onto your PC.  While in Impromptu select Help, Books Online.  Or you may Select Start, Programs, Cognos Series 7, Documentation, Cognos Impromptu to review all delivered manuals.

Q: Is there a way to have the page header only show up on the first page?

Yes, you can modify the properties of the page header.  Select your page header.  Right click, Properties.  On the Printing tab, choose the checkboxes to hide on the middle and bottom pages.  This will only show the page header on the first page of the report.

Q: How do I hide the grid lines that appear on a report?

Select the report body.  Right click, Format.  On the grid tab change the line type style to <None>.

Q: How do I insert a Page Break in a List report?

Click a cell in the list report.  Edit, Select Parent. Right click, Format, Properties, Layout tab.  Click the Page Break Before or Page Break After buttons that appear beside the group item.  To reset page numbers for one or more groups, click the group you want to reset page numbering for from the Reset the Page Number On box.  Click OK.

Q: How do I pull only a short amount of data for trial purposes?

Select Report, Query and then the Access tab.  Check “Limit data to ? rows” and type in the number of rows that you wish to see.

Q: Is there a way to have the background color alternate for every other line in Impromptu?  (i.e. simulate a green bar report). 

1.  From your simple list report  insert a calculation running-count ( Order No.)

 called Running count with automatic association for the report. This provides a

running count of 1 to the max number of rows.
2.  Insert another calculation mod (Running Count, 2) call it Alternate. This

provides an alternating number, either a 0 or a 1 for every other row.
3. Create a conditional format. Select the first row of data. From the right click

menu, click Conditional Format. Click Condition, Add Custom Condition. Select

Report Columns, this calculation should look like Alternate = 1.
4. Associate the new condition with a color, choose Grass Green 3.  The report alternates between white and green lines. The two calculations that were created can be removed from the display of the report but not from the query.

Q: Can I change the default formats set for currency?

Yes.  Select Tools, Options, Data Format tab.  Select the data type that you would like to change the default for (i.e. Numeric).  Select the new defaults for positive, negative and zero numbers and click <OK>.  NOTE:  This will change the default for ALL NUMERIC NUMBERS not just currency numbers!!

Q:How do I double space a report?  (i.e. SKIP-LINE in FOCUS)

Click on one of the fields in the row and drag the handle down to resize the row.

Q: How do I generate a Data Element Dictionary?

Log onto Banner and type in GURPDED process.  Select a printer or type ‘database’ to save a file on Banner.  For parameters type the following:

              01          USERID of Table Creator          (i.e. ALUMNI)

              02          Table(s) to be Reported              (i.e. A%)

              03          USERID of View Creator           (i.e. BANINST1)

              04          View(s) to be Reported              (i.e. AA%)

              05          Binding Margin Option    (Y or N)

              06          Heading Underline Option           (Y or N)

Check the ‘Save Parameters’ box.  Click the Save icon to run the report.  <OK> to Save Parameter values as user level defaults.  The log file and the list file name will be displayed on the bottom of the screen.  To review the output select Options, Review Output.  Double click in file name field and select the file that you would like to see.  At this point you can select Options, Show Document (Save and Print File) in a browser and then save the file to your hard drive as a TXT file.

Q: How do you sub-total a report?

First, group your report by the field you want to summarize by.  Second, click on the column you want to total and then the summary button ( ∑ ).

Q: How do I add the word ‘Total’ on a total line?

Select Insert, Text Frame.  Then, draw a box where you want the text to go and type your text (i.e. Total).

Q: How do you create a prompt?

Select Report, Prompt Manager and New for a new prompt.  Type in the name of your prompt (it can be any name you want), select the type of prompt (Type in or Catalog picklist are the most popular) and the message you want to display to the prompt user.  For a type in prompt you must select the data type (string, number, date, type . . .) and you may select a default value.  For a catalog picklist you must select the catalog column that you wish to display and use in your prompt by double clicking on the column name.  To execute the prompt you must put the prompt into your filter statement.  For example, to filter on state code your filter statement could look something like this:

  Pref Stat Code = ?State Prompt?

As you will notice prompts are indicated by question marks.

Q: I am getting an error: ‘ORA12154 – TNS could not resolve service name’.

Close everything down.  Open Impromptu.  Then, open the catalog that you want to use.  Now, open the report.  If you open the report first without opening the catalog you will likely get this error.

Q: I am getting an error that my user class password is invalid when trying to open a catalog.  I have opened this catalog before.  What could be wrong?

Remember that passwords are case sensitive.  Ensure that your caps lock button is not on and try again.  If you still have problems contact Application Services.

Q: How do I select data that contains multiple values?  For example suppose I want to select data if the state is NC or VA or SC or GA?

You will need to use the IN statement.  The above statement would be represented as:  State_Code in (NC,VA,SC,GA)

Q: When I am saving a report as a text file or an excel file columns not in my report are showing up in the file.

When saving a report as a text file or an excel file any columns in your query are included in the file whether they are shown on the report or not.  Select Report, Query, Data tab and remove the columns that you do not wish to appear in your file.

Q: When I am saving a report as a text file or an excel file the headers are not the same as on my report.

When saving a report as a text file or an excel file the headers will show as the true catalog names not as any modified headings in your report.  You can either use the headings as they are or talk to your project lead about the possibility of modifying the catalog names in the catalog folders. 

Q: All of the dates are showing up as date and time.  How do I filter on date?

Banner stores all dates as date plus time.  To perform any filters on the date only you will have to calculate a new date using the datetime-to-date function and filter on that new calculated field.

Q: One of my columns is not showing all of my data.  How do I fix this?

You can try resizing your report by selecting the column and dragging the handles.  If the column box is already the full size of the page then you need to select the column.  Right click, Properties, Layout tab.  Select line control  “Multi line with word wrap”.

Q: How do I send my Impromptu report to other individuals?

Select File, Save As.  Type in file name and save as PDF format type.  Then send the PDF file as an attachment.

Q:  How so I set my report to run with a record limit?

Select Report, Query and click on the Access tab.  Select “Limit Data to” and complete the number of rows that you would like returned.  Remember to uncheck this box when wanting to run it full-blown.  If you do not have an Access tab go into Tools, Options and check the ‘Show advanced tabs in the Query Dialog box’ and start again.

Q: My totals are including values that are not showing because of the ‘eliminate duplicate rows’ feature.  How do I correct this?

You need to find a value/field to distinguish the records and use this in your report filter.

Q: How do I stop a report from executing?

Hit your Escape key on your keyboard.

Q: How do I determine the number of records pulled in a report?

A:           Select View, Screen layout to change your report view.  Use your scroll bar to scroll down to the bottom of the report.  The number of records retrieved will be on the bottom right of your screen.

Q: How do I change my measurements selection to be measured in inches instead of centimeters?

Select Tools, Options

• Select the General tab
• Select Grid Interval to be Inches instead of Centimeters
• Click OK

Q: I have a really long text field but all of the data is not displaying on my report.  Where is my data?

Select Report, Query and click on the Access tab.  Under Query Options ensure that the ‘Maximum number of characters to be retrieved for large text items’ block is NOT checked.  Click OK.

Q:          How do I add an Excel spreadsheet into Impromptu and join the excel spreadsheet to a Banner table?

Step by Step instructions to add data from an Excel Spreadsheet into a new and an existing catalog.

Solution:
Use the following steps to:

-create a Named Range in Excel.
-create an ODBC connection.
-create a new catalog.
-create a hotfile.
-add the hotfile to an existing catalog.

Steps -

STEP UP YOUR EXCEL SPREADSHEET:

1. Open the spreadsheet in Excel and highlight each column and row of data, clicking on the Insert menu, Name, Define and give the Range of columns and rows a Name.

SETUP YOUR ODBC CONNECTION:

2. From the Start menu choose Settings > Control Panel > Administrative Tools > Data Source (ODBC).
3. Under the System DSN tab, add a new System Data Source. Select a driver for which you want to set up a data source. In this example, set up a Microsoft Excel driver (*.xls) and then click Finish.
4. Type the Data Source Name and the description (optional). Select the Version and click on Select Workbook.
5. Browse to your file and click OK twice.

CREATE YOUR NEW CATALOG:

6. In Impromptu, select Catalog > Databases, expand ODBC databases and click New Databases.
7. Give your logical database a name, and select the ODBC data source created above from the drop-down list.
8. Click Test to verify that you can connect.
9. From the Catalog menu choose New, Name the catalog.
10. Select the Catalog type and the database from the drop down list. Click OK.
11. Add the database table(s) to the catalog tables and click OK twice.

You can now create reports based on this new catalog.
If you want to add the data from this Excel Spreadsheet to an Existing Catalog, then continue with the following steps.

CREATE AND SAVE YOUR HOTFILE:

12. With Impromptu open, create a new report. Have this report contain all the data within the spreadsheet that you want in your existing catalog.
13. Save this report as an .IMS file (file menu >> Save As >> Type: IMS)

ADD THE HOTFILE TO THE EXISTING CATALOG:

14. Open your existing Catalog.
15. Go to the Catalog menu >> Tables >> select the Hotfile radio button >> browse to the location of the Hotfile >> click OK >> your hotfile may now be listed in the available tables in the left panel (if you do not see the hotfile there then click the Retrieve Tables button). Once you see the hotfile in the list, simply select it and click Add to add it to the catalog tables (right panel).
16. Choose your join method and close the Tables dialog.

Q:Can I hide summary lines when only one detail line of data exists for that record?

Yes, you can.  You need to create a conditional format that hides the group footer when there is only one row in the group.  See the example below taken from the “Mastering Impromptu Reports” manual – page 44-45.

Conditionally Hide and Show Report Objects

For reports that include sensitive or irrelevant data, you can hide report objects and show them

when you want. This is useful when you distribute printed reports. However, when you distribute

reports electronically, the users can show any objects you’ve hidden. Therefore, do not

electronically distribute sensitive information as hidden.

You can define conditional formats that automatically hide headers, footers, or entire rows.

For Example

You have a grouped report that contains details and a summary for each group. For some

groups with only one row of details, the summary in the group footer is the same value as the

value in the detail row. You set up conditional formatting that hides the group footer if there is

only one row in a group. 

The Total Sale Amount and the total in the group footer are the same when there is only one

order number for a customer, so you conditionally hide the group footer when there is only one

row in the group. The expression for this condition is:

(count (Order No.) for Customer No.)=1

1. Select the header, footer, or any rows that you want to hide based on a condition.

2. Right-click and click Conditional Formats.

3. In the Conditional Formats dialog box, click Conditions.

4. Add Custom Condition

5. Create the expression, click OK,

6. Click Close

7. In the Change Style To box, click the Hidden style, and click OK.

Q: When I try to edit a catalog my Oracle views do not show up. 

Under Tools, Options make sure the “Retrieve database tables when editing catalog tables” box is checked.

Q: Can a join be created based on ROWID (row id) in an Oracle database?

Each table in an Oracle database has a pseudo-column named ROWID, which is used internally by Oracle for various operations. The data or ROWIDs in the psuedo-column are not stored in the database, nor are they database data. As such, Impromptu can get no information about this data/column, and so it cannot be used in Impromptu joins or queries.

Q: What is the prefilter command and how does it work?

The prefilter option allows you to specify that summary calculations are performed before a summary filter is applied. For example, to report a count of the number of salespersons and the salespersons who sold over $100,000, you can specify that the count of the number of salespersons occurs before the summary filter (sales greater than $100,000) is applied to the report.
You establish that a summary calculation is performed before the summary filter is applied by choosing one of the following options in the Data Definition dialog box:

Select the Calculate Before Summary Filter check box. Selecting this check box ensures that all summaries in the expression are calculated before any summary filtering occurs.

Add 'prefilter' to a summary expression. The Prefilter option ensures that this summary is calculated before any summary filter is applied.

Syntax

summary (column or calculation) prefilter

Example

count (Salespersons) prefilter

Note: You can add the prefilter option even when there is no summary filter. This means that you can delete or add a summary filter without having to delete and add the prefilter option in the summary expression.

Q: When I try to modify my catalog – all of the views do not appear.

To resolve this problem perform the following four steps:

Step 1:

Open Impromptu catalog.  Select Tools, Options and click on the General tab.  Clear the ‘Retrieve database tables when editing catalog tables’ check box.  Click <OK>.

Step 2:

Select Catalog, Tables.  Click on the ‘Retrieve tables’ button.

Step 3:

In the database objects box click on User views.  Click on Retrieve.  All views are now available.  Click <OK>.

Step 4:

Return to Tools, Options and check the ‘Retrieve database tables when editing catalog tables’ check box.  Click <OK>.

Q: How do I display the column headers below the group header?

For a simple list report select the List Frame properties.  Remove the Check mark next to the Column Title Header.  Then add the group header.  Manually insert the column headers inside the group header using Text Frames.

Q: How do I automate pulling data for the past week?

To pull data for last week use the following expression in the filter tab:Gift Date  >= (today( ) – 007 00:00:00:000)

Conversely, if you want to pull something that is more than 6 months old you would use the following expression:  Contact Date <= (today( ) – 180 00:00:00:000)

Q: How do I filter to look for blank data?

You need to use the ‘is missing’ command.  You filter expression should look something like this:  Student_Major is missing.

Q: When a query returns no rows how do I display a message stating no rows of data were retrieved instead of just a blank page?

1. Insert a text box or use the report title, and then press ESC.
2. Right-click the text box.
3. Click Conditional Formats.
4. Click Conditions, and then click Add Custom Condition.
5. Name the condition.
6. Create an expression following the format " count(COLUMN_NAME) <> 0 ".
7. Click OK, and then click Close.
8. Click to select the newly created condition.
9. Change the style of the condition to hidden, and then click OK..

Q: How do I set my column width to be a specified size that does not change?

Click on a field in the column and right click Properties.  Click on the Size tab.  Instead of ‘Scan’ select ‘Specify Width’.  You can now set the column to a specified width.

Q: Can I create form letters using Impromptu?

Yes.  You need to do the following:

1.       Create a new report using the blank template

2.       Cancel to display the report without adding query data

3.       Insert a Form Frame by drawing a frame to fill your window

4.       From Report, Query select your data

5.       Sort the report

6.       Execute the query

7.       Click inside the form frame

8.       Right click, Properties

9.       Click the Layout tab

10.   Under Scope of data, click Single row of data and OK

11.   Rearrange the data and insert text frames as appropriate onto your letter

12.   Execute and save your report

Q: How do I reset the page numbers on a report?

In order to reset the page number you have to do the following steps:

1.       Select (highlight) the primary list frame

2.       Right click, Properties

3.       Select the Layout tab

4.       In the ‘Reset the Page Number On:’ drop down box select the field you want to reset the numbering on

5.       Select OK