Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

[SOLVED] Report Help

admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Report Help

Unread post by 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
Posts: 12
Joined: Thu May 19, 2016 8:29 pm

Re: Report Help

Unread post by 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
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Report Help

Unread post by 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
Posts: 12
Joined: Thu May 19, 2016 8:29 pm

Re: Report Help

Unread post by alalien »

That's exactly what I had posted as my solution about a week before :D .
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: [SOLVED] Report Help

Unread post by admin »

.
Locked