[SOLVED] Report Help

[admin]

alalien,

For Formatting dates have you seen this..http://wiki.nubuilder.net/nubuilderv3/i ... s#Text_Tab.

And Group By goes after Where is an sql statement.

Steven

[alalien]

I've been pretty wrapped up for the past couple of days and didn't get a chance, until now, to post that I got it working.

My PHP is rather rusty since I haven't had to use it in forever, but I knocked enough rust off to get this going. I was under a time limit since my old machine was dying a slow, yet accelerating death. I usually keep knocking at a problem until I figure it out (which is what wound up happening anyway), but I was hoping someone would have put me in the "You idiot" spotlight and posted up the code lol. I would have gladly accepted my 15 minutes of humble fame.

Here's the code in case someone else is searches for an answer to a similar question:

Code: Select all

$sql = "

CREATE TABLE #TABLE_ID#
SELECT
item, COUNT(*) AS Total, SUM(price) AS Total_Price
SUM(Total_Price) AS Total_Costs
FROM 2013_orders
WHERE odate BETWEEN '#from_date#' AND '#to_date#'
GROUP By item
";

nuRunQuery($sql);

That gave me everything I needed to get the report to display all items in the date range correctly.

A little creative editing the database itself got my date format worked out.

I did not use really anything from the INVLIST report as it wasn't geared for this. It's geared for another type of query and does it beautifully. Since I didn't need to incorporate a filter function, I created my own date range select form to keep it simple.

I will be using INVLIST as a guide to make a report that will show me all open orders, but that shouldn't be as much of a headache now...except for that fact that I will need that one to export to a CSV file. I am hoping that's covered in the wiki.

[admin]

alalien,


CREATE TABLE #TABLE_ID#
SELECT
item, COUNT(*) AS Total, SUM(price) AS Total_Price
FROM 2013_orders
GROUP By item
WHERE odate BETWEEN '#from_date#' AND '#to_date#'
should be..


CREATE TABLE #TABLE_ID#
SELECT
item, COUNT(*) AS Total, SUM(price) AS Total_Price
FROM 2013_orders
WHERE odate BETWEEN '#from_date#' AND '#to_date#'
GROUP By item

Steven

[alalien]

That's exactly what I had posted as my solution about a week before .

[admin]

.