complex queries in desktop intelligence.pdf

7/28/2019 Complex Queries in Desktop Intelligence.pdf

1/66

Complex Queries inDesktop Intelligence


2/66

2 Complex Queries in Desktop Intelligence

Appropriate Use And Security Of Confidential AndSensitive InformationDue to the integrated nature of the various Human Resources, Finance and Studentmodules in Banner and the reporting information in the Enterprise Data Warehouse

(EDW), you may have access to information beyond what you need to perform yourassigned duties. Your access to Banner and the EDW has been granted based onbusiness need, and it is your responsibility to ensure the information you access is usedappropriately.

Here are some reminders of good data stewardship to help you carry out yourresponsibility: Do not share your passwords or store them in an unsecured manner. Do not leave

your workstation unattended while logged on to administrative informationsystems. You are responsible for any activity that occurs using your logon id.

Do not share confidential and sensitive information with anyone, including

colleagues, unless there is a business reason. Retrieve printed reports quickly, and do not leave the reports lying around in plain

view. Secure reports containing confidential and sensitive information (e.g., FERPA, EEO

or HIPAA protected data). When disposing of reports containing confidential or sensitive information, shred

the documents in a timely manner.

Your responsibilities regarding the protection and security of administrativeinformation are outlined in the University of Illinois Information Security Policy postedat http://www.obfs.uillinois.edu/manual/central_p/sec19-5.htm. Any violation could

subject you to disciplinary action, which could include dismissal or, in those caseswhere laws have been broken, legal action. You should have signed a compliance formthat indicates you have read, understand and agree to comply with the University'sInformation Security Policy for Administrative Information. If you have not alreadysigned the compliance form, please see your Unit Security Contact, who is responsiblefor maintaining these forms.

File: i:\trainingdevelopment\businessobjects\businessobjects6 - complex queries\complex queries inbusinessobjects v3.1.docLast Printed:3/18/2008 12:58 PM


3/66

Complex Queries in Desktop Intelligence 3

Complex Queries in BusinessObjects

Table of ContentsAppropriate Use And Security Of Confidential And Sensitive Information............................................ 2

About this Course........................................................................................................................................ 5Objective............................................................................................................................................ 5Instructor Led Course ........................................................................................................................ 5Training Data ..................................................................................................................................... 5Practice.............................................................................................................................................. 5Files ................................................................................................................................................... 5Demos ............................................................................................................................................... 5

Chapter 1..................................................................................................................................................... 7Formulas and Variables ....................................................................................................................... 7

Formulas............................................................................................................................................ 7Variables .............................................................................................................................................11

Creating a Variable ...........................................................................................................................11Exercise 1 Formulas and Variables ...............................................................................................14

Chapter 2....................................................................................................................................................15Calculation Contexts ...........................................................................................................................15

Input and Output Contexts................................................................................................................15Calculation Context Operators..........................................................................................................16Calculation Context Keywords ..........................................................................................................16Calculation Context Example............................................................................................................17Viewing Calculation Contexts ...........................................................................................................19Changing the Calculation Context.....................................................................................................19Exercise 2 Calculation Contexts ....................................................................................................21

Chapter 3....................................................................................................................................................23Subqueries ..........................................................................................................................................23

Multiple Data Providers.....................................................................................................................25Verifying the Results.........................................................................................................................25Managing Data Providers .................................................................................................................27Renaming Data Providers.................................................................................................................28Purging vs Deleting a Data Provider.................................................................................................28Exercise 3 Subqueries ...................................................................................................................30

Chapter 4....................................................................................................................................................31Using Personal Data Files...................................................................................................................31

File Types Available..........................................................................................................................31Options with .XLS Files.....................................................................................................................31Creating a Report from an XLS File..................................................................................................31Options with Text Files......................................................................................................................33Create a table from a CSV personal data file....................................................................................33Create a Second Data Provider ........................................................................................................36Data Qualification in Excel (.csv and .xls) Files ................................................................................40Exercise 4 Using Personal Data Files............................................................................................43

Chapter 5....................................................................................................................................................45Combining Data from Different Data Providers ...................................................................................45

Linking Data Providers......................................................................................................................45Incompatible Objects ........................................................................................................................46Combining Data in the Table ............................................................................................................49

Exercise 5 Combining Data from Different Sources.......................................................................50Appendix A Review Answers....................................................................................................................51Appendix BUser Objects ..........................................................................................................................53

User Objects .....................................................................................................................................53Creating a User Object .....................................................................................................................53Using a User Object..........................................................................................................................57Removing a User Object...................................................................................................................58

Appendix C Combined Queries ...............................................................................................................59Combined Queries ............................................................................................................................59Restrictions.......................................................................................................................................59Building a Combined Query ..............................................................................................................59


4/66


Appendix D Using an XML Data Provider ................................................................................................61Appendix E ASCII character codes .........................................................................................................65


5/66

Complex Queries in Desktop Intelligence 5

About this CourseObjectiveThe objective of this course is to teach the advanced functionality of theBusinessObjects editor for creating complex reports from the Enterprise Data

Warehouse.

Instructor Led CourseThis course is presented in a computer lab with an instructor. The instructor presentsthe information by completing the examples in each chapter on the screen. Thestudents in the class follow by doing the same steps as the instructor. At the end ofeach chapter the students complete an exercise, which is similar to the example.

Training DataTwo universe/databases are used in the course. TheEDW STU-Course Scheduleuniverse is used for all the examples. This universe is very small and is also a

production universe. TheEDW R&A Traininguniverse is used for all the exercises.While this is still generic data, it is more like the production universes. The data wasused as test data for Recruiting and Admissions.

PracticeTo repeat the examples and exercises on your own, you can use play accounts whichhave access to the training data. These accounts have the same access as the accountsused during the class. The play accounts are:dsplay1, dsplay2, , dsplay10. Thepassword for all these accounts isDStra1n1ng.

Files

Completed versions of the examples an exercises are available in a zip file at thefollowing location: http://www.ds.uillinois.edu/training/complex_files.zip

DemosFor a list of demos on a variety of topics related to using the BusinessObjects editorsee: http://www.ds.uillinois.edu/how_to.asp


6/66



7/66

Chapter 1: 7

Chapter 1

Formulas and VariablesBusinessObjects allows you to create formulas and variables to manipulate the data

after it has been retrieved from the database. You can do projections, change theformat of the data, combine objects, or anything else you can imagine.

While user objects operate at the database level through the SQL query, formulas andvariables act on data that has already been retrieved and stored in a data provider.Formulas and variables are stored in the document where they are created. They areavailable to any user of the document and can be refreshed or edited.

FormulasFormulas are unnamed, which can cause some confusion when there are many formulaswithin one report or document. If a formula is changed, the previous version of the

formula is kept and the new version of the formula is added. These unused versionsshould be removed to prevent them from slowing the display of the report.

Formula RulesThere is certain syntax that needs to be followed when entering a formula: Must begin with an equal sign(=). Without an=the formula is displayed as a

constant or text. Variables included in formulas must be enclosed in aless than (). For example: . Text included in formulas must be enclosed in double-quotes ().

Creating and Entering a FormulaThere are three methods to create a formula: enter a formuladirectly into the cell enter a formula into theFormula Bar use theFormula Editor

In the next example we use theFormula Editor to enter a formula showing the changein enrollment from oneSection Enrollment Effective Dateto the next.

1. Create a standard report using theEDW STU Course Scheduleuniverse.

2. Add the following objects:Course NumberSection NumberSection Enrollment Effective DateSection Enrollment

3. Enter the following conditions:Term Code Equal to 120078Active Section (predefined condition)


8/66


Exclude Independent Study (predefined condition)Select Course Subject Code(predefined condition)

All the logical operators should be set toAnd.

4. Run the query.

5. Enter HIST at the prompt.6. Create a section forCourse Number.

7. Create a section forSection Number.

8. Remove two decimal places from theSection Enrollmentcolumn.

9. Wrap the text in the column header.

The report should look like this:

10.Select theSection Enrollmentcolumn.

11.Select InsertColumn to insert a column to the right of theSection Enrollmentcolumn.

12.Click a cell in the newly inserted column.

13.Click theFormula Editor button in theFormula toolbar.TheFormula Editor appears.


9/66

Chapter 1: 9

TheFormula Editor is very similar to the user object editor and the variable editor,except theFormula Editor does not have aDefinitiontab. A formula remainsunnamed and so has no need for aDefinitiontab.

14.Double-click theSection Enrollment object.

Notice that the cursor is placed to the right of.

15.Double-click the operator.

16.Click the+to display theMisc functions.


10/66


17.Double-click thePrevious function. Note that the cursor is placed in the locationfor the function argument.

18.Double-click theSection Enrollment object.

19.Click theOKbutton to close theFormula Editor.

The values for the change in enrollment are added to the blank column.There is no text in the column heading because the formula has no name associatedwith it.

20.Double-click the column heading for the last column.

21.Enter Change in enrollment.

22.Savethe document asCQ1aandcloseit.


11/66

Chapter 1: 11

VariablesA variable is a named formula. They can be used in the same way as variables in thedata provider. Variables are stored in the document.

Variables have some advantages over formulas. You can use variables in alerters, filters, sorts, breaks, master/detail reports, and in

drill hierarchies. Since a variable has a unique name, they are easier to work with. Variables can be used in other formulas. So, a complex formula can be simplified

by using variables to create the formula in pieces. If the formula for a variable is changed, the original is updated and there is still only

one copy of the variable.

Creating a VariableCreating a variable is a very similar process to that of creating a formula. The major

difference between the two is that theVariable Editor has aDefinitiontab, where thename and variable type are specified.

We will create a variable that determines the total fee amount generated per section intoone variable namedFees generated per section.

1. Create a standard report using theEDW STU Course Scheduleuniverse.

2. Edit the data provider and add the following objects:Course Subject CodeCourse NumberSection Number

Section Fee AmountSection Enrollment

3. Add the following conditions:Term Code Equal to 120078Course Subject Code In list ART,ARTD,ARTE,ARTF,AFTH,ARTSSection Fee Amount Greater than 0Current Section Enrollment (predefined condition)Active Section (predefined condition)Exclude Independent Study (predefined condition)

All the logical operators should be set toAnd.

4. Run the query.5. Create a section forCourse Subject Code.

6. Resize the columns and wrap the text in the column header row.

7. SelectDataVariables.TheVariableswindow appears.

8. Click theAdd button to bring up theVariable Editor.


12/66


9. Select theDefinition tab.

10.Name the new variableFees generated per section.

11.SelectMeasureas the qualification type.

12.Select theFormulatab.

13.Enter the following formula:

=*

14.ClickOKto save the new variable.

The new variable now appears in theVariableswindow:

15.Click theClosebutton to close theVariablesdialog box.

16.Drag and dropFees generated per section to the right of theSection Enrollment.



13/66

Chapter 1: 13

17.Savethe document asCQ1b andcloseit.


14/66


Exercise 1 Formulas and VariablesNote:

Answers to Review questions start on page 51 (Appendix A).

Review

1. A variable can be used in many ways that a formula cannot: True or False?2. A formula cannot be shared among different users: True or False?

ExerciseCreate a report using theEDW R&A Traininguniverse.Objects:First Name, Middle Name, Last Name, UINConditions: (Predefined) Springfield.

Add a variable,Formatted UIN, which removes the @ symbol from the UIN byusing theRight function. Add theFormatted UIN variable to the table and removeUIN.

Save the document asCQExercise 1. This will be used in the next exercise.

Completed Exercise


15/66

Chapter 2: 15

Chapter 2

Calculation ContextsBy default, BusinessObjects determines the result of a measure based on the

dimensions in the part of the report in which the measure is inserted. These sets ofdimensions are calledcalculation contexts.

For example, the tables below all include thePosted Transaction Amount object, butthe values displayed are different due to the context of the table.

Input and Output ContextsCalculation contexts are determined by both input and output contexts.


16/66


Input context determines what dimensions go into the calculation to producevalues. The calculation produces values based on the input context.

Output context determines how the values of the calculation are combined. It isgoverned by the cell location within the report.

Calculation Context OperatorsWhen creating a calculation context there areoperatorsandkeywords that areavailable to create formulas and variables.

Definitions for context operators are:ForAllSpecifies the dimensions you want to include in the calculation.

Syntax: measureForAlldimensionsExample: /(

ForAll )Divides the posted transaction amount by the posted transaction amount generated

for all periods.

ForEachAdds a dimension to the calculation environment.

Syntax: measureForEach dimensionsExample: Max(ForEach )Returns the maximum annual posted transaction amount for each fiscal year in thereport.

In

Uses a keyword to define the calculation environment (syntax 2 below), or specifies thedimensions included in the calculation environment (syntax 1 below).Syntax 1: measureIn dimensionsExample: In (,)The posted transaction amount for period and fiscal year.

Syntax 2: measureIn keywordExample: In ()The posted transaction amount for the period.

Calculation Context KeywordsKeywords available for creating formulas are: Body: Corresponds to dimensions in the current block and dimensions in the

current section. It can be used inside the block for dimensions in the current block,and outside the block for dimensions in the current section.

Block: Corresponds to dimensions in the current section. It can be used fordimensions inside the current block.


17/66

Chapter 2: 17

Report: Corresponds to all dimensions in the document. It can be used fordimensions anywhere in the report.

CurrentPage: Corresponds to all dimensions in the current page. It can be usedfor dimensions inside the current page.

Calculation Context ExampleLets look at an example of calculation contexts.

1. Open theCQ Calculation Context Exampledocument in the Complex folder.



18/66


2. Add aSumcalculation toSection Enrollment Measure.

3. Select the cell with the sum value.

4. PressCtrl+c to copy the contents of the cell.

5. Paste the cell contents into theTotal Department Enrollment

cell.

The value should be the same as at the bottom of the table because the outputcontext has not changed. The cell is still within the Department and Collegesections.

6. Paste the cell contents into theTotal College Enrollment cell.

7. Paste the cell contents into theTotal Term Enrollment cell.


The values in the cells are different because of the output context. Next we willdisplay the context.


19/66

Chapter 2: 19

Viewing Calculation ContextsOne method to learn more about Calculation Context operators is to view existingformulas. There are two methods to view formulas:

Using the Formula Bar:

1. Click the cell containing the calculation.2. Rest the cursor over the Formula Bar.

The extended syntax of the formula appears in a tooltip.

Using the Define As Variable command:

1. Click the cell containing the calculation.

2. SelectDataDefine As Variable.TheDefine as Variabledialog box will appear.

3. ClickEvaluate the formula in its context.

The extended syntax of the formula will appear in the dialog box.

The extended syntax for the three Total Sales cells are, from top down:=Sum(In Body) In Report=Sum(In Body) In =Sum(In Body) In (,)

Changing the Calculation Context

1. Copy the contents of the cell with theTotal Department Enrollment value (33).

2. Paste the cell contents into the% of College Enrollment cell.

3. Change the formula for the cell to:=Sum() / Sum(In Body) In () * 100

This divides the sum for the Department/College combination by the sum for theCollege. The report should look like this:


20/66


4. Copy and paste the formula into the% of Term Enrollmentcell directly below.

5. Change the formula to: =/Sum(In Body) In Report * 100

This divides the enrollment for the College/Departments by the enrollment for all

Departments for all Colleges. The cell should now display 2.56%.6. Copy and paste the formula in the Departments % of Term Enrollment cell into the

% of Term Enrollmentcell for the College.

This divides the enrollment for the College by the enrollment for all Departmentsfor all Colleges. The cell should now display 38.63%.

The finished report should look like this:


21/66

Chapter 2: 21

Exercise 2 Calculation ContextsNote:


Review

1. The Output Context keyword for all values is ______________.

2. What are two methods for viewing Calculation Context formulas?

ExercisesCreate a standard report using the EDW R&A Training universe.Objects:Last Name, First Name, UIN, ACT Composite ScoreConditions: (Predefined) Springfield

Create a variable that calculates the average ACT score.

Create an Alerter that displays red text for any ACT score less than the average score.

Save the report asCQExercise 2.

Completed Exercise


22/66



23/66

Chapter 3: 23

Chapter 3

SubqueriesA subquery is a query within a query. The subquery usually returns a single column of

data which is used in a condition of the main query. The subquery uses the same datasource. Subqueries are created using operand options with a condition. Depending onthe operator used in the condition, you will see one or both operand options:Create a subquery (ALL)This operand compares the object with all the values returned by the subquery. Forexample: Section Enrollment Greater than (all items of the query 1.1).

Create a subquery (ANY)This operand compares the object with anyvalues returned by the subquery. Forexample: Section Enrollment Equal to (one item of the query 1.1).

Lets create a report that requires a subquery. We want a list of Urbana HIST courseswith a course level of 200 or above which had a section enrollment of at least 100 inFall 2007 (term 120078) which also had a section enrollment of at least 25 in Summer2007 (term 120075). We cant do this with one query because there are different timeperiods.

1. Create the following query with:Course ID Course Subject Code Course NumberSection Number Section Enrollment

2. Add the following predefined conditions:Current Section EnrollmentActive SectionExclude Independent Study

3. Create the following conditions:Term Code Equal to 120078Course Subject Code Equal to HISTCourse Number Level >=200Section Enrollment >=100

4. Add a new condition for theCourse ID object.

5. SelectEqual to for the operator.

6. SelectCreate a subquery (ANY) for the operand.

A new query tab appears,Subquery 1.1.

The subquery needs to produce the list of HIST sections with a course level 200 andabove which had an enrollment of at least 100 in term 120078 and an enrollment ofat least 25 in term 120075.


24/66


7. AddCourse ID in the results of the subquery.

8. Add the following predefined conditions:Current Section EnrollmentActive SectionExclude Independent Study

9. Create the following conditions:Term Code Equal to 120075Course Subject Code Equal to HISTCourse Number Level >=200Section Enrollment >=25

The subquery should look like this:

10.Run the query.

The table should look like this:

To review: TheSubquery 1.1 returns the list of Course IDs whose enrollment was at least

25 in term 120075.


25/66

Chapter 3: 25

The main query returned the list of Course IDs whose enrollment was at least100 in term 120078 as long as the same Course ID was in the list produced bythe subquery.

Multiple Data Providers

A data provider contains the results of running a query. This data is then used to createreports. Building a data provider when you create a new document is a typical way ofusing BusinessObjects. You create the document in order to see your data - and to dothat, you have to build a data provider to access data from a data source.

You dont have to create a new document every time you want to see new data in areport. You can add data providers to existing documents. This feature enables you tocombine data from different sources in the same report.

A document can contain multiple dataproviders and multiple reports. Each

report can contain blocks that pull datafrom one or more data providers, orcombine data from different dataproviders in the same block.

Document 1

Report 1

Report 2

Report 3

DataProvider 1

The figure to the right shows adocument with two data providers andthree reports. Report 1 has a singletable with data from Data Provider 1.Report 2 has two blocks with datafrom different data providers. Report

3 has one block, which combines datafrom two different data providers.

DataProvider 2

Verifying the ResultsAlthough it is not necessary, we will display additional tables to verify that the resultswe obtained are correct.

1. Click theInsert Table button in theReport toolbar.

2. Click below the existing table to place the upper-left corner of the new table.


26/66


TheNew Table Wizardstarts.

3. SelectUse an existing query to build a new onefrom the list.

4. Select the first query from the list.

5. Delete the condition: Course ID Equal to (one item of the subquery 1.1).

6. Run the query.

The second table displays Course IDs which had a section enrollment greater thanor equal to 100 in term 120078.. Note there are two additional Course IDs,1003684and1003755.

7. Click theInsert Table button in theReport toolbar.


27/66

Chapter 3: 27

8. Click to the right of the bottom table.

9. SelectUse an existing query to build a new one from the list.

10.Select the first query from the list.

11.Delete the condition: Course ID Equal to (one item of the subquery 1.1).

12.Change the Term Code condition toTerm Code Equal to 120075.

13.Change the Section Enrollment condition toSection Enrollment Greater than orequal to 25.

14.Run the query.

The bottom table shows the Course IDs with an enrollment greater than or equal to25 in term 120075. As you can see, only Course ID 1003673 (HIST 273) had anenrollment greater than or equal to 100 in term 120078 and an enrollment greaterthan or equal to 25 in term 120075.

Managing Data ProvidersYou can manage the data providers in BusinessObjects documents in the followingways: Rename data providers Get statistics such as the date and time a data provider was last refreshed, and how

many rows of data were returned Empty data providers of their data (calledpurging)


28/66


Delete data providers you no longer need

Renaming Data ProvidersBusinessObjects assigns names to all data providers. The names of the data provider

can be changed using theDefinitiontab of theView Datadialog box. Renaming dataproviders is by no means required. You can work with the default names of the dataproviders. If objects with the same name exist in multiple data providers, the name ofthe data provider is added to the object name.For example, Section Enrollment (Query 2 with Schedule).

We will rename the data providers to make them easier to work with.

1. Select theView Data button in theStandardtoolbar.

TheData Manager appears.

2. Click theQuery 1 with Scheduledata provider from the list on the left.

3. Click theDefinition tab.

4. Type the new name,Enrollment at least 100 in Term 120078 and at least 25 inTerm 120075, in the Name box. Dont press Enter.

5. ClickQuery 2 with Schedule.

6. Change the name toEnrollment at least 100 in Term 120078.

7. ClickQuery 3 with Schedule.

8. Change the name toEnrollment at least 25 in Term 120075.

9. Click theOKbutton.

Purging vs Deleting a Data ProviderWhat is the difference between purging and deleting a data provider? Purging removesthe data, but keeps the structure. Deleting removes the data and the structure - anaction which cannot be undone.

So why purge or delete a data provider? If you want to share the report with other userswho have different security access, you can purge the data before sending thedocument. Purging also reduces the size of a document and saves disk space. You canpopulate a purged data provider by selecting theRefresh Datacommand on theData

menu. This command refreshes all the data providers in the document. You shouldonly delete a data provider, however, if you are certain it is no longer needed.In the next example we purge the second data provider,Enrollment at least 100 in Term120078, and delete the third data provider,Enrollment at least 25 in Term 120075, fromthe document.

1. SelectView Data.

2. Click theEnrollment at least 100 in Term 120078data provider.


29/66

Chapter 3: 29

3. Click thePurgebutton.

4. ClickYes in the confirmation box.

5. Click theEnrollment at least 25 in Term 120075data provider.

6. Click theDeletebutton.

7. ClickYes in the confirmation box.

8. ClickOKto close theData Manager.

The second table has no data since the second query has been purged. However, wecould always display the data again by refreshing the query. The empty cell marksthe former location of the third query which was deleted. To display the dataretrieved by the third data, we would have to create the query again.

9. Delete the empty cell.

10.Save the document asSubquery.rep.


30/66


Exercise 3 SubqueriesNote:


Review1. A subquery can use a different data source. True or False?

2. A _____________________ is the data returned by running a query.

3. Can there be multiple data providers in the same document?

Exercises

Create a table with all applicants to the Chicago campus that have applied toboth thecolleges ofLiberal Arts and SciencesandBusiness Administration. The tableshould include the applicants UIN, First Name, Last Name, and College PGPA.

Save the report asCQ Exercise 3.

Completed Exercise


31/66

Chapter 4: 31

Chapter 4

Using Personal Data FilesPersonal data files can be used as a source for a data provider. This enables you to

access data in text files, Excel spreadsheets, dBase files, and XML files. Usingpersonal data files allows you to use BusinessObjects to create reports fromdepartmental data, or combine that data with data from the EDW.

Creating a report from a personal data file is a two-stage procedure. First you specifythe personal data file you want to use for the report and then you set the options for thetype of file selected.

File Types AvailableThe types of files that can be used as a personal data file are: Microsoft Excel spreadsheet (*.xls)

DBASE files (*.dbf) Text files (*.txt, *.asc, *.prn, *.csv) XML files (*.xml)

Options with .XLS FilesFirst Row Contains Column NamesThis option indicates that the files first row of data contains the names to be used ascolumn headings in the BusinessObjects report.

Sheet Name

This option lists the worksheets in the file you selected, and lets you select theworksheet to import.

Field SelectionThis option lets you select the fields from the spreadsheet to be included in the report.You have the following options: All Fields

Retrieves all the data from the worksheet. Range Definition

Retrieves the data from specified range of cells (e.g. A1:Z20) Range Name

Retrieves the data from a named range defined in the worksheet.

Creating a Report from an XLS FileThe .xls file used in this example contains data about a resorts customers.

1. Select theNew Report Wizard button on theStandardtoolbar.

2. Choose toGenerate a standard report.


32/66


3. SelectOtherson theSpecify Data Accessscreen.

TheAccess Personal Datawindow is displayed.

4. SelectMicrosoft Excel 97 Files (*.xls) from the Format listbox.

; Note: The Microsoft Excel Files format is for Excel 95 files.

5. Click theBrowsebutton.

6. SelectLincoln Hall Room Information.xls from the files in theComplex folder.

7. Check the checkbox for First row contains column names.

8. Click theRun button.

The report based on the data from the Lincoln Hall Room Information.xls file willbe displayed.


33/66

Chapter 4: 33

9. Close the document without saving the changes.Options with Text FilesThe text file option includes four types of files: asc, prn, txt, and csv. The file typedetermines the delimiter used in the file. You can also use theDelimiter option toindicate the character used to delimit the data in the file. The default settings per filetype are as follows:

Tabulation: tab (.txt)Space: space (.prn)

Character: semi-colon (.csv)| (.asc)

Create a table from a CSV personal data file

1. Select theNew Report Wizard button in the Standard toolbar.

2. Choose toGenerate a standard report.


34/66


3. SelectOtherson theSpecify Data Accessscreen.

TheAccess Personal Datawindow is displayed.

4. Make sureText Files (*.asc, *.prn, *.txt, *.csv) is selected as the format.

5. Click theBrowsebutton.

6. SelectRoom Contacts.csv from the Complex folder.

7. Check the checkbox forFirst row contains column names

.

8. Click theViewbutton.

The data file is read and then theData Manager dialog box is displayed.


35/66

Chapter 4: 35

9. Click theRoom Number variable in theData Providerspanel on the left.

10.Select theDefinition tab.


36/66


11.Click theDimension radio button to change theRoom Numbersqualification todimension.

12.Click theOKbutton to close theData Manager dialog box.

The data from theRoom Contacts.csv file will be displayed in a table.

13.Adjust the column widths.

14.Remove the decimal places from theRoom Number column.

Create a Second Data ProviderWe will now add data from the EDW STU Course Schedule universe as a seconddata provider in the same document. We will create a condition that limits the productsin the second query to just the ones in the results of the first query.

1. Click theInsert Tablebutton on theReport toolbar.

2. Click to the right of the existing table to establish the location of the upper-leftcorner of the new table.

TheNew Table Wizarddisplays.


37/66

Chapter 4: 37

3. Select theAccess new data in a different wayoption and clickBegin.

4. Click theNext button to use aUniverseas the data source.

5. Select theEDW STU Course Scheduleuniverse from the list and click theFinishbutton.

6. Select the following objects in the order shown:Room NumberCourse Subject CodeCourse NumberSection NumberSection EnrollmentMeeting DaysStart Time End TimeInstructor Last Name

7. Use the following predefined conditions:

Current Section EnrollmentActive Section

Exclude Independent Study

8. Create the following conditions:

Term Code Equal to 120078Building Desciption Equal to Henry Administration BldgCourse Subject Code Equal to MATHSection Enrollment Greater than 0

9. Drag theRoom Number object to the conditions panel to create a new condition.

10.Select In list as the operator.


38/66


11.SelectSelect Query Resultsas the operand.

TheList of Data Providersdisplays. From the list you want to select the query andobject that contains the values you want.

12.Click the+to expand the query.

13.Click theRoom Number object.

14.ClickOKto close the dialog box.

The new condition should look like this:


39/66

Chapter 4: 39

; Note: This condition has a limit of 1000 items in the list.

15.Click theRun button.

The new table is displayed. This table contains the data from the universe, but only

for the items in the list in the CSV file.

The objects for the second data provider will be added to the variables list. Whenthere are multiple data providers in the document, it is usually better to display thevariable list divided by data provider.


40/66


16.Click theBy Data Provider radio button at the bottom of theReportManager.

Now the variables are listed by data provider.

17.Savethe document asRoom Contacts.rep.

18.Closethe document

Data Qualification in Excel (.csv and .xls) Files

Occasionally you may be unable to use the Select Query Results operand with an Excelfile due to the way the data is classified in the Excel document. For example, you maysee an error like this:

The condition we wanted to create checked to see if the values for the Room Numberobject in the Schedule universe were in a list of Room Numbers listed in thespreadsheet. However, Room Numbers in the spreadsheet are treated as numeric data;while Room Numbers in the Schedule universe are treated as text.


41/66

Chapter 4: 41

We can remedy this problem by changing the Room Number information in the Excelspreadsheet to text.

1. Open theRoom Contacts2.csv file.

2. Select theRoom Number column.3. SelectDataText to columns

4. ClickNext.


42/66


5. ClickNext again.

6. SelectText in theColumn data formatarea.

7. ClickFinish.

8. The column should appear as shown below:

9. Save and closeRoom Contacts2.csv.


43/66

Chapter 4: 43

Exercise 4 Using Personal Data FilesNote:


Review

1. Can an Access database be used as a personal data file?

ExerciseCreate a table with the information from the csv file: Problem Applicants.csv. Add asecond table with the following data from theEDW R&A Traininguniverse for theapplicants listed in the first table.UINFirst NameLast NameCity

State

Save the document asCQ Exercise 4.rep.

Completed Exercise


44/66



45/66

Chapter 5: 45

Chapter 5

Combining Data from Different Data ProvidersWe will now combine data from two data providers in the same table. The source for

the first data provider is a CSV file in the userDocs folder namedRoom Contacts.csv.This file contains the contact information for the room numbers used by MATH coursesin term 120078.

Linking Data ProvidersLinking data providers enables data from different data providers to be combined in atable, crosstab, or chart. Dimension objects from one data provider are linked todimension objects that contain the same values in a different data provider. DesktopIntelligence then finds the corresponding rows from the two data providers. Onlydimension objects can be linked.

In this example, theRoom Number object in the first data provider must be linked tothe corresponding object in the second data provider.

1. Open theRoom Contacts.repdocument.

2. Delete the table on the left with the data from the CSV file.

3. Move the remaining table to the left margin.

4. Click theView Data button in theStandardtoolbar.

5. Click on the+next to each data provider to expand the lists of objects for each.

6. Select theRoom Number object in the first data provider.

7. Click theDefinition tab.8. Click on theLink to button.

9. Select theRoom Number object from the second data provider.

10.Click theOKbutton to close theView Datadialog box.

Arrows, , indicating the link will appear in the data provider list.


46/66


11.ClickOKto close theView Datadialog box.

Incompatible ObjectsThere is one very important rule about combining data from different data provider inthe same table.There can only be unlinked dimension objects from one dataprovider. If there are dimension objects from both data providers that you want in thetable, but these objects are not linked, they cannot be appear in the table at the sametime.

Click thePerson with keysobject in the first data provider.

Notice that theCourse Number, Course Subject Code, Instructor Last Name, MeetingDays, Section Enrollment, Section Number, and Start Time End Timeobjects in thesecond data provider are grayed out. This means these are incompatible objects.

Click theCourse Number object in the second data provider.


47/66

Chapter 5: 47

Notice that theBeeper number, Office, and Person with keysobjects in the second dataprovider are grayed out. These are also incompatible objects.

1. Click theBeeper number variable in the Report Manager.

TheBeeper number, Office,andPerson with Keysobjects are unlinked dimensionobject. Course Number, Course Subject Code, Instructor Last Name, MeetingDays, Section Enrollment, Section Number, andStart Time End Timeare alsounlinked dimension objects. They cannot appear in the table at the same time. Toget around this restriction, we need to create detail variables that contain the samedata as the unlinked dimension objects from one data provider. Since we wantseven unlinked dimension objects from the first data provider and only three fromthe second data provider, it is easier to create three variables rather than seven.

2. Right-click theBeeper number object in theData tab.

3. SelectNew Variable from the pop-up menu.

4. Enter Beeper number descas the name of the new variable.

5. Change the qualification toDetail.

6. For theAssociated Dimension, selectRoom Number.

The detail variable must be associated to a linked dimension object.


48/66


7. Select theFormulatab.

8. Double-click theBeeper number object to enter it into the formula.

This means the new variable will have the same data as the Beeper number object.

But, because it is a detail variable, it can be added to the table while the Beepernumber object cannot.

9. Right-click theOfficeobject in theData tab.

10.SelectNew Variable from the pop-up menu.

11.Enter Office descas the name of the new variable.

12.Change the qualification toDetail.

13.For theAssociated Dimension, selectRoom Number.


15.Double-click theOfficeobject to enter it into the formula.16.Select the ClickOKto close theVariable Editor.

17.Right-click thePerson with keysobject in theData tab.

18.SelectNew Variable from the pop-up menu.

19.Enter Person with keys nameas the name of the new variable.

20.Change the qualification toDetail.


49/66

Chapter 5: 49

21.For theAssociated Dimension, selectRoom Number.


23.Double-click thePerson with keysobject to enter it into the formula.

24.Select the ClickOKto close theVariable Editor.

Combining Data in the Table

1. Drag and drop thePerson with keys namevariable onto the table betweenRoomNumber andCourse Subject Code.

Person with keys namecan be added to the table because it is not an unlinkeddimension object.

2. Drag and drop theOffice descvariable onto the table to the right of thePerson withkeys namecolumn .

3. Drag and drop theBeeper number descvariable onto the table to the right of theOffice desccolumn..

The table should now look like this:

4. Close the document and save the changes.


50/66


Exercise 5 Combining Data from Different SourcesNote:


Review

1. What has to be done to combine data from different data providers into the sametable?

2. What type of objects can be linked?

3. What are incompatible objects?

ExerciseOpen theCQ Exercise 4.repdocument. Combine the data from both data providersinto one table.

Completed Exercise


51/66

Appendix A: 51

Appendix A Review AnswersExercise 1 Formulas and Variables1. A variable can be used in many ways that a formula cannot:

True

2. A formula is not sharable among different users:False, a formula is stored in the document and is available to any user.

Exercise 2 Grouping Values1. The Group function works only with what Object types?

DimensionandDetail

Exercise 3 Grouping Measure Objects1. To group Measure Objects together you must use IfThenElse code:

True

Exercise 4 Calculation Contexts1. The Output Context keyword for all values isIn Report.

2. What are two methods for viewing Calculation Context formulas?The tool tip in the Formula Bar or DataDefine as Variable

Exercise 5 Subqueries1. A subquery can use a different data source.

False

2. A data provider is the data returned by running a query.

5. Can there be multiple data providers in the same document?Yes

Exercise 6 Using Personal Data Files1. Can an Access database be used as a personal data file?

No, only flat files can be used as personal data files(asc,prn,txt,csv,xls,dbf,xml)

Exercise 7 Combining Data from Different Sources1. What has to be done to combine data from different data providers into the same

table?

One or more dimension objects must be linked.2. What type of objects can be linked?

Dimension3. What are incompatible objects?

Unlinked dimension objects from different data providers. They cannot beincluded in the same table. The work-around is to create a Detail variable withthe same data.


52/66



53/66

Appendix B: 53

Appendix BUser ObjectsIt is often necessary for a report developer to add data to a report that is not present inthe universe. BusinessObjects provides three ways to add data to the report: User Objects Formulas Variables

They all make use of the Formula window, and so may seem similar at first. But thereare some important differences. One major difference is that user objects are associatedwith a specific universe, while formulas and variables are associated with a singledocument. Also, formulas and variables are stored in the document and are available toany user refreshing or modifying the document. User objects are stored on the authorshard drive and are not available to other users. Another difference is that user objectsare written in SQL code, but formulas and variables are written in BusinessObjectscode.

User ObjectsA user can create objects that appear to be part of the universe. These objects willappear in the User Objectsclass when the universe is displayed in the Query Panel.The user objects can be used in reports just like any other object in the universe. Theadvantage of user objects is that they dont have to be recreated for each newdocument.

User objects are personal objects that are not shared with other end-users. They arestored in a local file in the\Universe folder. The file will have the same name as theuniverse.

User Objects RestrictionsSome restrictions placed upon user objects are: User objects are available only in the universe in which they were created. User objects are not shared. Reports that include user objects can only be viewed

by other end-users.

This is because user objects are stored locally in a user object definition file. Otherend-users, who do not have the same user object definition file, are not able to accessthe user object definitions. If an end-user tries to refresh or edit a query that containsanother users user objects, BusinessObjects removes the objects from the query and

report.If a user object is meant to be widely shared, then we would recommend contactingDecision Support to inquire as to the possibility of having that user object added to theuniverse.

Creating a User ObjectThere are two ways to create a user object:

using the User Objects button, , found on the toolbar of the Query Panel.


54/66


using theToolsUniversesmenu option. This method permits you to select theuniverse the user object will be created for.

Both methods display the User Object dialog box to create the formula for the userobject. We will use the Tools menu to create our user object.

We want to project next years revenue based on a 3% increase in sales. We will dothat by adding a user object.

1. Click ToolsUniverses.

The Universes panel will appear, showing all universes to which you have access.

2. Select theeFashion universe.

3. Click theUser Objects button.

The User Objects panel will appear. Any existing user objects associated with theeFashion universe would appear in this window.


55/66

Appendix B: 55

4. Click theAdd button to add a user object.

The User Object editor will appear. This is similar to the editor used to createformulas and variables.

5. Click theDefinition tab.

6. Name the user objectProjected Revenue

7. SelectMeasureas the qualification.


56/66


8. SelectSumas the function.

This means that when the values for the Projected Revenue object are aggregateddue to the context of the report, the sum of the values will be used.

9. Click theFormulatab.

10.Click theMeasuresclass to display the list of measure objects.11.Double-clickSales revenueto add it to the formula.

At this point{Measures\Sales revenue}should appear in the top pane of theFormula tab.

12.Add the multiplication operator,*.

13.Enter 1.03 to the right of the* to calculate a 3% increase in sales revenue.

The completed formula will look like:{Measures\Sales revenue} *1.03

14.Click theTest button to check for syntax errors.

15.If there are no errors, clickOK.

Projected Revenue will now appear in the User Objects window.

(Insert Picture)

16.ClickOKon the User Objects window to close it.

17.ClickCloseon the Universes window to close that window also.


57/66

Appendix B: 57

Using a User ObjectWe will now use the newly created user object in a report.

1. Create a new document using theeFashion universe.

The User Objects class has been added at the bottom of the universe. The

Projected Revenueobject is now ready to be used along with permanent eFashionUniverse objects.

2. Select the following objects:

Store name Sales Revenue Projected Revenue

The Query Panel should look like:

3. Add the following condition to the Query Panel:

Year Equal to 2001

4. Run the query.


58/66


The table should look like:

5. Save the document asProjected Revenue.

6. Close the document.

Keep in mind, if you send this report to another user and that user tries to refresh oredit the query, the user object, Projected Revenue, will be removed from the query.

Removing a User Object

1. Click ToolsUniverses.

2. SelectUniverses.

The Universes dialog box will appear displaying a list of available universes.

3. Select theeFashion universe.

4. Click theUser Objectsbutton.

The User Objects window will appear with a list of current user objects associatedwith the eFashion universe.

5. Select theProjected Revenueobject.

6. Click theDeletebutton, and confirm your action to delete the user object.


59/66

Appendix C: 59

Appendix C Combined QueriesCombined QueriesCombined queries are very much like subqueries. Subqueries compare a single objectwith the results of the subquery. Combined queries compare the rows of one query

with the rows of the other. You can display the union, intersection, or the difference ofthe two queries.

Union (default): includes the rows from both queries

Intersection: includes the rows common to both queries

Minus: includes the rows from the first query minus the rows from thesecond query

RestrictionsThe major restrictions in combining queries are: the queries must contain the same number of objects the objects must be of the same type (i.e. character, date, number) there can be up to eight queries in a combined query when usingMINUS, the second query is subtracted from the first

Build ing a Combined QueryWe will create a table containing the categories that were sold in the 2001 that werentsold in 2000. We will use a combined query to subtract the rows for 2000 from therows for 2001.

1. Use the new document using the New Report Wizard and thee-Fashionuniverse.

2. AddCategoryobject to the Result Objects.

3. Add the condition:Year Equal to 2001.

4. Click theCombine Queries button to add a second query.

A second query will appear with a U, for Union, between the tabs. The ResultsObjects panel is automatically populated with theCategoryobject from Query 1.

5. Add a condition to this second query ofYear Equal to 2000.

6. Right-click the Query 2 tab.

The pop-up menu of combined query types is displayed


60/66


7. SelectMinus.

8. Run the query.

A table should appear with the two rows,SamplesandSkirts, that are in the queryresults for 2001 but not in the results for 2000.


61/66


62/66


The XML Filter editor is displayed.

6. Click the Load button.

7. Open theMy Documents\My Business Objects Documents\userDocsfolder.

8. Select theClass Schedule.xml file.

The structure of the XML file is displayed in the Structure panel.


63/66

Appendix D: 63

The XML Query Panel is displayed.

Some XML elements do not contain data; they act as a container for other elements.DATA_RECORD is an example of such an element in this file. It is not meaningfulto include such elements in a report.

9. Uncheck theAudienceandNotes fields to not include them.

10.Click theSavebutton.The Save XML Filter As dialog box is displayed.

11.Enter Schedulefor the filter name.

12.Select theFinishbutton to use the new XML filter.


64/66


13.Double-click the following objects to select them:CLS_SCHED_IDFROM_DTTO_DTSITE_ID

INSTR_ID

14.Click theRun button.

The table should look like:


65/66

Appendix E: 65

Appendix E ASCII character codes0 Null char. 43 + 86 V1 Start of Header 44 , 87 W2 Start of Text 45 - 88 X

3 End of Text 46 .(dot) 89 Y4 End of Transmission 47 / 90 Z5 Enquiry 48 0 91 [6 Acknowledgment 49 1 92 \7 Bell 50 2 93 ]8 Backspace 51 3 94 ^9 Horizontal Tab 52 4 95 _

10 Line Feed 53 5 96 `11 Vertical Tab 54 6 97 a12 Form Feed 55 7 98 b13 Carriage Return 56 8 99 c

14 Shift Out 57 9 100 d15 Shift In 58 : 101 e16 Data Link Escape 59 ; 102 f17 Device Control 1 60 < 103 g18 Device Control 2 61 = 104 h19 Device Control 3 62 > 105 i20 Device Control 4 63 ? 106 j21 Negative Acknowledg. 64 @ 107 k22 Synchronous Idle 65 A 108 l23 End of Trans. Block 66 B 109 m24 Cancel 67 C 110 n

25 End of Medium 68 D 111 o26 Substitute 69 E 112 p27 Escape 70 F 113 q28 File Separator 71 G 114 r29 Group Separator 72 H 115 s30 Record Separator 73 I 116 t31 Unit Separator 74 J 117 u32 Space 75 K 118 v33 ! 76 L 119 w34 77 M 120 x35 # 78 N 121 y

36 $ 79 O 122 z37 % 80 P 123 {38 & 81 Q 124 |39 82 R 125 }40 ( 83 S 126 ~41 ) 84 T 127 delete42 * 85 U


66/66


128 171 214 129 172 215 130 173 216 131 174 217 132 175 218

133 176 219 134 177 220 135 178 221 136 179 222 137 180 223 138 181 224 139 182 225 140 183 226 141 184 227 142 185 228 143 186 229

144 187 230 145 188 231 146 189 232 147 190 233 148 191 234 149 192 235 150 193 236 151 194 237 152 195 238 153 196 239 154 197 240

155 198 241 156 199 242 157 200 243 158 201 244 159 202 245 160 203 246 161 204 247 162 205 248 163 206 249 164 207 250 165 208 251

166 209 252 167 210 253 168 211 254