If you're new to SQL, this site is a great resource: http://www.w3schools.com/sql/
As you're building custom reports, having a local copy of the database is really useful. You'll need to have SQL Server Management Studio installed, with an instance of SQL on your computer. Then, contact your host for a copy of your live store's database, and restore that.
If you aren't running SQL locally and you're not sure what tables/columns are available to build custom reports around, this page might help: http://help.aspdotnetstorefront.com/manual/951/default.aspx?pageid=database_tables
This contains a list of all of the columns in each table in a version 9.5.1 database. Version 10 database structure is very similar, so for most custom reports, this reference will still be useful for newer versions.
Here's a custom report that exports a list of all products that have been designated for free shipping:
INSERT INTO CustomReport (Name, Description, SQLCommand) VALUES ('Free Shipping Products', 'All products with designated free shipping', 'SELECT p.ProductID, p.Name, pv.FreeShipping FROM Product p INNER JOIN ProductVariant pv ON p.ProductID = pv.ProductID WHERE pv.FreeShipping = 1')
Looks like this was posted forever ago and I missed it.
Has anyone else tried to make any custom reports in SQL? I'm curious if you've made any that were life-changing. I'll make sure to share my own once I get it working!
Nicole Swan
Custom Reports are a great way to extract some information from your store's database.
Get to know Custom Reports here:
http://help.aspdotnetstorefront.com/manual/1000/default.aspx?pageid=custom_reports&SearchHighlight=Custom%20Reports&condition=exactphrase
Following the instructions on that page, you can build your own custom reports. The sample query on that page lists out a test report that will provide the email address, and billing zip code associated with every order taken on your store.
See below for a few more custom reports you might find useful.
Here's a custom report that will list out all customer addresses:
This one will give you a report of all categories in your store:
Export a list of all products that don't have a SKU: