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
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
[SOLVED] Report Help
-
- Posts: 12
- Joined: Thu May 19, 2016 8:29 pm
Re: Report Help
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:
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.
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);
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.
-
- Site Admin
- Posts: 2782
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 2 times
Re: Report Help
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
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
-
- Posts: 12
- Joined: Thu May 19, 2016 8:29 pm