Thread: Custom Reports

Wher e is some understandable docs on Custom Reports. I read the manual and support docs and don't know how to put it in the menu.

We have directions on how to do that, refer to Creating Custom Reports For Use Within The Admin Site.

Does the report magically appear after testing the sql statement? At least it seems like magic if you read the doc.
What about complex reports? What's that do to the grid it magically apears in?

The results won't come up until you go to the admin navigate to Orders -> Reports -> Customer Reports and select your report from the drop down box and it will be populated in a datagrid below. This is clearly stated in the KB.

So once run in the SQL menu choice under Configuration the report will show up in the menu of Reports? Is the SQL statement saved in SQL Server? This is what I don't understand. I read the doc several times.

Yes, it is saved on the CustomReport table under the SQLCommand column

The auto response to a report SQL query is a grid. Any way to change that to an external xml or html formating?

What would be the SQL Query to get a custom report for the following

Monthly report with the following data:

Customer Level 1
Monthly Dollar Amount
Monthly Number of Orders


I would like to get a report for the entire past year with each month separately.

Thanks for any help.

This should do it... Extracts order totals, number of orders, and groups them by customer level ID by month

Code:

SELECT	DATEPART(MONTH, o.OrderDate) AS [Month],
		c.CustomerLevelID,
		SUM(OrderTotal) AS [OrderTotals],
		COUNT(*) AS NumberOfOrders
	FROM Orders o
	JOIN Customer c on o.CustomerID = c.CustomerID
	WHERE o.Deleted = 0
		AND o.TransactionState = 'CAPTURED'
		AND o.OrderDate >= '01/01/2009'
		AND o.OrderDate <= '12/31/2009'
	GROUP BY c.CustomerLevelID, DATEPART(MONTH, OrderDate)

Dan,

Thank you for your help but can you post a complete SQL command together with the beginning:

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('Test Report', 'Test Description of our report', 'SELECT Email, BIllingZip FROM ORDERS')

When I execute your command, it gets executed but nothing happens. No custom report to be seen.

Thanks much!

Try this:

Code:

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('Monthly Report', 'Monthly Report based on customer level Amount and no of orders', 'SELECT	DATEPART(MONTH, o.OrderDate) AS [Month],
		c.CustomerLevelID,
		SUM(OrderTotal) AS [OrderTotals],
		COUNT(*) AS NumberOfOrders
	FROM Orders o
	JOIN Customer c on o.CustomerID = c.CustomerID
	WHERE o.Deleted = 0
		AND o.TransactionState = ''CAPTURED''
		AND o.OrderDate >= ''01/01/2009''
		AND o.OrderDate <= ''12/31/2009''
	GROUP BY c.CustomerLevelID, DATEPART(MONTH, OrderDate)')

Thank you Alfred. This worked perfectly!