InSights Into Data Grids are very powerful. They are usually the first way we start to look at and explore our data. The above screen shot shows the grid with 3 records highlighted (red) and 3 records selected (gray-blue).
The grid allows the user to filter by column values, sort by column values, group by column values, and even do some basic math on numeric fields the group box is used (see more on this below)
One great feature of the grid is the ability to group by column values. In the grid above – you can see that the columns Region, Country, and City have been dragged up into the Group By box at the top. This creates automatic groupings that help you explore your data in a controlled manner that just sorting cannot do.
As well, you will notice that we have a count summary in the foot for each Region, Country, and City – as well as a Sum of the Ship Via costs. These were created by simply right clicking under the field and choosing what type of summary function you want to place on the column.
If you decided to export to our spreadsheet at this point – it would export with all the groupings and summary functions being carried over to the spreadsheet for you.
There are two ways to filter data in the grid by column. The first is shown in the screen capture above. There – right below the column headers is a filter row – that allows you to type in strings, numbers and even dates to filter the data shown by that column value. This is incredibly fast and easy to use! For string fields, you do not even have to enter the entire text – as it will filter as you type. In the above example we could have filtered by say “Be” instead of “Bern” – but we would have gotten any other cities that contained the character string “Be”.
As well as the filter row just below the header row – in the header of each column, you will note a small funnel type icon. If you click on this icon, the program will bring up a small Filter Windows – shown above. This will list all the unique entries in the column – and you can easily pick which items you wish to filter by. This is very handy when you may want to filter by multiple different items.
The filtering capabilities of the Grids are very powerful and flexible. You can filter by multiple columns, multiple values inside one column, You even had dropdown calendars for picking dates!
As well as the filtering by column value we discussed just above – the grid also has a powerful search feature. In the upper right hand corner of the grids is a small edit field – where you can enter Text you want to find in the entire grid. Once you hit the Find button (to the right of the edit field) – the program will immediately search EVERY column of Every row – and determine if that row meets your search criteria. It will do partial matches – so even if the text is in the middle of the column value it will be found. When it is done the search it will display only the rows that match – and will highlight the text in each column that met the search criteria.
You can see in the above screen shot that we have searched for the string “ang” – and it found it in multiple columns – anywhere it occurred in the column value. You may notice that it keeps the custom highlighting that we have done – as shown in the first row at the top.
As you use the grid – you might notice a small tab in the lower left hand corner, labelled as “Layout”. If you click that tab header – you will see something like the image capture above. This shows just one Row of the grid at a time – but it shows it vertically – not horizontally. This has one main advantage in that if you have a column that has multiple lines in it – you will be able to see, and scroll through, all the lines. It also uses different colours to show different field types – Strings are light blue, numeric fields are white, and dates are a light yellow. (Note: the underlying database here is a SQLite DB that holds dates as string fields – so they show up as blue here).
This view of the grid record allow you to focus on the values in just this one record – and allows you to see multiple line columns and scroll through them.
When you right click on any data cell in the grid – you will see this Popup menu. This allows you to easily take certain actions. The first two allow you to copy the cell values and even the entire row values! The third one allows you to readjust the column widths for the entire grid – based on the size of the data in the columns. This can be very handy to get everything back where you can easily read it.
The remaining items in this popup menu – duplicate the functionality of some of the buttons in the Ribbon bar located above the Grid.
When you right click on a Column Header cell at the top of the Grid – you will see a different popup menu. This menu deals more with column level functions – such as sorting, filtering, grouping by, hiding & showing. It is also where you can add Conditional Formatting to the grid where you can highlight data in different ways depending on the values in the Cells! It also allows you to make columns Fixed to the right or left sides of the grid – so that they will not move as you scroll right and left in the grid. This can be very useful when you need to keep the context and value of a certain column(s) in view while scrolling.
