\nTable Join Definition<\/a>
\nInitial Screen<\/a>
\nData Fields – VBRK<\/a>
\nData Fields VBRP<\/a>
\nRun the Report<\/a>
\nReport Result<\/a>
\nRemove unnecessary fields<\/a>
\nALV Functions<\/a>
\nReport Layout<\/a>
\nSort ascending \/ descending<\/a>
\nFilter Columns<\/a>
\nSave Layout<\/a>
\nExport to Excel<\/a>
\nOutput format<\/a>
\n SD billing report, customer level <\/a>
\nChange Join Condition<\/a>
\nHeader vs. Item level Data<\/a>
\nAmount and signs<\/a>
\nLogical Databases<\/a>
\nQuickViewer SAP Query<\/a><\/p>\n
Start Quick View from the System Menu under Services or with transaction SQVI.<\/p>\n SQVI_Menu<\/em><\/p>\n The Initial Screen opens. On the right side is the work area and on the left Welcome information.<\/p>\n SQVI_Create_Quick_view_report<\/em><\/p>\n SQVI_welcome.<\/em><\/p>\n Using the QuickView requires some knowledge of SAP tables and fields in them. The header level data of a billing document is saved in a table called VBRK. The item level data is in table VBRP. You can look closer at the content of the tables with SE16\/SE16N Data Browser.<\/p>\n Below you can see a simple SD billing document with a header and one item.<\/p>\n SQVI_BillingDocument_VF03<\/em><\/p>\n You find the table and field names with F1-help. Put the cursor on the field (e.g. Invoice Number), press F1 and click the \u201cTechnical Information\u201d icon in the Performance Assistant Screen.<\/p>\n Icons_technical_information<\/em><\/p>\n SQVI_BillingDocument_VF03_F1<\/em><\/p>\n You can see that the name of the screen field is \u201cVBRK-VBELN\u201d. The first part is the table name and second the field name.<\/p>\n SQVI_BillingDocument_VF03_F1_field_name<\/em><\/p>\n Let\u2019s get started. Name your new QuickView report and press Create. I often name the query after the tables used. I will call this report VBRK_VBRP.<\/p>\n Enter a title and select a data source. You can choose between table, table join, Logical Data base and SAP Query Info Set.<\/p>\n SQVI_Choose_data_source_choices<\/em><\/p>\n If you get all the data needed for the report from a single table<\/em>, you choose table. In the billing report, if you only need customer level data, you get it from the header level table (VBRK). Later in this presentation I have an example for this.<\/p>\n Table join<\/em> is needed when you have two or more tables involved. In case of billing report, when also product level data is needed, the item table VBRP must be included.<\/p>\n Some header + item tables cannot be used in joins, for these SAP has created packaged joins, called Logical Data base<\/em>. For example accounting document data (BKPF and BSEG) are such tables.<\/p>\n Info sets<\/em> defined for Sap queries and can be used in QuickViewer.<\/p>\n Here we choose Table join.<\/p>\n Accept the data source definition.<\/p>\n At the end of the Initial Screen you can see something called Mode. You can select the mode you want to work with (basis mode \/ layout mode). This can be changed later. The Basis mode is defaulted.<\/p>\n SQVI_basis_mode_layout_mode<\/em><\/p>\n Next you are required to define the tables to be joined. Click on the Insert Table icon. Add the first table VBRK.<\/p>\n SQVI_table_join_selection_table1<\/em><\/p>\n The table content comes visible. Click again on the Insert table icon and add the item table VBRP.<\/p>\n SQVI_table_join_VBRK<\/em><\/p>\n SQVI_table_join_VBRK_VBRP<\/em><\/p>\n The join condition VBELN Billing Document number is automatically proposed. The work area opens in Basis mode. You can switch to Layout mode, if you prefer the graphical layout. In this presentation the Basis mode is used.<\/p>\n SQVI_Initial_Screen_Basis_mode<\/em><\/p>\n SQVI_Initial_Screen_Layout_mode<\/em><\/p>\n On the left you can see the fields of the joined tables VBRK and VBRP. The fields used in our report will be chosen from these tables.<\/p>\n <\/p>\n SQVI_Initial_Screen_Basis_mode_2<\/em><\/p>\n You can select fields from Available fields on the right and move them to Fields in the list. When you do that the Data Field on the left becomes green and List Field is ticked.<\/p>\n I prefer to select the fields directly from the Data Fields on the left and tick both the List Field and\u00a0 the Selection filed at the same time. It is also possible to look for the fields using the binoculars<\/span><\/p>\n Mark the fields you want to appear in the report (List Fields) and fields that you want to use as selection parameters when starting the report (Selection Fields).<\/p>\n SQVI_table_join_VBRK_VBRP_header_fields<\/em><\/p>\n The List fields show on the right in Fields of the List. You can change the order with the black arrows.<\/p>\n SQVI_table_join_VBRK_VBRP_header_data<\/em><\/p>\n From the billing document headers following field are selected to our report:<\/p>\n When you have selected the Header fields, continue with the Item fields. Mark the list and selection fields on item level. Notice that the join condition \u2018Billing Document\u2019 is also in the item table. It is already selected in the header level.\u00a0 The rule is that always select the fields on the highest levels.\u00a0 Select the Billing Item at this level.<\/p>\n SQVI_table_join_VBRK_VBRP_data_fields_items<\/em><\/p>\n Order the list fields on the right. You can change the order later, if you want to.<\/p>\n SQVI_table_join_VBRK_VBRP_data_fields<\/em><\/p>\n The fields in my selection are:<\/p>\n Company Code, Sales Organization, Sold-to party, Payer, Billing Document, Billing item, Billing Type, Billing date, Profit Center, Plant, Material Number, Short text for sales order item, Actual Invoiced Quantity, Unit of Sales, Cost in document currency, Net Value in document currency, Sub total 1, currency.<\/p>\n Save the report.<\/p>\n Now the query is ready to run.\u00a0 On the initial selection screen you can see the fields that were marked as Selection Fields. Make your selections. You can also start the query without selection. Then number of data sets to be read is defaulted to 100.<\/p>\n SQVI_execute_selection_screen_filled<\/em><\/p>\n Click the Clock icon to start the report.<\/p>\n Look at the outcome. The fields are in the same order as in the Fields in List. Maybe some reordering is needed.<\/p>\n Notice that the Sales Unit (PC) appears twice in the list. When you chose the Sales Quantity, it came with the unit and Sales Unit is not needed. The same goes with the Cost and value fields (Net Value, Subtotal 1). The currency column is automatically added.<\/p>\n SQVI_result_1 <\/em><\/p>\n In SD there are several fields for values. How do you know which one to choose? There are several files called Subtotal that contain SD condition values. What\u2019s in these fields depends on the pricing procedure. In this example the Net value and Subtotal 1 seem to have same value. We can drop the other one from the query. The Cost fields shows the inventory value of the product from the material master.<\/p>\n SQVI_table_join_VBRK_VBRP_data_fields_items_subtotals<\/em><\/p>\n Let\u2019s go back to the QuickView definition and remove Sales Unit and Subtotal1 from the query.<\/p>\n Remove fields Sales Unit, Subtotal 1 and currency from the Fields in List. Choose the item either in the Fields in the List and move it to the right, or delete the check mark in table VBRP on the left.<\/p>\n SQVI_remove_sales_unit<\/em><\/p>\n SQVI_remove_sales_unit_1<\/em><\/p>\n Save after changes and start the report again. Below the Subtotal column and the Sales unit are removed. The columns are rearranged. In an ALV report you can move the columns by dragging them with the mouse.<\/p>\n SQVI_result_1B<\/em><\/p>\n SQVI_ribbon_numbered<\/em><\/p>\n QuickViewer reports use SAP Abap List Viewer (ALV) functionality. This offers a several tools to format the layout.<\/p>\n ALV layout functions that let you to sort the fields, drag the fields with mouse, add totals and sub totals, save the layout and define it as default layout to be used always when the report is started.<\/p>\n Below the report is sorted and sub totaled by the Sold-to-Party column.<\/p>\n SQVI_result_3<\/em><\/p>\n You can choose, change (rearrange columns, sort, filter, add totals and subtotals), save and manage the layouts with the Layout button.<\/p>\n SQVI_result_2<\/em><\/p>\n icons_choose_layout_2<\/em><\/p>\n The Change Layout out screen opens. Here you can remove fields by dragging them to Column Set on the right. You can drag the fields other location. You can save the Layout for later use or to be used as default Layout.<\/p>\n change_layout_screen<\/em><\/p>\n You can sort columns in ascending or descending order. The outcome depends wether you have You can select wether you ‘with or without cell merging’ selected. Below column Sold-to-party sorted and sub totaled with cell merging.<\/p>\n SQVI_result_cells_merged<\/em><\/p>\n Below the same report without cell merging.<\/p>\n SQVI_result_without_cell_merging<\/em><\/p>\n When the columns are sorted, the cells are merged. If you don\u2019t want that, choose the Change Layout \/ Display. There you can select option without cell merging during sorts.<\/p>\n SQVI_change_layout_Display_Cell_Merging<\/em><\/p>\n You can filter the values of a selected columns. For example you could show only items, where the Net value is bigger than 50.000.<\/p>\n You can use either the Set Filter icon in the bar, or the right click the column and select Set Filter from there.<\/p>\n icons_filter_2\u00a0<\/em><\/p>\n SQVI_save_layout_right_click_menu<\/em><\/p>\n Select the column and Filter. On the ‘Determine values’ pop up right click the Net value and select Options from the menu. Choose \u2018Greater than or equal to\u2019 and enter the threshold value 50.000.<\/p>\n SQVI_filter_options<\/em><\/p>\n Maintain selection options. Select Greater than Equal to and enter the threshold value 50000.<\/p>\n SQVI_filter_net_value<\/em><\/p>\n SQVI_filter_options_2<\/em><\/p>\n Below you can see the result. The small markings in the column headers tell you which columns have been sorted, totaled or filtered.<\/p>\n SQVI_result_filter_50000<\/em><\/p>\n Only values greater than 50.000 are displayed.<\/p>\n The filter is removed with Delete Filter.<\/p>\n icons_filter_2<\/em><\/p>\n If you want to cancel a sort, you can do it in the Change Layout Sort Order tab. Move the column name to Column Set.<\/p>\n <\/p>\n When you are happy with the styling of your report, you can save the layout.<\/p>\n SQVI_save_layout<\/em><\/p>\n Start the layout name with \/. If you want a user specific layout that only you can see, start the name with a letter (A-Z). You can also define the layout as Default. A default layout is always chosen, when you start the report.<\/p>\n SQVI_result_change_layout<\/em><\/p><\/div>\n Maybe you want to transfer your report Excel. There a several ways to do it.<\/p>\n Click the Export Button. Select Spreadsheet.<\/p>\n icons_export<\/em><\/p>\n icons_export2<\/em><\/p><\/div>\n Select the Spreadsheet Format. I use ’08 Excel in Existing XXL Format’.<\/p>\n SQVI_export_excel_2<\/em><\/p>\n The outcome in Excel looks like this.<\/p>\n SQVI_export_excel_1<\/em><\/p>\n Another way is to choose Excel from the Views icon.<\/p>\n icons_views_2<\/em><\/p>\n The outcome in Excel looks like this.<\/p>\n SQVI_excel_inplace<\/em><\/p>\n It is also possible to define the Excel Output in the QuickView definitions. SAP List Viewer is defaulted. If you choose another option, you will see an output selection list, when you start the report.<\/p>\n SQVI_export_as<\/em><\/p>\n If you have chosen Excel as output format, you will see all possible output formats at the end of the selection screen when you start the report. Select spreadsheet to use Excel as output form.<\/p>\n SQVI_export_as_excel<\/em><\/p>\n Below our report in Output format Spreadsheet.<\/p>\n![\"SQVI_toolbox\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_toolbox.png\")
<\/a><\/p>\n![\"SQVI_Menu\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_Menu_gif.gif\")
<\/a><\/p>\n![\"SAP](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2017\/04\/SQVI_Create_Quick_view_report.png\")
<\/a><\/p>\n![\"SQVI_welcome\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_welcome.png\")
<\/a><\/p>\n
\nThe documents usually have a header section and items section, which both are separate tables. In this exercise we are building a customer billing report. A billing document (SD Invoice) consists of header and items. The transaction for Display Invoice is VF03.<\/p>\n![\"SQVI_BillingDocument_VF03\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_BillingDocument_VF03.png\")
<\/a><\/p>\n![\"icons_technical_information\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_technical_information.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_BillingDocument_VF03_F1\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_BillingDocument_VF03_F1.png\")
<\/a><\/p>\n![\"SQVI_BillingDocument_VF03_F1_field_name\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_BillingDocument_VF03_F1_field_name-1.png\")
<\/a><\/p>\n<\/a>SD Billing Report<\/h1>\n
<\/a><\/p>\n![\"SQVI_Choose_data_source_choices\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_Choose_data_source_choices.png\")
<\/a><\/p>\n![\"SQVI_basis_mode_layout_mode\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_basis_mode_layout_mode.png\")
<\/a><\/p>\n<\/a>Table join definition<\/h1>\n
![\"SQVI_table_join_selection_table1\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_selection_table1.png\")
<\/a><\/p>\n![\"SQVI_table_join_VBRK\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK.png\")
<\/a><\/p>\n![\"SQVI_table_join_VBRK_VBRP\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP.png\")
<\/a><\/p>\n
\nBack with the green arrow.
\nNow you can start building your report.<\/p>\n<\/a>Initial Screen<\/h1>\n
![\"SQVI_Initial_Screen_Layout_mode\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_Initial_Screen_Layout_mode.png\")
<\/a><\/p>\n![\"SQVI_Initial_Screen_Basis_mode_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_Initial_Screen_Basis_mode_2-1024x367.png\")
<\/a><\/p>\n<\/a>Data Fields \u2013 VBRK<\/h1>\n
![\"SQVI_table_join_VBRK_VBRP_header_data\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP_header_data.png\")
<\/a><\/p>\n![\"SQVI_table_join_VBRK_VBRP_data_header_fields\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP_data_header_fields.png\")
<\/a><\/p>\n\n
<\/a>List Fields \u2013 VBRP<\/h1>\n
![\"SQVI_table_join_VBRK_VBRP_data_fields_items\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP_data_fields_items.png\")
<\/a><\/p>\n![\"SQVI_table_join_VBRK_VBRP_data_fields\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP_data_fields.png\")
<\/a><\/p>\n<\/a>Run the report<\/h1>\n
![\"SQVI_execute_selection_screen_filled\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_execute_selection_screen_filled.png\")
<\/a><\/p>\n<\/a>Report result<\/h1>\n
![\"SQVI_result_1\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_1-1024x147.png\")
<\/a><\/p>\n![\"SQVI_table_join_VBRK_VBRP_data_fields_items_subtotals\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_table_join_VBRK_VBRP_data_fields_items_subtotals.png\")
<\/a><\/p>\n<\/a>Remove unnecessary Fields<\/h1>\n
![\"SQVI_remove_sales_unit\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_remove_sales_unit.png\")
<\/a><\/p>\n![\"SQVI_remove_sales_unit_1\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_remove_sales_unit_1.png\")
<\/a><\/p>\n![\"SQVI_result_1B\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_1B-1024x125.png\")
<\/a><\/p>\n<\/a>ALV-functions<\/h1>\n
![\"SQVI_ribbon_numbered\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_ribbon_numbered.png\")
<\/a><\/p>\n\n
![\"SQVI_Result_3\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_Result_3-1024x137.png\")
<\/a><\/p>\n<\/a>Layout<\/h1>\n
![\"SQVI_result_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_2.png\")
<\/a><\/p>\n![\"icons_choose_layout_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_choose_layout_2.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_result_change_layout_screen\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_change_layout_screen.png\")
<\/a><\/p>\n<\/a>Sort ascending \/ descending<\/h1>\n
![\"SQVI_result_cells_merged\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_cells_merged.png\")
<\/a><\/p>\n![\"SQVI_result_without_cell_merging\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_without_cell_merging.png\")
<\/a><\/p>\n![\"SQVI_change_layout_Display_Cell_Merging\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_change_layout_Display_Cell_Merging.png\")
<\/a><\/p>\n<\/a>Filter Columns<\/h1>\n
![\"icons_filter\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_filter.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_save_layout_right_click_menu\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_save_layout_right_click_menu.png\")
<\/a><\/p>\n![\"SQVI_filter_options\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_filter_options.png\")
<\/a><\/p>\n![\"SQVI_filter_net_value\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_filter_net_value.png\")
<\/a><\/p>\n![\"SQVI_filter_options_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_filter_options_2.png\")
<\/a><\/p>\n![\"SQVI_result_filter_50000\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_filter_50000.png\")
<\/a><\/p>\n![\"icons_filter_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_filter_2.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_result_change_layout_sort\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_change_layout_sort.png\")
<\/a><\/p>\n<\/a>Save Default layout<\/h1>\n
![\"icons_choose_layout_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_choose_layout_2-1.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_result_change_layout\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_result_change_layout.png\")
<\/a><\/a>Export to Excel<\/h1>\n
![\"icons_export\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_export.png\"){kind=icon}
<\/a><\/p>\n![\"icons_export_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_export_2.png\"){kind=icon}
<\/a>![\"SQVI_export_excel_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_export_excel_2.png\")
<\/a><\/p>\n![\"SQVI_export_excel_1\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_export_excel_1.png\")
<\/a><\/p>\n![\"icons_views_2\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/icons_views_2.png\"){kind=icon}
<\/a><\/p>\n![\"SQVI_excel_inplace\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_excel_inplace.png\")
<\/a><\/p>\n<\/a>Output format<\/h1>\n
![\"SAP](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_export_as.png\")
<\/a><\/p>\n![\"SQVI_export_as_excel\"](\"https:\/\/www.esfoor.fi\/en\/wp-content\/uploads\/sites\/3\/2018\/02\/SQVI_export_as_excel-1.png\")
<\/a><\/p>\n