Sets of data

Power Pivot has functionality that offers the choice of a data set that will be used to create reports. Under a set of arbitrarily selected data I mean that for some reason we want to follow, and we want to save them and, if necessary, all together add to the table. Once created sets can be seen as all other reporting dimensions, and their inclusion or exclusion of we influence on the contest of display data.

In order to create data sets you need to add at least one reporting dimension in rows or columns of the Power Pivot report. In our case, we created a Power Pivot report in which we added two reporting dimensions in Rows: Classification and Product Name. In the Values we have added a sum of sold quantities (Sum of KOL). We start process of creating a set when we go to the Analyze ribbon and then click on Fields, Items & Sets. It should open a menu in which should choose the option Create Set based on Row Items. If we had added table fields- reporting dimensions into Columns Excel would have offered us the option Create Set Based on Column Items.

Dialog box opens with the title New Set. At the top, you should first of all, in the required field (Set Name), give the name of the data set. For example, it may be the name of the “best selling products”. Then, we can determine whether we want to find it in a separate folder in the list of reporting dimensions. By default all sets are recorded in Sets folder, but we can store them in a folder with a different name as well. Next, in the list below, we see all the data that will appear in the report. We may add, delete, copy or change the order of their presentation. Depending on the number of reporting dimensions they are shown in one or more columns. Further, click OK button to record the data set. The process of modification of the set can be very complicated, so it is recommended to first filter out data that you want to enter the set, and only then start the option to create it.

The menu Fields, Items & Sets has also an option called Manage Sets. Clicking on it opens a window named Set Manager, and here are contained options for adding, modifying or deleting sets. By clicking on the Add you’re about to add a new set, click on the Modify change an existing, while the Delete option can be used to delete previously made data set.

Once you’ve created a set we see its name in the field Rows. It behaves in Power Pivot report as we have added certain amount of data, hierarchically arranged, with subset of values that we want to show in report.