Sales Reporting

The following information allows owners of the PRO license to build a sales report including original file names.

Prerequisites

  • Valid Star*Explorer PRO license (or better)
  • Microsoft Access, or another JET/ADO compatible tool.
    All the examples are made from the MS access point of view.
  • General understanding of custom reporting and relational databases

 

Introduction

Since version 0.1.0.106 owners of the PRO license are able to build their sales reports more easily, since you can now link two databases together.

Your local cache database is stored in the .\Data\Cache folder and is named after your account with an MDB extension, e.g. .\Data\Cache\nik.mdb.

Your sales data (if you export them to the database) is located in .Data\Sales folder and is named in a following manner: .\Data\Sales\nik_sales.mdb.

Below is a simple routine showing you how to link databases, then link the tables and build nice report.

Important: As of now there is no easy way to obtain file name information for the already uploaded files. Eventually I'm planning to achieve that goal, too, but for now this would only work with files newly uploaded with version 105 or higher and sales data exported with version 106 or higher (good thing you can always re-export sales data:-).

 

Link Your Databases

For several rather important reasons your sales data is stored separately from the local cached data. This means that if you need to run a report which contains data from both data sources, your first step is to link them together. Luckily, MS Access allows to do this very easily.

Navigate to the .\Data\Sales folder and locate sales MDB file. Open it. If you have MS Access installed, you will see something like this:

Go to File menu, then Get External Data, Link Tables:

It will open standard File Open Dialog. Navigate to .\Data\Cache folder and select MDB named after your account. Click OK. You will see the list of tables (your particular list may vary), make sure to select the following ones:

  • T_CATEGORIES
  • T_SUBCATEGORIES
  • T_ALBUMS
  • T_IMAGES

as shown on the sample image below:

Click OK. You will see something like this:

where linked tables are marked with the little blue arrows.

Congratulations, we're half way there! And the best part - unless something bad happens to your local data, you don't have to repeat this step ever again!

 

A Very Simple Report

Let's start with a very simple report: let's list all our order details, but instead of more-or-less useful URLs let's show the actual file name.

Switch from Tables to Queries and start a new query Designer. Select T_IMAGES and T_SALES_D, as shown on the sample screenshot, below, hit Add and then Close:

Rearrange tables so you can see all the fields, and then drag F_IMAGE_SMID field from T_SALES_D to T_IMAGES, as shown below:

Click the relationship line with your right mouse button (it may be tricky at first, but you'll get to it) to activate the popup menu:

Select "Join Properties" command, and it will bring the following dialog:

Make sure you select option #2 (ALL records from the T_SALES_D), and click OK. Line changes to the arrow. Congratulations, you have just created what is called a "left outer join"!

We're almost there. Now we simply need to select a fields set and we'll be done.

Double-click once each of the following fields in the following order:

  1. F_PRODUCT
  2. F_QUANTITY
  3. F_COST
  4. F_PRICE
  5. F_ORDER_STATUS
  6. F_IMAGE_NAME (from T_IMAGES)

And hit Run button .

If you did everything right, and you actually have some sales on a freshly uploaded pictures, you'll get sales info with the file names.

Now save this query under meaningful name and let's get on to something more advanced.

 

Slightly Advanced Report

Let's find how each individual file did in sales. Good thing that all we need to do to achieve this is to modify our first basic query just a little.

First select the following fields:

  1. F_IMAGE_SMID (from T_SALES_D)
  2. F_IMAGE_NAME (from T_IMAGES)
  3. F_COST
  4. F_PRICE

Then hit right-mouse button on the fields grid and click Totals. The adjust Totals cells to match the screen shot:

That's it! Hit Run button again!

Homework:

  1. add F_QUANTITY and sum it too.
  2. Change F_PRICE total cell to max - and find your best seller:-)

 

 

Happy Reporting!

Copyright © 2005 Star*Explorer, contact: support@starexplorer.com