基于BS的企业报表管理系统-外文翻译

0附录外文资料SeveraltoolsareavailableinExcel2007formanagingdatainyourPivotTablereport.Usingthesetools,youcancontrolhowdataisrefreshed,utilized,andsavedinyourreports.Forexample,youcansetupautomaticdatarefreshesatspecifiedintervals,togglewhethertheunderlyingdatasetissavedwiththereport,enableupdatequeriestoruninthebackground,configuremultiplePivotTablestosharethesamesourcedata,anddefinehowPivotTablecellsreferencedoutsidethePivotTablearehandled.ThischaptercoversallthesedatamanagementfeaturesavailablewithPivotTablereportsandprovidesaperspectiveonhowyoucanusethesevariousfeaturestosimplifythemanagementandoperationofyourreports.IstartthischapterbydescribinghowyoucanusethevariousconnectionoptionsavailableintheConnectionPropertiesdialogbox,includingbackgroundrefreshsettingsandautomaticrefreshoptions.AfterthatIcoverthedataoptionsundertheDatatabofthePivotTableOptionsdialogbox.Here,IreviewhowyoucancontrolwhethertheunderlyingdatasetissavedwiththereportandhowPivotTablecellsarereferencedoutsidethereport.Next,IshowyouhowtocopyPivotTablereportsandconfigurethemtoeithersharethesamedatasetoruseaseparatedataset.Iconcludethischapterwithareal-worldexercisethathelpsbringtogethersomeofthetopicscoveredinthischapterintoarealisticscenariothatyoumightactuallyencounterinyourownorganization.BeforeYouBeginAlltheexamplesinthischapter,includingthe“TryingItOutintheRealWorld”exercise,utilizetheSQLqueryandPivotTablereportoutlinedinthissection.Ifyouplantofollowalongwiththematerialandexamplesthroughoutthischapter,IrecommendthatyoustartbycreatingthisPivotTablereportthataccessestheAdventureWorksDWdatabase.CompletethesestepstocreatethePivotTablereportandfollowalongwiththeexamplesusedthroughoutthischapter:11.FromExcel,chooseDataFromOtherSourcesFromMicrosoftQuerytobringuptheChooseDataSourcedialogbox.2.WhentheChooseDataSourcedialogboxappears,verifythatUsetheQueryWizardtoCreate/EditQueriesboxisunchecked,selecttheAdventureWorksDataWarehousedatasource,andclickOK.3.WhentheMicrosoftQueryprogramislaunched,youarepresentedwiththeAddTablesdialogbox.ClosethisdialogboxandclicktheSQLbutton.4.PasteortypethefollowingqueryintotheSQLdialogbox,andthenclickOK:SELECTtme.CalendarYearASYear,tme.EnglishMonthNameASMonth,cur.CurrencyNameASCurrencyName,fcr.EndOfDayRateASEndOfDayRateFROMFactCurrencyRatefcrINNERJOINDimCurrencycurONfcr.CurrencyKey=cur.CurrencyKeyINNERJOINDimTimetmeONfcr.TimeKey=tme.TimeKeyWHEREtme.DayNumberofMonth=1ANDtme.CalendarYear2001ONTHEWEBYoucandownloadthech12_/go/excelreporting/2007.LookforthisdocumentineithertheChap12.zipfileorChap12directory,dependingonwhich.zipfileyoudownload.5.ClickOKtoacknowledgethatthequerycannotbedisplayedgraphically.6.ClicktheReturnDatatoMicrosoftExcelbuttontoreturnthedatatoExcelandcontinue.7.WhentheImportDataDialogboxappears,choosePivotTableReportandclickOKtocreatethePivotTable.28.DragCurrencyNametotheReportFilterarea,MonthtotheRowLabelsarea,YeartotheColumnLabelsarea,andEndOfDayRatetotheValuesarea.9.Left-clickCurrencyNameincellB1oftheReportLayoutareaandchooseEUROfromthedrop-downmenutoshowonlytheend-of-dayratesforEuros.10.Right-clickthePivotTableandchoosePivotTableOptionsfromthepop-upmenutobringupthePivotTableOptionsdialogbox.ClicktheTotals&Filterstab,unchecktheoptionsShowGrandTotalsforRowsandShowGrandTotalsforColumns,andthenclickOKtoclosethedialogboxandsuppressthegrandtotalcalculationforrowsandcolumnsinthePivotTablereport.VerifythatyourPivotTablereportlookslikeFigure12-1.SettingConnectionPropertiesYoucanconfiguretheconnectionpropertiesforaPivotTablefromtheConnectionPropertiesdialogbox.ClickthePivotTablereporttoselectitandthenchoosePivot-TableToolsRefreshConnectionPropertiestobringupthisdialogbox.Alternatively,youcanchooseDataPropertiestobringuptheConnectionPropertiesdialogboxshowninFigure12-2.Thisdialogboxisseparatedintotwotabs:Usage(seeFigure12-2)andDefinition(seeFigure12-4).TheUsagetabisusedtoconfigurerefreshoperationsforPivotTablereportsthataccessOLTPandOLAPdatasources.Therearealsosectionsinthistabofthedialogboxforcontrollinghowformattinganddrill-throughfeaturesarehandledforOLAPcubes.Theconnectionfile,theconnectionstring,andtheSQLqueryareallstoredintheDefinitiontaboftheConnectionPropertiesdialogbox.ButtonsforchangingandexportingtheConnectionFile,accessingtheMicrosoftQueryprogram,andconfiguringtheauthenticationsettingsforExcelServicesarealsoavailablefromthistabofthedialogbox.ConfiguringUsageOptionsThedataoptionsintheUsagetaboftheConnectionPropertiesdialogboxinFigure12-32areseparatedintofoursections:RefreshControl,OLAPServerFormatting,OLAPDrillThrough,andLanguage.Onlythefirstsection,RefreshControl,appliestoOLTPdatasources.TheremainingthreesectionsallapplytoOLAPdatasourcesandarecoveredintheonlineOLAPchapter.TheoptionsunderRefreshControlareusedtoconfigurehowqueriesareexecuted,howoftendataisrefreshed,andwhetherthePivotTablereportisautomaticallyupdatedwhentheExcelworkbookwiththePivotTablereportisinitiallyopened.Table12-1providesbriefadescriptionofeachoptionunderRefreshControl.EnableBackgroundRefreshIfyouareimportingalargedatasetand/oraccessingthedatasourcethrougharemoteconnection,suchasaVirtualPrivateNetwork(VPN),thedatarefreshmighttakeawhiletocomplete.Whilethenewdatasetisbeingloadedintoyourreport,youcannotperformanyoperationsinExcel,becausetherefreshoperationhaltsallactivitiesuntiltherefreshhasfullycompleted.YoucanworkaroundthisconstraintbycheckingtheEnableBackgroundRefreshoption(seeFigure12-2).Checkingthisoptionconfigurestherefreshoperationtoruninthebackgroundsothatyoucangoonaboutyourreportoperationswhilethedatasetisbeingupdated.WarningRefreshoperationsthatarerunninginthebackgroundmaynotbenoticedbyPivotTablereportuserswhoarenotfamiliarwiththeEnableBackgroundRefreshoption.Besuretoreviewhowthissettingworksinordertoensurethatthereportuserunderstandsthatthereporthasntyetbeenrefreshed.ReportUpdatingOncethedatasetisfullyloadedintoExcel,thePivotTablereportisupdated.Duringthisupdate,Exceladdsanynewitemstoreportfields,recomputesCalculatedFieldandCalculatedItemformulas,andrefreshesfieldsintheReportLayoutareaandPivot-Table4FieldListdialogbox.ThisupdatealsofreezestheExcelsession,andunliketherefreshoperation,itcannotberuninthebackground.However,thisperiodistypicallybrief,soitsunlikelythatitwillnegativelyaffectthereportuser.Tip:KeepinmindthatthereportupdateprocessingtimeiscloselyrelatedtothesizeofthedatasetandthenumberofCalculatedFieldsandCalculatedItems.CheckingRefreshStatusAspinningglobeappearsintheStatusBar,locatedatthebottom-leftoftheExcelworkbook,wheneverabackgroundrefreshoperationisrunning.ClickingthespinningglobeopenstheExternalDataRefreshStatusdialogbox(seeFigure12-3),whereyoucanviewallthequeriesandassociatedPivotTablereportsthatarebeingupdated.YoucanalsobringuptheExternalDataRefreshStatusdialogboxbychoosingPivotTableToolsRefreshRefreshStatus.RefreshEveryTheRefreshEveryoptionschedulesthereporttoberefreshedeverynminutes.Thevalueforminutesmustbeanintegerbetween1and999.Thisoptionisusefulforreportsdesignedtoregularlymonitortransactionsorstatistics.Forexample,adataentryprocessingmanagermightwanttomonitorthetotalnumberoftransactionsprocessedthroughoutthedayandtheaveragetransactionprocessedeachhour.Usingthisoption,thereportcanautomaticallybeupdatedevery15minutestoshowtheupdatedtransactionsprocessedbythedataentrystaff.RefreshDataWhenOpeningtheFileChecktheRefreshDataWhenOpeningtheFileoptiontoautomaticallyrefreshthePivotTablereportwhentheExcelworkbookcontainingthePivotTableisfirstopened.ThisfeaturecanbeusefulwhenthereportuserhasnoexperiencewithPivotTablereports,5becauseitdoesntrequiretheusertotakeanyactiontorefreshthereport.Whenthisoptionischecked,arefreshoperationisautomaticallytriggeredforthereport.TheRefreshDataWhenOpeningtheFileoptionshouldnotbecheckedwhenareportuserneedstokeeparchivedcopiesofthereportdatapriortoitbeingautomaticallyrefreshed,becausereportusersarelikelytoforgettosaveacopyofthereportbeforethenewdatabecomesavailable.Thiscanhappenwhenthereportdataisarchivedatmonth-end,forexample.YoushouldalsocarefullyevaluatetheuseofthisoptionwhenthePivotTablereporttakesalongtimetorefresh.ConfiguringDefinitionOptionsSeveralconnectionsettingscanbeadjustedfromtheDefinitiontaboftheConnectionPropertiesdialogbox.LookingatFigure12-4,youcanseethattherearenumerouscomponentsinthistabofthedialogboxthatarecomprisedofpanes,buttons,fields,andcheckboxes.TheConnectionTypelabeldisplaysthetypeofexternaldatasourceconnectionbeingused.ClickingtheBrowsebuttonenablesyoutochangetheconnectionfileforthereport(changingthisconnectionfileenablesyoutoutilizeadifferentconnectionstringandSQLqueryforthereport).TheConnectionStringpanecontainstheconnectionstringinformationfortheexternaldatasource.TheCommandTextpanecontainsthetablenameorSQLquerybeingusedforthePivotTablereport.ClickingAuthenticationSettingsbringsuptheExcelServicesAuthenticationSettingsdialogbox,whereyoucanconfigurethePivotTablereportsecurityforMicrosoftSharePoint2007.ClickingtheEditQuerybuttonstartstheMicrosoftQueryprogram,whereyoucaneditanSQLqueryfortheexternaldatasource.LookingattheConnectionInformationTheconnectioninformationforthedatasourceisstoredinaconnectionfile.Thisfilecontainsinformationonthetypeofdatabeingaccessed,thelocationofthatdata,theauthenticationinformation(ifapplicable),andtheobjectordatatoextract(forexample,thedatabasetableorSQLquery).TheconnectioninformationisstoredintheConnectionString6pane.Here,youcanmanuallychangetheserverconnection,thedefaultdatabase,andthepassword.ClickingtheExportConnectionFilebuttoncreatesanOfficeDataConnection(*.odc)filethatcontainstheconnectioninformationandtheSQLquerythatisbeingusedforthePivotTablereport.SavePasswordTheSavePasswordoptiontoggleswhetherthepasswordtotheexternaldatasourceissavedwiththereport.Althoughthisoptionmaybeaccessible,itisonlyrelevantwhenthePivotTableaccessesanexternaldatasourcewhereapasswordisactuallyrequired.Forexample,ifthePivotTablereportusesanSQLdatabaseasitsdatasource,andthedatabaseusesanSQLServersecurityauthenticationtype,thisoptiontoggleswhetherthepasswordissavedalongwiththeuserlogin.KeepinmindthatifWindowsAuthenticationisusedinsteadofSQLServerauthentication,thisSavePasswordoptionhasnoeffectonthePivotTable,eventhoughitisstillaccessible.UncheckthisoptionifyouwanttheusertospecifyapasswordinordertorefreshthereportortoedittheSQLquery.Youcanalsouncheckthisoptionwhenyouwanttochangethedatasource.7中文译文在Excel2007的数据透视表中，可使用多种工具管理数据。利用这些工具，您可以控制数据刷新、使用的方式，并保存在报表中。例如，您可以按指定的时间间隔设置数据自动刷新或切换，而不必考虑底层数据集是否被保存在报表中。这使更新查询能够在后台运行、配置多个数据透视表共享相同的源数据，并定义数据透视表的外部引用的单元格数据透视表的处理方式。本章涵盖了所有可提供这些数据透视表的数据管理功能报表，并提供有关使用这些不同的功能透视简化报表管理和运作的介绍。本章以描述如何使用可在连接属性对话框中的各种连接选项开篇，选项包括背景刷新设置和自动刷新选项。之后涉及到数据透视表选项对话框下的数据选项卡。此处，我们回顾一下不考虑底层数据集是否被保存在报表中的控制方式和报表外部引用数据透视表单元格的方式。接下来，我将展示如何复制数据透视报表，将报表配置到共享数据源或不同的数据源。最后，我以一个实例来结束本章，实例综合运用这一章涉及到的内容，在今后的开发中可能对你们有所帮助。开始之前本章中包括“实战演练”在内的所有例子，使用本节所述的SQL查询和数据透视表。如果准备仿照整章的材料和例子，应当首先创建一个访问AdventureWorksDW数据库的数据透视表。完成下列步骤来创建数据透视表并仿照全章使用的例子：1.在Excel中，选择数据来自其他数据源从MicrosoftQuery弹出选择数据源对话框。2.当选择数据源对话框出现时，确认“使用查询向导来创建/编辑查询”选项框未选中，选择AdventureWorks数据仓库的数据源，然后单击确定。3.当MicrosoftQuery程序启动时，呈现界面添加表对话框。关闭此对话框并单击SQL按钮。4.在SQL对话框中粘贴或键入以下查询语句，然后单击确定。SELECTtme.CalendarYearASYear,tme.EnglishMonthNameASMonth,cur.CurrencyNameASCurrencyName,fcr.EndOfDayRateASEndOfDayRateFROMFactCurrencyRatefcrINNERJOINDimCurrencycurONfcr.CurrencyKey=cur.CurrencyKeyINNERJOINDimTimetmeONfcr.TimeKey=tme.TimeKeyWHEREtme.DayNumberofMonth=1ANDtme.CalendarYear2001互联网您可以在相关网站将ch12_example1.txt查询文件下载到电脑，网址为：/go/excelreporting/2007。在Chap12.zip文件或Chap12目录查询文件，文件的具体位置取决于您下载的zip文件。8单击确定以确认该查询不能以图形方式显示。单击返回数据至MicrosoftExcel按钮将数据返回到Excel后继续进行。7.当导入数据对话框出现时，选择数据透视表并单击确定来创建数据透视表。8.将CurrencyName拖到报表过滤区，Month拖到行标区，Year拖到列标区，EndOfDayRate拖到值域区。9.左键单击货币名称中的报表布局区域的单元格B1，然后从下拉菜单中选择欧元，显示最后一天汇率为欧元的数据。10.右键单击数据透视表，在弹出菜单中选择数据透视表选项来调出数据透视表选项对话框。点击汇总和过滤器选项卡，取消选中的显示总计的行和列选项，然后单击确定并关闭对话框，减少对数据透视表中行和列大规模的总计运算。11.数据透视表应同表12-1所示。设置连接属性您可以在连接属性对话框中为数据透视表配置连接属性。单击数据透视表进行操作，选择枢轴表格工具更新连接属性，弹出这个对话框。或者，你可以选择数据属性，弹出连接属性对话框，如图12-2所示。该对话框分为两个选项卡：使用（见图12-2）和定义（见图12-4）。使用选项卡用于配置访问OLTP和OLAP数据源的数据透视表的刷新操作。该选项卡中也有部分用于控制如何格式化和钻取功能的对话框，这些对话框用来处理OLAP多维数据集。连接文件，连接字符串和SQL查询都存储在连接属性对话框中的定义选项卡中。按钮用于修改和导出连接文件，访问Microsoft查询程序。在这个选项卡中还能配置ExcelServices的身份验证设置。使用配置选项在图12-2中，连接属性对话框中使用选项卡的数据的选项分为四个部分：刷新控制，OLAP服务器格式，OLAP钻取和语言。只有第一个部分-刷新控制，适用于OLTP数据源。其余三个部分都适用于OLAP数据源和覆盖在网上OLAP一章。首次打开的Excel工作簿的数据透视表时，刷新控制选项用于配置如何执行查询、刷新数据间隔、以及数据透视表是否自动更新。表12-1提供了每个刷新控制选项的简要说明。启用后台刷新如果要导入大量数据集和（或）通过远程连接，如虚拟专用网（VPN）访问数据源，数据刷新可能需要一段时间才能完成。当在报告中加载新的数据集时，您不能在Excel中执行任何操作，因为刷新操作是所有其他操作阻塞，直到刷新全部完成。您可以通过选择启用后台刷新选项来解除约束（见图12-2）。选中此选项配置后，刷新操作在后台运行，这样您可以在更新数据集的同时继续操作报告。警告不熟悉启用后台刷新选项的数据透视表用户可能无法注意到那些在后台运行的刷新操作。请务必清楚此设置原理，以确保报告用户理解报告尚未被刷新。9报表更新一旦数据集被完全加载入Excel中，数据透视表就被更新了。在此更新过程中，Excel会添加新项目至报表字段、重新计算计算字段和计算项公式，并刷新在报表布局区和枢轴表字段列表对话框中的字段。