SAP Reporting Tools: QuickView

Managers often want all kinds of reports about accounts, customers and vendors and business transactions that cannot be answered with existing reports. You need to ask the sales, purchases, production, and then try put it together in Excel. However, there are tools in SAP that can help you. Without programming skills, with only a little knowledge about the tables. One of these tools is SAP QuickView.

QuickView is an easy tool that can be used to answer simple questions or perform ad hoc analysis of data without programming skills. QuickView is a personal tool. Only you can see queries created by you.

In many companies using the reporting tools like QuickView is not allowed. The reasons are that badly created queries can harm the system perfomance, or are considered security risk. In case you have access to SQWI in the test system, create and test the report there. If it fulfills the requirements, you can convert the QuickWiew report to SAP Query. You can then copy the Query to Development, assign it as a report to an Area Menu, assign necessary authorization and finally transport all to production.

To give you a picture of how the QuickWiev works, below you find an example of a simple SD billing  report created with QuickView. This presentation is aimed for absolute beginners. For others there are plenty of material online. Just google SAP QuickViewer.

Table of contents

SD Billing Report
Table Join Definition
Initial Screen
Data Fields – VBRK
Data Fields VBRP
Run the Report
Report Result
Remove unnecessary fields
ALV Functions
Report Layout
Sort ascending / descending
Filter Columns
Save Layout
Export to Excel
Output format
SD billing report, customer level
Change Join Condition
Header vs. Item level Data
Amount and signs
Logical Databases
QuickViewer SAP Query

Start Quick View from the System Menu under Services or with transaction SQVI.

SQVI_Menu

The Initial Screen opens. On the right side is the work area and on the left Welcome information.

SQVI_Create_Quick_view_report

SQVI_welcome.

Using the QuickView requires some knowledge of SAP tables and fields in them.
The 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.

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.

Below you can see a simple SD billing document with a header and one item.

SQVI_BillingDocument_VF03

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 “Technical Information” icon in the Performance Assistant Screen.

Icons_technical_information

SQVI_BillingDocument_VF03_F1

You can see that the name of the screen field is “VBRK-VBELN”. The first part is the table name and second the field name.

SQVI_BillingDocument_VF03_F1_field_name

SD Billing Report

Let’s 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.

Enter a title and select a data source. You can choose between table, table join, Logical Data base and SAP Query Info Set.

SQVI_Choose_data_source_choices

If you get all the data needed for the report from a single table, 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.

Table join 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.

Some header + item tables cannot be used in joins, for these SAP has created packaged joins, called Logical Data base. For example accounting document data (BKPF and BSEG) are such tables.

Info sets defined for Sap queries and can be used in QuickViewer.

Here we choose Table join.

Accept the data source definition.

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.

SQVI_basis_mode_layout_mode

Table join definition

Next you are required to define the tables to be joined. Click on the Insert Table icon. Add the first table VBRK.

SQVI_table_join_selection_table1

The table content comes visible. Click again on the Insert table icon and add the item table VBRP.

SQVI_table_join_VBRK

SQVI_table_join_VBRK_VBRP

The join condition VBELN Billing Document number is automatically proposed.
Back with the green arrow.
Now you can start building your report.

Initial Screen

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.

SQVI_Initial_Screen_Basis_mode

SQVI_Initial_Screen_Layout_mode

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.

 

SQVI_Initial_Screen_Basis_mode_2

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.

I prefer to select the fields directly from the Data Fields on the left and tick both the List Field and  the Selection filed at the same time. It is also possible to look for the fields using the binoculars

Data Fields – VBRK

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).

SQVI_table_join_VBRK_VBRP_header_fields

The List fields show on the right in Fields of the List. You can change the order with the black arrows.

SQVI_table_join_VBRK_VBRP_header_data

From the billing document headers following field are selected to our report:

• Billing Document
• Billing Type
• Sales Organization
• Billing Date
• Company Code
• Payer
• Sold-to-Party

List Fields – VBRP

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 ‘Billing Document’ is also in the item table. It is already selected in the header level.  The rule is that always select the fields on the highest levels.  Select the Billing Item at this level.

SQVI_table_join_VBRK_VBRP_data_fields_items

Order the list fields on the right. You can change the order later, if you want to.

SQVI_table_join_VBRK_VBRP_data_fields

The fields in my selection are:

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.

Save the report.

Run the report

Now the query is ready to run.  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.

SQVI_execute_selection_screen_filled

Click the Clock icon to start the report.

Report result

Look at the outcome. The fields are in the same order as in the Fields in List. Maybe some reordering is needed.

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.

SQVI_result_1

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’s 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.

SQVI_table_join_VBRK_VBRP_data_fields_items_subtotals

Let’s go back to the QuickView definition and remove Sales Unit and Subtotal1 from the query.

Remove unnecessary Fields

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.

SQVI_remove_sales_unit

SQVI_remove_sales_unit_1

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.

SQVI_result_1B

ALV-functions

SQVI_ribbon_numbered

QuickViewer reports use SAP Abap List Viewer (ALV) functionality. This offers a several tools to format the layout.

1. Details
2. Sort in ascending/descending order
3. Find / Find next
4. Set Filter / delete Filter
5. Total
6. Subtotal
7. Print
8. Views (List Output, Grid, Excel inplace)
9. Export (File, spreadsheet etc.)
10. Layout (Choose, Change, Save, Manage)
11. Report Calls
12. Display Graphic
13. End User Documentation
14. Launch to SBO Explorer (Business Objects)

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.

Below the report is sorted and sub totaled by the Sold-to-Party column.

SQVI_result_3

Layout

You can choose, change (rearrange columns, sort, filter, add totals and subtotals), save and manage the layouts with the Layout button.

SQVI_result_2

icons_choose_layout_2

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.

change_layout_screen

Sort ascending / descending

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.

SQVI_result_cells_merged

Below the same report without cell merging.

SQVI_result_without_cell_merging

When the columns are sorted, the cells are merged. If you don’t want that, choose the Change Layout / Display. There you can select option without cell merging during sorts.

SQVI_change_layout_Display_Cell_Merging

Filter Columns

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.

You can use either the Set Filter icon in the bar, or the right click the column and select Set Filter from there.

icons_filter_2 

SQVI_save_layout_right_click_menu

Select the column and Filter. On the ‘Determine values’ pop up right click the Net value and select Options from the menu. Choose ‘Greater than or equal to’ and enter the threshold value 50.000.

SQVI_filter_options

Maintain selection options. Select Greater than Equal to and enter the threshold value 50000.

SQVI_filter_net_value

SQVI_filter_options_2

Below you can see the result. The small markings in the column headers tell you which columns have been sorted, totaled or filtered.

SQVI_result_filter_50000

Only values greater than 50.000 are displayed.

The filter is removed with Delete Filter.

icons_filter_2

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.

 

Save Default layout

When you are happy with the styling of your report, you can save the layout.

SQVI_save_layout

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.

Export to Excel

Maybe you want to transfer your report Excel. There a several ways to do it.

Click the Export Button. Select Spreadsheet.

icons_export

Select the Spreadsheet Format. I use ’08 Excel in Existing XXL Format’.

SQVI_export_excel_2

The outcome in Excel looks like this.

SQVI_export_excel_1

Another way is to choose Excel from the Views icon.

icons_views_2

The outcome in Excel looks like this.

SQVI_excel_inplace

Output format

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.

SQVI_export_as

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.

SQVI_export_as_excel

Below our report in Output format Spreadsheet.

SQVI_export_excel_1

SD billing report, customer level

Let’s create another report.  This time we are not interested in products. We only need the sales figures by customer. All information we need is in the header table VBRK

Start SQVI and choose Create. Name the report VBRK.

Enter a title.
As Data Source choose Table.
Give the name of the table.
Keep the defaulted Basis Mode.

SQVI_VBRK_Customer_billing_000

Select the list and selection fields.

SQVI_VBRK_Customer_billing_001

Order the fields with the arrow keys.

SQVI_VBRK_Customer_billing_002

SQVI_VBRK_Customer_billing_005

Save the report after changes. Start the report.

Make the selections and start the report with the clock.

SQVI_VBRK_Customer_billing_003

The report is displayed.

SQVI_VBRK_Customer_billing_004

Maybe you would like to have the customer name in the report.
The customer name is not in the table VBRK. It is in the Customer master data table KNA1. Showing the name would demand a join of these tables. However, at this point you cannot change the choice of the Data Source Table to Table join. If you want the Customer name in the report, you need to start from beginning and define a join between tables VBRK and KNA1. I don’t do it now. Instead I add the table in our first report VBRK_VBRP.

Changing the join conditions

Customer name could not be added to the report VBRK, because the the selected Data Source was Table, not Table Join. Let’s try to add it to our original report VBRK_VBRP.

Start SQVI, select report VBRK_VBRP and choose Change.

SQVI_Change

Open the Data Source tab and Change join.

SQVI_Change_table_join

We know that the customer name is in the table KNA1 and therefore want add this table to the join.

Click Insert Table and enter KNA1.

SQVI_Change_table_join_add_table

SQVI_Change_table_join_KNA1

The system joins the KNA1 table to table VBRP and default a join condition. However, this is not a correct link. Right Click the join line and delete the link.

SQVI_Change_tab_join_KNA1_KUNNR

Click Join Conditions.

icons_propose_join_conditions

Insert the tables to be joined. We want to link the KNA1 table with the header table VBRK.

 

SQVI_Change_table_join_conditions_2

The system proposes a join. This time we a not happy with field proposal and change it manually linking fields KUNAG and KUNNR. When we go back to the QuickView determination screen, the KNA1 table is added on the left.

SQVI_Change_tab_join_KNA1_added

Choose the Field Name1 from table KNA1. Move it after the Sold-to-Party.

SQVI_Change_tab_join_KNA1_name1

Save the report and run it. Looks like nothing has changed in the report. The reason for this, is that the name1 is missing from our default layout. Click the Change layout and move the Name1 to Displayed columns.

 

SQVI_change_tab_join_KNA1_report_Layout

SQVI_Change_tab_join_KNA1_report_name1

Now you can see the customer name.

Amounts and signs

The amounts are in document currency and values are shown as absolute values without signs. You cannot tell the difference between debit and credit notes.

In this example all items were F2 invoices and the sign is not a problem. But usually there are Credit Notes, Cancellations, Return, and the sign must be regarded. The billing type can be used to specify the signs.

In QuickWiew you cannot do anything. If this is the case, export the report to Excel and multiply the amounts that should be negative with -1.

You can not manipulate data in SQVI, you need SAP Query for that.

Header vs. item level data

Maybe your wondered why some fields appear both on header and item level. One example is the Net Value.

SQVI_net_values_header_vs_item

Let’s go back to our original QVI report VBRK_VBRP and do some experimenting. Select Net Value from both tables to the report. Save and start the report. The outcome looks somewhat funny. Same amount (the total of the items values) appears multiple times in the first Net value column. The net value taken from table VBRK is the total net value for the entire invoice. Item level Net value is the net value of individual product rows.

SQVI_result_net_values_header_vs_item

There is switch for technical names in the QuickView definition screen.

SQVI_net_values_tech_view

Logical Databases

Not all tables can be joined. For example accounting document tables BKPF (header) and BSEG (item) cannot be joined. Does this mean that you can’t create accounting reports? No it does not, instead of joins you can use Logical Databases. These are created by SAP and provide a particular view of database tables that can be used in queries.

SQVI_logical_database_BRF

QuickView vs. SAP Query

QuickViewer is an excellent tool for creating ad hoc reports and queries. However, it is a pretty simple tool with many restrictions. For example you cannot:

• add additional logic (add additional fields, distinguish between a debit amount and a credit amount, change the column header texts)
• drill-down to the documents is not possible
• share the queries with other users.

QuickViewer lets you to get familiar with the tables and their contents. Learning to use it is easy, which encourages to experiment and improvise.

When more permanent reports are needed, you can move over to SAP Query, another reporting tool to be used without programming skills. SAP Query has more features than QuickViewer and reports are available for other users.
It is also possible to convert the QuickViewer report to a SAP Query. You do this from SQ01, not SQVI. Use the menu path Query / Convert QuickView and enter the quick view query name.