For your company to have an effective reporting system, you need a few extra functions:
You want to make one workbook from several queries
You want to remove queries you no longer need
You also want to use the query results for other calculations, which means you have to detach the query from the BW Server
Deactivating Automatic Refresh
You can specify how the query is refreshed on the toolbar:
Choose Settings on the toolbar to activate or deactivate the "automatic refresh" function. This function is activated by default each time you add a query.If you have deactivated the "automatic refresh" function or interrupted the connection to the Business Information Warehouse Server (BW Server), you can refresh the query manually. You also have to do this, for example, if you have copied the query or moved the results area. Choose the Refresh icon on the toolbar. If you have selected a query on the worksheet, only this query is refreshed. If you have not selected a query, you can refresh all of the queries in all of the workbooks that are currently open.When you refresh a query, the format and column width of the cells are automatically adjusted to match the updated query data. You can specify how this is done in the query properties.
Choosing Query Properties brings you to a dialog box, which offers you the following change options:
ŸYou can change the name of the query as well as make settings for the results position, display options, number display, and the key date. If you have specified input variables in the query definition, you can determine the sequence in which they appear in the dialog box, which you see when you start the report. To do this, put the input variables in the order you want by using the arrow icons next to the input field.If you want to evaluate the data of the Business Information Warehouse with third-party products, you must release the query for "OLE DB for OLAP". All of the characteristics and key figures are then copied to the query. Release the query by selecting the check box next to the relevant field. Query Properties tells you who created the query, when it was changed and by whom.
Properties of Queries: Display
For more options in modifying the query properties, click the right mouse-button on the results area of the worksheet and choose Properties. In the selection menu that appears, you can choose between different tabstrips.
The tabstrip Display includes functions for changing how data is presented in the worksheet.As a rule, the position and size of the cell area in the results area of the query changes when the query is refreshed. The "Adjust formatting after refreshing" function ensures that the formatting is adapted to the updated query data when the query is refreshed. This function is activated by default.You can stop the formatting from being adjusted, if, for example, you use a worksheet with your own formatting templates.
By activating the function "Suppress repeated key values", you make sure that identical keys do not appear when the query is displayed. This setting is deactivated by default so that all of the keys are displayed for each characteristic.You can use the "Display scaling factor for key figures" setting to display the scaling factors for key figures (e.g. "*1000") in the row or column header.You can also choose whther you want to display filter cells for structures in the workbook - these give you a further navigation option in the query result. This function is activated by default.
Properties of Queries: Interaction
By default, interactive functions such as navigating, refreshing, and so on, are supported in a workbook. If you do not want the user to be able to navigate or change the view of the data, deactivate the function "Allow interactive functions".
If variables that are ready for input are used in a query, you can enter the values for the variables when the query is inserted in the workbook or confirm the default values. If you want to save and reuse the variable values, you can make the setting here. This has the advantage that you do not have to enter the values each time you refresh the data. This function is deactivated by default. If you want your query to be updated when you open the workbook, activate the relevant function here.
Column Width
As a rule, the position and size of the cell area in the results area of the query changes when the query s refreshed. The tabstrip Column Width offers you the functions you need to set the column format in the Excel worksheet. This means you can take into account how you want the changed data to be displayed.You can choose between the settings "Do not adjust column width", "Adjust to results area" or "Adjust to whole columns". The "Adjust to whole columns" option is activated by default.
Information
The details given on the Information tabstrip help you to clearly identify the query and its workbook.The local query ID is the identification number of the query within the workbook. You need this number, if, for example, you want to program a VBA routine for a user exit with this query. In this case, you would need to be able to clearly identify the query in the workbook.With the query ID for the server you can clearly identify the query on the server. For the workbook, refer to the workbook ID.
The details of the workbook's source system specifies on which system the workbook queries were created. You can refer to these details if you are having problems with executing the query, since a reason for it could be that the current system is not compatible with the query.
Moving, Copying, and Deleting Queries
You can use the "Delete query" function to delete one highlighted query from the workbook. The query definition itself is not deleted. This means you may insert this query in this or another workbook.
You can change the position of the query within the workbook. You can either copy the entire query or move the results area into the workbook. The query properties remain intact. After moving or copying, you must refresh the query in order to display the current data.You can arrange queries and their cell areas on a worksheet as required by moving, copying, or deleting them again. Even if you copy the entire query to a new worksheet, the position of the cells within the query remains the same. You can also copy or move the filter cells in a query. If you want to return the filter cells to their original position, simply delete all the filter cells and refresh the query. All cells return to their original position.
Inserting Queries
Queries are always inserted at the current cursor position in the workbook. You can also insert queries into a worksheet from various InfoCubes.If you have inserted more than one query in a workbook, there may be a clash between the result area and another query when you change the drilldown in the query. This applies to both the area next to the existing query and the area below it. The system detects this clash automatically and provides three solutions:
ŸYou can overwrite the result area of the other query.
ŸYou can insert cells below or beside the query.
ŸYou can insert entire rows or columns.
Detaching and Deleting Results
You can use the "All queries in workbook", "Delete results" function to delete all the results areas of all the queries contained in the workbook. You can reset the results areas by selecting the "Refresh" on the toolbar.This function allows you to store the workbooks centrally, without everyone who has access to the server being able to display the contents of the reports.The "All queries in workbook", "Detach" function deletes the connection between the cell areas of the queries and the data. If you detach the queries, the visible data area on the worksheet remains intact but it can no longer be navigated on or refreshed.IMPORTANT: You cannot undo the "Detach query" function.This function enables you to record the current data permanently. No new data can be added to the workbook once the queries have been detached.
Related Posts
No comments :
Post a Comment