[SOLVED] Report Help

Re: Report Help

Postby admin » Tue Jun 21, 2016 9:18 am

alalien,

For Formatting dates have you seen this..http://wiki.nubuilder.net/nubuilderv3/index.php/Adding_Objects#Text_Tab.

And Group By goes after Where is an sql statement.

Steven
admin
Site Admin
 
Posts: 3153
Joined: Mon Jun 15, 2009 9:53 am

Re: Report Help

Postby alalien » Wed Jun 22, 2016 5:15 am

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.
alalien
 
Posts: 12
Joined: Fri May 20, 2016 3:59 am

Re: Report Help

Postby admin » Fri Jul 01, 2016 12:43 pm

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
admin
Site Admin
 
Posts: 3153
Joined: Mon Jun 15, 2009 9:53 am

Re: Report Help

Postby alalien » Sat Aug 06, 2016 6:19 am

That's exactly what I had posted as my solution about a week before :D .
alalien
 
Posts: 12
Joined: Fri May 20, 2016 3:59 am

Re: [SOLVED] Report Help

Postby admin » Wed Aug 17, 2016 1:05 pm

.
admin
Site Admin
 
Posts: 3153
Joined: Mon Jun 15, 2009 9:53 am

Previous

Return to Report Writer