Home

BI Query™Queries - Oregon State University

1. cee 125 using buttons to export to applications ee 227 268 results options acHons Ofo unn button preferences 0 eee preferences ynin Save Column Headings Save PrOMPtSensecrniiiss Save Query Name specifying for export s s s Text IN Quotes cccccccccsseeesceesseeeeees Transpose results section in standard reports 152 results sets COMDINING istrie iaeiei 141 combining OVervieW cece 140 definitio matsie vied eases eehearsiateeseeech 125 SUPET QUETICS oo cette ee eeeee tetas 129 results exporting by copying and pasting 0 eee 219 Op HOTS eter ui decist aud ani ES 220 to an application oes 227 retrieving data Model oiera 19 Cahot EET 35 241 values using variables 114 TEUSINE QUETIOS shoiron eoii s 35 reverse qualifications eee eeeeeeeeeees 42 FIGht onteer JOINS neuiu o 144 rows NEE LE E E 141 database rieren a a a N 4 TO E0 aT ANAO 31 Apt files EAA E EAEE 174 Ron Modein AS SR he 11 run DDE command eee 233 running GQUETICS osi e Hire Ree a ea 29 S Save Column Headings results option 220 Save Prompts results option 220 Save Query Name results option 220 Index saving combined gueiet sarosopan 33 data values queries s s s 33 dynamic relationships 0 0 0 198 GQUIETICS 5 3282 fiat tettie Marte ch a results subqueries Sealat fUNCHON sanani scheduling queries by using vari
2. 2 In the Preferences dialog box click Data Formatting The Format Preferences dialog box opens 3 Click the ellipsis button for the data type category you want to format The Format dialog box opens Chapter 4 Formatting Data Specifying Data Model Default Formats Use the Format Preferences dialog box to specify the default formats for the entire data model These defaults apply to any target attribute or result report column that does not have its own format Format Preferences emmo x C e Note A data model can connect to more than one DBMS The data type for a given attribute is determined by the DBMS and the connection to it However the default formats you specify for the data model are not connection specific they apply to all design windows and therefore all connections To specify the data model format for a data type category 1 On the Tools menu click Preferences 2 In the Preferences dialog box click Data Formatting 63 BI Query Queries User s Guide 3 In the Format Preferences dialog box do one of the following e To use a predefined format select it from the drop down list that corresponds to the category To use the Regional Settings defined in the Control Panel select lt Regional Settings gt w e To specify a custom format select lt Custom gt from the drop down list then click the ellipsis button In the Format dialog box
3. Export Options x Application Excel X Execute command line l c program files microsoft office office Excel exe a Service name Excel Commands DDE Paste z C Menu item Note Microsoft Office 97 and up no longer support exporting by Menu item Do one of the following e Ifthe path for the application is defined in your system PATH variable type the name of the application in the Execute Command Line box Use the name that appears in the title bar of the main window for the application e Ifthe executable for the application isn t in your system PATH variable type the full path and file name of the executable file in the Execute Command Line box Alternatively click the browse button to the right of the Execute Command Line box In the Select Application dialog box browse to and select the executable file then click Open If you want to paste results to a specific document type the full path and file name of the document in the Execute Command Line box after the application file 225 BI Query Queries User s Guide 5 6 7 For examples of DDE commands for Excel see DDE Command Examples for Excel on page 232 8 Export Methods If youre exporting using DDE and the service name is not the same as the executable name type the service name in the Service Name box The service name is the name that the application registers with the DDE server In the Commands area sele
4. To edit a standard query by modifying its SQL string 1 Make sure that the Edit Queries permission is assigned and that the Show SQL String preference is set 2 Create open or retrieve the query 3 On the Query menu click Show ed Query ProductList OF x show Product Category Product Subcategory Product Product Name Product Unit Price Product Unit Cost group by sort by Tea where 4 Make the changes to the SQL string Note If you select Show Prompt Values BI Query displays in the SQL string the information provided for prompt qualifications the last time the query was run You can t edit the SQL string while Show Prompt Values is selected 208 Chapter 10 Building Advanced Queries You can use the Custom Check permission to improve the efficiency of the query For more information see the Help for your BI Query application If you subsequently modify the query in an attribute window after changing the query s SQL BI Query displays an alert box to warn you that the modified SQL will be overwritten BI Query Admin 1020 x Q The query string will be changed to match your selections in the data model Do one of the following e Click Change to overwrite the SQL string you modified e Click Keep to undo your changes in the attribute window Using SQL Prefixes and Suffixes You can fine tune the SQL you send to the DBMS by specifying a prefix and or a suffix for e
5. BlUserID Supplies the BI Server user name for the user currently logged on to BI Server BIGroupID Supplies the list of groups to which the user currently logged on to BI Server belongs You can use this variable only if the BI Server administrator has created BI Server groups and associated users with those groups You cannot edit the predefined variables Note If a query using a variable is converted into a query data object the variable is preserved within the object For more information on query data objects see the B Query Data Models User s Guide 113 BI Query Queries User s Guide Typical Uses for Variables Variables can carry out a variety of operations In their simplest form they can retrieve values in the database so you don t have to specify them in queries For example when you need information about retailers in a particular country you can use a variable to determine the states or provinces that are included in that country rather than specify each state or province yourself Variables can also perform calculations based on database functions to work out date time and other values For example you can use a variable to retrieve retailer sales that were placed today before today and so on The variable supplies what today s date is and BI Query returns the corresponding results Variables are especially useful for queries that are run on a regular basis or are scheduled to run at specific int
6. For information on To create a single prompt modifying and deleting prompts see the Help for 1 your BI Query application 2 On the Edit menu click Prompts The Prompts dialog box opens In the Prompts dialog box click New The Edit Prompt dialog box opens Edit Prompt x Name web Prompt type fab Text z Vales Data type automatic Formatting Allow multiple selections IV Allow data value queries String a T Required In the Edit Prompt dialog box type the name of the prompt into the Name box In the Data Type list box select the data type for the prompt In the String box type the phrase that you want to prompt the user for example Please enter an employee ID To force users to supply a value for the prompt select Required to make the prompt optional clear Required If the prompt will appear in a published standard report or BI Query report for BI Web users specify its web properties in the Web area These properties apply only to prompts that appear in reports in the BI Web Portfolio Do the following a From the Prompt Type drop down list select the object for displaying the allowed data values b To let BI Web users enter multiple values for the prompt select Allow Multiple Selections This check box is enabled only if you have selected Text Box List Box or Check Boxes from the Prompt Type drop down list 101 BI Query Queries User s Guide F
7. Note If a join or append operation is performed on two of the component queries it s shown in the window above the queries and their associated operations Any operation that appears above a join or append operation is applied to the results of the join or append The bottom message pane displays information about the currently selected item in the query When you select a component query the bottom pane displays the query s SQL string When you select an operation the bottom pane shows information about what the operation does to the query results If there s an error in the query the bottom pane displays information about why the error occurred and how to correct it Example The following example shows the Super Query window for the Retailer Accounts query EE Super Query Retailer Accounts OF x 8 28 Reorder columns Name Retailer H Invoice Amount Current Receivable 7 S E Join 6 Sort by ascending Retailer 2 S 48 Invoice Amount in range 25 000 to 50 000 F Invoice Amounts S E Sort by ascending Retailer 6 5 S E Reorder columns Name Retailer Current Receivable F Retailer Receivables select retailers cust_num retailers cust_name sales ord_amount from retailers This super query performs the following sequence of operations 1 Retrieves the results of the Invoice Amounts query 2 Applies a range to the results 3 S
8. on page 248 6 Click Publish 7 Test the query to be sure that it s been published Close it then retrieve it from the Repository 240 Chapter 12 Working with BI Server Retrieving Queries Before you can retrieve a query from the BI Server Repository you must either open a local copy of the published data model on which the query is based or retrieve the published data model from the Repository To retrieve a query from BI Server 1 On the Query menu click Retrieve 2 Inthe Retrieve Query dialog box select the query you want then do one of the following To retrieve the selected query click Retrieve e To retrieve and submit the query click Run Deleting Published Queries When you delete a published query from the Repository you can delete the local copy at the same time To delete a published query 1 Ina saved and published data model on the Query menu click Retrieve 2 Inthe Retrieve Query dialog box select a query then click Delete The Delete Published Query dialog box opens 3 If you want to delete the local copy of the selected query at the same time select Also Delete Local Copy 4 Click Yes Publishing Standard Report Specifications If you are in a BI Server environment you can publish a standard report specification to the Repository where it will be available to BI Web users and other BI Query users By default the corresponding query for the report is also published You c
9. 213 Outer JOINS IN eisst sitistischiesckerstissceverecrees 216 with correlations 0 0 ccc 210 without correlations 214 215 Classic ANSI SQL 89 outer join syntax 206 Clearing previous queries seee 30 column headings in standard reports eee 152 columns adjusting size in standard reports 162 banding in standard reports 164 changing heading style in standard reports 166 combining in standard reports 163 hiding in results oes JOIN G4 2 2 cess screen alee TEOTAETING wo eee reordering in results reordering in standard reports 137 specifying order s es 31 stacked by the Report Converter 173 stacking in standard reports 165 combined data models c cecceeeceseeeeeeee 8 10 combined queries OX POTUMG a a aae eaaa saast 33 SAVINA bossin nin onn a nn aig 33 combining CUTE secceccsccectsnscescecnscescectteseeteeeeyeeees 146 Qualifications s eueuci anoreksien 42 results SetSacgnnircnccronenana 140 141 three results sets in Excel 233 commands Dynamic Data Exchange DDE 232 233 common language names substitute for database names 87 88 comparing EIET EEA actecse 47 Cates itno an a e a a a 47 521 E E E 47 component queries deleting u RE 134 editing in super queries ee 133 Condition E E es eeeeeeeeeeeeees 37 conditional prompts overview creatin seseie sess cues evescaseiees sn NAMIN testingan n a RA R connecting
10. 5 Doone of the following e To save the specification to the Queries folder click Save BI Query also saves the corresponding query to the same location if Save Query Also is selected 155 BI Query Queries User s Guide 156 e To export the report specification to a location other than the Queries folder click Export In the Export Report Specification dialog box browse to the location where you want to save the file then click Save You can also publish standard reports to the BI Server Repository Published reports can be opened by BI Web users For more information see Publishing Standard Report Specifications on page 454 Applying Report Specifications After you ve saved a report specification you can apply it to the results of other queries When you select a specification BI Query compares the columns in it with the new results If the corresponding tables and attributes match BI Query formats the columns accordingly If they don t match BI Query formats the columns based on their position in the report For example it applies the format of the first column in the report specification to the first column of results To apply a report specification to results 1 Retrieve open or generate the results you want to present in a standard report 2 On the Results menu point to Show as Report then do one of the following e To use a specification that is stored on your local machine or network
11. Full Outer Join A full outer join includes all rows from both results sets even if they do not match in the joined columns Left Outer Join A left outer join includes all rows from the left results set and those from the right that match in the joined columns Right Outer Join A right outer join includes all rows from the right results set and those from the left that match in the joined columns Note Unmatched data in a full left or right outer join is represented by a NULL in the joined column Example In the following example the first set of query results shows the total sales for products that a company has produced in the U S The second set of results shows the total payments received to date for all products sold by the company including those produced outside the U S An inner join of both results sets on Product ID shows all American products by the company that have been sold and received some payment to date A left outer join on the same column shows the sales and payments of all American products regardless of payment Query Results 1 Query Results 2 Product ID Sales Product ID Amount Received 101 15 000 101 13 050 102 52 303 102 52 303 213 995 331 1 061 331 2 561 382 10 090 511 8 000 449 900 514 2 303 818 612 818 612 145 BI Query Queries User s Guide 146 Equijoin on Product ID Product ID Sales Amount
12. Text in quotes End of file lt None gt x E Transpose i Cancel Help To specify results options and separators 1 Open the Results Options dialog box by doing one of the following To specify default results options for the data model on the Tools menu click Preferences In the Preferences dialog box click Results Options To specify results options for a particular results set on the Results menus click Options The results set must be active To specify results options for a button linked to a query open the Edit Button dialog box for the button select File from the drop down list in the Output To area then click Results Options 2 Inthe Results Options dialog box specify the option s you want In the Separators area specify the separator s you want from the Field Record and End of File drop down lists You can either type the separators or select them from the lists Use the caret character to represent the Control key For example type D to represent Control D ASCII 4 Chapter 11 Exporting Information BI Query provides the following codes to represent other ASCII characters Code Description lt TAB gt The tab character ASCII 9 lt LF gt The line feed character ASCII 10 lt CR gt The carriage return character ASCII 13 lt Space gt The space character ASCII 32 4 Click OK Export Options If you create a button that exports results dire
13. Deleting a User Created Relationship 201 Using Outer Joins in a Query 201 Specifying Join Order 203 About Join Conflicts 204 About Outer Join Syntax in Queries 206 Changing Outer Join Syntax for a Connection 206 Modifying the SQL for a Query Using SQL Prefixes and Suffixes Building Circular Queries Circular Queries with a Correlation Building a Circular Query with a Correlation Circular Queries without a Correlation Building a Circular Query without a Correlation 207 209 210 210 213 214 215 Chapter 10 Building Advanced Queries Applying Functions to Attributes You can apply a function to an attribute to perform a basic calculation such as summing or averaging results You can also apply a user defined calculation to an attribute to carry out more sophisticated calculations Aggregate functions are mathematical operations that allow you to calculate summary values from a set of values for an attribute For example you can retrieve retailers total sales by applying the SUM function to an Invoice Amount attribute Different functions are available depending on the attribute its data type and the DBMS you re using When you apply an aggregate function to an attribute BI Query automatically groups all the other attributes in the query in the order in which you selected them If this order isn t appropriate to your needs you can change it Grouping organizes the data into sets and retrieves a summary val
14. In the attribute window click the question mark button beside the name of the attribute The Format box displays sample data in the attribute format Chapter 5 Using Data Values Files About Data Values Files 79 Types of Data Values Files 80 Naming and Storing Data Values Files 80 Order of Data Values File Use 81 Creating Data Values Results Files 82 Creating Data Values Results Files Using a Text Editor 83 Creating Data Values Query Files 85 Using a Data Values File in a Query 86 Data Aliasing 87 Creating a Data Values File to Alias Data 88 Chapter 5 Using Data Values Files About Data Values Files One way to limit what a query retrieves is by qualifying attributes with data values Before you submit the query you can insert one or more data values into a qualification either by typing them or by choosing them from a list Lists of data values are provided by data values files A data values file contains a subset of the possible values in the database These values appear as a list when you click the Data Values button Selecting data values from a list is a more accurate and efficient way to insert values than typing them E kwj Ee Data Values Prompts Variables Subqueries Data Sets all Canada France Germany UK Data values list from a data values file If no data values file is available for a particular attribute when you click the Data Values bu
15. Repository Before you can access a Repository model you must log on to BI Server e New data models are empty models that you can create and develop Only BI Query administrators can open new data models 17 BI Query Queries User s Guide 18 The BI Server Environment Before you are prompted to select a data model the BI Server Log On dialog box may open This dialog box indicates that your corporate querying environment includes a central storehouse for data models queries results reports and data sources the BI Server Repository 4 Hummingbird BI Server Log On Cancel Work Offline You can log on to use the Repository or you can work offline If you log on you can publish secure and retrieve queries and reports in the Repository You can also schedule queries and reports If you choose to work offline you do not have access to the Repository However you can still connect to the database submit queries and work with query results Opening Data Models A data model can be either split or combined Combined data model files have the extension gql To open a split data model open the user layer extension gqu If youre working online and you open a local copy of a published data model you ll receive a message if a version of that model has been published more recently than the date of the local copy Depending upon the settings specified when the data model was published you may be able to cho
16. by qualifying the Country attribute with France Germany and Spain you can restrict the results to those countries After entering a qualification click outside the qualification tree If you entered one value it appears in the qualification tree if you entered more than one value click the List button to view the values Country T France County EH List button Single value New Entry Delete All Entries Canada France Germany UK List Button Lets you add modify or delete entries in the list of qualification values Values that you enter in the Qualification box are displayed in a list when you click the List button By clicking a value in the list you can add that value to the Qualification box Alternatively use the Up or Down arrow keys on your keyboard to display each value in the Qualification box Data Values Button Lets you insert into the qualification one or more of the following e Data values For more information see Qualifying with Available Data Values on page 39 e Data sets For more information see Using a Data Set to Qualify an Attribute on page 194 37 BI Query Queries User s Guide e Prompts For more information see Inserting a Prompt in a Query on page 102 e Variables For more information see Using Variables to Qualify an Attribute on page 119 e Subqueries For more information see Creating and Inserting Subqueries on page 191
17. click Open Report Specification The Open Report Specification dialog box opens e To use a specification stored in the BI Server Repository click Retrieve Report Specification You must be in a BI Server environment to use this menu item The Retrieve Report Specification dialog box opens 3 In the dialog box double click the specification you want to apply to the current results set Chapter 9 Creating Standard Reports When you print a report you can include a title page that provides the date time number of pages and list of prompt values used in the query Before printing the report Click Title Page on the Report menu Printing and Exporting Standard Reports You can print a standard report to a printer or a file or export it directly to a text file Standard reports print exactly as they appear on screen To print a report 1 Create the report or run the query associated with it 2 On the File menu click Print The Print dialog box opens 3 Doone of the following e To print to a printer specify the options you want then click OK To print to a file select Print to File then click OK In the Print To File dialog box specify a name for the file then click OK To export a report to a text file 1 Create the report or run the query associated with it 2 On the Report menu click Export Report 3 In the Export Report to File dialog box specify a location and name for the file 4 Click
18. specify the custom setting then click OK For more information see Formatting Data The Basics on page 60 4 Repeat step 3 for any other category you want to format 5 ClickOK Setting the Null Values Option You can format NULL values for a particular attribute result report column or data type You can also set the default format for all NULL values in the data model To specify NULL formatting for an attribute column or data type 1 Open the Format dialog box for the target you want to format For more information see Opening the Format Dialog Box on page 61 2 From the Format Type list select Null Values 3 Doone of the following e To use a predefined format select it from the Predefined Formats list To specify your own display format for NULL values type it in the Display box 4 ClickOK To specify the data model format for NULL values 1 On the Tools menu click Preferences 2 In the Preferences dialog box click Data Formatting 64 Chapter 4 Formatting Data 3 In the Format Preferences dialog box do one of the following To use a predefined format select it from the Null Values drop down list To use the Regional Settings defined in the Control Panel select lt Regional Settings gt To specify a custom format select lt Custom gt from the drop down list then click the ellipsis button In the Format dialog box specify the custom setting then click OK 4 Cli
19. to a database oo eeeeeeteeeees 22 toa DBMS 25 sco cae te cas Shee ston a oh a8 es cae ao 22 BI Query Queries User s Guide connection files changing outer join syntax 206 connecting to a DBMS ou eee 22 e T ssc lsshcieee EEEE hoses EE 22 using non default 0 ce eeeeeteteeeeeeee 22 connections database Trenuri a ieee 22 DBMS sccintirnisiie weitaaniien eile 22 Contains operator match any match all eeeeeteeees 48 converting single standard reports 0 eee 174 copying results for export s es 219 correcting errors in super query windows 135 QUICTICS iiesitesseeceicthaciosesetaceccteteserensdistatetes 53 correlation names for data objects alias 210 correlations circular queries without 214 COUNT aggregate function s s s 181 Count All MO fier ih sseessessrsseetsrensseecncereceeeecnest 43 COUNT DISTINCT aggregate function 181 creating calculated attributes ee 188 conditional prompts 0 0 0 105 data values files to alias data 88 data values query files eee 85 data values results files 0 1000000 82 83 dynamic relationshipS 0 ceeeee 198 group PLOMPTs ceecceeseesseeseeseeteeees 103 Prompts iioii eia enia iaeia en nisa ie 101 GUCLICS T 28 SUDQUETIES iiipin ieni 191 ee le E TE EEE 118 variables OVErvieWieanid eee 114 creating tables Wi the databases ssscscss csseesh sebeees yest eee ses 196 curly braces cu
20. BI Query also provides a set of predefined formats for each category that you can use to format a particular attribute column or data model default The predefined formats include the Regional Settings defined in the Control Panel If you are formatting an attribute or result report column you can also select the default data model format String A template for the category in the form of a string Using special control characters in the string you can add or remove characters in all values of the selected attribute or result column For example you could format a string of 10 digits into a recognizable telephone number This option is not available for data model formats Chapter 4 Formatting Data Format Option Description Null values The format for any NULL values in the category This format is specific to NULL values in the category it is not the same as the default format for the Null data type category Computed string A conditional replacement string for values in the category You can change the formatting color and value of data in the selected attribute or result column based on a comparison between the original data and one or more specified values For example you could replace all values less than a certain number with the word Unsatisfactory This option is not available for default data model formats Format Order You can set more than one formatting option for a give
21. E bs Note BI Query cannot reliably interpret ambiguous dates such as 04 05 2002 in which the day can be confused for the month or 04 05 02 in which the day month and year are not obvious To ensure that BI Query interprets dates correctly make sure that you type dates using all four digits of the year and if necessary use the name of a month rather than its number Converting Year Formats You can specify a date window to control how BI Query converts two digit years to four digit years The date window consists of a start year and end year If you specify a date window with a start year of 1918 and an end year of 2017 BI Query adds the century 19 to all two digit years greater than or equal to 18 and the century 20 to all two digit years less than or equal to 17 When you enter 45 BI Query converts it to 1945 when you enter 15 BI Query converts it to 2015 Chapter 4 Formatting Data Note e Ifyou change the date window after you ve entered two digit years BI Query doesn t apply the new date window to those dates e Changes you make to the date window in one Hummingbird BI product affect all other Hummingbird BI products To specify the date window 1 On the Tools menu click Date Entry The Date Entry dialog box opens 2 Inthe Start Year Is box type the start year for the date window The end year is calculated automatically 3 Select Sliding or Fixed A sliding date window adds one to the start and en
22. E era eais Saiit 59 Formatting Data The Basics osmasi a laaa ania eaaa 60 Opening the Format Dialog Box sssesssesessesssesssessesssessrersrerreessessreeseessessees 61 Specifying Data Model Default Formats eeeeeeseeessceeeeeeseeeeeeeeeeeeees 63 Setting the Null Values Option wo arnser trne ti Nais 64 Setting the String Option ssicivsecesiaces isnt eis te eE EE E cress 65 Setting the Computed String Option se ssesssessesesesersrersrrerersrerseesseesresseesees 66 Contents Formatting Specific Data Types 0 essssescsseseeseeeesseceeessscesssceeesssceesseeessceeesseeeaaees 68 Formatting Boolean Data 0 ieee eescecssceesseeessceesseeesaeeesaceesaeeesseeeeaeeesaeees 68 Formatting Numeric Data 0 0 eesececesseeecesceecesseeeeessneecesseeesesaaeeeesaes 69 Formatting Date and Time Data 0 ee ee eseesseesseeeeeeesseeesaeersneeesaeeesaeees 71 Formatting Data for Data Entry sein a aaeei a i 72 Setting the Data Entry Format 2 0 0 eee eesceesseeesceesseeeesceceseeceseeseseeseaeeees 73 Entering Date and Numeric Data oo eee eeeeeesecesseeceseeeeeeceseeesaeeesaeeeaees 73 Convertine Year Formats esteiras ieres renei Ei A TTSS 74 Correcting Invalid Entries s ssseseseseesseesesssessressessrersesssesseersressresreesresseessese 75 Chapter 5 Using Data Values Files 77 About Data Valties Files c cvccsiiseaceeettsseoitias qevtitepriaksvens is iaceas cdasidessgeedaedeegones 79 Types of Data Values Biles siscsss ccctessieatteietssaav
23. File to Alias Data You can create a data values file to alias data so that a user can see values in the data values list that are meaningful such as last names while still using values from the database such as ID numbers To create a data values file that aliases data 1 Inthe attribute window as you construct a new query select the attribute that is to be sent to the database and the attribute you want to serve as the alias whose values are displayed in the data values list 2 Click the Sort box corresponding to the alias attribute Staff lolx Attribute Group Sort 4 JC Empore 7 3 Doone of the following Save the query as a data values query file naming it appropriately in the Save Query dialog box e Submit the query and save the results as a data values results file 4 Name the file after the first attribute the attribute sent to the database Chapter 6 About Prompts Types of Prompts Naming Conventions for Prompts Data Types for Prompts Required Versus Optional Prompts Prompts for BI Web Reports Common Tasks Using Prompts Creating a Single Prompt Inserting a Prompt in a Query Creating a Group Prompt Testing a Group Prompt Creating a Conditional Prompt Testing a Conditional Prompt Importing Prompts Showing Prompt Values in Queries Specifying Web Reporting Values Using Prompts 91 92 94 94 96 97 99 100 102 103 103 105 105 106 107 107 Chapter 6 Usi
24. Guide 176 To specify a destination folder for the new BI Query Reports files 1 In the Bl Query Standard Reports Converter dialog box click the Options tab 2 In the Default Destination Directory box type the full path of the destination folder you want 3 Click Close 4 Restart the converter You must restart the converter for your changes to take effect lt lt lt 5 Convert the files For more information see Standard Reports on page 174 Converting Multiple To change the working directory for the temporary conversion files 1 In the Bl Query Standard Reports Converter dialog box click the Options tab 2 In the Working Directory box type the full path of the folder you want Make sure that the working folder is not the same as the destination folder 3 Convert the files Chapter 10 Building Advanced Queries Applying Functions to Attributes 179 Built In Aggregate Functions 180 Types of Grouping 181 Applying a Function After Grouping 182 Applying a Function Before Grouping 184 Grouping without Functions 185 Creating Calculated Attributes 186 Using Functions in Query Expressions 189 Using Subqueries 190 Creating and Inserting Subqueries 191 Using Data Sets 192 Data Set File Syntax 193 Using a Data Set to Qualify an Attribute 194 Viewing Data Sets 194 Using a Data Values Results File as a Data Set 195 Creating Tables in the Database from Query Results 196 Creating Dynamic Relationships 197
25. Prompts then click the right arrow button to move the selected prompts to the Current Prompts box 5 Click Import and in the Prompts dialog box click Close Showing Prompt Values in Queries Values that have been typed into prompts at run time can be viewed in the SQL string area of the Query window To view the SQL string the Show SQL String preference must be set To show a prompt value in a query 1 Open the Query window 2 Inthe Query window select Show Prompt Values If you have entered prompt values they are usually displayed in the WHERE clause of the SQL string For calculated attributes they appear in the SELECT clause If no values were entered for the prompt the prompt does not appear in the SQL string Specifying Web Reporting Values If you are creating a prompt for a BI Web report and the prompt does not use a text box date entry time entry or date amp time entry you can specify the web reporting values explicitly The values you specify can come from the database in which case you must specify a particular data object and attribute in that object You can also enter your own values directly 107 BI Query Queries User s Guide To specify web reporting values for a prompt 1 Open the Web Reporting Values for Prompt dialog box for the prompt In the Edit Prompt dialog box click Values Web Reporting Yalues for Prompt stores city x Web Values Data O
26. Received 101 15 000 13 050 102 52 303 52 303 331 2 561 1 061 818 612 612 Query Results 1 Left Outer Joined to Query Results 2 on Product ID Product ID Sales Amount Received 101 15 000 13 050 102 52 303 52 303 213 995 NULL 331 2 561 1 061 511 8 000 NULL 514 2 303 NULL 818 612 612 Combining Results by Joining Columns Use the Join Columns dialog box to specify which results sets to join on which columns You must also specify the type of column join The joined columns must have the same or similar data type You can join one or more pairs of columns Typically you should join columns that represent primary keys To join results set columns 1 Open the Join Columns dialog box by doing one of the following e Run one of the queries whose results you want to join This query will produce the left results set With the results set active click Show on the Query menu The Super Query window opens On the Query menu point to Add Operation then click Join Open the two results sets you want to join On the Results menu click Combine then click Join Columns Chapter 8 Working with Results Join Columns x Left results Right results Current Receivable X Query Results 7 x Include all left rows and those that match from the right IV Include all left rows T Include all right rows toen A SS stored Jenone gt A SE eo
27. Results Options and Separators When you save results to files or export them to other applications be sure to specify the results options and separator characters required by the receiving application You should also set these options if you are importing data to update the database Results Options The results options you can set are as follows Results Option Description Save Query Name Includes the name of the query that generated the results This option is useful if you export to applications that use macros to perform certain operations depending on which query generated the data Save Column Headings Includes results column headings Save Prompts Includes the number of prompts used in the query followed by a record separator plus the following information for each prompt e the prompt ID followed by a field separator e the number of values used in the prompt followed by a field separator e the prompt values each separated by field separators Text in Quotes Inserts double quotation marks around textual data Chapter 11 Exporting Information Results Option Description Transpose Transposes rows and columns so that the data is stored by columns rather than rows for example the irst row of results becomes the first column of data D A B C BJE D E F CIF Original Transposed This option applies only to results saved to a file
28. Save Adding Calculations to Standard Reports For more information on calculated attributes see Creating Calculated Attributes on page 186 You can add subtotals and grand totals to standard reports to perform standard calculations sum average and so on or custom calculations such as squaring the sum of a column You can also add calculated columns similar to calculated attributes that perform user defined calculations Unlike calculated columns calculations on totals do not appear in a separate column instead they appear on separate subtotal or grand total lines 157 BI Query Queries User s Guide 158 Adding Subtotals Subtotals allow you to analyze data after results are returned A subtotal line appears below each group of information in the column displaying a label and default calculation The label is an ornament you can change its content and appearance font size and so on You can also move the label to a different location or delete it When you add a subtotal a blank header is added above each group of information You can add ornaments to the header and resize it Sales Report Customer Amount Triflex Inc Customer subtotal Cybertek Ltd Customer subtotal 2000 75 5500 00 1300 25 8801 00 3500 00 1200 00 4700 00 Blank header Subtotal line To add subtotals 1 Click the column on which to base the subtotal usually
29. Server Working with BI Server seesssesssseceseeeescseeccsceessceeeseseeeeseeeaeeeeees Requirements for Publishing Queries 0 eeeeeeeeeeeeeeeeeees Publishing Queries e aa aa a e ieee ist Retrievitig Queries sinisi saie a Deleting Published Queries cee eeescessceeeneeseeeeceeeeeaeeees Publishing Standard Report Specifications 0 leer Publishing Data Values Results Files seee Controlling Access Using BI Server sssessssesssseceseeeeeeeeeesseeeaees Users and Groups in BI Server ceeseeeseeesseeeeneeeeeeeeeseeeees Access Inheritance Rules 0 ec ceeeesecesceceseeeececeseeceeeeeeneeees About the Set Security Dialog Box oo eee eeseeeeneeeneeeeeeees Setting SCcurity ecse nr iiai e a EEES Setting Security for Queries sesessesessessesrersressressresresseeseee Using BI Server Scheduler x ssscdiccccesscsescccencacssavsvecssicenscevtonsvepsesatenens Scheduling A Query sisiiic ci cece lei eles dh avaasivandadatasvsoteoens Retrieving Scheduled Results 0 cee eeeseesseeeeneceeeeeeneeeees Appendix A Accessibility and Technical Support ACCESSIBILI erae cs costa Eaa e A 5 a E A Microsoft Accessibility Options sseeseseseseseesesseersesssersrereeesee Technical Support cides aaa eaii neta nie Index 237 orenat iir 239 239 239 241 241 241 243 243 244 244 245 246 248 249 249 250 251 253 254 254 257 xi Chapter 1 Introducing BI Query About BI Query BI Query Admin
30. Toledo 11105193 15 698 24 Toledo 01 06 94 45 800 00 Toledo 05 0193 15 000 00 a Chemex Mexico City 0419 93 11 958 00 Mexico City 1244193 20 546 16 Mexico City 09 27 93 65 300 00 A standard report window default format A standard report consists of two components e aquery that generates the results that appear in the report e areport specification that specifies how the results are formatted and what additional calculations are applied to them If the BI Query administrator has provided report specifications with the data model you can use them to create a report from query results You can also create reports using your own specifications or the default specification If you are working in a BI Server environment you can publish your standard reports to the BI Server Repository BI Web users can then access and run the reports over the Web Other BI Query users can also access the reports when they retrieve the data model from the Repository 151 BI Query Queries User s Guide For more information on using ornaments see the BI Query Data Models User s Guide 152 Note Standard Reports is an older and less sophisticated reporting facility than BI Query Reports However you can convert standard reports into BI Query Reports format For more information see Converting Standard Reports on page 173 Standard Report St ructure Standard reports are divided into the following sections Sec
31. Unlike data values results files which you usually produce using BI Query data set files can come from any source and can contain any values For example you can use an application like Microsoft Excel to create a file containing a series of comma separated values When you include a data set in a query all of the values stored in the data set qualify the query Data sets let you include large blocks of values in a query without entering them one by one They also let you edit those values using a text editor or spreadsheet program Like data values results files data sets have an associated qrr file BI Query creates the qrr file when you first include the data set in a query You can use csv comma separated files and qrd files data values results files as data sets Chapter 10 Building Advanced Queries Data Set File Syntax Data set files have a specific syntax that you must follow if you create them in a text editor Distinct values must be delimited throughout the file by one of the following delimiters e atab ASCII 9 e acomma ASCII 44 e anewline ASCII 13 10 Use only one type of delimiter throughout a given file Make sure that the final value in the file is followed by a delimiter character Use the standard HTML XML syntax for comments as follows lt comment_text gt where lt and gt are literal strings as written and comment_text is the actual text of the comment Data set commen
32. Window dialog box to open the window 6 Correct any errors 7 Save the query 133 BI Query Queries User s Guide 134 Editing an Operation in the Super Query Window You can change how an operation manipulates query results by editing the operation in the Super Query window When you select the operation you want to edit BI Query opens the same dialog box you used to create the operation The Join Columns dialog box is slightly different You can t change the results sets that are being joined When you click OK in the dialog box the changes are applied to the query If any errors occur as a result of the changes you need to correct them before you can save or submit the query To edit an operation 1 Do one of the following e Ifthe query you want to edit isn t opened on the Query menu click Open In the Open Query dialog box select the query then click Open e Inthe query s results window on the Query menu click Show 2 In the Super Query window right click an operation then click Edit Operation on the pop up menu that opens 3 In the dialog box that opens make your changes to the operation The type of operation youre editing determines which dialog box appears 4 ClickOK Note You can t edit an append operation To delete an operation 1 In the Super Query window select an operation 2 On the Query menu point to Super Queries then click Delete Operation 3 If an alert dialog box o
33. You filter results in order to see different views of your data You can specify a sort order before you submit the query or after you have results When you specify the sort order before submitting the query the DBMS does the sorting You place fewer demands on the DBMS by specifying the sort order in the results since BI Query sorts the results However if you specify the sort order in the results you can t save the query as a data values query file and you can t save the SQL for the query as a text file Note All forms of filtering results sorting results after the query has been submitted setting a range and performing calculations on results cause your query to become a super query when saved from a results window You can also filter results by adding operations in the Super Query window Getting Results in Sorted Order The rows in any column of results can be sorted into alphabetical numerical or chronological order or the reverse as appropriate You can determine which columns to sort and the order in which they are to be sorted Using the Sort dialog box you can specify a sort order before you submit the query or after you have results For example if you have a column of customer names and a column of delivery dates you can specify whether they should be organized alphabetically by customer name with delivery dates given for each customer given chronologically or chronologically by delivery date with an alpha
34. a correlation name to create a permanent copy of a data object Users of the data model can then create a query involving the aliased data object more than once without creating a circular query at the same time Example If you select the Managed By relationship last and create a correlation for Employees BI Query creates a second temporary copy of Employees called Emplo__Manag The result is two copies of Employees one connected to Departments through the Work In relationship the other temporary copy connected to Departments through Managed By Departments Departments Work In Managed By Work In Managed By Employees Employees Emplo__Manag This is what you see in the Behind the scenes BI Query creates a temporary design window copy of Employees and connects it to Departments using the Managed By relationship 211 BI Query Queries User s Guide 212 When you double click the original data object in the design window youre prompted to specify which attribute window you want to use the one for the original data object or the one for its correlation You can use one attribute window to ask one question then the other attribute window to ask the second question For example suppose you want to use the data objects illustrated above to find out which employees work for managers who make over 60 000 You need a correlation because you re asking for two kinds of information from the Employees data object the
35. a function to an attribute or create your own calculation Info Attribute Funition Quakfp Group Sort I Sort and group results Ki Select attributes to specify what data Qualify attributes to to retrieve from the database filter the data When you submit a query you choose whether to send the results directly to the results window for use in BI Query or to a text file for use in other applications or to a new table in the host database for use in creating other queries You can also schedule queries to run at certain times if you have access to the BI Server Repository and have the appropriate publishing and scheduling permissions 27 BI Query Queries User s Guide For more information on combining query results see Combining Results Sets on page 140 28 Building Queries You build a query by selecting attributes in a data object The selected attributes appear as column headings when your query brings back the information As soon as you select one attribute you have built a query It is up to you to decide how much more you want your query to include For example if you select the Store Name attribute in a Store data object and then submit the query to the database it brings back the names of all the stores in the database table in a column labeled Store Name If you also select the City attribute in the same data object and submit the query it brings back the store name
36. a that positions the cursor in Excel then pastes the results into the Button on page 227 i Ng appropriate location in the default worksheet For example to combine three results sets you could set up three buttons to execute the following commands Button Command Sequence Button A open c msoffice excel test xlm run test xlm macrol Button B run test xlm macro2 Button C run test xlm macro3 For more information on In this case you would have to click button A first However if the the global macro sheet macro sheet is in the BI Query data model folder or if the macros are see Opening Macro me on the global macro sheet you can accomplish the same result using the Sheets Automatically on following commands page 231 Button Command Button A run test xlm macrol Button B run test xlm macro2 Button C run test xlm macro3 2 Click each button in turn Don t close Excel until all three buttons have sent their results otherwise you ll create three separate worksheets 234 Chapter 11 Exporting Information For more information on macro sheets see Opening Macro Sheets Automatically on page 231 Method 2 Create a button that selects the current query then exports the results using the default paste to Excel This method reduces the number of buttons but requires you to open each query
37. add reports to the list of files to be converted click Add In the Select Files to Convert dialog box locate and select the reports then click Open You can use the Shift and Control keys to select more than one file but you can add only the reports that are associated with the selected data model To add all of the standard reports associated with a data model click Add All In the Add All Reports From Directory dialog box specify the directory containing the date model then select Include Subdirectories Alternatively in the Directory box type the explicit path for the folder containing the report specifications Click OK 6 Click Convert If the query for a standard report contains a prompt the prompt appears while the report is being converted In this case specify the prompt values you want Setting Batch Converter Options The batch converter keeps a log file of the conversion process The log file lists the changes made during the conversion and keeps a record of any problems that occurred To view or open the log file 1 In the Bl Query Standard Reports Converter dialog box click the Log tab The log appears in the Log page 2 To open the log file click Open By default the batch converter saves the converted files in the same folder as the original standard report specifications You can specify alternative destinations for the temporary conversion files and the converted files 175 BI Query Queries User s
38. attributes while creating the query You can change that Reordering Columns order before submitting the query or after you have results Before Submitting a Query on page 31 137 BI Query Queries User s Guide 138 To reorder columns after you have results 1 To open the Reorder Columns dialog box do one of the following e Inthe Super Query window on the Query menu select Add Operation then click Reorder e In the Results window on the Results menu click Reorder Columns e With a Standard Report active on the Report menu click Reorder Columns In the Reorder Columns dialog box the top to bottom order of columns in the Columns list corresponds to the left to right order of columns in the results window Reorder Columns x Columns U Country P Receivable State Down Store Id Top Bottom Hide e ston Cancel Help 2 From the Columns list select the column whose position in the list you want to change Use the Up Down Top or Bottom buttons to move the selected column to the desired position 8 3 To hide a result column select it from the Columns list then click Hide A red X icon appears beside the column in the list Hidden columns do not appear in the results window or in any report based on that results window Chapter 8 Working with Results To display a hidden column select it from the Columns list then click Show Note You cannot hide or show columns
39. click OK Finding the Administrator Layer in a Split Data Model Each user layer of a split data model contains an internal link to the administrator layer If the administrator layer file has been moved or renamed BI Query will not be able to find it when you open the model In this case BI Query prompts you for the new location To reset the internal link you must locate the file then save the data model To find the administrator layer when it s been moved or renamed 1 In the alert box that opens when you try to open the data model click Browse N Can t find the Admin layer of the data model It has probably been moved or deleted Cancel 2 Inthe Open Data Model dialog box find and open the administrator layer file 3 On the File menu click Save Chapter 2 Getting Started Working Online or Offline If you are in a BI Server environment you have the option of working online logging on to the BI Server Repository or offline You can log on to BI Server when you start up BI Query You can also log on after you have started BI Query To log on to BI Server from within BI Query 1 On the File menu click Work Online 2 Inthe BI Server Log On dialog box type your user name and password If the Domain box appears and is enabled type your domain name into it Click OK To work offline after you have logged on to BI Server On the File menu click Work Online Connecting to a DBMS For informa
40. color to the replacement value BI Query evaluates and applies each condition separately To specify the Computed String option 1 Open the Format dialog box The Computed String option is available only for formatting attributes and result report columns For more information see Opening the Format Dialog Box on page 61 Format Eg Format type Operator Original value Replacement Integer lt H sooo gt invalid B j Nul values p A o afma gt Ca gt n DO e a E Delete Row Insert Row Cancel Help In the Format dialog box select Computed String from the Format Type list Place the cursor in the first empty row of conditions From the drop down list in the Operator column select the operator you want to use to compare the results value against the test value Note You can apply the Else operator to the last condition only In the box in the Original Value column type the test value 67 BI Query Queries User s Guide 6 To specify a replacement value type it into the box in the Replacement column m 7 To specify a color for the new value in standard reports only click the color button in the same row and specify a color If you have not specified a replacement value BI Query applies the color to the value in the target 8 To specify more than one substitution condition repeat steps 3 7 for each condition 9 Click OK Note To insert a row place the cursor in the ro
41. combined e create buttons to export results automatically using menu commands or Microsofts Dynamic Data Exchange DDE e e mail the query SQL files and results files that you ve created e copy the results from a result set in BI Query and paste them directly into a document in another application If the appropriate permissions are assigned you can also send the results of a query to a new table in the database For more information on this procedure see Creating Tables in the Database from Query Results on page 196 Preparing Results for Export Before exporting you need to prepare your results for export There are three things to consider e Display format How numbers characters and dates are represented For example Microsoft Excel doesn t accept some date formats allowed in BI Query like YY MM DD For more information on data formats see About Data Formats on page 57 219 BI Query Queries User s Guide 220 e Results options Settings required by the receiving application For example a worksheet application may require double quotation marks around textual data If you re not sure what the receiving application requires check its documentation For more information see Specifying Results Options and Separators below e Export options The receiving application the export mechanism and the commands to be executed For more information see Setting Export Options on page 224
42. data object called Retailers with an attribute called country a data values file would be called Retailers country Be sure to use the case used in the attribute or prompt name Data values query and results files are stored in the Dat aVals folder BI Query stores data values results files in two separate files the qra file which stores results in text format and the qrr file which stores additional information that the application needs to read the text file Without the qrr file BI Query can t open the qrd file Data values query files have the same file extension as regular queries qry The file names you give these files determine the order in which BI Query selects the files in the DataVals folder as well as whether the corresponding data values are available in a qualification or in a prompt Order of Data Values File Use The following table shows the order in which BI Query uses data values files their names and where they re used Order Data Values File Available In 1 Results file named after the Prompt dialog boxes corresponding prompt 2 Query file named after the corresponding Prompt dialog boxes prompt 3 Results file named after the data object Qualification tree and and attribute used in the query prompt dialog boxes 4 Query file named after the data object Qualification tree and and attribute used in the query prompt dialog boxes When you click the Data Values button in a prompt dial
43. database on deleting user defined data objects and the corresponding tables see the Help for your BI Query application 196 Most of the time the tables you need already exist in the database However any query that retrieves information from a number of tables takes time because it uses multiple joins Summarizing the information in one table eliminates the joins and reduces the number of rows that the DBMS has to process This gives you faster access to the information and reduces the load on the database server For example instead of querying the Stores Sales and Sales Detail three tables and two joins tables to get a store s order history you could create an order history table that combines those records together in one table If the database administrator grants permission to create tables and if the Edit Data Model Save Data Model and Submit to Table permissions are assigned you can create tables in the database from query results This method works best when you need to analyze information that doesn t change regularly For example by creating a table that summarizes sales for a particular year you can query the table and perform calculations on the results without the values changing and becoming out of date When results are saved to a table in the database BI Query creates in the active design window a data object that corresponds to the table If the Submit to Named Table permission is assigned you c
44. default connection file for the data model 1 On the Host menu click Connect 2 Ifthe Enter Connection Information dialog box appears specify the requested information such as database user name and password Click OK To connect using a different connection file 1 On the Host menu click Connections The Connections dialog box opens 2 From the Available Connections list select a connection file If the file you want does not appear in the Available Connections list click Browse and locate the desired file on your system 3 Click Connect Chapter 2 Getting Started 4 Ifthe Enter Connection Information dialog box appears type the requested information then click OK Disconnecting from the DBMS BI Query automatically disconnects from the DBMS whenever you close a data model or exit from BI Query You may wish to disconnect at other times You can continue to carry out many activities in BI Query while not connected but you cannot submit queries send an SQL script to the DBMS or update information in the database To disconnect from the DBMS do one of the following e On the Host menu click Disconnect On the Application toolbar click the Disconnect button 23 Chapter 3 Building Queries Query Basics 27 Building Queries 28 Submitting and Clearing Queries 29 Cancelling Queries 30 Reordering Columns Before Submitting a Query 31 Sorting Rows 31 Viewing Query Specifications 33 Saving Queries 3
45. eee cece 46 Pads wilinesi susedi eian a 46 Nie aet E hile 45 IS NO PIND aie cies ds acs iscedeepcecstseetbede 49 NOT BETWEEN ee eeeseeeeeeeeeees 45 NOTIN onna eiiis 45 NULI peee pae RNN 49 User defined meriearroncici iri ian 50 qualification tree combining qualifications 0 0 0 0 42 modifying values s es 36 negative qualification ee 42 Operator list oe eeeeseeseeeeseees 45 USING ysis ts cvs N NN 36 qualifications COMBINING oaei a ate teeta 42 MOUP leserne rna e seme rers epee terry 42 multiple with and or ee 41 MEBALIVE niri eei as 42 restricting with operators 45 Index qualifying AttrIDUtES neern nae aa 36 38 attributes with prompts 00 0 0 102 attributes with variables oe 119 more than one attribute with a prompt essas 102 one attribute with another ow 44 QUEE a eaa a a queries overview with data values snis esrin with data values files cee 86 With T st ccieecleslices cure ah Mobos 47 with ranges of values 48 with subqueries asns iiis 191 with user name variables 121 GQUICTICS ei sicstuivtieisdtte E 27 OVELVICW ive aise nekss ies sks tbneseetovencsenesnsrentand 27 ad hoc UY eee I a 727 Budding eier a N 28 building with a correlation 213 Gancellin oeaan aeo Ena aSa EEn T 30 CM CU Al pinunta 210 circular with correlations 000 0 210 circular without correlations 214 215 clearing PLeVIOUS snucia uaren 30 combining results eee 140 COTE CHING wi cvsnsvesevissee
46. five prompts in one dialog box BI Query presents the distinct prompts in a group in alphanumerical order If a query contains more than five prompts in a group the first dialog box displays the first five prompts in order a second displays the next five and so on A query can contain more than one group prompt Using a group prompt in a query lets users see all the prompts at once and reduces the number of steps in the query A group prompt also relates one prompt to another Chapter 6 Using Prompts Conditional Prompts A conditional prompt is a group prompt in which the available data values for each successive prompt in the group depend on the values you specify for the preceding prompts For example a conditional prompt involving Country and City attributes may list only data values for those cities located in the country that you specify Stores By City Eg Indicates a required value Please enter a country fsa Cti lt i isis COM A pen In this case the Data Values See Sree ae button lists only cities in the BE specified country USA Kansas City Los Angeles Myrtle Beach New York City Orlando Portland San Francisco Data loaded 05 33 28 PM Conditional prompts can eliminate inappropriate data values and make it easier for users to run a given query Conditional prompts are also useful in a data model that doesn t let users build their own queries becaus
47. in a standard report directly You must hide or show the column in the results set or super query then generate the report Repeat steps 2 4 until you have achieved the desired column order Click OK Narrowing the Range of Results To help you analyze your data you can focus on a particular range of results Specifying a range for results includes only the rows within that range For example you can specify retailer credit limits between 200 000 and 600 000 sales received between June 30 and December 31 and salespeople s names between the letters A and S You can also focus on results outside a range To narrow a range of existing results 1 In a Results window point to Filter on the Results menu then click Range The Range Filter dialog box opens Range Filter x Action Include rows Exclude rows Where Zip Countr State Store Id Is gt than or Is lt than or 60000 100000 A cancel Help 139 BI Query Queries User s Guide 2 From the Where list select the column you want to filter 3 Inthels gt Than Or box type the minimum value for the range 4 Inthels lt Than Or box type the maximum value for the range 5 Do one of the following e To display the results that lie within the specified range select Include Rows e To display the results that lie outside the specified range select Exclude Rows 6 Click OK Note You can also specify a range for results before s
48. is called Stores By City Data Types for Prompts Prompts that qualify attributes automatically use the data type of those attributes However if you are creating a prompt that does not qualify an attribute such as a prompt in a join or object qualifier you need to set the data type of the prompt When users respond to the prompt the value they enter must conform to the data type of that prompt The data type for a prompt ensures that users enter values that are acceptable to the database BI Query groups prompt data types into general categories When you create a prompt you select the category that corresponds to the data type that the database expects rather than the specific type itself Chapter 6 Using Prompts Example You want to use a prompt as an argument in a DBMS function that requires Smallint values When you create the prompt you select the prompt data type category that corresponds to Smallint data type in this case Integer When the users run a query involving that prompt they must supply integer values if they enter character data or floating point values BI Query displays an alert box to warn them of the problem then lets them either modify the value to the correct format or enclose the value in curly braces to send it to the database as is Data Type Categories You can set the data type for a prompt using the Data Type list in the Edit Prompt dialog box If you are creating a prompt that qualifies
49. it cancels opening a default worksheet but that function can be included in the macro if needed The disadvantage to this approach is potentially developing a global macro sheet containing a lot of specific macros that aren t useful in every worksheet e Place the macro sheet in your system PATH variable or in the subdirectory of the data model youre using This is somewhat unorthodox but the effect is the same as placing the macros on the global macro sheet and it has the advantage of grouping the macros in a separate sheet DDE Command Examples for Excel The following table lists common DDE command sequences that you can use to send results to Microsoft Excel For other commands see your Excel user documentation Command Sequence Description paste Pastes the results into a worksheet already open in Excel or if Excel is not already running runs Excel opens a new worksheet and pastes the Clipboard contents into it 232 Chapter 11 Exporting Information Command Sequence Description new paste Pastes the results into a new worksheet in Excel open c mydocs test xls Opens the specified worksheet if Excel paste isn t already running and pastes the results into it If you often use the same format for a worksheet you can set up a worksheet template then use this command sequence to paste results into it Once the results have been pasted you can sav
50. model from within BI Query 1 On the File menu click Open In the Open Data Model dialog box select the data model file you want then click OK If the Enter Data Model Password dialog box appears type your password A message box may open to indicate that a newer version of the data model exists in the database To open the newer version click Yes Retrieving Data Models When you retrieve a data model from the BI Server Repository BI Query copies it locally Any queries data values files data sets connection files standard reports specifications and BI Query Reports files that were published with the data model are retrieved and stored in the appropriate folders You must be logged onto BI Server to retrieve data models 19 BI Query Queries User s Guide For more information see the B Query Data Models User s Guide 20 If you have the appropriate BI Server and BI Query permissions you can make and save changes to the copy then publish it to the Repository again The source data model remains on your local machine as originally saved unless you delete it To retrieve a data model 1 Do one of the following e Ifyowre starting BI Query in the Welcome to Hummingbird BI Query dialog box click Repository Data Model If BI Query is already running close the data model youre using then on the File menu click Retrieve 2 Inthe Retrieve Data Model dialog box select the data model you want then
51. nnes A SE eo d Jenone gt A S eo Jenone gt A SEE eo d Jenone gt A SEE eo d enone gt A SE eo lt none gt z _ lt none gt z i i Cancel Help 2 Do one of the following e Ifyou are joining results sets directly select the left results set from the Left Results drop down list then select the right results set from the Right Results drop down list e Ifyou are using the Super Query window to join results select the right query from the Query For The Bottom Branch drop down list The left query is the super query 3 Doone of the following To create an inner join clear the Include All Left Rows and Include All Right Rows check boxes This is the default setting To create a left outer join select Include All Left Rows To create a right outer join select Include All Right Rows To create a full outer join select both check boxes 147 BI Query Queries User s Guide 4 Select the columns you want to join up to eight pairs in the order in which you want to join them Note A joined column appears only once in the combined results set 5 ClickOK 148 Chapter 9 Creating Standard Reports About Standard Reports 151 Standard Report Structure 152 Data Formats in Standard Reports 153 Basic Report Tasks 153 Creating Standard Reports 153 Saving Report Specifications 154 Applying Report Specifications 156 Printing and Exportin
52. not results exported to an application It is not available for results generated by buttons that link to queries Separators Separator characters delimit each value in a row normally a tab character and each row of data normally a carriage return A separator can also indicate the end of the file A separator must be a valid ASCII character Results Options Targets You can specify results options and separators for the following targets e The entire data model In this case the options you specify apply to all results sets and buttons you subsequently create and to all data you import for updating the database These options are the default for the model e A particular results set In this case the options apply only to the active results set they override the data model defaults e The results generated by a button linked to a query In this case the options apply only to the results sets created when you click the button the options override the data model defaults 221 BI Query Queries User s Guide For more information on options and separators see the previous topic 222 Specifying Results Options and Separators Use the Results Options dialog box to set the options and separators for a particular results set a button or the entire data model Results Options x Options Separators J Save query name Field lt TAB gt X I Save column headings I Save prompts Record lt iF gt
53. object is a text box date entry time entry or date amp time entry you cannot specify particular web reporting values You must specify these values for all other display objects The following table summarizes the available prompt options based on the display object Requiredor Multiple Data Values Specific Object Type Optional Selections Query File Data Values Prompt Allowed Allowed Allowed Text box Either Yes Yes No List box Either Yes No Yes Combo box Either No No Yes Option buttons Required No No Yes Check boxes Either Yes No Yes Date entry Either No No No Time entry Either No No No Date amp time entry Either No No No Common Tasks Using Prompts A typical application of a prompt is to improve the scope of a given query For example you could create a query about customer payment information that prompts you for a country name each time you submit the query When you want information on customers located in Argentina you submit the query and type Argent ina in response to the prompt when you want information on customers located in Australia you submit the query and type Australia in response to the prompt and so on 99 BI Query Queries User s Guide For information on using prompts in join conditions and object qualifiers see the B Query Data Models User s Guide 100 For tables that contain a large number of rows you can use prompts t
54. on types of joins see the B Query Data Models User s Guide 144 e the database doesn t support outer joins e you need to combine results from more than one database e after examining recent results sets you want to join certain columns but do not want to resubmit the component queries for example they may require too much processing time Rules for Joining Columns Adhere to the following rules and guidelines when joining a column of one results sets with a column of another e The joined columns must have the same data type However you can join columns containing floating point and money data types e Ifthe joins you need already exist in the data model you may want to create a query that retrieves the results rather than a super query that combines results sets The DBMS which can handle a single query at a time may be able to optimize the join operations more efficiently than BI Query Types of Column Joins You can specify the relationship between results sets using one of four types of join an inner join a full outer join a left outer join or a right outer join g Inner join Left outer join Right outer join Full outer join Chapter 8 Working with Results Inner Join An inner join includes only those rows that have an exact match in the joined columns BI Query actually performs a type of inner join called a natural join which displays the joined columns only once in the combined results set
55. options you want For more information see Formatting Data The Basics on page 60 Click OK In the Attribute dialog box or Format Preferences dialog box click OK if necessary Formatting Numeric Data The numeric data categories Currency Fixed Float and Integer use the same version of the Format dialog box The following table describes the category specific options for numeric data Option Description Prefix Specifies the character or set of characters that precedes the numeric value such as a dollar symbol Suffix Specifies the character or set of characters that are appended to the numeric value Such as a percent sign Scale Specifies the scale for the numeric value the number of digits after the decimal point Precision Specifies the total number of digits in the number including the digits after the decimal point but excluding any prefix and suffix characters If the actual precision of a number is smaller than the specified precision BI Query pads the number with leading zeros If the actual precision is larger than the specified precision BI Query preserves the original precision of the number 69 BI Query Queries User s Guide 70 Option Description Exponent Permits numbers with exponents If you set this option you cannot edit qualification values Hide Zero Values Removes zero values from the results Negatives In Red Displays neg
56. placing curly braces around the prompt name in the qualification tree Required Versus Optional Prompts When you create a prompt you can specify whether it is required or optional Required Prompts Are useful to prevent queries from returning too much data A required prompt forces users to supply at least one value for the prompt If users leave a required prompt empty the query does not run BI Query marks required prompts with a red asterisk in the prompt dialog box Stores By City x X Indicates a required value Please enter a country x E m Cancel Help Warning Users can remove any prompt required or otherwise from the qualification tree for a query Therefore do not use required prompts to control user access to the database Chapter 6 Using Prompts For more information see Creating Data Values Query Files on page 85 Optional Prompts Are useful if a query contains different prompts for different users not all of whom want to use each prompt Users can choose to enter no values for an optional prompt In this case if the prompt qualifies an attribute the prompt has no effect on the query BI Query removes the qualification involving the prompt from the WHERE clause of the query However if the prompt does not qualify an attribute for example if it is an argument to a function BI Query does not remove the prompt in this case an empty prompt value may produce a DBMS sy
57. produced manually The lists of values they contain are not updated by BI Query so they are sometimes called static data values files Results files reduce the load on the database server because they eliminate the need to query the database If your querying environment includes BI Server you can let other users access your data values results files by publishing them to the BI Server Repository Note Data values results files are useful only when data values change infrequently or not at all for example sales data from previous years If they must be updated often it is more efficient to create data values query files instead Naming and Storing Data Values Files Data values query and results files require special naming conventions When they supply values to be used in a prompt they use the prompt name When they re used in both qualifications and prompts for a particular attribute they use the following format data_object attribute where e attribute is the name of the attribute e data_object is the name of the data object that stores the attribute Chapter 5 Using Data Values Files For information on the order in which data value files are used see Order of Data Values File Use on page 81 For more information on prompts see About Prompts on page 91 data_object and attribute are the names used in the data model not the names of the table and column in the database For instance for a
58. report using the stored report specification If you save a query and a report specification separately and use the same name except for the extension BI Query automatically applies the specification to the results each time you submit the query Note If BI Web users run a query that has a specification associated with it BI Web presents only the query results To view the results as a standard report BI Web users must open the report directly Chapter 9 Creating Standard Reports You can also remove the specification associated with a query to avoid generating a report each time you run the query With the query open click Remove Report Specification on the Query menu You can save queries and report specifications only if the Save Queries permission is assigned To save a report specification 1 Create and format the report Add any required calculations to it 2 Onthe Report menu click Save Report Specification The Save Report Specification dialog box opens Save Report Specification x Report specifications Report name ProductList Revenue_Store_Standard ProductList_EU_Standard ProductList_NA Standard Revenue Store Standard Export Set Security Delete IV Save query also 3 In the Report Name box type the file name for the specification Do not include the rpt extension BI Query adds it automatically 4 Ifyou don t want to save the corresponding query clear Save Query Also
59. results options required by the external application For more information see Results Options and Separators on page 220 2 Open the Save Query Result to File dialog box by doing one of the following e Create or open the query that generates the results you want to save On the Query menu point to Submit To then click File e With the results window active point to Save As on the Results menu then click Results 3 In the Save Query Result to File dialog box type a name into the File Name box BI Query displays the corresponding file names with the qrd extension 4 Click Save 5 In the external application open the qrd file to view the saved results When you delete an exported results set be sure to delete both the qrd and the qrr files Exporting Results to an Application Using a Button If the Edit Data Model and Save Data Model permissions are assigned you can create a button that sends query results to an application To check on the Tools menu click Permissions When you click the button BI Query runs the linked query formats the results according to the results options you have specified copies the results to the Clipboard and starts the target application if it is not already running If you have specified any export options such as opening a particular document and pasting the Clipboard contents into it BI Query also executes those commands To use a button to export results to an applic
60. results to Excel the default DDE command is Paste When you export your results BI Query starts up Excel if it isnt already running then instructs it to paste the contents of the Clipboard the query results into a worksheet Instead of using the default Paste command you can substitute up to two commands For example you could specify the commands to open a specific worksheet and paste results into it Alternatively if you are content to use a generic worksheet you can open a macro sheet run a macro on that sheet then save the sheet under a different name Chapter 11 Exporting Information To run a macro that is not on the global sheet you must first open its sheet However you can configure a worksheet so that it opens the macro sheet automatically You can then use two commands opening a specific worksheet and running a macro to obtain the effect of three Using Two DDE Commands When you create a button that sends query results to Excel you can specify up to two DDE commands If the first of the two commands is to be executed only once such as opening a specific worksheet on startup use the pipe symbol between the two commands The pipe symbol has a different purpose for DDE commands than it does for menu commands You can also use the pipe symbol to prevent Excel from doing either of the following e opening a worksheet that is already open e pasting results into an open worksheet and over
61. t deny yourself access The symbols that indicate access settings in the Set Security dialog box are as follows Symbol Description Green light Access specifically granted Red no entry symbol Access specifically denied A Green check mark A granted access that has been inherited Red x A denied access that has been inherited Setting Security You can set security using one of the following methods By Item In this case you select the item then grant or deny access to that item for particular users and groups By User and Group In this case you select a user or a group first then select the items and grant or deny access to those items To set security 1 Open the Set Security dialog box The Set By Item page opens by default 2 To set security by item select the item from the Data Model list From the User and Groups list select the users and groups for whom you want to grant or deny access Chapter 12 Working with BI Server 3 To set security by user and group rather than by item click the Set By User and Group tab 1A Set Security RetailGolf gql lolx Setbyltem Set by User or Group Use this tab to view or set the security for one user group at a time Users and Groups Data Model Everyone RetailGolf amp 8 Marketing E DataVals Folder 28 Professional Services E Permissions Folder op 04 E Queries Folder 28 Technical Writing PerformanceComparis
62. t relate the attributes the way you need them to be related you can create dynamic relationships Dynamic relationships are typically created on the fly for temporary use In order to create a dynamic relationship between two tables you need to decide the condition under which the data in one table is related or joined to data in the second That is you need to specify a join condition For example you can create a relationship between the Retailer and Sales tables where the Retailer Number attribute in one table is made equal to the Retailer Number attribute in the other table A query that includes both data objects will then return data only for retailer numbers that appear in both tables Choosing a Join Operator When you relate two data objects you must specify an operator to determine the join condition The available operators depend on the DBMS you are using 197 BI Query Queries User s Guide 198 The default operator is the most common type of relationship it specifies that the value of an attribute from one table equals the value of an attribute from another You can also use other comparison operators greater than less than greater than or equal to less than or equal to for the join condition You may need to consult your administrator to ensure that the relationships you create use the appropriate join conditions and produce the results you want Note Depending on the DBMS you are using
63. text in an ornament Note To create a text ornament or button that contains a literal version of one of the above strings precede the string with an extra ampersand for example peas amp amp queues displays as peas amp queues Adding Text Ornaments to Standard Reports When you add an ornament to a page in a multi page report it appears on all the pages above and below that page If you add an ornament to a particular section of a report such as a subtotal or the blank header above a subtotal it s reproduced in each corresponding section To add a text ornament to a standard report 1 Inthe report window right click where you want the ornament to appear 2 On the pop up menu that opens click Create Ornament 3 Inthe Create Ornament dialog box click Edit Text The Text Style dialog box opens Text Style Es Size Style Align cc Bookman Old Style E oo Italic C Left BauerBodni Titl BT amp Center Bembo Right Book Antigua Bookman Old Style Effects Bookshelf Symbol 1 T Strikeout Bookshelf Symbol 2 T Underline Bookshelf Symbol 3 Braggadocio This is a TrueType font This same Font will be used on both Prompts your printer and your screen f Cancel Help 171 BI Query Queries User s Guide 4 In the Text box type the text for the ornament You can include any of the special text strings supported in Standard Reports For more information see Special Text Strings in
64. than one value in the Qualification box and choose to match any of the values or all of them To match any or match all values 1 With a data model open on the Query menu click New 2 Double click a data object 3 In the attribute window select an attribute and select the Qualify box in the same row 4 In the qualification tree from the Operator list for the selected attribute select one of the following operators e Begins With e Does Not Begin With e Contains e Does Not Contain 48 Chapter 3 Building Queries e Ends With Does Not End With 5 In the Qualification box type the values that you want to match Press the down arrow after entering each value 6 Inthe List box select either Match Any or Match All 7 On the Query menu click Run Alternatively on the Application toolbar click the Run Query button Determining if Data is Missing A NULL value occurs when no value has been entered into the database It doesn t represent a zero or a character space but is instead a placeholder for missing information Because NULLs don t match anything you can t retrieve them using standard comparison qualifications such as Greater Than or Less Than To include or exclude rows for which information hasn t been recorded apply the IS NULL or IS NOT NULL operator to an attribute To include or exclude rows for which information hasn t been recorded 1 In the attribute window select an attribute a
65. the defaults Automatic Numeric or Automatic Character 6 In the Expression box type the calculation Add a space between each item in the calculation You can also build the calculation using the available buttons as follows e To enter the database name of an attribute click Attributes then use the Insert Attribute Name dialog box to select and insert the name To enter a number or arithmetical operator use the key pad at the right of the dialog box For more information on To insert a function into the qualifier click Functions In the using functions in Functions dialog box select the function you want to insert from the calculated attributes see Using Functions in Query Expressions below Functions list or Other Functions list then click Insert 188 Chapter 10 Building Advanced Queries If you select a built in function from the Functions list it automatically applies to the selected attribute the function and the database name of the attribute appear in the Expression box and the Aggregate option is selected This ensures that when the calculated attribute is used in a query the other attributes in the query are grouped 7 To ensure that the other attributes in the query are grouped when the calculated attribute is used in a query select Aggregate 8 To save the attribute for future sessions select Save in Data Model 9 Click OK The calculated attribute is added below the attribute you s
66. the rows of results as the DBMS returns them from the database unless you specify a different way of sorting them You can sort rows in ascending or descending alphabetical numerical or chronological order as appropriate 31 BI Query Queries User s Guide You can also sort results after you ve submitted a query For more information see Getting Results in Sorted Order on page 136 32 Use the Sort boxes in the attribute window to sort BI Query sorts results first by the data returned for the attribute with 1 in its Sort box then by the data returned for the attribute with 2 in its Sort box and so on You can sort attributes from more than one attribute window in the same query For example suppose that you select the Store Name Short and Receivable attributes in a Store data object then click the Sort boxes for Receivable and Store Name Short in that order When you submit the query BI Query sorts the results numerically by receivable amounts first then alphabetically by abbreviated store name Info Attribute 7 Zip To specify a sort order in an attribute window 1 Inan attribute window select the attributes you want to sort 2 Click the Sort box for each attribute in the sort order you want 3 To reverse the sort method ascending or descending for any sorted attribute click the Sort box again for that attribute m Ascending sort Descending sort Chapter 3 Building Querie
67. us 206 CONMICHS His ee ee eet EEE ERES in circular queries in dynamic relationships TD QUETICS ianen iiia JOU OFMEL ose cece ee este ee tees eeeeeeeees TOW SYMLAX E E T syntax changing per connection 206 using legacy joins as iS oe 53 outer joins join ordet seee 201 Automatically Order Outer Joins preference and permission 203 Show Join Order preference 201 specifying join Orde ose 203 P passwords BI Server log OM osetinai iik data model paste DDE commanid performing calculations using Variables ccceeeeeeseteeeeeeeenees permissions Automatically Order Outer Joins 202 203 placeholders examples of use in variables 0 0 0 0 116 for attributes and operators 115 preferences Automatically Order Outer Joins 202 203 Preserve Legacy Query Outer Joins 52 53 results options oeeie 220 Show Join Conflicts cccceceseeseeees 204 Show Join Order ceeeeeeeeee 201 202 prefixes SQM scticsssceecsessshsrHajesssdeocest tein ates 209 Preserve Legacy Query Outer Joins preference 52 53 printing standard reports eee 157 BI Query Queries User s Guide prompts OVELVICW i sssstssssevstess seus aa 91 and curly braces s es 103 appearance in BI Web wu ee 91 automatic formatting conditional creating conditional overview conditional testing CLeAtiNg oo data types data values allowed BI Web 97 data values in BI Web eee 97 default v
68. using in a query qualifier 121 borders adding to standard reports 168 building circular queries without correlations 215 GUCTICS AAE AE Shh tates teas 28 buttons combining results sets in Excel 233 FTCA T S 7 EXport OPTIONS oes 224 exporting results to applications 227 c calculated attributes OVELVIEW ihai eireas a tins 186 choosing functions 189 i DINT EEA EE ET 188 qualifying other attributes 44 Index calculated columns in standard reports eeeeeeeeeeeees 160 calculating a function grouping after grouping before EIn EAT PAE calculations in standard reports adding grand totals eee 159 basing one on anothe eee 160 in report COLUMNS eee 160 cancelling Qualifications riaient 36 atita e E shaaces toes candi otosteveedes 30 changing column order in results eee 137 SUPEP QUCTICS tsicseststisdscitss ct cotpetsesvircoee ee 132 characters COMPAL p ansin nnn aE 47 inserting deleting in results reports 65 matching patterns oer 46 spaces appearing as nulls 49 strings containing wildcards 0 0 0 46 checking conditional prompts eee 105 data values 2d csscd sesescstscsseeetevssssstensedsanens 39 group prompts ceceseeseseeseeeseeneeeees 103 query specifications 33 SUPET JUETIES fisiese hio a een nE eiit 135 chronological sort order 136 Circular Queries nosunu nordna aa Aee 210 building with a correlation
69. variable 1 2 On the Edit menu click Variables The Variables dialog box opens Variables Click New to create a new variable or to edit an existing variable select the variable then click Edit The Edit Variable dialog box opens Edit Variable x ThisQuarterEnd Determines today s date in order to return results for this attribute at quarter end Attribute gt fn curdatet Chapter 7 Using Variables For more information on prompts see About Prompts on page 91 For more information on scheduling see the B Server Scheduling Guide Type or edit the name and description of the variable in the Name and Description boxes In the Expression box type the expression that supplies the value for the variable You can include database values and functions calculations subqueries other variables and prompts Use curly braces to enclose functions or formats supported by your DBMS for example a current date function that BI Query doesn t support You can also use the available buttons to build the expression To insert an attribute into the expression select a data object from the Attributes drop down list then select the attribute from the list below Click Insert To specify a placeholder for an attribute place the cursor in the Expression box then click Attribute To specify a placeholder for an operator place the cursor in the Ex
70. x Left side Right side l Store x Staff x Store Name Region Office TF Include all left rows x T Include all right rows Join conditions ee Staff_S store_id 3 From the Left Side drop down list select a data object then click an attribute in the list below Left Side 4 From the Right Side drop down list select a data object then click an attribute in the list below Right Side 5 Specify the join type using the appropriate procedure Join Type Procedure Inner join From the Operator drop down list select a join operator Ensure that the Include All Left Rows and Include All Right Rows check boxes are cleared and that the join operator is not or Half Outer join Classic syntax From the Operator drop down list do one of the following e Select to make the Left Side data object row preserving e Select to make the Right Side data object row preserving The design window must support Classic syntax 199 BI Query Queries User s Guide 200 o oN DW Join Type Procedure Any outer join From the Operator drop down list select a join operator ODBC or ANSI then do any of the following SQL 92 syntax e To make the Left Side data object row preserving select Include All Left Rows e To make the Right Side data object row preserving select Include All Right Rows Note The Include All Left Rows and Include All Right Rows check boxes are a
71. you publish it 4 Click Publish Controlling Access Using BI Server For any item you publish to the BI Server Repository there may be people in your organization who need access to the information contained in it people who need customized views of it and people who shouldn t see it at all You must secure the data so that it can be viewed or changed only by the people with the authority to do so The Set Security dialog box lets you control individual as well as group access to the information you publish you can grant or deny general access and provide individual exceptions as necessary You exercise this control based on a structure of users and groups that the BI Server administrator creates 243 BI Query Queries User s Guide 244 Depending upon your needs the administrator may make it possible for you to grant system permissions to other users such as the ability to schedule queries and reports For information on these permissions and on how they re assigned see the BI Server Administrator s Guide Users and Groups in BI Server Your BI Server logon user name and password identify you as a user in a structure of users and groups created by your BI Server administrator The administrator assigns system permissions that determine the extent to which you can use the features of BI Server such as the ability to publish and retrieve items and schedule the items you publish Using the user and group structure provi
72. you select the Country attribute for a query the query retrieves all names of countries in the database table listing each in the order found and as many times as it is found If you group by Country your results set contains the name only once for each group For example no matter how many times Canada appeared as a country the name Canada appears only once in the list as the name for the whole group You can use this method to produce results sets for data values results files 185 BI Query Queries User s Guide For more information on the Distinct modifier see Using Query Modifiers on page 43 Grouping attributes in this way is similar to applying the Distinct modifier to eliminate duplicates Some databases perform better if you group attributes rather than apply the Distinct modifier to the query Note When you group attributes most DBMSs also sort the results alphabetically numerically and chronologically However grouping attributes is not the same as sorting them because the sort process does not remove duplicate rows For more information on sorting see Sorting Rows on page 31 To group without applying a function to an attribute 1 Inan attribute window select the attributes you want 2 To group the data click the Group box for the remaining attributes Creating Calculated Attributes 186 BI Query allows you to add calculated attributes to a data object A calculated attribute perfo
73. 0210 90210 D 0210 DK 0210 Using Wildcard Characters You can use wildcard characters with operators such as Begins With Ends With or Contains to make less specific searches of the DBMS There are two wildcard characters represents a character string of any length including no length _ represents a single character Examples Qualification Possible Matches Begins with _r anything that has r as the second letter Ends with Smith Ms Smith Smithers Smithsonian Contains L t d Limited Ltd Ltd Laminated Chapter 3 Building Queries Making Comparisons When you need to compare values for example to find values that are larger or smaller earlier or later in the alphabet or equal to a value you can specify the value on which you want to base the comparison in the Qualification box and apply the appropriate comparison operator While comparisons are typically applied to numbers they can also be applied to characters and dates With characters and dates the less than symbol lt means earlier in the alphabet or in chronological order the greater than symbol gt means later Examples Qualification Possible Matches Credit Limit 2 000 Credit limits exactly equal to 2 000 Credit Limit 2 000 All credit limits except those equal to 2 000 Credit Limit lt 2 000 Credit limits less than 2 000 Credit Limit lt 2 000 Credit limits less than or equal to 2 000 Credit Limit
74. 1 On the Edit menu click Variables 2 Inthe Variables dialog box click Import 3 Inthe Select Import File dialog box open the Prompts file containing the variables you want to add to the current data model The Import Variables dialog box opens Import Variables x Available variables Current variables 8 BIGroupID 8 BIUserID 8 DBUserID Products States Products gt States Determines today s date in order to return results For this attribute at quarter end 120 Chapter 7 Using Variables gt 4 Select the variable s you want from the Available Variables list then For more information on security tables see the B Query Data Models User s Guide click the right arrow button to move the selected variables to the Current Variables list 5 Click Import then in the Variables dialog box click Close Using Variables to Supply User or Group Names The creator of the data model can include a security table a data object that associates data with the value s returned by one of the predefined variables DBUserID BIUserID or BIGroupID If such an object exists in the data model you can use the predefined variables to qualify a query so that it retrieves results customized for you If you distribute that query to other users it retrieves results customized for each of them You can also use these variables to restrict data to certain users or groups To qualify a query using a user or group
75. 2 About Column Joins ccccccccccssssccccccessscccccessscscceesssecccsseesseccceseesccseesens 143 Ty pesvof Column Joins eruen enei n raa inea EE AE R aa 144 Combining Results by Joining Columns sssssssssseseesesersreeseerrererersrerrersseeee 146 Contents Chapter 9 Creating Standard Reports About Standard Reports Standard Report Structure Data Formats in Standard Reports 0 cee ceeeeeescesseeeseceseeeesseeesaeeesaeeeees Basic Report Tasks oo eseeeeeeeees Creating Standard Reports Saving Report Specifications Applying Report Specifications cecia sss sislocatassvccentenssoonseseesssdgabepsunteteces Printing and Exporting Standard Reports eceeceeecseesseeseesseeseeeseeees Adding Calculations to Standard Reports ce esssessseeceseeeeeeeeeeeseeceseeeeeeseeeaseees Adding Subtotals oe Adding Grand Totals Adding Calculated Columns Manipulating Columns in Standard Reports es eessseseeceseeceeeeesseeeceseeesseeeeaees Resizing Report Columns Combining Columns Banding Columns Stacking Columns Formatting Column Text and Headings eee eeeeseeeseeeseeeeseeeeneeesaes Customizing Column Settings 0 0 eeeeesseeeseceeseeesseeescecseessaeessaeeenaees Adding Borders to Standard Reports ecsesseesscseececeeeesseeeeessseessseeassceessceeeaseees Using Ornaments in Standard Reports se eessseeeseecesseeeceeeeeesseeesc
76. 3 Opening Saved Queries 35 Qualifying Queries 36 Using the Qualification Tree 36 Adding Modifying or Deleting Qualifications 38 Qualifying with Available Data Values 39 Applying Multiple Qualifications with And Or 41 Combining Qualifications 42 Using Negative Qualifications 42 Using Query Modifiers 43 Qualifying One Attribute with Another 44 Using Qualification Operators 45 Matching Character Patterns 46 Using Wildcard Characters 46 Making Comparisons 47 Matching Items in a List 47 Finding a Range of Values 48 Matching Any Matching All 48 Determining if Data is Missing 49 Applying Your Own Operators 50 Querying Multiple Data Objects Removing Unnecessary Joins from Queries Updating Queries Refreshing a Query Using Legacy Joins As Is 50 51 52 52 53 Chapter 3 Building Queries Query Basics For more information see Using BI Server Scheduler on page 249 In order to retrieve information from the database you need to build queries sometimes called ad hoc queries that ask the questions you want answered To build a query you choose categories of information and then decide how much of the information you actually want to receive in the results You may want the query to perform calculations on the data it retrieves before providing you with results You may also want to specify how rows are to be sorted and how columns are to be ordered in the results You build a query in the attribute window Apply
77. 91 Using Data Sets cc ctalechecdtncuiitetecdeedcteh cidh edb ses ads ucbedts dtldde anal atid E 192 Data Set File Syntax nce incor a E e aen teeing acess 193 Using a Data Set to Qualify an Attribute oo eee eeseeseeeeseeeneeeeeeeeeees 194 Viewing Data Sets nisi aia E Ea e E hin aii eedei es 194 Using a Data Values Results File as a Data Set oo eeeeessneeeseeeeseeeeees 195 Creating Tables in the Database from Query Results oe eeeeeeceseeeeeeeeeeeeeees 196 Creating Dynamic Relationships 00 0 seesssseesseeesseceseseeseecsceeesceeeasseeesseeeeaeeeeees 197 Deleting a User Created Relationship 0 0 eee eeseeeseeeeseeeeseeeeneeeesneeenes 201 Using Outer Joins in a QuETY oo eeeeseesceseeecsecscesceeeseesceesecseeseeaseaesesseesseaseeseees 201 Specifying Join Order sven csesy ives ccccedd heave te souakcodee hi aaeoa S EEESTIS 203 About Join Conflicts iisen a sedi ey odoedcasserscess beset a elon 204 About Outer Join Syntax in Queries oes eeseeeeseeceneeeeeeceaeeeeaeetsaeeeees 206 Changing Outer Join Syntax for a Connection es eeseeeeseeeeseeeeneeeeaees 206 Modifying the SQL for a QuETY oo eeesseseseseeseeeeseeeeeeseeeeecsceceacsceeesseceaceeeasseeaeees 207 Using SQL Prefixes and Suffixes 0 eee eesecesseeceseceeseeeeseeeeseeeseeesseeesaes 209 Building Circular Queries e ceases contthoreendineenia A Ei 210 Circular Queries with a Correlation cccccccssessssesssesecccecccseeseeeeeeeeenees 210 Building a Circular Query with a Correlatio
78. Adding Modifying or Deleting Qualifications To cancel a qualification You can qualify multiple attributes in a given query and qualify a given click the Condition box if attribute more than once You do not need to select an attribute to qualify it Wiere are nO Valles IN the Selecting an attribute indicates only that the attribute appears in the results Qualification box If there are qualification values click the Condition box To qualify an attribute tieni press the Delete key 1 In the attribute window click the Qualify box for an attribute you want to qualify A qualification tree appears at the bottom of the attribute window Store Info Attribute Function Piste I C alif Cz C Recre JE JL m a B Store Name Short a Click tne Qualify box for an attribute to open the qualification tree For more information on 2 Inthe qualification tree select an operator from the Operator drop available operators see down list The default operator is Using Qualification Operators on page 45 3 Qualify the attribute by doing one of the following e In the Qualification box type the qualification value To type more than one value hit the down arrow key between successive values 38 Chapter 3 Building Queries e Click the List button and select a previously entered value from the pop up menu that opens e Click the Data
79. BI Query Queries y 8697 0M a s Hummingbird BI Query Queries User s Guide 0800 8697 0M 5 31 02 Hummingbird Ltd 1 Sparks Avenue Toronto Ontario Canada M2H 2W1 Tel 1 416 496 2200 Toll Free Canada USA 1 877 FLY HUMM 1 877 359 4866 Fax 1 416 496 2207 E mail support hummingbird com or getinfo hummingbird com FTP ftp hummingbird com For more information visit www hummingbird com RESTRICTED RIGHTS LEGEND Unpublished rights reserved under the copyright laws of the United States The SOFTWARE is provided with restricted rights Use duplications or disclosure by the U S Government is subject to restrictions as set forth in subparagraph c 1 ii of The Rights in Technical Data and Computer Software clause at DFARS 252 227 7013 subparagraph c 1 and 2 a 15 of the Commercial Computer Software Restricted Rights clause at 48 CFR 52 227 19 as applicable similar clauses in the FAR and NASA FAR Supplement any successor or similar regulation Information in this document is subject to change without notice and does not represent a commitment on the part of Hummingbird Ltd Not all copyrights pertain to all products 1988 2002 Hummingbird Ltd All rights reserved Genio Genio Suite Genio Miner Genio MetaLink Genio MetaLink for SAP R 3 Genio MetaLink for ERwin Genio MetaLink for Power Designer Genio MetaLink for IDOC Genio MetaLink for BW Genio Designer Genio Scheduler Genio Administration Man
80. Comparison_NAO1 PerformanceComparison_NA0Z Product Correlation Product PerformanceAllCountries Product Year Export query Export SQL only ProductList_EU_Standard ProductList_N amp ProductList_NA amp _Standard ProductMargin RegionalOffices F RegionalPerformance01 Eee Hoe avenue Cateoory pe E I Data values queries 2 Inthe Query Name box type a name for the query 3 Doone of the following e To save the query as a file with extension qry click Save To save the query as a data values query file in the DataVals folder click Data Values Queries then click Save For more information see About Data Values Files on page 79 e To save the query in a location other than the Queries or DataVals folders click Export In the Export Query dialog box select a location for the file then click Save Note If you save a query in the results or report window BI Query saves the query that generated the results or report displayed If you are in any other window BI Query saves the current query 34 Chapter 3 Building Queries For information on retrieving a published query see Retrieving Queries on page 241 Opening Saved Queries You can submit or modify a saved query Locally stored queries in a data model that you can access without logging on to BI Server are opened queries stored in the BI Server Repository must be retrieved To open a saved query 1 On the Query m
81. Date Component list in the Format Preferences dialog box For example amp d0 displays the current date using the first predefined date format amp d1 displays the date using the second predefined format and so on amp d is equivalent to do ncorresponds to a named predefined format only The lt Regional Settings gt lt Custom gt and lt Blank gt formats do not apply in this case amp p Displays the current page number The default page number ornament contains the text Page amp p amp pp Displays the total number of pages To display a page number in the form Page 1 of 3 type Page amp p of amp pp in the text ornament amp q Displays the name of the query associated with the report If the query hasn t been saved the ornament displays the word Untitled amp prompt x Displays the values of the prompt called prompt x For example amp Count ry displays the values entered for the Country prompt This string is useful if you need to include an ornament that identifies the information on which the report is based for example Shipments to Singapore where Singapore is the value supplied for the prompt Prompt values also appear on the report title page 170 Chapter 9 Creating Standard Reports For more information on report sections see Standard Report Structure on page 152 You can change the content and appearance of these strings as you would any other
82. NULL missing values of an attribute or group ignoring any duplicates Types of Grouping Grouping can be done either before or after the calculation depending on the results you want Grouping Before Calculating When you group results before applying a function BI Query eliminates the records that don t meet the specified qualification groups the results then performs the calculation Grouping After Calculating When you group results after applying a function to an attribute you qualify the attribute with a group qualification The qualification is applied to the entire query after all aggregate functions have been calculated including any calculated attributes that are treated as aggregates and the appropriate attributes have been grouped BI Query allows you to apply a group qualification to query results obtained using aggregate functions such as SUM AVERAGE and COUNT When you group results before calculating the SQL for the query contains a WHERE clause When you group results after calculating the SQL for the query contains a HAVING clause 181 BI Query Queries User s Guide 182 Examples In order to retrieve retailers largest sales you might include the Retailer Name and Invoice Amount attributes in the query qualify Invoice Amount to be greater than 50 000 then apply the SUM function to it In this case BI Query finds all sales that are greater than 50 000 groups them by Retailer N
83. PerformanceComparis PerformanceComparis Product Correlation gr Product Performance Y Product_Year qry O f ProductList qry ProductList_EU_Stan ProductList_EU_Stan VY Product ist NA oa gt A Deny Inherit From the Users and Groups list select the user or group for whom you want to grant or deny access From the Data Model list select the items that you want to secure 4 Do one of the following To grant access click Grant To deny access click Deny e To allowa user or group to inherit security from the group of which it is a member click Inherit 5 When you have set security for as many items and as many users and groups as you want click OK 6 Publish the item 247 BI Query Queries User s Guide 248 Setting Security for Queries When you publish data models you can include queries with them and set security for the queries as part of setting security for the data model as a whole If you have the appropriate permissions you can also add a query to a previously published data model whether the model is your own or someone else s When you publish such a query it automatically has the security settings of the data model with which it s published If youre working in BI Query Admin once you ve published a query whether as part of a data model or separately you can change its security settings To set security for an i
84. Query for Selected Results If you want to retrieve more detailed data about specific results you ve retrieved you can do so in Ad Hoc Drill Down Mode In this mode you select one or more results cells then submit a query that returns results only for those values To modify a query for selected results 1 In the results window on the Results menu click Ad Hoc Drill Down 2 Select the cell s on which to base your query Current Receivable Of x The original query returns a list of store names store IDs and receivables Cells you select using Ad Hoc Drill Down are indicated in grey cm _ E l 5l el _ E 1 l 1 E When you select a single cell drilldown appears in the qualification tree instead of the contents of the cell E E Ge 127 BI Query Queries User s Guide 3 128 When you select more than one cell the List button appears you can use it to view and modify the list of cells you selected The List button displays a list of cells selected in the results window Do one of the following e To retrieve results that match these items resubmit the current query To retrieve different results for these items modify the current query then submit it urent Receivable 1 The Current Receivable query has been modified in Ad Hoc Drill Down Mode to return store and country information for the stores with the six largest receivable amounts To work
85. Reports is an integrated component of BI Query For more information on Standard Reports see About Standard Reports on page 151 Types of Data Models BI Query provides two types of data models split and combined Split Data Models A split data model lets users add their own customizations to a data model including changes to their preferences and other default settings and retain those customizations when a new version of the data model is distributed This type of data model is suitable for most sites A split data model is stored as two layers an administrator layer and a user layer Administrator Layer The administrator layer of a split data model is the data model that the administrator creates and edits It consists of a file with the extension gga a Queries folder a DataVals folder and a Reports folder The administrator layer file includes all the components that make up a basic data model The administrator distributes this file plus the associated folders to all users Chapter 1 Introducing BI Query User Layer The user layer of a split data model consists of a file with the extension gqu a Queries folder a DataVals folder and a Reports folder Along with the administrator layer the administrator distributes an essentially empty copy of the user layer file to all users The user layer file contains a few default settings such as preferences It also contains the path to the administrator layer when a u
86. Standard Reports above 5 To add prompt values click Prompts In the Prompts dialog box double click the name of the prompt used in the query The prompt appears in the Text box preceded by an ampersand amp and delimited by chevron characters and 6 Specify the format for the ornament then click OK 7 Inthe Create Ornament dialog box click OK MONTROSE SHIPPING Shipments to Singapore This text ornament displays the City prompt value used in the query Cargo Units coffee 1000 grain 300 oil 60 Editing Default Ornaments in Standard Reports Use the Edit Ornament dialog box to edit the default title date and page number ornaments To edit a default ornament in a standard report 1 In the report window double click on the ornament 2 Inthe Edit Ornament dialog box click Edit Text The Text Style dialog box opens 3 In the Text box edit the text for the ornament as necessary You can include any of the special text strings supported in Standard Reports 172 Chapter 9 Creating Standard Reports 4 Specify the format for the ornament then click OK 5 In the Edit Ornament dialog box click OK Converting Standard Reports You can convert standard reports to BI Query Reports format using the Report Converter utility Once you have converted a standard report you can open and modify it in BI Query Reports Before you can use the Report Converter you must inst
87. TICS oninia 52 Regional Options Control Panel 0 58 Regional Settings Control Panel 58 relational database management system RDBMS cise cates Setar atten eee 4 relationships sist cie0shalsee hapten cent adhe 197 between unrelated data objects 198 missing from data objects 197 TOW ei EA EEEE ONENESS 197 LA i a o AAPEEE AAT EEATT 51 267 BI Query Queries User s Guide reordering GOUMANS nae 31 columns in results 137 standard reports columns 137 tepihilesscnat rans atta ital Report Converter utility installing troubleshooting nesonora reports BL QUeLY saisies BI Query Reports Standard Reports REPOSITORY aienea aoo NE a ELENE repository data model restricting qualifications with operators 45 result sets CXPOTtiNg oe eee 219 results OVELVICWE iaia ea Teasa Eit 125 BI Server environment cece 125 combining results sets 1 0 00 140 141 eh HOD ase ee eee has 7 eleta EE E E E 227 Blesensis 226 filtering OVerVIeEW ss sssssesssreeseererrereee 136 finding texterna nduna puan 125 LOTT a EEA E E 125 grouping after calculating functions 181 modifying queries for selected results 0 eee 127 narrowing the range s s s 139 OPENING dissini Misetiis aististetescteatestiecey 126 preparing for export s src 219 A AEA E teeaetent 226 SOU pe AUER RRR R RRNA 125 SOPUNG rra en U s 136 sorting before or after submitting 136 spreadsheet VieW
88. UM function to each group Most DBMSs sort each group before returning the results In this case groups are sorted first alphabetically by name then alphabetically by city Name City Invoice Amount Billings Toronto 7000 Saddler Toronto 5000 Sterling Ottawa 4000 Triteck Ottawa 2000 Triteck Toronto 4000 Built In Aggregate Functions BI Query provides the following built in functions Function Name Operation AVERAGE The average of all the values of an attribute or of each group 180 Chapter 10 Building Advanced Queries For more information on calculated attributes see Creating Calculated Attributes on page 186 Function Name Operation MAXIMUM The maximum value of an attribute or of each group The maximum is the last alphabetical item the most recent date or the highest number MINIMUM The minimum value of an attribute or of each group The minimum is the first alphabetical item the earliest date or the lowest number SUM The sum of all the values of an attribute or of each group COUNT The number of values that are not NULL missing values of an attribute or group AVERAGE DISTINCT The average of all the values of an attribute or of each group ignoring any duplicates SUM DISTINCT The sum of all the values of an attribute or of each group ignoring any duplicates COUNT DISTINCT The number of values that are not
89. User and Update Basic Concepts Database Components Data Model Components Queries Reports Types of Data Models BI Query Operation Modes Using BI Query with BI Server Nr CON FP A WwW W pai j Chapter 1 Introducing BI Query About BI Query Hummingbird BI Query is a query and reporting tool that provides a comprehensive solution for accessing analyzing and presenting data stored in enterprise databases BI Query lets users extract the information they need using a data model a graphical representation of the database Using BI Query s graphical approach users can form queries without needing to know SQL Structured Query Language the language used for retrieving data from most databases For Administrators BI Query provides the flexibility to tailor information access to the exact needs of business users The administrator makes business critical information available while maintaining data security quality and integrity For Business Users BI Query provides an easy to use visual way to query databases integrate data with other applications and generate reports BI Query Admin User and Update The BI Query product line consists of three applications Admin User and Update BI Query Admin Lets the BI Query administrator manage the use of the program by users The BI Query administrator can design data models set permissions for users set passwords and control access to the database and the func
90. Values button and select one or more data values data sets prompts variables or subqueries from the pop up menu that opens 4 Press Enter or click outside the qualification tree 5 On the Query menu click Run To add a new comparison value to the qualification 1 Click the List button then click New Entry from the pop up menu that opens 2 In the Qualification box type the new value then press Enter or click outside the qualification tree To modify existing values 1 Ifyou have qualified with multiple values click the List button then click the value you want to modify 2 Click in the Qualification box then type over the value to modify it To delete values do one of the following e Click in the Qualification box to highlight the value then press the Delete key e Ifyou have qualified with multiple values click the List button To delete the entire set of values at once click Delete All Entries Otherwise click the value you want to delete then with the value highlighted in the Qualification box press the Delete key Qualifying with Available Data Values The Data Values button in the qualification tree displays a pop up menu that lets you select a data value or set of data values to insert into the qualification for an attribute 39 BI Query Queries User s Guide When you click Data Values from the pop up menu BI Query submits a default SELECT DISTINCT query that retrieves all the data valu
91. a 68 Formatting Numeric Data 69 Formatting Date and Time Data 71 Formatting Data for Data Entry 72 Setting the Data Entry Format 73 Entering Date and Numeric Data 73 Converting Year Formats 74 Correcting Invalid Entries 75 Chapter 4 Formatting Data About Data Formats A data format specifies how BI Query represents data in a result set or report You can apply a data format to any of the following e Attributes Whenever you run a query that involves the formatted attribute BI Query applies the format to the corresponding column in the results set and report if you create a report based on the results set e Columns in a results set or report In this case you format the data after you have run a query The format you specify is limited to the current result set or report and does not apply to any other e Data types in the data model In this case the format you specify for a particular data type applies to all attributes and therefore results of that data type in the model This format is the default data model format for the data type Note The format applied to a particular attribute or result report column overrides the data model format for the corresponding data type Data Type Categories For formatting purposes BI Query organizes the various data types in the data model into general categories For example BI Query treats the distinct integer data types Tinyint Smallint Integer Largeint and so on
92. a column that s been sorted 2 On the Report menu click Add Subtotal 3 Click the default calculation box in one of the subtotals Be ee nnn r City subtotal Click the default calculation box to open the calculation pop up menu SUM sum F none AVERAGE MAXIMUM MINIMUM v SUM COUNT AVERAGE DISTINCT SUM DISTINCT COUNT DISTINCT Calculation Chapter 9 Creating Standard Reports For more information on calculated columns see Adding Calculated Columns on page 160 4 From the pop up menu that opens do one of the following e To apply a standard calculation click it e To specify your own calculation click Calculation then create the calculation in the Edit Subtotal Calculation dialog box 5 Click outside the subtotal to run the calculation Note The Count operator counts only the visible values in a column That is if you ve suppressed duplicates the count doesn t include the suppressed values To insert a page break after each subtotal in a report 1 Click in a subtotal line 2 On the Report menu click New Page After Subtotal Adding Grand Totals You can add one or more grand totals to a report to help you analyze data after results are returned For example you may wish to calculate both the sum and the average for a numeric column Sales Report Customer Amount Triflex Inc 2000 75 5500 00 1300 25 Cybertek Ltd 3500 00 1200 00 Gran
93. ables 114 searching query results 0 0 eee 125 selecting Operators fOr JOINS eee 197 separators in results options 220 SOLVED nere a a can stndatreatsestuestecanss 18 setting EXPOTt OptiSeo erii st 224 export options for Excel 230 Show Join Conflicts preference 204 Show Join Order preference 201 202 showing prompt values in queries 107 query specifications 33 sort order DES aiaeei aa e 32 TOWS K oasen ae Nies Saar EEn aE Ar ESEO ENEAS 31 EA AN N TE 136 sorting alphabetical or chronological 136 before submitting eee 31 32 PESE aniona aa 136 results before or after submitting 136 FEVETSE OLAEL aunan a be 136 TOWS aaaea aie eGuevsuebevescvedcven ieetevessenbedes 31 special text strings for standard reports s s s 170 specifications applying in standard reports 156 for standard reports eee 155 publishing cy ca iva sateen anes 241 specifying COMMN OFdErs si iss ees hes ieee awe 31 EXPOTt OPTIONS eee ceeeeeteteeeesees 224 export options for Excel 230 SOrt Orders a genni sort order of results split data models OVELVICW erir AA E A administrator layer User layer anona spreadsheet view of results SQL exporting to text files oe 230 fine tuning ee eee ee iaai e 209 mod DE lt 5 oss cecheiss aed N 208 modifying OVETVIEW sessies 207 permissions required to modify 207 pref
94. ager Genio Engine Genio Scheduling Service Genio Polling Service Genio BW Service Genio Met Data Hummingbird BI BI Analyze BI Cube Creator BI Server BI Query BI Query Admin BI Query Reports BI Query Chart Editor BI Query Update BI Query User BI Web Personal Portfolio BI Web Hummingbird Portal and Hummingbird Core Services are trademarks of Hummingbird Ltd and or its subsidiaries ACKNOWLEDGEMENTS WinHelp extensions copyright Blue Sky Software Corporation This product makes use of the Blowfish library an SSL implementation written by Eric Young 1995 1997 Eric Young Photo clipart copyright 1996 PhotoDisc Inc All rights reserved All other copyrights trademarks and tradenames are the property of their respective owners DISCLAIMER Hummingbird Ltd software and documentation has been tested and reviewed Nevertheless Hummingbird Ltd makes no warranty or representation either express or implied with respect to the software and documentation included In no event will Hummingbird Ltd be liable for direct indirect special incidental or consequential damages resulting from any defect in the software or documentation included with these products In particular Hummingbird Ltd shall have no liability for any programs or data used with these products including the cost of recovering such programs or data Related Documentation and Services Manuals All manuals are available in print and online The online versions
95. all it BI Query Reports must also be installed Note BI Query Reports treats stacked columns as separate items For example if you move one column the other columns don t move with it When the converter creates stacked columns it increases the size of the detail band to accommodate the columns in the stack then aligns the columns vertically To install the Report Converter 1 Insert the Hummingbird BI or BI Query CD into your CD ROM drive 2 Using Windows Explorer browse to the following folder on the CD ROM Utilities BI Query Report Converter 3 Double click setup exe If the error message The referenced file or dll is not a valid Windows NT image appears while you re installing the converter on Windows NT you must install the converter using the 32 switch Do the following 1 On the Windows Start menu click Run The Run dialog box opens 2 In the Open box type the path and file name of the setup exe file in the Utilities BI Query Report Converter folder Alternatively click Browse and use the Browse dialog box to locate and select the file 173 BI Query Queries User s Guide 3 In the Open box at the end of the path and file name of the setup exe file type a space followed by 32 4 ClickOK Converting Single Standard Reports Once you have installed the Report Converter you can convert a standard report to BI Query Reports format The new report automatically opens in BI Query Reports To c
96. alog box that lets you save or publish items In BI Query Admin you can also open the dialog box by clicking Set Security on the Tools menu A Set Security RetailGolf gql OF x Set by Item Set by User or Group Use this tab to view or set security for one item at a time Data Model Users and Groups E RetailGolf Everyone DataVals Folder Marketing E Permissions Folder Professional Services Queries Folder E Views Folder Marketing Marketing Reports Sales E 2 2 Technical Writing Sales Reports Welcome Clouds_Omament GolfPicture_Ornamer Marketing_Button MarketinoAe oe Refresh Inherit Help The Set Security dialog box organizes the components of the data model into the following folders DataVals Lists the data values files associated with the data model Permissions Lists the data model permissions such as Save Data Model and Edit Data Model Queries Lists the queries associated with the data model Views Lists the data model design windows and the data objects object qualifiers attributes relationships buttons and ornaments that the windows contain 245 BI Query Queries User s Guide 246 You can set security for the individual items within the folders although you can t directly grant or deny access to the folders themselves they inherit the access granted or denied to the data model You also can
97. alues nnee ens 91 display objects in BI Web uw ee 98 examples Of uses ceceseeseeeeteteeeeeeeesenees 99 group Creating 103 QLOUP OVETVICW sesssssssrsreresrsrererrrrsrereres 92 STOUPs TESINE anina n ana A 103 POIN p iais aa r s 106 in query data objects cee 91 ini variables panan ea 117 inserting in queries s s 102 naming and storing s s s 101 naming CONVENTIONS 0 cesses 94 NO fOrmattiNg ce eeeeeeeeeteeeeeeeeeeees 96 optional versus required cee 96 showing values in queries ee 107 specifying web reporting values 107 OPES eA E EAEE EAE AN 92 using curly braces secsec 102 using more than once in queries 102 using radio buttons eee 98 publishing data values results files 243 CUCTICS Sin N ee dani iit 239 standard reports specifications 241 publishing queries 0 0 cceeeeeseeeeeeeeeeeeees 239 deleting published queries 04 241 requirements Toroa A 239 266 Q 80 226 AREE Soraa e EE E REE AE D 80 226 Aa E EEEE 33 80 Qualification Edit DOX cccccececeseeseeseeteees 37 qualification operators E as diays sue disses tactsescsectvsscte essevesteite 47 Sasgeties defi a a lies aa aeei aoaie aa 47 E E A EA 47 Re cece A tecabecet uecs 45 E r E A 47 D PE E E EE 47 to oer o Na EAE eO AAEE iA ETa ESET ETRE 47 Regins With ccctscscscctsessecees cveecvescvevcvevcoens 46 BET WEEN eaae tarena ea E anete ieas 45 Does not contain
98. ame then sums them You could also apply the SUM function to a query to show the total of each salesperson s sales If you wanted the query instead to return only those salespersons whose total sales exceeded their sales quota you could apply a group qualification to produce that result BI Query would first sum the sales for each salesperson then filter for those sums that exceed the quota You could not achieve this result if you restricted the query to a value of greater than the sales quota then applied the SUM function In this case you would be telling BI Query to restrict results to only those individual sales that by themselves exceeded the quota if any BI Query would apply the SUM function to these results Applying a Function After Grouping When you apply an aggregate function to an attribute BI Query automatically groups all the other attributes in the query in the order in which you selected them You can also specify your own group order by clicking the appropriate Group boxes in the attribute window If you do not specify an order for each selected attribute BI Query automatically orders them in the order in which you selected them To apply a function after grouping 1 Inan attribute window select the attributes you want and apply any necessary qualifications 2 Click the Function box for an attribute then select a function from the pop up menu that opens Chapter 10 Building Advanced Queries Sale
99. an attribute the prompt automatically uses the data type of the attribute even if you assign a different data type to the prompt The following table lists the data type categories for prompts Data Type Category Description Automatic Formatting Ensures that a prompt has the same data type as the attribute it qualifies Do not select this type for any of the following prompts that do not qualify an attribute e prompts that qualify a date time or datetime attribute ina Teradata DBMS Text Includes the following DBMS data types c char character long long varchar test varchar varchar2 vchar varcharacter Integer Includes the following DBMS data types integer smallint number p int smallint tinyint bit serial integer integer2 integer4 byteint smint decimal p Real Includes the following DBMS data types dec decimal p s numeric p s float real double precision largeint number number p s money smallmoney smallfloat float4 floats 95 BI Query Queries User s Guide 96 Data Type Category Description Date Includes the following DBMS data types date datetime for example year to day Time Includes the following DBMS data types time datetime for example hour to second Date and Time Includes the following DBMS data types date datetime timestamp smalldatetime No Formatting Lets users enter any type of data This is equivalent to
100. an contact Hummingbird Technical Support to report problems or suggest enhancements We require product and company information before we can investigate any problems so please fill out the electronic form on the Hummingbird web site or phone in fax us the corresponding information before contacting Technical Support Appendix A Accessibility and Technical Support For Technical Support services please use the contact information for your area Hummingbird Ltd 1 Sparks Avenue North York Ontario Canada M2H 2W1 North America Tel 1 800 486 0095 8 00 am 8 00 pm EST Fax 1 613 548 7616 Email hbi support kingston hummingbird com Europe Except UK and Scandanavia Tel 33 1 55 35 96 80 9 00 am 6 00 pm GMT thr Fax 33 1 42 61 31 87 Email fr bi support hummingbird com UK and Scandanavia Tel 44 0 118 902 9507 9 00 am 5 00 pm GMT Fax 44 0 118 978 9325 Email uk support hummingbird com Other Locations 1 613 548 4355 8 00 am 8 00 pm EST 1 613 548 7616 Email hbi support kingston hummingbird com Using the Trace Utility Hummingbird provides a trace utility with the software to help troubleshoot problems you are having The trace utility simplifies problem solving by monitoring the activity of your products If you are having problems with the software Technical Support may ask you to run the trace utility reproduce the problem save the trace information and send us the r
101. an name the new table and the corresponding data object Otherwise BI Query generates the name To create a table in the database from query results 1 Create a query that retrieves the information you want to include in the table 2 On the Query menu point to Submit To then click Table The Temporary Table dialog box opens Temporary Table Eg Table name OrderHistory Cancel Help Chapter 10 Building Advanced Queries 3 Type a name for the new table then click OK When you save the data model the new table is saved with it Warning If you have the Submit to Named Table permission assigned this name is also applied to the corresponding table in the database If this query is linked to a button each time the button is used BI Query overwrites the table with current results Otherwise BI Query creates a new table each time and allows the user to name only the corresponding data object Creating Dynamic Relationships For more information on relationships see Data Model Components on page 4 For more information on join conditions see the B Query Data Models User s Guide The relationships between data objects in a data model let you form queries using more than one object Rather than using all possible relationships between data objects the data model typically provides only the most important ones When data objects don t have a relationship or when existing relationships don
102. ance your business performance As well we can provide training on how to use your Hummingbird products If requested we can design courses that are tailored to meet your organization s specific needs These courses can take place at your workplace or at our own training centers To register or for more information pricing and detailed course outlines contact Hummingbird Professional Services Telephone 1 613 548 4355 ext 1700 Fax 1 613 548 7801 Email proservices hummingbird com Website www hummingbird com Hummingbird Expos Online Hummingbird Expos Online is an electronic mailing list and online newsletter It was created to facilitate the delivery of Hummingbird product related information It also provides tips help and interaction with Hummingbird users To subscribe unsubscribe browse to the following web address http www hummingbird com expose about html User Groups and Mailing Lists The user group is an unmoderated electronic mailing list that facilitates discussion of product related issues to help users resolve common problems and to provide tips help and contact with other users To join a user group Send an e mail to listserv hummingbird com Leave the Subject line blank In the body of the e mail message type the following subscribe hbi users Your Name To unsubscribe Send and e mail to the listserv address Leave the Subject line blank and type unsubscribe hbi users in t
103. annot publish a standard report to the Repository unless the corresponding data model has also been published 241 BI Query Queries User s Guide For more information on security see Setting Security on page 246 242 BI Web users can open a standard report only if the following conditions are true e the report specification has the same name as the corresponding query e the query has been published e the users have been granted access to the query Published standard report specifications inherit the security setting applied to the data model You cannot set security on a specification independently of the model However you can effectively grant or deny access to a standard report by granting or denying access to its query because a specification is visible to BI Web users only if its query is also visible Similarly BI Query users who have retrieved a data model can refresh its standard reports only if they have been granted access to the corresponding queries To publish a report specification 1 Create and format the report Add any required calculations to it 2 On the Report menu click Publish Report Specification The Publish Report Specification dialog box opens 3 In the Report Name box type the name for the specification you want to publish Do not include the rpt extension 4 Ifyou do not want to publish the corresponding query with the specification clear Publish Query Also 5 To control acce
104. appear with a dotted border You can also press Shift Tab to go back to a previous selection within the dialog box Spacebar Press the Spacebar to toggle check boxes on and off or to select buttons in a dialog box Esc Press the Esc key to close a dialog box without implementing any new settings Enter Press the Enter key to select the highlighted item or to close a dialog box with the new settings You can also press the Enter key to close all About boxes ToolTips ToolTips appear for functional icons This feature lets users use screen reviewers to make interface information available through synthesized speech or through a refreshable braille display 253 BI Query Queries User s Guide Microsoft Accessibility Options Microsoft Windows environments contain accessibility options that let you change how you interact with the software This feature can add sound increase the magnification and create sticky keys To access the Microsoft Windows Accessibility options open Control Panel and click Accessibility If you installed the Microsoft Accessibility components for your Windows system you can also find other Accessibility tools on the Start menu under Programs Accessories Accessibility To add the Accessibility components 1 In Control Panel double click Add Remove Programs 2 On the Setup tab select the Accessibility Options check box and click Apply 3 Click OK Technical Support 254 Administrator s c
105. as one formatting category The available categories are as follows e Boolean for boolean or bit data e Currency for numeric data that represents currency values e Date for date and time date only and timestamp data e Fixed for numeric data with a fixed scale a fixed number of digits after the decimal point e Float for floating point real data e Integer for integer data of any standard byte size 57 BI Query Queries User s Guide In Windows 2000 default data formats are defined in the Regional Options item in the Control Panel 58 e Null for NULL data e Time for time only data Note The Null category does not represent a particular data type but rather the NULL values of any data type With the exception of the Boolean category BI Query takes the initial default format for each category from the Regional Settings item in the Windows Control Panel There is no Regional Settings value for boolean data Use the Format dialog box to set the format for a category The appearance and functionality of the dialog box depends on the category you want to format Data Format Options For each data type category BI Query provides the following formatting options Format Option Description Category specific options The formatting options that are specific to the category For example for numeric data types you can specify the scale the number of digits after the decimal point
106. ation 1 Build and save the query if necessary 2 IfBI Query is not in Design Mode click the Design Mode button on the Application toolbar 227 BI Query Queries User s Guide 3 On the Drawing toolbar click the Button button then click and drag the mouse over the area where you want the button to appear 4 Double click the button that appears The Edit Button dialog box opens Edit Button Text Button1 eyed Pecurentqueys OOOOOS Ej Ble tere Application E 5 In the Link To area of the Edit Button or Create Button dialog box select Query from the drop down list box on the left then select a query from the drop down list on the right To submit any ad hoc query without creating a button for each select lt Current Query gt 6 In the Output To area of the Create Button or Edit Button dialog box select Application then click Export Options The Export Options dialog box opens 7 Set the export options For more information see Setting Export Options on page 224 8 Click OK In the Edit Button dialog box click OK 228 Chapter 11 Exporting Information For more information on using your e mail client see your client documentation For more information on standard report files see About Standard Reports on page 151 Exporting Results Using E Mail If you re running an e mail application on your computer you can export BI Query files as attac
107. ative values in red in standard reports only Separators Specifies the position and format for separators such as commas and periods Negatives Specifies the text format for negative numbers Note Not all options are meaningful for each category When you set the format for numeric data you can either use a predefined format or customize your own To use a predefined format for numeric data 1 Open the Format dialog box for the target you want to format For more information see Opening the Format Dialog Box on page 61 2 Do one of the following e From the Predefined Formats list select the format you want to use To use the Regional Settings for the category select lt Regional Settings gt e To use the data model format for the category select Default available only if you are formatting an attribute or result report column 3 Apply any other options you want For more information see Formatting Data The Basics on page 60 4 ClickOK 5 In the Attribute dialog box or Format Preferences dialog box click OK if necessary Chapter 4 Formatting Data To specify a customized format for numeric data 1 nH uu A WwW N Open the Format dialog box for the target you want to format To modify an existing format select it from the Predefined Formats list Specify any of the options described above Apply any other formatting options Click OK In the Attribute dialog box o
108. available only if you are formatting an attribute or result report column 4 To modify the selected format edit the text in the Format String box 5 Apply any other options you want For more information see Formatting Data The Basics on page 60 6 Click OK 7 Inthe Attribute dialog box or Format Preferences dialog box click OK if necessary Formatting Data for Data Entry 72 The data format you specify for a given attribute governs how BI Query formats values for that attribute in a results set column or report column This format also applies when you enter data in a qualification or prompt for that attribute or when you enter data to update the database values for that attribute BI Query can recognize multiple formats for a given data type When you enter data if BI Query is able to interpret the format you use it automatically converts the data to the specified format for the attribute If you enter data in a format that BI Query is unable to interpret it displays an alert message Chapter 4 Formatting Data Warning Avoid using an attribute format for numbers or dates that hides information For example if a date attribute has the format MMM DD and you type Sept 15 2001 as the qualification value BI Query inserts 2001 09 25 into the SQL for the query but displays Sep 15 in the qualification tree If you change the value to Sept 26 BI Query interprets your change as being a complete val
109. ax dialog box closes 6 In the Edit Connection dialog box click Save or Save As to save your changes Modifying the SQL for a Query You can modify the SQL string generated by BI Query when you formulate a query However you can t directly modify the SQL for queries in which the results have been modified or combined with other results You must have the Edit Queries permission to modify the SQL You also need the Show SQL String preference to display the SQL in the Query window You can then modify anything after the SELECT word subject to any restrictions placed on you by the DBMS BI Query expects results using the data format of the query selected in the design window The modified query must return compatible results from the database If it doesn t the results may have missing misaligned or otherwise incorrect columns To avoid this problem do not change anything before the WHERE clause don t change the attributes you ve selected don t delete add or change the function you ve applied to them 207 BI Query Queries User s Guide Changes you make to the SQL string should be the last changes you make to a query If you modify the SQL string incorrectly the DBMS may return an error message The SQL string in the Query window uses attribute names that appear in the database When you edit the string you must use these database names not the display names that may appear in the attribute window
110. betized list of customers who received delivery for each date To sort results 1 Open the Sort dialog box by doing one of the following e Open the Super Query window On the Query menu point to Add Operation then click Sort e On the Results menu point to Filter then click Sort e JInan attribute window click the attributes you want to sort On the Query menu click Sort Chapter 8 Working with Results gt 2 From the Available Columns list click each column or attribute that you want to sort then click the right arrow button The selected items move to the Sort Rows By list The top to bottom order of items in the Sort Rows By list corresponds to the sort order the topmost item is sorted first Available attributes Sort rows by Store State 4Store Country Up Store Zip 4 Store Receivable Down Top Bottom Descending Sort Ascending Sort 3 To change the position of an item in the Sort Rows By list select the item then click the Up Down Top or Bottom buttons as appropriate 4 To sort an item in reverse alphabetical numerical or chronological order select the item then click the Descending Sort button 5 To sort an item in alphabetical numerical or chronological order select the item then click the Ascending Sort button 6 Click OK Reordering Columns For more information on Columns appear in results windows and Standard Reports in the order in ordering columns see which you chose
111. bined query by modifying the SQL string Your changes won t be checked for errors Chapter 8 Working with Results Editing Component Queries You edit a component query a query that s part of a larger query by copying it from the Super Query window into the data model The component query then becomes the current query allowing you to open the attribute windows you want to use and make your changes When you ve finished editing the query you paste it back into the Super Query window If any errors occur as a result of making the changes you need to correct them before you can save and submit the query To edit a component query 1 Do one of the following e Ifthe query you want to edit isn t open on the Query menu click Open In the Open Query dialog box select the query then click Open e With the cursor in the query s results window on the Query menu click Show 2 In the Super Query window right click the component query you want to edit then click Copy Query to Model on the pop up menu that opens 3 Minimize the Query window for the component query 4 Inthe design window click the appropriate data object s and make your changes to the component query 5 Inthe Super Query window right click the component query you ve edited then click Paste Query from Model on the pop up menu that opens If you can t find the Super Query window in the list on the Window menu click More Windows then use the Select
112. bject in a data model an attribute window opens listing the attributes stored in the data object Attribute window for the Sales data object Sales Saline Info Attribute Function d Orderid rm e Relationships A relationship connects two data objects together and indicates that the connected objects contain at least one attribute in common For example an Employee data object might be related to a Department data object on the basis of acommon Manager attribute In order to include the attributes from two or more data objects in a query the data objects must be related BI Query Queries User s Guide Data objects can have more than one relationship so that you can get different information using the same objects BI Query represents relationships as connecting lines between objects Relationships can also appear with a diamond icon and a name a You could use the works in relationship to find information about an employee who works in a particular department Employee Department You could use the managed by relationship to find information managed by about an employee who manages a particular department Design Windows Design windows are the workspace in which administrators design data models and users formulate queries Design windows contain the data objects that represent tables in the database and the relationships that tie them together Design windows can also contain buttons for navi
113. bjects Source Database Aberdeen Amsterdam Barcelona Belfast Berlin Boston Chicago Store Z Attributes Region Office State Store Id Store Name zi OK Cancel Help 2 To select values from the database do the following until all the values you want appear in the list a From the Data Objects list select an object From the Attributes list select an attribute b Click Load The Source Database list displays all distinct values in the database for the selected table and attribute gt c From the Source Database list select the web reporting values that you want to use for the prompt then click the right arrow button The selected values appear in the Web Values list 3 To specify your own values do the following until all the values you want appear in the list a Click Create The New Allowed Data Value dialog box opens b In the Data Value box type a data value Click OK 108 Chapter 6 Using Prompts For more information on sorting and editing web reporting values see the Help for your BI Query application 4 Set the order for the values The top to bottom order of the values in the Web Values list is the order in which they appear in the BI Web prompt form Use the available buttons E E E E H E Up Down Top Bottom Ascending Descending Sort ort Click OK 109 Chapter 7 Using Variables About Variables 113 Typical Uses for Variables 114 Expressions f
114. browse for and select a results set 3 Click OK Appendix A Accessibility and Technical Support Accessibility 253 Microsoft Accessibility Options 254 254 Technical Support Appendix A Accessibility and Technical Support Accessibility Hummingbird products are accessible to all users Wherever possible we developed our software using Microsoft Windows interface standards Depending on the product in which you are working a subset of the following accessibility features is available Access Keys Menus have associated access keys mnemonics To open any menu press Alt and the underlined letter in the menu name as it appears on the interface For example to access the File menu press Alt F Once you have opened a menu you can access an item on the menu by pressing the underlined letter in the menu item name or you can use the arrow keys to navigate the menu list Keyboard Shortcuts Some often used menu options also have shortcut accelerator keys The shortcut key for an item is listed beside it on the menu Directional arrows Use directional arrows on the keyboard to navigate through menu items or to scroll vertically and horizontally You can also use the directional arrows to navigate through multiple options For example if you have a series of radio buttons you can use the arrow keys to navigate the possible selections Tab key sequence To navigate through a dialog box press the Tab key Selected items
115. can use qrd files as data sets because they are essentially the same thing a text file containing one or more delimited values There are several advantages to using a qrd file as a data set rather than as a data values file e All values in the qrd file apply to a query You do not need to select the values as a group This makes it easy to apply a large set of values to a qualification e You can edit multiple values in the query by editing the file you do not need to edit the SQL or the qualification tree to change the values e The values in the data set are applied to the qualification at run time they are not saved with the query This means that some other person or process can update the data set any scheduled query that includes the data set will automatically use the updated qualification values Using a qrd file as a data set also lets you include values that contain delimiter characters for example 1 330 00 which includes a comma Each qrd file automatically comes with a qrr file that BI Query uses to interpret the data If you create a data values results file for an attribute whose values contain delimiters BI Query lets you choose other delimiters for the file 195 BI Query Queries User s Guide Creating Tables in the Database from Query Results A query that submits results to a tab linked to a butt the process of new tables in t is automated For information e can be on so that creating he
116. ceeeacecsaceesaeecsaeeesaees 120 Using Variables to Supply User or Group Names eee eeeeseeseeeeseeeees 121 Chapter 8 Working with Results 123 Aout REG UIES a a a AE tnastatvsua chats ertea yeestieatewm cate 125 Opening Results sron sorie en aerer adani a R 126 Modifying a Query for Selected Results sssssessessssseesesesesessssssresesesssresesessssereseseese 127 Using Super Queries erei a a aaa Ea a Ei eaaa Ei Eneas 129 About the Super Query Window sesssssssessssssesssesesersresrerssessresresssesseesees 129 Opening the Super Query Window sseeesssesessssssesssessesssersressressresreesresseee 132 Guidelines for Editing Super Queries esssseesesesessesssersrersessrersrerrerssesse 132 Editing Component Queries eessessesesesssessessressrssressressesssereressessressreseess 133 Editing an Operation in the Super Query Window sssesseeserserrerserseesee 134 Correcting BLOTS sesen feteesehsesiadstbibincoedazeticesageyhoadigsdeisbviassesisss beats 135 Filtering Results eisenai e a EA eiA 136 Getting Results in Sorted Order sesssseesesesesssessesssessesssersressrersrssreesresseese 136 Reordering Colaminss si cascciersisdsvcisagsbectciedsavsodeveveeuan e e aa 137 Narrowing the Range of Results ssessssesesesessessserssessressessrersressresseesseesees 139 Combining Results Sets diuinis enn i eae eaa aani enee ieira 140 About Appended Columns rossner 141 Combining Results by Appending ROWS ceesessseeeeseeceseeeeaeeceseeeeaees 14
117. ck in a section a border appears around it When a section is too small to accommodate the information in it or when it displays too much empty space you can resize it to improve the layout and ensure that all the information appears when you print the report Data Formats in Standard Reports When BI Query returns results from the database the format in which they appear may not be the way you want them represented in a standard report You can change the default display format for each attribute in a standard report If you save a standard report specification with a query and subsequently change the display format for an attribute in the query the original display format in the corresponding standard report does not change To apply the new format to the report change the format for the corresponding attribute in the report window then save the report specification with the query Basic Report Tasks This section describes the basic tasks you need to perform to create and save a standard report Later sections describe how to add calculations to a standard report how to manipulate its columns and how to change its appearance Creating Standard Reports When you create a standard report the report window displays the results of a query in a default format You can change the appearance of a report in a variety of ways and add information to it The title of the report corresponds to the query that generated the results To creat
118. ckOK Setting the String Option Use the String option to format any data type as a string This option lets you include additional information in the data such as a punctuation mark or a code and exclude unwanted information To use the String option you create a template string for the data the template can contain special control characters that dictate the data format The following special characters are available Character Description Inserts a character from the original data in sequence Suppresses a character in the original data in sequence g Inserts the remaining characters in the original data Inserts the next character as is Use this character to include one of the special formatting characters as itself For example type to insert the symbol To insert any other character type it where you want it to appear in the string To specify the String option 1 Open the Format dialog box The String option is available only for formatting attributes and result report columns For more information see Opening the Format Dialog Box on page 61 65 BI Query Queries User s Guide 66 2 From the Format Type list select String 3 In the Format String box type the template string you want to format the data Use the special characters described above 4 ClickOK 5 Ifyou are specifying the option for an attribute click OK in the Attribute dialog box Example A
119. ct You may also want to qualify one or more attributes to restrict the results to the particular information that interests you such as the sales information for a particular store This query returns all year month and order amount data for all stores with ID 2011 Info Attribute Geyer Won ey o em a Ki Selected attributes specify what data Qualified attributes to retrieve from the database filter the data BI Query Queries User s Guide For more information on BI Query Reports see the B Query Reports User s Guide The designer of a data model typically creates queries and saves them with the data model users can then open the queries from the data model and submit them to the database The designer can also let users edit existing queries and create their own called ad hoc queries Reports Once you ve gathered the data you want by querying you may need to present it in a report BI Query has two report generators BI Query Reports and BI Query Standard Reports that let you produce your own professional looking reports BI Query Reports provides a flexible reporting environment that includes tables crosstabs charts and maps You can open BI Query Reports from within BI Query or directly from the Hummingbird BI program group in the Windows Start menu BI Query Reports has its own online Help system which you can access from its Help menu BI Query Standard
120. ct DDE or Menu Item In the box to the right of the Commands area type the commands to be executed in the receiving application To send two DDE commands from BI Query add a vertical bar between the DDE command sequences you type The first sequence is sent to the application only if it is not already running BI Query must start it You must enter all arguments in each DDE command The menu command mechanism requires the following format menucommand command For example to run the Paste command on the Edit menu type Edit Paste Click OK When you export query results you can either save the results or SQL to a text file or send the results directly to an application using a button Exporting Results to a Text File You can export query results to a text file in order to use them again in BI Query or retrieve them in other applications such as word processors or spreadsheet programs When you open results in BI Query saves results as two files one with the extension qrd ASCII text BI Query only the grd file format for use in other applications and one with the extension qrr BI appears in the Open Query specific information such as formats for each column and the SQL Results File dialog box 226 used to retrieve the results Chapter 11 Exporting Information For more information on using buttons see the B Query Data Models User s Guide To save the results of a query 1 Specify the
121. ctly to another application you need to specify the following export options e the application to which the results will be exported e the export mechanism you re using DDE or menu commands e the commands to be executed in the receiving application When you export results to another application BI Query copies the results then checks whether the receiving application is already running and if not starts it Depending on the export mechanism BI Query then calls the appropriate menu commands in the receiving application or sends the DDE commands to the receiving application DDE lets you pre program commands in the target application s programming language such as Excel s macro facility and further manipulate the results For example you could open an existing Excel spread sheet and run a macro to perform a calculation not available in BI Query Whether you use menu commands or DDE depends to some extent on what you want the receiving application to do 223 BI Query Queries User s Guide 224 To Do This Use This Export results by specifying the menu commands to be Menu Commands executed in the receiving application For example to paste the contents of the Clipboard into a Microsoft Excel worksheet you could specify the Paste command on the Edit menu in Excel Include arguments in your commands that instruct the DDE receiving application to carry out more specific tasks than simply using menu com
122. d Total 13501 00 159 BI Query Queries User s Guide For more information on calculated columns see Adding Calculated Columns on page 160 160 To add grand totals 1 On the Report menu click Add Grand Total 2 For each column do the following a Click the default calculation at the bottom of the report WestBank Corp 100 250 00 25 893 00 26 582 23 vr i H i 1 COUNT pum 1 f b From the pop up menu that opens click a standard calculation to apply to the column Alternatively to specify your own calculation click Calculation then create the calculation in the Edit Grand Total Calculation dialog box 3 Click outside the grand total to run the calculation Adding Calculated Columns You can add a column to a report to perform a specific calculation based on information in the report such as values in a numeric column subtotals and grand totals For example suppose your report contains a list of countries order amounts for each country and a grand total of those orders You can create a calculated column to determine what percentage of the grand total each order amount represents You can also use multi pass reporting to create a column that performs a calculation on other calculated columns In this case BI Query must make two or more passes through the data to arrive at the final calculation For example suppose your report contain
123. d in each country There are three query modifiers in BI Query e Distinct Removes duplication and shows you a list of unique values that is for the above example a listing of the unique countries e Count All Lets you find how many times each item occurs in the database Using the above example you would get a listing showing the number of times each country name occurs in the database Trim Relationships Removes from a query any relationship to a data object that has no attributes selected You can also trim relationships by selecting this feature in the Preferences dialog box To use a query modifier In an attribute window before submitting a query point to Modifiers on the Query menu then click one of the query modifiers 43 BI Query Queries User s Guide For more information see Creating Calculated Attributes on page 186 44 Qualifying One Attribute with Another You can build queries in which one attribute qualifies another so that the limits of the second are applied to the first The attributes you use as qualifiers can be calculated attributes When you use an attribute as a qualifier you must use the database name of the attribute not its display name For example to get a listing of all profitable products you define profitability as a unit price at least ten per cent higher than the unit cost Unit Price gt Unit Cost 1 10 To qualify the Unit Price attribute to find all the pr
124. d year on January 1st every year For example if the date window is 1918 to 2017 it becomes 1919 to 2018 when the current year changes A fixed date window is not incremented automatically 4 ClickOK Correcting Invalid Entries BI Query alerts you if you type data in a format that it can t interpret Bl Query Admin 1249 Eg 1 The value entered is invalid If you enclose the value in Bl Query will not attempt to convert it Cancel To return to an entry and correct it Click Cancel in the alert window 75 BI Query Queries User s Guide This feature is for users with advanced knowledge of SQL database access For more information on configuring data entry formats see Setting the Data Entry Format on page 73 To force BI Query to use the entry you typed Click Add Your entry appears within curly brackets in the qualification tree When you submit a query BI Query inserts your data in the SQL string as is To determine the format for an attribute 1 Ifyou have configured the data model to use the default data model formats for data entry open the Format Preferences dialog box a On the Tools menu click Preferences b In the Preferences dialog box click Data Formatting Note the current default format for the relevant data category 2 Ifyou have configured the data model to use attribute formats for data 76 entry open the Attribute dialog box for the relevant attribute
125. database attribute stores telephone numbers as 13 character strings for example 6135554355x26 To format the data in this attribute as a recognizable phone number you can create the following template Ht HHH HHH Ext If you apply the String option using this template BI Query formats the value 6135554355x26 as 613 555 4355 Ext 26 Setting the Computed String Option The Computed String option lets you replace values in the target an attribute or result report column based on certain conditions For example to identify areas that require further attention you could replace certain values in the target all sales below a certain amount with the word Unsatisfactory If the target is a standard report you could also apply a color to these values Each condition for the Computed String option has the following components e A test value BI Query compares each value in the target against this value e An operator The operator specifies the nature of the comparison For example if you select lt as the operator BI Query checks if each value in the target is less than the test value Chapter 4 Formatting Data A replacement value lIf the result of the comparison is true BI Query substitutes the replacement value for the value in the target A replacement color optional If the target value meets the condition and the results appear in a standard report BI Query applies the specified
126. de 142 Appended Results Name Sales State Abicon Inc 12 000 Textal Ltd 113 000 Unisystems 10 145 Ziptech 6 430 Bainstream Inc 10 000 New York Knatl Corp 2 000 California MonoLitho 2 100 Ohio Textronics 60 000 Illinois Combining Results by Appending Rows Use the Append Rows dialog box to append one results set to another Append Rows x First results Current Receivable X Second results Query Results 7 x Cancel Help To append rows using existing results sets 1 Open the results sets you want to combine either by running the corresponding queries or by opening saved results set files 2 On the Results menu point to Combine then click Append Rows The Append Rows dialog box opens 3 From the First Results drop down list select the first results set 4 From the Second Results drop down list select the results set you want to append to the first set 5 Click OK Chapter 8 Working with Results For more information on Creating joins between data objects see the B Query Data Models User s Guide To append rows using queries 1 Specify the first query by doing one of the following e Open an existing super query e Runa query With the results set active click Show on the Query menu The Super Query window opens 2 On the Query menu point to Add Operation then click Append The Append Rows dialog box opens 3 From
127. ded by your BI Server administrator you can grant or deny access to the items you publish If you find that you can t use features or perform activities appropriate to your work ask your administrator to review the system permissions that have been assigned to you Access Inheritance Rules In the BI Server security structure users inherit group access according to the following rules Inheritance Rule Description Group members inherit When you grant or deny access to a group all their group s access members of the group inherit that setting if a group has access each member has access and if a group is denied access each member is denied it Members of more than If a user belongs to one group that is granted access one group inherit from the and to another group that is denied access the user groups that grant access has access Member security settings You can give a group member a setting different from override inherited group that of the member s group When you do the settings overriding setting always applies to that member even when you make it a member of other groups You can remove an overriding setting to let a member inherit from the group again Chapter 12 Working with BI Server About the Set Security Dialog Box You can set security for data models queries reports and other data model components using the Set Security dialog box This dialog box is available from any BI Query di
128. des the first prompt In this way the values supplied by the first prompt qualify the query that supplies a choice of values for the second prompt To create a conditional prompt 1 Create the group prompts you want Use the group prompt naming convention so that BI Query presents the prompts in the order you want 2 To restrict data values for the first prompt in the group create and save a data values query file that returns the data values you want for the first prompt Name this file after the name of the first prompt When you insert this prompt into a qualification BI Query makes the results of the query file available in the corresponding prompt dialog box If you do not create a data values file for the prompt BI Query uses the default SELECT DISTINCT query to generate the available data values 3 Create a new query to return the data values you want for the second prompt In this query use the first prompt to qualify the attribute that corresponds to the first prompt then apply the appropriate operator 4 Save the query as a data values query file using the name of the second prompt Testing a Conditional Prompt Before using or distributing a conditional prompt you should test it to ensure that it works properly To test a conditional prompt 1 Inan attribute window click the attributes you want 2 Use the first prompt to qualify the corresponding attribute 3 Use the second prompt to qualify the corre
129. e e adda special border to the column To customize a column 1 On the Report menu click Column Settings The Column Settings dialog box opens 2 From the Columns list click the column you want to customize 3 To change the title for the column type the new title in the Column Heading box 4 To apply a special border to the left side of the column click Special Column You can format this special border using the Report Borders for Standard Reports dialog box 5 To wrap column data that exceeds the width of the column select Wrap Column This check box is enabled only for columns containing character data 6 Click OK 167 BI Query Queries User s Guide Adding Borders to Standard Reports You can also create Using the Report Borders for Standard Reports dialog box you can add borders manually using the horizontal and vertical borders to report components to help you organize Line tool on the Drawing and highlight information For example you can add vertical lines between r U atte di columns or horizontal lines below column headings or between records borders see the Help for You can set a default border format for all standard reports you can also your Bl Query application specify borders for a particular report Report Borders for Standard Reports x Report component L report Frame column header C special column Simple Report C records primary subtotal ___ secondary subto
130. e a standard report 1 Retrieve open or generate the results you want to present in a standard report 2 On the Results menu point to Show as Report then click BI Query Standard 153 BI Query Queries User s Guide If you create a button that links to the query and sends output to a results window BI Query runs the query whenever you click the button then creates the standard report using the associated specification 154 Saving Report Specifications Report specifications help you present information consistently and adhere to corporate standards such as corporate logos and fonts If you create similar reports on a regular basis you can use a report specification to avoid formatting the data each time Bentley Bicycles EIT TIT Orders Clarion Music Orders 5 N Purchase Date Amount Racing bikes 1000 00 5000 00 Sheet music 01 02 96 100 1300 00 Instruments 11 04 96 5000 Mountain bikes 3000 00 CDs 03 10 96 140 1200 00 Cassettes 03 10 96 45 Grand Total 11500 00 When you save a report specification locally BI Query automatically adds the extension rpt to the file name and stores the specification file in the Queries folder unless you export the specification By default BI Query also saves the corresponding query and stores it in the Queries folder with the same name but with the extension qry When you submit the query BI Query automatically displays the
131. e ae ia tess 103 MATING ot ses cava cues esd cpshavok spoked syd evehvotchs 103 order of execution eee 103 OVELVICW ars cesss a an aas 92 TSUN Gin satiate an awace 103 grouping after calculating functions 0 181 before calculating functions 0 0 0 0 182 with aggregate functions 179 without calculating functions 185 H headers in standard reports 152 hiding columns in results 137 Hummingbird accessibility 253 l importing PrO PISE enee sees ren aa rR E A ERA 106 variables from a file eee 120 IN QPCPa tones oteren t ik 45 47 including database functions in variables 117 prompts in variables 0 117 rows with character spaces eee 49 rows with null values 0 eeeeeeeeeeeeee 49 inserting database functions in variables 117 prompts in queries SUDQUETICS 00 eeteteteteeeeeeneeeneees variables into variables invalid data formats correcting 75 IS NOT NULL qualification operator 49 J joining COUM Sienna en cee thse 143 146 KONAR EESTIT ETTEI 141 ta DOS nnr ae AES 197 198 263 BI Query Queries User s Guide joins COMMIS es 5 sess A Conflicts E E aunts natural joins operators for join conditions 197 Order in outer joins 201 removing unnecessary from queries 51 UY PES sis teas Seas tees cree toe Sees ve te bark Seeded 144 L layers ACMIMISHAtOE psi oinnes enie 8 MISCY 5 056225 R even EA aE 8 left OUt
132. e the worksheet under a different name open c mydocs test xlm Opens the specified macro sheet then runs run test xlm macrol the macrol macro on that sheet open c mydocs test xls Opens the test xls worksheet opens the run test xlm macrol test xlmmacro sheet and runs the macrol macro For this sequence to work one of the following must be true e The worksheet opens the macro sheet e The macro sheet is in the same folder as the data model e The macro is in the global macro sheet For more information see Opening Macro Sheets Automatically on page 231 run test xlm macrol Opens the test xlm macro sheet then runs the macro1 macro on that sheet In this case either the macro sheet must be in the same folder as the data model or the macro must be in the global macro sheet Combining Multiple Results Sets You can combine the results of more than one query in an Microsoft Excel worksheet This is useful when you re using data from different tables or when you re using a combined query There are three methods you can use to combine multiple results sets in a worksheet 233 BI Query Queries User s Guide Method 1 For more information see 1 In BI Query create a button for each results set Link each button to the Exporting Results to an appropriate query Set each button to send the query results to a macro Application Using
133. e they provide a means of drilling down in the data to obtain increasingly detailed information 93 BI Query Queries User s Guide 94 Naming Conventions for Prompts Single prompts can have any name prompts that qualify an attribute are usually named after the attribute For example if you create a prompt that qualifies the City attribute the prompt can be named City Group prompts including conditional prompts have a special naming convention Group Prompt Names The name for each prompt in a group takes the following form group_name prompt_name where group_name is the name of the group and prompt_name is the name of the prompt in that group For example the city prompt in the stores group has the name stores city group_name is optional it lets you create several groups of prompts Group prompts can also have a special Title prompt associated with them This prompt stores the title of the group prompt dialog box If you do not supply a Title prompt the dialog box for the group is called Enter Values Title prompt names take the following form group_name Title where group_name is the name of the group and Title is the literal string as written case is important The prompt string for a Title prompt represents the title for its group For example if you create a Title prompt for the stores group stores Title and specify its prompt string as Stores By City the dialog box that opens for that group
134. ecting Data Values from the pop up menu 86 Chapter 5 Using Data Values Files Data Aliasing Data aliasing lets users select from a list of common language names for data values while BI Query inserts the corresponding actual values into the query To set up data aliasing you create a data values results file or data values query file that contains two columns in the first column are the actual values in the second are the aliases Only the data in the second column is displayed in the list of data values For example instead of selecting a value for a qualification that may be difficult to understand or remember such as a staff person s ID number youll be able to select a more meaningful value such as the person s full name You select the name from a list of data values and BI Query inserts the ID number into the Qualification box for the query When you qualify the Employee ID attribute click the Data Values button in the qualification tree and BI Query displays staff names as a list When you select a name from the list BI Query inserts the corresponding ID number into the qualification tree Ee CE LIS Data Values s DECR EUGI Subqueries Data Sets Data values Dave Hafey x Data loaded 03 35 40 PM Cancel Help 87 BI Query Queries User s Guide For more information on alias data values see the Help for your BI Query application 88 Creating a Data Values
135. ects in the circular path a A WwW N In the attribute window s select the attributes you want to include in the query then close the attribute window s 6 On the Query menu click Run 216 Chapter 10 Building Advanced Queries 217 Chapter 11 Exporting Information Exporting Information 219 Preparing Results for Export 219 Results Options and Separators 220 Specifying Results Options and Separators 222 Export Options 223 Setting Export Options 224 Export Methods 226 Exporting Results to a Text File 226 Exporting Results to an Application Using a Button 227 Exporting Results Using E Mail 229 Exporting the SQL for a Query 230 Exporting Results to Microsoft Excel 230 Using Two DDE Commands 231 Opening Macro Sheets Automatically 231 DDE Command Examples for Excel 232 Combining Multiple Results Sets 233 Chapter 11 Exporting Information Exporting Information By exporting query results to other applications you can extend the usefulness of your queries BI Query exports results as text files that can be used by any application For example you can augment a report prepared in Microsoft Word by including actual data that you ve retrieved from a query or you can transfer results to a worksheet in Microsoft Excel to perform advanced statistical analyses In addition to exporting query results directly to text files you can do the following e export the underlying SQL for queries that have not been filtered or
136. edocsyasbseteedsicdsesoudeietaasseasiaas 80 Naming and Storing Data Values Files 0 0 ceeceeeeceseeesseeceneeeeseeeeeeeaees 80 Order of Data Valties File Use sccccccscessceveciec nics ed sosetas cavecessubtuseeectteeabaaese 81 Creating Data Values Results Files i eesssecssesceseseeseceseeeeseeceseeceecseeeeaseeeaeseseees 82 Creating Data Values Results Files Using a Text Editor eseese 83 Creating Data Values Query Files 0 tesssscsseeseceseeceecsceecscecessseeasscseescseeesseearaces 85 Using a Data Values File in a Query oo eeesseeeseeeeseeceeesseeecssceecssseeaseceasseeesscseeasaees 86 Data Alia Sing 5 8 ke sastse ie tusk ee eoe a e a a A EER 87 Creating a Data Values File to Alias Data eseeeseseseseseserersessrersrerressressees 88 Chapter 6 Using Prompts 89 About Prompts aiat nna O atid iia 91 Types Of Prompts eronneen ne n EEEE aE AAE EEE EEE E 92 Naming Conventions for Prompts ssssseseseseresesseesrersesssessressresseeseeesressee 94 Data Typ s for PrOMPtS eiakhroen ioie ea A EEE SSiN 94 Required Versus Optional Prompts ssesssesesesssesessessressessseesressresseeseeesresees 96 Prompts for BI Web Reports seesesssessssesesssessesssessressressesssessresseesseeseeeseesees 97 Common Tasks Using Prompts esssssseesessssseseesesesseresessesesesestesereseseseeseresesseresesesseses 99 Creating a Single Prompt satsissisceseninedd skctboteed dest vi peeecdesnipradenevnestdadeony 100 Inserting a Prompt 1M a QUETLY oe eee e
137. eeeecsceasseeeeaees Special Text Strings in Standard Reports 0 eeeeseeeeseceseeeeseeseeeeaees Adding Text Ornaments to Standard Reports 0 eeeeseeeeeneeeeeeeeeeeees Editing Default Ornaments in Standard Reports oo eee ee eeeeeeeeees Converting Standard Reports Converting Single Standard Reports cee seesseeseeesseceesceceseeeeseeesseeeaees Converting Multiple Standard Reports eee seeesesseceeseceseeeeeecseeeeaees Setting Batch Converter Options 00 cee eeeeseeeeseeeeseeeeseeeeseeeeeeeseeeeseeesaes Chapter 10 Building Advanced Queries Applying Functions to Attributes Built In Aggregate Functions Types of Grouping Applying a Function After Grouping oncsssecsciesacciscs saeseeundaiubnscius tenteewenoeds Applying a Function Before Grouping essssssesserserserserseerreseeseseeseeseeses Grouping without Functions 149 151 152 153 153 153 154 156 157 157 158 159 160 162 162 163 164 165 166 167 168 169 170 171 172 173 174 174 175 177 179 180 181 182 184 185 BI Query Queries User s Guide Creating Calculated Attributes 0 eeessssescesececeseeeeseeeeeeseeeaeseeeeseeessseeeassesees 186 Using Functions in Query Expressions ceseseecesseceeesseeeeesseeseeseeeeeees 189 Using Subqueriessciccieveciciscasettdeveeacteee erase du neg heii ii ates 190 Creating and Inserting Subqueries 0 ce eeeeeeeeseceneeeeseeeeeeeeseeeeseeeeeees 1
138. eeeeseeecesneeeceseeeecesseeeeesaeeeseseeeeeeea 102 Creating a Group Prompt eee seeeeeseeecesseeeeeseecceseeecesseeeeessneesesaeeesnas 103 Testing a Group Prompt 2 ascsscccdecelsca sa tateveecic dake Virsas srera nisa iaaio 103 Creating a Conditional Prompt 0 eee eeeeceeseceseeeeseeeeseeeseeesseeseeeesaes 105 Testing a Conditional Prompt 0 eee eeeeeeseeeececeeeeeeeeereseeeeaeeesseeesaeeees 105 Importing Prompts snese eein T a 106 Showing Prompt Values in Queries eeessseseseserssessesssessreseessseserersrerseese 107 Specifying Web Reporting Values essseseseesesesssrsreseseesesesssresesesteresestsseseresesreseseee 107 vii BI Query Queries User s Guide viii Chapter 7 Using Variables 111 About Variables si cstsedsvnstncsiestacgahasdactesvaaithars wats tewnmnast ior a a aaa E oas 113 Typical Uses for Via tia Ble sse iossiisrseri cvs seteavancvdscuess ereoanssvoeseenceeedenys 114 Expressions for Variables vaanid neer e e e e E ai ERER s 114 Using Literals in Variables asrianiasr e a aea 114 Using Placeholders in Variables ssesesessseseseeeseseeseeesessrersesssessresseeseeesees 115 Using Functions Prompts and Other Values in Variables 116 Creating and Using Variables occccccssvccssiiasiescsscssecosvessncedecoeveseunsvtonevdsivevedeotsyennseenes 117 Using Variables to Qualify an Attribute oo eee eeseeseeeeeneeeeeeeeseeeees 119 Importing Variables from a File oo eee ee seeeseecescecen
139. eeseteeeeesseteteeetees 179 applying to attributes oe 182 AVERAGE fescue ated sf oes 180 181 AVERAGE DISTINCT ou eee 181 COUNT sissy suse sates 181 COUNT DISTINCT nanea 181 defined example of grouping with ww 179 grouping after calculating ow 181 grouping before calculating 182 MAXIMUM opoe ae goscnedcs 181 MINIMUM proren T 181 SUM laea e aS 181 SUM DISTINCT owen 181 aliased data rrna naa a 87 data values file kaserenan 88 aliases for data objects correlation names 210 alphabetic sort Orde eee 136 and or qualifications 41 ANSI SQL 89 outer join syntax 0 206 ANSI SQL 92 outer join syntax 206 appending TOWS meeen ene EEE 141 applications exporting results to overview 0 0 227 using buttons to export results to 227 BI Query Queries User s Guide applying aggregate functions to attributes 182 operators to qualifications cee 38 Attribute Name boxes cccciiisencrdcccget 37 attribute window checking available data values 39 creating QUETICS eee eeee sete eeeseeeees 27 grouping attributes oo eeeeeeeeees 182 modifying SQL oo eeeeseeeeeeeee 209 qualification tree sinyin ai 36 EIS EE E E EO T 32 SOLUNG TOWS ue iian Te E E ia 32 attributes calculated creating ss ssesesereereeree 188 calculated overview 186 definition iosian ioro eie Vaid ah 5 Ta E E E 114 placeholders in variables 0 00 115 UALR yE ei se scet
140. elected in step 2 Using Functions in Query Expressions Use the Functions dialog box to include a function in the following types of expression e the expression used in a group qualification e the calculation for a calculated attribute e the expression that defines a variable Functions Other Functions AYG MAX MIN SUM COUNT AVG DISTINCT SUM DISTINCT COUNT DISTINCT The Functions dialog box displays a list of built in functions in the Functions list and for certain DBMSs a list of scalar functions in the Other Functions list A scalar function takes a predefined number of parameters usually one or two and produces a value for each result in turn Aggregate functions are often used with a GROUP By clause scalar functions aren t 189 BI Query Queries User s Guide You can use most scalar functions available in your DBMS even if they don t appear in the Other Functions list Manually type the function as part of the expression Check your DBMS documentation for the correct format When you run the query BI Query evaluates the function and inserts its return value into the expression Using Subqueries 190 You can access your data in more dynamic ways by qualifying a query witha nested query statement or subquery A subquery returns values for a single attribute A subquery doesn t have to qualify the attribute on which it s based but it must have the same data type When you qualify an at
141. enu click Open 2 Inthe Open Query dialog box select the query you want to open Queries Query name All Stores Browse All Stores Totals PerformanceComparison_EU PerformanceComparison_NAQ1 PerformanceComparison_NA02 Product Correlation Product PerformanceallCountries Product_Year ProductList ProductList_EU_Standard ProductList_N amp ProductList_NA_ Standard ProductMargin RegionalOffices RegionalPerformance01 avenue Cateoory E Run Close Help I Data values queries 3 Do one of the following e Click Run to open the query and submit it to the DBMS e Click Open to open the query without submitting it The Query window opens displaying both BI Query and SQL information about the query While the opened query is current you can alter it by selecting or deselecting attributes When you have finished editing the query save and submit it 35 BI Query Queries User s Guide Qualifying Queries 36 You can limit query results to a subset of the available information by qualifying one or more attributes In general a qualification takes the following form attribute operator testvalue where e attribute is the name of the attribute you are qualifying e testvalue is the value or values you want to compare against the values of the attribute e operator is the logical operator that specifies the nature of the comparison For example if you want to return rows
142. eo naa s 5 in Circular QUETICS eee eeeeeeeeeeees 210 CCT YING i rninn idene reinis 50 relating ee eae 197 198 using twice in ONE query 0 cee 210 data sets OVELVIGW eest asisas a Esai Ei Teia 192 character delimiters in 193 COMMENES IN cece sete eeeseneneeeees 193 file extensions enina 192 in qualification trees eee 194 QUE HGS oS case tisc deta aint 192 15120 D Cpe EES 193 using as a qualifier eee 194 using qrd files as a data set 195 VIC WIND oe a a e e S 195 data type formatting categories 00 0 57 data values Che chitig s E ah SNA aS 39 VEG E T PER EE PE E E E eet 39 qualifying With eee 39 data values fl senin innn uaiiie 79 OVELVICW si Soa ea anna E OAE tots 79 aliasitig datas A 88 data aliasing wi ceucadncmnas in conditional prompts MATING 3 3 cssahecenessvsissndeessseetevescuesssssseatees naming for use in prompts 80 Order OF USE 4 4 ba we witia wet doers STONE eines a E bene use in prompts 0 eects eee using in queries data values query files creating defined location naming SAVING aneor nie aaa EAEE atest data values results files CP ANE a scctesiecececvcecssescavsrensveseevenveastavess 82 creating with a text editor 83 defined nine ineei eissii Teki 80 Jain ati tAE P E O E E 82 using as a data Set eee 195 data determining if missing 0 49 database OVELVIEW A PEE ey AEEA EA 4 AttriIDUteS cnciccsisieisiisiiiiiiiiiii nan 4 connectio
143. eport menu click Column Settings The Column Settings dialog box opens From the Columns list select the column you want to resize then modify its character width in the Column Width box Click OK Chapter 9 Creating Standard Reports Combining Columns When related data such as a person s name and title are in different columns and you want to present them together you can combine the columns You can combine two or more columns into a single column and you can merge combined columns with another column When BI Query combines columns the new column displays the heading of the first selected column Sales Report Sales Report Surname Title Surname Czerny VP Sales Czerny VP Sales Plainsfield VP Marketing Plainsfield VP Marketing Hamford District Mgr Hamford District Mgr Jorgenson Regional Mgr Jorgenson Regional Mgr Lifkin Sales Rep Lifkin Sales Rep Machu Regional Mgr Machu Regional Mgr Bruining Distribution Mgr Bruining Distribution Mgr Millard Account Dev Millard Account Dev Original Combined To combine columns 1 Shift click the columns you want to combine On the Report menu click Combine Columns The Combine Columns dialog box opens Combine Columns x Separator l lt Space gt Cancel Help From the Separator drop down list box select the separator you want to appear between the items in each combined column for example a space You can a
144. eporting values 107 wildcard characters s 46 windows Attrib tesan ee a 5 S per QUETY cuneis iiin 132 Wiping queries s s sssssssesissesissesisrerisrerisreresren 30 working OE e E AAS 18 21 aI i TE AE E E teat wanteslecetieehs 21 Y years two digit to four digit 74 271 Notes Notes Notes Notes Notes
145. er JONS iesieta iaiia 144 legacy join Operators sesser 206 lists matching iteMS eee 47 loading NE EAE AT 35 FESS ean a EE 126 local data modeleri a 18 logging on to BI Server 18 21 M macro sheets opening automatically 231 MAPI mailing from BI Query 229 matching All Vales A EEE ET 48 a y valdesa e aai 48 character patterns seee 46 THEMIS ASS ranae A ARON 47 materialized relation definition 204 MAXIMUM aggregate function 181 merged tables in outer joins 202 MINIMUM aggregate function 181 missing Aat a so cssu2s u3 lt esvnsisueseed chee busanedaseseated 49 modifiers CountAll ac atest a eana aaaea 43 Distinct UCL OE EAA EA 264 modifying component queries 0 eee 132 133 qrd file with a text editor 83 queries SO ics tty eae ak ata Super g erieSisusueounni ririri 132 multi pass reporting 160 N naming attributes and operators in variable 114 conditional prompts 0 0 105 data values files cece 80 data values files used in prompts 80 data values query files 85 data values results files 00 82 group prompts ou cecceseeseeseeteeeteeees 103 PFOMIP US ssscsceessesacesssaz ieee cedecenesete secs 94 101 Natural JOINS 4 witha can 145 negative Qualifications occ A NeSted GUETICS EEE E E teens etre CTCAUING snn ienn INSCLHNG ia new outer join Syntax non SQL Operators NOT BETWEEN operator NOT IN o
146. ervals because they can automatically supply data values for the time when they are run Expressions for Variables 114 When you submit a query qualified with a variable BI Query evaluates the expression for the variable and adds the result to the WHERE clause for the query Each variable usually contains information about what attribute to qualify what operator to use and what value to test against the attribute There are two ways to provide attribute and operator information in a variable e Use an actual attribute name and operator e Use placeholders for attributes and operators Using Literals in Variables A user defined variable that names an actual attribute and operator always qualifies that attribute using that operator even if you insert it into the qualification for a different attribute in the query Unless you define the variable with placeholders BI Query removes the qualified attribute and qualification operator from the WHERE clause for the query Chapter 7 Using Variables The syntax for variable expressions depends on the DBMS you re using the examples provided here are based on ODBC Example A query includes the Store Name and State attributes of the Store data object The State attribute is qualified with the user defined variable Current Ratio Bae o Cemeata The expression for Current Ratio is Store Receivable gt Store Payable When you run the query BI Q
147. es in the database for that attribute and displays them in the Data Values dialog box unless the BI Query administrator has disabled data values for that attribute Ewe FE LS Data Values fr Data Values Subqueries Data Sets Data values For more information on If a data values file has been created for the attribute BI Query instead data values files see displays the list from that file The list can be a timesaving subset of the data About Data Values Files values stored in the database on page 79 CI al a Data Values Prompts Variables Subqueries Data Sets all Canada France Germany UK Data values list from a data values file 40 Chapter 3 Building Queries If you need to access all the data values in the database instead of just those in the subset you can click Load All in the Data Values dialog box To qualify an attribute with a data value 1 In the attribute window click the Qualify box for the attribute you want to qualify 2 Inthe qualification tree for the attribute click the Data Values button From the pop up menu that opens do one of the following e Click the value from the list of available values at the bottom of the menu These values appear only if the attribute has a data values file associated with it e Click all to insert all listed values into the qualification e Click Data Values to open
148. es the basic steps to follow in setting a data format for one of the following targets e an attribute e aresult report column e a data model default You can find more detailed information on each step later in this chapter To specify a data format for a particular target 1 Open the Format dialog box The topmost item in the Format Type list displays the data type category for the selected target For more information see Opening the Format Dialog Box below 2 Doone of the following Chapter 4 Formatting Data e To use a custom format specify the category specific options for the target For more information see Formatting Specific Data Types on page 68 e To use the Regional Settings for the category select lt Regional Settings gt from the Predefined Formats list e To use the default data model format for the category select Default available only for attributes and result report columns To format NULL values for the target select Null Values from the Format Type list then specify the format For more information see Setting the Null Values Option on page 64 To format the target as a string select String from the Format Type list then specify the format For more information see Setting the String Option on page 65 To format the target according to certain conditions select Computed String from the Format Type list then specify the format For more information see Setting
149. esulting trace file To run the trace utility double click trace exe in the following path Program Files Hummingbird BI Utility For information of configuring the trace utility see Trace Help 255 Symbols opetatonicsiccen in nia eae 47 formatting characte oo eee 65 wildcard character in qualifications 46 amp Setextsting sor iweven awe inane 171 amp prompt text String oes 170 Sd text String chelsea ieee teste teetets 170 amp dn text String ee eects 170 Xp tex String fs havent en isiel teal 170 amp pp textStnn et v 05 tabs tee daleaay 170 Seq text SUING oy ann ana nna e 170 OC TEXE SUING tananana aaa tess estate 170 formatting character 65 B Opera aaa a E 206 lt Operator essessesssessssssesssesseeseersesseesseesee 45 47 gt Operatora asaris 45 OPCLlatOk o E E oie neaeeties 45 47 O I A0 EE EE 206 K OPErITO D raa NEEE EEE 45 47 aO O E 45 47 formatting character eee 65 _ formatting character cceeeeeeeeeeeeees 65 _ wildcard character ccecccecccesscesseessscesseees 46 A aborting queries 30 accessibility features cece 253 ad hoc drill down mode wo eeeeeteeeeeeeeeees 127 GQUETIOS n i e 7 27 Ad Hoc Drill Down Mode 127 Index administrator BliQUC ss ewes ae hee ae ed 3 TOC saara aa Ep REER 3 administrator layer files 20 loca home ian o Oaa 20 split data model oo eerie 8 aggregate FUNCTIONS nonoesporioninria ioiii 179 and grouping ee eeeeseee
150. f you do not create a Title prompt for a group the dialog box for the group prompt is called Enter Values Testing a Group Prompt Before distributing a group prompt you should test it to ensure that it works properly When you submit the query the group prompt dialog box displays the prompts in the specified order and with the specified title 103 BI Query Queries User s Guide 104 To test a group prompt 1 2 In an attribute window select the attributes you want For each attribute do the following a Select the Qualify box for the attribute b On the Edit menu click Prompts then specify one of the prompts that have been grouped with the naming convention from the list in the Prompt dialog box 7 PF Store Name Shor LAddess F Let estorestcity Country estoresticountw Submit the query In the group prompt dialog box specify a value in the first box Type it or use the Data Values button to select a value from a list Specify values in the subsequent boxes Click OK Chapter 6 Using Prompts For more information on data values query files see Creating Data Values Query Files on page 85 Creating a Conditional Prompt A conditional prompt is a special kind of group prompt that uses a data values query to restrict the available data values for successive prompts in the group The second prompt in a conditional prompt uses a data values query that inclu
151. g Standard Reports 157 Adding Calculations to Standard Reports 157 Adding Subtotals 158 Adding Grand Totals 159 Adding Calculated Columns 160 Manipulating Columns in Standard Reports 162 Resizing Report Columns 162 Combining Columns 163 Banding Columns 164 Stacking Columns 165 Formatting Column Text and Headings 166 Customizing Column Settings 167 Adding Borders to Standard Reports 168 Using Ornaments in Standard Reports 169 Special Text Strings in Standard Reports 170 Adding Text Ornaments to Standard Reports 171 Editing Default Ornaments in Standard Reports 172 Converting Standard Reports Converting Single Standard Reports Converting Multiple Standard Reports Setting Batch Converter Options 173 174 174 175 Chapter 9 Creating Standard Reports About Standard Reports For more information see Publishing Standard Report Specifications on page 241 BI Query includes a fully integrated reporting facility called Standard Reports Using Standard Reports you can produce fully formatted professional reports from the results of your queries Each standard report you create appears in a report window in BI Query where you can format it in a variety of ways The results in a standard report correspond to the results in a results window Report Query Results 1 olx a Query Results 1 64796 Hame City Date Amount AustralAir Sydney 084093 60 000 00 Sydney 11 21 93 6 500 00 Banco Real Toledo 11 2393 25 400 00
152. gating between design windows and automating tasks and ornaments such as graphics and text that can provide information and improve the usability of the model Ornaments Design windows ji l Retail Sales Application a B A Periods Sales Detail Product Sales Reports Marketing Marketing Reports IAS Regional Office T Buttons Data objects Relationships Chapter 1 Introducing BI Query Buttons Buttons automate frequent activities They let users connect automatically to the database submit multiple queries combine the results and generate a report all with a single click of the mouse Buttons can also display design windows save results to files export results to other applications open associated document files and launch other applications such as Visual Basic and Excel Ornaments Ornaments are text and graphical objects such as titles logos borders backgrounds and notes that provide additional information act as visual organizers and improve the appearance of design windows Queries A query is a request for information from a database In order to retrieve information using the data objects and relationships in a data model you create a query and submit it to the database The results returned by the database for a given query are known as a results set The first step in creating a query is to select attributes from at least one data obje
153. ge and the address of the recipient then send the message Note When you retrieve e mail that has been sent to you by another user store query and report files in the Queries folder so that they appear in the corresponding directory dialogs when you open them Store results in your main BI Query folder Be sure you retrieve both qrd and qrr results files To use retrieved files close the data model if it s open reopen it then open the files 229 BI Query Queries User s Guide Exporting the SQL for a Query If the Save Queries permission is assigned you can export the SQL for a query to a text file provided that the query does not manipulate the results The SQL can then be used in other applications For instance you can use the saved SQL in stored procedures in the database When you export a query from a results window BI Query saves the query that generated that results set When you export from any other window BI Query saves the current query To export a query s SQL to a text file 1 Open create or run the query you want to export On the Query menu click Save In the Save Query dialog box select Export SQL Only then click Export A WwW N In the Export Query dialog box specify the location and the name for the query SQL text file then click Save Note You cannot export a separate SQL text file for a super query Exporting Results to Microsoft Excel 230 If you create a button that sends query
154. good practice to build it initially with an extra qualification to limit the number of rows returned In this way you can verify the design of the query without retrieving a possibly large amount of data To build a circular query with a correlation 1 Decide which data object you want to create a correlation for and which relationship you want to use for that correlation Click the relationships joining the data objects you want to include in the query making sure that the last relationship you click is the one you want to use for the correlation In the Circular Query dialog box click the data object you want to create a correlation for Circular Query x Your query has a circular path that may restrict your results To avoid this by using a data object in two different roles let BI Query create a correlation name Create correlation for C Departments Employees C No correlation Cancel Help Click OK In the design window double click the data object you created the correlation for In the Open Attribute Windows dialog box click the attribute window s you want to open 213 BI Query Queries User s Guide 214 Open Attribute Windows x Data objects Emplo__Manag Selecting Employees opens the attribute Employees window for the original Employees data object Selecting Emplo__Manag opens the attribute window for the original object s correlation Cancel Help 6 Cl
155. gt 2 000 Credit limits greater than 2 000 Credit Limit gt 2 000 Credit limits greater than or equal to 2 000 Matching Items in a List When you qualify an attribute with a list BI Query automatically applies the IN operator to the list The IN operator allows you to find all records that match any one item in the list You can also find items that don t match those in a list Examples Qualification Possible Matches Country IN France Germany Spain France Germany Spain Country NOT IN France Germany All countries except France Germany Spain Spain 47 BI Query Queries User s Guide Finding a Range of Values You can qualify an attribute with a range of values to obtain results within a particular range The range is inclusive retrieving the lower value and the upper value as well as the values in between To qualify with a range of values enter the values as a qualification list and apply the BETWEEN operator You can also find results that are outside a particular range by applying NOT BETWEEN If you qualify a character field be sure you use the same case upper or lower in which the value is stored in the database Examples Qualification Possible Matches Credit Limit BETWEEN 2 000 and 5 000 Credit limits between and including 2 000 and 5 000 Credit Limit NOT BETWEEN 2 000 and 5 000 Credit limits outside this range Matching Any Matching All You can enter more
156. h any match all 48 Does not contain operator matching character patterns 46 Does not end with operator match any match all 48 duplicates eliminating 43 185 dynamic relationships eee 197 between unrelated data objects 197 198 choosing Operators s es 197 Chat einans deleting 0 0 0 0 outer joins in 262 E editing component queries of super queries 133 operations in super queries 134 SUPET QUETIECS oo cee eects ee eeeeeeeeees 132 yarlableS innisis eresie een 118 eliminating duplicates ee 43 185 e mail exporting BI Query files 229 e mail exporting information 229 Ends with operator match any match all eects 48 matching character patterns 0 0 0 0 46 entering data values ie hrr nirisan pai 39 QUI ONS ianaeaieo ierit 144 error messages modifying SQL eee 207 208 errors in super query WindowS 135 Excel combining commands with DDE 231 combining three results sets 233 setting export options for 230 using two commands eee 231 excess joins Cleaning UP eee 51 excluding rows with character spaces 49 rows with null values eects 49 executive BULtONS 0 eects eeeeeeeeees 7 EXPOTt OPTIONS eects 223 224 EXCO ss fete te ite LETT 230 exporting BI Query files by e mail oe 229 queries and results sets SQL t text leS nnna arat exporting results COpy and pas ea nanei 219 OPl
157. he body of the e mail message To post messages to the user group Send your e mail to hbi users hummingbird com To search the mailing list archives Go to the following web site http www hummingbird com support usergroups html Contents Chapter 1 Introducing BI Query 1 ADOUUBI QUT y eperen aree area Ee a E Ee EEE a a aE aaen 3 BI Query Admin User and Update oe eee eesccesseceseeeeseeseseeeeseeseneeeeseeees 3 B ile COmCE Pts vessel n ha nr eae eA Siva e AEE AE Ea aE 4 Database COmMmponentS koerse Aaa hess aea ea aeiae ira tsa 4 Data Model Components esecessseceseecesneceseeceereceacesesceceaeeesaeersaeeesaeeess 4 OU ELIESE ASS Asa zecnsee EEE acta enc Fe EEE E E E Ses 7 REPOLUS orerar erei Eit E EEEE EE r i a iie 8 Typesot Dat Models haee aaae ives ave heii tatters 8 BI Query Operation Modes ssesssesessssesessressesssessresseessessressressesssesseeseessees 11 Using BI Query with BI Server deher eeesceeseeseseeeeseeceseeeeeeceseeeeseeseeeensees 12 Chapter 2 Getting Started 15 Starting BE Query Sennori aeriene nioi anr Esne ias roAa S eaa ES Tarn RRES EnS 17 Opening Data Models ssori asta a Eii 18 Rettieving Data Models Mavala teeta atentar ae aaae Enaissi aeris 19 Finding the Administrator Layer in a Split Data Model eee eeeeees 20 Working Online or Offline pinent i ises oiiaii anias 21 Connecting t a DBMS seeniori eiaeaen e r r arr i as 21 Disconnecting from the DBMS sssssssssssesesssesserssessre
158. he object If you choose to do so BI Query creates a temporary copy of the object using the correlation name You can also choose not to create a correlation The choice you make determines how join conditions are applied in your query and the extent to which your results are restricted A query that uses a correlation name allows you to ask two questions of the same data object and is less restrictive than a query that doesn t use a correlation Queries with a circular path are typically built by advanced users who understand how to use join conditions to retrieve the information they need Circular Queries with a Correlation The last relationship you select when building a circular query determines which objects can be aliased you can create a correlation for one of the two objects that form that last selected relationship The last selected relationship also determines which join conditions apply to the correlation Chapter 10 Building Advanced Queries For more information on correlation names see the BI Query Data Models User s Guide BI Query creates the correlation name for an object using the following format object__relationship where e object is the first five characters of the name of the aliased object e __is the literal string as written two consecutive underscore characters e relationship is the first five letters of the last selected relationship If you are using BI Query Admin you can also use
159. herena eik amp prompt Kee AS amp dn QP pa n A OCP Passes n a tia uit ie pole Nis a Xan n a lt o E EE EE three tier environment Transpose results Option eee 221 trim relationships ee aa a 51 Trim Relationships modifier 0 0 43 types Of COLUMN JOINS sssr 144 U updating database UCTICS E TEETE S Index User defined operators 0 eee 50 user layer split data model 8 user name BI Server log OM 21 Vv values comparing sini ial as asia iat a 47 TUM sc cirtirn tire Head Bard eee eae 49 variables OVELVICW ar a Miettinen arise cee 113 attribute and operator information 114 BIGTOUPID selec Riise scai 113 121 BIU Seri issn aa 113 121 Crea s Taiana seth 114 118 DBUs r Dein onsen inca ent anlsaks 113 121 OCI G aritna Aa ane eaa aa ias 118 examples of placeholder use 116 importing from a file eee 120 in queries converted to QDO 113 in scheduled queries 114 including database functions 117 including prompts 117 naming S naming attributes and operators 114 performing calculations 0 0 0 eee 114 placeholders for attributes and Operators 115 retrieving values 114 storage 113 USES eeeeceeceeseeseeeeeesenseeseeseneeteeeeeeseeneenes 114 using to qualify attributes 0 119 within yari beien annes 117 viewing ETA E EE NEEN ee ewes te 195 query specifications seseeeeeeeseesee 33 Ww web r
160. hments with your e mail messages E mail provides an easy way to distribute new and revised queries and results to others As long as your e mail application supports Microsoft s Messaging API MAPI you can e mail files directly from BI Query When you send e mail from within BI Query it starts your e mail client and automatically attaches the query results or report files to the mail message depending on the context in which you open the client Ifyou open or build a query before starting your e mail client BI Query automatically attaches the corresponding qry file in your message regardless of which window you re working in e Ifyou open retrieve or generate a results set then start your e mail client the corresponding qrdand qrr files are attached only if the results window is the active window To automatically attach a results set display the results window before starting your e mail client e Ifyou open or save a standard report specification before starting your e mail client BI Query automatically attaches files corresponding to the current report rpt If you don t want to include the current query or results set in an e mail message remove the file from the enclosure To e mail files 1 On the File menu click Send Your e mail application dialog box appears with a list of the enclosed files 2 Remove any files you don t want to send or enclose additional files 3 Supply a subject for the messa
161. ickOK In the attribute window s select the attributes you want to include in the query then close the window s 7 Select any other attributes you want to use from the other data objects in the circular path 8 To maintain all the join conditions in the query on the Query menu point to Modifiers then deselect Trim Relationships if necessary 9 On the Query menu click Run Circular Queries without a Correlation When you build a query without a correlation BI Query doesn t create a temporary copy of a data object in the circular path This allows you to ask one question using all the data objects and all the join conditions in the circular path The results are usually more restricted because the rows returned from the database must meet all the join conditions in the circular path Example Suppose you want to use the Employees and Departments data objects to find out which employees make over 60 000 and manage the department they work in In this case you need to create a circular query but you don t need a correlation because you re using each data object only once Chapter 10 Building Advanced Queries When you submit the query the DBMS processes it in two stages Behind the scenes the DBMS first obtains a preliminary results set names of all employees who make over 60 000 and the departments they work in by applying the join condition for Work In to Departments and Employees The DBMS then applies the joi
162. ieving Scheduled Results 250 Chapter 12 Working with BI Server Working with BI Server For more information on scheduling queries see Using BI Server Scheduler on page 249 If you are in a BI Server environment you can take advantage of BI Server s publishing retrieving scheduling and security features The requirements for using these features depend on the item you want to publish or otherwise access Requirements for Publishing Queries You can include queries when you publish and secure data models You can also publish and secure queries individually Published queries are available to other users and can be scheduled Although you don t have to save a query before you can publish it the data model on which the query is based must have been published If it hasn t BI Query requires you to publish it before you can publish a query based on it In order to be published a query must also be the current query To publish a query to the BI Server Repository you must also have at least one of the following BI Server system permissions e Publish Queries to Admin Layer e Publish Queries to User Layer In addition to queries you can also publish data values results files Publishing Queries When you publish a query it s automatically saved locally in the Queries folder where the data model is stored Data values queries are stored in the DataVals folder Then it s published to the Repository If you wan
163. in BI Query before running it You must also reposition the cursor in Excel each time so that the next set of results does not overlap Method 3 Paste into a specific worksheet using the following procedure 1 Configure the worksheet so that it automatically opens the macro sheet or use an alternative method to make the macros automatically available 2 Create a button for each results set then link each one to the appropriate query Set each button to send the query results to a particular macro For example to combine three results sets you could set up three buttons to execute the following commands Button Command Sequence Button A open c msoffice excel test xls run test xlm macrol Button B run test xlm macro2 Button C run test xlm macro3 In this case you must click button A first to open the worksheet 3 Click each button in turn 235 Chapter 12 Working with BI Server Working with BI Server 239 Requirements for Publishing Queries 239 Publishing Queries 239 Retrieving Queries 241 Deleting Published Queries 241 Publishing Standard Report Specifications 241 Publishing Data Values Results Files 243 Controlling Access Using BI Server 243 Users and Groups in BI Server 244 Access Inheritance Rules 244 About the Set Security Dialog Box 245 Setting Security 246 Setting Security for Queries 248 Using BI Server Scheduler 249 Scheduling a Query 249 Retr
164. ing the Range of Results 139 Combining Results Sets 140 About Appended Columns 141 Combining Results by Appending Rows 142 About Column Joins 143 Types of Column Joins 144 Combining Results by Joining Columns 146 Chapter 8 Working with Results About Results For more information on Results are the information returned from your database after you submit a saving results as text see query Query results can be used as is or as a source of data for a report Exporting Results toaText Results can be returned to a results window on your computer screen File on page 226 where they can be combined filtered and formatted as well as stored and distributed without further database access You can also submit a query so that the results are stored in a text file or in the database then load the results at a later time If your querying environment includes BI Server and you have the necessary system permissions you can schedule a query to run automatically In that case the results are returned to the BI Server Repository where they can be retrieved by you as well as by other users with appropriate permissions All the results returned from a query taken together are called a results set Spreadsheet and Form Views You can search for specific By default results are returned in a spreadsheet view in the results window content in query results It displays multiple records each record is displayed as a row in the For m
165. ixes and suffixes ee 209 stacked columns in BI Query Reports 173 Standard Reports ceeeeeeeeeeeeeees 151 applying specifications ee 156 converting multiple reports 174 converting to BI Query Reports 174 creating ananin nnana eii 153 data formats 11 eee 153 default text ornaments in wo 169 exporting reports to text files 0 157 installing the Report Converter 173 PHNUNG Nes Bae esodwiaseinaiin 157 publishing specifications we 241 reordering Columns eee 137 report specifications oo eee 154 saving specifications 00 155 special text strings for oe eects 170 structure and characteristics 152 subheadings in eee 164 269 BI Query Queries User s Guide Standard Reports editing adding borders 168 adding calculated columns 160 adding grand totals eee 159 adding subtotals eee 158 adding text ornaments eee 171 adjusting column size s es 162 banding columns basing one calculation on another 160 changing heading text style 0 0 166 combining columns eee 163 customizing column settings 167 editing default ornaments in 172 formatting colUMNS eee 166 stacking columns 0 0 eeeeeeeeteteteeeeee 165 starting QUETICS ponosni seis 30 submitting QUETICS aanne e ia 29 subqueries Cenere T at a Te AAEE EEE E 191 Cataiity Pes E E 190 EX POLE Dohrana ea E e E a 33 INSEGNE aroia a a 191 invalid opera
166. l Layer Component Combined Data Model Data model file datamodel gqL e design windows e data objects e relationships e calculated attributes e buttons e ornaments e drawing objects e permissions e default preferences Queries folder e query files prompts file e variables e standard report specifications Reports folder e BI Query Reports files Data Values folder e data values query files e data values results files e data sets BI Query Operation Modes BI Query operates in one of two modes depending on the type of activities you want to perform Run Mode Lets you perform activities that require access to the database submitting queries updating tables submitting SQL scripts and so on Design Mode Lets you modify the design and layout of the data model create buttons and ornaments and perform other activities that do not require a connection to the database 11 BI Query Queries User s Guide 12 3 You can switch between these two modes by clicking the Design Mode button on the Application toolbar Application toolbar Layout palette EBI Query Admin RetailGolf gql Sales File Edit Layout Design Hast Tools Window Help ajea ee el eer lel le T Welcome Ty a A I gt X jma Periods Sales Reports Marketing Marketing Reports Drawing toolbar A design window in design m
167. lONS ys cas ivaienaaie preparations to an application onean 227 Using esmai eusi ai hartas 229 F file extensions for data sets 192 Index files administrator layer ones 20 Bakes sivsdalita ieviewin eiras 53 COMNCCHON 4 0004 Aik Bata canadanasa 22 data values eceeccesccesscesecesseeeeeees 79 80 data values query 33 80 data values results eee 80 PGA aty iced eee leet Mie ete eee ett 8 Olive nie ei nA ena os vote ete N a 10 QU ATE TE 8 a EAEE 80 226 GT seas EE E E EE 80 226 aa Ath a E ea ea 33 80 Cae a a e T T 33 LEP A EE TS 174 YESMltS nssiiesssesini insesi 226 a E A 174 filtering ra E A toes E TEE 136 results sets super queries 00 0 eee 129 finding items in query results s es 125 ranges Of ValUeS 0 0 eee eeeeseseeeeeeees 48 fines tuning SQV vieisvcsesedsdesedt sscereseresseeeioess 209 footers in standard reports 0 0 152 formatting characters Piette oases Leah MRT RAGE Rese eae hes 65 functions AVERA G Eonar E cutive sseesivouetuiss 180 AVERAGE DISTINCT oe 181 COUN T EAEE O 181 COUNT DISTING Toeceriseeinaiia 181 MAXIMUM mei reti 181 MINIMU Mess ro e EN 181 SUM vita waatibieh iain atts 181 SUM DISTING cscs iiin 181 functions in calculated attributes 189 G Sa TES E elesets weteseleveleselesclesetett 8 QUAM esses essa E Shea E A 10 r cs ests crea vect crete trewsstorstessttreswsstorsl 8 grand totals adding to standard reports 159 group prompts GROANS haer
168. le associated with the prompt or the attribute it qualifies then the prompt uses the data values from the default SELECT DISTINCT query for the attribute List Box lets the user select one or more values from a list Combo Box lets the user type a value or select one from a list zw Option Buttons let the user select exactly one value from a group If you use option buttons for a prompt it must be a required prompt 3 Check Boxes let the user select multiple values from a group Date Entry lets the user enter a date value in the form YYYY MM DD Time Entry lets the user enter a time value in the form hh mm AM e O 4 Date amp Time Entry lets the user enter a date and time value in the form YYYY MM DD hh mm AM The display object that you specify for a prompt determines how the BI Web user s browser displays the web reporting values For example if you specify 7 web reporting values and select check boxes as the display object the BI Web user can select the prompt value s from among 7 check boxes The display object also determines how many values the user can select If in the previous example you had selected option buttons as the display object the BI Web user could select only a single value for the prompt You can also override the properties of the display object and force users to select a single value Chapter 6 Using Prompts Note If the display
169. lifications 1 In the qualification tree click the condition box corresponding to the combined qualification that you want to separate To select more than one hold down the Shift key and click each box 2 On the Query menu click Qualification then click Uncombine Using Negative Qualifications You can make a qualification have the opposite meaning to the condition specified This is useful when you wish to exclude certain information from a query or when it is easier to specify the information you don t want than to specify the information you do for example if you want all customers located outside Illinois and Indiana Chapter 3 Building Queries To remove the Not indicator click it To negate a qualification 1 In the qualification tree select the qualification you want to negate by clicking the corresponding condition box To select more than one condition box hold down the Shift key and click each box 2 On the Query menu click Qualification then click Negate Clause The Not indicator appears in front of the condition box no E i Ga Using Query Modifiers Query modifiers allow you to change the way a query operates One use of query modifiers is to summarize information For example you may have stores in many different countries A query that lists these countries can include duplicates if there are several stores in the same country Alternatively you may want to know how many stores are locate
170. lowed data values BI Query stores prompts in a single Prompts file in the Queries folder BI Query does not save the prompts created for a given data model until you close the data model Note If you insert a prompt into a query and then convert that query into a query data object QDO all queries using that QDO will require users to respond to the prompt although the prompt will not be visible in the data object or the SQL for the query For more information on QDOs see the B Query Data Models User s Guide 91 BI Query Queries User s Guide 92 Types of Prompts There are three types of prompts single prompts group prompts and conditional prompts Single Prompts A single prompt retrieves a value or set of values for a given query Single prompts usually qualify a single attribute A single prompt appears in the Enter Value dialog box Group Prompts A group prompt is made up of two or more distinct prompts that are grouped by their names When you run a query that involves multiple prompts in a given group they all appear in the same dialog box For example a group prompt may consist of prompts for the attributes Country and City a query that qualifies those attributes prompts you to select a country and a city using the same dialog box Stores By City x X Indicates a required value Please enter a country D BB Please enter a city f Cancel Help A group prompt can display up to
171. lso type a separator in the box Click OK 163 BI Query Queries User s Guide Banding Columns To create a standard report in which one column occupies the entire width of the page you can band the column This feature is useful if a report contains several columns with short pieces of data and one column that contains a lengthy description You can band the descriptive column so that it appears across the page either above or below the other columns Banding columns also lets you create subheadings in a report Sales Report Sales Report Order Amount Sales Region Order Amount 089 1 200 Northern Ireland Northern Ireland 089 1 200 269 5 500 Southeast Asia Southeast Asia 348 3 500 Middle East 269 5 500 Middle East 402 2 800 United Kingdom 348 3 500 United Kingdom 402 2 800 Original Banded with Before selected Sales Report Sales Report Order Amount Comments Order Amount 089 1 200 Customer placed 089 1 200 initial order in June and plans to reorder in September pending budget approval 269 5 500 Site license purchase pending product competitive analysis Customer placed initial order in June and plans to reorder in September pending budget approval 269 5 500 Site license purchase pending product competitive analysis Original Banded with After selected Chapter 9 Creating Standard Reports To band a column 1 On the Report men
172. ly Select either the Automatically Order Outer Joins preference or the Automatically Order Outer Joins permission You can set permissions using BI Query Admin only BI Query automatically chooses the join order for you When selected the Automatically Order Outer Joins permission overrides the Automatically Order Outer Joins preference Note The join order that BI Query automatically determines for a given query depends on the order in which you add the joins to the query However different join orders may produce the same results set If you deselect and reselect joins within a query the automatic join order may change but the outcome will be the same To specify join order manually Clear the Automatically Order Outer Joins preference and permission Select click the joins in the desired order 203 BI Query Queries User s Guide 204 If you aren t satisfied with a join order chosen manually you can change it by deselecting the connection lines and then selecting them again in the order you want About Join Conflicts A join conflict occurs when more than one join could supply NULLs to the same materialized relation either a nested join that is performed in advance of the joins in question or an actual table BI Query cannot resolve join conflicts on its own instead it can highlight the conflicting joins If you want BI Query to display join conflicts select the Show Join Conflicts preference Conflicting joi
173. mands for example to open a particular worksheet and run a macro To determine the requirements of the receiving application see its user documentation You may also need the help of your administrator to find out whether an application supports DDE and what service name to use Results must be set up properly in order to be exported directly to an application For more information see Specifying Results Options and Separators on page 222 Setting Export Options You can specify export options as a preference or when you create a button Specifying export options in the Preferences dialog box applies the options to buttons created thereafter Export options specified from the Create Button or Edit Button dialog boxes override the options specified as preferences To set export options 1 Open the Export Options dialog box using one of the following methods To specify default export options open the Preferences dialog box on the Tools menu click Preferences In the Preferences dialog box click Export Options To specify export options for a button open the Create Button or Edit Button dialog box In the Output To area select Application from the drop down list then click Export Options Chapter 11 Exporting Information In the Export Options dialog box from the Application drop down list box select the application to which you want to export If the application isn t in the list select User Defined
174. n 0 cee eeeeeeesseeseeeeeeeee 213 Circular Queries without a Correlation ccccccssssscceeccccsceseeeeeeseesesees 214 Building a Circular Query without a Correlation wo ee eeeeeeeee 215 Chapter 11 Exporting Information 217 Exporting Information sicc3ceefaieiccsneites Adan a ta RO ease eae ai oes 219 Preparing Results for Export 3 ic cscsisies dsesiceesvsevcencvecoeiscbecdosdscueinvssantveuscobstonsrsvavennts 219 Results Options and Separators esceesscesscesseeessceeeseeesseeeseesseeseseeesaes 220 Specifying Results Options and Separators esseeseceeseceseeeeeeceeeeeaees 222 Export Options ic seccesccdeccceessvestbepe suite aces reiissi ieira s EE E iE Ss 223 Setting ExportOptions coise rar snig nirst E ieee oee EE ARKE a 224 Export Methods siiri a aud eh Mena 226 Exporting Results to a Text File sennaciisto e ai 226 Exporting Results to an Application Using a Button 00 eee eeeeeeeeeeeeee 227 Exporting Results Using E Mail 0 ceeeessesesecesseeeeneeescereeesssceesaeeenaes 229 Exporting the SQL for a Query oe eeeseeesseesseeesnceeseeeseeesaeeseseeseseeeeaes 230 Contents Exporting Results to Microsoft Excel oe essesesssceeeeeeeseeeeeeseeeesees Using Two DDE Commands eee eeeeeeeseecesneeeeceeeseeeeeeeees Opening Macro Sheets Automatically oseese DDE Command Examples for Excel i icccc ssccsssecacseasecess e Combining Multiple Results Sets 0 cee eeseeesseeeseeeeseeeeees Chapter 12 Working with BI
175. n the results include only data from that country CI como EE ee Equal l Not equal lt Less than lt Less than or equal gt Greater than gt Greater than or equal Begins with Contains Ends with Does not begin with Does not contain Does not end with IN NOT IN BETWEEN NOT BETWEEN IS NULL IS NOT NULL Custom Using the Operator list in the qualification tree you can apply other operators to restrict a qualification Keep in mind the following guidelines when applying an operator e the gt gt lt lt operators let you insert a single value when you submit the query 45 BI Query Queries User s Guide 46 e the BETWEEN and NOT BETWEEN operators require two values for an upper and lower range e the IN NOT IN and lt gt operators let you insert one or more values Matching Character Patterns You may not be sure how an item is stored in the database or you may want to find records for which there is some kind of pattern For example you may need information about a retailer and aren t sure of the spelling of the name but know that it begins with Cars You may also need a list of businesses with a postal code of 30115 Examples Qualification Possible Matches Retailer name Begins with Sweet Sweet Indulgence Sweets to Go Retailer name Does not contain Limited All retailer names except those containing Limited Zip Postal code Ends with
176. n attribute column or data model default However the particular options that apply depend on the value of the data Format Order for NULL Data If the data is NULL BI Query applies the Null Values formatting only no other formatting options apply The format that BI Query uses in this case is one of the following in order of precedence 1 2 the Null Values format for the attribute or result column if specified the data model format for Null Values in the corresponding data type if specified the data model format for the Null data type category 59 BI Query Queries User s Guide Format Order for Non NULL Data If the data is non NULL BI Query applies the specified options in the following order 1 Ifyou have used the Computed String option BI Query evaluates the conditions based on the value of the data If the data meets the conditions BI Query applies the Computed String formatting no other options apply in this case 2 Ifyou have not used the Computed String option or if the data does not match the conditions defined for it BI Query applies one of the following category specific formats in order of precedence a the formatting for the attribute or result report column if specified b the default format for the corresponding data type 3 Ifyou have specified String formatting BI Query applies it to the result from step 2 Formatting Data The Basics 60 The following procedure outlin
177. n condition for Managed By to the preliminary results set to obtain the final results employees who make over 60 000 and manage the department they work in Departments Without a correlation this circular query returns only the rows that meet the join conditions in both the Work In and Work In Managed By Managed By relationships Employees The SQL for this query is as follows SELECT Employees LastName FROM Departments Employees Departments W F Employees Salary gt 60000 00 AND Departments DepartmentID Employees Department ID AND Departments Manager Employees EmployeeID Building a Circular Query without a Correlation Building a query without a correlation is similar to building a query with a correlation except that the order in which you select the relationships isn t important When BI Query prompts you to specify whether or not to create a correlation you choose not to create one 215 BI Query Queries User s Guide Note If the circular query contains an outer join you must create a correlation The No Correlation check box in the Circular Query dialog box is dimmed To build a query with a circular path that doesn t use a correlation 1 Click the data objects and relationships you want to include in the query In the Circular Query dialog box click No Correlation Click OK In the design window double click the data obj
178. n either side of a merged object will not likely be consecutive The missing ordinals belong to joins inside the merged object Example 1 In the following example the inner join between the actual relationship PersonAddress and the data object Address takes place first 1 Person is then left outer joined to the first join 2 The result is then left outer joined to AddressType 3 L 2 gt 1 Person PersonAddress Address 3 J T ype 3 Addresstype The SQL ANSI SQL 92 for this example is SELECT Person Address AddressType RO ty Person LEFT OUTER JOIN PersonAddress INNER JOIN Address ON Address ID PersonAddress AddressID ON Person ID PersonAddress PersonID H EFT OUTER JOIN AddressType Address AddressTypeID AddressType ID O Chapter 10 Building Advanced Queries Example 2 In the following query both of the inner joins have an order of 1 whereas the outer join has an order of 2 SELECT A B C FRO A INNER JOIN B ON A x B x LEFT OUTER JOIN B INNER JOIN C ON B y C y ON A z B z This query doesn t tell the DBMS how to order the inner joins with respect to each other because they occupy the same level of nesting Specifying Join Order You can define the order of joins either automatically or manually To specify join order automatical
179. n files 22 creating tables from query results 196 fine tuning the SQL eee 209 functions in variables eseeeeeeeees 117 modifying SQL wo eee 208 TOCOLAS sc8iejctenilasccain dare aan 4 supported Operators s src 50 date formats aMDigUoOuUS sssssssssissesiessiesesiersreresresrsrse 74 automatic conversion Of 74 dates COMP ALIS as bss fa catia case Fase susate cecate de cesnie daees 47 DBMS connections ceeececceseseceessseeeesees 4 22 DBUS D csscicitcchasesti en oeae 113 using in a query qualifier 121 DDE combining Excel commands 231 combining three results sets I Excel a tak E ti aes es 233 261 BI Query Queries User s Guide DDE commands COMB UM E u TEE A 1e e ET A TEA BEW o AOI OOOI TEE PIE A FO default data values Query eee 39 79 export options overriding 224 export options setting 224 results Options 221 default text ornaments 0 eee 169 deleting COMPONENE queries 0 eects 134 dynamic relationshipS 0 ce eee 201 operations in super queries 134 POSUIES E P I EN A 227 values in qualification tree 36 Design Modenos tester Achaea darts 11 design windows eLO ce e AE NS 6 disconnecting from DBMS sssr 23 displaying AEE S A E A cds 39 prompt values in queries 0 0 107 Distinct Modifier eeeeeeeeeeeeeeeeeeeeees 43 Does not begin with operator match any match all 48 Does not contain operator matc
180. n items you publish publishing also provides a secure way to share your information with other users For more information on using BI Query in a BI Server environment see Working with BI Server on page 239 13 Chapter 2 Getting Started Starting BI Query 17 Opening Data Models 18 Retrieving Data Models 19 Finding the Administrator Layer in a Split Data Model 20 Working Online or Offline 21 Connecting to a DBMS 21 Disconnecting from the DBMS 23 Chapter 2 Getting Started Starting BI Query For more information on opening new data models see the B Query Data Models User s Guide When you start BI Query the Welcome to Hummingbird BI Query dialog box automatically prompts you to open a data model You can also open a data model after you have started the program Hummingbird BI Query Admin Si Recent Data Model Z b gt Open a data model you ve recently worked on mA Local Data Model ET Open a local data model Repository Data Model Uh Retrieve a data model from the repository New Data Model t Create a data model F Hummingbird The types of data models you can open at start up are as follows e Recent data models are those data models on your system that you have recently accessed Local data models are all the data models on your system you do not need to log on to BI Server to access these models e Repository data models are the data models available in the BI Server
181. na text editor or spreadsheet program do one of the following e Open the qrd file you created modify it and save it under the same name Type a list of values in a new document file adding a newline carriage return line feed after each one then save the file using the same path and file name as the qrd file Chapter 5 Using Data Values Files 12 To test the file in BI Query select the attribute used to create the original results click its Qualify box then click the Data Values button in the qualification tree Creating Data Values Query Files You can create query files that retrieve subsets of values for the attribute and use them to qualify attributes in other queries by inserting the data values query file into qualifications and prompts To create a data values query file for an attribute 1 Double click the data object that contains the attribute In the attribute window select the attribute then click its Sort box and apply any other restrictions you want To eliminate duplicates on the Query menu point to Modifiers then click Distinct Click in the Qualify box beside the attribute and then enter the values you want in the Qualification box for the selected attribute On the Query menu click Save The Save Query dialog box opens Queries Query name Staff Employee Id Staff Staff Name Eoo Export options Export query Export SQL only Set Security IV Data val
182. name variable 1 Double click the data object that serves as the security table 2 In the attribute window click the Qualify box for the attribute you want to qualify This attribute must contain data that corresponds to the data returned by the variable you want to use For example BIUserID returns the BI Server user name for the current user The attribute that you qualify with this variable must store BI Server user names 3 In the qualification tree place the cursor in the Qualification box 4 Click the Data Values button then select Variables from the list 5 In the Variables dialog box select the appropriate variable DBUserID BlUserID or BIGroup D 6 Click Insert Warning Users must be logged on to BI Server when submitting queries that execute the BlUserlD or BIGroupID variables Otherwise the variable can t supply the user or group name s This is true even if the user is submitting a local copy of the query that would not otherwise require logging on to BI Server 121 Chapter 8 Working with Results About Results 125 Opening Results 126 Modifying a Query for Selected Results 127 Using Super Queries 129 About the Super Query Window 129 Opening the Super Query Window 132 Guidelines for Editing Super Queries 132 Editing Component Queries 133 Editing an Operation in the Super Query Window 134 Correcting Errors 135 Filtering Results 136 Getting Results in Sorted Order 136 Reordering Columns 137 Narrow
183. names of the employees and who is a manager making over 60 000 You d use the Employees data object to find out the names of the employees and you d use its correlation to find out who is a manager making over 60 000 You use the correlation for the second question because it s the one that uses the join condition in the Managed By relationship When you submit the query the DBMS processes it in two stages Behind the scenes the DBMS first obtains a preliminary results set names of all employees and the departments they work in by joining each row in Employees with each row in Departments then applying the join conditions for Work In The DBMS then applies the join condition for Managed By to the preliminary results set and Emplo__Manag the copy of Employees to retrieve the final results employees who work for managers making over 60 000 The SQL for this query is as follows SELECT Employees LastName FROM Employees Emplo_ Manag Departments Employees W HERE Emplo_ Manag Salary gt 60000 00 AND Departments DepartmentID Employees Department ID AND Departments Manager Emplo__Manag EmployeeID Chapter 10 Building Advanced Queries Building a Circular Query with a Correlation A circular query with a correlation is less restrictive than one without a correlation Because circular queries can be complex if you build one with a correlation it s
184. nct to eliminate duplicate values 3 Ifyou want to retrieve only specific values qualify the attribute accordingly New Entry Delete All Entries Canada France Germany UK 82 Chapter 5 Using Data Values Files 4 Submit the query 5 Ifyou plan to customize your file using a text editor or spreadsheet on the Results menu click Options Select lt CR gt lt LF gt from the Record drop down list box and lt None gt from the End of File drop down list box then click OK 6 On the Results menu point to Save As then click Data Values The Save Data Values Results dialog box opens 7 Inthe Data Values Results Name box type the file name you want BI Query adds the extension qrd to the file To make the data values in the file available in qualifications and prompts for a particular attribute name the file using the following format object attribute where e object is the display name of the data object that stores the attribute e attribute is the display name of the attribute To make the results available only for a specific prompt use the name of the prompt Prompt attribute and object names are case sensitive 8 Click Save Creating Data Values Results Files Using a Text Editor You can use any text editor or spreadsheet program to create a data values results file that contains a set of customized data values To do so you first create a data values results file to serve as a template fo
185. nd select the Qualify box in the same row 2 In the qualification tree from the Operator list for the selected attribute select the IS NULL operator to include rows with NULL values or the IS NOT NULL operator to exclude rows with NULL values 3 On the Query menu click Run Alternatively on the Application toolbar click the Run Query button In some cases missing information is represented by a character space rather than a NULL if for example the space bar was pressed during data entry Since a space is a character to BI Query the above method will not work In this case you have to query for 49 BI Query Queries User s Guide Combining the two queries with an OR will allow you to test if information is missing regardless of whether it is a NULL condition or a space To find rows containing a character space 1 In the attribute window select an attribute and select the Qualify box in the same row 2 In the qualification tree from the Operator list for the selected attribute select the operator 3 Type in the Qualification box Be sure to put a space between the single quotation marks 4 On the Query menu click Run Alternatively on the Application toolbar click the Run Query button Applying Your Own Operators In addition to applying the standard SQL operators provided in BI Query you can restrict a qualification using any other operator that your DBMS database management system
186. ndividual query 1 Ifthe query has not been published create or open it 2 On the Query menu click Publish The Publish Query dialog box opens 3 Do one of the following To secure an unpublished query type its name in the Query Name box e To secure a published query select it from the Published Queries list 4 Click Set Security 5 Inthe Set Security dialog box review the security assignments currently in effect for the query Make any changes as necessary For more information see Setting Security on page 246 6 When you ve finished setting security click OK 7 Click Publish to publish the query Chapter 12 Working with BI Server Using BI Server Scheduler For more information on default connection files see the B Query Data Models User s Guide If your querying environment includes the BI Server Repository and you have the appropriate publishing and scheduling permissions assigned by the BI Server administrator you can schedule queries Scheduling regulates the flow of information from the database by processing the enterprise s queries and reports in a managed time frame Jobs can be scheduled to run once or repeatedly at specific intervals during less busy times or when a specific event has occurred Scheduling also provides a variety of ways to distribute information If the queries in a data model you publish will be scheduled to run automatically you must set a default connection file f
187. ng Prompts About Prompts The name of the Enter Value dialog box changes depending on the type of prompt in use For more information see Naming Conventions for Prompts on page 94 A prompt is a request to enter a value or set of values Typically you can insert a prompt into a query qualification instead of specifying data values Each time the user submits the query the query can retrieve information for different values depending on what response the user makes to the prompt You can also include prompts in other contexts including join conditions and object qualifiers A prompt consists of a name and a prompt string The name distinguishes the prompt from others you may create and may indicate if it belongs to a special aggregate known as a group prompt The prompt string is the text that appears in the Enter Value dialog box when users run a query involving the prompt for example Please enter a city name Enter Value x Indicates a required value List Button Lists previously entered values for the prompt Please enter a city name Data Values Button Lists available data values and data sources such as variables for the prompt You can also associate a particular data type with a prompt and specify whether users must supply a value for the prompt If the prompt appears in a report for BI Web users you can specify the appearance of the prompt and in some cases an optional list of al
188. nother application About the Super Query Window Use the Super Query window to combine queries You can also use it to sort results reorder columns and specify a range of results The Super Query window is divided into two panes separated by a splitter bar which you can use to resize the panes 129 BI Query Queries User s Guide uper Query Invoice Amounts OF x Toolbar 8 3 Append Operation 2 November Invoices F 3 December Invaices Component queries tetailers cust_name sales ord_amount Message pane retailers sales The top pane displays the structure of the query using a tree list Each component query and each operation is included in the tree and is represented by one of the following icons Icon Description E Component query ES Sort operation E Range operation a Reorder operation E Join operation z Append operation x Error in operation The operations performed on a component query s results appear above the component query in the tree For example if the results of a component query are reordered then sorted the tree list shows three branches first the component query then the reorder operation then the sort operation above it 130 Chapter 8 Working with Results When you re working with complex queries you can see where a join or append occurs by collapsing the tree structure then expanding the branches one by one
189. ns appear as dashed connection lines Join conflicts do not appear in BI Web Example Consider three tables A B and C with the following data Table A Table B Name ID Country CountryCode Jane Smith 100 UK 0114 Hector Alvarez 200 Canada 2010 Ella Bruce 300 Mexico 2276 Francis Coulomb 400 Japan 4531 Table C ID Title Country 100 Manager France 200 Writer Mexico 250 Manager Germany 500 Assistant Australia Chapter 10 Building Advanced Queries Tables A and B each form outer joins with C such that A and B are both row preserving C in this case is the materialized relation _ C If the join between A and C has order 1 and you create a query involving both A and B the join conditions between A and C are tested first The results of this first join contain all rows from A plus those from C that match A on the joined column ID Table A supplies NULLs for its unmatched rows Name ID Title Country Jane Smith 100 Manager France Hector Alvarez 200 Writer Mexico Ella Bruce 300 NULL NULL Francis Coulomb 400 NULL NULL The results of the second join contain all rows from B plus those from the results of the first join that match B on the joined column Country However the NULL values supplied by A do not match any values in B in the joined column so that the corresponding rows from
190. ntax error Note Each prompt retains the last value selected by you and displays that value as the default the next time it opens If you leave an optional prompt empty you effectively erase the last value Prompts for BI Web Reports For BI Web users who access published standard reports or BI Query reports you can specify allowed data values for prompts in the corresponding queries These values are known as web reporting values When BI Web users open and refresh a report whose query has a prompt they can select a value for the prompt from the web reporting values you have specified As an alternative to specific values you can also make available the data values from a data values query file if one exists for the prompt or the attribute if the prompt qualifies an attribute Note If data values query files exist for both the prompt and the attribute only the file associated with the prompt supplies data values 97 BI Query Queries User s Guide 98 Prompt Display Types for Reports in BI Web When you create a prompt for a BI Web report you must design its interface For a given prompt you can select one of the following display objects Object Description ab Text Box lets the user type one or more values This is the default object for web reporting values You can use a data values query file for web reporting values only if the display object is a text box If there is no data values query fi
191. o improve query performance and prevent users from submitting queries that retrieve more data than they need For example regional sales managers may need sales information only for their regions When they submit queries against the table containing sales information the queries prompt them to specify the regions they want Other Prompt Applications In addition to using prompts to qualify attributes you can include a prompt in any context where you want the user to supply a value For more information on prompts and their applications see the following topics e Using Functions Prompts and Other Values in Variables on page 116 e Adding Calculated Columns on page 160 e Applying a Function Before Grouping on page 184 Creating a Single Prompt Use the Prompts and Edit Prompt dialog boxes to create a prompt and specify its properties Edit Delete Prompts Staff 1 BeginDate New Tie 1 EndDate i l E String Please Enter Year ategory YN eak NA Datatype char 4 stores Title Appearance Text Properties The Prompts dialog box stores 1country i Required lists the properties of th Soni Data Value Queries allowed eae a i j Multiple values p Pl If the list is too long to view click in the list then use the up and down arrow keys to scroll through the items in the list Last Used Values 2001 Web Values 1999 2000 2001 Close Help Chapter 6 Using Prompts
192. ode The Drawing toolbar and Layout palette are available only in Design Mode The tools on the Drawing toolbar let you create objects and assign properties to them color line thickness and so on The Layout palette provides tools for resizing and aligning objects Using BI Query with BI Server BI Server is an application server that uses a common set of services and a common repository of information to provide data for Hummingbird BI applications The BI Server Repository is a storehouse for enterprise information produced using Hummingbird BI applications It stores data models queries results sets reports multi dimensional data sources and associated metadata If you are in a BI Server environment you will be able to take advantage of BI Server s publishing retrieving scheduling and security features Chapter 1 Introducing BI Query Specific terms are used to distinguish Server related actions from local computer based actions Locally saved data on your desktop computer is saved or opened Material stored on the BI Server Repository is published or retrieved If you ve been assigned the appropriate system permissions you can publish BI Query information to the Repository and you can retrieve information you ve published as well as information published by others By publishing to the Repository you ensure that the information is accessible to other BI Query users and to BI Web users Because you can set security o
193. oducts whose unit price is greater than unit cost 1 10 you cannot type unit cost 1 10 in the Qualification box BI Query would interpret unit cost as text not as an attribute name You must insert the real database name for attribute Unit Cost To qualify one attribute with another 1 In the qualification tree click in the Qualification box for the first attribute 2 On the Edit menu click Insert Attribute Name The Insert Attribute Name dialog box opens Data objects Attributes Order Details Orders Periods Product Product Lines Regional Office Retail Locations Sales Sales Detail Staff Database name Store_S country Cancel Help 3 From the Data Objects list select the data object that contains the attribute you want to qualify first 4 From the Attributes list select an attribute then click Insert Chapter 3 Building Queries 5 From the Operator list for the qualified attribute select the appropriate operator 6 If you want to include a calculation click in the Qualification box and type the calculation BI Query automatically delimits the expression with brace brackets for example Sales order 100 Using Qualification Operators When you qualify an attribute BI Query displays the operator in the qualification by default then finds all the records in the database that match the qualification s you specify For example by setting the Country attribute equal to Spai
194. og box in order to display a list of data values that can be used in the prompt BI Query checks to see if there s a data values file with the same name as the prompt If there is BI Query uses it otherwise it checks to see if there is a data values file named after the data object and attribute used in the query If there is BI Query uses it 81 BI Query Queries User s Guide Stores By City x X Indicates a required value Data values for a prompt Please enter a country that qualifies an attribute x Be Canada Cancel Help Lae more Variables If BI Query doesn t find a data values file and if data values for the qualified attribute haven t been disabled you can click Data Values in the pop up menu to open the Data Values dialog box The dialog box lists all the unique values in the database for the attribute Creating Data Values Results Files For information on You can create data values results files from BI Query results or customize retrieving and opening the results using a text editor or spreadsheet You can qualify attributes by stored data values results inserting these data values into qualifications and prompts files see the Help for your BI Query application rae To create a data values results file 1 In an attribute window click an attribute then click the Sort box and apply any other restrictions you want 2 On the Query menu point to Modifiers then click Disti
195. ollowing AVG Sales_S order_amount 4 Type the qualification in the Expression box Chapter 10 Building Advanced Queries For example to find only averages greater than 2000 you would add gt 2000 so that the complete entry in the Expression box reads as follows AVG Sales_S order_amount gt 2000 If you had grouped before calculating only amounts greater than 2000 would have been averaged You can also use the available buttons to build the expression e To include a prompt in the qualification click Prompts then use the Prompts dialog box to select and insert it To include a variable click Variables then use the Variables dialog box to select and insert it e To include another attribute in the qualification click Attributes In the Insert Attribute Name dialog box select the data object and attribute name then click Insert To include a built in or DBMS specific function click Functions then use the Functions dialog box to select and insert the function Click OK Submit the query The group qualification will be retained with the current query unless you remove it from the Group Qualification dialog box or click New on the Query menu Note When apply group qualifications to restrict the number and types of groups returned you can specify a group order as well Grouping without Functions To eliminate duplicates you can group attributes without applying functions For instance if
196. olumns 1 Shift click the columns you want to stack 2 On the Report menu click Stack Columns Formatting Column Text and Headings Varying the appearance of text in a report provides variety and contrast improves readability and helps distinguish between different pieces of information A unique typeface can make a title or masthead stand out Bolding and italicizing add further contrast and emphasize important information You can format the text in columns and column headings in terms of font size style alignment effects strikeout and underline and color You can also change the actual text of a column heading which is initially the same as the column heading in the corresponding results set To format text in a column or column heading 1 Click the column or column heading you want to format 2 On the Report menu click Text Style The Text Style dialog box opens 3 Specify the text format for the selected column or heading Chapter 9 Creating Standard Reports For more information on border formats see Adding Borders to Standard Reports on page 168 4 Ifyou are formatting a column heading and want to change the text of the heading type the new heading in the Text box 5 ClickOK Customizing Column Settings In addition to resizing and banding a column you can apply the following customizations e modify the column name e change the text wrap within the column e make the column fit the pag
197. onger exist in the database When you open a data model BI Query compares the dates on which the data model and the queries were last refreshed If the data model was refreshed more recently than the queries BI Query prompts you to refresh them The administrator may have also changed the syntax type for outer joins in the data model in which case the saved queries that include those joins may no longer work correctly If you enable the Preserve Legacy Query Outer Joins preference BI Query treats new syntax outer joins as legacy joins when you run a saved query Refreshing a Query When you refresh a query BI Query compares the queries with the data model and displays information on each query that has been affected by changes to the data model BI Query can detect only whether attributes have been deleted and not whether they have been changed To refresh queries 1 Ensure that BI Query is not in Design Mode 2 On the Tools menu click Refresh Queries 3 Inthe Refresh Query dialog box select the queries you want to refresh or click Select All then click Refresh Data values queries are identified by the words Data Values after the query name Data values queries cannot be refreshed 4 Do one of the following e Click Continue to leave the query unchanged and view the next one e Click Change to update the query e Click Change All to update all the queries selected e Click Cancel to return to the Refresh Query dialog box
198. onvert one report at a time 1 In BI Query open and submit the query associated with the standard report Close the report window 2 On the Results menu point to Show as Report then click Bl Query Standard Reports Converter The Open dialog box opens 3 In the dialog box locate and select the report specification you want to convert then click Open By default BI Query saves standard report specifications in the Queries folder Specifications have the extension rpt 4 In BI Query Reports check the newly converted report and make any necessary changes The converted report has the same name as the specification except that it has an rep extension The converter saves the converted report in the same folder as the specification Converting Multiple Standard Reports You can convert multiple reports simultaneously using the batch converter utility To convert multiple reports using the batch converter 1 Using Windows Explorer browse to the folder where BI Query has been installed By default this folder is the following C Program Files Hummingbird BI 174 Chapter 9 Creating Standard Reports 2 Inthe folder locate and open the Report sConverter subfolder then double click Rpt2Rep exe The Bl Query Standard Reports Converter dialog box opens 3 On the Files page click Model 4 Inthe Select Bl Query Data Model dialog box locate and select a data model then click Open 5 Do one of the following To
199. or Variables 114 Using Literals in Variables 114 Using Placeholders in Variables 115 Using Functions Prompts and Other Values in Variables 116 Creating and Using Variables 117 Using Variables to Qualify an Attribute 119 Importing Variables from a File 120 Using Variables to Supply User or Group Names 121 Chapter 7 Using Variables About Variables A variable or automatic variable is a predefined operation that you can use to qualify a query Typically variables qualify attributes but you can also use them in object qualifiers Using variables can simplify potentially complex queries A variable is defined by an expression When you submit a query that includes a variable BI Query evaluates the corresponding expression and adds it to the WHERE clause for the query A variable can contain attributes operators values in the database calculations functions subqueries prompts and other variables anything that s valid in an SQL WHERE clause You can also use special placeholders in the expression for a variable to increase its scope Variables are stored in the Prompts file in the Queries folder and therefore must have names that are different from those given to prompts You can import variables stored in another user s Prompts file Predefined Variables BI Query includes three predefined variables DBUserID Supplies the database user name for the user currently logged on to the database
200. or let BI Query automatically set the data type based on the calculated values Note If you select an automatic data type for a calculated attribute BI Query cannot run the default SELECT DISTINCT query for the attribute In this case if you want to qualify the attribute you cannot select the qualification value s from a list of data values retrieved from the database 187 BI Query Queries User s Guide To create a calculated attribute 1 Double click the data object to which you want to add the calculated attribute BI Query must not be in Design Mode 2 In the attribute window click the Function box for the attribute below which you want to add the calculated attribute 3 On the pop up menu that opens click Calculation The Edit Calculated Attribute dialog box opens 4 In the Name dialog box type the name you want to give to the calculated attribute For more information on 5 From the Data Type drop down list select the data type for the attribute available data types for calculated attributes see If you want the type to automatically match the data type of the the Help for your BI Query calculation select either Automatic Numeric or Automatic Character application depending on the calculation You can also select a specific data type for the attribute Warning If you re connecting to a Teradata DBMS and you re not using ODBC you must select a specific data type for the calculated attribute Do not use
201. or more information see c To make data values from a data values query file available to the Naming and Storing Data user select Allow Data Value Queries This check box is enabled only Values Files on page 80 if you have selected Text Box from the Prompt Type drop down list You must create the file if it does not exist or does not have the correct name For more information see d To specify allowed data values explicitly click Values The Web Specifying Web Reporting Values for Prompt dialog box opens Specify the values you Reporting Values on want to make available to users then click OK You cannot specify pagel values if you have selected Text Box Date Entry Time Entry or Date amp Time Entry from the Prompt Type drop down list 8 Click OK then click Close to close the Prompts dialog box Inserting a Prompt in a Query You define a qualification with a prompt in the same way you would a qualification with a value If you qualify more than one attribute in a query with the same single prompt the value you specify for the prompt applies to each attribute To insert a prompt when forming a qualification 1 Inan attribute window click the Qualify box for an attribute 2 Inthe qualification tree place the cursor in the Qualification box for the attribute 3 Click the Data Values button then select Prompts from the list 4 Inthe Prompts dialog box select a prompt If the prompt you want isn t li
202. or the model before you publish it Since the Scheduler runs queries from BI Server not from the machine where they were created or last saved you need to ensure as well that the connection you use is supported from the BI Server machine Your BI Server administrator can provide you with this information For more information on scheduling see the BI Server Scheduling User s Guide Scheduling a Query You must publish your query before you can schedule it and in order to publish a query you must first publish the data model on which it is based To schedule a query 1 On the Query menu click Retrieve 2 Inthe Retrieve Query dialog box select a query to schedule and click Retrieve 3 On the Query menu click Schedule 4 Inthe Schedule Job dialog box provide the appropriate information The Schedule Job dialog box is part of another BI program module with its own Help system For information about this dialog box click its Help button 249 BI Query Queries User s Guide For more information on scheduled queries see the BI Server Scheduling User s Guide 250 Retrieving Scheduled Results You can retrieve from the BI Server Repository any results sets generated by scheduled queries To do so you must have the BI Server Schedule Queries permission To retrieve results for a scheduled query 1 On the Results menu point to Retrieve then click Scheduled Results 2 Inthe Retrieve Results Sets dialog box
203. ore information see spreadsheet The columns reflect the attributes selected in the query The EF es name of the results window corresponds to the name of the query Current Receivable x Store Name Short m m ah l E l el l l 1o l E 3 The results window for the Current Receivable query 125 BI Query Queries User s Guide 126 You can also display a row in form view by double clicking the row number in the results window Form view displays one row at a time and provides a way to navigate through the rows you have retrieved Current Receivable Form Formatting Results You can format the results that appear in a results window either by formatting the attributes involved in the query or by formatting the result columns directly For more information on formatting results see About Data Formats on page 57 Opening Results BI Query saves results as two files one with the extension qrd ASCII text format for use in other applications and one with the extension qrr BI Query specific information such as formats for each column and the SQL used to retrieve the results When you open results in BI Query only the qrd file appears in the Open Results File dialog box To open a results file 1 On the Results menu click Open 2 In the Open Results File dialog box double click the qrd file you want to open Chapter 8 Working with Results Modifying a
204. orts by Retailer 4 Retrieves the results of the Retailer Receivables query 131 BI Query Queries User s Guide 132 Reorders the columns Sorts by Retailer Joins the results from step 3 with the results from step 6 on A A Reorders the columns to produce the final query Opening the Super Query Window You can open the Super Query window from an open query or a results set To open the Super Query window from a current query On the Query menu point to Super Queries then click Show Super Query The new query appears in the Super Query window where you can combine it with saved queries To open the Super Query window from a current query results set On the Query menu click Show The Super Query window also appears when you open a saved query that was created using the Super Query window Guidelines for Editing Super Queries When you want to change the results a combined query retrieves you can edit it in the Super Query window You can change each component query each query that makes up the larger query and each operation performed on the results it returns For example you can add an attribute to a component query then change a sort operation to use the new attribute When you edit a query in the Super Query window follow these guidelines e Start with the innermost query and work up through the tree This makes it easier to correct any errors e Save your work regularly e Don t edit a com
205. ose whether to retrieve the more recent version or you may be required to do so To start BI Query and open a data model 1 On the Start menu navigate to the Hummingbird BI program group point to Hummingbird BI Query then click your BI Query application one of BI Query Admin BI Query Update or BI Query User 2 Ifthe BI Server Log On dialog box appears do one of the following Chapter 2 Getting Started To log onto the Repository type your user name and password If the Domain box appears and is enabled type your domain name If you re unsure what information to provide in the dialog box check with your BI Server administrator Click OK To work without access to the Repository click Work Offline In the Welcome to Hummingbird BI Query dialog box do one of the following To open a data model that you have used recently click Recent Data Model In the Recent Data Models dialog box select the data model you wish to open then click Open To open a local data model click Local Data Model In the Open Data Model dialog box select the data model file you want then click OK To open a new data model click New If the Enter Data Model Password dialog box appears type your password If a password has not been set this dialog box does not appear A message box may open to indicate that a newer version of the data model exists in the database To open the newer version click Yes To open a data
206. our query from that data object as well If your query is complete click Run on the Query menu or on the Application toolbar click the Run Query button Submitting and Clearing Queries Once you ve built a query you can submit it to the database The results of the query are displayed in a separate results window named after the query BRI Stores The results window for the All Stores query 29 BI Query Queries User s Guide 30 You can build your entire query and then submit it Or you can specify some initial qualifications submit the query review the results and then revise the query to retrieve exactly the set of results you want To submit a query 1 Do one of the following e Build a new query e Open a previously saved query on the Query menu click Open and specify a query in the Open Query dialog box e Retrieve an existing query from the BI Server Repository For more information see Retrieving Queries on page 241 2 On the Query menu click Run or on the Application toolbar click the Run Query button Queries remain current even after they have run This may not be obvious if the query is not on the screen You should always clear the previous query before starting a new one unless you want to modify the previous query To clear a query do one of the following e On the Application toolbar click the New Query button e On the Query menu click New Cancelling Queries Af
207. p you want to delete Note BI Query deletes the relationships that you have created but not saved Relationships will still be part of the query if you have saved the query Using Outer Joins in a Query For more information on For queries containing outer joins the order of the joins can significantly outer joins see About affect the query results For this reason once you add an outer join to a Column Joins on query BI Query sets an order for every join If you have selected the Show page 143 3 A ee Join Order preference BI Query displays the join order for the connections if the join order is relevant to the current query Note There can be two or more joins with the same join order This is because join order is based on the level of nesting Two joins that are nested at the same level within other joins have the same join order 201 BI Query Queries User s Guide For more information on merged data objects see the B Query Data Models User s Guide 202 If there are merged data objects in the query and the Automatically Order Outer Joins preference or permission is assigned BI Query treats each join within the merged object as any other join and orders it automatically Ifthe preference and permission are not assigned the join order for each join in the merged object matches the order in which the join was added to the object If the Show Join Order preference is set the ordinal number for joins o
208. pens select the branch of the join or append operation that you want to delete Chapter 8 Working with Results Note To delete a component query you must delete its join or append operation Each branch in a join or append operation corresponds to a different component query Correcting Errors Errors in the Super Query window must be corrected before the query can be saved or submitted You can correct errors by editing either the operation or its associated component query Most errors occur because an attribute used in an operation is missing from the component query on which the operation is performed To correct an error in the Super Query window 1 In the Super Query window click the operation causing the error When an error occurs BI Query displays an error icon beside the corresponding operation lolx Read the information in the message pane The message pane provides information about why the error occurred and how to correct it Edit the component query or operation If yov re editing more than one operation start with the operation immediately above its corresponding query and work up through the tree Save the query To test the query on the Query menu click Run 135 BI Query Queries User s Guide Filtering Results For information about narrowing the range of results and performing calculations on results see Narrowing the Range of Results on page 139 136
209. perator oe polnd ssa avis He ease ee NULL OPCTALOL oo a N S e a Nr saN 49 values format order eee 59 Hull values EE statistic at anaes 49 NUMETIC sort Ord E eee eee e ee eeeees 136 0 ODBC syntax for outer joins Offline working oo eee ea opening data models e super query windows Index operations deleting in super queries 0 0 cee 134 editing in super queries 134 operator list qualification tree s es 45 operators applying to qualifications 38 Begins With eeeseseseseeeseteeeeeeees 46 48 BETWEEN is chun Auta ee 45 48 oTa tat ia AANA 48 database supported sssr 50 Does not begin With eee 48 Does not contain 0 ceeeceeeeeeeeee 46 48 Does not end with ssec 48 Ends WIth Asccssicscdecteorseestetesecctvescortede 46 48 for join Conditions eee 197 IN sssdiceccst conte na a kae 45 47 ID VaTtiables sii ube el aaa epedt 114 IS NOT NULL eerie 49 NOT BETWEEN eee 45 48 NOTIN sire score Feciosiothioninniitenct 45 47 NUE E es esse ue iets 49 placeholders in variables 0 0 0 0 115 User defined ooro nenne ats ens a 50 options export setting for Excel owe 230 results actions Of 220 ordering columns in results 137 ornaments in standard reports eee 169 special text strings 1 eee 170 outer join types full OUter JOINS horia 144 left outer JOINS eee 144 right Outer JOINS eee 144 outer joins available syntax 206 Classic ANSI SQL 89 syntax
210. pression box then click Operator To insert a prompt click Prompts then use the Prompts dialog box to create or select the prompt and insert it into the expression To insert a variable click Variables then use the Insert Variable dialog box to select the variable and insert it into the expression To insert a function click Functions then use the Functions dialog box to create or select the prompt and insert it into the expression 5 Click OK In the Variables dialog box click Close Using Variables to Qualify an Attribute You can qualify an attribute so that a variable specifies the search conditions for the query A variable can contain anything that s valid in an SQL WHERE clause attributes operators values in the database calculations functions and subqueries Variables are especially useful for queries that run on a regular basis or are scheduled to run at specific intervals using BI Server s scheduling facility 119 BI Query Queries User s Guide To qualify an attribute with a variable 1 In the qualification tree place the cursor in the Qualification box for the attribute 2 On the Edit menu click Variables 3 Inthe Variables dialog box select the variable you want or create or edit one 4 Click Insert Importing Variables from a File When you want to use variables created by another user you can import them from that user s Prompts file To import variables from a file
211. r Format Preferences dialog box click OK if necessary Formatting Date and Time Data The Date data category encompasses date and time date only and timestamp data The Time category is for time only data You can format both categories using the same version of the Format dialog box To format date or time data 1 Open the Format dialog box for the target you want to format For more information see Opening the Format Dialog Box on page 61 Do one of the following To use a predefined time format select it from the Predefined Time Formats list To remove the time component from date and time data select lt Blank gt from the Predefined Time Formats list e To use the Regional Settings for the category select lt Regional Settings gt from the Predefined Time Formats list e To use the data model format for the category select Default available only if you are formatting an attribute or result report column If you are formatting Date category data do one of the following To use a predefined date format select it from the Predefined Date Formats list 71 BI Query Queries User s Guide To remove the date component from date and time data select lt Blank gt from the Predefined Time Formats list e To use the Regional Settings for the category select lt Regional Settings gt from the Predefined Time Formats list e To use the data model format for the category select Default
212. r changes to be local to a particular window or data model create a new connection file assign it to the window or model then make the necessary changes to it For more information on creating connection files see the B Query Data Models User s Guide Changing Outer Join Syntax for a Connection Use the Join Syntax dialog box to select an outer join syntax for a connection file The syntax you select must be supported by the connection To change the outer join syntax for a connection 1 On the Host menu click Connections 2 Inthe Connections dialog box select the connection file you want to use then click Edit 3 In the Edit Connections dialog box click Join Syntax The Join Syntax dialog box opens 4 From the Join Syntax drop down list select the outer join syntax type that you want to apply to the connection The dialog box displays the default settings for the selected syntax Chapter 10 Building Advanced Queries Join Syntax x Join syntax sQL 92 Default Outer join capabilities Default C Custom I Left outer joins IV Right outer joins I Full outer joins IV Nest outer joins I Inner and outer joins IV Multiple joins I Reuse non preserved side of left or right outer join in an inner join I Order table names in join condition differently than in outer join expression IV Use operators other than in outer join conditions IV Cross Products Cancel Help 5 Click OK The Join Synt
213. r the customized data values This sets up the qrd file which stores results in text format and the qrr file which stores additional information that the application needs to read the text file Once you create these files you replace the data values in the qrd file with the customized data values When you type values into the text editor or spreadsheet program type them in the order and case you want them to appear in BI Query pressing Enter after each value 83 BI Query Queries User s Guide 84 To create a data values results file using a text editor 1 Inan attribute window click an attribute You can select only one attribute for a data values results file 2 Click the Sort box to sort the results then apply any other restrictions you want 3 On the Query menu click Modifiers then Distinct to eliminate duplicate values 4 On the Query menu click Run to submit the query 5 When the results appear on the Results menu click Options The Results Options dialog box opens Options Separators I Save query name Field lt tas gt E e ola Sa I Text in quotes End of file lt None gt I Transpose Cancel Help 6 Inthe Separators area select lt CR gt from the Record drop down list and lt None gt from the End of File drop down list 7 ClickOK 8 On the Results menu point to Save As then click Data Values 9 In the Save Data Values Results dialog box name the file 10 Click OK 11 I
214. r the new column You can also do the following e Use the numeric and operator keypad at the right of the dialog box to enter arithmetical expressions e Use the Items drop down list box to select an item on which to base the calculation Columns Subtotals Grand Totals Prompts From the list below the Items drop down list box select the calculation attribute or prompt that you want to add to the calculation Click Insert 4 ClickOK Manipulating Columns in Standard Reports When data appears as a sequence of asterisks the corresponding column is not wide enough to display the data You can also reorder columns For more information see Reordering Columns on page 137 162 When you generate a standard report from the results of a query BI Query displays the results in the order in which they appear in the results window If you want to reorganize the results or save space you can change the column order and combine band or stack columns Resizing Report Columns If a standard report contains more columns than fit on one page BI Query spreads the columns over the required number of pages a gray shaded line separates each page To view each column scroll to the right To prevent columns dividing over more than one page resize or reorder them as necessary To resize columns 1 Click a column 2 Do one of the following e Drag the right hand border of the column to the width you want e On the R
215. require Adobe Acrobat Reader 5 0 and are installed only if you do a Complete installation Your Hummingbird product comes with the following manuals BI Query Installation Guide Determine system requirements and install BI Query and BI Query Reports BI Query Queries User s Guide Query corporate databases and export data to other applications BI Query Data Models User s Guide Create and manage data models and update records in the database BI Query Reports User s Guide Produce reports using BI Query Reports from data obtained using BI Query BI Query Chart Editor User s Use advanced features to edit charts created in BI Query Guide PDF Only Reports Help The online Help is a comprehensive context sensitive collection of information regarding your Hummingbird product It contains conceptual and reference information and detailed step by step procedures to assist you in completing your tasks Release Notes The release notes for each product contain descriptions of the new features and details on release time issues They are available in both print and HTML The HTML version can be installed when you install the software Read the release notes before installing your product Professional Services Hummingbird offers consulting and training services worldwide Working alongside your technical and non technical staff Professional Services can help you identify areas where improved information management can enh
216. ries cannot supply sorted data values 2 On the Query menu click Save The Save Query dialog box opens 3 In the Query Name box type a name for the subquery then click Save To qualify an attribute with a subquery 1 Ifnecessary click the New Query button on the Application toolbar to begin a new query 2 Double click a data object and then select the attributes for the query you want to build 3 Click the Qualify box for the attribute you want to qualify then click the Data Values button in the qualification tree 4 On the menu that opens click Subqueries 5 Inthe Insert Subquery dialog box select the subquery from the Queries list then click Insert 6 From the Operator drop down list in the qualification tree select an operator If the subquery is returning a single value select If it is returning a list of values select IN or NOT IN 191 BI Query Queries User s Guide Store Info Attribute m Beceni E Subquey Ma ecsivane lel Es m 7 Submit the query or save it for later submission Note The Begins With Contains Ends With Does Not Begin With Does Not Contain and Does Not End With operators are invalid for subqueries Using Data Sets For more information on qrr files see Naming and Storing Data Values Files on page 80 192 A data set is a text file that stores one or more data values You can use data sets to qualify attributes in a query
217. rly brackets for text entry 0 ee 75 customizing results for USCYS necununoniani ai customizing data models 260 D data types for prompts nsss 94 data alias OVEEVICW ssrsicdeevhatterctesstavestedstsieveegee teats 87 creating a data values file 88 data display results form VieW ss 125 spreadsheet view 125 data formats anacan iik 57 converting year formats s s s 74 correcting invalid data entry formats 75 data entry formats oe 72 data type formats 57 date entry format eee 74 formatting Options eee 58 in standard reports cece 153 modifying SQL 207 208 number entry format ee 73 opening the Format dialog box 61 order of formatting options 59 predefined formats ccc 58 TAT SOL AY POS isesi a ai 60 data formats options category specific Options n 58 Computed String option 59 Null values option 59 order Of Options oo eects 59 String Option Aninu 58 data formats specifying default formats occ for boolean data c for data entry ee for numeric data for years number of digits the Computed String option the Null Values option the String option oe data model design eee eee eee eeeees Index data models CUSLOMNIZING seczzssstesssustesrases treaties ad definitiones menma excess Seessieet newts testd opening passwords Recent repository data models split data objects efit Des
218. rms a user defined calculation in a query The calculation can be based on more than one attribute from more than one data object in a design window You can also apply a function to an attribute in the calculation For example a data object may include Unit Price and Unit Cost attributes that retrieve a product s market price and production cost respectively By creating a new attribute that calculates Unit Price minus Unit Cost you can determine the profit made on each product Query Results 12 iof x The calculated attribute Profit calculates the profit made on each product Chapter 10 Building Advanced Queries For more information on editing and deleting calculated attributes see the Help for your BI Query application In acombined data model users can edit or delete any calculated attribute In a split data model users can edit or delete only calculated attributes that they create not those provided by the administrator Use the Edit Calculated Attribute dialog box to create calculated attributes Edit Calculated Attribute x Name Data type Profit Automatic numeric z Expression Product_S unit_price Product_S unit_cost a I Attributes rout 7 8 9 Insert Product Id Product Name Profit Subcategory Unit Cost Unit Price Aggregate IV Save in data model When you create a calculated attribute you can either set a specific data type for the attribute
219. rnaments and appear in specific sections of a report by default The title and date are text ornaments that appear in the header of a report The title is based on the name of the corresponding results window The date is the same as the date on your computer The page number is a text ornament that appears in the footer of a report You can change the appearance and content of each default ornament move it to a different position or remove it altogether For example you can change the default title of your report to reflect the information it contains and you can change the date to display a different date format You can also add text and graphical ornaments to a standard report You create these ornaments in much the same way as you create ornaments in a design window in BI Query 169 BI Query Queries User s Guide Special Text Strings in Standard Reports The content of a text ornament in a standard report can contain any of the special text strings that BI Query supports For example the default date ornament contains the special string amp d which displays the current date BI Query supports the following lowercase strings in text ornaments in standard reports String Description amp t Displays the current time amp d Displays the current date amp dn Displays the current date using predefined format n where n is a number greater than or equal to zero 0 n corresponds to a predefined format in the
220. s 3 To group the data in a different order than the order in which you selected the attributes click the Group box for the remaining attributes The function is applied after the results are grouped F Sales This query calculates the average of all order amounts Grouping occurs first for employee IDs then store IDs 183 BI Query Queries User s Guide 184 Applying a Function Before Grouping Use the Group Qualification dialog box to build the expression for a group qualification Group Qualification x Expression AVG Sales_S order_amount gt 2000 Aggregated attributes Insert Attributes AVG Sales Order Amount Functions Prompts Variables mere So di Aggregated Attributes lists the Count function plus any attributes to which an aggregate function has been applied and any calculated attributes that are treated as aggregates To perform a calculation before grouping results 1 Construct a query using an aggregate function 2 On the Query menu point to Qualification then click Group The Group Qualification dialog box opens 3 From the Aggregated Attributes list select the column with the aggregate function to which you want to apply the group qualification then click Insert The function and attribute appear in the Expression box For example if you have applied the AVERAGE function to an order amount attribute the Expression box might read the f
221. s For more information see Modifying the SQL for a Query on page 207 For information on deleting saved queries see the Help for your BI Query application Viewing Query Specifications You can view query specifications selected attributes functions sort order and group order for each data object in the Query window fd Query ProductList OF x show Product Category Product Subcategory Product Product Name Product Unit Price Product Unit Cost group by sort by 23 where sale T Show Prompt Values If the Show SQL String preference is set the Query window displays the corresponding SQL string If the Edit Queries permission is assigned you can modify the SQL string directly in the Query window To view query specifications 1 Open or create the query 2 Onthe Query menu click Show Saving Queries If the Save Queries permission is assigned you can save queries in order to use them again in BI Query You can also export the underlying SQL text for use in other applications You can save a query before or after submitting it to the DBMS You can open saved queries in BI Query then run or delete them 33 BI Query Queries User s Guide To save a query 1 On the Query menu click Save The Save Query dialog box opens Save Query Eg Queries Query name All Stores ProductList All Stores Totals PerformanceComparison_EU Export Export options Performance
222. s in a column labeled Store Name and the city in which each store is located in another column labeled City You can select as many attributes as you like in each data object And you can select attributes from as many data objects as you like within one data model window If your data model has data objects in more than one window you can build separate queries in each window then use one of several methods for combining them If your data model contains many data objects you can find the ones you want using the Find and Replace dialog box For more information on searching a data model see the BI Query Data Models User s Guide To select attributes 1 Ifa query is already open and you want to build a new one click New on the Query menu 2 Double click the data object for which you want to retrieve information Sales Detail Product Staff Store Regional Office Double click the Store data object to open its attribute window Chapter 3 Building Queries 3 For more information see 4 Querying Multiple Data Objects on page 50 5 In the attribute window click the attributes that represent the categories of information you want to retrieve na Atiibute 0 one Rynction Quay Group Soi E 2 L Region Office fle Ciy S lL State S U aL Zp S 2 Receivabe PaL PL 2l Payable E If an additional data object is connected by a relationship with the first you can select attributes for y
223. s a list of products the cost per unit of each product the number of units sold a calculated column that determines the value of each sale the cost per unit multiplied by the number of units sold and a grand total of sales You can add another calculated column to determine what percentage of the grand total each sale represents Chapter 9 Creating Standard Reports For information on editing or deleting a calculated column see the Help for your BI Query application The Total Sale column calculates the value of each sale cost per unit multiplied by the number of units sold Sales Report Cost Units Total Item Unit Sold Sale Total The Total column divides each value in the Total Sale column by the grand total and multiplies by 100 to determine the percentage of the total that each sale represents 2 nails 3 3 9 37 50 nuts 3 2 6 25 00 pins 1 6 7 29 17 Grand Total 24 To add a calculated column 1 On the Report menu click Add Calculated Column The Add Calculated Column dialog box opens 2 Inthe Name box type the name of the new column Add Calculated Column Total Order Amount Sum of Order Amount 100 Grand Totals Sum of Order Amount SOT 161 BI Query Queries User s Guide Your calculation can involve more than one item in the report or corresponding query 3 In the Expression box type the calculation fo
224. seesesererersesssersrersressessees 44 Using Qualification Operators nokristies ristini eied 45 Matching Character Patterns scree orenen itai EAE 46 Using Wildcard Characters eeseeesccessccessceessceeseeessceesaceesaeecsaceeseressaeees 46 Making Comparisons csser soiin a a eies 47 Matching Ttems naliste on a e a tee ii wets 47 Finding a Range of Values essssesesesesesssessesssessressrersessrerereserersessreesresseesees 48 Matching Any Matching All sssessessessssseseerssessessererseessesreseesressesrereeseesrenes 48 Determining if Data is Missing eeesessesssseseseeesssessersressessressrerseesressresseeses 49 Applying Your Own Operators ceescesscesssecesseceesecessecesceceseeeesaeeesaeessaes 50 Querying Multiple Data Objects oo eesssseesseeseseeeeeesseeeecscecesesceecsceesaceeaeeeeesaeees 50 Removing Unnecessary Joins from Queries 0 eee ee seeeeseeesteeeeeeeteseeeeaeeeee 51 Updating Queries cso ernir iieiaeie E n e Ai 52 Refreshing a Query issccssscsatesvgsinisiseedidedeiccvercebedesvahausanenestevssnareqievessecedccess 52 Using Lesacy Joins ASIS sitione ae ie ee re cds diiisdieeseleteas 53 Chapter 4 Formatting Data 55 Abo t Data Formats 5 lt 25cscec astiepessesndies rads voassa PaaS REEE ANo EA a Ea aa aiii 57 Data Type Categories scicisccccsissisiivesesescccccssstscnsvecsedsavedvabieasscovesssatececuccecs 57 Data Format Options ea raren ear eas ee reaa aTa rataa EAEra EPEE A TEN i 58 Ermat Order ahna tienes arsaa a EA aE
225. ser opens the user layer file BI Query uses the path to find the administrator layer BI Query then combines all the elements stored in the user layer file with the elements stored in the administrator layer file The following table shows the distribution of components in a split data model Layer Component Administrator Layer User Layer Data model file datamodel gqa e design windows e data objects e relationships e calculated attributes e buttons ornaments e drawing objects e permissions e default preferences datamodel gqu e design windows e calculated attributes e buttons ornaments e drawing objects e preferences Queries folder e query files prompts file e variables e standard report specifications e query files prompts file e variables e standard report specifications Reports folder e BI Query Reports files e BI Query Reports files Data Values folder e data values query files e data values results files e data sets e data values query files e data values results files e data sets BI Query Queries User s Guide 10 The first time a user opens a split data model the user sees only the data model stored in the administrator layer When the user layer is stored in a folder separate from the administrator layer BI Query creates empty Queries DataVals and Reports folders to store user defined queries prompts variables data val
226. sponding attribute 105 BI Query Queries User s Guide 106 4 Submit the query 5 To enter a value for the first prompt do one of the following e Inthe group prompt dialog box type a value into the first box e If data values haven t been disabled for the attribute click the Data Values button and select a value from the list 6 Click the Data Values button for the second prompt From the pop up menu that opens click Data Values 7 Inthe Data Values dialog box select the value s you want Depending on the operator you applied to the qualification you can select one or more values 8 Click Insert 9 Inthe group prompt dialog box click OK Importing Prompts To use prompts created by someone else import them from that person s Prompts file stored in that person s Queries folder Use the Import Prompts dialog box Import Prompts x Available prompts Current prompts IStaff gt 1Staff Category 1 EndDate Region 2 LastName Region_N amp Category stores 1country Region stores city Region _NA Year Store Year Please Enter the Begin Date MM DD YY Chapter 6 Using Prompts To import prompts 1 On the Edit menu click Prompts 2 In the Prompts dialog box click Import 3 In the Select Import File dialog box select the file that contains the prompts you want to import then click Open The Import Prompts dialog box opens gt 4 Select the prompts you want under Available
227. sresseessressrerressessresreesee 23 Chapter 3 Building Queries 25 Query Basiese ee e e T R A A h dei Aes s 27 Building Queries oorsee noen ii ie aiad iiia 28 Submitting and Clearing Queries esesssesesssseseserseersessrerseessressresressressees 29 Cancelling Queries sidene oae giie a e ea a E a oes 30 Reordering Columns Before Submitting a Query essssssesesererererreseresees 31 SOFtNG ROWS ara a a a eaa eea a aaas 31 BI Query Queries User s Guide vi Viewing Query Specifications 0 eeseessecesceeesceceseeceseeseeeesseeesaeeesaeeeeaees 33 Savin Queries rrodhi seenen eE OOS Se a dae eVe AS ard ESRR 33 Opening Saved Queries eciscccveiesatiavesssdeg ctusneeeledenahiededepipeeseaeetbynscoeanaeede 35 Qualifying Queries os sek ease ara aaa aaao eea Sao eejokgvcvsstbeipeeadvatsbetivegess 36 Using the Qualification Tree ssssesesesesesersesseerssersesressresstesressresseeseeesresse 36 Adding Modifying or Deleting Qualifications sesessssereerserserrerrerreesees 38 Qualifying with Available Data Values sesssesesessseesesersressesssrssrersressessees 39 Applying Multiple Qualifications with And Or sesesessesserserseeseeserrresreerese 41 Combining Qualifications esesesesssseseessessresssssessresstersrssresseessessseesresseesees 42 Using Negative Qualifications ssessssessesesesessesssersressressesssessresseessresereeresee 42 Using Query Modifiers sisese arasan noen e eR 43 Qualifying One Attribute with Another esssesesess
228. ss ches A ANAS 36 qualifying other attributes 44 SCLCCUITG A E E 28 automatic variables eee 113 Automatically Order Outer Joins 202 PELMUSSION sieeve een oaa aici 203 Preterenice airaa 203 AVERAGE aggregate function 180 AVERAGE DISTINCT aggregate function 181 B sak files ss os saniaton asia od aiavietos 53 batch conversion standard reports to BI Query Reports 0 174 Begins with operator s es 46 match any match all eeeeeeteeees 48 BETWEEN operator neseser 45 48 BI Query OVELVIGWie sh irinin inaani in einsi 3 administratores n Ae 3 disconnect from DBMG eee 23 opening a data model 18 REP ORS ronn ea EA 8 Sa Ea TAE ATTAT 18 258 BLOU Adrie na eee hate 3 Admine maot aa a BI Query Reports cece n seeeenees BI Query Update oo BI Query User ew ceeeeeeseeeeeee BU SOP VCE ennaii ENVITONMENE eee OF ohinn ru uiii Repository eee system permissions terminol opererer BI Server permissions Publish Queries to Admin Layer 239 Publish Queries to User Layer 239 Schedule Queries ccccccccceseeeeseees 250 BI Server security oo eeteeete teens 243 access inheritance rules eee 244 ACCESS symbols 246 SECULING QUETICS 0 cece eseeeeteeeeteteees 248 setting by item eee 246 setting by user OF group s es 246 Users and groups s s s 244 BIGTOUPID 8 5 vases eeeiteineii oioi 113 using in a query qualifier 121 JILO d i D E EE E 113
229. ss to the specification click Set Security then use the Set Security dialog box to grant or deny access to the corresponding query The Set Security button is available only if Publish Query Also is selected 6 Click Publish Published standard reports appear in the BI Web Portfolio in a special subfolder for each data model When a BI Web user opens a standard report in the Portfolio the report is automatically refreshed Chapter 12 Working with BI Server For information on retrieving and deleting published data values files see the Help for your Bl Query application Publishing Data Values Results Files When you publish a data values results file you can make its data values available in qualifications and prompts for a particular attribute To do so to name the file using the following format data_object attribute where attribute is the name of the attribute and data_object is the name of the data object that stores that attribute To make the data values available only for a specific prompt name the file after the prompt Attribute prompt and data object names are case sensitive To publish a data values results file 1 Create a data values results file or open an existing one 2 On the Results menu click Publish Data Values The Publish Data Values Results dialog box opens 3 Inthe Data Values Results Name box type the name of the file BI Query automatically adds the extension qrd to the file when
230. ssascevevetessedadetenteeate 53 default data value oe eeeeeeteeeeeeeees 79 etn ON EE Sethe tess oea oe oat 7 EXPOTUING 222 4550 an e a ee 33 219 exporting ESOL ensaioa 230 loading and modifying ee 35 modifying for selected results 127 multiple combining eee 140 Nested sia ie iie etait wale danas 190 Outer JOINS 1D oes eee 201 206 Gualifyingsisst skit tas chat taes 36 38 qualifying with variables 0 0 00 119 refreshing negano tann st state ees 52 removing unnecessary joins 51 retrieving and loading eee 35 TOUSUIG aan on n e a ES 35 PUDDING Aee E eS 29 aTa AEAT AAT 27 33 SAVIN TESUIES asesas nanan 226 SUDIMIUING AEE EE E T 29 too IONS iana 30 using legacy joins as 18 s s s 53 Viewing specifications ss 33 query data objects containing prompts 91 file location wo ee multiple data objects results creating tables from 196 VIEWING COntents cee erect eens 33 viewing specifications u s 33 Query Admin administrator oe eee e eee 3 query modifiers Count Alls wn en esanasennenna 43 DISCE aetatis ra e 43 Trim Relationships 0 0 0 0 ee 43 query results searching 125 querying multiple data objects 50 R range of results MALTO WANG eneen range of values finding real database name using to qualify attributes recent data model record defined cwewewinchowshekeowseee 4 reducing network load eee 196 refreshing QUe
231. sted you can create it 5 Click Insert Eee wj 6 If your query is complete click the Run Query button on the Application toolbar or save it for later submission 102 Chapter 6 Using Prompts For more information see Naming Conventions for Prompts on page 94 Note Curly braces around a prompt value let the database receive formats that BI Query doesn t support If you place curly braces around the prompt name in the qualification tree then when the query runs and the user enters values as prompted each value is enclosed in curly braces making it unnecessary for the user to remember to add them Creating a Group Prompt To make a group prompt you create two or more prompts and name them with the group prompt naming convention To create a group prompt 1 Create the prompts you want You will require at least two Name each using the group prompt naming convention for example stores city stores country The prompts are listed in alphanumeric order in the prompt dialog box stores city before stores country but stores 1country before stores city so name them according to the order you want them to have To specify a title for the group prompt dialog box create a Title prompt with the name group_name Title where group_name is the name of the group from step 1 and Title is the literal string as written case is important Type the title in the Prompt String box Note I
232. supports For information about operators your DBMS supports see the documentation for your DBMS To apply your own operator 1 Inan attribute window select Custom from the Operator list in the qualification tree 2 In the Custom Operator dialog box type an operator into the Operator box 3 Click OK Querying Multiple Data Objects 50 You can create a query involving more than one data object as long as a relationship exists between the data objects BI Query depicts relationships as lines drawn between data objects in a design window If there is no relationship between two data objects you can create a dynamic relationship between them For more information see Creating Dynamic Relationships on page 197 Chapter 3 Building Queries The more objects that are used in a query the longer the query will take To query multiple data objects 1 Double click each object in turn and select the attributes that you want If the objects are directly joined BI Query automatically highlights the relationship between them If there are any other objects between the two you want to access select the attributes from the first object click the lines linking it to the second data object then select the attributes from the second object This allows BI Query to make the necessary adjustments to your query As you build your query the qualification tree at the bottom of the attribute window displays all qualifications
233. t to publish a query as a data values query file for a particular attribute name the file using the following format data_object attribute where e attribute is the name of the attribute e data objectis the name of the data object that stores that attribute 239 BI Query Queries User s Guide To make the data values available only for a specific prompt name the file after the prompt Attribute prompt and data object names are case sensitive To publish a query to the Repository 1 Ina saved and published data model create or open a query 2 On the Query menu click Publish The Publish Query dialog box opens Published Queries Query name 4 PerformanceComparison_EU ProductList 4 PerformanceComparison_NA01 8 PerformanceComparison_NA02 f Product Correlation 8 Product PerformanceallCountries f Product_Year B ProductList ProductList_EU_Standard f ProductList_NA amp f ProductList_NA_Standard 8 ProductMargin 8 RegionalPerformanceO1 Revenue_Category Revenue_Store i 4 Revenue_Store_Standard SEE 8 SalesCihe st gt Delete I Data values queries 3 If you want to publish the query as a data values query file select Data Values Queries 4 Inthe Query Name box type the file name for the query 5 To set security for the query click Set Security then use the Set Security dialog box to grant or deny access to the query For more information see Setting Security for Queries
234. table gives us all the information about a customer specific name specific address and so on Rows are also known as records DBMSs The collection of programs that manage a database constitute a database management system DBMS A DBMS lets users examine and manipulate data in real world terms customers orders products without needing to know how the computer actually stores the information Data Model Components A data model is a graphical representation of the data in a database Data object Periods Sales Detail Product Relationship FA A data model includes data IN objects and the relationships p Staff Store Regional Office between them Chapter 1 Introducing BI Query When you use BI Query to get information from a database you work with a data model Depending on the BI Query application you are using and the permissions assigned to you you may be able to customize data models to suit your needs Administrators who use BI Query Admin to design corporate data models set permissions and preferences for each data model Users can change the preference settings but not the permissions Data models consist of the following components Data Objects Data objects are rectangular or graphical icons that represent the tables stored in the database Each data object contains one or more attributes Attributes An attribute represents a column of data in a database table When you double click a data o
235. tal L grand total Y Y paseepetssaases peeeee p AP i t i Border below I Primary subtotal Tl Secondary subtotal To add borders to a standard report 1 Open the Report Borders for Standard Reports dialog box by doing one of the following e On the Tools menu click Preferences In the Preferences dialog box click Report Borders e Create the report On the Report menu click Borders 168 Chapter 9 Creating Standard Reports Note If you open the Report Borders for Standard Reports dialog box for a particular standard report the border format you specify applies only to that report If you open the dialog box from the Preferences dialog box the border format applies to all standard reports that you subsequently create 2 From the Report Component list select the component you want to format 3 From the Style drop down list select the line style for the border 4 To specify a color for the border click the Color button and select the color from the palette that opens To specify a custom color click Custom Color in the palette 5 Repeat steps 2 4 for all other report components that you want to format 6 To adda border below any primary and secondary subtotals select the Primary Subtotal and Secondary Subtotal check boxes 7 Click OK Using Ornaments in Standard Reports For more information on ornaments see the B Query Data Models User s Guide Some default report elements are text o
236. ter submitting a query you may realize that you need to make some changes or you may decide that it is taking too long to run You can cancel a query only after rows have been returned Not all databases support this function To cancel a query do one of the following e On the Query menu click Cancel e On the Application toolbar click the Cancel Query button Chapter 3 Building Queries For more information about reordering columns in BI Query Reports see BI Query Reports Help Reordering Columns Before Submitting a Query Columns appear in results windows and reports in the order in which you select attributes while creating the query You can change that order before submitting the query after you have results or in a report To specify the column order before submitting the query 1 On the Query menu click Reorder Attributes The Reorder Query Attribute dialog box opens The top to bottom order of attributes in the Attributes list corresponds to the left to right order of columns in the results window or report Reorder Query Attributes x Attributes Up Down AVG Sales Order Amount Sales Year Top Bottom Cancel Help From the Attributes list select the attribute whose position in the list you want to change Use the Up Down Top or Bottom buttons to move the selected attribute to the desired position 3 Click OK Sorting Rows When you submit a query BI Query displays
237. that contain data for sales greater than 10 000 you could qualify the Sales attribute using the following expression Sales gt 10000 You qualify an attribute by clicking its Qualify box in the attribute window then specifying the qualification in the qualification tree Using the Qualification Tree The qualification tree appears at the bottom of the attribute window when you click the Qualify box for an attribute Condition box Qualification box E coun a Data Values button Operator list List button Attribute Name box Chapter 3 Building Queries If your database is case sensitive and the Case sensitive preference is set when you type a qualification the characters must be in the case in which they are stored in the database If this preference isn t set case isn t important Attribute Name Box Displays the name of the qualified attribute Condition Box Lets you combine multiple qualifications You can also use the Condition box to reverse the effect of a particular qualification To remove a qualification from a query click the Condition box then hit the Delete key Operator List Lets you specify the type of restriction you want For example you can apply the Greater Than gt operator to find all rows in which the specified attribute has a value greater than the specified comparison value Qualification Box Lets you specify the comparison value for the attribute For example
238. the Computed String Option on page 66 Click OK BI Query formats the data for the target according to the options you have specified Note You can use more than one format option but BI Query applies them according to its format order rules For more information see Format Order on page 59 Opening the Format Dialog Box The procedure for opening the Format dialog box depends on the target you want to format To format an attribute Open the attribute window for the data object that stores the attribute BI Query must not be in Design Mode Click the question mark button beside the attribute name The Attribute dialog box opens Click Edit The Format dialog box opens 61 BI Query Queries User s Guide 62 Format Ea Format type Predefined formats Prefix String z Null values Suffix Computed string Scale Precision I Exponent Hide zero values I Negatives in red Separators 9 999 99 z Sample M Default Negatives 9999 00 99 999 Cancel Help The Format dialog box for Integer data types To format a column in a results set 1 Click the column you want to format 2 On the Results menu click Format The Format dialog box opens To format a column in a standard report 1 Inthe standard report click the column you want to format 2 On the Report menu click Format The Format dialog box opens To format a data model default 1 On the Tools menu click Preferences
239. the Data Values dialog box Use this dialog box to select the value or values that you want to insert into the qualification then click Insert Note You can view the data values for any attribute and insert them into the qualification for any attribute using the Choose Data Values dialog box on the Edit menu click Data Values Applying Multiple Qualifications with And Or When you qualify more than one attribute or when you qualify the same attribute more than once BI Query assumes that the query must satisfy both qualifications This is reflected in the qualification tree where the word and appears between two qualifications You can change and to or EI and E County Canada Lee Kingston To specify that the query must satisfy either or both of the qualifications Click the word and It changes to or or C4 1 oe 41 BI Query Queries User s Guide 42 Combining Qualifications You can avoid ambiguities in a query by combining two or more qualifications so that they take precedence over those that are not combined To combine qualifications 1 In the qualification tree select the qualifications to be combined by holding down the Shift key and clicking the corresponding condition boxes and or no 2 On the Query menu click Qualification then click Combine 3 To change the condition in the new branch from and to or click the word and LA or To separate combined qua
240. the Query for the Bottom Branch drop down list select the query whose results you want to append to the results of the first query 4 Click OK In the Super Query window an append operation appears in the tree list above the component queries About Column Joins You can join columns from different results sets to produce a new results set The new results set can contain data from more than one data object including actual relationships and query data objects from more than one design window Typically the BI Query administrator specifies the join operations between data objects when the model is created However you can produce the same results by joining columns from different results sets In effect you are creating a relationship between results sets that is equivalent to a relationship between joined data objects By convention a column join has a left results set and a right results set You specify which column in the left set joins to which column in the right You can join more than one pair of columns The combined results set represents each pair of joined columns in a single column Reasons for Joining Columns You may want to join columns of results sets for the following reasons e the design window doesn t provide joins for the attributes you need to join e the data model doesn t include permission to create relationships between tables 143 BI Query Queries User s Guide For more information
241. the data set file that you want to apply to the attribute Click Open The qualification tree displays the name of the data set file in the form Data set dsname where dsname is the name of the file County Data set NewCountres csv When the query is submitted BI Query inserts the contents of the data set file into the qualification Viewing Data Sets Once you have inserted a data set into a query you can view the values it contains and the number of values in the set Chapter 10 Building Advanced Queries For more information on scheduling a query see Using BI Server Scheduler on page 249 For information on editing data values results files see Creating Data Values Results Files Using a Text Editor on page 83 To view the contents of a data set 1 In the qualification tree click the List button beside the attribute that is qualified by the data set 2 On the menu that opens click View Data Set If you have applied more than one data set to the attribute the Data Set dialog box opens in this case select the data set file from the list and click OK The View Data Set dialog box opens The top of the dialog box displays the name of the selected data set and the number of values it contains The list in the center of the box displays the actual values 3 Click OK to close the dialog box when you have finished viewing the values Using a Data Values Results File as a Data Set You
242. the first join are not part of the second set of join results In other words the final result does not preserve all rows from table A If the join between B and C has order 1 a similar problem occurs not all rows from B are preserved BI Query represents join conflicts using dashed connection lines oA B Y 1 2 E a eC 205 BI Query Queries User s Guide For more information on outer join syntax types see the B Query Data Models User s Guide 206 About Outer Join Syntax in Queries BI Query supports the Classic ANSI SQL 89 outer join syntax that uses the legacy operators and It also supports the new ODBC and ANSI SQL 92 types of outer join syntax The Classic and new types of syntax cannot appear in the same design window The particular outer join syntax in use in a design window or across the data model depends on the connection assigned to that window or model If you want to use a particular type of outer join syntax in a query you do not have to edit the existing joins instead you can change the syntax for the connection In this case BI Query applies the specified syntax to all outer joins that use the connection Note Because outer join syntax information is specific to a given connection it is stored in a connection file When you change the syntax for a connection it applies to all design windows and data models that use the corresponding connection file If you want you
243. tion Description Header Contains the title and date and appears at the top of each page of the report You can enlarge it change its appearance and content and add ornaments to it Column headings Correspond to the headings in the results set You can change their appearance content width and depth You can also add ornaments to them Results Contain the actual results of the query You can add subtotals grand totals and ornaments to them You can reorder them replace their values and change their widths appearance and data formats Footer Contains the page number and appears at the bottom of each page of the report You can enlarge it change its appearance and content and add ornaments to it Report Query Results 1 AustralAir Banco Real Chemex Click to select entire section Query Results 1 1 City Sydney Sydney Toledo Toledo Toledo Toledo Mexico City Mexico City Mexico City Header Date 084093 11 21 93 11 23 93 11 05 93 01 06 94 05 0193 0419 93 1244 93 09 27 93 Default date ornament aan coun S h in 60 000 00 eadings 6 500 00 25 400 00 Query 15 698 24 results 45 800 00 15 000 00 11 958 00 20 548 16 65 300 00 ick to select specific section Chapter 9 Creating Standard Reports For more information on formatting standard reports see Formatting Data The Basics on page 60 When you cli
244. tion on creating connection files see the B Query Data Models User s Guide In order to query a database BI Query must connect to the corresponding database management system DBMS running on the server To make the connection to a DBMS BI Query uses a connection file Each connection file stores the information about the DBMS and the middleware that is necessary to make the connection Typically the BI Query administrator includes at least one connection file in the data model the default the administrator may configure the model so that it automatically connects to the database each time you open the model or submit a query Even if the model has not been configured to connect automatically BI Query attempts to connect if you submit a query and a connection file is available 21 BI Query Queries User s Guide 22 You can connect a data model to the corresponding DBMS by using a default connection file or by specifying a different one To specify a particular connection file use the Connections dialog box Available connections Browse RetailGolfClassic con RetailGolfTest con Delete Default To create a new connection choose a DBMS and a connection method then click New DBMS onec x Connection method onec New Close Help You need to specify a connection file if the data model does not have a default You can also create your own connection files To connect using the
245. tionality of BI Query With permission from the DBMS administrator the BI Query administrator can also update the tables in the database BI Query User Lets users run queries provided by others and depending on their user permissions create ad hoc queries of their own BI Query Update Provides users with the same functionality as BI Query User with the additional ability depending on their DBMS permissions to update tables in the database BI Query Queries User s Guide Basic Concepts The following sections describe the basic concepts behind BI Query s graphical approach to extracting information from corporate databases Database Components A database is a collection of related information The basic components of a database are as follows Tables Ina relational database information is held in tables A table usually relates to something in the real world For example a database might store customer names and addresses in one table products in another stock levels in another purchase orders in another and so on Columns Tables are made up of columns and rows Each column represents an attribute of the object that the table represents For example customers have names addresses fax numbers and so on A table for customer information would have a column for each of these attributes Columns are also known as attributes Rows Fach row ina table is an instance of the object that is each row in the customer
246. tors s s s 192 OVETVIEW sist auoe DE neser ie 190 SAVIN Ee aeeie ae NNE Era e 33 substituting common language names 87 88 subtotals adding to standard reports 158 SUPMXES SOL cies nna En S a EEA NS 209 SUM aggregate function sesser 181 SUM DISTINCT aggregate function 181 SUPET QUETIES osciiocniossiissesrseessipsiaresiresst i 129 OVELVICW iiaia iira raai rs t 129 cannot export SQL eects 230 combining multiple results sets 129 COTTECHINGELPOUS cdi seedisctisd ans 135 editing a deat tetas tach hata 132 editing component queries 133 filtering results sets eee 129 from filtering results ee 136 modifying component queries 132 super query windows HESEUTG ses lhe tetas ee detects 270 super query windows OPENIN ia ana En 132 OVEIVIEW oaro nan ine an 129 T tables creating in the database 196 data asensin a aaia 4 Jonne haste AEREE 197 198 Technical Support ensino 254 Teradata DBMS prompt data types 95 terminology aruinn aiaa a 5 testing conditional prompts 0 0 cee 105 group prompts cceceseeseeseeseeteeees 103 s per QUCTIES ses crac crscestersnensserseesees 135 text matching in queries cee 46 ornaments special strings in 170 text files exporting SQL to 230 Text in Quotes results Option eee 220 text ornaments in standard reports cette 169 text strings Kunanon d unanond
247. tribute with a subquery you cannot include any other qualifiers on that attribute such as other subqueries data values results file data sets prompts and so on If you use a subquery to qualify an attribute that is already qualified with other data values those values are removed from the qualification You cannot delete a subquery from a qualification without removing the entire qualification Example To retrieve information about retailers with the highest credit limit you could build and save a query that includes the Receivable attribute with the MAXIMUM function applied to it Store x Info Attribute i l Chapter 10 Building Advanced Queries For more information on subqueries see the Help for your BI Query application When you build other queries you can insert that query as a subquery into a qualification to obtain results based on the maximum receivable amount When you submit such a query BI Query submits the subquery first then uses the results to qualify the query In this way the query is always qualified with the current maximum value in the database Creating and Inserting Subqueries To qualify a query with a subquery you need to create the subquery first then insert it into the qualification tree To create a subquery 1 Inthe attribute window click an attribute then qualify it and apply any other necessary restrictions or modifiers Note Do not sort the attribute Subque
248. ts appear on a line by themselves a given comment cannot extend beyond one line Remember to store the data set file in the DataVals folder for the data model When you publish data values files to the BI Server Repository the data set files are also published For more information on publishing data values files and data sets see the BI Query Data Models User s Guide Example Data Set File The following example of a data set file includes two comments and uses carriage returns to delimit data values lt N American sales centers gt Albany Peterborough Winnipeg lt European sales centers gt Bristol Paris Verona 193 BI Query Queries User s Guide 194 Using a Data Set to Qualify an Attribute When you qualify an attribute with a data set BI Query applies all of the values in the data set to the qualification You can insert more than one data set in the qualification for a given attribute in this case all of the values in all of the files qualify the attribute You can also combine data sets with other qualifiers such as variables prompts and data values To qualify an attribute with a data set 1 Click the Qualify box for the attribute 2 Inthe qualification tree click the Data Values button beside the attribute On the menu that opens click Data Sets CI Lex EJ Data Values Prompts Variables Subqueries 3 In the Open Data Set dialog box select
249. tton and select Data Values BI Query runs the default data values query and retrieves all the values The administrator can disable access to the default data values query Disabling it is recommended when loading all the values in the database uses network resources inefficiently for example when an attribute has a large number of values or when those values are simply numbers and dates Data values files can also be inserted into conditional prompts for use in executive information system buttons They can also be used to set up data aliasing making it possible for instance for users to select items by meaningful names instead of by numerical codes 79 BI Query Queries User s Guide For more information see Publishing Data Values Results Files on page 243 For more information see Naming Conventions for Prompts on page 94 80 Types of Data Values Files There are two types of data values files Data Values Query Files Query the database and retrieve a subset of values to create the list Data values query files provide the most accurate lists the first time you use them in a session they query the database to supply the values Because they can return different results depending on when they are run they are sometimes called dynamic data values files Data Values Results Files Display a stored set of values Data values results files are produced by storing the results of a query or sometimes are
250. u click Column Settings The Column Settings dialog box opens Column Settings Eg Columns Column heading Order Amount Country Year Column width characters 3 23 J Special column for right border setting I Wrap column IV Band column C Before After Fit all columns on page 2 From the Columns list select the column you want to band 3 Select Band Column then select Before or After to display the column above or below the other columns 4 ClickOK Stacking Columns Instead of displaying columns horizontally across a page you can stack them into a single vertical column with each item appearing on a separate line the columns that you don t stack appear in their normal position The stacked column is as wide as the widest column in the stack Only the heading of the left most column in the stack is used A standard report can contain only one stacked column You can add columns to a stacked column by un stacking the column then re stacking You can include both banded and stacked columns in a report Stacked columns appear to the left of banded columns The stacked column displays the heading of the first column you select 165 BI Query Queries User s Guide 166 Sales Report Sales Report Country City Country Canada Ottawa Canada France Paris Ottawa USA Chicago Australia Sydney France Paris USA Chicago Australia Sydney Original Stacked To stack c
251. ubmitting a query using the BETWEEN and NOT BETWEEN operators in the qualification tree For more information see Finding a Range of Values on page 48 Combining Results Sets For information on In order to enlarge the scope of your data you can combine results from troubleshooting combined multiple queries into one results set You can do the following operations results see the Help for your Bl Query application append the rows of one results set to another e join one or more columns of one results set to another The results sets can be from queries submitted in different design windows within the model Because different windows can connect to different databases you can combine information from two different databases You can combine the results in two ways e Before you run the corresponding queries In this case you use the Super Query window to combine the results of the two queries e After you run the queries In this case you specify the results sets you want to combine directly 140 Chapter 8 Working with Results For more information on buttons see the B Query Data Models User s Guide If you save the query after combining results you can open and edit it in the Super Query window However you cannot save the corresponding SQL string as a text file You can also link the combined query to a button to perform the combine operations automatically About Appended Columns When you ha
252. ue Sept 26 not Sept 26 2001 The hidden year is lost and the date value in the SQL 09 26 becomes invalid Setting the Data Entry Format By default BI Query takes the data entry format for an attribute from the data format for that attribute However you can set the data entry format to the data model default for the corresponding data type To set the data entry format 1 2 On the Tools menu click Preferences The Preferences dialog box opens In the Qualifications area do one of the following e To use attribute formats for data entry select Attribute To use the data model formats for data entry select System Default Click OK Entering Date and Numeric Data When entering data you can type numbers including currencies without formatting them BI Query converts them to the format you ve specified for the corresponding attribute ao FI Elk p Credit Limit T 5 005 500 73 BI Query Queries User s Guide 74 You can also type dates using any format comparable to the one you ve specified For example if the date format you ve specified is MM DD YYYY you can type dates in the following formats e 2001 1 14 e 1 14 2001 e January 14 2001 e 2001 Jan 14 As long as you include the day month and year and BI Query is able to unambiguously interpret the format it converts the date to the specified format LJ m Date E us 0 Ee Date
253. ue for each set All selected attributes to which functions haven t been applied are grouped to ensure that the results set doesn t calculate a summary value for individual members of the group Example A database contains the following data about retailer sales Name City Invoice Amount Sterling Ottawa 3000 Triteck Toronto 1000 Billings Toronto 3000 Sterling Ottawa 1000 Billings Toronto 4000 Saddler Toronto 5000 Triteck Ottawa 2000 Triteck Toronto 3000 If you want to retrieve a list of retailers cities and total retailer sales you could form a query in which you select the Name City and Invoice Amount attributes in that order and apply the SUM function to Invoice Amount 179 BI Query Queries User s Guide When you submit the query the DBMS organizes the data into groups first by Name then by City Each row of results in a group has identical values except the attribute to which the function is applied The table below shows how the DBMS groups the data The rows for Billings Toronto represent one group the row for Saddler Toronto represents another group and so on Name City Invoice Amount Billings Toronto 3000 Billings Toronto 4000 Saddler Toronto 5000 Sterling Ottawa 1000 Sterling Ottawa 3000 Triteck Ottawa 2000 Triteck Toronto 1000 Triteck Toronto 3000 The DBMS then applies the S
254. uery substitutes the variable for the qualification of the Store Name attribute In other words the original qualification Store State Current Ratio becomes Store Receivable gt Store Payable Using Placeholders in Variables As an alternative to actually specifying attribute and operator information in a variable you can create a variable that uses placeholders for the attribute and operator The placeholders let you create variables that return results based on the particular attribute qualified and the operator used in the qualification There are two placeholders for variables AAttribute The placeholder for the qualified attribute When you submit a query containing the Attribute placeholder BI Query replaces the placeholder with the qualified attribute this attribute becomes part of the WHERE clause for the query AOperator The placeholder for the operator in the qualification When you submit a query containing the Operator placeholder BI Query replaces the placeholder with the operator from the qualification this operator becomes part of the WHERE clause for the query 115 BI Query Queries User s Guide 116 Use the Attribute and Operator buttons in the Edit Variable dialog box to insert placeholders in the expression for a variable Note The predefined variables DBUserID BlUserlD and BlGrouplD use placeholders for attributes and operators This means
255. ues files data sets and reports BI Query saves any other customizations the user adds such as extra buttons ornaments and design windows in the user layer file The next time the user reopens the data model the user sees the administrator s data model as well as all the customizations he or she has added The changes made by one user don t affect those made by other users Users can t make changes to the administrator layer A Welcome Administrator layer Ji E A User layer Regional Offices Managers Periods Products Welcome Information Sales a Regional Offices Managers Periods Products Welcome Information Mapping Report Sales Reports is i CEZ dakomara Staff Retailers Partners What the user sees Combined Data Models A combined data model consists of one data model file with extension gql and the associated Queries DataVals and Reports folders Each time the administrator revises a combined data model and distributes it to users any user customizations are overwritten Chapter 1 Introducing BI Query Typically combined data models are created for organizations that don t allow users to make any changes to the data model The following table lists the components of a combined data mode
256. ues queries 85 BI Query Queries User s Guide 5 Select Data Values Queries 6 Inthe Query Name box type the file name you want A file name with the extension qry is created by BI Query Use the following file naming convention format to make the data values available in qualifications and prompts data_object attribute where e data object is the display name of the data object that stores the attribute e attributeis the display name of the attribute to which you want to apply the file Case is important make sure that you match the case of the data object as it appears in the title bar of the attribute window and the case of the attribute as it appears in the attribute list in the attribute window 7 Click Save Using a Data Values File in a Query You can use a data values file to qualify an attribute in a query To use a data values file in a query 1 Create a query that involves the attribute the data values file is based on 2 To access the data values list click the attribute s Qualify box Then click the Data Values button in the qualification tree and select Data Values from the pop up menu If you created a data values results file BI Query loads it and displays the values as a list If you created a data values query file BI Query submits it then displays the values in the Data Values dialog box You can also access the data values list by clicking the Data Values button in the prompt window and sel
257. vailable only in design windows that use either the ODBC or ANSI SQL 92 join syntax and only if the design window doesn t contain a legacy outer join operator Click the down arrow button Click OK To save a relationship and name it on the Query menu click New In the BI Query dialog box click Save In the Save Dynamic Relationship dialog box specify a relationship name and click Save The name should reflect how the two data objects are related and should be unique Save Dynamic Relationship x Save the relationship between Store and Staff Relationship name managed by f Don t Save Help Note If you have created more than one relationship repeat for each To remove a relationship click Don t Save in the Save Dynamic Relationships dialog box When you save the data model the relationship s are saved with it Chapter 10 Building Advanced Queries Deleting a User Created Relationship Users can delete relationships they create only if they have not already saved them To delete a relationship 1 Do one of the following e Open another query e On the Query menu click New The BI Query dialog box displays the message Save dynamic relationships before deleting current query 2 Inthe BI Query dialog box do one of the following e Click Delete to delete all new relationships e Click Save In the Save Dynamic Relationships dialog box click Don t Save for each relationshi
258. ve similar sets of results for instance identical kinds of data from two different years that you want to present together you can append the rows of one results set to another If the two sets have different column names BI Query copies the name s from the first set to the combined set Rules for Appending Rows Adhere to the following rules when you append the rows of one results sets to the rows of another e The data types of the columns in the first results set must match the data types of the corresponding columns in the second results set For example if the first results set contains two columns and their data types are character and numeric respectively the first two columns of the second results set must also be character and numeric The order of columns in one results set must match the order of columns in the other One set may have additional columns that the other does not have as long as these extra columns are at the end on the right Example In the following example the rows from Query Results 2 have been appended to Query Results 1 to form a new results set Query Results 1 Query Results 2 Name Sales Names Sales State Abicon Inc 12 000 Bainstream Inc 10 000 New York Textal Ltd 113 000 Knatl Corp 2 000 California Unisystems 10 145 MonoLitho 2 100 Ohio Ziptech 6 430 Textronics 60 000 Illinois 141 BI Query Queries User s Gui
259. very query and subquery that you submit For example if you re using a DB2 DBMS on a VAX machine you can improve database performance by appending the suffix with ur to every query to allow uncommitted reads If you re using Sybase you can use the suffix for browse You can also specify whether the suffix occurs before or after the semicolon that terminates the SQL for queries This feature requires the Edit Queries permission To specify a prefix and suffix 1 In the Preferences dialog box click Query Options SQL prefix SQL suffix l with ur IV Place suffix before semi colon 209 BI Query Queries User s Guide 2 Inthe Query Options dialog box specify a prefix and or a suffix 3 To place the suffix after the semicolon that terminates the SQL clear Place Suffix Before Semi Colon 4 ClickOK Building Circular Queries 210 When the data model you re using contains two or more data objects whose relationships form a circular path you can build queries that use all the data objects and all the relationships in that path For example if your data model contains the Employees and Departments data objects and they re connected by the Work In and Managed By relationships you can build queries that use both data objects and both relationships Because a data object in a circular path may be used in two roles BI Query prompts you to specify whether or not to create a correlation name or alias for t
260. w fn dayofmonth fn now This variable determines the date for the beginning of the month You can use this variable to find all retailer sales received since that date Prompts Use a prompt in a variable if you want to qualify the query but do not want to restrict the comparison to a specific value For example in the following expression fn week Attribute Operator weekofyear 1week AND fn year Attribute Operator weekofyear 2year the variable includes the weekofyear group prompt This variable returns results based on the week and year that you specify Other Variables Use other variables in a variable as the building blocks for a more complex expression For example the following expression Attribute Operator Today 1 includes the Today variable which determines today s date The resulting expression returns yesterday s date Creating and Using Variables For more information see Creating Calculated Attributes on page 186 Before you create the expression for a variable you can test that it returns the results you want by creating a calculated attribute using the same expression Use the attribute in a query then verify the results If the expression is correct you can use the Edit Variable dialog box to create the variable 117 BI Query Queries User s Guide You can t edit any variables with lock icons beside them 118 To create or edit a
261. w below which you want to insert the new one then click Insert Row To delete a row you ve specified place the cursor in the row then click Delete Row Formatting Specific Data Types For more information on setting the default formats see Specifying Data Model Default Formats on page 63 68 Attributes and result report columns have specific data types However for formatting purposes BI Query collects related data types into general data categories When you format an attribute or a result report column the available formatting options depend on the category for the corresponding data type Similarly you specify the default formats for the data model in terms of categories and not separate data types The following sections describe the formatting options available for each category Formatting Boolean Data The boolean or bit data category stores one of two values The names applied to the two values depend on the context yes or no true or false 1 or 0 and so on You can set these names as part of the format Chapter 4 Formatting Data To format boolean data 1 Open the Format dialog box for the target you want to format For more information see Opening the Format Dialog Box on page 61 Do one of the following From the Predefined Formats list select the format you want to apply e In the True and False boxes type the formats for the corresponding boolean values Apply any other
262. with the same subset of results later save the new query Chapter 8 Working with Results Note e SQL INclauses are created in the current query The query will be resubmitted to retrieve results matching only the items selected e To modify the current query to retrieve additional columns of results for these items display the attribute window s for the appropriate items make the changes you want then submit the query To cancel Ad Hoc Drill Down Mode In the results window on the Results menu click Ad Hoc Drill Down Using Super Queries For more information about editing super queries see Guidelines for Editing Super Queries on page 132 For more information on combining results see Combining Results Sets on page 140 A super query is a query that either returns a results set and then filters it or returns multiple results sets and combines them You can save open and edit these queries in BI Query However you can t use them in data values query files and you can t save their SQL as a text file In contrast a standard query returns a single set of results that has not been manipulated in any way If you save a query before you submit it or if you save a query after you submit it but without manipulating the results you are saving it as a standard query You can save a standard query open it edit it or save it as a data values query file and you can save the underlying SQL for use in a
263. without comparing the remaining queries Chapter 3 Building Queries If you modify any queries BI Query makes the changes then saves the queries in the Queries folder BI Query also saves the original queries with the extension bak 5 Submit any modified queries to make sure they still produce the desired results Using Legacy Joins As Is When you run a query that uses legacy outer join operators in a data model that has been converted to a new outer join syntax BI Query automatically converts the query to the new syntax If this conversion does not produce the same results as the query using the old operators you can force BI Query to treat all outer joins in the data model as if they used the Classic ANSI SQL 89 syntax To use Classic syntax for legacy joins in a query 1 On the Tools menu click Preferences 2 In the Preferences dialog box select Preserve Legacy Query Outer Joins 3 Click OK Note The Preserve Legacy Query Outer Joins preference does not override the Preserve Legacy Query Outer Joins permission set by the administrator 53 Chapter 4 Formatting Data About Data Formats 57 Data Type Categories 57 Data Format Options 58 Format Order 59 Formatting Data The Basics 60 Opening the Format Dialog Box 61 Specifying Data Model Default Formats 63 Setting the Null Values Option 64 Setting the String Option 65 Setting the Computed String Option 66 Formatting Specific Data Types 68 Formatting Boolean Dat
264. writing the contents If you use the pipe symbol the first command is sent only if the application isn t already running and BI Query must start it up the second command is always sent even when the application is already running Remember to specify all arguments for each command Note The pipe symbol is not necessary if it s logical for both commands to run each time you export results Opening Macro Sheets Automatically You can extend DDE functionality by issuing two commands to obtain the effect of three For example when you open a specific Microsoft Excel worksheet and run a macro you re doing three things opening a specific worksheet opening a specific macro sheet and running a macro The two commands would take the following form open c msoffice excel test xls run test xlm macrol 231 BI Query Queries User s Guide To obtain this effect you must attach the Excel auto_open function to your worksheet so that your macro sheet opens whenever the worksheet opens To open macro sheets automatically 1 Open the worksheet 2 On the Formula menu click Define Name 3 Specify a name such as Auto_open then use the following syntax in the Refers To box test xlm open_macro You can also achieve the same effect using either of the following methods e Place the macro on the global macro sheet global x1m This sheet is opened automatically with Excel and doesn t require a command Using
265. you can directly qualify any attribute using these variables Example The variable Exceeding Credit is defined as follows Attribute Operator Retailers Credit If you qualify the attribute Receivable in the Retailers data object with this variable Retailers Receivable gt Exceeding Credit then the actual qualification becomes Retailers Receivable gt Retailers Credit If you qualify the Invoice_amount attribute of the Retailers object with the same variable Retailers Invoice_amount lt Exceeding Credit then the query returns the list of retailers who have been invoiced for amounts less than their credit limit Retailers Invoice_amount lt Retailers credit Using Functions Prompts and Other Values in Variables Typically the expression for a variable takes the form of a comparison between an attribute and a value An operator specifies the nature of the comparison for example Sales order_id 10002 The expression can also include functions prompts or other variables These additional terms often supply the comparison value for the attribute Chapter 7 Using Variables For more information on functions see Using Functions in Query Expressions on page 189 Functions Use a function in a variable to create an expression that performs a calculation For example you can include in an expression the DBMS function for the current date Attribute Operator fn no
266. you can specify outer joins in a dynamic relationship using the and operators or the new syntax check boxes in the Choose Relationship dialog box For more information on outer joins see Types of Column Joins on page 144 Basic Procedure Creating dynamic relationships requires the Dynamic Relationships permission and the Allow Dynamic Relationships preference Saving the relationships requires the Save Data Model permission You don t need permission to save a dynamic join as part of a query When the query is reopened the dynamic join is recreated temporarily for that query to use To create a dynamic relationship 1 Open the Choose Relationship dialog box by doing one of the following e Select the two data objects you want to join Shift click On the Query menu click Relationship e Control click and drag from one data object to the other 2 Inthe Choose Relationship dialog box from the Relationship drop down list select lt dynamic gt If no relationship currently exists between the selected data objects lt dynamic gt appears by default If a relationship already exists the Join Conditions box displays the relationship and the list of attributes for the selected data objects is dimmed You can create a dynamic relationship between any data objects even if they are already related Chapter 10 Building Advanced Queries Choose Relationship Eg Relationship Operator lt dynamic gt X Equal
267. you have added to the query regardless of the attribute on which they were placed w 2 When you have finished building the query click the Run Query button For more information on join conditions see the B Query Data Models User s Guide on the Application toolbar Removing Unnecessary Joins from Queries The relationship between two data objects consists of one or more join conditions that specify how the data objects are related When you submit a query that involves both objects any rows returned from the DBMS must satisfy the join conditions between the objects You can set the Trim Relationships preference to remove excess joins from the SQL for a query If a query contains a relationship to a data object that has no attributes selected the Trim Relationships preference removes the relationship when you submit the query If you want to add qualifications by selecting a relationship you must disable this preference To set the Trim Relationships preference do one of the following e On the Tools menu click Preferences In the Preferences dialog box select Trim Relationships e Open or create a particular query On the Query menu point to Modifiers then click Trim Relationships if it is not already selected 51 BI Query Queries User s Guide Updating Queries 52 After the administrator has updated a data model some of the saved queries may not work for example they may include attributes that no l

BI Query™Queries - Oregon State University

Contents

Download Pdf Manuals

Related Search

Related Contents

BI Query&trade;Queries - Oregon State University

Contents

Download Pdf Manuals

Related Search

Related Contents

BI Query™Queries - Oregon State University