cal data, you can make better informed decisions.
Currently, many large organizations hire highly-paid information technology (IT) professionals who get data out of databases and provide it to decision makers. This process can be slow and costly. And typically, very few people benefit from the knowledge derived by this method. However, with Discoverer, you or anyone else with the right access privilege can get data from databases delivered right to your desktop computer. With Discoverer, get the information you need, make better informed business decisions, and share your business decisions with other people in persuasive ways.
Overview of Lesson 1
The tutorial uses a small database that contains information about a fictitious chain of video stores. The database contains data about the chain's sales, inventory, and stores. You will use this database to get data about the chain of video stores, to analyze the data, and to share the data in the form of Excel spreadsheets and HTML pages.
Note: In order to connect to the video store database, your Discoverer administrator must provide you with the correct login name, password, and database name.
In Lesson 1, you will look at the Discoverer work area and learn the names of Discoverer components that you see on screen, and learn how to use them. To do this, you will connect to the video store database. In the video store database, you will find the sample Workbook that you will use with this tutorial, and you will move through its Worksheets. You will also learn how to find more information about Discoverer in its online Help system.
Looking at the work area
When you get data from the database, Discoverer shows it to you as a Discoverer Workbook like the one shown below. Compare the list of terms below with the Workbook. These terms are used throughout the tutorial, the online Help system, and in the Workbook Wizard.
Workbooks. Look at the top of the Video Stores Analysis Workbook. Do you see the words "Oracle Discoverer - [Video Stores Analysis]"? The name of the Workbook you are using appears at the top of the Workbook in brackets, in this case [Video Stores Analysis]. When you change to another Workbook, its name appears in brackets so that you can keep track of which Workbook you are using. A Workbook is a collection of Worksheets.
Worksheets. Look at the bottom of the Video Stores Analysis Workbook. Do you see two tabs that are labeled "Tabular Layout" and "Crosstab Layout"? Each tab is a separate Worksheet. Both Worksheets contain data about the video stores, but each Worksheet is organized differently. Worksheets contain the data you want and allow you to analyze and share it.
Queries. Every time you open a Workbook or create a new one, Discoverer sends a query to your company's database. A query is a question that Discoverer asks the database in order to get the data you want; for example, "what are the total sales for stores in the Eastern region in the last quarter?" Queries are written in SQL, a language that databases understand. You do not need to understand SQL to communicate with the database. Discoverer writes the SQL for you.
Items. Look in the main section of the Worksheet. Notice that data is organized in rows and columns similar to a spreadsheet. The rows and columns contain the data that answers the query Discoverer asked the database. Do you see the column headings "Region", "Department", and "Profit SUM"? In a Worksheet, column and row headings identify Items. An Item contains a set of data that is stored in your company's database; for example, the Item "Region" contains all the regions that have video stores. In Lesson 2, you will create your own Workbook, and you must select Items from the database.
Page Items. Look above the column headings at the area named Page Items. Do you see the words "Year: 2000"? This database contains data for three years: 1998, 1999, and 2000. This page only shows data for 2000. When a column heading appears above all the other Items, it is called a Page Item. By selecting different Page Items from the Page Item drop-down list, you are actually switching pages within that Worksheet. In Lesson 3, you will use Page Items to switch between pages within a Worksheet.
The Discoverer administrator at your company provides you with the information you need to connect to your company's database. The Discoverer administrator should provide you with the URL, or Web address, for connecting to Discoverer. The administrator should also provide you with a user name, password, and the name of the database. You need this information before you can connect to the database and before you can use Discoverer. Your administrator may give you a different password and database name specifically for the video store database. If so, use the video store's connection information in the instructions below.
1. If your administrator has given you the necessary connection information, click anywhere on the Welcome to Oracle Discoverer Web page to make it active.
2. On the right side of the Welcome to Oracle Discoverer Web page, click the magnifying glass graphic above the words Click the image above to start. Wait a few moments until the Connect to Oracle Discoverer dialog appears.
3. In the Connection Information box, type the User name, Password, and Database name given to you by your Discoverer Administrator.
4. Click the Connect button. After a few moments, the Workbook Wizard appears, which means you are connected to the video store database and ready to begin using Discoverer.
NOTE: If you do not see the Workbook Wizard screen, you may have entered the wrong connection details. Re-enter the connection details and try again. If you cannot connect to Discoverer, contact your Discoverer Administrator.
5. The Workbook Wizard guides you through the necessary steps for getting the data you want from the database.
You are now connected to the database and can begin working through the tutorial. Begin by Opening an existing Workbook.
Opening an existing Workbook
You are going to open the tutorial Workbook that you will use with this tutorial. The tutorial Workbook is saved in the video store database. Your Discoverer Administrator will tell you the name of the Workbook that you need to open. In this tutorial, the Workbook is called Video Stores Analysis.
1. At the Workbook Wizard, click the Open an existing Workbook button.
2. Click the Database button. The Open Workbook from Database dialog appears, which lists all the Workbooks that are already saved in the video store database.
3. Click on the Workbook, Video Stores Analysis.
Note: Your Discoverer Administrator may have given this Workbook a different name. For example, your Discoverer Administrator may choose the name, Video Analysis Tutorial. If so, choose the Workbook name provided by your Discoverer Administrator.
4. Click the Open button. If you are asked whether you wish to run the query for the Worksheet, click Yes.
The sample Workbook, Video Stores Analysis, appears.
Switching between Worksheets
The Video Stores Analysis Workbook automatically opens to the Tabular Layout Worksheet as shown above. The Video Stores Analysis Workbook also contains another Worksheet, called Crosstab Layout.
1. Click the Crosstab Layout tab located at the bottom of the Workbook. A dialog appears asking if you want to run the query for that Worksheet.
2. If you are asked whether you wish to run the query for the Worksheet, click Yes.
Discoverer sends a query to the database and presents the data to you as another Worksheet.
Notice that the Tabular Layout Worksheet and the Crosstab Layout Worksheet both contain information about the video stores. However, the information is organized differently in the two Worksheets. When you create your own Workbooks, you can group related Worksheets together as a single Workbook in this way.
Using the online Help
Although you are probably familiar with using online Help, take a moment to familiarize yourself with Discoverer's online Help. When you need help, use the online Help system to find procedures for complicated tasks, examples, and definitions of terms. There are two ways to get help:
· The online Help menu.
· The context-sensitive Help buttons.
Use the online Help system at any time you have a question about using Discoverer. However, if you are looking at a Discoverer dialog on screen and you do not know how to use its buttons and text fields, use the dialog's context-sensitive Help button.
To get help from the online Help menu:
1. From the Discoverer menu bar, choose Help | Help Topics. The main Online Help window appears in a separate Web browser window so that you can read the topic and still see the Discoverer window.
2. Notice that topics are organized by common tasks:
o Getting Started
o Getting the Data You Want.
o etc.
3. Click the link Looking at the work area topic in section 1, (Getting Started). Read the description of that topic and scroll down the page until you see additional hypertext links for related topics. These additional links take related topics.
4. Perhaps this is not exactly the topic you are looking for.
Notice other links that point to related topics, one of which might be the one you want.
5. If you cannot find the topic you are looking for, click the Index button at the top right of the page.
An alphabetical list of index entries appears.
6. Click the letter S. Scroll down the list of index entries that begin with the letter S until you see sharing Workbooks.
There are two related topics under sharing Workbooks. They are name 1 and 2.
7. Click the 1 link next to sharing Workbooks.
You will see help relating to that topic.
8. Close the Help topic window and click anywhere on the Discoverer window.
To get help with context-sensitive Help buttons:Most Discoverer dialogs contain context-sensitive Help buttons. Click these Help buttons to get assistance with using the dialog.
1. From the Discoverer menu bar, choose Sheet | Edit Sheet. The Edit Worksheet dialog appears.
2. On the Edit Worksheet dialog, click the Help button located on the bottom left. A Help topic appears specifically for that dialog. The context-sensitive Help topics describe the procedure for using that dialog.
3. If necessary, scroll to the bottom of the Help topic window until you see the underlined hypertext links. The context-sensitive Help topics also provide a choice of related procedure topics for that particular dialog.
4. Close the Help topic window and click anywhere on the Discoverer window.
Questions and Answers
Test how well you learned Lesson 1 by answering the questions below. If you are using the tutorial online, click the word Answer after each question to see its If you printed out the tutorial, the answers are located at the very bottom of the lesson's last page.
What is the difference between a Workbook and a Worksheet?
What is the difference between an Item and a Page Item?
How do you switch between pages of a Worksheet?
How do you switch between different Worksheets in the same Workbook?
What is the Workbook Wizard?
Summary of Lesson 1
Congratulations on finishing Lesson 1. Now you have learned how to connect to a database from Discoverer and how to open a Workbook that is saved in a database. You also know the main features of a Workbook, such as Worksheets, Items, and Page Items. And you know that at any time you need help using Discoverer, you have two choices: the online Help system and the context-sensitive Help buttons found on Discoverer dialogs.
Answers
A Workbook is a collection of related Worksheets. For example, you may choose to have a Workbook with separate Worksheets for Sales by Region, Sales by Department, and Sales by Product; each Worksheet contains sales information grouped by region, department, and product.
A Worksheet contains data that answers the query Discoverer asks the database; for example, the Sales by Region Worksheet contains data that answers the question (query) "What are my company's sales for the Eastern, Central, and Western regions?"
An Item identifies a specific set of data in the database; for example, if you want to see all departments in the database, you select the Department Item. In Lesson 2 you will learn how to select Items from the database with the Workbook Wizard. On a Worksheet, Items appear as column and row headings, and in Lesson 3 you will learn how to rearrange Items on a Worksheet.
A Page Item is a special Item that groups all the data on a page; for example, data can be grouped by the years 1998, 1999, or 2000. On a Worksheet, a Page Item appears above all the other column headings. In Lesson 3 you will learn how to create Page Items and how to switch between them.
If your Worksheet contains more than one page, you can switch between pages by switching Page Items. For example, if you group data on a page by year, you can switch the year Page Item to 1998, 1999, or 2000. In Lesson 3 you will learn how to switch Page Items and how to create your own.
If there is more than one Worksheet in a Workbook, click a tab located at the bottom of the Workbook to switch between Worksheets. The first time you click a tab, Discoverer may ask you if you want to run the query for that Worksheet, (depending on how Discoverer is configured in the Tools\Options\General tab). If you choose Yes, or if Discoverer is configured to automatically run the query, Discoverer retrieves the most current information for that Worksheet from the database. Each Worksheet has its own query.
The Workbook Wizard is a Discoverer tool that helps you create new Workbooks or Worksheets. The Workbook Wizard automates much of the work of creating Worksheets, including writing SQL queries and sending them to the database for you. In Lesson 2, you will use the Workbook Wizard to create a new Worksheet.
Lesson 2
Getting the Data You Want
Overview of Lesson 2
Overview of Lesson 2
In Lesson 2, you will choose layout defaults for new Workbooks that you create using the Workbook Wizard. After you create a new Workbook, you will organize Items and Page Items on your table Worksheet. You will duplicate your table Worksheet as a crosstab Worksheet. Then, you will reduce the amount of data in your Worksheet by creating conditions and parameters. Finally, you will add new Items to your new Workbook.
Selecting layout defaults for your Worksheets
Before you create a new Workbook, you can decide how you want the data in your Workbook to look. For example, you can choose font size, background color, and text alignment. The choices you make become the default settings for all new Workbooks. You can later change your mind and change the default settings. However, default settings affect only new Workbooks from that time forward. They do not change the layout of Workbooks that you already created or that were created by someone else.
1. Launch Discoverer and connect to the Video Store database. Did you forget how? Click here for a .
2. The Workbook Wizard appears, which guides you through the steps necessary for getting the data you want from the database. For now, click the Cancel button. The Workbook Wizard closes.
3. From the menu bar at the top of the Discoverer window, choose Tools | Options. The Options dialog appears with the General tab active. You use the Options dialog to choose default settings for using Discoverer. Options customize Discoverer to your work preferences.
4. Click the Default Formats tab.
5. If it is not already highlighted, click Data Format, and then click the Change... button. The Data Format dialog appears, which you use to choose font styles for data in new Worksheets.
6. On the right side of the Data Format dialog, you see an example of the current font settings. On the left side of the Data Format dialog, click the B button and notice how the example text changes to bold. The example changes every time you change text features. Change some of the font settings and notice how the example changes: choose a font size that you like from the Size drop-down list, choose a color for your text and a background color in the Color group box, and choose the vertical alignment you like.
Note: The Discoverer Administrator at your company typically chooses horizontal alignment for you based upon the type of data; for example, your administrator will often align numbers to the right.
7. When you are finished, click OK. The Data Format dialog closes.
8. If you want, change the default formats for headings and totals also. The Heading Format selection affects row and column headings, and the Total Format selection affects how totals are displayed; for example, if you want your grand totals for profits and sales to stand out, make total format bold and a larger size than data format.
9. Click OK to close the Default Formats dialog.
QUESTION: Do your layout options affect Workbooks created by other people, even if you open them on your own computer? Answer.
QUESTION: Do your layout options affect Workbooks created by other people, even if you open them on your own computer? Answer.
NOTE: You can make changes to the layout of your Workbooks later, using the Format tab on the Edit Worksheet dialog. For example, you may want to change the format of your currency.
For the moment, we will put aside the tutorial Workbook Video Stores Analysis, and create our own Workbook.
Before you can get the data you want, you must create a new Workbook using the Workbook Wizard. With the Workbook Wizard, you select Items from the video store database. The Items you choose determine the data you get. You must also decide how you want to display the data you get from the database.
1. From the Discoverer menu, choose File | New. The Workbook Wizard appears.
2. The Workbook Wizard asks you how you want to display the data that you will get from the database. Discoverer gives you four ways to display data:
o Table
o Page-Detail Table
o Crosstab
o Page-Detail Crosstab
3. The simplest way to display your data is in a table. A table displays data in columns with column headings. Click Table and then click the Next button.
4. The next page of the Workbook Wizard asks you to select Items from the database. By selecting Items, you decide which data you want to get and analyze in your table. On the left side of the Workbook Wizard, click the arrow below the word Available. From the drop-down list, choose Video Store Tutorial. The choices you see in the drop-down list are different parts of your company's database that your Discoverer Administrator has organized for you - these are called Business Areas.
Business Areas contain data specific to your area of interest; for example, an Accounts Payable Business Area, a Sales Business Area, and an Operations Business Area. Your Discoverer Administrator creates Business Areas for the whole company and grants access and other privileges to each Business Area.
5. In the Available group box, click the [+] next to Video Analysis Information. The [+] becomes a [–] and Video Analysis Information expands to show a list of all Items that you can get from the video store database. For example, the first Item in the list, Department, contains data about the departments at the video stores.
6. Click the word Department and add it to the Selected group box.
To add an Item, you can either drag-and-drop it to the Selected group box, or you can use the left arrow and right arrow buttons.
Note: If you click the [+] next to the Item, Region, notice that there are three regions: Central, Eastern, and Western. If you want, you can drag-and-drop just one or two of the regions at a time. Or if you want all the regions, drag-and-drop the word Region and notice that all three regions move to the Selected group box.
1. In the Available group box, click the [+] next to the Profit Item so that Profit expands to show a list of additional Items.
2. Add Region and Calendar Year and to the selected list.
3. Add Profit SUM to the Selected group box.
For numerical data like sales, costs, and profits, Discoverer provides you with a choice of aggregations, or summaries. For example, if you want to know the total profit for a particular region, choose SUM. If you want to know the average profit for the same region, choose AVG.
You should now have the following Items in your selected list:
Department
Region
Calendar Year
Profit SUM
QUESTION: Which aggregate moved: SUM, COUNT, MAX, MIN, AVG, or Detail? Answer.
4. Click Next to display the Format page. In our case will accept the default Format settings.
5. Click Next to display the Table Layout page.
You can see that Department is in the Page Items area. Region, Calendar Year, and Profit SUM are in the rest of the Worksheet.
6. To re-order the Items, click the Calendar Year Item and drag it to the left of the Region Item. A black bar between the columns shows you where the Item will be positioned.
7. Click next to display the Conditions page.
8. Click the check box next to Department is Video Rental or Video Sale. This will restrict our data to only the Video Rental and Video Sale Departments.
9. Click the Finish button. A new Worksheet, called Sheet 1, appears. Sheet 1 contains the data for the Items you selected. Notice the column headings; they match the Items that you chose in the Workbook Wizard.
Discoverer saves your Workbooks in your company's database. Your Discoverer Administrator must grant you the privilege to save Workbooks in the database. If you try the procedure below and find that you cannot save your Workbook, it is likely that your Discoverer Administrator has not granted you this privilege. If you cannot save your work, stay connected to the video store database instead for the duration of this lesson. You will occasionally see a warning that asks if you want to stay connected. If so, click Yes.
1. From the Discoverer menu bar, choose File | Save As. The Save Workbook to Database dialog appears.
2. In the text field under the label, New name, type a name for your Workbook; for example, My First Workbook.
3. Click the Save button.
QUESTION: Where will you find the name of your Workbook on the Workbook itself? Answer.
QUESTION: Where will you find the name of your Workbook on the Workbook itself? Answer.
Working with the Worksheet
The data you got from the database contains columns of information. The rows are disorganized and not yet useful for any meaningful analysis. In Lesson 3 you will learn to use Discoverer's most powerful features: its data analysis tools. After you organize your data for analysis, you will add more Items to the table.
One technique you can use to organize your data is to create Page Items. For example, Sheet 1 contains data for two departments at the video stores: Video Rental and Video Sale. With Page Items, you can organize your data so that you get a separate page for each department.
However, suppose you want to see your data organized by region instead of department. To create a separate page for each region, you must move region to the Page Items area.
1. From the menu, choose Sheet | Edit Sheet. The Edit Worksheet dialog appears. Notice that the Edit Worksheet dialog looks like the Workbook Wizard. Discoverer provides you with both methods for getting the data you want, analyzing your data, and sharing your results with others. However, you use the Edit Worksheet dialog after you have already chosen some initial data with the Workbook Wizard.
2. Click the Table Layout tab. With the Table Layout tab, you create Page Items and re-arrange the columns on your Worksheet. The Table Layout tab does not affect the data itself; it just re-organizes the data.
Notice the checkmark in the box next to the words Show Page Items. This check box allows you to create Page Items. In this example, the box is already checked and the Department Item appears in the Page Items text field.
3. Click the Region and drag it into the Page Items text field next to Department.
4. Click the Item, Department, and drag it down into the main body of the table. Notice that a black line appears that indicates the new position of the Item's column. Drag-and-drop Department so that it is positioned between the Calendar Year and Profit SUM Items.
5. Click OK. The Worksheet appears, re-organized by Region. Notice that Page Items: Region appears at the top of the Workbook window. The Department Item is now displayed between the Year and Profit SUM columns. For which region are you viewing data? Click the word Region and choose another region from the drop-down list. Notice how the data changes.
6. Save your work. .
Changing from a Table layout to a Crosstab layout
Another way to organize your data for analysis is to duplicate your table as a crosstab. A crosstab relates two sets of data and then summarizes their interrelationship in terms of a third set of data. For example, a typical crosstab for the video stores shows the monthly profit for each region, organized by department. In other words, there are three sets of data: total sales, region, and department. By duplicating your table as a crosstab, Discoverer calculates a subtotal of the profit for each region, as shown below.
In the screen shot above, notice that you now have new data, the subtotal of profit for each Region for each Year. Notice also that the Regions appear on the left side as row headings. In addition to columns, crosstabs also have rows.
A warning appears to let you know that you must have rows defined in a crosstab. Rows on Crosstabs are side axis items. Click OK.
2. The Edit Worksheet dialog appears again, open to the Crosstab Layout tab.
Notice in the Crosstab Layout screenshot below that there are no side axis Items on the left hand side. The three Items Calendar Year, Department, and Data Point:Profit SUM, all appear on the top axis.
The Crosstab Layout tab looks very similar to the Table Layout tab that you have already seen.
Notice that the column heading, Department, and a new one called Data Point:Profit SUM appear by themselves. Calendar Year, Department and Data Points are top axis Items because they appear on top of other Items. When you create a crosstab, you must choose which column headings become the top and side axis Items. You can rearrange axis Items whenever you want from the Edit Sheet dialog.
3. Drag the Region Item from the Page Items are to the side axis area.
4. Drag the Department Item to the Page Items area.
5. Click OK. If you are unclear about axis Items, look at the new Worksheet. Notice that the crosstab is organized by Region on the left axis Item. And notice that Department is the top axis Item.
6. If you would like to rename your new Crosstab Worksheet, double click the Sheet 2 tab at the bottom of the Worksheet.
7. Type in a new name and click OK.
Filtering data with conditions
Some video stores show larger profits than others. What factors cause some stores to earn more profit than others? If you are getting data from a very large database, the amount of data you get could be so large that you would have difficulty finding the answer to that question. So if you are interested in analyzing only the stores with the highest profits, how could you reduce the amount of data you get from the database?
Discoverer provides you with a tool called conditions that reduces the amount of data you get from the database. Conditions filter data to exclude some information so that the data is more manageable. For example, if you want to analyze stores with the highest profits, you could create a condition that says "Show me regions with annual profit great than 50,000 dollars".
1. To return to the Tabular Worksheet, click Sheet 1 tab at the bottom of the Workbook.
2. From the menu, choose Tools | Conditions. The Edit Worksheet dialog appears, open to the Conditions tab.
3. Notice that two conditions already exist. They were created for you by the Discoverer Administrator at your company. Some conditions have check marks next to them and some do not. The conditions with checkmarks next to them are active, or turned on, in this Worksheet.
4. Select the check box next to 'Department is Video Rental or Video Sale' to restrict your Worksheet to profits in these Departments.
5. You are going to create a new condition. Click New. You will create a new condition to limit the data to Regions with annual profit over 50,000 dollars.
6. If the Generate name automatically is not selected, click this check box. Discoverer will create a condition name for you.
7. In the text field under What description would you like to give your condition? type, Regions with annual profits greater than $50,000. This description appears in the Conditions tab. If the name is unclear, the description you write helps other users understand the purpose of your condition.
8. Look at the Formula group box. This is where you create the condition's formula. Discoverer displays any data that equals the formula and ignores any data that doesn't equal the formula. In this example, Discoverer displays any data about regions with profits greater than 50,000 dollars and ignores all other data. From the left-most drop-down list, select Video Analysis Information.Profit SUM. From the middle drop-down list, select the "greater than" symbol [>]. In the right-most text field, type 50000.
9. Click OK. Discoverer returns you to the Conditions tab.
10. Notice the name of your new condition: Profit SUM > 50000. This is the condition name that Discoverer generated automatically for you. You can see the description that you typed in below: Regions with annual profits greater than $50,000. The checkmark next to the name means that the new condition is turned on. If you want to turn off the condition, uncheck the checkbox.
11. Click OK. Discoverer sends a new query to the database and shows you the new results in the Worksheet. Notice that you now have less data. Only regions with annual profits greater than 50,000 dollars are shown.
As you can see, only for the Video Sale Department in the Central Region did profits exceed $50,000.
NOTE: Another way to highlight data is to use Discoverer Exceptions. Here, the data in the Worksheet is not restricted, but values that match a specified figure are highlighted. Refer to the Discoverer 4i Plus User's Guide for information about using Exceptions.
NOTE: Another way to highlight data is to use Discoverer Exceptions. Here, the data in the Worksheet is not restricted, but values that match a specified figure are highlighted. Refer to the Discoverer 4i Plus User's Guide for information about using Exceptions.
12. When you have finished working with the Worksheet, remember to turn off you Condition Profit SUM > 50000. Choose Tools\Conditions, then uncheck the Profit SUM > 50000 Condition check box.
Creating parameters for other users
Sometimes you may open this Workbook and want to see data about the year 2000, and sometimes you may want to see data about the year 1999. Or perhaps you want to share this Workbook with another person, and you do not know what Year they want to see. Discoverer gives you another tool, parameters, which gives you and other users a choice of filters when a Workbook opens.
You will create a new parameter that asks you which Year you want to look at. Every time you or another person opens this Workbook, Discoverer will ask you to which Year you want to look at.
1. Make sure that you have turned off the Condition Profit SUM > 50000, (see step 12 in the previous task).
2. From the menu, choose Tools | Parameters. The Edit Worksheet dialog appears, open to the Parameters tab. The Parameters tab lists all the parameters that already exist for this Workbook. A checkmark next to a parameter indicates that it is turned on.
3. Click New to create a new parameter. The New Parameter dialog appears.
4. In the text field under What do you want to name this parameter?, type Choose a Year.
5. From the drop-down list under Which Item do you want to base this parameter on?, select Video Analysis Information. Calendar Year.
6. In the text field under What prompt do you want to show other users?, type Please choose a Year. Every time this Workbook opens, you or other users will see this prompt.
7. In the text field under What description do you want to show other users?, type Restrict the Workbook to one or more Years. Every time this Workbook opens, you or others will see this description. If the prompt or name is unclear, the description you write helps other users understand the purpose of your parameter.
8. Click the drop-down list under the question What default value do you want to give this parameter? and click 2000. Every time this Workbook opens, 2000 will be the default choice that users see when asked to choose a Year.
9. Put a checkmark in the box next to Let users select multiple values. If you or another user wants to see data for both 1999 and 2000, ticking this checkbox allows you to select more than one Year at a time.
10. Leave the default value for Allow only one value for all sheets selected. The Parameter will apply to all Worksheets in the Workbook.
11. Leave the default value for Create condition option selected. A new Condition will be created, which will allow us to turn this Parameter on and off.
12. Click OK. Discoverer returns you to the Parameters dialog.
13. Notice that your new parameter, called Choose a Year, appears with a checkmark next to it. Look at the Description box at the bottom. The description you wrote, Restrict the Workbook to one or more Years, appears also.
14. Click OK. The Edit Parameter Values dialog appears and asks you to choose which Year you want.
15. Accept the default value '2000' from the drop-down menu and click OK. Discoverer sends a new query to the database and displays the Worksheet. Only data for the year 2000 appears in the Worksheet.
Notice that only data for the Year 2000 is displayed on your Worksheet. You are also restricting your worksheet to data for the Central Region using the Region Page Item.
Because you checked the Create condition option when you created the parameter, you can turn this parameter value off and on using the Conditions dialog. Choose Tools\Conditions and uncheck the Calendar Year = :Choose a Year condition to turn this value off.
Because you checked the Create condition option when you created the parameter, you can turn this parameter value off and on using the Conditions dialog. Choose Tools\Conditions and uncheck the Calendar Year = :Choose a Year condition to turn this value off.
What is the difference between a condition and a parameter? Answer.
How do you turn conditions on and off? Answer.
Adding more Items to the Worksheet
Now that you have begun to organize your data for analysis, you realize that you need more information in your table. You know the Profit for each region, but you would also like to see Sales.
1. Return to the Tabular Worksheet by clicking the Sheet 1 tab at the bottom of the Worksheet.
2. From the menu, choose Sheet | Edit Sheet. Once again, the Edit Worksheet dialog appears.
3. If it is not already clicked, click the Select Items tab. Once again, you see the Available group box and the Selected group box. Click the [+] next to Video Analysis Information, which expands to show you a list of additional Items. From the list of Items, move Sales SUM to the Selected group box.
4. Click the Table Layout tab. Drag-and-drop the Items so that they are organized in the following order: Calendar Year, Department, Profit SUM, and Sales SUM.
5. Click OK.
Your Worksheet now contains the new Item Sales SUM. You can now analyze how Sales relate to Profit figures.
Summary of Lesson 2
In lesson 2 you got the data you want from the database and made choices about how the data appears. By selecting layout defaults, you customized the way all new Workbooks look after you create them. Layout defaults do not affect Workbooks created by other people or Workbooks that you created before you changed the defaults, only new Workbooks created after you change defaults.
You used the Workbook Wizard to create a new Workbook by selecting Items that you want to see in your Worksheet. Later, you went back to the Workbook Wizard to add more Items to your Worksheet. You can add or delete Items from Worksheets at any time using the Workbook Wizard. After saving your Workbook in the database, you arranged Items into Page Items and axis Items to make analysis easier. You then created conditions that reduce data in your Worksheets to make them more manageable. You also created parameters, which give you the choice of filters when the Workbook opens.
Answers and Definitions
Do your layout options affect Workbooks created by other people, even if you open them on your own computer?
No, your layout options only affect the Workbooks you create after the time you choose your options. If you change your layout options, only new Workbooks are affected. Old Workbooks or Workbooks created by other people retain the layout options that were in effect at the time they were created.
SUM moved. In this example, Discoverer defaults to SUM for aggregations. Your Discoverer Administrator decides which type of aggregation to use as the default.
The Workbook's name appears at the top of the Workbook, in brackets. For example, [Video Stores Analysis].
A condition filters data in the same way every time the Workbook opens. Parameters offer you and other users a choice of filters to use.
From the Tools menu, choose Conditions. In the Conditions dialog, you see a list of conditions that already exist. A checkmark next to the name indicates that the condition is turned on. By removing the checkmark, you turn the condition off.
Lesson 3
Analyzing Your Data
Overview of Lesson 3
In lesson 2 you created your own workbook. In this lesson, we return to the tutorial Workbook Video Stores Analysis to do some ad hoc analysis. Discoverer provides several powerful analysis tools, such as:
· Sorting rows and columns.
· Pivoting rows and columns.
· Drilling into data to see more detail.
· Adding totals to your Worksheets.
· Calculating percentages on numerical data.
· Creating your own calculations on data.
· Graphing your data.
In lesson 3 you will use these data analysis tools to help you arrive at a business decision. For example, are sales higher in particular regions? What other factors contribute to higher sales? By using Discoverer's analysis tools, you will find the answers to these and your own particular business questions.
Data in a Worksheet is arranged in rows and columns similar to a spreadsheet. Also similar to a spreadsheet, you can sort both rows and columns alphabetically and numerically, from high-to-low or low-to-high. However, Discoverer's sorting capabilities are more powerful than what you may be used to from using spreadsheets. In addition to simple column and row sorting, you can also sort within other sorts, or group sort. For example, you may wish to sort on City name within Region.
In the previous lessons, you learned how to open the Video Stores Analysis Workbook, and how to create your own Workbook. In this lesson, we return to the Video Stores Analysis Workbook to perform some analysis tasks on the data.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. .
2. At the bottom of the Worksheet, click the tab labeled Tabular Layout. After the query finishes, notice that the Worksheet is already sorted alphabetically by Region.
3. From the Page Items area, choose 2000 from the list of Years.
4. Instead of sorting by Region, you want to sort by Profit SUM, so that you can see Profit SUM figures in descending order, (highest first). From the menu, choose Tools | Sort. The Worksheet Wizard appears, open to the Sort tab.
5. Click the box next to the Region Item and click Delete to remove the Region sort.
6. Click the Add drop-down list and select Profit SUM. The Add drop-down list shows all the Items that you have in your Worksheet. Choosing an Item from the Add drop-down list causes Discoverer to sort using that Item.
7. Click the Low to High pull down list next to Profit SUM, (under Direction). From the drop-down list, choose High to Low.
8. Under the column heading Group, do not change the setting None.
9. Click OK to view the change to your Worksheet. The data is now sorted by Profit SUM, with the highest value at the top ($109,637) for the Video Sale Department in the East Region.
Find out more about what you can do with Discoverer's sorting features by experimenting with different sorts on different Items. When you have finished, if you want to return to the original Video Stores Analysis Workbook, close the current Workbook without saving it.
In Lesson 2, you created a Page Item to group data by Region. You also created axis Items to group data by Region and Department. At any time, you can change Page Items and axis Items so that your data is grouped in a way that is more meaningful for you. Changing the positions of Page Items and axis Items is called pivoting.
Perhaps you want to compare sales by Department for each Region. You still want to see information about the Regions, but you would rather see all Departments on the same page with a separate page for each Region. By pivoting the position of your Page Items and axis Items, you can create comparisons that better suit your needs.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. .
2. From the menu, choose Sheet | Edit Sheet. By now you have figured out that you do most of your work with the Edit Sheet dialog. As you would expect, you also use the Edit Sheet dialog to pivot Items.
3. Click the Table Layout tab. Currently, you have only one Page Item: which is Calendar Year. Drag the Calendar Year Item down and to the left of the Worksheet. When you are moving Items, a black bar indicates where the column will appear.
4. Pivot the Region Item from the main body of the Worksheet by clicking on the Region Item and dragging it to the Page Items axis.
5. Click OK. Discoverer sends the Worksheet's query to the database. The Worksheet now shows all the Departments on a single page. Notice also that the Region Item is displayed in the Page Items area. To display data for a different region, click the down arrow in the 'Central' Page Item and choose another Region from the list of options. The Worksheet will be updated with data for that Region.
Now that you see data for each department group sorted by Year, you may wonder "Are sales better in some Quarters than others?" Right now your Worksheet only shows you data for each Year. To answer this question, you need to drill into the data and show detailed sales information by Quarter.
Notice a small triangle next to the Item Year. This is a drill icon; it indicates that you can drill deeper into this Item to get more detail.
1. Click the drill icon for Year.
2. A drop-down list appears with the choices:Calendar Year, Calendar Quarter, and Calendar Month. Click Calendar Quarter to drill down to Quarter level.
3. When the query finishes, your Worksheet now contains a new column, Quarter. The profit data is also now broken down for each Quarter. By using drill icons, you can see deeper levels of detail about your data. If this is too much detail for you, use the drill icon again to collapse the quarterly detail back into yearly totals.
To drill out of the Quarter data back to the original Worksheet, (or collapse the data), click the drill icon next to Quarter and choose Calendar Year from the list of options. The Quarter Item is removed.
Adding Totals to Worksheets
In this section, we learn how to add a Total to a Worksheet.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. .
2. Click the Crosstab Layout tab to display the crosstab Worksheet.
3. From the menu, choose Tools | Totals. The Edit Worksheet dialog appears, open to the Totals tab.
4. Click New to create a new total. The New Total dialog appears.
5. Under the question, "Which data point would you like to create a total on?", click the drop-down list and choose Profit SUM. You want to see a total of all the profits for each year.
6. Under the question, "What kind of total do you want?", click the drop-down list and choose SUM. Discoverer offers you different types of totals to choose from, such as Sum, Average, Maximum, and Minimum. In this case, you want the sum of the profits.
7. Under the question, "Where would you like your total to be shown?", click Grand total at bottom.
8. Click OK. Discoverer returns you to the Edit Worksheet dialog. Notice that your new total, called Grand Total Rows Sum for Profit SUM, appears with a check mark next to it.
9. Click OK to use this total in your Worksheet. After the query finishes, the Worksheet now shows a subtotal for each year.
By experimenting with the Totals dialog, you can create totals that answer your unique business questions and show the kind of detail you want. Experiment with other types of totals, such as Average, Maximum, and Minimum.
Adding Percentages
Now that you know the sum of profits for each year, what if you want to find out the percentage that each department contributed to the bottom line? Which departments contribute a higher percentage than others toward the total profit? To find the answer to these questions, Discoverer allows you to add a new column to your Worksheet that calculates percentages for you.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. . Then, click on the Tabular Worksheet.
2. From the menu, choose Tools | Percentages. The Edit Worksheet dialog appears, open to the Percentages tab. If your Discoverer administrator or another user has already created percentages, you see them listed on the Percentages tab. If you don't see any percentages listed, no one has created any yet. Your job is to create a new percentage.
3. Click New. The New Percentage dialog appears.
4. Answer the question, "What do you want to name this percentage?" by typing: Percentage of Annual Profit. You want Discoverer to find the grand total of the profits for all departments, and then calculate the percentage that each department contributed to the grand total.
5. Answer the question, "Which data point do you want to base your percentage on?" by choosing Profit Sum from the drop-down menu.
6. Under Calculate as a percentage of, choose Subtotal at each change in, then choose Calendar Year from the pull-down list below.
7. Answer the question, "Which page Items do you want to include?" by choosing Calculate percentage only for current page Items. Discoverer calculates a total percentage for Page Items in your Worksheet. In this case, you have only one Page Item: Region. Discoverer calculates the total percentage for all regions.
8. On the right side of the New Percentage dialog, put a checkmark in the Show subtotal and subtotal percentage check box.
9. Click OK. You return to the Percentages tab.
The name of your percentage, Percentage of Annual Total, appears in the Percentages tab with a checkmark next to it.
10. QUESTION: What do you think the checkmark is for? What do you think happens if you remove the checkmark? Answers.
11. Click OK. The Worksheet appears with a new column called Percentage of Annual Profit.
We can now see that in 1998, the Video Rental Department in the Central Region made 12% of the annual profit across all three Regions. If we wanted to produce a report on percentages, we could now re-sort our Worksheet on our new Item Percentage of Annual Profit.
In addition to adding totals and percentages, you can create your own custom calculations to add to your Worksheet. Do you need to find the profit margin for each department? Do you want to calculate the commission earned by your sales people? Do you want to calculate sales tax and show it in the Worksheet? Using Discoverer's Calculation feature, you can create custom calculations that meet your own unique business needs.
In this portion of lesson 3, you will create a custom calculation to find the sales tax due for each department's profit.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. . Then, click on the Tabular tab.
2. From the menu choose Tools | Calculations. The Edit Worksheet dialog appears, open to the Calculations tab.
3. If your Discoverer administrator or another user has already created calculations, you see them listed on the Calculations tab. If you don't see any calculations listed, no one has created any yet. Your job is to create a new calculation.
4. Click New. The New Calculations dialog appears.
5. Answer the question, "What do you want to name this calculation?" by typing: Sales Tax. You want Discoverer to the calculate sales tax for each department's profit.
6. In the Show list box, click Profit SUM and then click Paste. Profit SUM moves to the Calculation list box. You are now creating your new calculation in the Calculation list box.
7. To calculate a sales tax of 8 percent, you will multiply Profit times .08. Click the [x] button, the symbol for multiplication. And then type .08.
8. You have just created a formula for calculating sales tax. Click OK. Discoverer returns you to the Calculations tab.
9. Your new calculation, Sales Tax, appears with a check mark next to it. The check mark indicates that this calculation is being used in this Worksheet. If you un-check the box, the calculation will not be used. Click OK. After the query finishes, a new column appears on your Worksheet, called Sales Tax. It shows the sales tax that each department owes for each share of profit.
To help you analyze your data more easily, Discoverer lets you display the information visually using a wide range of graphs and charts.
In this portion of lesson 3, you will create a Bar graph to compare Profit figures for Video Sales and Video Rentals in 1998, 1999, and 2000.
1. Connect to the Video Store Tutorial database and open the Video Stores Analysis Workbook. .
2. Click the Crosstab Layout tab to display your data in cross-tabular format.
3. Choose Graph | New Graph to start the Graph Wizard.
4. Click the Bar option from the Graph type list.
5. Click the Bar option from the Graph subtype list. Make sure that the 3D Effect option is checked.
6. Click Next to display the Graph Wizard: Titles, Totals, and Series page of the Graph Wizard.
7. Type "Profits 1998, 1999, and 2000" into the text box. Make sure that the Show Title option is checked.
8. Click Next to display the Graph Wizard: X-Axis page of the Graph Wizard.
9. Type "Region" into the text box as a title for the horizontal axis. Make sure that the Show X-Axis Title option is checked.
10. Click Next to display the Graph Wizard: Y-Axis page of the Graph Wizard.
11. Type "Profits" into the text box as a title for the vertical axis. Make sure that the Show Y-Axis Title option is checked.
12. Click Next to display the Graph Wizard: Plot Area page of the Graph Wizard.
13. Data columns are assigned default colors. Here, we will change the default color for the 1998 profit data. Click the color block next to Profit SUM, 1998 and choose a red shade from the color picker pane. The column Profit SUM, 1998 is now represented in red.
14. Click Next to display the Graph Wizard: Legend page of the Graph Wizard.
15. Click the Location pull-down list and choose Top to display the legend at the top of the graph. The Sample pane is updated to show you how the graph will look.
16. Click Finish.
Depending on the default position setting, your graph is displayed either as part of the Worksheet or in a separate window. If your graph is not visible on your Worksheet, choose Graph | Display Graph and choose Right of Data option.
17. Now that you have created your graph, you can edit it on screen by clicking elements and dragging them to another location. Or, you can right-click elements to display editing menus. Click to the right or left of the legend to display its blue editing box, click a corner point, and then drag it to re-size the legend. Or, to re-position the legend, drag the whole editing box to a new position.
18. You can also right click graph elements to access editing options, for example to display the Graph Wizard. Right-click over one of the columns to display the editing menu, then choose Series Color to display the color picker panel.
Having created your graph, you can now print it or export it as part of your Worksheet. See also Sharing your results with others.
Summary of Lesson 3
As you can imagine, the possibilities of using Discoverer's data analysis tools are limitless. For almost every business question, there is a way to analyze data and arrive at an By using combinations of sorting, pivoting, and drilling you can arrange the data on your Worksheet in a way that lends itself to easier analysis. By adding totals, percentages, and custom calculations, you can extend the information you already have. By adding graphs and charts you can analyze your data visually - this allows you to compare numerical data and see trends at a glance. The more you use Discoverer, the more opportunity you will find for combining its analysis tools in ways that suit your unique business needs.
Answers and Definitions
A check mark next to a total, percentage, or calculation indicates that it is active in the Worksheet; that is, that the Worksheet is currently using that total, percentage or calculation. If you remove the checkmark, you effectively turn the total, percentage, or calculation off. To turn it back on again, put the check mark back.
Query. Every time you open a Workbook or create a new one, Discoverer sends a query to your company's database. A query is a question that Discoverer asks the database in order to get the data you want. Queries are written in SQL, a language that databases understand. You do not need to understand SQL to communicate with the database. Discoverer writes the SQL for you.
Data Point. Data points are numerical data, such as Sales, Cost, or Profits. There are two kinds of data points: detail data points and aggregate data points. A detail data point is one sale or the cost of one product. An aggregate data point is a summary, such as total profit over a year or quarter.
Items. An Item is a name for a specific set of data in the database; for example, if you want to see all departments in the database, you select the Department Item. In a Worksheet, Items appear as column and row headings.
Page Items. A Page Item is a special Item that groups all the data on a page; for example, data can be grouped into separate pages by the years 1998, 1999, and 2000. On a Worksheet, a Page Item appears above all the other column headings. This special Item means that all the data currently visible in the Worksheet describes a single year (for example 2000). By selecting different Page Items from the Page Item drop-down list, you are actually switching pages within that Worksheet.
Axis Items. Like Page Items, axis Items are special Items on a Worksheet. Axis Items can be moved to another location on a Worksheet. For example, you could move the Item, Department, to the side axis, thus making it a row heading. You could move it to the top axis, making it a column heading. Or you could move the axis Item to the Page Items axis, making Department a Page Item.
Lesson 4
Sharing Your Results with Others
Overview of Lesson 4
After you get the data you want and analyze it, you will ultimately arrive at a business decision or have data that persuades others to make a decision. Of course, you want to share you results with others so they can see the fruits of your labors. Discoverer provides you with a variety of options for sharing your results with others. Using Wizards and the menu bar, you can print out your Worksheets or export them to popular application formats, such as Microsoft Excel, and HTML. And you can also share your Discoverer Workbook with other Discoverer users at your company.
In lesson 4, you will open the sample Workbook, Video Stores Analysis, from the database and share it with others in each of these four ways:
· You will use the Print Wizard to select print options for printing the Worksheets.
· You will use the Export Wizard to export a Worksheet to an Excel spreadsheet.
· You will also export your Worksheet to HTML format and view the results in your Web browser.
· And finally, you will share your Discoverer Worksheet with other Discoverer users at your company.
To help you with printing, Discoverer uses the Print Wizard. The Print Wizard offers you more control over what you print than the standard Print dialog of most applications.
1. Connect to the Video Store Tutorial database and open the Workbook Video Stores Analysis. .
2. Choose Year:2000 from the Page Items area.
3. From the Discoverer menu, choose File | Print to start the Print Wizard.
4. Page 1 of the Print Wizard offers you the choice of printing the current Worksheet or all Worksheets in the Workbook. In this case, we wish to print the current Worksheet only. Choose Current Sheet.
5. Under What do you want to print?, Table/Crosstab only is selected by default. If you have created a graph with your Workbook, you can choose either of the other options Both Graph and Table/Crosstab and Graph only.
6. Click Next to move to Page 2 of the Print Wizard.
Page 2 of the Print Wizard lets you choose whether to monitor the printing process. To be sure that you are printing the most current data in your company's database, Discoverer sends the query for the Worksheets to the database. By clicking Supervised, you are notified if there are any issues that need to be resolved. For example, if the query takes a long time, you may see a message asking if you wish to continue. Under Do you want to supervise the print process? choose the Supervised option.
7. If the Worksheets contain any parameters, you will be asked to choose parameters. If so, choose the values that you want to print.
8. Click Finish. The standard Print dialog appears.
9. From the Printer Name drop-down list, choose the printer you want. Click OK. Note that the Print Wizard prints only the Worksheet that is currently visible on your screen, not every combination of Page Items.
Some people you want to share your results with do not have Discoverer. Or, they may want to use your results in some other format, such as an Microsoft Excel spreadsheet. Discoverer allows you to export your Worksheets and gives you a choice of common application formats. In this part of lesson 4, you will export to Microsoft Excel using the Export Wizard.
1. Open the Video Stores Analysis Workbook. .
2. From the Discoverer menu, choose File | Export to start the Export Wizard.
3. Page 1 of the Export Wizard offers you the choice of exporting the current Worksheet or all Worksheets in the Workbook. Choose Current Sheet.
4. Under What do you want to export?, choose Both Graph and Table/Crosstab.
5. Click Next to move to Page 2 of the Export Wizard.
6. Page 2 of the Export Wizard lets you choose the export format and target location of the export file. Click the down arrow underneath the question, "What export format do you want?" From the drop-down list, choose Microsoft Excel Workbook (*.xls). Notice that you can also export to other formats, such as Text (*.txt), CSV (*.csv), Formatted Text (*.prn), and others.
7. In the text field underneath the question, "Where do you want to save the file?," notice the name of this file; it has the three-letter file extension, .xls. If you want to change the name of this file, type over the name in this text field but keep the .xls file extension.
8. Click Next to move to Page 3 of the Export Wizard.
9. Page 3 of the Export Wizard lets you choose whether to monitor the printing process. Under Do you want to supervise the export process? choose the Supervised option. To be sure that you are exporting the most current data in your company's database, Discoverer sends the query for the Worksheets to the database. By clicking Supervised, you are notified if there are any issues that need to be resolved. For example, if the query takes a long time, you may see a message asking if you wish to continue.
10. Click Finish. Discoverer exports your Workbook to Excel format. The Export Log appears, which informs you that export is complete and alerts you to any problems that happen during the export process.
11. If you have Microsoft Excel installed on your computer, put a checkmark next to the question, "Open the first exported sheet?" and click OK. Your Worksheet will appear in a Microsoft Excel spreadsheet. Compare the original Discoverer data with the exported data in the Microsoft Excel Workbook.
NOTE: You can also export to Microsoft Excel format quickly by choosing File | Export to Excel, or by clicking the Export to Excel icon 
on the toolbar.
Discoverer gives you the ability to turn your Worksheet into Web pages without having to know any HTML at all. By using the Export Wizard, converting to HTML is as simple as exporting to Microsoft Excel. And you can compare the original Discoverer Worksheet to the new HTML page directly in your Web browser.
1. Return to the Video Stores Analysis Workbook.
2. From the Discoverer menu, choose File | Export to start the Export Wizard.
3. Page 1 of the Export Wizard offers you the choice of exporting the current Worksheet or all Worksheets in the Workbook. Choose Current Sheet.
4. Under What do you want to export?, choose Both Graph and Table/Crosstab.
5. Click Next to move to Page 2 of the Export Wizard.
6. Page 2 of the Export Wizard lets you choose the export format and target location of the export file. Click the down arrow underneath the question, "What export format do you want?" From the drop-down list, choose Hyper-Text Markup Language (*.htm).
7. In the text field underneath the question, "Where do you want to save the file?," notice the name of this file; it has the three-letter file extension, .htm. If you want to change the name of this file, type over the name in this text field but keep the .htm file extension.
8. Click Next to move to Page 3 of the Export Wizard.
9. Page 3 of the Export Wizard lets you choose whether to monitor the export process. Under Do you want to supervise the export process? choose the Supervised option. To be sure that you are exporting the most current data in your company's database, Discoverer sends the query for the Worksheets to the database. By clicking Supervised, you are notified if there are any issues that need to be resolved. For example, if the query takes a long time, you may see a message asking if you wish to continue.
10. Click Finish. Discoverer exports your Workbook to HTML format. The Export Log appears, which informs you that export is complete and alerts you to any problems that happen during the export process.
11. Put a checkmark next to the question, "Open the first exported sheet?" and click OK. Your Worksheet will appear in your browser. Compare the original Discoverer data with the exported data displayed in your browser.
NOTE: You can also export to Web format ( HTML) quickly by choosing File | Export to HTML, or by clicking the Export to HTML icon 
on the toolbar.
Sharing a Workbook is a privilege granted to you by your Discoverer administrator. If you are unable to share your Workbook in this lesson, you may not have this privilege. If you cannot share your Workbook, skip this section of the lesson and review the lesson 4 summary. If your Discoverer administrator has granted you this privilege, you can share your Workbooks with other people at your company.
Discoverer gives you two ways to share Workbooks:
· assign one or more Workbooks to a single user.
· assign one or more users to a single Workbook.
In this lesson you will assign one or more Workbooks to a single user, and assign the Video Stores Analysis Workbook to a user called "Public".
1. Return to the Video Stores Analysis Workbook.
2. From the Discoverer menu, choose File | Manage Workbook | Sharing. The Share Workbooks dialog appears.
3. Click the User > Workbook tab.
4. To share the Workbook, Video Stores Analysis, with a user called Public, choose PUBLIC from the User pull down list.
5. Choose Video Stores Analysis from the Available list, then click the single right arrow to move Video Stores Analysis to the Shared list box on the right.
6. Click OK. You are now sharing the Video Stores Analysis Workbook with other Discoverer users in your company who are part of the Public role assigned by your Discoverer administrator. Every time you update the Workbook, other users will get your most current version.
QUESTION: How would you assign three or four users to a single Workbook? Answer.
Summary of Lesson 4
Discoverer provides you with a variety of options for sharing your results with others. Using the Print Wizard, you can control how you want to print your Workbooks, whether one Worksheet at a time or all the Worksheets in your Workbook. Using the Export Wizard, you can export Workbooks to popular application formats, such as Microsoft Excel and HTML. And you can share your Discoverer Workbook with other Discoverer users at your company.
Answers and Definitions
By choosing the Workbook > User tab, you can select a Workbook and then assign as many users as you want to it.
Query. Every time you open a Workbook or create a new one, Discoverer sends a query to your company's database. A query is a question that Discoverer asks the database in order to get the data you want. Queries are written in SQL, a language that databases understand. You do not need to understand SQL to communicate with the database. Discoverer writes the SQL for you.
Parameters. Sometimes you may open a Workbook and want to see data about the Central region, and sometimes you may want to see data about the Western. Or perhaps you want to share this Workbook with another person, and you do not know what Region they want to see. Parameters give you and other users a choice of filters when a Workbook opens.
Page Items. A Page Item is a special Item that groups all the data on a page; for example, data can be grouped into separate pages by the years 1998, 1999, and 2000. On a Worksheet, a Page Item appears above all the other column headings. This special Item means that all the data currently visible in the Worksheet describes a year (for example 2000). By selecting different Page Items from the Page Item drop-down list, you are actually switching pages within that Worksheet.
Lesson 5
Putting It All Together
Overview of Lesson 5
Lesson 5 is a summary of all lessons you have learned in the tutorial. Use lesson 5 as a self-test to determine how well you have learned each lesson and whether you need to repeat a lesson. Each step in the lesson includes a to the section of the previous lesson that covered that task. You do not need to complete an entire lesson, just the particular task that you may be unsure about.
In lesson 5, you will create a simple Worksheet from scratch, analyze the data, and then export the Worksheet to HTML.
Connect to a database
1. Using the connect details given to you by your Discoverer Administrator, connect to the Video Sales Tutorial database. .
2. Create a new Worksheet as a table. .
Get the data you want
1. In the Workbook Wizard, choose the Video Store Tutorial business area. .
2. From the Video Analysis Information Folder, choose the following Items: .
o Region
o Store Name
o Calendar Year
o Costs SUM
o Sales SUM
3. Make the Region Item a Page Item. .
4. Arrange the remaining Items in the following order: Calendar Year, Store Name, Sales SUM, Costs SUM. .
5. Create a new condition that reduces the data so that you only see Regions with annual sales greater than 40,000 dollars. .
6. Sort the Worksheet by the Item Store Name in ascending alphabetical order. .
7. Create a calculation to find the Profit from each store (Sales minus Costs). .
8. Make sure the calculation Sales Tax is active. . (If you do not have this item, create a Calculation for 8% Sales Tax. )
9. Click Finish and view the data in your Worksheet.
10. Duplicate the table as a crosstab. .
11. Save your Workbook in the database as Summary Workbook. .
Analyze your data
1. Click the tab for the crosstab Worksheet.
2. Pivot the Item Store Name to the top axis and the Item Calendar Year to the left axis. .
3. Drill into the Item Calendar Year to see detail at the quarterly level, (to Calendar Quarter). .
4. Graph the data to visually analyze the Video Stores profit figures. .
5. Save your work.
Share your data with others
1. Print you Worksheet. .
2. Export your Worksheet to Microsoft Excel format. .
3. Export your Worksheet to HTML. .
4. Compare the original Discoverer Worksheet to the HTML page in you Web browser.
Food for thought
Do you have a project that you are working on right now for which you can use Discoverer?
If so, what Items do you need to choose from the database?
How will you arrange them on the Worksheet?
What level of detail do you want to see?
What type of graph will best represent your data?
What format will you use to share your results with others.
Summary of Lesson 5
Congratulations on completing the tutorial! As you use Discoverer, you will find uses for it that you may not have thought of before. You will support your business decisions with empirical data from your company's database; and you will be able to persuade others with your results. And remember, if you have questions about using the product, use Discoverer's online Help and context-sensitive Help buttons.
Definitions
Axis. Like Page Items, axis Items are special Items on a Worksheet. Axis Items can be moved to another location on a Worksheet. For example, you could move the Item, Department, to the side axis, thus making it a row heading. You could move it to the top axis, making it a column heading. Or you could move the axis Item to the Page Items axis, making Department a Page Item.
Business Area. Your Discoverer Administrator arranges data from the database into Business Areas. Business Areas contain data specific to your area of interest; for example, your administrator may have created an Accounts Payable Business Area, a Sales Business Area, and an Operations Business Area. Your Discoverer administrator creates Business Areas for the whole company and grants access privilege to each Business Area.
Condition. Conditions filter data to exclude some information so that the data is more manageable. For example, if you want to analyze stores with the highest profits, you could create a condition that says "Show me regions with annual profit greater than 50,000 dollars".
Drill. By using drill icons, you can see deeper levels of detail about your data; for example, you can drill from yearly to quarterly sales detail. If this is too much detail for you, use the drill icon again to collapse the quarterly detail back into yearly totals.
Items. An Item is a name for a specific set of data in the database; for example, if you want to see all departments in the database, you select the Department Item. In a Worksheet, Items appear as column and row headings.
Page Item. A Page Item is a special Item that groups all the data on a page; for example, data can be grouped into separate pages by the years 1998, 1999, and 2000. On a Worksheet, a Page Item appears above all the other column headings. This special Item means that all the data currently visible in the Worksheet describes a year (for example 2000). By selecting different Page Items from the Page Item drop-down list, you are actually switching pages within that Worksheet.
Pivot. At any time, you can change the position of Page Items and axis Items so that your data is grouped in a way that is more meaningful for you. Changing the position of Page Items and axis Items is called pivoting.
No comments:
Post a Comment