Archive

Archive for the ‘Data Query’ Category

SQL Joins

May 2, 2012 Leave a comment

One of the more difficult concepts to learn is connecting tables using the Query Builder and understanding the links.   The links are referred to as “Joins” in SQL (Structured Query Language), and there are different types.   Look at the first figure (click on the figure to see it better).

This figure shows the Sunset Reports Query Builder and a query based on two tables:  Customers and Orders.   The Customers table is on the left and the Orders table is on the right.   Note on the Link Properties window which you can see with a double click on the graphical link, that both “Select All from Left” is unchecked and “Select All from Right” is unchecked.   This results in an “Inner Join” in the SQL statement.   It means we only want the records that exist in both tables.  To understand this better, look at the next figure.

The left circle (yellow) represents the left table or the Customer’s table and the right circle (blue) represents the Order’s table or right table.   An Inner Join results in only those records were corresponding records exist in both tables shown by the overlap of the circles (green).  This green area is the “inner” part and is why it is called the “Inner Join”.   That should make it easy to remember.

Now say we want to see all the customers and only the orders that have a customer record.   We need to create a “Left Outer Join”.   We want all the left circle including the overlap region (yellow and green), but not the right circle not in the overlap (blue).   In other words, all records except where there is an order without a corresponding customer.   This is handled in the Query Builder as shown in the next figure.

Now let’s do the opposite.   Now say we want to see all the orders and only the customers that have an order record.   We need to create a “Right Outer Join”.   We want all the right circle including the overlap region (blue and green), but not the left circle not in the overlap (yellow).   In other words, all records except where there is a customer without a corresponding order.   This is handled in the Query Builder as shown in the next figure.

And finally we could create a “Full Outer Join” where everything is returned.   Both circles and the overlapping part.   This is not always available in some database systems (Microsoft Access is an example of this).   For those database systems that support this type of join, the Query Builder can be used to create it as shown in the last figure.   Note both checkmarks are shown on the Link Properties window.

Advertisements

Information Management

April 28, 2012 Leave a comment

If you refer to the definition of “Information Management (IM)” from Wikipedia, you will see that IM “is the collection and management of information from one or more sources and the distribution of that information to one or more audiences. …. the organization of and control over the structure, processing, and delivery of information”

Does this definition describe Sunset Reports?   Actually this definition is exactly what Sunset Reports does.    You can define many sources of information from databases, Excel documents, or any other type of file created by another system.   With the query builder, report designer, calculations, scripting, etc., information can be processed.   The delivery of information can take many forms in terms of file formats.    Delivery can be scheduled and there are several destination types including websites, shared folders, cloud services, email, and more.

Academia

February 6, 2012 Leave a comment

Sunset Reports is a perfect tool for the classroom.

Advantages

  • A working software tool for a variety of “Information System” topics
  • Easy installation for instructor and students
  • No additional software required
  • Many samples included and the instructors can create their own examples
  • Sample course outline provided
  • Concepts directly applicable to other software
  • FREE – No cost to instructor or students – License valid for duration of semester
  • Discount available for purchase after semester

Applicable Subject Areas

  • SQL and Query Design
  • Report Design
  • Graphing Data
  • Web Page Design
  • Programming

System Requirements

The software runs on any Windows XP, Windows Vista, or Windows 7 computer.  It can run on either 32 or 64 bit machines.  Licensing is for installation on a single workstation.   The program is written based on Microsoft .NET version 3.5.   This is generally available as part of the operating system, but can be installed free from the Microsoft website if needed.  The installation program will warn you if this is needed.   The user manuals are in PDF form, so you will also need a PDF reader, available free from the Adobe website.   Minimal memory and hard disk space is necessary.

Procedure to Get Software

Instructors:

For a FREE copy:  Contact Sunset Reports via email at Education@SunsetReports.com.  Include in your email your name, school name, course name and number, and approximate number of students. If you have a web link to either your website and/or the course website, please include that as well.   Also include the dates the course will be held.   You will then receive an email with instructions for downloading the software for yourself.   The software will then need to be activated and will be usable from the date of the request to 1 month after the course ends.   To Purchase a copy:  Go to the Purchase page.   Choose the Workstation Edition with the Educational discount, and pay online.

Students:

For a FREE copy:  Contact Sunset Reports via email at Education@SunsetReports.com.  Include in your email your name, school name, instructor name, course name and number, and starting date of the course.  You will then receive an email with instructions for downloading the software for yourself.   The software will then need to be activated and will be usable from the date of the request to 1 month after the course ends.

Data Gathering

January 5, 2012 Leave a comment

A recent customer of Sunset Reports really exploited the power of the product.    The customer was a distributor of parts and wanted to get sales data from their customers.  The application required connection to several different sales databases including Access, SQL Server, and Oracle.   It was necessary to collect sales data for products.   Each customer got a copy of Sunset Reports.  Since there were several different database schema’s, the query builder in Sunset Reports was used to extract the data.   Each different system required a different query.   A report was created that normalized the format of the data so all systems could be combined and analyzed in the same way.   The report was used to create a CSV text file and the file was daily uploaded to an FTP site.   All this was done with Sunset Reports.    The FTP site was hosted by the distributor and a program was written to consolidate the CSV files.   Then another copy of Sunset Reports produced a set of reports against the consolidated data.

The savings in software development was tremendous.   Sunset Reports provided the means to connect to, query, and transform the data.   The data was exported to a file, uploaded, and then reported on.   Comparatively, the alternatives would have cost over $20,000 more.

Reporting from the Web

July 26, 2011 Leave a comment

If you need the ability to include Web data in a report, here is a great method.  Using Excel gives you the ability to get data from the Web.   The data comes from tables that exist on web pages.   You create a web query in Excel 2010 by going to the Data tab on the ribbon bar, then selecting the icon “From Web”.   Using the New Web Query dialog, select a data table on the web page.   Once you click “Import”, the data is loaded onto the worksheet and the data range is given a name.   You can change the name if you want.   Now, with a named range, Sunset Reports can directly read the data and you have a data source for your report.

We have plans to include Web RSS feeds as a data source for a report as well as capturing an image of a webpage and placing it in a report.   These changes are currently in development.

Data Repository

April 15, 2011 Leave a comment

Do you ever need to extract data from some data source and store it temporarily so you can use it again? Sunset Reports is a great tool for data conversion. You can connect to a variety of data sources and create a report design that is solely used to send data to Excel. Excel is then the repository. Now you can create a report using that Excel repository as the data source.   Check out the video titled Query Builder and Excel Demo both on our YouTube channel Sunset Reports on YouTube