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
Data Fields – VBRK
Data Fields VBRP
Run the Report
Remove unnecessary fields
Sort ascending / descending
Export to Excel
SD billing report, customer level
Change Join Condition
Header vs. Item level Data
Amount and signs
QuickViewer SAP Query
Start Quick View from the System Menu under Services or with transaction SQVI.
The Initial Screen opens. On the right side is the work area and on the left Welcome information.
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.
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.
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.
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.
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.
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.
The table content comes visible. Click again on the Insert table icon and add the item table VBRP.
The join condition VBELN Billing Document number is automatically proposed.
Back with the green arrow.
Now you can start building your report.
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.
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.
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).
The List fields show on the right in Fields of the List. You can change the order with the black arrows.
From the billing document headers following field are selected to our report:
- Billing Document
- Billing Type
- Sales Organization
- Billing Date
- Company Code
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.
Order the list fields on the right. You can change the order later, if you want to.
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.
Click the Clock icon to start the report.
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.
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.
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.
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.
QuickViewer reports use SAP Abap List Viewer (ALV) functionality. This offers a several tools to format the layout.
- Sort in ascending/descending order
- Find / Find next
- Set Filter / delete Filter
- Views (List Output, Grid, Excel inplace)
- Export (File, spreadsheet etc.)
- Layout (Choose, Change, Save, Manage)
- Report Calls
- Display Graphic
- End User Documentation
- 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.
You can choose, change (rearrange columns, sort, filter, add totals and subtotals), save and manage the layouts with the Layout button.
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.
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.
Below the same report 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.
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.
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.
Maintain selection options. Select Greater than Equal to and enter the threshold value 50000.
Below you can see the result. The small markings in the column headers tell you which columns have been sorted, totaled or filtered.
Only values greater than 50.000 are displayed.
The filter is removed with Delete Filter.
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.
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.
Select the Spreadsheet Format. I use ’08 Excel in Existing XXL Format’.
The outcome in Excel looks like this.
Another way is to choose Excel from the Views icon.
The outcome in Excel looks like this.
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.
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.
Below our report in Output format Spreadsheet.
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.
Select the list and selection fields.
Order the fields with the arrow keys.
Save the report after changes. Start the report.
Make the selections and start the report with the clock.
The report is displayed.
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.
Open the Data Source tab and Change 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.
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.
Click Join Conditions.
Insert the tables to be joined. We want to link the KNA1 table with the header table VBRK.
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.
Choose the Field Name1 from table KNA1. Move it after the Sold-to-Party.
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.
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.
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.
There is switch for technical names in the QuickView definition screen.
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.
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.