AC280_EN_Col92_FV_Part_A4 (第7页) | ERP软件技术分享

第 257 页 / 共 319 页

AC280 Lesson: Queries

Exercise 12: Insight into SAP BW: Queries

Exercise Objectives

After completingthisexercise, youwillbeableto:

• CreateandmodifyyourownqueriesintheBExAnalyzer

• Navigateintheanalysisresultforthequeryandfilterdata

• Integrateadditionalinformationastextelementsintothequeryresult

• Define and use value and text variables

Business Example

You have the task of creating some query definitions yourself. You want to

implementotheranalysisfunctionsinthequeryresults.

Hint: The##symbolisaplaceholderforyourgroup(01,02,03... and

so on).

Task 1:

Youwant tofindout thecumulativebalancesof your customers. Call theBEx

Analyzerand createa newqueryfor thispurpose.

1. DefineanewqueryusingtheInfoCubeFIAR:Transactiondatainthe“FI

AccountsReceivable”InfoArea. Thequeryshouldhavethefollowinglayout:

Customer Cumulativebalance

1000 1000,00

1100 2000,00

.... ....

2. SavethequeryusingthetechnicalnameGR##QUERY1andthedescription

AC280 QUERY 1 GR##.

3. Execute the query.

Continued on next page

241

Customer	Cumulativebalance
1000	1000,00
1100	2000,00
....	....

第 258 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Task 2:

Onceyouhavelookedattheresult,youwanttochangethequery.

Changethequery using the BEx Query Designer.

Hint: Youcanonlymakepermanentchangestothequerybychoosing

“Change Query (Global Definition)”. If you change a query in the

“ChangeQuery(LocalView)”mode,thesystemupdatestheworksheet,

butdoesnotsavethechangesinthequerydefinition.

1. You want tofindout about customersincompanycodeIDES AG(1000)

only,butdonotwanttodisplaythecompanycodeontheworksheet. You

want toinclude theCustomerindustryand theFiscal year/Periodasfree

characteristics. YouonlywanttofindoutabouttheTrade(TRAD),high

tech (HITE), and Engineering industries (MBAU) and the figures for

December2002. You also want todisplaytheSalesfortheperiod inthe

columns.

2. Save your changed query

Execute the query again.

3. Onceyouhavelookedattheresultofyourchangedquery,youwanttosee

the result forTrade(TRAD) in theanalysis.

Hint: SetthefilterforTrade(TRAD)intheworksheet.

4. Nowremove the filter for Trade(TRAD).

Task 3:

Tointerpretthedata,youhavetoperformvariousnavigationsteps. Initially,the

customershouldbedisplayedin the rows, andthetwo keyfiguresCumulative

balanceandSalesforperiodshouldbedisplayedinthecolumns. Afteryouhave

performedthenavigationsteps,theworksheetshouldbelaidoutasfollows:

1. Displaytheoverallresultwithoutcharacteristicsinthedrilldown.

2. Thecustomerindustryshouldappearafterthecustomerinthequery.

Hint: Returntotheinitialdisplay,inwhichthecustomerisdisplayed

intherowsandthetwokeyfigurescumulativebalanceandsalesfor

periodaredisplayedinthecolumns. Thenmakethechanges.

Continued on next page

242

第 259 页 / 共 319 页

AC280 Lesson: Queries

Customer

industry

Fiscal

year/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

.. . . . . . . .. . . . . . . .. . . . . . . . . . . . . . .

3. Exchangethecharacteristicscustomerandcustomerindustry.

Customer

industry

Fiscalyear/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

....... ....... ....... .......

4. RetainthecharacteristicvalueTrade(TRAD)(forcustomerindustry)asa

filtervalueandlistallthecustomersforthisindustryintherow.

Customerindustry Trade(TRAD)

Fiscalyear/period

Customer

Key figures

Customer Cumulativebalance Salesforperiod

.......... ............ .........

5. Nowdisplaythefiguresfor aspecificcustomer.

Continued on next page

243

Customer industry Fiscal year/period Customer Keyfigures

Customer	Customer industry	Cumulative balance	Salesforperiod
.. . . . . . .	.. . . . . . .	.. . . . . . .	. . . . . . . .

Customer industry Fiscalyear/period Customer Keyfigures
Customer industry	Customer	Cumulative balance	Salesforperiod
.......	.......	.......	.......

Customerindustry Fiscalyear/period Customer Key figures	Trade(TRAD)
Customer	Cumulativebalance	Salesforperiod
..........	............	.........

第 260 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Customer industry

Fiscal year/period

Customer XYZ

Key figures

Cumulativebalance Sales for period

............ ............

6. Within the customer industries, you now have to display the customers

individually. YouhavetodisplaytheKeyfortheindustryandthekeyforthe

customers. Sorttheindustriesindescendingorderbykey. Alwayssuppress

the result rows for the customers. For the industry, you want an overall

resultdisplayedforthecumulativebalanceandfortheperiodsales.

Customer

industry

Fiscalyear/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

Trade 1001 EUR1,500.00 1,000.00EUR

1002 980.00EUR 910.00EUR

1033 ...... ......

...... ...... ......

High tech 1460 3,000.00EUR 2,500.00EUR

...... ...... ......

Engineering 1000 EUR5,670.80 3,215.80EUR

...... ...... ......

Overallresult XX,XXX.XX XX,XXX.XX

EUR EUR

7. When you created query AC280 QUERY 1 GR##, you filtered

characteristics. Whenyouareperformingtheanalysis,youwanttodisplay

the information that you did not filter (for example, author, last changed

by, infoprovider, technicalname, andso forth). Add thetext elementsto

the worksheet.

Continued on next page

244

Customer industry Fiscal year/period Customer Key figures	XYZ
Cumulativebalance	Sales for period
............	............

Customer industry Fiscalyear/period Customer Keyfigures
Customer industry	Customer	Cumulative balance	Salesforperiod
Trade	1001	EUR1,500.00	1,000.00EUR
	1002	980.00EUR	910.00EUR
	1033	......	......
	......	......	......
High tech	1460	3,000.00EUR	2,500.00EUR
	......	......	......
Engineering	1000	EUR5,670.80	3,215.80EUR
	......	......	......
Overallresult		XX,XXX.XX EUR	XX,XXX.XX EUR

第 261 页 / 共 319 页

AC280 Lesson: Queries

8. Savetheresultsof query AC280QUERY1GR##asanewworkbookin

yourfavoriteslist. CalltheworkbookQuery1GR##Result.

Task 4:

Youwanttofindoutyourcustomer'ssalesfortheperiod. Inthenextexerciseyou

willcreateandusecharacteristicvariablesforthecustomercharacteristic

1. DefineanewqueryusingtheInfoCubeFIAR:Transactiondatainthe“FI

AccountsReceivable”InfoArea. Youwanttofilterthequeryusingcompany

codeIDESAG(1000). EnterFiscalyear/Periodasfreecharacteristics. You

wanttodisplaythecustomerintherowsandtheperiodsalesinthecolumns.

Customer Sales for period

1000 1000,00

1100 2000,00

... ...

2. SavethequeryusingthetechnicalnameGR##QUERY2andthedescription

AC280 QUERY 2 GR##.

3. Execute the query.

4. The query output for AC280 QUERY 2 GR## lists all customers in the

companycodeIDESAG.Inthenextoutput,youwanttobeabletostructure

thedisplayofthecustomerareaflexibly. YoudonotdecideonFromandTo

customers until you perform the analysis.

Change query AC280 QUERY 2 GR##. Define a variable for the

characteristicCustomer(InfoObject0CUSTOMER),whichyouwillneedto

selectcustomers. Thisvariableisarangethatyouhavetoeditmanuallyor

byusingdefaultvalues. Youhavetodefineitasmandatoryandreadyfor

inputanditshouldbepossibletochangeitwhenyounavigateinthequery.

Use the following naming conventions:

Variablename: DEBVB##Description: From/ToCustomerGR##

Enter1000asthedefaultvalueand2000astheTodefaultvalue.

5. Save query AC280 QUERY 2 GR##.

6. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000.

Continued on next page

245

Customer	Sales for period
1000	1000,00
1100	2000,00
...	...

第 262 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Task 5:

Nowyou will be working withtext variables. You want to displaysales for a

specificfiscalyear/periodandthecorrespondingheading(forexample,12/2002)

as follows:

Customer Sales 12/2002

1000 1000,00

1100 2000,00

.... ....

1. Remove the characteristic Fiscal Year/Period from the category Free

Characteristics and Key Figures from the columns. Create a new

characteristicvaluevariableforthefiscalyear/period.

Variablename: PERYR##; description: Period/Year##.

Processed by: Manual entry default value.

Other entries: Individual value, Entry obligatory, Ready for input and

Changeableduringquerynavigation. Enter12/2002asthedefaultvalue.

2. Createanewstructureforthecolumns. Thestructureshouldcontainanew

selection. Thefirstcomponentofthenewselectionforthecolumnstructure

shouldcontainthesalesfor theperiodandthesecondpartshouldcontain

yourvariableforthefiscalyear/periodPeriod/Year##.

Choose Select text variable.

Create a new text variable. Variable name: PYRTXT##, Description:

Per/YearText##, Processedby: Replacementpath.

Other entries: characteristic Fiscal year/Period (0FISCPER), replace

variable with External attribute of key, From value, Offset start: 0003,

Offset length0008. EnterSales &PYRTXT##& as adescription for the

new selection.

3. Save query AC280 QUERY 2 GR##.

4. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000and

enter 011/2002 as the period/year ##.

246

Customer	Sales 12/2002
1000	1000,00
1100	2000,00
....	....

第 263 页 / 共 319 页

AC280 Lesson: Queries

Solution 12: Insight into SAP BW: Queries

Task 1:

Youwant tofindout thecumulativebalancesof your customers. Call theBEx

Analyzerand createa newqueryfor thispurpose.

1. DefineanewqueryusingtheInfoCubeFIAR:Transactiondatainthe“FI

AccountsReceivable”InfoArea. Thequeryshouldhavethefollowinglayout:

Customer Cumulativebalance

1000 1000,00

1100 2000,00

.... ....

a) Open Queries→InfoAreas→FinancialManagement&Controlling

→FinancialAccounting→AccountsReceivableAccounting→Cube

FIAR:Transaction Data → New

Customer Cumulativebalance

1000 1000,00

1100 2000,00

.... ....

Dimensions→Customer→Copythecustomer(characteristic)tothe

rows using drag and drop.

KeyFigure→Copythecumulativebalancetothecolumnsusingdrag

and drop.

2. SavethequeryusingthetechnicalnameGR##QUERY1andthedescription

AC280 QUERY 1 GR##.

a) Choose Save query as → Entry:

Field name Values

Description AC280QUERY1GR##

Technical name GR##QUERY1

Save

Continued on next page

247

Customer	Cumulativebalance
1000	1000,00
1100	2000,00
....	....

Customer	Cumulativebalance
1000	1000,00
1100	2000,00
....	....

Field name	Values
Description	AC280QUERY1GR##
Technical name	GR##QUERY1

第 264 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

3. Execute the query.

a) Execute query

Task 2:

Onceyouhavelookedattheresult,youwanttochangethequery.

Changethequery using the BEx Query Designer.

Hint: Youcanonlymakepermanentchangestothequerybychoosing

“Change Query (Global Definition)”. If you change a query in the

“ChangeQuery(LocalView)”mode,thesystemupdatestheworksheet,

butdoesnotsavethechangesinthequerydefinition.

1. You want tofindout about customersincompanycodeIDES AG(1000)

only,butdonotwanttodisplaythecompanycodeontheworksheet. You

want toinclude theCustomerindustryand theFiscal year/Periodasfree

characteristics. YouonlywanttofindoutabouttheTrade(TRAD),high

Continued on next page

248

第 265 页 / 共 319 页

AC280 Lesson: Queries

tech (HITE), and Engineering industries (MBAU) and the figures for

December2002. You alsowant todisplay theSalesfortheperiodinthe

columns.

a) ChooseChangequery →Changequery(globaldefinition).

Dimensions→Companycode→Copythecompanycodetothefilter

using drag and drop.

Right-clickcompanycode→Restrict→Selectindividualvalues→

Companycode→SelectIDESAG→Selectwithpushbutton→OK

Dimensions → Customer → Copy the industry to the free

characteristics using drag and drop.

Right-clickCustomerindustry→Restrict→Selectindividualvalues

→ Industry Select “ (TRAD) Trade” → Select with pushbutton →

OKSelect“(HITE)Hightech”and“(MBAU)Manufacturing”using

the same procedure

Copy Dimensions → Time → Fiscal year/Period to the free

characteristics using drag and drop. The Fiscal year/Period

characteristicismovedtoo. Youcanremovethischaracteristicinthe

sameway using drag and drop totheleft.

Right-click Fiscal year/Period and choose Restrict→ (click down

arrow). Display Other Values →

Restriction(s): EnterFiscalyear/Period012.2002: Add: OK

Double-click December 2002

OK

Key figure → Copy the sales for the period into the columns using

drag and drop.

2. Save your changed query

Execute the query again.

a) Execute query

3. Onceyouhavelookedattheresultofyourchangedquery,youwanttosee

the result forTrade(TRAD) intheanalysis.

Hint: SetthefilterforTrade(TRAD)intheworksheet.

a) Right-click the customer industry → Find filter value → Select

individualvalues→. Double-clickTRAD→OK.

Continued on next page

249

第 266 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

4. Nowremove the filter for Trade(TRAD).

a) Right-clicktheCustomerindustry→Removefiltervalue.

Task 3:

Tointerpretthedata,youhavetoperformvariousnavigationsteps. Initially,the

customershouldbedisplayedin the rows, andthetwo keyfiguresCumulative

balanceandSalesforperiodshouldbedisplayedinthecolumns. Afteryouhave

performedthenavigationsteps,theworksheetshouldbelaidoutasfollows:

1. Displaytheoverallresultwithoutcharacteristicsinthedrilldown.

Customer's industry Fiscal

year/Period

Customer

Key figures

Cumulativebalance Sales for period

........ ........

Right-clickCustomer→Removedrilldown.

2. Thecustomerindustryshouldappearafterthecustomerinthequery.

Hint: Returntotheinitialdisplay,inwhichthecustomerisdisplayed

intherowsandthetwokeyfigurescumulativebalanceandsalesfor

periodaredisplayedinthecolumns. Thenmakethechanges.

Continued on next page

250

Customer's industry Fiscal year/Period Customer Key figures
Cumulativebalance	Sales for period
........	........

第 267 页 / 共 319 页

AC280 Lesson: Queries

Customer

industry

Fiscal

year/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

.. . . . . . . .. . . . . . . .. . . . . . . . . . . . . . .

a) Right-clickOperatingConcernandchooseReturntoStart.

Right-clickCustomerindustry→Drilldown→Vertically.

3. Exchangethecharacteristicscustomerandcustomerindustry.

Customer

industry

Fiscalyear/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

....... ....... ....... .......

a) Right-click the customer column → Exchange customer with

customer industry

4. RetainthecharacteristicvalueTrade(TRAD)(forcustomerindustry)asa

filtervalueandlistallthecustomersforthisindustryintherow.

Customerindustry Trade(TRAD)

Fiscalyear/period

Customer

Continued on next page

251

Customer industry Fiscal year/period Customer Keyfigures

Customer	Customer industry	Cumulative balance	Salesforperiod
.. . . . . . .	.. . . . . . .	.. . . . . . .	. . . . . . . .

Customer industry Fiscalyear/period Customer Keyfigures
Customer industry	Customer	Cumulative balance	Salesforperiod
.......	.......	.......	.......

第 268 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Key figures

Customer Cumulativebalance Salesforperiod

.......... ............ .........

a) Right-click the customer industry → Find filter value → Select

individualvalues→. Double-clickTRAD→OK.

Double-clicktheCustomerIndustrycolumn. Thisdisplaysalistofall

customerswiththecharacteristicvalueTRAD.

5. Nowdisplay thefiguresforaspecificcustomer.

Customer industry

Fiscal year/period

Customer XYZ

Key figures

Cumulativebalance Sales for period

............ ............

a) Right-clickCustomerindustry→Removefiltervalueanddouble-click

the Customer column.

Right-clickCustomer→Findfiltervalue→Selectindividualvalues.

Double-clickaCustomer(forexample,BeckerBerlin),chooseOK.

6. Within the customer industries, you now have to display the customers

individually. YouhavetodisplaytheKeyfortheindustryandthekeyforthe

customers. Sorttheindustriesindescendingorderbykey. Alwayssuppress

the result rows for the customers. For the industry, you want an overall

resultdisplayedforthecumulativebalanceandfortheperiodsales.

Continued on next page

252

Customer	Cumulativebalance	Salesforperiod
..........	............	.........

Customer industry Fiscal year/period Customer Key figures	XYZ
Cumulativebalance	Sales for period
............	............

第 269 页 / 共 319 页

AC280 Lesson: Queries

Customer

industry

Fiscalyear/period

Customer

Keyfigures

Customer Customer Cumulative Salesforperiod

industry balance

Trade 1001 EUR1,500.00 1,000.00EUR

1002 980.00EUR 910.00EUR

1033 ...... ......

...... ...... ......

High tech 1460 3,000.00EUR 2,500.00EUR

...... ...... ......

Engineering 1000 EUR5,670.80 3,215.80EUR

...... ...... ......

Overallresult XX,XXX.XX XX,XXX.XX

EUR EUR

a) Remove filter value for customer

Right-clickCustomerandchooseRemovefiltervalue.

• Double-clickCustomerIndustry. Thisdrillsdowntheindividual

customer industries in the row.

• Double-click Customer. This drills down the individual

customersintherow,totherightofthecustomerindustries.

• Right-click the Customer industry column and choose

Customerindustry→Displayas→Key

• Right-click the Customer industry column and choose

Customerindustry→Suppressresultsrows→Conditional.

• Right-clicktheCustomerindustrycolumn. Sortindescending

order by key.

• SelecttheCustomer columnwiththeright-handmouseand

chooseCustomer →Displayas→Key

• Select the Customer column with theright-hand mouseand

chooseCustomer→SuppressResultsRows→

Always

Continued on next page

253

Customer industry Fiscalyear/period Customer Keyfigures
Customer industry	Customer	Cumulative balance	Salesforperiod
Trade	1001	EUR1,500.00	1,000.00EUR
	1002	980.00EUR	910.00EUR
	1033	......	......
	......	......	......
High tech	1460	3,000.00EUR	2,500.00EUR
	......	......	......
Engineering	1000	EUR5,670.80	3,215.80EUR
	......	......	......
Overallresult		XX,XXX.XX EUR	XX,XXX.XX EUR

第 270 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

7. When you created query AC280 QUERY 1 GR##, you filtered

characteristics. Whenyouareperformingtheanalysis,youwanttodisplay

the information that you did not filter (for example, author, last changed

by, infoprovider, technicalname, andso forth). Add thetext elementsto

the worksheet.

a) Layout → Displaytext elements →All.

8. Savetheresultsofquery AC280QUERY1GR##asanewworkbookin

yourfavoriteslist. CalltheworkbookQuery1GR##Result.

a) Save query Save as new workbook

Inthe“Description”field,enterQuery1GR##Result,andchooseSave.

Task 4:

Youwanttofindoutyourcustomer'ssalesfortheperiod. Inthenextexerciseyou

willcreateandusecharacteristicvariablesforthecustomercharacteristic

1. DefineanewqueryusingtheInfoCubeFIAR:Transactiondatainthe“FI

AccountsReceivable”InfoArea. Youwanttofilterthequeryusingcompany

codeIDESAG(1000). EnterFiscalyear/Periodasfreecharacteristics. You

wanttodisplaythecustomerintherowsandtheperiodsalesinthecolumns.

Customer Sales for period

1000 1000,00

1100 2000,00

... ...

a) Dimensions→Companycode→Characteristic. Dragthecompany

code to the filter usingdrag and drop.

Right-clickcompanycode→Restrict →Selectindividualvalues→

Companycode→SelectIDESAG→Selectwithpushbutton→OK

Copy Dimensions → Time → Fiscal year/Period to the free

characteristics using drag and drop. The Fiscal year/Period

characteristicismovedtoo. Youcanremovethischaracteristicinthe

sameway usingdraganddrop totheleft.

Choose Dimensions→ Customer. Dragthecustomer characteristic

to the rows using drag and drop.

Keyfigure: Copythesalesfortheperiodintothecolumnsusingdrag

and drop.

Continued on next page

254

Customer	Sales for period
1000	1000,00
1100	2000,00
...	...

第 271 页 / 共 319 页

AC280 Lesson: Queries

2. SavethequeryusingthetechnicalnameGR##QUERY2andthedescription

AC280 QUERY 2 GR##.

a) Choose Save query as → Entry:

Field name Values

Description AC280 QUERY 2GR##

Technical name GR##QUERY2

OK

3. Execute the query.

a) Execute query

4. The query output for AC280 QUERY 2 GR## lists all customers in the

companycodeIDESAG.Inthenextoutput,youwanttobeabletostructure

thedisplayofthecustomerareaflexibly. YoudonotdecideonFromandTo

customers until you perform the analysis.

Change query AC280 QUERY 2 GR##. Define a variable for the

characteristicCustomer(InfoObject0CUSTOMER),whichyouwillneedto

selectcustomers. Thisvariableisarangethatyouhavetoeditmanuallyor

byusingdefaultvalues. Youhavetodefineitasmandatoryandreadyfor

inputanditshouldbepossibletochangeitwhenyounavigateinthequery.

Use the following naming conventions:

Variablename: DEBVB##Description: From/ToCustomerGR##

Continued on next page

255

Field name	Values
Description	AC280 QUERY 2GR##
Technical name	GR##QUERY2

第 272 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Enter1000asthedefaultvalueand2000astheTodefaultvalue.

a) ChooseChangequery →Changequery(globaldefinition).

Expand the node for the dimension Customer. Expand the node

for the characterisitc Customer.

Place the cursor on characteristic value variables for customer.

Right-click

Characteristic value variables for customer → New variable →

Continue →

Variable name:DEBVB##:

Description: From/ToCustomer-GR##

Edit manually/Copy default value.

→ Continue

Variablerepresented: Choose Interval

IntheVariableentryisfield, chooseObligatory.

Accept Ready for input .

SelectChangeable for Query Navigation.

→ Continue.

Enter1000asthedefaultvalueand2000astheTodefaultvalue.

→ Continue → Exit.

Expand the node for characteristic variables (customer) →

Characteristicvaluevariables. CopyFrom/tocustomerGR##tothe

rows using drag and drop.

5. Save query AC280 QUERY 2 GR##.

a) Save query AC280 QUERY 2 GR##

6. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000.

a) ExecutequeryAC280QUERY2GR## . Selectcustomers1000to

3000.

Continued on next page

256

第 273 页 / 共 319 页

AC280 Lesson: Queries

Task 5:

Nowyou will beworking with text variables. Youwantto displaysalesfor a

specificfiscalyear/periodandthecorrespondingheading(forexample,12/2002)

as follows:

Customer Sales 12/2002

1000 1000,00

1100 2000,00

.... ....

1. Remove the characteristic Fiscal Year/Period from the category Free

Characteristics and Key Figures from the columns. Create a new

characteristicvaluevariableforthefiscalyear/period.

Variablename: PERYR##; description: Period/Year##.

Processed by: Manual entry default value.

Other entries: Individual value, Entry obligatory, Ready for input and

Changeableduringquerynavigation. Enter12/2002asthedefaultvalue.

a) ChooseChangequery →Changequery(globaldefinition).

Double-click the characteristic variables icon below the Fiscal

year/Period

Continue.

Entervariablename: PERYR##anddescription: Period/Year##

Processedby: Editmanually/Copydefaultvalue.

Continue.

Variablerepresented: EnterIndividualvalue

Variable entry is: Enter Obligatory

Accept Ready for input.

Continue.

Enter 12/2002 as the default value.

Continue.

Exit.

Continued on next page

257

Customer	Sales 12/2002
1000	1000,00
1100	2000,00
....	....

第 274 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

2. Createanewstructureforthecolumns. Thestructureshouldcontainanew

selection. Thefirstcomponentofthenewselectionforthecolumnstructure

shouldcontainthesalesfor theperiodandthesecondpartshouldcontain

yourvariableforthefiscalyear/periodPeriod/Year##.

Choose Select text variable.

Create a new text variable. Variable name: PYRTXT##, Description:

Per/YearText##, Processedby: Replacementpath.

Continued on next page

258

第 275 页 / 共 319 页

AC280 Lesson: Queries

Other entries: characteristic Fiscal year/Period (0FISCPER), replace

variable with External attribute of key, From value, Offset start: 0003,

Offset length0008. EnterSales&PYRTXT##& asadescriptionfor the

new selection.

a) IntheColumnsarea,removetheKeyFigurescolumnsbydragging

KeyFiguresintotheleftcolumnusingdraganddrop.

PlacethecursorthecolumnsandchooseNewStructure fromthe

context menu.

PlacethecursoronStructureandchooseNewSelectionfromthe

context menu.

MoveSalesforperiodtotherightusingdraganddrop.

Dimensions → Time → Characteristic Fiscal Year/Period . Copy

CharacteristicvaluevariablePeriod/Year##totherightbelowyour

keyfigureusingdraganddrop. TheFiscalyear/Periodcharacteristicis

movedtoo. Removethecharacteristicagainbydraggingittotheleft.

Choose Select text variable .

Create anewtext variable: ChooseNew.

→ Continue

Variable name:PYRTXT##.

Description: Per/Year Text ##

Processed by: Replacement path

→ Continue

Characteristic: Fiscalyear/period(0FISCPER)

→ Continue

Replacevariablewith: Externalattributeofkey

Offset Start: 0003 Offset Length:0008.

Exit.

Enter Sales &PYRTXT##&as a description for the new

selection.Choose OK.

3. Save query AC280 QUERY 2 GR##.

a) Savequery AC280 QUERY 2 GR## .

Continued on next page

259

第 276 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

4. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000and

enter 011/2002 as the period/year ##.

a) Executequery AC280QUERY2GR## .

Selectcustomers1000to3000andenter011/2002astheperiod/year##.

260

第 277 页 / 共 319 页

AC280 Lesson: Queries

Lesson Summary

You should now be able to:

• Create and change queries

• Explainandusethenavigationoptionsprovidedforquerydisplay

• Save query output in a workbook

• Defineandusecharacteristicvalueandtextvariables

261

第 278 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Lesson: Exceptions, Conditions, and Other Query

Properties

Lesson Overview

Oncetheyhavecreatedqueries,employeesinFinancialAccountingwanttowork

withexceptions,conditions,andsender/receiverreportassignments.

Lesson Objectives

After completingthislesson, you will beable to:

• Defineconditionsandexceptionsandusethemwhenyoudisplayaquery

• Tailorcertainpropertiesofqueriestoyourrequirements

• Add queries to existing workbooks

Business Example

Oncetheyhavecreatedqueries,employeesinFinancialAccountingwanttowork

with exceptions and conditions.

Figure 113: Defining Exceptions

Youcandefineaglobalexceptionatquerylevel. Thisthenappliestothequery

in all workbooks.

262

第 279 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

Procedure:

• Using Drag&Drop, drag the required key figures to the directories inthe

query definition.

• To display the entry screen on which you define exceptions, choose

Exception → New exception.

Figure 114: Exception Attributes: Cell Restrictions

Procedure when defining an exception:

• IntheDescriptionfield,enteranamefortheexception.

• Enterthekeyfigurestowhichyouwanttoapplytheexception. Thereare

twowaysofdoingthis: Youcanperformtheexceptionforallthekeyfigures

inthequerydefinition,oryoucandisplaytheexceptionforaspecifickey

figure.

• OntheExceptionValuestabpage,specifythethresholdvaluesorrangesto

whichyouwant toassignamessagepriority. Messageprioritiesareused

to preassign the color values that are shown in the query result. As the

difference between the query result and the predefined values increases,

the color displayed changes and can become deeper to reflect a higher

“warning”level. Thereareninedifferent shadesof green, yellowandred

(traffic light colors).

• Todefineanewinterval,chooseNewandentertheFromandTovaluesin

the corresponding fields. Thereis aswitch to enableyou tounderlay the

intervalwithacolor. Onceyouhaveenteredtheappropriatewarningcolor,

chooseCopytocopythenewexceptioninterval.

• Ifthereareconsecutiveintervalswithoverlappinglimit values, thisvalue

isdisplayedonlyonceandisunderlaidwiththecolorthatrepresentsthe

highestwarninglevel,thusdisplayingtheworstcasescenario.

263

第 280 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Figure 115: Exception: Result in Worksheet

Thequeryresultshowsthevaluesdefinedintheexceptionunderlaidwithvarious

colors. Thecolorsrepresent thedegreeof variance.

Tochangetheexception, chooseExceptionsandChangeexceptioninthequery

definition. Thesystemthenprovidesallthefunctionsusedtodefineexceptions

again.

You can also enter exceptionsin theworksheet(right-click intheresultsarea-

exceptions). However, theseexceptionscanonlybeusedlocally inthecurrent

query.

If you have large quantities of data, you can also process exceptions in the

background. Todothis,definetherelevantsettingsintheReportingAgent. The

systemthendisplaysandlogstheresultsinthealertmonitor.

264

第 281 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

Figure 116: Defining a Condition

Procedurewhencreatingacondition: IntheDescriptionfield,enteranamefor

yourcondition. MakesurethattheActivecheckboxisselected(thisactivatesthe

condition). IntheEvaluateconditionsbelowfor... tofield,enterthecharacteristics

towhichyouwishtoapplythecondition. Therearetwooptions:

All characteristics independent in drilldown: The condition is used for all

characteristics in the query. You can navigate freely within the worksheet to

change thecharacteristicsdisplay, andtheconditionwill betaken intoaccount

for all characteristics.

Singlecharacteristicsandcharacteristiccombinations: Ifyouselectthisoption,

thesystemdisplaysallthecharacteristicsforthequeryinthebottomwindow. In

thiswindow,youcanchooseoneormorecharacteristicsforwhichyouwishto

displaythecondition. Selectingcharacteristicsinthiswayactsasafilterfor

thecondition. Forcharacteristicsthatarenotselectedandthusnotrestricted,the

systemcontinuestodisplayall thevaluesinthequery.

DefinetheconditioninthePresentallvaluesthatfulfilatleastoneofthefollowing

conditionsfieldatthebottomofthescreen. DefineaconditionbychoosingNew

and choosing the key figure and then the operator in the pulldown menus (for

example,TopN,BottomN,islessthanorequalto,isgreaterthanorequalto). In

theValuesfield,entertheappropriatemeasure(amount,figures,orpercentage).

Tocopyyourcondition,chooseCopyandconfirmyourentrybychoosingOK.

265

第 282 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Figure 117: Condition: Result in Worksheet

Ontheworksheet,thesystemdisplaysonlythevaluesthatyourequire.

Tochangethecondition, chooseConditionsandChangeconditioninthequery

definition. Thesystemagainprovidesallthefunctionsusedtodefineconditions.

You can also enter conditions in the worksheet (right-click in the results area

-conditions). However,theseconditionscanonlybeusedlocallyinthecurrent

query.

Figure 118: Properties of q ueries: Interaction

266

第 283 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

In the standard system, interactive functions such as navigate or refresh are

permitted in a workbook. If you want to prevent users from navigating in the

queryresultsorfromchangingtheviewofthedata,deactivatethefunctionAllow

Interactive Functions.

If input variables are used in a query, you can enter values for variables when

adding the query to the workbook or accept the default values. If you want to

saveandreuseanyvariablevalues,activatethisfunction. Inthisway,youdonot

needtoenterthevaluesforthevariableseachtimeyourefreshthedata. Withthe

default settings, this function is not activated.

Ifyouwantyourquerytoberefreshedwhenyouopentheworkbook,activatethe

corresponding function.

Figure 119: Adding Queries

Queries are always inserted in the workbook at the point where the cursor is

located. QueriesfromdifferentInfoCubescanbeinsertedinthesameworkbook.

Ifyouhaveinsertedseveralqueriesinaworkbook,switchingthedrilldownwithin

aquerycanleadtoaconflictwiththeoperatingconcernofanotherquery. This

appliestotheareabesidethequerytobechangedaswellastotheareabeneathit.

Thesystemrecognizestheconflictandoffersyouthreeoptions:

• Youcanlettheoperatingconcernoftheotherquerybeoverwritten.

• Youcanhavecellsinsertedbeneaththequeryorbesideit.

• Youcanhaveentire linesor columnsinserted.

267

第 284 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

268

第 285 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

Exercise 13: Insight into SAP BW: Queries

Exercise Objectives

After completingthisexercise, youwillbeableto:

• Createexceptionsandconditionsandusethemforresults

• Tailer queries to your needs

• Add queries to existing workbooks

Business Example

Youhavethetaskofcreatingvariousquerydefinitions. Youwanttouseadditional

analysis functions in the query results.

Hint: The##symbolisaplaceholderforyourgroup(01,02,03... and

so on).

Task 1:

Youwanttodisplaythetop10customersalesfiguresfor thisperiod. Youalso

wanttohighlightsalesthatfallbetween200,000EURand400,000EUR.

1. ChangequeryAC280QUERY2GR##. Createanewconditionwiththe

description GR##-CONDITION. You want to apply this condition to the

characteristicCustomer(0CUSTOM)only. EntertheoperatorsTopNand10

forthekeyfigureSales&PYRTXT##&. AcceptyourentriesandchooseOK.

2. CreateanewexceptionwiththedescriptionGR##-EXCEPTION.Enterthe

structureSales&PYRTXT##&. Createthevaluesfortheexception: From

value200,000, Tovalue400,000, andanalertlevel(forexample,green2,

good).

Accept your entries. To restrict the cells (area of validity for the

exception), choose All (recommended for relativequantities)and choose

the characteristics Customer and All (all balances). Accept your entries

and choose OK.

3. Save query AC280 QUERY 2 GR##.

4. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000and

enter 012/2002 as the period/year ##.

5. Savetheresultsof query AC280QUERY2GR##asanewworkbookin

yourfavoriteslist. CalltheworkbookQuery2GR##Result.

Continued on next page

269

第 286 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Task 2:

Optional

1. Younowwanttochangesomepropertiesofyouranalysisinyourworksheet.

ExecuteyourqueryAC280QUERY1GR##(##=yourgrpupnumber). As

thefiltervalueforyourcustomers,enterthevaluerange1000through2000

(BeckerBerlinthroughCarborGmbH).Deactivatetheinteractivefunctions

of your query output.

Once you have viewed your workbook again and have tested the new

settings,activatetheinteractivefunctionsagainandthenremovethefilter

value for the customers.

2. AddthequeryyoucreatedAC280QUERY2GR##(##=yourgroupnumber)

totheworksheetalongsidequeryAC280QUERY1GR##(##=yourgroup

number).

Hint: Toinsertthesecondquery, selectacell inExcel columnF

alongsideyourqueryalreadylocatedintheworksheet(forexample,

line 1, column F)

Entercustomer1000tocustomer2000andperiod012inyear2002. Asthe

filtervalueforyourcustomersforAC280QUERY1GR##,enterthevalue

range1000through2000(BeckerBerlinthroughCarborGmbH).Remove

exception GR##-EXCEPTION from the second query (AC280 QUERY2

GR##).

Update (refresh) your second query (AC280 QUERY2 GR##). You are

requiredtoenterthevalueofthevariableforperiod/year. Accepttheentries

and execute the query again.

Save the results of the two queries jointly as a new workbook with the

description 2 Queries GR##.

3. Nowexecuteyour threeworkbooksconsecutively:

• Result Query 1 GR##

• Result Query 2 GR## and

• 2 Queries GR##

270

第 287 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

Solution 13: Insight into SAP BW: Queries

Task 1:

Youwanttodisplaythetop10customersalesfiguresfor thisperiod. Youalso

wanttohighlightsalesthatfallbetween200,000EURand400,000EUR.

1. ChangequeryAC280QUERY2GR##. Createanewconditionwiththe

description GR##-CONDITION. You want to apply this condition to the

characteristicCustomer(0CUSTOM)only. EntertheoperatorsTopNand10

forthekeyfigureSales&PYRTXT##&. AcceptyourentriesandchooseOK.

a) ChooseChangequery →Changequery(globaldefinition).

Choose Condition →New condition.

EnterthedescriptionG##-CONDITIONandselecttheActivefield.

ChooseEvaluateconditionsbelowfor→Individual characteristics

andcharacteristiccombinations. SelectthefieldbeforeCustomer.

Choose Present all values that fulfil at least one of the following

conditions: New.

→ Enter the following:

Key figures Operator Values

Sales&PYRTXT& Top N 10

Choose Copy.

Choose OK.

2. CreateanewexceptionwiththedescriptionGR##-EXCEPTION.Enterthe

structureSales&PYRTXT##&. Createthevaluesfortheexception: From

value200,000, Tovalue400,000, andanalertlevel(forexample,green2,

good).

Continued on next page

271

Key figures	Operator	Values
Sales&PYRTXT&	Top N	10

第 288 页 / 共 319 页

Unit5: InsightintoBWforFinancialAccountingQueries AC280

Accept your entries. To restrict the cells (area of validity for the

exception), choose All (recommended forrelative quantities) and choose

the characteristics Customer and All (all balances). Accept your entries

and choose OK.

a) ChoosetheExceptionbutton andthenchooseNewexception.

EnterthedescriptionG##-EXCEPTIONandselecttheActivefield.

ChooseEvaluationfor→Structure→Sales&PYRTXT##&.

ChooseValuesforexceptiontabpage→Newandenterthefollowing

in the columns:

From To Alert level

200000 400000 Good 2

Copy

OntheException: AreaorValiditytabpage, chooseAreaofvalidity

for all characteristics that are not shown→ All (recommended for

relative quantities).

Choose New.

Characteristic: Customer Operator: All

Choose Copy.

Choose OK.

3. Save query AC280 QUERY 2 GR##.

a) Save query

4. ExecutequeryAC280QUERY2GR##. Selectcustomers1000to3000and

enter 012/2002 as the period/year ##.

a) Execute query

5. Savetheresultsofquery AC280QUERY2GR##asanewworkbookin

yourfavoriteslist. CalltheworkbookQuery2GR##Result.

a) Choose Save → Save as new workbook in your favorites list.

Description: Query 2 GR## Result.

Continued on next page

272

From	To	Alert level
200000	400000	Good 2

第 289 页 / 共 319 页

AC280 Lesson: Exceptions, Conditions, and Other Query Properties

Task 2:

Optional

1. Younowwanttochangesomepropertiesofyouranalysisinyourworksheet.

ExecuteyourqueryAC280QUERY1GR##(##=yourgrpupnumber). As

thefiltervalueforyourcustomers,enterthevaluerange1000through2000

(BeckerBerlinthroughCarborGmbH).Deactivatetheinteractivefunctions

of your query output.

Once you have viewed your workbook again and have tested the new

settings,activatetheinteractivefunctionsagainandthenremovethefilter

value for the customers.

a) ExecuteyourqueryAC280QUERY1GR##(##=yourgrpupnumber).

b) OpentheBExAnalyzer(chooseSAPMenu→BusinessExplorer→

Analyzer).

Open BusinessExplorer → Queries

PlaceyourcursoronyourqueryAC280QUERY1GR##→andexecute

it (by choosing OK).

c) As the filter value for your customers, enter the value range 1000

through2000(BeckerBerlinthroughCarborGmbH).

SelectCustomerandchooseFindFilterValuefromthecontextmenu.

Selection for customer: Value Range

Placeyourcursoronthefirstentry(=Becker). Usingtherightmouse,

chooseTechnicalNamesfromthecontextmenu.

Double-click 1000 and scroll down through the list until you reach

2000. Double-click 2000. Choose OK.

d) Deactivatetheinteractivefunctionsofyourqueryoutput.

Withtherightmouse,selectanycellandchooseOperatingConcern

→ Properties

TitleElement Field Name Value

Interaction Allow Interactive Empty

Functions

Choose OK.

e) Now test the new setting.

By way of example, you could try to remove the filter value for the

customers. Thisis no longer possible.

Continued on next page

273

TitleElement	Field Name	Value
Interaction	Allow Interactive Functions	Empty