[SOLVED] Report Help

[alalien]

I've gotten my primary forms working in NBPro.

Now I am working on two reports. The first one prompts for a date range and then takes the item name, sum of item quantity, and sum of item cost for each item type in the table and prints it to screen.

Eg -

Code: Select all

Part Number                 Total                Cost
5551111                         52                 $5400.24
662100-1                         4                 $36121.45

and so forth......

Here was the SQL code from the NB2.0 version:

Code: Select all

$startdate = '#from_date#';
$enddate = '#to_date#';
$ord='2013_orders';
 
$sql = "CREATE TEMPORARY TABLE tmp1 SELECT $ord.item, $ord.price FROM $ord WHERE $ord.odate >= '$startdate' and $ord.odate <= '$enddate' ORDER BY item";
nuRunQuery($sql);

$sql = "CREATE TEMPORARY TABLE tmp2 SELECT item, SUM(price) AS total from tmp1 GROUP BY item WITH ROLLUP ";
nuRunQuery($sql);

$sql = "UPDATE tmp2 SET item='0' WHERE tmp2.item='NULL'";
nuRunQuery($sql);

$sql = "CREATE TEMPORARY TABLE tmp3 SELECT item, COUNT(*) AS qty from tmp1 GROUP BY item WITH ROLLUP ";
nuRunQuery($sql);


$sql = "CREATE TABLE #dataTable# SELECT tmp2.item, tmp3.qty, tmp2.total FROM tmp2, tmp3 WHERE tmp2.item=tmp3.item ";
nuRunQuery($sql);


I am guessing I will need to make a select_date form (and table) and then have PHP run the item tallies.

I am at a loss on the PHP part of it tho....

Help?

[admin]

alalien,

Have you read the wiki?

http://wiki.nubuilder.net/nubuilderv3/index.php/Reports

or tutorials..

https://www.youtube.com/watch?v=1TjcguxvTJs

Steven

[alalien]

Thank you for responding. Forgive me for seeming a bit out of sorts on this. I suffered a heart attack a couple of weeks ago and am still getting the cobwebs out of my head.

I have I looked at the wiki and video several times as well as the example in the live demo. I have the report built. I am able to select the date range and get the report page to pop up, but I am unable to get any data to appear.

I'm apparently missing something in the syntax requirements for NBPro. At this point, I cannot tell if my issue is in how I am executing my select_date table/form or if it is in my sql syntax for the report itself.

[hanstel]

you might be getting some Syntax Error on your SQL, always check the Debug Window (Home > Debug)

[alalien]

Not getting anything, so I'm looking at my date range form as the first culprit.

What's the best way to set a date range form?

Currently, I have a form called select_date that corresponds to a table called select_date with startdate & enddate as the values.

[hanstel]

install the "Demo" and check this report 'INVLST', this is exactly what you are looking for

[alalien]

That was the first place I went. The wiki is great, but seeing something in practice is always the approach that works for me.

I don't have a quantity or line item status. In NB 2.0 I had the SQL code query for each occurrence of each part number and then give a total number of those occurrences. It also tallied the dollar amount of those occurrences for each part number.

I'm still stuck.

[hanstel]

What's the best way to set a date range form?

all you have to do is adapt the form of 'INVLST' date range fields
and then the corresponding PHP code to your own requirement

[alalien]

and then the corresponding PHP code to your own requirement

This is what I was asking help with in my first post I had the ZBTW & bla forms and INVLIST in my reports before I created the thread .

Here is my table structure:



There is not an "Outstanding" field to query from, so that is out. Every item of each order gets its own line. This is because we require the return of the broken assembly to complete that order. So, we do not have a "Quantity" column either as that would be redundant.

There are roughly 30 unique items. I need each instance of a part number from "item" to be tallied along with the dollar amounts for that part number from "price" to be tallied.

Basically, if there are 100 instances of part number 100001 at $5 each across the selected date range, then the report query would tally those instances and then sum the dollar amounts of those instances.

For that item, it would show:

Code: Select all

100001                              100                                 $500

The SQL code I posted above is how I was achieving this in NuBuilder 2.0. I have tweaked and edited the INVLIST report every which way I can think of and I have yet to get it to work.

I hope this clears up where I am at and what I need help with

On a side note: it will be easy to edit INVLIST to query for open orders across a date range. This is the other report I'll have to create. INVLIST is more geared towards that type of query. In that case, I'll have it query for an "rdate" for 0000-00-00. All of those together will generate the Open RGA Report.

[alalien]

I've got it "mostly" working.....

If I leave out the line:

Code: Select all

WHERE odate BETWEEN '#from_date#' AND '#to_date#'

Then it will display all part numbers (no repeats), the total count of each, and the total price sum for each. It does this for the entire table.

The problem is that when I add the date line, I get nothing. Here is the code with the line inserted:

Code: Select all

$sql = "

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#'
";

nuRunQuery($sql);

I've checked my date select form and everything matches up in the form and in the objects themselves.

The debug screen gives this:

Code: Select all

===USER==========



===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE odate BETWEEN '2016-02-01' AND '2016-02-29'' at line 6

===SQL===========



CREATE TABLE ___nu157645e4651d64___
SELECT
item, COUNT(*) AS Total, SUM(price) AS Total_Price
FROM 2013_orders
GROUP By item
WHERE odate BETWEEN '2016-02-01' AND '2016-02-29'


===BACK TRACE====

/var/www/html/nu/nurunpdf.php(55) : eval()'d code - line 11 (nuRunQuery)

/var/www/html/nu/nurunpdf.php - line 55 (eval)

Also, all of my dates in the date column of my table are in the following format: yyyy-mm-dd

Is this the way to convert the dates in that column to mm-dd-yyyy? That way, I can use the pop up calendar rather than full manual date entry.

Code: Select all

SELECT convert(varchar, getdate(), 105)
FROM 2013_orders.odate