Home

QUERY User Guide - Winshuttle Software

Contents

1. TableName FieldName Valuel Value2 Value3 Value4 Value5 Value6 TableName FieldName Valuel Value2 Note e If variable is applied for a DATE field type then the value must be given in YYYYMMDD format e If variable is applied for a TIME field type then the value must be given in HHMMSS format 85 Winshuttle QUERY User Guide 86 If variable is applied to the Numeric field type such as Int16 Int32 Itn64 Double Decimal or Byte then the Value must be the raw data i e it should not be formatted data according to regional settings of your machine e g raw data is 1234567 89 number format available under SAP Defaults in Options For more information see SAP Defaults If variable is applied for String field type then no format is required The variable value for run time file variables that is the path of the file must not contain and If the variable is added on an optional field not mandatory then user may skip the variable value at run time Leaving the value as BLANK or not specifying the field name at all at run time fetches ALL records for the field If the variable is added on a Mandatory field marked as required during query creation then user is not allowed to Skip the value at run time Leaving the value as Blank OR not specifying the field at run time stops the user from running the query Recording transaction codes You can now record the Transaction Codes in QUERY to capture T
2. Adaptively Throttle d Query il in r Query Execution Time minutes N n 0 5 0 _ 32 46 61 75 89 Percentage of Server Load Note Although disabling this option while running the query can improve the query performance the risk is that it can bring down the server when it is already overloaded Hence we recommend that you always run queries with this option enabled unless it is ensured that the SAP server is not highly loaded Authorizations Required SAP Authorizations in QUERY QUERY fully protects SAP security features Under no circumstances can QUERY override the SAP authorization restrictions you are bound to This document can help you and your security team to understand the SAP authorizations that are required to work with QUERY In most cases these SAP authorizations are already in place This document can also help if you have tried QUERY but cannot use it or if you see error messages Remote Function Calls RFC Authorization QUERY makes RFC calls to SAP RFC access must be assigned In most cases these authorizations are already assigned to you To work with SAP the following objects with the indicated values should be in your SAP user profile For the S_RFC Authorization Object e Field RFC_TYPE FUGR function group e Field ACTVT 16 execute or 108 Table Level Authorization e Field RFC_NAME Table Level Authorization To access a specific table in QUERY you n
3. Accounting Documer E BSEG C BSEG Accounting Documer E 8570 BSID Accounting Seconda MEE ey ia ot Data dictionary before synchronization and after synchronization For the purposes of this example we will use the MARA and MAKT tables 33 Winshuttle QUERY User Guide To add tables to the query builder area e Do one of the following o Drag and drop the tables Infoset query or logical database you want into the query builder area or double click a table name Or o Click the Search in Data Dictionary button below the data dictionary Click Search by Table and enter a table name or a table description Click Search Select the table you want from the list that appears and click Add Selected Table to Query Builder Area P test qsq Winshuttle QUERY OO File Edit Tools Help a ww SAP Certified Powered by SAP NetWeaver QUERY script doctest e 0 Q Change File Name test gsq Data Source Table SAP Tabl o Query Builder 5 E E Winshuttle Data Dictionary W6R_800 a H Basis Components BC E E Cross Application Components CA 4 Controlling CO 5 E Enterprise Controlling EC 2 EH Occupational Health EH E i Financial Accounting FI E Investment Management IM m I nnictire Fveriitinn fi FY 3 MAKT Material Descriptio x To build a query 1 In each table select the fields that you want to query The field names ap
4. You open QUERY to see the Most Recent Files and Favorites pane From Most Recent Files and Favorites you can create a new query or open an existing query You can view and open files from the list of most recently used queries and from a list of files that you have specified as your favorites The Back button At any time if you need to return to a previous screen or to advance to the next stage in some processes click the Back button which is always available at the top of the QUERY screen The Open button as i5 Click Open to open an existing query The Close button If you need to exit QUERY at any time click the Close button You are prompted to save any open file The Change button With the Change button you can quickly move from the current query file to select another existing query file Tabs Tabs contain buttons that help you access functionality The available buttons change as the steps of the query process change Winshuttle QUERY User Guide File tab ww Contains the New Open Save and Save As buttons Tools tab a amp Displays buttons for the Log Viewer Scheduler and Options When QUERY is in the Select stage the Search button is also available Help tab On TF A Bowe Displays buttons for online Help User Manual which is available in a PDF format Release Notes of your QUERY installations Automatic Update of QUERY Support Information to help Winshuttle to assist you and
5. again when the Winshuttle Data Dictionary is revisited Centralized Data Dictionary The Winshuttle Data Dictionary can be available individually on each user s computer or as a single Centralized Data Dictionary that is made available to all users by the administrator Synchronizing tables or the data dictionary If you have a local data dictionary or are a data dictionary administrator when QUERY is first used and after each update to QUERY you must synchronize the tables To synchronize the data dictionary e Inthe data dictionary pane right click Winshuttle Data Dictionary On the shortcut menu click Synchronize The names in the pane change from red to black when the files are synchronized For tables the icons distinguish cluster pool or transparent tables and table views a Winshuttle Data Dictionary WE6_800 E B Winshuttle Data Dictionary WE6_800 4 gt Basis Components BC gt Basis Components BC P gt Controlling CO Controlling CO 4 Finance Accounting FI 2 Finance Accounting FI E 7004 T004 Directory of Charts o E 79775 P T0775 G L account groups 1909 P T009 Fiscal Year Variants 7880 T880 Global Company Dat E 7014 T014 Credit control areas E 70100 P T0100 Posting Period Variz E Tor0P P T010P Posting Period Varia E 70018 T001B Permitted Posting Pe E 7003 T003 Document Types E Tor2 _ T012 House Banks E BNK
6. box available for the download Selecting this check box has the same effect as if No of records to extract is set to 0 Must be enabled by the administrator No of records to extract Specify how many records should be extracted If 0 is specified QUERY returns all records if allowed by the administrator or the maximum number of records that you can download The default is 1 million records which is set by the administrator If you specify a number that exceeds the maximum limit the query will not run The Extract all records setting overrides this setting Add a comment about why the query was run Select Run now or Run later to specify an unattended run To run a query e Inthe Run pane click Run For Excel files the file opens and the results are displayed In Excel 2007 each cell can accommodate 8192 characters and in Excel 2003 each cell can accommodate 1024 characters If a long text field that was used in the query contains characters that exceed these limits the field must be trimmed to ensure a successful download Adding run time values If you have included variables as criteria values at run time you need to add other values or add the name of the file that contains the values e If you have included variables run time variables as criteria values at run time you need to add other values or add the name of the file that contains the values input them e If values were set as Required when the crit
7. Contact to your IT department to confirm the port number for your organization o Attachment size limit MB Specify the maximum attachment size The attachment size should not exceed the size allowed on the exchange server o From Email address Specify the Exchange server e mail address to which the message should be sent Note Application sends the e mail message with attachment with the configuration specified only when the user is logged on to the system with his or her domain authentication Proxy settings for Connect Proxy settings are used for Connect and are similar to Internet sittings Consult your system administrator before you change any proxy settings To configure a proxy setting 1 Click Proxy Server Settings 2 Do one of the following e Click Use system proxy Click OK e or e Click Custom proxy 1 Select the Authentication Required check box 2 Type in the username and password Click OK Specifying QUERY script properties QUERY script properties in QUERY comprise the information about your file When you start a new query you are prompted to specify these properties for the file You can update this information for a file any time you have the file open by clicking the Script properties button The QUERY Script Properties dialog box appears when you create a new query file but you can also edit file properties for the current file by clicking the QUERY Script Properties button ES 22 Query sc
8. NetWeaver UE QUERY script has not been created yet Select Map Run Create a new QUERY script You must load or create a You must map QUERY script before QUERY script before running mapping r SAP Logon At WS WE6 ECC6 2 gt Create Query gt Create Query Using User Data EP Logon EP Logon with browser Winshuttle Templates Create Query Using Client 800 SAP Recording User mar Password System Language EN x A Create a new Query g o 3 7 sar Disconnected EN r e E The SAP logon pad appears Select the server on which you want to build a test query It is good practice to test only on a non production server To set the system as the default for every logon select Use selected system as default Click OK Under User Data enter your user name and password Click OK The QUERY File Properties dialog box appears Winshuttle QUERY User Guide o Select the type of data source you want whether tables infosets and queries or logical databases Note that infosets and SAP queries must be enabled by the administrator before you can use them o Specify a title reason for the file and add comments as needed For example a title might be Materials type and a reason for the file might be List material types acquired in past month o Add a title and the reason why the file was created if you want o QUERY supplies the SAP system the alias of the person who created the file and the time and
9. Number of records to be nrc 1000 extracted Sane pati connection alf C Test TestALF alf Specify SQL logon file _ sIf in case query Delfin slf mapped to SQL result sIf c Test Testquery slf destination For suppressing Spw production server warning log F Log column Not applicable for XML and TEXT destinations For Open program screen appears C Program Files Winshuttle query querySHUTTLE exe opn C Test Testquery qsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery al For Run program screen appears C Program Files Winshuttle query querySHUTTLE exe run C Test Testquery qsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery al For Run As a console application C Program Files Winshuttle query querySHUTTLEcom exe run C Test Testquery qsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery alf For SQL Result Destination In case of SQL destination the SQL Logon details need to specified in the form of a SQL Logon File i e SLF which can be Saved by entering correct SQL Logon details Windows SQL authentication when saving a table to SQL server 84 Running QUERY from the command line slf c Test Testquery slf For Attaching Result File in e mail message Not for SQL Result Destination arf For Suppressing Production Server Warning Spw Run time va
10. QUERY 93 Schedulng GUEY Uria a 93 MGnaging query Tlesuiai A A 95 Producto server WOMAN nia ete RB eden eee ahs 97 CUSTOMIZING QUERY aa nda ce rar a aaa DE A oe ante Aan RNa ae aR ARE 97 Application Detau Saa ti 97 Disabling and enabling the RUNNER add in occcocccconcnoconononnnononononononononono nono nono nono nono nono nono ncno nono ccoo 98 Error Management ada aladdin 98 VIS WIG OOS silicio Ra aE AA EAE E EA 101 Working with Winshutfle CONNECT ccsccsscssscesscessesssesssesssesseesseesseesseesseesseeeseeeseseeeeeeseeeeeessneeeeeeenes 103 Advanced features 105 Administrator 9plIonsa u REIN AE SG ABBA AUS BAAR REE A 105 Query execution TIME OUTS cccccescccessccessccesscecsscccessscessecesseccsssecsssecessecessecceseecsssecesecesseeesseecssesenes 106 Changing the admin options PASSWOTG earnen a A A AE 106 Editing SAP authorization objects IN QUERY coooococcnoconocanocononononononononononononon nono nononononnno cnn nnnnnnnnnnnns 106 Adaptive query ihrotiliigsrnadkseneideneren istessisdeneneissisteosieskoensb dahen 107 PAUTIOMZOTIONS a ET RER AA AA A A EELER 108 Required SAP Authorizations in QUERY ccceccssccessccessecessecesseccsseecessecesecessecesseecessecessecessecesseeenseees 108 Remote Function Calls RFC Authorizafion cooccocccoconocanonononononononononononcnnnnnonnnnnnonnonnnnonnnnnnonnnos 108 Table Level AUTMONZOTON iii in Ai AR E S ESTESE ii 109 Windows error messages 111 Index 113 QUERY Introduci
11. QUERY User Guide In addition you can add any table from the Recorded Transaction Codes category File Edit Tools Help 40100430 QUERY script File Name Untitled qsq QUERY Script Chance E Winshuttle Data Dictionary W6R_800 1 7 Basis Components BC 3 Cross Application Components CA 2 Controlling CO gt Enterprise Controlling EC 1 MAKT Material Descriptions 53 Y MATNR E SPRAS E MAKTG MAKTX gt Production Planning and Control PP 2 Project System PS 5 Personnel Time Management PT Ey Payroll PY Ey Quality Management QM 5 Sales and Distribution SD gt Treasury TR 2 Asset management AM 2 Transaction Codes TC gt Recorded Transaction Codes RC gt MMO2 tt fl E B E D E E Fe mm Output Selection Field Description Technical Name Material Description Sho MAKT MAKTX MARA General Material D T130F Field attributes T681 Conditions Structu 88 Advanced run options On the Run pane click the horizontal Advanced Run Options bar on the right to use advanced settings Close application after running Select this option to close QUERY after a query runs This option is a good choice for unattended runs Auto logon file Select the auto logon file ALF you would like to use For more information see Automating your logon D
12. QUERY prompts you to enter run time variables for each of the linked script file The Run time variable wizard displays the fields to enter values of variables You can type in the values manually or fetch the values from SAP using the browse button Run time variable wizard test qsq Language Key Material Number Manufacturer Par Note Linked query script files must be in production status before they can be scheduled for running Result file options not available for Winshuttle SERVER Save and close output file after run Click to save the contents of the data file and close it after a data download Warn if data is present on result file Click to be warned if there is already contents in the output file You are given the options to save as a new file Cancel or write to the file Writing to the file presents three more options which you can also set in the Data writing options Write header information on result file Not available for Access or text files 90 Write field expression Available only for queries from a single table or output to a text file Selecting this option writes only the field name to the header and not the table name Write field expression is always selected for logical databases and for infosets and queries and you cannot change this setting Data writing options Clear mapped columns or Clear result file Clear mapped columns clears data from the start row to the end row of sh
13. Sub Category HL Controlling d g tH gt Finance Acco _ 6 1 5 Human resou gt Material Mang El EBAN FeKN G Synchronize EINA AD T EINE EIPO EKAN EKBE EKBI Restore Master Data Dictionary EKES NON m The Restore Master Data Dictionary item on the shortcut menu 44 Adding tables and transaction codes to the Winshuttle Data Dictionary Adding tables and transaction codes to the Winshuttle Data Dictionary You can add individual tables and transaction codes tcodes to the data dictionary for the SAP Data Dictionary including custom tables Custom tables are user defined tables that usually start with the letters Y or Z To add a sub category 1 Click Winshuttle Data Dictionary or click a node and then click Add a sub category 2 Type the name of the sub category Click Add To add tcodes to the Winshuttle Data Dictionary 1 Right click the tcode node and then click Add Transaction Code 2 In the Transaction Code box type the name of the code Click Add To add individual tables to the Winshuttle Data Dictionary 1 Right click a category or tcode node and the click Add a table 2 From the list select the category where you would like to add the table 3 Type the name of the table Click Add The selected tables appear in the query builder area and in the Search node You can then move the table to any category you like in the Winshuttle Data Dictionary pane NOTE By default you
14. Tools tab 5 2 o click Scheduler To edit a schedule e Click Edit 9 to open the Schedule Run Wizard to edit options To remove a query from the schedule queue e Click Delete A To schedule a run from the Scheduler pane e Click Schedule a run Schedule arun The Schedule Run Wizard opens Stopping a query run You can stop a running query by clicking the Stop button BER The stop functionality is useful if you inadvertently begin a large run or if a runaway process is impacting database performance Note You can stop a running query only if it has been created with tables as the data source You cannot stop a query run that contains InfoSets SAP queries or logical databases Using saved queries You can use saved queries by going to the Query Manager For more information see Managing query files Running QUERY from the command line QUERY can be used from command prompt in two ways e Run QUERY so that programs screens appear Use querySHUTTLE exe e Run QUERY as a console application so that program screens do not appear Use querySHUTTLEcom exe NOTE You must enclose the name of the QUERY executable and the full path in double quotation marks In addition ensure that there is a single space between the QUERY executable path and the query file path 82 Running QUERY from the command line For Open program screens appear C Program Files Winshuttle RUNNER for QUERY xSHUTTLE exe opn C Te
15. Winshuttle at 44 208 704 4170 and then choose option 2 In addition you can view the most frequently asked support questions at http www winshuttle com Support and Training To read and to share articles and tips and tricks for using Winshuttle products visit the Winshuttle community page at http community winshuttle com Viewing the end user license View the end user license agreement from the Help menu Viewing the end user license O e Click About to view product version and licensing information e Click View License Agreement to display the End User License Agreement in a PDF format Activating QUERY Installing QUERY You can install QUERY in only a few minutes Download and run the installation file from the link you have received from your account representative Follow the on screen instructions to finish the installation Prerequisites to installing QUERY on a client computer e SAP GUI release 710 or later must be installed on your workstation e Microsoft Excel version 10 or later e Windows 98 NT 2000 XP Vista Windows 7 Note Winshuttle software is adapted to Windows 32 bit or Windows 64 bit platforms Select the appropriate Winshuttle installer for your platform Installing QUERY You can install the software by e Using a CD ROM e Downloading the installation file from the network Folder locations The new installation will create new product folders However you can move the folders to other l
16. are limited to four tables in the query builder However the default can be changed by the QUERY administrator For more information see Admin options Selecting tables directly If you know a table name you can add a table directly to the data dictionary To add a table directly to the Winshuttle Data Dictionary e Right click on a category in the Winshuttle data dictionary On the shortcut menu click Add table and then type the name of the table One of the following messages appears 45 Winshuttle QUERY User Guide o If the table is present in the Data Dictionary you are notified of where it is located You can move the table to the desired category o If the table is present in the current category you are notified that the table already exists o If the table is not in the Data Dictionary but is in the SAP data dictionary the table is added to the current category o If the table name does not exist an error message appears Locating a table in the Winshuttle Data Dictionary after you have added it to the query builder To locate a table in the Winshuttle Data Dictionary after you have added it to the query builder 1 Right click in the table 2 On the shortcut menu click Locate Table in Data Dictionary The table is highlighted in blue in the data dictionary mmoz MARA General Material Data MAKT Material Descriptions MYKE Sales Data for Material KONM Conditions 1 Dimensio MARC Plant Data for Ma
17. automatically when multiple tables are added to the query builder area if a primary key link or a check table link exists between the tables Primary key links and check table links A check table link exists when two tables are linked by foreign key fields One table assigns some of its fields to be the primary keys of another table called the check table 52 Creating joins between tables A primary key join is possible when the primary keys in the tables have the same field name and are of the same data type and field size The primary is indicated by a key icon Indexes are indicated by a blue inverted key Default joining of Primary and Index Keys If a primary key and index key have matching data fields the application creates automatic joins between the primary key and the index key The tables must contain the same field name field size and be of the same data type NOTE If primary keys are linked index keys are not joined automatically User created joins In QUERY you can create and remove joins if the administrator grants permission to do so In addition to creating joins on regular tables you can join cluster and pool tables and views You can also join a table to itself The administrator must grant permission for all joins For more information see Admin options see Administrator options NOTE Automatic and user created joins appear only on SAP systems where the function module is installed For a
18. information About QUERY For deployments that use Connect a button to start the Connect site The three step process and the three panel screen e Everything you do in QUERY centers on the three step process of Select Map Run which corresponds to the three pane screen Most tasks in the QUERY return to the three pane screen e You create queries map the fields to columns in result files and run the queries from these panels e To create a query you drag and drop tables from the data dictionary into the query builder area e You can click the Search in Data Dictionary button to search for tables and fields in the Winshuttle Data Dictionary and available SAP tables Search In Data Dictionary e You can mouse over a table name to see a text explanation of the purpose of the table e To see the complete field text that explains field names in the tables drag a border to enlarge tables e To delete a table from the query builder area click the red x e To remove a field from the query builder right click the field and then click Remove Item on the shortcut menu You can also click the check box for that field in the table e At any time after you complete a step in the query building process you can update your work by clicking one of the update buttons in the three pane screen Click Update query or Update mapping e To preview a query click the Show query preview tab Select Map Query file has Query File has been cre
19. not Run as logged on enter your Windows user name Select the time of day when the scheduled task Start Time gt is to take place Password Enter the password the user provides to log onto Windows Run niv ifiogaed n When selected the user must be logged on for y gg the scheduled task to be completed e Ifyou are running a SQL Server table specify your authentication selection Or o o Authentication Select SQL Server Authentication or Windows Authentication Windows Username If you selected Windows Authentication your Windows username and password should be applied automatically SQL Server Username If you selected SQL server authentication type your SQL Server username and password Winshuttle SERVER for QUERY Address Add the IP address Port for the server you want to run the query on To complete the scheduler settings click Next to go to page 3 See the instructions for Excel files to complete the scheduling options The Scheduler pane In the Scheduler pane you can manage all your scheduled queries View the query name the name of the output data file the time when the run is scheduled the last date and time when the query was run the next time the query is scheduled to run and the e mail address of the person who will receive notification that the query has run You can also set the schedule for queries 81 Winshuttle QUERY User Guide To open the Scheduler pane imi o e On the
20. only for interim calculations and will be deleted after the calculations are finished 70 Winshuttle SERVER for QUERY gt Mapping A gt 6 y AutoMap Change Destination Type Validate S o SAP Field llame Expression SAP Field Description Field Type Field Size Mapped to column E Excel Expression IN Tas PA Expression Evaluation cell Transform Original Mapping Expression Evaluation Desc i Numbei ring 2 MTART MARA MTART Material Type String 4 B L f rer MARA BRETT Iwi lena 13 b F 4 hoe unarose ch e 3 E E g 5 ame NARA LAENG lengh Deca 13 IF al b Ras MARA BRGEW Gross Weight Dedna 13 16 62 100 350 112 F Normalized Gross weight You may also choose to give relevant header description for the evaluation cell in the column Expression Evaluation Description This information is not mandatory After you save the changes and run the query you can extract the transformed data To open an existing result file cs 1 Click Open sen and then select the Access or Excel file you want 2 Mapping to an existing file does not change the headers in the file 3 To add headers to an existing result file ensure that Write header information on result file is selected on the Advanced Run Options pane For more information see Advanced Run Options To save a file a Save e Click Save to save the mapping instructions to an Excel or Access file
21. operators change according the field that is selected Operators include lt gt lt gt lt gt Like Between In Is Null Is not Null Condition Type a condition to define the search or press F4 to see a list of SAP values that are allowed for that field OR and And Use AND or OR to apply more than one condition expression To specify conditions in a WHERE clause 1 Specify an operator or the NOT operator 2 Do one of the following o Specify a condition o Click inside the Condition box and then click Select SAP values F4 to choose a value from SAP o Add the list of condition values to a text or Excel file and use Select file constants to indicate the file path o Use the Enter values option to enter multiple condition values 3 To apply another condition click OR or AND and then repeat steps 1 and 2 The Like operator and wildcards The like operator has unique behavior in QUERY You can apply wildcards to fine tune your query 61 Winshuttle QUERY User Guide Single underscore or question mark a_ or a returns records for values like al a2 and ab but not a12 or abcd You can use any number of underscores the number of characters you want to match Percentage a or a returns all values that start with a followed by any number of characters For example it matches al a2 ab a12 and abcd Select Date Constants Values With the Select Constant Variables option perform d
22. query file is exported to the template you select Using the add in for Excel QUERY provides an add in for Excel called the QUERY add in You can use the QUERY add in to run existing queries or update the mapping of existing queries but not to edit or create them A query can be run from Excel if it is published to any Excel file or template from QUERY client If TRANSACTION is also installed on your computer along with QUERY you can use the QUERY add in to query the data edit it and upload data back to SAP using TRANSACTION When QUERY is installed the QUERY add in is automatically installed in Excel To view the QUERY add in in Microsoft Office Excel 2007 e Click the Winshuttle tab shuttle and then click the QUERY add in To view the QUERY add in in Microsoft Excel 2003 e Click the QUERY add in QUERY add in settings The default values for run settings are the same as in the query file However at run time they can be changed 74 Using the add in for Excel Select query file Available only if no published file exists in the Excel file Click Browse and select the query file to open The file details appear automatically You cannot type in the file path Published query files Lists all queries that are published in the Excel file Select a file to see its details Start Row A required setting If no value is entered the start row is 2 The setting is not saved after run time in the Excel sheet or in the published q
23. the Run Reason that was entered on the Run pane for this query file Client The number of the client on which the file was created Winshuttle QUERY User Guide Unsuccessful queries Provided only when the destination type is SQL Server or when the query is run in scheduled mode The unsuccessful queries are marked in red Click the record to view the reason why the query failed 102 Working with Winshuttle Connect Winshuttle STUDIO and Connect If you have a Winshuttle Studio installation when you log in and select Remember my log in for one program Connect remembers your credentials for all programs Concurrent logons It is possible to log in to two computers at the same time When you log in to the second computer a message appears to tell you that you are logged in twice but it disappears and you can continue to work You cannot log in to a third machine For more information contact your Winshuttle Connect administrator If your Internet is disconnected Full functionality is available for your programs through Connect for up to 6 hours after the Internet is disconnected However you must have authenticated once before the disconnection so that it is clear to Connect that you are a valid user The authentication requirement prevents unlicensed users from working offline M sw WE EN If your Internet is disconnected you are notified in the status bar Disconnected from Winshuttle However the status bar m
24. to browse to a new folder o If you don t want to specify a new folder click Next Follow the to select the products you want to install Activating a Winshuttle license Activating the 15 Day Evaluation Version To activate the evaluation copy 1 On the startup screen click Activate 2 On QUERY license option screen click Evaluation Click Next The Evaluation screen appears 3 On Evaluation screen type your name company name and e mail address and then click Next 4 On successful evaluation license activation you have the option launch the application Click to clear the Launch Application check box if you do not want to launch the application after license activation 5 Click Finish to complete the process NOTE The evaluation version is valid for only 15 days When the evaluation period ends you are prompted for an evaluation extension key Contact your IT department for the extension key Evaluation licenses are limited to 250 records for QUERY Activating a Winshuttle license Activating a Connect License Winshuttle Connect is a program that tracks the assignment and usage of licenses for Winshuttle products To use Connect you must be connected to the Internet If you are using a Winshuttle product for the first time and if you know that you have been assigned a Connect license and that the version is 10 3 follow these steps to activate the software To activate the software through Connect 1 Ensu
25. values from SAP Note If you compare non numeric data without padding QUERY may return unexpected results SAP type The data type of the field in SAP table To add criteria for a field 1 Click Selection for a field on which you want to set the criteria 2 The default values for the other columns will appear as explained below 3 You can change these default values as per your requirements If you chose the Selection type as fixed the Where clause builder launches as seen in the figure below Where Clause Builder for MARA MATNR Material Number MESA Select Values from SAP F4 Field Expression Field Type Field Size NOT Operator Condition And Or MARAMATNR String 18 J l y 000000000000000200 MARAMATNR String 18 x 000000000000001000 000000000000002000 MARA MATNR String 18 gt fin Se NT Winshuttle Query Logid Select file constants Enter values Values File path 4 If you want to make criteria on any field as mandatory click Required 5 Set the lookup type 6 Click Show Preview to see sample run of the designed query 59 Winshuttle QUERY User Guide To clear criteria e Click the criteria that you want to remove right click and then click Clear Criteria Criteria Dr In CL I o To sort criteria e You can sort criteria on the following columns in the criteria pane Output Selection Field Description and Technical Names The power o
26. 0000001000 C E B BERLIN 1000 06 0000001001 SKF Kugelmeier KGaA 1000 07 0000001003 Gusswerk US 1000 08 0000001005 PAQ Deutschland GmbH 1000 09 0000001010 Sunny Electronics GmbH 1000 10 10000001011 SKF Americas EP a The Preview Run dialog box which displays an example of the kinds of records that will be returned by your query 64 Best practices for query names Show query e Click the Show query button Bto see the SQL expression that is created by the query Saving a query When you have finished creating your query QUERY prompts you to save the query You can also save a query from the File menu 65 Winshuttle QUERY User Guide Best practices for query names A good practice for naming query files is to follow this convention functional area tables and date For example for an asset master a query file name would look like this asset_master_ANLA_111208 66 Mapping In the mapping stage of the query process you map the fields of your query to the columns of your output file Each column header corresponds to a field QUERY provides easy drag and drop editing of fields Output options in QUERY include Microsoft Excel Microsoft Access and Microsoft SQL Server files You can map the fields of any data source available to QUERY Map Query file has not been mapped Create Mapping Auto Mapping The map pane before a query file is mapped Click Create Mapping to map manually or click Aut
27. A BNKA Bank master record E SKAL SKA1 G L Account Master El BNKA BNKA Bank master record E BKPF BKPF Accounting Documen E BSEG Cl BSEG Accounting Documer E 8570 BSID Accounting Seconda ES en Er nt ye Data dictionary before synchronization and after synchronization 43 Winshuttle QUERY User Guide To synchronize an individual table infoset or logical database e Inthe data dictionary pane right click the table infoset or logical database On the shortcut menu click Synchronize The names in the pane change from red to black when the files are synchronized To automatically synchronize a table infoset or logical database e Drag a table into the Query Builder area The table synchronizes automatically Restoring the data dictionary to its original state You may want to restore the data dictionary to the state it was in when you first installed QUERY Restoring the data dictionary has the following effects e All the previously synchronized categories tables infosets SAP queries and logical databases are unsynchronized e Newly added categories and items are removed e All search results and favorites are cleared To restore the data dictionary to its original state e Right click in the Winshuttle Data Dictionary and then click Restore Master Data Dictionary SAP Tables j SAP Infosets amp SAP Queries ay eater anar OWES RNN Al 1 sl So Basis Compor Add
28. ENTRAL Winshuttle Function Module and Adaptive Query Throttling are trademarks of Winshuttle LLC in the United States and or other countries The names of actual companies and products mentioned herein may be the trademarks of their respective owners Contact Information For more information please contact 1 800 711 9798 or visit our Web site at www winshuttle com Contents Contents QUERY 7 Introducing QUERY AA RA 7 New Features in Winshuttle QUERY 10 3 essesssssessessessessessessessessessessessessesessessessesessessessessessessessesnt 7 Getting help in Winshuttle QUERY ceeeccesessscsceeseeseesececeeeesecsecseceaeesecseseeceaeesecseseeeaeeaecesessesecaeeseeees 7 HEIP NOSE CMA ii dicas 8 Winshutfle ce st merservice n aneeisununkeintinkm nidesunn arten n E R a e aE aS 8 Mewing the enduusenlic AS iia ia 8 Activating QUERY 11 A A teens diene hiess 1 PAST OMIMG QUERIA ia 1 Installing Winshuttle STUDIO 0 eee eeeesceceeseeseeeceeceseesecseceeceaeesecsesesceseesecseceneesecseseeseaeeaecaeseeseseeaecseseeeeas 13 Activating the 15 Day Evaluation Version cccsccsessssssesecsscessecececseceseeseesecseseseeaeesecneseneeseeseseesenees 15 Activating a Winshutile license ianausnenssueninsuninnnkulnnnangnunlnkelsikenuhten 15 MigratingTo Conneeia ai eds Dhak adc 16 Activating without the Ternet niisiis aaeeei 16 DEGCTIVGMING AMICS ri Ai 16 Activating a Node locked Fixed Desktop or FlexNet License oooococccocccocncoc
29. Or browse to an existing file to save the new mapping instructions Winshuttle SERVER for QUERY With the Winshuttle SERVER for QUERY formerly known as sqISHUTTLE you can send extracted data to a SQL Server database You create a SQL Server table just as you would create an Access table The only difference is that when you save the table you connect to the server and select a database Your new table is then created in the database Winshuttle SERVER must first be connected to SQL servers For more information see Winshuttle SERVER Configuration To map fields to a SQL Server table 1 Drag each selected field and drop it under a column header in the output file The changes can be seen in the field form 71 Winshuttle QUERY User Guide 2 Drag each selected field and drop it under a column header in the output file The changes can be seen in the field form i Add Log Col 3 If you want click Add Log Column t BEER to add a column into which the SAP log data can be written To modify fields in an new SQL Server table 1 To change the name of a field right click the field and then click Rename Table 2 To change the name of the table right click the Table tab A NOTE You cannot rename a table or change fields in an existing SQL Server table You can only add fields or a log column To open an existing database table 1 Click Open The Connect to Server dialog box appears Connect to SQL Server Server Na
30. Search node For example if you select 10 tables but are permitted to add only four four appear in the query builder area and the remaining six are highlighted in the data dictionary Infosets SAP queries You can use only one Infoset SAP query to the query builder area at a time Joining of more than one Infoset SAP query with tables is not supported After adding the Infoset SAP query into query builder area all the underlying fields in the query are seen in one single table For each of the fields even the table name is seen Refer the figure below For Infoset query you can chose any field as output field and they get listed in the criteria pane You can now set criteria on it For more details on usage of criteria pane please refer to the section Using criteria to define fields Note You cannot deselect the default selection fields for criteria in an Infoset These selection fields have been set during the creation of Infosets on your SAP server For SAP query you cannot select or deselect any field for output or criteria However you can modify the criteria You can change the specifics of the criteria Note AND expression and IN operator are not available for Infosets Queries 51 Winshuttle QUERY User Guide Searching data dictionaries for sources Search in Winshuttle Data Dictionary es Search By Case Criteria LDB Name LDE Node Case sensitive O Case insensitive LDB Node Name Search in Winshuttle Da
31. Server Host If available type the message server host information Select this check box to enable single sign on With single sign on the Use SSO1 SAP system uses the Windows credentials user name and password for each user Winshuttle QUERY User Guide 94 parameter if you select single sign on Dakar Specify the SAP password for the user Specify the language Language that the system should use If available specify the PEUR enterprise portal URL Type the e mail address for the person who you want to receive notification by e mail message when the scheduled run is completed You can specify multiple e mail addresses that are separated by semicolon Select to attach the Attach data file to the mail results file to the e mail message Run on console is selected by default This Run on console setting cannot be modified Click Test to confirm that your logon information works Click Next E mail notification On page 3 select the following options Once Schedules a one time run If you select Once you can set the Run on date Daily Schedules a daily run Weekly Schedules a weekly run If you select Schedule Weekly enter the weekly frequency Perform this task You can also specify the day of the week for the run The Weekly setting is not available for SQL Server Monthly Schedules a monthly run If you select Schedule Monthly enter either the date each month for the r
32. There is no other additional layer of security To access SAP queries and Infosets which are already created in SAP use QUERY 40 Selecting Data sources Your query starts with your selection of a data source in the properties dialog box With each data source that is available to QUERY you can use the three step process to create a query map it and run query SAP and Custom Tables The Winshuttle Data Dictionary displays the most commonly used tables The tables display the following information e Functional area such as Finance Accounting Human Resources Production Planning e Anode for transaction codes which lists the tables used in common transactions e Descriptive text to make it easier to identify the table e Unique icons to distinguish different table types such as cluster pool and transparent tables and also table views Logical Databases LDBs Logical databases are special ABAP programs that retrieve data and make it available to application programs The most common use of logical databases is still to read data from database tables by linking them to executable ABAP programs You can use LDBs with QUERY only if the Winshuttle Function Module is installed on the SAP server you use Security handling The inbuilt logic for security inside the LDBs is respected by Winshuttle QUERY No additional layer of security is required Infoset SAP Queries SAP query and Infoset query are the end user query
33. Type page click Complete or Custom 6 If you selected custom on the Product Selection page select each product you want to install 7 Follow the instructions 13 Winshuttle QUERY User Guide To install STUDIO by performing a complete installation 1 2 Insert the disc into your computer s DVD or CD drive Installation begins automatically If the installation does not automatically begin select Start on the task bar and then select Run Select Browse to go to the CD or DVD drive Then select Setup to begin the installation The first page of the wizard click Next Read the License Agreement page and if you accept the license terms click I accept the license terms Add the user name and company name On the Setup Type page click Complete Installation begins To install STUDIO by performing a custom installation Or 1 Insert the disc into your computer s DVD or CD drive Installation begins automatically 2 If the installation does not automatically begin select Start on the task bar and then select Run Select Browse to go to the CD ROM drive Then select Setup to begin the installation The first page of the wizard click Next Read the License Agreement page and if you accept the license terms click I accept the license terms Add the user name and company name On the Setup Type page click Custom On the Choose Destination Location page do one of the following o Click Change
34. Using Winshuttle Templates 2 To use a template do one of the following o To use a QUERY script template double click the template or select the script and click Create Save it to your computer You can update this query as required Or o To use an Excel template download the template to your computer by double clicking the template or select the script and click Create 3 Start the Excel template or open the query file that is embedded inside the Excel template If you open query script the embedded query opens You can modify the query according to your requirements and run it 4 If you open the Excel template you can use the QUERY add in for Excel to download data into the template Firewall issues If you experience difficulty downloading templates your company s firewall might be preventing the downloads Contact your IT department You can also contact Winshuttle support to have the template sent to you through an e mail message 36 Introduction Running queries Introduction Mapping your fields to a result file With Winshuttle QUERY you can create modify a template for an output file so that when the query runs the extracted data are arranged in the output file the way you want Output files include Microsoft Excel Microsoft Access text or Microsoft SQL Server files To map fields to an output file 2 Drag the field to the column where you would like the results to appear For each mapped field the q
35. WINSHUTTLE QUERY User Guide Version 10 3 Legal The Winshuttle QUERY help file is for informational purposes only WINSHUTTLE MAKES NO WARRANTIES EXPRESS IMPLIED OR STATUTORY AS TO THE INFORMATION IN THIS DOCUMENT The software contains proprietary information of Winshuttle LLC it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law Reverse engineering of the software is prohibited Due to continued product development this information may change without notice The information and intellectual property contained herein is confidential between Winshuttle LLC and the client and remains the exclusive property of Winshuttle LLC Except as expressly provided in any written license agreement from Winshuttle the furnishing of this document does not give you any license to patents trademarks copyrights or other intellectual property rights covering subject matter contained in this document No part of this publication may be reproduced stored in a retrieval system or transmitted in any form or by any means electronic mechanical photocopying recording or otherwise without the prior written permission of Winshuttle LLC If you find any problems in the documentation please report them to us in writing Winshuttle LLC does not warrant that this document is error free 2000 2012 Winshuttle LLC All rights reserved QUERY TRANSACTION SERVER RUNNER C
36. ables and the fields that are being modified The recording captures the Tables and the fields that are being modified and a new category appears in the Winshuttle Data Dictionary called Recorded Transaction Codes SAP Tables Winshuttle Data Dictionary W6R_800 1 Basis Components BC gt Cross Application Components CA Controlling CO U Enterprise Controlling EC EH Occupational Health EH L 5 Financial Accounting FI 4 Investment Management IM Logistics Execution LE Logistics General LO 5 Materials Management MM E Treasury TR Asset management AM Transaction Codes TC Recorded Transaction Codes RC 5 MMOZ test Favorites EH EH El El a 35 EJ Search In Data Dictionary At least one recording should be performed to display the category The fields that are changed during the recording are automatically selected as Output fields when you add those tables in the Query Builder The recording of transaction codes will assist in identifying the tables as well as the fields to be used for building the query scripts To use recorded transaction codes 1 On the Select panel click Create Query Using SAP Recording 2 Enter SAP credentials to log on to an SAP server 3 In the QUERY Script Properties dialog box enter the t code you want to use and specify other settings Click OK Winshuttle
37. anguage that the EP URL i the enterprise protocol Select this check box to enable single sign on With single sign on the SAP system uses the Windows credentials user name and password for each user Specify this connection parameter if you select single sign on After you capture system data and logon credentials and save the file the path and name of the alf file appear Auto Logon File SAP defaults To set the default SAP logon 1 On the Tools tab click Options 2 Click SAP logon default 20 Client Connection Settings 3 Specify default SAP logon languages 4 If you want select Remember last logon password 5 Specify a customized path for the SAP logon ini folder You can place the SAP logon file at this location For 64 bit operating systems SAP logon defaults display an option to select the Single Sign On 64 bit configuration file User data formats In QUERY you can specify whether data is displayed as raw data or as formatted data Use regional settings Select this check box to ensure that dates and numbers are downloaded and displayed in accordance with the regional settings on your computer Regional settings apply to Excel text and XML files Regional settings also apply to data that is displayed in QUERY such as previewed text and values displayed through F4 Access and SQL Server databases use their own data formats and do not display data in the regional setting format You sp
38. ata chunk value This setting can help performance when you are extracting a large number of records By default for single table queries QUERY calculates the optimum chunk value for the run based on query characteristics such as total field length and number of records to extract With the data chunk value set QUERY fetches only the number of records for each chunk that you can specify The maximum allowed value for chunk size is 450000 A Stop button also appears so that you can stop the extraction if it is running for too long Data chunk value is not available for Infosets SAP queries Run as SAP Background Process Select this to for a cleaner run to keep the data file closed during the run Enable adaptive query throttling Available only when the administrator grants permission When the throttling algorithm is applied query execution slows as the system load increases however without the algorithm the query executes in constant time and competes for resources with other transactions on the system Adaptive query throttling is not available for logical databases or for infosets and SAP queries Note Although disabling this option while running the query can improve the query performance the risk is that it can crash the server when it is already overloaded Hence we recommend that you always run queries with this option enabled unless it is certain that the SAP server is not highly loaded Link QUERY script Select this check bo
39. ate math such as Today 120 You can also select the date from the calendar displayed or chose an SAP initialized date Date values for criteria must always be specified in the correct date format as selected in Tools gt Options gt SAP Defaults Select Date N x Date constant Operator value No of days StartOfYear y A vl The Enter Values of Run Time Variables dialog box which appears when you select constants and variables Select SAP Values F4 To add SAP values to the criteria builder 1 To add values appropriate to a field in your criteria builder click Select SAP Values or press F4 2 Click a value and then click the Check Mark The value appears in the Condition box 62 The Like operator and wildcards E Material Number 1 500 Entries found 02L0150 07 ASSEMBLY INSTRUCTIONS DELUXE TANK EN T ASTOZ amp 03L0150 07 ASSEMBLY INSTRUCTIONS DELUXE TANK EN T AS7O3 04L0150 07 ASSEMBLY INSTRUCTIONS DELUXE TANK EN T AS704 05L0150 07 ASSEMBLY INSTRUCTIONS DELUXE TANK EN T AB705 06L0150 07 ASSEMBLY INSTRUCTIONS DELUXE TANK EN T AS706 opalen erro up g A ur The SAP values that appear when the SAP GUI is available e If the SAP GUI is not installed on your computer or if the field selected in the table has no associated description in SAP only the values without their description are displayed By default the first 100 records are scanned and relevant values are displaye
40. ate reports from the system Security handling The inbuilt logic for security inside the LDBs is respected by Winshuttle QUERY There is no other additional layer of security The table containing authorization objects for fields see Authorizations on page 108 is used in securely extracting records for Infoset SAP Queries Security is applied on Infoset Query corresponding to the presence of table and field in authorization table For example if MARC WERKS is present in Infoset Query then its existence is checked in single table authorization table If it exists then data is filtered accordingly There is difference between how it is handled for Infoset query and SAP query e For Infoset query It does not depend whether you have selected a field as output or not e For SAP query Security will be applied only for output fields It is not applied if a field is just a selection field To access SAP queries and Infosets which are already created in SAP use Winshuttle QUERY Introduction to LDBs Logical databases are special ABAP programs that retrieve data and make it available to application programs The most common use of logical databases is still to read data from database tables by linking them to executable ABAP programs You can use LDBs with Winshuttle QUERY only if the Winshuttle Function Module is installed on the SAP server you use Security handling The inbuilt logic for security inside the LDBs is respected by QUERY
41. ated been mapped gt Update Query Update Mapping The Select and Map panes The three pane screen Everything you do in QUERY is centered on the three pane screen You can view queries in the Select pane modify them in the Run pane and run queries in the Run pane In QUERY you can view an existing query update the mapping and then run the query Tabs Tabs contain buttons that help you access functionality The available buttons change as the steps of the query process change File tab ww Contains the New Open Save and Save As buttons Tools tab 38 e Displays buttons for the Log Viewer Scheduler and Options When QUERY is in the Select stage the Search button is also available Help tab Winshuttle QUERY User Guide O MS _ _ Displays buttons for online Help the user guide which is available in a PDF format Release Notes of your QUERY installations Automatic Update of QUERY Support Information to help Winshuttle to assist you information About QUERY and if your environment includes Winshuttle Connect a button to Launch Connect Other buttons Other buttons are available when you have any query open With the File Properties button you can update the file properties information that describes your file at any time The Publish query file button provides a way to embed a query file in an Excel file or template The Show Query button which shows the SQL statement for the query Wi
42. ay indicate that your license is still online You can keep working or save your work and sign out Viewing Connect messages You can view updates warnings and other messages by clicking the Connect Message icon in the status bar Winshuttle Messages Message Type Message Creation Date Time Update Update Message By Amir 17170001 5 30 00 AM Update Update Message For Query Dev Team 11 3 2011 6 08 17 PM Information Information Message For Query Dev Team 11 3 2011 6 08 43 PM Warning Warming Message For Query Dev Team 11 3 2011 6 08 54 PM f Error Eror Message For Query Dev Team 11 3 2011 6 09 05 PM The Connect Message box in a client Winshuttle QUERY User Guide Signing in as a different user It is possible to sign in as a different user from the same computer if that user name is properly provisioned with a current license To sign in as a different user 1 On the Most Recent Files and Favorites page click the Welcome Welcome contosoluser ah i arrow and then click Sign in as a different user Sign in as Different User The Connect login dialog box appears 2 Click User credentials 3 Type the user name and password Click OK Changing proxy settings You can change proxy settings from within the client or from the login box For more information see Client Connection Settings 104 Advanced features Administrator options Administrator options are password protected The default password is queryshu
43. bles are present in the query builder use the zoom feature Zooming in on the query builder To zoom in e In the query builder toolbar click Full Screen lt To zoom out e Inthe query builder toolbar click the icon to toggle back to normal view Query Builder G y o 7 A PERE xi 1 HRP E008 Infoby x PLM Inspection a FP PL AR Plan Version 5 sd Pasito Y Y OTYPE Object Type Y TE TA m 2 onan miini m rm P PLNKN Number a The query builder tool bar 47 Winshuttle QUERY User Guide Zooming in or out of a table To see details about the fields in a table such as the field length and field type you must zoom in To zoom in or out of a table 1 Right click the table 2 On the shortcut menu click Zoom In on Table The details for the fields for that table appear in an easy to read window You can make additional field selections or clear field selections in this window 9 SKB1 G L account master X lt Company Cc G L Accoun Alternative ac BEWGP Sort By Field Description gt O BUSAB Sort By Field Name gt DATLZ Sort By Field Selection gt ERDAT Default Field Sorting ERNAM O FDGRY Zoom In Table SKB1 4 dp Select All Fields O FiPOS Deselect All Selected Fields dea Locate Table in Data Dictionary C HKTID Show All Possible Joins for SKB1 ve wien A _ Adding SAP Queries to the Winshuttle Data Dictionary Unlike for Infosets and tables no l
44. build a query including the following e Create a new query qsq file and define all the search criteria e Re use saved queries e Open a query template To start a new query Es e On the QUERY welcome screen click New To open a query template e On the Select pane click Create Query using Winshuttle Templates To re use an existing query e On the QUERY welcome screen click Open Tables and the Winshuttle Data Dictionary Depending on the data source you choose while creating a query the building block of any Winshuttle QUERY could be tables Infosets or SAP queries or logical databases These items are found within the Winshuttle Data Dictionary The Winshuttle Data Dictionary The Winshuttle Data Dictionary contains the most frequently used SAP tables logical databases and infosets and SAP queries 42 Tables and the Winshuttle Data Dictionary In the data dictionary the tables are organized according to functional area such as Materials Management Sales and Distribution Human Resources and Production Planning Tables that are relevant to specific transaction codes are organized under the Transaction Codes item Tables appear in data dictionary in the following order views transparent tables pool tables and cluster tables The tables are sorted alphabetically within each table category When a new table is added to a category it is added at the bottom of the list but the entire list of tables is sorted
45. d To see more records specify the number you want to scan in the No of Records box Maximum number of values that can be scanned are 5000 No of Records 100 Get Records 3000000013 3000000014 3000000015 3000000016 2000000018 3000000019 3000000021 v be The SAP values dialog box which appears when you click the SAP Values button Adding run time values When you include run time variables as criteria values the values must be added at run time If the criteria was set as Required it is mandatory to add values at run time If you use the IN operator in the criteria you can select either a text or an Excel file to contain a list of values that are read in when the query is previewed or run 63 Winshuttle QUERY User Guide The dialog boxes for the Select File option Number of entries This feature is available for single table and multiple table queries Click Number of Entries gt to see the number of records that the selected criteria will return Show Preview e Click the Show Preview button to see the kind of records that the query you have built will return By default 100 records are returned Preview Run For Existing Query LFA1 LIFNR LFA1 NAMEI LFB1 BUKRS Lee Account Number of Yendor or Creditor Name 1 Company Code Reco gt 01 0000000 TiedemeerEntsorgung mbH 1000 000016 02 0000000111 KBB Schwarze Pumpe 1000 03 0000000200 SMP 1000 04 0000000300 AluCast 1000 05
46. date on which the new query was started If you want to protect the query with a password click Lock Queryfile and then enter and confirm a password Click OK Authorization issues If you are not able to complete a transaction and receive authorization errors ensure that you have Winshuttle Function Module installed Winshuttle products use remote function calls to access SAP These add another layer of security to your processes Verify that you are authorized to access the appropriate Winshuttle authorization objects by clicking the Authorization Object button on the Tools tab Consult the Authorizations topic for more information ini issues If you have problems logging on and receive ini errors check the location of the ini file You may need to point SAP to your ini file 32 Introduction Building a query Introduction Building a query Before you build a query you must decide what information you need and what data source contains that information Be specific about what you extract Retrieving a narrow range of data improves response time and reduces unnecessary information If this is your first use of QUERY you must synchronize the Winshuttle Data Dictionary The Winshuttle Data Dictionary is specific to the data source you selected whether Tables or Infosets SAP Queries or logical databases Each of these contains the most frequently used SAP tables Infosets or Queries and logical databases for that source Th
47. der area e Drag and drop the selected table from the data dictionary to the query builder area 50 Adding items to and searching for items in query builder File Edit Tools Help Bw SAP Certified Powered by SAP NetWeaver Change SAP Tables ICAO B Query Builder Show Criteria Pane Show Preview Number of Entries Win Basis Components BC Cross Application Components CA Controlling CO Enterprise Controlling EC EH Occupational Health EH Environment Health and Safety EH Fimancial Accounting FI Investment Management IM Logistics Execution LE Logistics General LO Materials Management MM Personnel Management PA pagar MU grant inter EN an i PR Pe e Se O I a 3a a aa a aa The data dictionary is on the left The main query builder area is empty on the right If you cannot find the table you want by looking in the data dictionary you can search for the table In addition if you know the field name or know the kind of information you want you can search by using the field name or description A list of tables that contain such fields is returned Limits on tables that can be added For tables if you try to add more tables to the query builder area than are permitted by your administrator the maximum number of tables is added to the query builder area and the remaining number are highlighted under their node in the data dictionary or under the
48. e QUERY You can click any one of the Help ribbon buttons at any time to obtain assistance and to learn how to use QUERY The Help tab selections are available on all screens Winshuttle QUERY User Guide Help ribbon selections In the QUERY the Help tab offers the following items e A Getting Started help file e An Advanced User Guide in PDF format which you can open from within QUERY print or save to your computer e QUERY release notes that include bug fixes and new features introduced in different versions of the product e Auto update button to check for recent product updates and related downloads such as for security and critical updates You can also set up to automatically check for available software updates by using the Auto Update option e Support SHUTTLE information that Winshuttle Support might ask you to generate to better assist you to use QUERY e Information about your QUERY version and license See Activating a Winshuttle license for more information e Launch Connect Site button to open the Winshuttle Connect site to update your Connect information such as your password Winshuttle customer service Winshuttle provides a full range of self service options to assist you in using effectively You can also contact Winshuttle Customer Support directly by e mail message or by telephone E mail support winshuttle com In the US call the Winshuttle at 1 800 711 9798 and then choose option 2 In the UK call
49. e option and repeat the actions that resulted in an error A text file is created in the default log folder that you can send to the support team for review Note It is important to shut off trace after you have the information you need The log file will be written to continuously which may degrade the program s performance 99 Viewing logs The QUERY Log Viewer provides you with a comprehensive log of all successful queries You can view logs by clicking Log viewer on the Tools tab To view logs e On the Tools tab click the Log viewer button To sort on any log fields e Click a field name To export log information to an Excel file e Click Export To filter listed query files e Inthe Filter box select one of the following o All to display all query files o IP address Port for a server to display all files that were run on that server o Local to display all files that were run on your computer The log viewer shows the following fields Query file name The name of the qsq file SAP user name The name of the user who created the file Download date The date on which the query was run System The SAP system on which the query was run Records The number of records extracted Execution time The length of time it took to complete the download Tables used Tables used to create the query Selection Fields The fields used in the actual query Output fields Fields included in the report Run Reason Displays
50. ecify regional settings on the Regional and Language Options dialog box that is available through Control Panel on your computer Clear the Use regional settings check box Clear this check box to download raw data in Excel text and XML files Raw data is displayed in QUERY such as in previewed text and values displayed through F4 Access and SQL Server databases use their own data formats Dates You can specify the download format for dates in the Date Format box The date format selected is used to display dates in Excel text and XML files in Access and SQL Server databases and in preview and F4 values that are displayed in QUERY Client Connection Settings Depending on your firewall and email policies your organization may select to route email through the internal email server or the Winshuttle default email server e Attachments can be added to email messages sent through an internal server e No attachments can be attached to messages that are routed through the Winshuttle default email server To set up email connections 1 On the Tools tab click Options 2 Click Client Connection Settings 3 Select Winshuttle default mail or Internal e mail server 21 Winshuttle QUERY User Guide For an internal account select settings for the account that should receive the results o Microsoft Exchange Server Provide the name of the Microsoft Exchange Server o Server port number By default the server port number is 25
51. ed in previous version of QUERY may not generate accurate results 54 Logical Databases e The following conditions are applicable for Possible Joins when a left join exists o All entries conform to left join conditions o Selecting some joins in the grid may disable other joins because of left outer join limitations o Running a query with a left outer join on a version of the Winshuttle Function Module with a version earlier than 10 2 displays an information message to abort the run To delete a join e Right click the join line that you want to delete and then click Remove Joining with infosets and SAP queries You can use only one Infoset SAP query on query builder area at a time Joining of more than one Infoset SAP query with tables is not supported Selecting fields Selecting the specific fields that can provide answers to your questions reduces response time and unnecessary records Fields represent specific data elements in databases Infosets and SAP Queries After adding the Infoset SAP query into query builder area all the underlying fields in the query are seen in one single table For each of the fields even the table name is seen For Infoset query you can chose any field as output field and they get listed in the criteria pane You can now set criteria on it For more details on using the criteria pane see Using criteria to define fields Note You cannot deselect the default selection fields for cri
52. eed table level access Table level access in SAP is independent of a transaction For example you may have access to the transaction MMO1 which uses the Material Master table MARA but this does not give you automatic access to that table Table level access is controlled by these authorization objects e S_TABU_DIS for client dependent tables e S_TABU_CLI for client independent tables Almost every client dependent table in SAP is assigned to a specific authorization group in the SAP table TDDAT field CCLASS For example table MARA is assigned to the authorization group MA To access Table MARA authorization group MA must be assigned to your SAP profile in the authorization object S_TABU_DIS as indicated in the following example Authorization Object S_TABU_DIS Fields Authorization group DICBERCLS MA For table MARA Activity ACTVT 03 Display Notes 1 Each table may belong to a different authorization group For you to access different tables your profile must have the proper authorization for the appropriate groups 2 For client independent tables where the field MANDT is not present in the table you need the following authorization object in your SAP profile Authorization Object S_TABU_CLI Field CLIIDMAINT X 3 To attaining the specific authorization group if your required table is not listed in the SAP table TDDAT you must make the following assignment Authorization Object S_TABU_DIS Fields A
53. eet or column Available only for Excel files Clear result file clears all contents from a file or table Available for SharePoint lists text and XML files and for Access and SQL Server tables Append data to result file Appends data to the existing data Not available for XML files Overwrite existing data Overwrites the data Available only for Excel files Managing QUERY Scheduling a query run You can schedule unattended queries QUERY requires that your Connect log in information is saved If you did not select Remember my login information when you logged in to Connect you are prompted when you select an option that runs later To schedule a run e Under Run options click Run later or Run later SERVER and then click Run The Schedule Run wizard appears Follow the steps to schedule a query run To complete the Schedule Run Wizard 1 Do one of the following o Click Capture SAP details to automatically retrieve the information that is selected on the SAP Logon dialog box Clicking Capture is the easiest and most error free method for adding logon details to the scheduler Or o Type in the required information in the fields SAP System Name Specify the SAP system name Application Server Host Enter the host name System number Specify the system number Select this check box to Use Logon Group update logon group information Logon Group If available type the auto logon group information Message
54. eria were set it is mandatory to provide inputs e If you used the IN operator you can select an Excel or a text file 78 Scheduling a run The Criteria Values File dialog box with the Text File option selected For an Excel file specify the following required settings e Sheet Name e Column Name e Start Row e End Row For a text file you need only specify the path for the file that contains the values Scheduling a run With QUERY you can schedule unattended queries To schedule a run e Under Run options click Run later and then click Run You are prompted to save the query The Schedule Run wizard appears Follow the steps to schedule a query run To complete the Schedule Run Wizard 1 Do one of the following o Click Capture SAP details to automatically retrieve the information that is selected on the SAP Logon dialog box Clicking Capture is the easiest and most error free method for adding logon details to the scheduler Or o Type in the required information in the fields 79 Winshuttle QUERY User Guide SAP System Name Specify the SAP system name Application Server Host Enter the host name System number Specify the system number Select this check box to update logon Use Logon Group f group information Logon Group If available type the auto logon group information Message Server Host If available type the message server host information Select this check box to enable s
55. ese components are organized organized according to functional area such as Materials Management Sales and Distribution Human Resources and Production Planning Tables that are relevant to specific transaction codes are organized under a tcode item To synchronize the data dictionary e Do one of the following o Inthe data dictionary pane right click Winshuttle Data Dictionary On the shortcut menu click Synchronize or o Drag a table into the Query Builder area The table synchronizes automatically The names in the pane change from red to black when the tables are synchronized Icons indicate cluster pool and transparent tables and even table views E B Winshuttle Data Dictionary WE6_800 E Fm Winshuttle Data Dictionary WE6_800 A cH Basis Components BC 6 Basis Components BC gt Controlling CO Controlling CO 5 fe Finance Accounting FI E El Finance Accounting FI E 7004 T004 Directory of Charts o E 79775 P T0778 G L account groups E 7009 P T009 Fiscal Year Variants E 7880 T880 Global Company Dat E 7014 T014 Credit control areas e TOLGO P T0100 Posting Period Variz E oroe P T010P Posting Period Varia E 70018 TOO1B Permitted Posting Pe E 7003 17003 Document Types E Tor2 T012 House Banks El ana BNKA Bank master record E SKAL _ SKA1 G L Account Master E BNKA BNKA Bank master record El BKRF BKPF
56. f WHERE clauses Use WHERE clauses to limit extractions and thus reduce download time Where clauses are especially useful in multi table queries If no WHERE clause is specified all records will be returned With the WHERE clause builder only those logical operators are available that apply to the selected field In addition you can use AND and OR operators between clauses You can build as many WHERE clauses as you need Where Clause Builder for MARA MATNR Material Number eS Field Expression Field Type Field Size NOT Operator MARAMATNR String 18 __ 000000000000000200 MARA MATNR String 18 z Between 000000000000001000 000000000000002000 gt j MARA MATNR String 18 Cole jeu E lt EINEN Query iLogid el Select file constants File path The WHERE Clause builder 60 The Like operator and wildcards Fields in the WHERE clause builder Field name Automatically generated this indicates the table name and the selected field name Field type QUERY displays the type of field whether a string double byte date or time data type QUERY displays the size of the selected field in bytes Padding Controls automatic padding for numeric values of the string fields Note If you compare non numeric data without padding QUERY may return unexpected results NOT operator Specify whether you want the inverse NOT of the specified operator Operator The available
57. field F The cell which will be used for interim calculations of Expression evaluation cell excel formula Transform original Option to overwrite the original data with mapping transformed data Fa evaluation Relevant header description for the evaluation cell description Mapping headers To map fields to Excel column headers e Drag each selected field and drop it under a column header in the output file To map fields to Access tables 1 Drag each selected field and drop it under a column header in the output file E Add Log Column 2 Click Add Log Column optional to add a column into which the SAP log data can be written To modify fields in an Access table e To change the name of a field right click the field and then click Rename Table e To change the name of the table right click the Table tab 69 Winshuttle QUERY User Guide Transforming data before the download By using Excel formulae during mapping stage you can transform data before downloading it This feature is available only when the destination type is Excel 1 Click Update Mapping on the Mapping pane 2 For the field whose output you want to transform find the Excel Expression column and then enter the Excel formula In the example below a formula is entered for the field GROSS WEIGHT This formula will be applied to GROSS weight values that start from cell G2 and resultant value will be stored in column H from cell H2 onwards till the
58. ge of values such as FROM and TO as input the BETWEEN operator will be available for this field in QUERY e Click Preview run to test the designed query e Click Number of entries to know how many records are extracted Note The following items are not supported in the current version of QUERY e Radio buttons list boxes buttons on selection screen e Logical databases with Dynamic node e Any logical databse which has any additional screen such as a pop up which comes after the selection screen e LDBs Node Types are not supported e C Complex data object e A Typed nodes ANY at runtime such as PNP PNPCE Tables A table can contain from a few fields to hundreds By default primary key fields and index fields are listed at the top according to name and description Fields that are primary keys are represented by a key icon Y and index fields are represented by an inverted key icon QUERY also offers other means for you to easily find fields in a table 56 Logical Databases To find fields in a specific table e Right click the table and then select one of the following sort or search options on the shortcut menu o Sort by name Sort by ascending or descending order o Sort by description Sort by ascending or descending order o Default sorting By field name but with primary keys at the top o Sort by selection This option displays your selections at the top of the tables and is useful when you want to crea
59. hat exceed these limits the field must be trimmed to ensure a successful download Using the QUERY add in for Excel with TRANSACTION You can use QUERY add in and TRANSACTION add in together in Excel To download data edit it and upload query results from an Excel sheet 1 Select the published query file you want to run 2 Enter the details in the run settings 3 Click Run to execute the query The records are downloaded to the Excel sheet 4 Make the necessary modifications in the downloaded records 5 To open QUERY click QUERY add in in Excel 2007 or in Excel 2003 6 Select an existing TxR file that was created in the same file or template Edit the run settings 7 Click Run to run the TXR file The changes are uploaded to SAP 76 Running the query Running With QUERY you can run a query manually or you can perform unattended data downloads by scheduling runs for off peak hours to reduce system resource load For unattended data downloads if you provide an e mail address QUERY can send e mail notification or an attached copy of the results file when the run finishes Before you run a large query It is recommended that you first do a test run with a few hundred records Once you are satisfied with the test run you can proceed with your query Run Select result file fc Documents and Settings Ssharma My Docume E Sheet sheet Log column or cell p Start row 2 Extract all records 7 No of records t
60. ingle sign on With single sign on the SAP system OS uses the Windows credentials user name and password for each user Partner Specify this connection parameter if you select single sign on Specify the SAP password for the user Specify the language that the system EP URL en specify the enterprise portal Type the e mail address for the person who you want to receive notification by e mail message when the scheduled run is completed You can specify multiple e mail addresses that are separated by semicolon Attach data file to the mail Select to attach the results file to the e mail message Runon console If the query is to be run from the command line select Run on console 2 Click Test to confirm that your logon information works Click Next E mail notification 3 On page 2 for Excel text and XML files and for Access tables select one Once Schedules a one time run If you select Once you can set the Run on date Daily Schedules a daily run Perform this task Weekly Schedules a weekly run If you select Schedule Weekly enter the weekly frequency You can also specify the day of the week for the run The Weekly setting is not available for 80 The Scheduler pane SQL Server Monthly Schedules a monthly run If you select Schedule Monthly enter either the date each month for the run or the ordinal date You also mark the months for the run If you want the task to run even if you are
61. ist of SAP queries is available in the data dictionary To add an SAP query to the data dictionary e Right click Queries and click Add Query Or e Select user group and click the query you want from the list Data dictionary organization for infosets and queries The data dictionary for infosets and queries had two main areas the Standard area and the Global area These are based on the Query Areas defined in SAP 48 Adding items to and searching for items in query builder Standard query area Infosets queries in the standard area are client specific which means they are available only within the client in which they were created For example if you created a standard query on a production client the query exists only on that client Global query area Infosets queries in the global area can be used on any client Adding items to and searching for items in query builder To build a query you must first add items to the query builder area Logical databases Logical databases are special ABAP programs that retrieve data and make it available to application programs The most common use of logical databases is still to read data from database tables by linking them to executable ABAP programs Using logical databases in QUERY You can use logical databases with Winshuttle QUERY only if the Winshuttle Function Module is installed on the SAP server you use The query builder area is divided into three areas e LDB struct
62. last record extracted The cell H2 is mentioned in the expression Evaluation Cell refer to step 4 below A r Y Mapping J gt y AutoMap Change Destination Type Validate S No SAP Field Name Expression SAP Field Description Field Type Field Size Mapped to column Excel Expression Expression Evaluation cell Transform Orginal Mapping Expression Evaluation Desc IMARA MATNO IN IMARA MATNR Materi EA nn 2 MTART MARA MTART Material Type Sting 4 B BREIT MARA BREIT Width Decmal 113 D 4 HOHE MARA HOEHE Height Decimal 113 E 15 LAENG MARA LAENG length Decimal 1 F BRGEW MARA BRGEW Gross Weight Decimal 113 6 62 100 350 2 T Normalizec 3 Enter the appropriate Excel cell value in the Expression Evaluation cell This cell will be used to evaluate the excel expression and place the transformed data It is mandatory to enter this information In the example mentioned in step 2 the transformed data will be written into the column H starting from cell H2 Y s Validate s 4 To validate the Excel formula click Validate Note that the validate option is supported only for formulae that are entered in English 5 To choose to overwrite the original data with transformed data select Transform Original Mapping In the example below the formula is applied to MATERIAL NUMBER and the transformed value is written back in column A In this example column I is used
63. le to Excel worksheet columns or Access database fields To auto map query fields in the mapper Excel Access or SQL database e Inthe mapper click AutoMap The fields are automatically arranged in the result preview form gt Mapping 4 gt AutoMap Change Destination Type The AutoMap button as it appears in the Mapping pane The result file preview The result file preview consists of columns or rows into which you drag fields With this form you can see how your output file will look To change the type of output file Mapping Ca gt e Click Change Destination Type Change Destination Type Click Excel Access or Microsoft SQL Server The query field form S Row ID or sequence of fields for output SAP name usually a German acronym or 68 Auto map query fields to a data file Expression The table name and field name SAP Field Description A description in English of the contents of the field Field Type Byte decimal integer string date or time Field Size Number of characters in the field contents Controls automatic padding for numeric values of Padding the string fields Note If you compare non numeric data without padding QUERY may return unexpected results The letter of the output column to which a field is Mapped to Column mapped Available for Microsoft Excel and Microsoft Access Fields that apply only to Excel The excel formula you want to apply on Excel Expression corresponding
64. ll other setups only single table downloads are possible To create a join between two tables e Drag a field from one table and drop it into the other All downloads from joined tables are by an inner join all matching records are downloaded Important If you use multiple tables they must be joined If the tables that you query are not joined the query will not run To join a table to itself 1 Double click the table twice to add the table and its alias to query builder area For example if the table is MARA its alias will have the name MARA1 2 To create the join drag a field from the table or alias and drop it onto the alias or table All downloads from joined tables are by an inner join all matching records are downloaded Left Outer Joins You can create left outer joins in the QUERY builder This would allow data to be returned from one table even there are no matching records in the second table Note that outer joins can degrade performance Recognizing left outer joins You can recognize a left outer join in QUERY by the join line with an arrow pointing to the left table 53 Winshuttle QUERY User Guide 2 MARA General Material D X MAKT Material Descriptions X Material Pa K C MATNR Material 1 C SPRAS Language K JA MATKL Material C MAKTG i 5 JA BISMT Old mate C MAKTX A VHART Packagint C SATNR Cross Pla C14 PMATA Pricing R _ MA nrnna hl nans f art I
65. low the on screen instructions to complete your activation Note Location of the license file is dependent on the version of Windows run on the client machine For Windows7 Vista C ProgramData Winshuttle license For Windows XP C Documents and Settings All Users Application Data Winshuttle license 17 Setting preferences Automating your logon You can automate your logon that is create an auto logon file ALF to automatically enter SAP logon information each time you use a query file and to logon on to the system during scheduled unattended runs An ALF file stores your SAP logon information in an encrypted file format ALF Management Use ALF Management to create an Automated Logon File ALF to automate your log on to SAP You can use an ALF to schedule a run or when you want to use the same logon information each time you run one of your script files For more information about scheduling see Scheduling a Run When you save credentials you are not asked to provide a user name and password for that system and are automatically logged in to the system Select Tools from the Main Menu and click Options button to display the Options dialog box with the Application Defaults shown Then click ALF Management to display the settings shown below To create or modify an ALF 1 Choose one of the following responses Click Capture to create a new ALF file When the SAP SERVER logon dialog box is displayed type the logon inf
66. lt file you specified is ready You can add a reason for the run in the Run Reason field You can select to run the query now or later To run the query 1 Click Run Run 4 Select result file C Users margaretz Documents Winshuttle Que G 3 Sheet Sheet1 Log column or cell O 3 ys Start row 2 5 un 5 i ma No of records to 500 S be extracted No of entries a Run reason Run options Run Now M The Excel file opens and the results are displayed 2 Click the Back button You are prompted to save the qsq file Click Save The Most Recent Files and Favorites pane appears The most recently run query appears at the top You have completed your first query in QUERY Stopping a query run You can stop a running query by clicking the Stop button Stop useful if you inadvertently begin a large run The stop functionality is Note You can stop a running query only if it has been created with tables as the data source You cannot stop a query run that contains InfoSets SAP queries or logical databases 38 Introduction Infosets SAP queries and logical databases 39 Winshuttle QUERY User Guide Introduction Infosets SAP queries and logical databases SAP queries and Infoset queries are SAP end user query reporting tools They are valuable tools for generating customized reports In particular business users derive real benefit from them because they would otherwise rely on programming staff to gener
67. me WIDS3 Authentication SQL Server Authentication SQL Server Username sa ee Test Connection Select the name of the database for the connection Password Connect to database serverl i eost center y Connect Cancel 2 Type in the name of the server you want to connect to or select a server from the list 3 Select the type of authentication to use whether SQL Server Authentication or Windows Authentication 4 Do one of the following 72 Undo mapping o For SQL Server Authentication type your SQL Server user name and password o For Windows Authentication your user name and password are automatically captured o Click Test Connection to verify that your user name is authorized o Select the database and table Click Connect To save a file to SQL Server table al Save 1 Click Save to save the mapping instructions The Connect to Server dialog box appears Connect to SOL Server Server Name Authentication Windows Authentication v Windows Username Password Test Connection Select the name of the database for the connection Connect to database v Connect Cancel 2 Type in the name of the server you want to connect to 3 Select the type of authentication to use whether SQL Server Authentication or Windows Authentication 4 For SQL Server Authentication type your SQL Server user name and password You
68. n case two tables are added with left outer joins the joining arrow may not appear accurately However this does not affect the function of the joins 2 MARA General Material D X MAKT Material Descriptions X Material Pa JO L1 MATNR 4 4 MTART Material 1 C SPRAS Language K CI MATKL Material Cl MAKTG i 5 A BISMT Old mate C MAKTX NA VHART Packagin SATNR Cross Pla PMATA Pricing R AUF MATAR o Material f MFRPN Manufact Plant na MFRNR Number r M n cor Matarial To convert to left outer joins 1 In QUERY builder right click on the joining link between the tables 2 In the shortcut menu click Convert to left outer Join A warning messages displays stating that converting to left outer join may degrade the performance To remove or convert an outer join 1 Right click the left join 2 On the shortcut menu click Remove or click Convert to outer join Conditions for creating left outer joins e Maximum of 25 tables can be joined that can include inner and left outer joins e Only two tables can have left outer join between them in a query e The table on right of a left outer join cannot be a right table of any other join e For left outer join between two tables no other type of join is allowed across any of their fields e Any field in the right table that has a left outer join cannot be used as selection e Any query with a left outer join alter
69. ng QUERY Even with the variety of business intelligence BI systems that are installed and operational at most companies one of the top challenges reported by SAP end users is the lack of easy access to real time SAP data in the transactional system The ability for business users to easily query and extract transactional SAP data is critical for many purposes such as e Operational reporting e Data fire fighting driven by business changes such as to extract data for mass changes e Ad hoc reporting and prototyping steady state BI reports With Winshuttle QUERY you can securely and easily extract live SAP data into familiar Microsoft Excel and Microsoft Access formats for immediate and accurate analysis A variety of governance features and performance tuning features in QUERY ensure that user generated queries are secure and have minimal impact on the SAP server performance With RUNNER you can view queries that were created in QUERY change the mapping of those queries and then run them However to create or edit queries you need the full functionality of QUERY New Features in Winshuttle QUERY 10 3 e Queries based on tables can now be stopped during a run with the Stop button e Padding or leading zeros is now an optional feature e Queries can be scheduled in the background in SAP WFM can process RFC in background for multiple table QUERY scripts e Run time variables are now supported in query chains Getting help in Winshuttl
70. nning QUERY from the command line 82 Running the query 77 S SAP defaults 20 Saving a query 65 Scheduling a query run 86 95 Selecting 41 Selecting fields 55 Setting preferences 19 Specifying QUERY script properties 22 Starting a query 42 T Tables and the Winshuttle Data Dictionary 42 The QUERY screen 27 U Using saved queries 82 Using the add in for Excel 74 V Viewing logs 103 Viewing the end user license 8 W Windows error messages 113 Winshuttle customer service 8 Working with Winshuttle Connect 105
71. noonnonnnonnnononononononos 17 Setting preferences 19 Aviomaino your lO GON srann A 19 ALF Management O E aa EANES 19 Ihe ALF fSlA S neninn nnne A ie 19 SAPO unicidad 20 Client Connection seting ii een 21 Specifying QUERY script properties c sccseccseccseccssccsecssecssecssccssecssesssecssccssecssecssessseessecsseessessseessenss 22 Query script properties uu eeeccessccsssccesscccsssecsssecssecesecessecesseecsssecsssecsssecessecesstecsssecsssecsnsecessecesstecsueecs 23 QUERY 25 TAG QUERY SCT nininini E a EE iaaa 27 Starting with QUE ici RE Edle 27 TODS E E A A A A 27 The three step process and the three panel screen s ssessessssseesesessrssssrssseesesrestseesrseenrseserseseeses 28 TNE three pane screen essessseesssensssessnenensennnsnnensnnnnnnensnsenennnensenenssensnsensnsenensenenssnesssnnsssensssenensenn 29 A O 29 OlhernbuU lO Sica ia iio iii ie I ee I 30 Winshuttle QUERY User Guide STATUS O ONO 30 Introduction Getting started with QUERY ssssessessessessessessessessessessessessessessessessessessessessessessessessesss 31 Introduction Building QUETY seia A A AAA AAAA 33 Introduction QUERY Templates iia ati ia 36 TA EA NO 36 Introduction Mapping your fields to a result file ooonoccnoccnoccnocanonanonononononononononononononcno nono nonnos 37 INTFOGUETION RUNNING QUEMES aaien ana aee it 38 Introduction Infosets SAP queries and logical AOtabDAaSeS coccocccocccoccnonanonononononononononononononono
72. nstalling Winshuttle STUDIO Winshuttle STUDIO offers three installation options e Upgrade Select Upgrade to install STUDIO on a computer that already has a previous version of TRANSACTION or QUERY and to confirm that your current version of a Winshuttle program can be upgraded If your version of TRANSACTION or QUERY cannot be upgraded you need to choose Custom e Complete Select Complete to install all available Winshuttle products including TRANSACTION QUERY and DIRECT The files are installed to the default directory C Program Files Winshuttle e Custom Select Custom to install select Winshuttle products and to choose the destination directory Note On upgrade or re installation of version 10 0 new document folders My Documents Winshuttle will be created with the name TRANSACTION However your application options will not be re set to these new paths If you want to use the new folders we suggest you do that manually To upgrade to STUDIO 1 Insert the disc into your computer s DVD or CD drive Installation begins automatically 2 If the installation does not automatically begin click Start on the task bar and then click Run Click Browse to go to the CD or DVD drive Then select Setup to begin the installation The first page of the wizard click Next 3 Read the License Agreement page and if you accept the license terms click I accept the license terms 4 Add the user name and company name 5 On the Setup
73. o Mapping to automatically map all the query fields In the mapping stage of the query process you map the fields of your query to the columns of your output file Each column header corresponds to a field RUNNER provides easy drag and drop editing of fields for queries that are already mapped To change query mapping e Inthe Mapping pane click Update query mapping Mapping fields manually To map fields manually 1 From the Map pane click Create mapping The Mapping screen appears including the query field form and the empty preview of the results file 2 Drag fields down from the form to the file preview area in the order you want 3 From the Map pane click Update mapping The Mapping screen appears including the query field form and the preview of the results file 4 Drag fields down from the form to the file preview area in the order you want Winshuttle QUERY User Guide Auto map query fields to a data file To map fields to an existing or new data file from the map pane Excel or Access 1 From the Map pane click Auto Mapping 2 The Auto Map File dialog box appears 3 Specify the type of destination file for the data whether Access or Excel 4 Do one of the following o To create a new file specify the path and name of the file or o TO map the data to an existing file click the folder icon to locate the file Click OK You can use Auto Mapping to automatically map all data fields in the query fi
74. o Po be extracted No of entries Run reason Run options Run Now y The Run pane with an Excel file open The Run pane contains the following options Select Result file or Specify a result file or database The default is the file or database that database you saved during the mapping step Sheet Table The sheet or table to which the data is sent The default is the sheet or table that you specified during the mapping step However you can Excel Access and SQL specify another sheet or table databases only Reconnect and Refresh Click Reconnect and Refresh to display a list of all tables in the system database tables only 2 You can then change the table to which data will be downloaded Record delimiter text files The character used to separate the values of each row of data Select only from 7 amp F i ar gt and TAB Header delimiter text files The character used to separate the values in each header row Select Winshuttle QUERY User Guide only from amp _ and TAB St Specify the row wherein you want QUERY to begin adding records art row e Available only for Excel files Log Column Cell Excel Specify the column and cell or column in which SAP log information is files and Log Column written This information appears automatically Access and SQL files Extract all records check Select this check box if you do not know how many records are
75. o be available to users The user setting appears in the Advanced Run Options pane Using a patent pending algorithm QUERY adaptively throttles the execution of the query on the SAP server based on the current load on the SAP server The available server resources are calculated from the total number of open dialog processes When over 50 percent of the dialog processes on the SAP system are available the query is executed at full speed but when the available server load starts falling below 50 percent the execution of the query is dynamically slowed Adaptive throttling addresses the performance concern around querying on a live transactional system that query execution could take substantial computing resources that might compete against regular SAP transactions and adversely affect the performance of these transactions QUERY addresses this issue by allocating only available compute resources to execute queries in the live SAP system The graph below shows the query performance time against the system load with and also without the adaptive query throttling algorithm applied When the throttling algorithm is applied query execution slows as the system load increases however without the algorithm the query executes in constant time and competes for resources with other transactions on the system 107 Winshuttle QUERY User Guide Adaptive Query Throttling un w n S i w N Regular Query N 1
76. ocations Old scripts and data files are retained in legacy folders Using a CD ROM To install software using the CD ROM 1 Insert the CD ROM in your computer 2 Installation begins automatically 3 If the installation does not automatically begin select Start on the task bar and then select Run Select Browse to go to the CD ROM drive Then select Setup to begin the installation 4 Follow the on screen instructions to complete the installation Winshuttle QUERY User Guide Downloading the installation file from the network To install the software by downloading the installation file 1 Click the link that you received in email 2 Download the Setup exe file and save it on your local drive 3 Double click Setup exe and follow the on screen instructions to complete the installation 4 When the installation is complete follow the appropriate procedure to activate the software For more information see Activating through Winshuttle Connect Begin using QUERY Installing a product by using the single MSI or enterprise set up Prerequisites When installing MSI in silent mode enterprise deployment ensure that Microsoft NET version 3 5 SP 1 is installed on the target systems otherwise the silent MSI installation will abort Installing a major upgrade with the single MSI setup For MSI you need to pass a command line option SILENTINST and the value 1 needs to be set for it At the command line msiexec i product m
77. on on a network Ensure that you have read write permissions for the folder that is set as the data dictionary location Default path for Data Dictionary Files C Documents and Settings ritikat WSE Application DatalWinShuttle QueryShuttle Dat B Read Write permissions required Microsoft Excel defaults Sheet name Default Excel sheet to use when running your query file The default is Sheet 1 Start row The default row is 2 Log Column Cell Specify the default column or cell into which log information is written Disabling and enabling the RUNNER add in If Excel files take a long time to open or if the add in is incompatible with the Excel add in or with other applications e Click Disable Runner Add in Other defaults Default number of records to download Specify the default number of records for each download Error management To use error management tools e Select Tools from the Main Menu and click the Options button to display the Options window and then click Error Management Trace The trace log can be used to help find the cause of problems you might experience with the program When this option is enabled it traces all the actions you have taken in the software and writes them to a file The trace file is available under C Users lt user name gt My Documents Winshuttle QUERY LOG or at your default path for log files 98 Error management If you experience a software problem check the trac
78. on server warning Production server warning Prod Svr Warning You might not want to run critical data on a production server With the Production server warning box you can maintain a list of SAP production servers Each time you log on to a listed production server a warning appears to remind you that this is a production server To display production server warnings o 1 On the Tools tab click Options 2 Click Prod Svr Warning 3 To see a list of available servers click Add 4 In the SAP Logon pad select the server you want to add and then logon with your user name and password 5 The server is added to the list Do this for every server you want to add 6 To enable the warning select the Enable Warning check box To delete a server from the production server warning list o 1 On the Tools tab click Options 2 Click Prod Svr Warning 3 Select the server you want to delete from the list and then click Delete Customizing QUERY Application Defaults In QUERY you set options from the Options button on the Tools tab The Application Defaults settings contain Path defaults Type any of the default file paths for the following files Winshuttle QUERY User Guide e Result files specify Excel Access or text e Query files qsq files e Log files and trace files e Auto logon file e Data Dictionary You can set the location where the data dictionary is saved You can even save to a locati
79. ormation for SAP to capture the ALF information automatically This is the easiest and error free way to create an ALF You can also mark the Use Logon Group check box and type the Logon Group and Message Server Host settings to save a logon group in the ALF Or Click Open to display an existing ALF file to make changes to the file Click Test to perform a test of your log on information A message is displayed to confirm that your log on was successful 2 Click Save to store the ALF when you are finished NOTE ALF files are encrypted which makes it difficult to open and read your SAP password and other information However if the ALF file is accessed it could be used to log on to SAP by any other person who has authorization to use QUERY For this reason we strongly recommend that you protect your ALF file either by using Windows system encryption to prevent others from using it under their user name or by storing the file on removable media that is password protected The ALF fields SAP System Name Specify the SAP system name Application Server Host Enter the host name Winshuttle QUERY User Guide System number Specify the system number Us todon Grou Select this check box to update g P logon group information Logon Group Type the auto logon group information Message Server Host Type the message server host information Enter the client number Specify the user name Specify the SAP password for Specify the l
80. pear in the criteria pane 2 To specify criteria for a field click Selection The default selection type is Run Time If you want the criteria to be fixed set the selection type to Fixed The WHERE clause builder opens Do this for all your fields 3 Click the Back button to move to the mapping stage When you are prompted to save the file click Yes 4 In the Save As dialog box enter a name for the query file and click Save You are now back in the three pane screen and are ready for the mapping stage 34 Introduction QUERY templates Select Map Query file has Query File has been created been mapped gt Update Query Update Mapping The map pane of the three pane screen after a query has been built and saved 35 Winshuttle QUERY User Guide Introduction QUERY templates QUERY provides query templates which guide you through the most common queries used for accounting finance material master transactions and help you to customize your own queries Templates are made available online and can be downloaded to build queries You can browse all templates or browse only Excel templates or only QUERY scripts When you select a file the file name description and size User assistance documents such as step by step guides and videos Collateral Link called collateral are available by clicking the in the product screen Creating a query using Winshuttle Templates 1 On the Select pane click Create Query
81. ption AEKNZ Change indicator ERSDT Date of creation ERNAM Name of Person who Created the Object AEDAT Changed On AENAM Name of Person Who Changed Object WPTXT Maintenance Plan Text STRAT Maintenance strategy ABRHO Scheduling Period EQUNR Equipment Number TPLNR Functional Location TERN TERN VA EE 43 Fields Present LDB structure view The hierarchy of logical database nodes and the parent child relationship is shown in this view Note Fields from two nodes which are at same level in hierarchy cannot be selected Using the previous image as an example if five fields are selected in the node DIPLKO and you select two fields in the node DIAUFK an error will occur LDB Node View Includes LDB node details like name description and table details Use the Search field to search for required fields To select and deselect all fields right click in screen Tables what tables look like The Winshuttle Data Dictionary displays the most commonly used tables The tables display the following information e Functional area such as Finance Accounting Human Resources Production Planning e Anode for transaction codes which lists the tables used in common transactions e Descriptive text to make it easier to identify the table e Unique icons to distinguish different table types such as cluster pool and transparent tables and also table views To add tables to the query buil
82. r details are automatically filled in case of Windows Authorization 5 Click Test Connection to verify that your user name is authorized 6 Select a database where information will be stored Click Connect The data movement happens only within the database so you do not see the file open However the status bar displays a message to show the file name the server on which it was created and the database in which it was stored ells anstnd en WIDS3 Saryerfurmer Undo mapping After selecting a particular destination type user can undo any step performed in mapping The number of levels of undo is unlimited 73 Winshuttle QUERY User Guide To undo mapping e Press CTRL Z Embedding a query file in an Excel worksheet From the Run pane you can embed the active query file in an Excel file or template Then you can send the Excel worksheet that contains the query file to business users To embed a query file in an Excel spreadsheet 1 Click Publish query file to embed the query file in an Excel file or template The Publish query File dialog box appears 2 Type the file name you want to save the file under The file extension is xls for a file or xltx for an Excel template 3 On the Export to Template dialog box type the file path to the Excel you want to use You can also click the folder icon on the right to browse to the query files folder Enter a short description of the purpose or use of the file The current
83. r you start the program again The Migrate to Connect dialog box indicates that you have a Connect license 7 Type in your user name and password Click OK The program opens Your license is now a part of Connect 8 To ensure that scheduled runs can execute click Remember Login Activating without the Internet For a first time installation of a Winshuttle version 10 3 product if the computer that hosts your node locked license is not connected to the Internet you must install a 10 2 license of the product before you can upgrade to 10 3 Deactivating a License To deactivate or revoke your license e See your Connect license administrator Activating a Node locked Fixed Desktop or FlexNet License Activating a Node locked Fixed Desktop or FlexNet License 1 On the splash screen that appears the first time you start QUERY click Activate Product 2 On the QUERY license screen click Node locked Network 3 Enter the Activation key in the License Activation window along with the requested user information and then click Activate License Click Next 4 Do one of the following e If connected to the Internet o On successful evaluation license activation you have the option launch the program selected by default Click to clear the Launch Application check box if you do not want to launch the application immediately after license activation Or e If not connected to Internet o Internet Explorer opens Fol
84. re that you have registered with the Connect account On the splash screen that appears the first time you start QUERY click Connect The Connect Login dialog box appears Type in your user credentials Select the Remember my login check box to open the program again without entering credentials To work offline and to schedule runs Remember my login must be selected Click OK The program opens 15 Winshuttle QUERY User Guide Migrating to Connect If you already have a Winshuttle product license you must migrate to the Connect license when you upgrade your Winshuttle product To use Connect you must be connected to the Internet NOTE After you migrate to Connect you cannot migrate back to a flex license To migrate your license to Connect 1 Upgrade from Winshuttle product version 10 2 to 10 3 2 Click the program icon 3 The Migrate to Connect box appears 4 By default I would like to request Connect access is selected Click OK to the request for migration to your Winshuttle license admin You will be notified by email when a license is available for you The process may take a few days 5 If you do not want to migrate your license click Do not show this at startup and then click Skip this step The program opens by using the node locked license To migrate later click the Migrate to Connect button Migrate to Connect at the bottom right corner of the home screen 6 When a Connect license is available fo
85. reporting tools These are very valuable tools in generating customized reports The real benefit is for the business users who otherwise will have to rely on programming staff to generate reports from the system You can access SAP queries and Infosets which are already created in SAP using Winshuttle QUERY You can use Infosets and SAP queries only if it is enabled in Administrator options In addition only one Infoset SAP query can be added to the query builder area at a time Joining of more than one Infoset SAP query with tables is not supported Security handling The table containing authorization objects for fields is used to securely extract records for Infoset SAP Queries For more info see Authorizations on page 108 Winshuttle QUERY User Guide Security is applied on Infoset Query corresponding to the presence of table and field in authorization table For example if MARC WERKS is present in Infoset Query it is checked in a single table authorization table If it exists the data is filtered accordingly There is difference between how it is handled for Infoset query and SAP query e For Infoset query It does not depend whether you have selected a field as output or not e For SAP query Security is applied only to output fields It is not applied to selection fields Starting a query The selecting stage is the first stage in the query builder process of QUERY You are defining your query You have options for how you
86. riables If you added run time variables to your query you can call them when you run the query from the command prompt Variables take the following form rtv TableName FieldName Valuel Value2 Value3 Value4 Value5 Value6 The hash symbol is used for separation between run time variables values Values should be in same sequence as they are defined in Query Where Clause Builder screen The Tilde symbol is used for separation between run time variables The upbar pipe symbol is used to separate variable values in which between and in are used Parameter Format is as follows TableName FieldName Valuel Value2 Value3 Value4 Value5 Value6 TableName FieldName Valuel Value2 For example the first field name has four run time variables in which between and in variable values are separated by the upbar The hash symbol is used for separation between run time variables value Here is the example of file variables For txt file TableName FieldName Valuel Value2 Value3 c testvalue txt TableName FieldName Valu el Value2 For Excel file TableName FieldName Valuel Value2 Value3 c testvalue xlsx Sheet name Column name Start row End Row TableName FieldName Valuel Value2 Note If End Row will be given as O then the last value considered will be one having 5 empty rows after it Tilde example If more than one run time fields are present in the query they should be separated by a
87. ript properties To enter or edit properties of your script 1 Do one of the following e Click New Or e Click the QUERY Script Properties button 2 Enter options as desired Click OK Query script properties Data source Specify which of the following data sources you want to use in the query e Tables e Infosets SAP Queries e Logical databases Title Specify a title for your query A title is required information An example of a title for a query is Vendor Account Balance Report NA Lock query file Select the Lock query file box to apply a password to the query file Password and Confirm Password In the Password box type a password for the file and then in the Confirm Password box type the same password SAP system Indicates the SAP system on which the query resides The information in this field is supplied by the system and cannot be edited Created by Indicates the user who creates the file The information in this field is supplied by the system and cannot be edited Date and Time Indicates the date and time when the file is created Purpose of file Specify a reason for the file An example of a reason is Provide a report for a vendor in North America with past due balances Comments Enter comments about the query file 23 QUERY The QUERY screen QUERY provides a look and feel that is similar to other Winshuttle products and to other programs that you use every day Starting with QUERY
88. si qb SILENTINST 1 Installing a minor product update by using the single MSI setup If you install an update to an existing product installation you must do so from the command line 1 At the command line set reinstall all 2 Set the command reinstallmode vomus In all but the most advanced scenarios the command reinstallmode should be set to vomus The following is an example of a typical command line for setup msiexec exe i product msi reinstallmode vomus reinstall all Installing a minor product update by using the single MSI setup If you install an update to an existing product installation you must do so from the command line 1 At the command line set reinstall all 2 Set the command reinstallmode vomus In all but the most advanced scenarios the command reinstallmode should be set to vomus The following is an example of a typical command line for setup Installing Winshuttle STUDIO msiexec exe i product msi reinstallmode vomus reinstall all NOTE It is critical that you use the command line installation only when a previous version of the product already exists on the target computer If you set the properties when the product does not already exist on the computer the installation appears to run in minor upgrade mode but your application files may not be installed Installing a major release from the command line For a new installation where a previous version does not exist do not set reinstall all I
89. ss 40 Niroduetion to LOBS ns ee A A a E E a id 40 Selecting 41 DAISESOUTESS ra A 41 EM ERE GS E a POETER O 42 Tables and the Winshuttle Data DiCtiONAIY ccooccnocccconnconanaconnnonanaconanonananonnonnnn conan oconnncnnnnnonnnconnnos 42 Adding tables and transaction codes to the Winshuttle Data Dictionary eeen 45 AA e e AAA ehe E E a ATE a AE EAR TE 46 Zooming in on tables and the query builder ooocoocccocccocncoonconnconnonnnonnnononono nono nononono nono nonononnnos 47 Data dictionary organization for infosets and QUETIES oooconcccnoncnonononononononononononononono nono nono nono nonnss 48 Adding items to and searching for items in query bDuilder oooocooccocccooncoonconnonononnnonononononnnos 49 Creating joins between tables csccesccssccssccssccesccssccssccssccssccssccssccssccsscesecsncessecssccssesesecsneceteesteces 52 Conditions for creating left outer JOINS esessessessessessessessessessessessessessessessessessessessessessessessessessessesses 54 JOINING with infosets and SAP QUETICS ceccesscesscesscesscesecesecesecesecesecesecesecesscesecesecesscesscesscesseeseenseees 55 AEE A E e EE E E EEEN AREN EEN REEN E RINEN E E EEEN T ERE 55 Inf sets andiSAR Queries ossicles sanecseansseeascabelanadsrcuaasegadisauareusecsayelosauavectesagelasaanneearansieuiaaavecnaeniete 55 Logica Dat atan i ARABS Oe ANRHE MU A 56 Using eriteriarto define TENS iii irc 58 Theskike operator and Mildred adit hi
90. ss atta Keene 61 NUMbEROR SATMSS ll dai ot aid tddi 64 SOW IRIS VIS Wisin Se a a ae esse en Drae dee rn nee Dre Peine een eure aAa O pirenaica 64 SAOWIQUEIY autos ess be dd tia 65 SAVINO QUE iia li a iii 65 Best practices for query NAMES cccccsssccsssccsssccsssccessecesseecsscecsssecssecesseeesscecsssecsssecessecesseeesseecsneeenss 66 Mapping 67 Mapping tlelds Manva sis scsteoditise Rite Ann Mn ANNE ARMA ARE SRA ARR ARMA ARES ARi 67 Auto map query fields to a data file oo ee eee eescccescccsssccessccessecessecesscecsssecessecesseceeseecsseecssecstaeeesaes 68 Winshuttle SERVER fOr QUERY rue dann sasisinninsteneneisilelenne nein aha fee 71 WAG ONGOING usais seta asedadaaed Ms 73 Embedding a query file in an Excel worksheet oooocococccocononononononononononononono nono nono nono nono nono nono nono conos 74 Contents Using TECATE A A einen 74 Running the query 77 A O Maida awa Henne ON 77 AG GIANG TUN TIME VOOS sica eles A Libabtescauhabul SasacdwhcaShalus Liiatdescatheluliatasaeladed 78 SCHEQUIING A TUN iii idad 79 TES ENS AUMENTE A A AAA A AAA AA 8 STOP DING A GU SY TUN ati a A alen sr end oni E ia idas 82 USING SAVE JUSTES DAA AA A AAA AAA 82 Running QUERY from the command liN8 oooccnoccooncoonconnonnnononononono nono nono nono nono nono nono nono a 82 Recording transaction codes ccccseccseceseceseccseccseccsecescescescesecescssesssesesesssessensssesssesssesssesssessseseess 87 Advanced run options 89 Managing
91. st Testquery qsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery alf For Run program screens appear C Program Files Winshuttle RUNNER for QUERY xSHUTTLE exe run C Test Testquery qsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery al As a console application C Program Files Winshuttle RUNNER for QUERY xSHUTTLEcom exe run C Test Testquery gsq rfn C Test Testquery xls rdt Sheet1 nrc 1000 srw 10 alf C Test Testquery alf QUERY commands All commands that run at the command prompt take precedence over the same commands that are already contained in the query file QUERY overwrites the existing query file commands in memory with the commands passed from the command line NOTE It is recommended that you first test these commands in a non production system with a limited number of transactions before deploying the query script opn C Test Testquery qsq en open This token can only be used with queryShuttle exe rfn C Test Testquery xls Result destination for rdt Excel sheet name table rdt Sheet1 name for Access and SQL Server oe Start row in case of srw 10 excel only i rtv Tablename Fieldname variablename variabl rtv Run time variables evalue 83 Winshuttle QUERY User Guide E mail Addresses eml 1 test com 2 test com Attaching result file with arf arf mail are
92. ta Dictionary LDB Node Description 5 Search in SAP Data Dictionary Search The Search in Winshuttle Data Dictionary dialog box with options common to all sources To search for a data source 1 At the bottom of the Winshuttle Data Dictionary click Search in Data Dictionary Search In Data Dictionary E 2 In the Search in Winshuttle Data Dictionary dialog box enter a item name field name a description of the item you want or a description of the field you want You can use wildcards to narrow your search and you can apply case sensitivity to the search 3 Click Search in Winshuttle Library All tables that contain the string you searched for are listed Select the items you want The items appear in the query builder 4 To add the selected items to the data dictionary click Add a Table to the Data Dictionary The table is added to data dictionary and to the query builder 5 If you cannot find the item in the Winshuttle Data Dictionary click Search in SAP Data Dictionary and then try again The selected items appear in the query builder area and are also added to the Search Results node in the Winshuttle Data Dictionary pane if they are not already in the data dictionary If the found tables are in the data dictionary they are highlighted in the nodes that they belong to Creating joins between tables Relationships between tables increase the power of queries In QUERY relationships or joins are created
93. te joins between two tables that contain many fields o Zoom In on Table The Fields Selection windows shows field names and descriptions You can search for fields by using a whole name or use the Begin with setting to search by using the first letters of a name o Begins with Select when you are sorting on search fields to search first on field description and then on field name Fields Selection for SKB1 G L account master company code Search Field lv Begins with Is Primary Field Name Field Description Field Size Field Type Interest calculation indicator 2 String br _ pr eg WEN A PP ge A table after you zoom in With the Begins with setting selected the letter v was entered as the search field It returned two fields whose names and description begin with a v To select fields for output e Select the check box next to the field you want to add 5 SKAT G L Account Master KTOPL Chart of A A selected fields in the G L Account Master table Y SAKNR G L Accou Y SPRAS Language O McoDi Search Terr TXT20 G L account Mv TXTSO G L Accou To select all fields e Right click the table and then click Select all fields on the shortcut menu Winshuttle QUERY User Guide To deselect all selected fields e Right click the table and then click Deselect all selected fields on the shortcut menu Using criteria to define fields e Inthe Criteria form you specify how the fields
94. teria in an Infoset These selection fields have been set during the creation of Infosets on your SAP server For SAP queries you cannot select or deselect any output or criteria fields However you can modify the criteria For example you can change the selection type to Fixed from Runtime change the Where Clause details or make the criteria mandatory Note AND expression and IN operator are not available for Infosets and SAP queries e Click Preview run to test the designed query e Click Number of entries to know how many records are extracted 55 Winshuttle QUERY User Guide Logical Databases Once the logical database is added to query builder area select the output fields The selected output fields appear in the criteria pane Selection fields are the fields on which the criteria is set You cannot deselect any of the existing selection fields or add new selection fields You can use only what is already set inside the logical database you want to use However you can modify the criteria For example you can change the selection type to Fixed from Run time change the Where Clause details and make the criteria mandatory Note AND expression is not available for logical databases on Where clause builder In the WHERE clause builder only those logical operators are available that are applicable to the selected field For example if there is a field on the selection screen of an logical database which requires a ran
95. terial A table highlighted in the data dictionary Custom categories To add custom categories to the data dictionary 1 In the data dictionary pane right click Winshuttle Library to add a category to the data dictionary or right click an existing category to add a new category to a functional area 2 In the shortcut menu click Add Category 3 In the Add Category dialog box type a name for the category and then click Add The new category appears as the last item in the list To rename a category e Right click the category In the shortcut menu click Rename category 46 Zooming in on tables and the query builder To delete a category e Right click the category In the shortcut menu click Remove category Warning If you delete a category all the subordinate categories and tables will also be deleted Favorite tables If you use tables frequently you can add them to the Favorites heading in the data dictionary With a Centralized Data Dictionary you can assign favorites which are not written to the database To add a table to the Favorites heading 1 In the data dictionary right click on the table name 2 On the shortcut menu click Add to Favorites To remove a table from the Favorites heading 1 In the data dictionary right click on the table name 2 On the shortcut menu click Remove from Favorites Zooming in on tables and the query builder To see details in fields and table joins when multiple ta
96. th the Favorites button you can add your query to the Favorites list in the Recent Files and Favorites pane at any time The Remove favorites button removes files from the Favorites list a Status bar The following items are displayed in the status bar M sa WEG EN The status of the SAP connection whether connected or disconnected is indicated on the left hand side of the status bar The version of the Winshuttle function module available on your computer The current language of QUERY is indicated on the right hand side of the status bar Select English French or German Dutch or Spanish The changes appear when QUERY is restarted Introduction Getting started with QUERY Extracting information in QUERY centers on a three step process Select Map Run so that you can easily build and execute your query After each step of the process you return to the three pane screen You can output your query to Excel Access text or SQL Server files For the purposes of this demonstration we will output to an Excel file Production servers versus non production servers When you first create a query it is good practice to build and test the query on a non production servers Creating a query To begin a query 1 In QUERY click New In the Select pane click Create Query t Winshuttle QUERY a m o ES File Edit Tools Help a g SAP Certified a Powered by SAP
97. time that QUERY waits during a query execution before it times out To set a query execution time out e Inthe Query execution time out box type how many seconds you want QUERY to wait before it times out NOTE If you have the Winshuttle function module installed on your SAP server you can maintain the query time out centrally by maintaining it in the table WINSHTLQ QREPRM If you set this value both on SAP and on the QUERY client the lowest duration of the two is chosen Changing the admin options password To change the admin options password 1 Select Change Password 2 Type your old password 3 Type your new password Type the new password again to confirm Editing SAP authorization objects in QUERY When you work with an SAP system that does not have the QUERY function module installed you must add the authorization objects for each user so that organization level security is maintained You can edit or delete the authorization objects from within QUERY 106 Adaptive query throttling To edit authorization objects 1 Click the Edit SAP Authorization Objects link 2 In the Authorizations pane locate the object you want and then click Edit To delete an authorization object e Inthe Authorizations pane locate the object you want and then click Delete To add a new row for a new authorization object e Click Add a new row Adaptive query throttling The administrator must give permission for this setting t
98. ttleadmin It is recommended that you change the default password to a unique password To edit admin options o 1 On the Tools tab click Options 2 Click Admin Options 3 Type the password Click OK To allow users to create and remove joins between tables e Select Enable Creation of Joins in Query Builder To allow users to create joins on cluster and pool tables and on views e Select Enable joins for cluster view pool tables To enable the use of Infosets and SAP queries e Click Enable Infosets SAP queries To enable left outer joins 1 On the Tools menu click Options 2 In the left pane click Admin Defaults 3 Select the Enable Left Outer Join check box To allow all records to be downloaded e Select No Limit On Records to Download To enable adaptive query throttling e Select Enable adaptive query throttling The setting is enabled by default Winshuttle QUERY User Guide To allow users to manage string padding of criteria e Select Allow users to manage string padding of criteria To specify the maximum number of records a user can download e Inthe Max number of records to download box type the number you want The default is 1 million records To specify the maximum number of tables a user can add to the query builder e Inthe Max number of tables in query builder box type the number you want The default is 4 tables Query execution time outs You might have to specify the length of
99. uery file Log Column or Cell Type the Excel column where the log message has to be returned If left blank the log message is written to the first cell of the last column Extract all records Select this option if all records are to be extracted No of records to extract Enter the required number of records to be extracted This gets disabled if Extract all records option is selected This is a required field If you do not specify a value the value is set to 1000000 Write Header Adds or removes the column header to the output file Run reason Enter a reason for the run This is a run time setting and is not saved in the Excel sheet or in the published query file Run Click Run to execute the query and download the records Number of Entries Click on this to get the number of records that will be extracted using this query Status bar This bar provides the logon information It can also be used to log off from the currently logged in system 75 Winshuttle QUERY User Guide Running queries from the QUERY add in for Excel To run a query in Excel 1 Select the query file or published file you want to run 2 Enter the details in the run settings 3 Click Run to execute the query The records are downloaded to the Excel sheet In Excel 2007 each cell can accommodate 8192 characters and in Excel 2003 each cell can accommodate 1024 characters If a long text field that was used in the query contains characters t
100. uery indicates which column it has been added to QueryFile Material Movement Analysis File Name Material movement analysis qsq Mapping CA gt Change Destination Type Field Type Field Size Mapped to column S No SAP Field Name Expression SAP Field Description 2 AUFNR MSEG AUFNR Order Number String 12 3 BWART MSEG BWART Movement Type Inventory Management String 3 Cc 4 4 CHARG MSEG CHARG Batch Number String 10 lo ee 5 DMBTR MSEG DMBTR Amount in Local Currency Decimal 13 lE Fu 6 EBELN i MSEG EBELN Purchase Order Number j String 10 F 7 INSMk Imses n SMK stock Type String li G 8 LGORT MSEG LGORT Storage Location string 4 ln y lo MEINS MSEG MEINS Base Unit of Measure String 3 f 4 MENGE MSEG MENGE Quantity Decimal C Documents and Settings margaretz My Documents Winshuttle querySHUTTLE Datalmateriam movement xlsx Change Sheet Sheet SEG MATN Material Number 3 Click Save You are prompted to save the preview Click Save again 4 Click the Back button You are now back in the three pane screen and are ready to run your query At any time throughout the three step process you can add a query to your favorites list so that you can quickly find the query Click Add Favorites The file then appears in the Recent Files and Favorites pane 37 Winshuttle QUERY User Guide Introduction Running queries The resu
101. un or the ordinal date You also mark the months for the run Runas If you want the task to run even if you are not logged on enter your Windows user name Managing query files Select the time of day when the scheduled task Start Time is to take place Enter the password the user provides to log Password onto Windows When selected the user must be logged on for Run only if logged on the scheduled task to be completed This setting is not applicable for Windows 7 Managing query files The query manager provides a way to see an overview of the most relevant query files and to search for relevant query files on your computer To open query manager e On the Most Recent Files and Favorites pane click Query Manager Query Manager _ To see details of a file e Click a file in the List of files pane on the left to see its details appear in the main pane To search for a file in within a file or across Query Manager e Type a user name a field a table name or a date and then click Search You can use wild cards The returned files are listed in the main pane To open a file in Query Manager e Double click on a file in the list in the left pane or main pane to open the file in the three pane screen To delete a query file 1 In the List of Query Files right click the file you want to delete Click Delete Query You are prompted to confirm whether you want to delete the file Click Yes 95 Producti
102. ure view The hierarchy of logical database nodes and the parent child relationship is shown in this view This will assist you in selecting relevant output fields Note Fields from two nodes which are at same level in hierarchy cannot be selected For example if five fields are selected in the node DIAUFK PM order and you try to select fields from DIPLKO Task list header the action will fail e LDB Node View Lists the logical database node details like name description and table details You can search for required fields using the search bar You also have an option to Select Deselect All Fields on right click of mouse e Criteria pane Similar to tables infosets and SAP queries criteria pane is also available for LDBs where you can go set desired criteria To add a logical database to the query builder e Drag and drop the logical database into the query builder Logical database structure in the query builder There are two views to logical databases 49 Winshuttle QUERY User Guide LDB Structure View re Er WPI Maintenance plans LDB Node Name DIMPLA Eam 2 DIMPLA MaintPlanHeader LDB Node Description MaintPlanHeader ee DIMHIS MaintPlanCalls En E a DIMPOS Maintenance item Search Field Y Begins with sm DIAUFK PM Order i a DIPLKO Task list header E DIILOA Location ILOA Y WARPL Maintenance Plan 12 DIOBIK Object List z 7 i Output Is Primary field Name Field Descri
103. uthorization group DICBERCLS amp NC amp Activity ACTVT 03 Display 109 Windows error messages If the file you are trying to run contains macros you may receive a security warning To enable macros in Excel 2003 1 Click the Tools menu 2 Click Macro and then click Security 3 On the Trust Publishers tab click Trust all installed add ins and templates 111 Index A Activating a Winshuttle license 15 Activating QUERY 11 Activating the 15 Day Evaluation Version 15 Adding items to and searching for items in query builder 49 Administrator options 53 107 Advanced features 107 Advanced run options 71 91 Authorizations 39 41 110 Automating your logon 19 91 C Client Connection Settings 21 Creating joins between tables 52 Customizing QUERY 99 D Data sources 41 E Embedding a query file in an Excel worksheet 74 Error management 100 G Getting help in Winshuttle QUERY 7 l Installing QUERY 11 Introducing QUERY 7 Introduction Building a query 33 Getting started with QUERY 31 Infosets SAP queries and logical databases 39 Mapping your fields to a result file 37 QUERY templates 36 Running queries 38 L Legal 2 M Managing QUERY 95 Managing query files 97 Mapping 67 N New Features in Winshuttle QUERY 10 3 7 P Production server warning 99 Q QUERY 7 25 R Recording transaction codes 89 Running 77 Ru
104. x to enable linking of QUERY scripts In the text box specify the name of the next QUERY script to be linked e You can click Search button to find the next QUERY script as needed e Data File Carry Settings In addition to specifying the QUERY script name you can also mark which execution settings are to be carried forward to the next QUERY script by using any of these checkboxes o File Use the same data file o Sheet Use the same sheet o Start Row and No of Records Use the same Start and Number of Records Both Start and No of Records settings are carried forward together Approval process for linked scripts e You can send linked scripts for approval but only one review process applies to all scripts e You can run a linked script from Excel Winshuttle QUERY User Guide e If no data review is required you can run the data sheet for linked scripts from QUERY Validating linked scripts Linked scripts intended for Excel output can be validated Validation on linked scripts is possible for the following configurations e Column based mapping and cell based mapping in Excel forms e Column based files linked to cell based files and vice versa If the second file uses cell based mapping with loops you are prompted to provide the name of the log column before the second file runs Linked scripts with run time variables You can link query scripts with multiple run time variables While running the linked query scripts
105. you have chosen are processed in the query You also specify criteria that further define the query on the selected fields Fields in the criteria pane Column name Details Select this if you want the field to be displayed in the Output result set If you clear the check box you can still use it to build a Where clause and use it to filter data Select this if you want to set criteria on a field Shows the SAP field description You can edit it and enter your own custom description The expression table name Field name is displayed Technical name here Field description Selection type Using this option you can choose either to predefine the criteria or set it during run time Where clause Click the ellipses Le to launch the Where clause builder and set the criteria Select this option to make it mandatory to enter criteria Required J during run time Select the source for the lookup values SAP Optional Either look for values in SAP or manually enter any value Lookup type SAP only Select the values from the list SAP provides List Select values from the customized list created by the query designer Controls automatic padding for numeric values of string fields Padding Padding is applied automatically for Num Char Numchar and string values but you can select to remove it Padding improves the accuracy of your data extracts and is important for extracting the correct 58 Using criteria to define fields

Download Pdf Manuals

Related Search

Related Contents

VIASYS Vela Ventilator Service Manual  Guia de boas-vindas do suporte técnico PDF  Here  HERCON® VAPORTAPETM II INSECTICIDAL  Interface with Depositary Systems User Manual  ARCHIVES REVUE DE PRESSE FRANCE BIOTECH  『 レビス ® インスリンキット（ブタ用） 』取扱説明書  - Tipo - Aker Security Solutions  User Manual  COR-COTE HCR - Sherwin Williams  

Copyright © All rights reserved. Failed to retrieve file