Freestyle Reports Session 1

Transcript

Freestyle Reports DW DIG –Basic Skills and Tools Overview There are two types of reports in BI Query. The original report tool is now called Standard Reports. It is the quickest and easiest way of creating a basic spreadsheet-style report. The standard report has rows and columns like a spreadsheet. Repeated values can be suppressed or duplicated. You can create calculated columns and subtotals, although the formulas you can use in those calculations are fairly limited. Reports can be printed, saved, and exported. We won't spend any time in Standard Reports. It is covered in the Standard Report Development DW DIG. BI Query Reports is the newer reporting tool. It is a separate application, with more powerful functionality than Standard Reports. It provides a flexible reporting environment that includes tables, crosstabs, and charts, all of which can appear within a single presentation. The calculations can be much more complicated, using more complex formulas. Reports can be printed, saved, and exported. BI Query Reports is being taught through a series of six sessions. We call this series "Freestyle Reports" because you can be much more flexible about how the data is arranged. If you wanted to put the grand total in the report header, you could! The sessions will be taught using the RetailGolf sample data model that comes with BI Query so that you can focus on the techniques being presented rather than the specific data, and so that it is applicable regardless of which model you usually use. I strongly encourage you to schedule some time after each session to take what was covered and experiment with it in whichever model you use most to see how you can make use of it to resolve your reporting needs. Demonstration of Example OUS/OSU Reports Watch a demo of some reports designed in BIQ Reports for various OUS/OSU models. • • • • Budget Status Report – Revenue and Expense by Fund Type (bud_stat_rpt_ol.pdf) Revenue and Expense Report – Self Support Funds – Activity by Financial Manager and Fund (Rev_Exp_by_FundMgr_Fund_Index.pdf) Dean’s Verification of Graduation Status (Deans_Verification_of_Graduation_Status_by_College.pdf) NCAA Bylaw 14.4.3 – Certification of Academic Progress for Student - Athletes (NCAA_Bylaw_by_College_Future.pdf) Now let's take a look at an example of a BI Query Report provided with the RetailGolf model. Then we will create our own simple report. Opening the RetailGolf Sample Data Model We’ll start by finding and opening the RetailGolf model. If you don't have the RetailGolf model installed yet1, you could just watch the demonstrations, but I think you'll find that the hands-on practice will be important. You might want to log into the model you use the most and practice the techniques in that other model. 1 To get the RetailGolf model installed, you should contact your computer technical support folks for assistance. If they have questions, have them contact Patty Ross, (541) 737-0616, [email protected] or Joy Bautz, (541) 737-3375, [email protected]. Page 1 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools • Go to Windows Start menu > All Programs > Hummingbird > BI Data Models > RetailGolf. Running an Already Designed Report • • From the Welcome window, navigate to the Sales Reports window. Under Miscellaneous Reports, click on the Top 5 Sales People report to run it. The BI Query Reports application will open, and it may ask you if you want to refresh data. When you save a report, the data in the report is saved as well. If you choose "Show existing data", it will show whatever data was saved with that report, if any. If the report was created a long time ago, you could be looking at very old data. However, sometimes that is desirable. For example, if you want to explore a report you aren't familiar with, opening the report with existing data just so you have something to look at would be faster, particularly if the query required to populate the data would take a long time to run – something you wouldn't already know when running a new report. Also, sometimes you might want to save the data with a report so that you can go back and look at the data as it was at the time, rather than as it is today. If you choose "Refresh the data", it will rerun the query to pull the most current data. It may ask you to log into your model. If prompts were used, it will give you a chance to put different values in the prompts. • Make sure "Refresh the data" is selected, make sure there is no checkmark in front of "Don't ask me this again", and click OK. Page 2 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Notice that this report includes logos, links to external web pages, ranking and highlighting of top sellers, and a drop down box called a hot spot designed to let the user interactively choose a different year. If you scroll right, you'll also see a chart that provides additional analysis by sales person. Page 3 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Creating a New Report Using Presentation Designer (A Reports Wizard) Before you can create a report, you need to have created a query and generated a results set that contains the data that you want to base your report on. To keep things simple, we will use a query that already has been developed for the RetailGolf model. To create a report, you send the results to BI Query Reports. • • • • • • • • On your Windows taskbar, notice that you now have two applications open: BI Query User and BI Query Reports. Switch back to BI Query User. Close the previous results set. Go to the Sales Reports window and click on Salesperson Activity. In the prompt dialog box, enter Floyd Ross as the salesperson. Enter 7/1/2003 and 7/31/2003 for the begin and end dates. Note that the query generates a results set with 38 rows of data. Send the data to BI Query Reports, either by clicking on the BI Query Reports toolbar icon, or selecting Results > Show as Report > BI Query Reports from the menu. You can also right-click to bring up a shortcut menu and make the selections there. or After sending the data to BI Query Reports using any of these methods, watch your Windows taskbar. You should see the BI Query Reports application button flash a couple of times to get your attention. That is telling you that you need to switch to that other application to continue. • Switch to BI Query Reports. In BI Query Reports, the Presentation Designer has opened up to help you get started on your report. The wizard first asks you to choose the presentation type that you want to display. Page 4 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools “Presentations” are tables, crosstabs, or charts that you can use to present your data to your readers. In general, choose tables to display detailed data, crosstabs to display summary data, and charts to display trends and exceptions over time. Tables provide a quick and easy way to present data. They usually display detailed data in columns (with headings across the top), and in bands. However, tables are flexible enough to format form letters, invoices, purchase orders, catalogs, and so on. We will cover tables most thoroughly since they are the most used presentation type. • Leave Table selected and click the Next button to go to the Style tab. I strongly recommend that at this point you always change the default Title to a meaningful name. That will help you distinguish this set of data (data source) from others and can save a lot of time and confusion. If you started with a query that has already been named, that name will default in and you can just use it. However, if you reuse that query later for a redo or a variation on your presentation, you may want to differentiate the name further. Table Styles will apply various basic designs to your data. Selecting the right one will give you a good start on your report design. Page 5 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Table Style Columnar and Columnar with Totals Free-form and Free-form with Totals Stacked and Stacked with Totals Stacked Indented and Stacked Indented with Totals Basic • • • • • Description Arranges data in columns. You can also choose to add totals to numeric columns. Doesn't anchor data to columns. You can also choose to add totals to numeric columns. Stacks and left-aligns sorted data, placing remaining data in columns. You can also choose to add totals to numeric columns. Stacks and indents sorted data, placing remaining data in columns. You can also choose to add totals to numeric columns. Arranges data in columns using the default formatting defined in your Preferences. Change the Title as necessary. Choose Columnar with Totals. Select "Fit on one page" if it doesn't default in, to force all of the columns in the data to fit on one page of the report. If unchecked, columns may spill over onto additional pages. Select the Landscape for the orientation. Click Next to go to the Arrange Data tab. Use this page to arrange columns in the table you are about to create. Columns you don’t want to show in the table can be dragged into the Available box. Sorted columns can be dragged between the Group by and Columns boxes. Columns in the Group by box will appear as the left-most columns in the table and will group the data (with Group Header and Footer bands). Only sorted columns can appear in this box. Column order reflects sort order. To change the sort, return to BI Query, modify the query or results set and send properly sorted results set to BI Query Reports Presentation Designer again. Page 6 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Columns in the Columns box will not group data and will be shown in the table to the right of the columns in the Group by box. • • Drag Order Id to Available Click Finish to see the initial version of your table presentation. Working with Table Columns Columns are a fundamental element of tables. Columns divide tables vertically. A column is separate from objects it contains. It's a vertical area in a table that can contain data items and report objects (text and drawing objects). When a column is inserted, it'll be empty until you populate it with data items or report objects. All objects “in” a column (i.e. not “Float” - see Anchoring Objects in Columns below) may be selected by clicking the column heading in the Column Control bar. Right-clicking column headings in the Column Control bar provide a menu that allows you to easily manipulate all the objects in a column. You can use the Column Control bar to change the width of columns so that the data fits nicely. Page 7 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools • • • Click on a column heading in the Column Control bar to select the column. Place your mouse cursor at the right edge of the selected column in the column control bar to get a double headed arrow. Click and drag left or right until the column is the desired width. Note that if the column isn't wide enough to display the entire amount, it won't give you a good visual cue by replacing the numbers by asterisks. Instead, it just hides part of the amount. Resize such a column wider as necessary. You can also use column headings to move columns. Place your mouse cursor over a column heading “button” to highlight it, and then click and drag left or right to move the column. • Move Pga Experience to the right of Product Name. Also note that if you resize the right edge of the right-most column, you resize the width of the entire presentation, as indicated by the gray border. Page 8 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools One trick to add white space to the side of a report without resizing the entire presentation, is to insert empty columns. • • • Right-click in the Column Control bar. Select Insert. Move the new empty column to the desired location in the report. Anchoring Objects in Columns How an object is anchored affects how it relates to the columns boundaries. Before moving an object, you may have to change how that object is anchored. Select the object(s) you want to move and chose Anchor from right-click menu and then pick one of the following options: Option Fit to Column Left Right Unanchored Float Meaning Anchored items are locked to a specific position in a column. - By default items are anchored to fit the column. So, resizing a column, resizes the items in it as well, ensuring that they're always the same size as the column. - Alternatively, items can be anchored to the left or right of a column. Unanchored items cannot go beyond column boundaries but are not locked to a specific position in the column. Float items are not restricted by column boundaries. Anchored For data presented in a table and the associated column headings, "Fit to Column" is usually what you want to choose. For other items in Headers and Footers, you will often want to change the anchor option to "Float". • • • • Click and drag the text item "Floyd Ross" into the empty column. Notice how it jumps to fill the entire column. Right-click on "Floyd Ross" and choose Anchor > Float. Click and drag "Floyd Ross" to the right of the report name and notice that it stays put without fitting into the nearest column. Page 9 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Working with Bands Bands are a fundamental element of tables. Bands divide tables into horizontal sections. Bands can be added, formatted, and shown or hidden. You can place objects (text objects, graphics, charts...) and the results of functions (subtotals, averages, grand totals...) into bands. One of the most important concepts to master is understanding the different bands, and the difference between selecting them and activating them. • • • • • • • Report Header Band – appears once at the top of the table Page Header Band – appears at the top of each page Group Header Band – appears above each group of data Detail Band – the data in each row of the query results Group Footer Band – appears below each group of data Page Footer Band – appears at the bottom of each page Report Footer Band – appears once at the bottom of the table The Show/Hide Bands dialog box will show you which bands exist in your table, and of those, which are hidden and which are showing. A band that is showing will have a green checkmark next to it. A hidden band will have a red X next to it. • Go to Format > Table > Show/Hide Bands. (If the menu item is grayed-out, you may need to select the presentation first before going to the menu.) Notice that each of the two columns that were sorted on in our original data, and that we grouped on during the presentation designer process, has a header and a footer. H1 stands for Header1, and identifies the highest level primary grouping, which was on Staff Name. F1 is the footer for that same grouping. You can have as many items grouped in your report as were Page 10 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools sorted on in your original data, and as makes sense. The more headers and footer bands you have to manage, however, the more complicated arranging data can get. • Select F1: Staff Name and click the Hide button to suppress the Staff Name group footer band. To perform any operation in a band, you need to SELECT the band first. To select a band, click inside the band. Make sure you click between the data items that appear in the band. If you are having a hard time finding a place where you aren't clicking on a data item, usually clicking at the left most edge of the table will work. After you have selected it, a handle box will appear above and within the box around the band. To resize a band, after selecting it, point at a handle, then click and drag until the band is the desired size. To add an item to a band, you need to ACTIVATE the band first. Otherwise, the item will only appear where you insert it but not be repeated in other occurrences of the band elsewhere on the page or on subsequent pages. To activate a band, double-click the band at the outermost edge of the table. If you double-click an item in a column instead of the band, the Properties dialog box appears. A handle box will appear below and outside the box around the band. To inactivate a band, either click on it again, or click somewhere outside the band. Tip: If you aren't sure which band is which, go to Format > Table > Show/Hide Bands. Click a band and then click the Hide button. Repeat until they are all hidden. Then show only one band at a time, in sequence, to see what is in that band. Formatting each band with a different color can help you see the bands and keep track of them. • • • • • Select the Report Header band. Right-click on it (or go to the Format menu) and choose Properties. Click on the Fill Color button. Choose bright orange and click OK to exit the Color dialog box. Click OK to exit the Properties dialog box. Selecting and Aligning Multiple Items In order to use the alignment tools and to format multiple items at once, you have different ways of selecting multiple items. Page 11 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools • • • • • To select non-adjacent columns, use Ctrl+click in the column control bar to select multiple columns. To select a contiguous block of columns, click the first column in the column control bar, then use Shift+click to select the last column to select all columns between them. To select all items in all columns, including column headings, calculated columns, subtotals, and grand totals, use Alt+click in the column control bar. To select all items in a single band, use Alt+click on one of the items in the band. To select several items in a band, select one and hold the shift-key down while selecting the rest. Once you have selected several objects within a band, you can use the Layout menu or Alignment toolbar arrange them all neatly. (If you need to turn on the Alignment toolbar, go to View > Toolbars > Alignment.) For example: • • • To make all of them the same height, choose Layout > Make Same Size > Height. To align them vertically by their tops, Layout > Align > Top. To space them evenly horizontally, Layout > Space Evenly > Horizontally The first object you select sets the standard of size or position for the others. • • • • • • • • • Select non-adjacent columns by going to the column control bar, selecting the Product Name column and Ctrl+clicking on the Store Name column. Change the font color for those columns to blue using the formatting toolbar. Select adjacent columns by going to the column control bar, clicking on the Pga Experience column and Shift+clicking on the Sum Ext Price column. Change the alignment for those columns to centered using the formatting toolbar. Select the page header band (where the column headings are), and use the resizing handles on it to make the band taller. Select the Staff Name item and drag it to the bottom of the band. Click in the white space above Staff Name to unselect that item. Select all items in the band by using Alt+click on Staff Name. Click the Align Bottom toolbar button to get all other column titles to line up with Staff Name. Page 12 of 13 Revised 1/14/2009 Freestyle Reports DW DIG –Basic Skills and Tools Saving Reports We talked at the very start of this session about the two methods of creating reports: BI Query Standard reports, and BI Query Reports. The report files that are created by the two methods are also named differently and stored in different default locations. A report created using BI Query Standard Reports is saved in the Queries folder under your model, along with the query file. The query and report files usually have the same name, but different extensions. The query file ends with .qry and the report file ends with .rpt. The file is saved in BI Query User by going to the Report menu and choosing "Save Report Specifications". A report created by BI Query Reports is saved in a Reports folder under your model. Most of the OUS and OSU data warehouse models are split data models, where there is an admin folder and a user folder, each with their own reports folder underneath. Reports you create will usually be in the \modelname\User\Reports folder. The query code is embedded in the file, so you don't have to save the query separately, although I recommend you do anyway because it may make later changes easier. The data last used in the report is also saved. The file is saved in BI Query Reports by going to the File menu and choosing Save. The file extension will be .rep. The RetailGolf sample data model, is not a split model, so it will only have a single reports folder. Also, it is usually not stored with your other models. BI Query Reports files created in this model are typically saved in C:\Program Files\Hummingbird\BI\Data Models\SAMPLEDATA\Reports. (Note: Depending on the policies of your computer tech support person, you may not have been granted the ability to save changes to that folder.) Additional Reference Materials BI Query manuals can be found at http://oregonstate.edu/dept/computing/warehouse/handouts.html In particular, you might want to refer to the BI Query Reports User's Guide and the Advanced Report Building Training Manual for additional information about using BI Query Reports. Our team contact information can be found at http://oregonstate.edu/dept/computing/warehouse/contact.html Page 13 of 13 Revised 1/14/2009