Date Range Report (Video to Share)

[bchankent]

Re: Date Range Report (Video to Share)

Great screencast. Definitely helped out with my date ranges for my reports.

[Rich Hartnett]

Re: Date Range Report (Video to Share)

Thanks for sharing.

Wish there was a video for doing the same thing using a DBF backend.

[Mike Reed]

Re: Date Range Report (Video to Share)

Thank you Lee and Chad for these. They are great.

One question. Is there a way to add a drop down so the user can use the sort function?

I have a report that lists products. I am using the formats that are outlined in the screencasts and they work great. Now the user wants the ability to sort the report by SKU, Product Description or Product Type. It doesn't appear that I can use an argument in the MySQL order by command.

Any one have have any thoughts?

Thanks,

[iRadiate]

Re: Date Range Report (Video to Share)

Outstanding guys. This is exactly what makes this place so great, everyone is so willing to share and help others.
Thanks Lee & Chad ... fantastic stuff!

[chadbrown]

Re: Date Range Report (Video to Share)

You should be able to do that with the sql order area using the arguments the same way that you use the arguments for the filter. You may need to remove any order clauses from the report as it may try to override the argument.

[Al Buchholz]

Re: Date Range Report (Video to Share)

You should be able to do that with the sql order area using the arguments the same way that you use the arguments for the filter. You may need to remove any order clauses from the report as it may try to override the argument.

FWIW

Removing the order clauses isn't the only option.

Sometimes the ordering options of the report are needed to build group headers and footers.

If the data is already in the order needed, then the option to use set ordering won't resort the data, but will use the data in the order provided.

This is a really powerful option and can give strange results when applied if you're unaware of how it works.

It can also be applied for creative results as needed.

[Mike Reed]

Re: Date Range Report (Video to Share)

If I use this code, then the list is ordered properly:

SELECT product.PROD_ID, product.ACTIVE, product.PRODUCT, product.CATEGORY, product.SKU, product.UOM, product.SVC_TYPE, product.PAY, product.UNITPRICE, product.DISTRICT, product.COM_ID, product.MGMTCODE, complex.COMPLEX, complex.ACTIVE AS ACTIVE1
FROM product product
INNER JOIN complex complex
ON product.COM_ID = complex.COM_ID
ORDER BY product.SKU

If I use this code; ie using arguments, then it does not work, the list comes back, but is not sorted.

args.set("orderby","product.sku")

SELECT product.PROD_ID, product.ACTIVE, product.PRODUCT, product.CATEGORY, product.SKU, product.UOM, product.SVC_TYPE, product.PAY, product.UNITPRICE, product.DISTRICT, product.COM_ID, product.MGMTCODE, complex.COMPLEX, complex.ACTIVE AS ACTIVE1
FROM product product
INNER JOIN complex complex
ON product.COM_ID = complex.COM_ID
ORDER BY :orderby

This is for MySQL.

thanks,
mike

[chadbrown]

Re: Date Range Report (Video to Share)

Is there an order or grouping on the report as Al has said?

[Al Buchholz]

Re: Date Range Report (Video to Share)

Is there an order or grouping on the report as Al has said?

Since the SQL works without arguments, I thinking that the issue is more in the definition of the args or the execution of the sql .

Show us more of the code you are using - from the argument's dim statement to the .execute of the SQL

[Mike Reed]

Re: Date Range Report (Video to Share)

No the report does not have any filter or orderby. Actually, it doesn't seem to make any difference whether there is a report or not.
Here is code and response from the interactive window. Note that the first response is correct, but when done with an argument, it is not. Trying to sort by the SKU.

dim cn as sql::Connection
dim args as sql::Arguments

cn.Open("::Name::MySQL")
qry = <<%A%
SELECT product.SKU, product.PROD_ID, product.ACTIVE, product.PRODUCT, product.CATEGORY, product.UOM, product.SVC_TYPE, product.PAY, product.UNITPRICE, product.DISTRICT, product.COM_ID, product.MGMTCODE, complex.COMPLEX, complex.ACTIVE AS ACTIVE1
FROM product product
INNER JOIN complex complex
ON product.COM_ID = complex.COM_ID
ORDER BY product.sku
%A%
?cn.Execute(qry)
= .T.

rs = cn.ResultSet
?rs.ToString()
= 1 BDRM CRCH 34323 T 1 BDRM PAINT + LIDS COLOR CHAN Painting Service - Interior Painting EA PA 120 200 LV L2077
1 BDRM CRCH 34373 T 1 BDRM PAINT + LIDS COLOR CHAN Painting Service - Interior Painting EA PA 120 200 LV L781
151-2TCCPLG1 28979 T painted kitchen/bath cabinets Painting Service - Interior Painting EA PA 75 150 DA D155
151-PL1 28981 T 1 bdrm full paint Painting Service - Interior Painting EA PA 0 85 DA D155
1BDRM REHAB 43197 T 1 BDRM REHAB PAINT Painting Service - Interior Painting EA PA 0 325 LV L217
1BW 30203 F One Bedroom White Paint+ Paint Painting Service - Interior Painting EA PA 40 145 LV L712
1cl 33802 T One bedroom clean Cleaning - Units EA CL 40 80 LV L2575
1CP+P 32120 F 1 Bedroom Ceiling Paint + Paint Painting Service - Interior Painting EA PA 20 70 LV L2282
1CPMAT 27071 T 1 Bdrm Ceiling Paint Material Painting Service - Interior Painting EA PA 0 10 HO H223


args.Set("orderby","product.sku")
qry = <<%A%
SELECT product.SKU, product.PROD_ID, product.ACTIVE, product.PRODUCT, product.CATEGORY, product.UOM, product.SVC_TYPE, product.PAY, product.UNITPRICE, product.DISTRICT, product.COM_ID, product.MGMTCODE, complex.COMPLEX, complex.ACTIVE AS ACTIVE1
FROM product product
INNER JOIN complex complex
ON product.COM_ID = complex.COM_ID
ORDER BY :orderby
%A%
?cn.Execute(qry,args)
= .T.

rs = cn.ResultSet
?rs.ToString()
= MGR 26916 T SUBCONTRACTED MANAGER Admin EA ZZ 1200 0 AD A1073
ADV 27549 T Advance Pay To Subcontractor for tools Advance EA AV 50 0 PH A1073
RM 33745 T Reimburse For Expenses Maintenance - General EA AD 0 0 AD A1073
REDO 34563 T Redo - Paint Painting Service - Interior Painting EA PA -1 0 AD A1073
REDO 36130 T Redo - Cleaning Cleaning - Other EA CL -1 0 AD A1073
REDO 37791 T Redo - Drywall Maintenance - Drywall EA DR -1 0 AD A1073
REDO 39491 T Redo - Maintenance Maintenance - General EA MA -1 0 AD A1073

[Al Buchholz]

Re: Date Range Report (Video to Share)

is there anything noted in cn.CallResult.text when the results aren't sorted?

Or is there somewhere else to look for sql errors?

Can we look at the SQL that was executed to see what the actual SQL is when the argument values are substituted?

[Mike Reed]

Re: Date Range Report (Video to Share)

is there anything noted in cn.CallResult.text when the results aren't sorted?

Or is there somewhere else to look for sql errors?

Can we look at the SQL that was executed to see what the actual SQL is when the argument values are substituted?

1. When I run it, with or without the sort or argument, this is the result
?cn.CallResult.Text
= "Success"

2. I don't know where else to look, but I am certainly open to suggestion

3. I'm not sure what you mean here. How would I look at the actual SQL?

Thanks for your help.

Mike

[Jim Coltz]

Re: Date Range Report (Video to Share)

The problem is the way that Alpha resolves the argument. Since it is a character value, Alpha wraps it in quotes when it resolves.
To use it in the order clause, where MySQL is expecting a non quoted field value, you need to do something to get the value without the quotes. There are a couple of ways. Below is an example showing the problem and one way how to overcome it in a SQL statement.

dim args as sql::Arguments
dim cn as sql::Connection
dim rs as sql::ResultSet
dim text as c = "text"
dim a as c
dim b as c
args.Set("order",text)
dim sql as c = <<%txt%
select * from tasks  where 1=1 order by :order
%txt%

?sql
= select * from tasks  where 1=1 order by :order

b  = replace_arguments_in_string(sql,args)

'the problem is the double quotes around the column name "text"
?b 
= select * from tasks  where 1=1 order by "text"

'use the .find() method to get the actual value of the argument.
sql = "select * from tasks order by "+ args.find("order").data
?sql
= "select * from tasks order by text"

cn.Open("::Name::Access_gantt")
if .not. cn.Execute(sql,args) then
	msgbox("error", cn.CallResult.text)
	cn.Close()
	end
end if
cn.PortableSQLEnabled = .t.
cn.TraceSQL = .t.
rs = cn.ResultSet
cn.Close()
a = rs.tostring()
'now the order clause will resolve correctly (text is the second column)
?a
= 68	Access Gantt	11/28/2013 12:00:00 00 am	2	0	1	0	0	My test                                                                                                                                                                                                                                                        
67	custom task	11/28/2013 12:00:00 00 am	1	0	1	0	0	Custom Column                                                                                                                                                                                                                                                  
66	My test	11/27/2013 12:00:00 00 am	1	0.200772	1	0	0	UDC                                                                                                                                                                                                                                                            
69	New task	11/28/2013 12:00:00 00 am	1	0	1	0	0

[Mike Reed]

Re: Date Range Report (Video to Share)

Thanks for this info. I will work on this.
appreciate your help, Jim

mike

[GGandhi]

Re: Date Range Report (Video to Share)

hello

I think in MySQL you can use column number for order expression.

args.Set("orderby","product.sku")

so this can be written
args.set("orderby",1) since product.sku is the first column.
then try cn.execute(qry,args)
see if that gives the results you want.