BEx folder of your SAP installation). More advanced functionality related to query design will be covered in Chapter 5, Running Queries in Excel: Business. running SAP BusinessObjects BI solutions with SAP NetWeaver BW and SAP ERP Full BI suite for Enterprise BI. Discovery and analysis. Dashboards and apps. Reporting . soundofheaven.info pdf. You can perform a detailed analysis of BI information both on the Web and in Web applications and formatted reports can be easily converted into PDF files.
|Language:||English, Spanish, Dutch|
|Genre:||Health & Fitness|
|ePub File Size:||19.69 MB|
|PDF File Size:||8.44 MB|
|Distribution:||Free* [*Regsitration Required]|
Warehouse (SAP BW) that provides flexible reporting and analysis tools that you can In this course, you learn about the basic functions of SAP BW reporting. Jens Rohlf, Jörg Rose, Oliver Schrüffer. SAP BW. Reporting and Analysis .. Data Analysis with SAP Business Information Warehouse—. The Query Concept. different reporting procedures and an analysis tool to run queries and reports with SAP. NetWeaver BI enhancements of SAP BW and various options for querying, reporting, broadcasting and integration .. different formats such as – PDF.
Allows you to cause the results to follow the calculation behavior of the single values. Figure 4: U Query Restr. Execute the query in the BEx Analyzer. The Change Selection dialog box appears. Create a query that contains the characteristic Cal. Deine Structures and work with Cells 45 Minutes Unit 5:
Simply drag the characteristic value outside of the analysis area and it will exclude any data for that characteristic value. There are many more navigation options you can use in the analysis, but most are very intuitive and can be mastered quickly by even casual users. System Data System: User ID: Set up instructions: Navigating in Reports 2. Show the same navigation options using the context menu.
Show, only in the BEx Analyzer, the double-click function. Show, in the BEx Analyzer only, the possibility for drill-down and for ilter using the Query Properties from the context menu. In addition show the Display Options tab page. Show how to integrate a hierarchy for characteristic for example, Material into the report.
Saving Query Views You save a current navigational state as a query view to open a report exactly using this navigational state as the initial one. Navigating in Reports Password: Navigating in Reports 35 Exercise 1: Navigate in Reports Exercise Duration: Business Example You want to analyze business key igures for example, sales volumes or incoming orders within existing BW queries by using the different navigation options.
Assigned system Client: Assigned user ID Password: Assigned password Set up instructions: No settings are necessary. Task 1: Use the Find function to search by either the description or technical name.
Insert the characteristic Distribution Channel in the drill-down. Try out both inserting it in the rows and in the columns. Use different navigation techniques. Remove the characteristic Distribution Channel from the drill-down, again using different navigation techniques.
Navigating in Reports BW 5. Expand and collapse hierarchy nodes, and inally, go back to the initial state of the report. What happens? How can you insert the removed key igure in the report again? How can you insert the removed value in the report again? Compare the result of the previous navigation option with the result of the following one: Go back to the initial view of the query. In order to change the drill-down you also can use the Query Properties function from the context menu.
Place the Distribution Channel characteristic in the rows under the Country characteristic by using this possibility. Additionally, repeated key values should not be suppressed any more. Finally, save a view for your own user-deined navigational state of the report. Navigating in Reports Task 2: Enter the user ID and password provided by your instructor.
Use the Search function to search by either the description or technical name. You have to execute the report again via New Analysis. How can you insert the removed key igure in the report again. How can you insert the removed value in the report again.
Navigating in Reports BW Where can you display the ilter situation of the report? Remove the ilter value for Country and insert Country as the irst characteristic in the drilldown, the second characteristic in the drilldown should still be Material. Navigating in Reports Solution 1: Navigate in Reports Task 1: Select the Find button.
When the query is found, select the Open button at the bottom to execute the query. Remove Drilldown. Navigating in Reports BW 4. When the mouse pointer changes to show two arrows in a circular motion you are ready to release the mouse button. Click OK to see the result displayed using the hierarchy. The sequence of the key igures will be swapped.
When the mouse pointer changes to show a black ilter icon you can then release the mouse button. The Invoiced quantity key igure is now gone. Navigating in Reports 8. Wait until you see the mouse pointer change to show a black ilter before releasing the mouse button. Select Keep Filter Value menu option. Navigating in Reports BW You will ind very easily your view under the Favorites folder or under the History folder.
If not, change it to Query. Press the Search button. Select the desired query and OK. Navigating in Reports BW Try out both inserting it in the rows and in the columns. Hierarchy Node Expanded and Expand Hierarchy. Navigating in Reports How can you insert the removed key igure in the report again. When the mouse pointer changes to show a black cross icon you can then release the mouse button.
Wait until you see the mouse pointer change to show a black cross before releasing the mouse button. Use the Filter link on the right-hand upper side of the screen and look in the dropdown box for Country. Use the Filter link on the right-hand upper side of the screen, and in the dropdown box for Country choose Clear. If not, change it to View. Select your desired view and OK. Navigating in Reports Lesson Summary You should now be able to: Calling the Query Designer Create a simple query Finding a query Formulating Selection Conditions Filtering data in the query deinition Deining and using reusable ilters Coniguring properties of the query Create Simple Queries Creating Simple Queries 50 Lesson Duration: You want to deine simple queries, execute these, and make changes to the report deinition.
By selecting and combining the InfoObjects in a query, you determine the way in which data from the chosen InfoProvider is evaluated. None required 1. Demonstrate the various options for opening the Query Designer. Functions of the BEx Query Designer — different panes: The functions are described within the context of query deinition. Please note, toolbar functions are context sensitive, which means that certain buttons may be inactive if they are not valid for your context.
End and Discard changes: With this function, you close the Query Designer with the option to save. New Query: With this function, you are able to deine a new query. Open Query: Choose this function if you want to open an existing query.
Save Query: Using this function, you can save your query. Save All: You can use this function to save the query and its components, as well as the reusable objects for this InfoProvider that are not used in the query for example, newly created variables in the InfoProvider screen area , in one step.
Choose this function if you want to display your query results via the portal. Check Query: This function will perform a validity check on your query and inform you of any errors.
Query Properties: Choose this function if you want to change the properties of the query for example, the description of the query, the settings for the result position, display options, numeric display, zero display, and key date of the query. You use this function to remove a query object ready for inserting into another section of the query deinition. You use this function to identify a query object in preparation for an insert of the object into another section of the query deinition. This function is used to insert either the copied or cut object in query deinition.
Be sure to mark the place where you want the query object to inserted. Creating Simple Queries InfoProvider: This function will open the InfoProvider pane. This function will open the Filter pane. This function is only available for queries with two structures. You can deine formulas and selection conditions for cells explicitly. In this way, you control the values of cells that appear at the intersections of structural components.
This function enables you to access individual cells in queries or to assign special values to these.
You use this function to deine conditions for a query. For each characteristic, you can give limit conditions to the key igure values to determine, for example, all sales revenues above or below a speciied threshold value. You use this function to deine exceptions for a query. Exceptions are deviations from normal key igure values, as deined by you, and are highlighted in color in the query view. This function opens the Properties pane.
Choose this function if you want to deine properties of the query and of the query elements. This function opens the Tasks pane. Here you can display the actions, which are valid for the query object you have highlighted.
This function opens the Messages pane. In the messages pane there is a list of the outstanding messages relating to the status of the query.
Use the Check Query function to display a list of messages in the messages pane relating to the correctness of the query. Using this function, you can ind out in which objects Workbooks, Web templates the query is used. Using this function you can open the Documents pane where you can edit and view documents for the query deinition with help of the Document Browser. Technical Names: Creating a Query This section describes the most important functions required for creating, saving, and modifying a query.
In the New Query: Select InfoProvider dialog box, select the required InfoProvider. Creating Simple Queries Figure New Query Dialog Choose one of the three button on the left side of the dialog box: Once you have selected the InfoProvider, the Query Designer displays as shown in the following graphic. Query Designer Layout Filter View The Query Designer contains many different panes, some panes are only displayed when a function button is pressed.
However, we will now describe the key panes: Directory tree of the selected InfoProvider. Once you have selected the required InfoProvider, all available objects dimensions, key igures, structures, ilters display in the directory tree in the left screen area of the Query Designer. Characteristic Restrictions Here you deine the characteristic ilter values, which apply to the entire result set.
Default Values In this pane you deine the characteristic ilter values, which should be used for the initial view of the result set. The user may choose to modify these ilters in the result. Properties Here is where the settings relevant to the currently highlighted query object are displayed.
You can also make changes to the setting here. Often there will be multiple tabs used to organize the settings in this pane. Creating Simple Queries This pane is where informational or error messages are displayed. Free Characteristics. Put the characteristics, which you want to offer to the user for navigation purposes, in this pane.
These characteristics do not appear in the initial view of the query result set, the user must use a navigation control to make use of them. You do not deine the ilter values here. Columns Here is where the query objects key igures or characteristics must be placed if you want them to appear in the columns of the results set.
Rows Here is where the query objects key igures or characteristics must be placed if you want them to appear in the rows of the results set. Preview This pane gives you an idea of what the layout of the results set will look like when you execute the query. Tasks A list of suitable tasks relating to highlighted query object are displayed here, you can click any of the tasks in the list to go directly to the settings.
Creating Simple Queries BW Where Used This pane provides information relating to the use of the query object. You will only see the tab for Where Used once you insert your irst query object in the deinition. We are simply showing you in the graphic where this pane will appear. To create a query, you can expand or collapse the InfoProvider directories by choosing the plus or minus symbol. By expanding the key igure node in the InfoProvider tree, for example, you can display a list of all the key igures for the InfoProvider.
You can also transfer these characteristics using the keyboard and the clipboard.
You can also include characteristic attributes and variables in the query deinition in this way. To do this, irst select the required query component, then select a menu option from the context menu.
For example, in the ilter pane, if you double-click a characteristic, the dialog box for restricting characteristics appears. Simply click one of the tasks to open up the dialog box to make the settings. The following graphic shows the Query Designer with a query deinition.
The Material was also included in the rows. This was restricted to a range of materials in the default ilter pane. Once you have deined a query, choose Save Query or Save As If you have not yet saved the query, you must enter a query description and a unique technical name. You can also choose where you want to save the query in your roles or in your favorites folder. You can use Save Query As Simply overwrite these if you prefer to use your own names. You are able to save a query even though it may contains errors.
This means you can return to the query at a later time and correct the errors. Creating Simple Queries Once you have saved the query, you have the following options: To do this, choose Exit and Use Query. However you must irst have launched the Query Designer from the BEx Analyzer to see the required function.
To do this, choose Execute. The individual steps for creating a query and the various options for deining a query are described in more detail in the following section. Create a simple query Purpose Create a simple query to demonstrate the basic functions. Filtering is described in the next section in detail so do not discuss or demo this yet! Briely point out the functions of the toolbar. Choose New Query. Expand the dimensions to show the characteristics, drag Sold to party and Material to the rows.
Drag characteristic Country into the Free characteristics 9. Explain the result preview pane. Save the query. Choose Technical Name and comment on the display. Deactivate the technical names. Then show the Task pane and the Message pane areas and explain these. Execute the query in the BEx Analyzer.
Show how to return to the query deinition again. Finding a Query To ind a query for which you already know a part of the description or other information, choose Find. In the Search for Queries dialog box, enter the technical name or description of the query. If necessary, you can restrict the search to additional criteria. Choose Find. Creating Simple Queries Demonstration: Finding a query Purpose Show the participants the search functions and the various methods by which to search.
Show the various ways of inding the query. Find in the different areas Roles, InfoArea, History the following query: Filtering Data in the Query Deinition It is often necessary to restrict the characteristics to speciic values in your reports. For example, you are interested only in the results of a speciic sales team. But the InfoProvider on which the query is assigned contains sales results for all sales teams, you can restrict the query by deining a ilter on the required characteristic.
The result will be that your report will only show the results for the required sales team. The ilters are deined under the tab Filter.
Be sure to select this tab before attempting to deine any ilters. Once you are displaying the Filters tab you can deine the ilters in either the Characteristic Restrictions pane or the Default Values pane. These are often regarded as static ilters. These are often regarded as dynamic ilters. However, the user is able to navigate and ilter freely, but only within the range of the static ilters. Creating Simple Queries BW There are two basic ways to restrict a characteristic in the query deinition: By selecting a characteristic value from the InfoProvider tree on the left side of the query designer.
Simply open the tree to display the required characteristic within the appropriate dimension and then open the characteristic node to reveal the sub folders, one will be called Characteristic Values.
Open this folder to display the values and then drag and drop these to the required pane. You can also restrict a characteristic you have already included in the query deinition in the Characteristic Restrictions pane or in the Default Values pane by calling the context menu for the characteristic and choosing Restrict. Or simply double-click on the characteristic to launch the default task, which for a characteristic in the ilter view is Restrict.
The following igure shows the selection dialog box for the material characteristic. You make your choice by selecting the Show drop-down box.
Here you can also use the search features and also select the single values from a hierarchy there is also a search feature within the hierarchy. The search option allows you to enter either the characteristic key that is, material number or characteristic text that is, Flatscreen or you can enter any of the attribute values of the characteristic that is, color, speed. You can mix up your choices but you are limited to a maximum of three search criteria.
This choice provides the following additional options: Between 2. Greater than or equal to 3. Less than or equal to 4. Greater than 5. Creating Simple Queries BW For the selected values, which you adopted form the left-hand side of the window to the Chosen Selections on the right-hand side, you will ind the following options: You supply a suitable name for the ilter collection when you open your favorites the collections are displayed with their names.
If your selection criteria returns a large result set then you should maximize the selection window so you can see more of the results before you need to scroll. Creating Simple Queries Formulating Selection Conditions 64 Use You can select individual values and value ranges from a list or from a selected hierarchy in the selection dialog box.
The values available in the left-hand window are restricted to a maximum number, which you deine in the settings dialog. You want only characteristic values that match your speciic criteria to be offered in the selection dialog. Prerequisites You have selected the characteristic for which you want to use characteristic values for restriction and chosen Restrict in the context menu using the right mouse button. Procedure 1. From the Show drop-down choose the option Single Values.
In the irst search criteria Text enter the text or only a part of the text. Enter any further criteria in the remaining search ields. Press the Start Search button to see the results appear. Drag and drop or highlight then use the arrow button to the right side of the dialog box to select the ilters.
The list of available values is now restricted according to your formulated selection condition. Filtering data in the query deinition Purpose Show the various ilter options. Open your query from the preceding demo. Include the time characteristic Cal. Choose a few values.
Creating Simple Queries BW 4. In the selection dialog, save the collection of ilter values with a suitable name then show how they can be recalled under Favorites. Demonstrate the search functions within the selection dialog box also using a hierarchy. Switch on a few attributes in preferences to add clarity. Use the ilter you have set to explain the difference between the functions in the different Query Designer ilter panes the characteristic restriction and default values.
At this point, it is already possible to make task 1 from the exercise. All necessary issues have been explained. Deining and using reusable Filters If you have deined a ilter for the query consisting of several characteristics which are restricted more or less complex or if the ilter represents a very common ilter situation it can be wise to save the ilter as reusable object.
On the Filter tab page, either in the Characteristic Restriction area or in the Default Values area, Right click and Save as a reusable ilter. Save as a Reusable Filter In the InfoProvider area on the left-hand side you now see, under the folder named Filter the Filter objects. These Filter objects can now be used for other queries on the same InfoProvider.
Using Reusable Filters Note: Later changes for a reusable ilter, made in any query where the reusable ilter is used, affect all queries in which the reusable ilter is used. If you only want to make local changes in a particular query, choose Remove Reference in the context menu for the ilter, and as a result, change the reusable ilter into a local ilter.
Changes on this local ilter do not affect the deinition of the reusable ilter, nor the queries that you made with this reusable ilter. Continue the preceding demo. Show the interaction between ilters set in the Characteristics Restrictions and in the Default Values panes. Drag Cal. Creating Simple Queries 3.
Execute the query and show which ilter values can be dynamically chosen. Show how to reuse the reusable ilter. Point out the effect on queries, which reuse the reusable ilter when the deinition of the reusable ilter is changed. Explain and show Remove Reference in the context menu for the ilter.
Query properties are accessed via the right most pane of the query designer. Be sure to select the properties tab at the bottom of the pane and also be sure that you have selected the top-most item in the drop-down list of the properties pane this pane is also used to access the characteristic and key igure properties. The top most item is where you will see the query name. The query properties are organized via tabs as follows: This text will appear as the title of the query in the output.
This can be changed at any time in the query. It is also possible to insert text variables so that the description is dynamic and determined at runtime that is, report date, product division chosen by user.
Once a technical name is entered and the query is saved you cannot change this, if you wanted to use a different technical name you would have to use the Save as option. Think about a consistent naming convention for any BEx technical names. For example the irst two characters might relate to the business area, the second two characters may relate to the author and so on. This will certainly help with authorizations planning. The Use standard date checkbox is used to tell the query to use the current date at query runtime.
In the general tab you will ind also information relating to the owner of the query and the person who last changed the query and date and times. To prevent this from happening perhaps to improve query performance or you want to use your own format templates you need to make sure this box is unchecked. The default settings will present the row results at the bottom and the column results to the right.
When you make changes, you will see the preview diagram at the foot of the pane also changes. You can decide if the entire row or column should be zero or simply the net result is zeros before suppressing.
Use the preview to help you decide. Planning mode allows the result cells to be changed. Coniguring properties of the query Purpose Show the most important query properties. Explain briely the most important query properties. Creating Simple Queries 71 Exercise 2: Create Simple Queries Exercise Duration: You have been given the task of creating these query deinitions. None required Task 1: You are interested in inding out the incoming order values and the incoming order quantities of your customers for speciic materials between January and April You want to enable the users of your report to select by speciic countries.
U Query First Steps Technical name: In the next step, you want to change the query deinition. Find the query you created U Query First Steps using the search function. In the query deinition, include the material in the report in order to display the incoming orders for each material for the customers. You also want to ilter the data with a characteristic restriction to only report data for the months Then save and execute the query.
In the local view of the executed query, remove the material from the drilldown and include the country in the drilldown in front of sold-to party to list the sold-to parties by country. Also in the local view, restrict the country to Germany and Norway. In a further step, in the global deinition of the query, establish the restriction for the country to Germany and Norway as default values.
Execute the report and make a note of which sold-to parties are listed in your report. Are you able to select other countries in the report? Now execute your query on the Web. Task 2: Create a further query for the time period from April to June about the Sales Volume EUR and the Invoiced quantity of the customers and their materials and from the sales organization Germany Frankfurt.
Initially, the lat screens are in the focus, but there has to be the option of selecting further materials. There should also be the option of a drilldown for month and with the possibility to select values for other months, but only within the year In the executed report try out the different options for iltering and arranging the drilldown.
Include Cal. Creating Simple Queries For the initial view, you are only interested in the lat screens Flatscreen MS P M to Flatscreen MS P M and in the months April to June , but in the executed report, you should also have the possibility to select values for other materials and for other months, but only within the year Try out the different options for arranging the drilldown and for iltering.
Finally, verify which months can be selected when iltering. Back in the query deinition, drag the key igure structure into the rows under the characteristic material. Additionally, the repeated key values of the characteristics should not be hidden any more. Verify now the drilldown in the executed report. At last, go back to the Query Designer and drag the key igure structure back into the columns area again. What can be done with such a reusable Filter?
Create Simple Queries Task 1: Leave the BEx Analyzer open for the next exercise step. Creating Simple Queries BW In the query deinition, include the material in the report in order to display the incoming orders for each material for the customers. Press Find to start the search. In the Select Values for Select Choose OK. Creating Simple Queries drilldown and include the country in the drilldown in front of sold-to party to list the sold-to parties by country.
Select More to open the Selection window, if necessary. Choose OK f View the results. The BEx Query Designer opens. In the selection screen, choose Single Values. Select Germany and Norway, and transfer the selection using the Arrow button. Only those sold-to parties for the countries in the Default Value area of the ilter are listed in the initial view, but it is possible to change the ilter and to analyze the other countries as well. For the initial view, you are only interested in the lat screens Flatscreen MS P M to Flatscreen MS P M and in the months April to June , but in the executed report, you should also have the possibility to select values for other materials and for other months, but only within the year Select Germany Frankfurt Adopt the selection using the Arrow button.
In the selection screen, choose Value Ranges and Between. Creating Simple Queries BW Try out the different options for arranging the drilldown and for iltering. For example, in order to list the sales volumes for the individual months in your report, drill across on Cal. Months can be selected within the year only! Creating Simple Queries At last, go back to the Query Designer and drag the key igure structure back into the columns area again.
Alternatively, you reach the properties of the query by using the button in the Query Designer toolbar. You see the two key igures are now arranged in rows one below the other for each sold to party and each material, and the repeated key values are not suppressed any more.
Drag the key igure structure back into the columns area again and save the query. They can now be used for other queries on the same InfoProvider. By coniguring the properties of key igures the report can be optimized additionally. Out of the limited number of key igures provided in the InfoProvider additional information can be achieved. This unit shows you how structures can be deined and how they can be saved and reused in various queries.
The unit also shows an example of formula collision and explains the functions of the cell editor that can be used when implementing two structures in a query deinition. Creating Restricted Key Figures Deining Restricted Key Figures Create Restricted Key Figures Creating Calculated Key Figures Deining Calculated Key Figures Deinition of Boolean Operators Create Calculated Key Figures Coniguring Properties of Key Figures Key Figure Properties Display Tab Calculation Tab Extended Tab Currency Translation Deining Exception Aggregation Deining Nested Exception Aggregation Exception Aggregation and Nested Exception Aggregation Conigure Properties of Key Figures Deining Structures and Working with Cells Creating Structures Creating Reusable Structures Deining characteristic structures and key igure structures, handle formula collision Deining Exception Cells Deining and using cells, save and reuse structures Exercise 6: Deine Structures and work with Cells Creating Restricted Key Figures Lesson: Show and explain the difference between the use of a global ilter and the restriction of a key igure.
Explain to the participants the difference between deining the restricted key igure at the InfoProvider level and at the query level: Business Example You need restricted key igures to be able to deine detailed reports. For example, you want to list sales volumes for individual time segments next to each other in the report. If you create them at the InfoProvider level, you are then able to reuse the individual key igures in other reports. Deining Restricted Key Figures Restricted key igures are basic key igures of the InfoProvider that are restricted iltered by one or more characteristic selections.
The key igure that is restricted by one or more characteristic selections can be a basic key igure, a calculated key igure, or a key igure that is already restricted.
By using restricted key igures, you can focus the query result on certain values. Unlike a ilter, whose restrictions are valid for the entire query, for a restricted key igure, only the key igure in question is restricted to its allocated characteristic value or characteristic value interval.
Restricted key igures that are deined at the InfoProvider level are available in every query deinition of the InfoProvider in question. Then, in the context menu of the new restricted key igure, choose Edit. The Change Restricted Key Figure dialog appears. If there is not yet a key igure structure, one will be created automatically. In the context menu of the key igure to be restricted, choose Edit.
Use the New Selection function: If there is not yet a key igure structure, select the heading of the Rows or Columns area and use the right mouse button to choose New Structure.
Select the structure header and use the right mouse button to choose New Selection from the context menu. The New Selection dialog box appears. If there is already a key igure structure, select the header or any member of the structure and use the right mouse button to choose New Selection from the context menu.
Edit the description of the restricted key igure into the text ield. In the context menu of the characteristic s , choose Restrict. Select the characteristic values you require for the restriction. Conirm your entries by choosing OK. For the deinition at the InfoProvider level, you enter a technical name in the dialog box, and conirm your entry by choosing OK. The newly deined key igure can be found in the Restricted Key Figures folder. In the case of deinition at the query level, the new restricted key igure is already part of the key igure structure.
Purpose System Data System: Demonstrate Exercise Task 1 Determining Constant Selection In the Query Designer, you use selections when deining a restricted key igure to determine the data that you want to display at runtime. You can use navigation steps to further restrict your selections at runtime. The Constant Selection function allows you to mark a deined selection as being constant.
As a result of this setting, navigation and ilters no longer have an effect on the selection in question. This feature is often used when you would like to ensure that a particular restricted key igure remains the same in the result so it can be used for comparisons to other key igures, which may have a different ilter value due to navigation. Creating Restricted Key Figures Figure Constant Selection Demonstrate Exercise Task 2. Creating Restricted Key Figures 93 Exercise 3: In your report you want to generate an overview of the sales volumes in various time periods for each sold-to party.
You also need detailed information on sales volumes, restricted to the division High Tech, for your individual sold-to parties. You need three different restricted key igures to do this. In each case, you use the basic key igure Sales Volume EUR, and then proceed according to the view you want.
Deine two restricted key igures that depict the Sales Volume EUR for the period leading up to and for the year itself, and additionally a third restricted key igure representing the Sales Volume EUR, again for the year , but restricted to the division High Tech.
In the same query, deine the second restricted key igure Sales Volume EUR , this time at query level. Please be aware that you have two different techniques to create a restricted key igure on query level: Deine the third restricted key igure, again at query level.
U Query Restr. Optional In the precedent report, as further analysis requirement, there should also be the possibility to show the sales volumes before and in restricted to a range of speciic divisions, and to compare with the sales volume for the division High Tech in the year Enable the users of the report to select data related to speciic divisions.
Execute the report on the BEx Analyzer or in the web. Restrict the data in the executed report by iltering on the characteristic Division using the ilter values Pumps, Motorcycles, Lighting. Creating Restricted Key Figures 4. How can you achieve that the values for the restricted key igure Sales Volume EUR Hightech are not inluenced by navigation, in our case by iltering on the characteristic Division? Create Restricted Key Figures Task 1: Select Germany Frankfurt and adopt the selection using the Arrow button.
Then choose Edit from the context menu of the new restricted key igure. In the selection screen, choose Value Ranges. Choose Less than. Select and adopt the selection using the Arrow button. Working with Key Figures and Structures in Queries BW Please be aware that you have two different techniques to create a restricted key igure on query level: Right-click Selection 1 and choose Edit.
The Change Selection dialog box appears. In the selection screen, choose Single Value and select Sales Volume EUR Copy it using Copy and Paste from the context menu of the key igure. Note that several lines are available for key igure names. This is to control the column width in the report. In the Show ield, choose Single Values. Select High Tech 07 and adopt the selection using the Arrow button. U 0 Query Restr. Adapt the description: Division c At last, choose OK, save the query, and execute it again.
You will ind the values very easily in the History. Check your solution. Creating Calculated Key Figures Lesson: This lesson deals with how to deine calculated key igures at the InfoProvider and query level.
Explain to the participants the difference between deining a calculated key igure at the InfoProvider level and at the query level. Business Example The use of complicated calculations for information analysis is the order of the day.
This requires various mathematical functions such as percentage functions and totals functions. You can take basic key igures, restricted key igures, or calculated key igures to deine new calculated key igures. For example, you use the restricted key igures for two speciic years to calculate the difference between the sales volumes of the years in question. At the query level, the calculated key igure is valid only for the query in question. If you create a calculated key igure at the InfoProvider level, you can use it in all queries that are based on the same InfoProvider.
Calculated Key Figures Deining Calculated Key Figures at the InfoProvider Level To deine calculated key igures at the InfoProvider level, you can include all types of key igures, which are deined at the InfoProvider level, these include basic key igures, restricted key igures and, of course, calculated key igures.
However you must remember that you do not have access to all operators as some operators only make sense at the query level. Any calculated key igure deined at the InfoProvider level can be used in others queries. Query developers may want to try to change the deinition or just the name of your calculated key igure so it its their purpose. They will receive a warning message if they try to do this, but assuming they do have the correct authorizations they can make the changes. These changes will affect all queries where the calculated key igure is used.
Double-click this dummy key igure to open the Change Calculated Key Figure dialog box. Enter a description of the calculated key igure into the Description ield. Deine the required formula using basic key igures, restricted key igures deined on InfoProvider level, calculated key igures already deined on InfoProvider level, formula operators, and functions. Key igures always have the same units when a query is executed. This means that the formula is semantically incorrect if you add a currency unit for example, EUR to a unit of weight for example, kg.
If you want to calculate values without using units, you should use the Value Without Dimension function. See Data Functions. In the Properties pane, you can overwrite the technical name which was automatically generated. Deining Calculated Key Figures at the Query level To deine calculated key igures at the query level, you have to include key igures needed for the calculation in the query deinition, and choose New Formula from the context menu of the header or any member of the Key Figure structure.
These formulas are available only locally in the query deinition. Working with Key Figures and Structures in Queries BW Deining Missing Objects Directly In a calculated key igure, at the query level only, it is possible to deine missing objects directly in the formula editor dialog box.
This means you do not have to leave the formula dialog box to create the missing key igure. Right click in the place where you want the new restricted key igure to appear in the formula.
Use the New Selection menu option if you want to insert an existing basic key igure, or an existing restricted key igure on InfoProvider level, or an existing calculated key igure on InfoProvider level into your formula.
Use the New Selection menu option if you want to insert a so far not existing restricted key igure at the query level into your formula. This will create a dummy key igure Selection in the correct place. Right-click the dummy key igure and use the menu option Edit to select or to deine the desired key igure. Use the New Formula menu option if you want to insert a so far not existing formula at the query level into your formula.
This will create a dummy key igure Formula in the correct place. Right-click the dummy key igure and use the menu option Edit to deine the desired formula. The key igures inserted directly into to the new formula will appear in the key igure structure and therefore will be part of the result as well as the new formula.
Creating Calculated Key Figures Figure Deining Missing Objects Directly Functions for Deining Calculated Key Figures Below are examples and explanations of the functions available as operators when you deine a calculated key igure.
The result means the result of aggregation at the next level interim result. The overall result means the result of aggregation at the highest level in the list. In the calculation of the overall result, the dynamic ilters are included. Unlike the process for calculating the overall result, dynamic ilters are not included in the calculation of the query result.
This means that the calculation always normalizes to the same value, irrespective of the dynamic ilter status and the navigational state. Otherwise, delivers the value 0. Is equal to 0 with division by 0, otherwise x. Otherwise, the result is the value of the expression. Mathematical Functions These include: Demonstrate the difference between the available operators and key igures depending on whether you are deining at the InfoProvider level or query level: None 1.
Deine a new calculated key igure at the InfoProvider level as the difference between two restricted key igures. Demonstrate that all basic key igures and restricted key igures are available. Show which percentage functions are available. You can also define formatted reports optimized for presentation and printing. Web applications and formatted reports can be easily converted into PDF files and printed.
Users access BI information from the portal. They can use information broadcasting functions to distribute this information by e-mail, for example, or distribute it to KM folders and collaboration rooms. In addition, the integration of BI into the Visual Composer allows you to include BI data in composite applications, which users can access in the portal.
Business experts create iViews or portal pages based on data from SAP or third-party systems. The data can be stored relationally or multidimensionally. A set of query templates that cover specific business aspects and the user-friendly operation in the Visual Composer support business experts in creating BI applications. For more information, see Enterprise Reporting, Query, and Analysis. This section on the administration of the Enterprise Reporting, Query, and Analysis scenario lists the most important administration tasks for the optimal operation of the Business Explorer tools.
You also have to execute administration tasks for the following scenarios: Administration of other IT scenarios. Enterprise Data Warehousing. This scenario covers the staging and modeling of the data that forms the basis of analysis and reporting in the Enterprise Reporting, Query, and Analysis scenario. In addition, the Enterprise Data Warehousing scenario offers performance optimization, user management, and monitoring options.
Running an Enterprise Portal. With the Enterprise Reporting, Query, and Analysis scenario, you can publish content from BI to the portal and allow users to work with structured data from BI together with unstructured data and the various functions in the portal.
Enterprise Knowledge Management. In the portal, this scenario provides central and role-based access to the information in your company and establishes a link between structured business data and unstructured documents.
Enabling User Collaboration. With user collaboration in the portal, users can use services for collaboration to edit documents including BI documents in an application sharing session, for example.
For more information, see the SAP Library at help. The following figure outlines the main components required for the Enterprise Reporting, Query, and Analysis scenario.
The BEx Query Designer is available for creating queries. Using information broadcasting, you can broadcast the generated content from BI by e-mail, or publish it to the portal. For more information about the relevant components, see the table below. The following table provides an overview of where you can find further information: Query Design: