I recently talked about different ways to use views when writing reports. Here I’ll tackle two additional ways you can use views: summarizing data and avoiding nulls.
Importing too much data into a PC is a sure way to create problems. If you’re in a high transaction environment, like analyzing hits on a popular website or selling items among thousand of stores, it’s not impossible that you receive thousands of transactions per day. So you may have a sales transaction database that stores the following data:
If you pull all that transactional data into Excel or Crystal, you may have a problem.
Some systems will do the work for you, so you may have a Sales History file that looks like this, with all your data neatly summarized:
But what if you don’t have a file or you want to see the data summarized by day? You can build a view that will produce the data to look like this:
I’ve previously posted on left outer joins and on the problems of nulls. In a solid relational database, we expect there to be a record in each of the three files for a record in the transaction file. But we can’t always expect to find a record in the supporting file.
Let’s take our transaction file for example. What if, in addition to columns that always have values like Company, Profit Center and Account, you had another field, Project, which is sometimes filled in and sometimes isn’t. Instead of the original join you would create a “Left Outer Join” which would allow you to add the information when it existed.
But this can lead to another problem – how do your reporting tools and your end users deal with this new field? Because we don’t always have an entry in the Project field, we have a null ? meaning a missing value.
In the view, you can supply the spaces as necessary:
Case when Project.description is null then spaces else Project.description.
For some reporting tools, you may want to go further. Some tools don’t like fields that sometimes exist and sometimes don’t – it messes up the slicing and dicing. Again could again use your case tool for something like this:
Case when project is blank then “999999” else project.