SQL Query Report

[Akshay Mehta]

Hi,

Is there a way we can add and work with Parameters (?) in the SQL Query Report? If yes can anybody quote an example.

regards

Akshay Mehta

[Rushabh Mehta]

On 15-Aug-2014, at 1:52 pm, Akshay Mehta <mehta....@gmail.com> wrote:

Hi,

Is there a way we can add and work with Parameters (?) in the SQL Query Report? If yes can anybody quote an example.

Check how you can set filters:

https://github.com/frappe/erpnext/blob/develop/erpnext/accounts/report/accounts_payable/accounts_payable.js

regards

Akshay Mehta

--
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-fo...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAMgq%3DkeQPCjUMysn8EgtTNtC%3DC_jLpZUPpjPtHt7j3UwDLfGDA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Akshay Mehta]

Thank you

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/73ADF3DE-7075-427B-8A00-1A43490E9ADD%40gmail.com.

[Akshay Mehta]

Hi,

I was successful in using the following TYPE of Custom report, but today it is giving an error :

Query Report :

select * from `tabItem` where name like %(name)s

Javascript:

frappe.query_reports['Test Query Parameter'] = {

    "filters": [

       {

       "fieldname":"name",

       "label":__("Item Name"),

       "fieldtype": "Data",

       }

    ]

}

Error :

Traceback (innermost last):

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/app.py", line 49, in application

    response = frappe.handler.handle()

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 66, in handle

    execute_cmd(cmd)

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 89, in execute_cmd

    ret = frappe.call(method, **frappe.form_dict)

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/__init__.py", line 529, in call

    return fn(*args, **newargs)

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/widgets/query_report.py", line 81, in run

    result = [list(t) for t in frappe.db.sql(report.query, filters)]

  File "/home/erpnext/frappe-bench/apps/frappe/frappe/database.py", line 108, in sql

    self._cursor.execute(query, values)

  File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 185, in execute

    for key, item in args.iteritems())

 KeyError: 'name'

Any Idea?

regards

Akshay

[Anand Doshi]

also set "reqd": 1 for that filter and try.

-Anand.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAMgq%3DkdgkV_p0gh-%2BjQiLZ%2Baq_n4NW2%3DtekOur2D0rHL1yoyuA%40mail.gmail.com.

[Akshay Mehta]

does not work ... same error

akshay

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAAQzbF1p%2B-wJWibC7jSOE%2BHkRJ%2BVgFrz0cJwsSRtL_DHCmHjYw%40mail.gmail.com.

[Anand Doshi]

Check if you are passing some value in "Item Code".

Also, confirm if the report's name is "Test Query Report", since you are using frappe.query_reports['Test Query Parameter']

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAMgq%3Dkcw7Nuz-SuXqtZKa%2BGOjd-E-uDTKsBHao7-FgAyWrCRCA%40mail.gmail.com.

[Akshay Mehta]

Yes I think I was not putting any value in the field ....

Is it possible to set "default":"%%"? I tried this but did not work.

Akshay

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAAQzbF2upYAmx8QOHSLZ1STrKb0bQ2ykSbD_HkMUmWsrA-GEGw%40mail.gmail.com.

[Anand Doshi]

Hi Akshay,

Should work. After saving the report, go to Help > Clear Cache and try after that

-Anand.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CAMgq%3Dkdno_vEW6DF%2B7%2B8F42BbS98__-CXLWd-GfuUgY94sOYxQ%40mail.gmail.com.