Use of Context Variables in Report Views' where clause

Description

You can not use Context Variable values in the where clause of Report Views.

My use case:

I want a report for my sales representative person. He shall only see his own business partners. I did a report in the System client and used a "Report View". In this report view I can filter the report via a "SQL WHERE" clause. I thought I needed a string like "SalesRep_ID=@#AD_User_ID@" but it does not work. It looks like you can not use Context variables at this point.

(a42niem told me he is doing a similar report and it works for him. I looked into the code and I can't find the place where the @...@ is parsed. Perhaps he or me misunderstood that - just wanted to mention it here for the case I am wrong.)

Environment

None

Activity

Show:
Thomas Bayen
September 16, 2014, 5:21 PM

Hi , I use your patch since many months and can confirm that it works for me.

Carlos Ruiz
December 31, 2013, 2:15 AM

Hi Thomas, I reviewed the case and the suggestions, and I think that I prefer the patch suggested by Dirk (with a minor change using windowNo=0 instead of 1)

I'm attaching a patch IDEMPIERE-659_CR.patch - can you please review it and see if it works for you?

NOTE: Using Env.parseVariable can be more powerful (than Env.parseContext) but can add some security concerns

Thomas Bayen
December 27, 2013, 3:34 PM

While doing Bug Triage I found this issue. I use this patch for some time and it works well for me. I combined my 3 commits to one single patch for better review.

I hope we can integrate this soon.

Thomas Bayen
February 25, 2013, 5:34 AM
Edited

Hi Dirk,

thanks for your code. I was sure I missed something.

I remember that you asked in chat why the given where clause was used twice in the result sql string. Your patch addresses this too because the "where query" was added both in DataEngine and in ReportEngine.

I thought long about your patch and why the one or the other place would be better to insert the where clause. I got no easy reason. Perhaps you got one?!? WDYT?

I looked into the Eclipse view of the Calling Hierarchy of "DataEngine.getPrintData(...)". It seems that all possibilities to call this method are originating at ReportEngine. But it seems also that there may be more than one call from different parts of ReportEngine. Our patched line uses "Query.addRestriction(...)". This method call changes the query object. If you call it more than one time you will add another instance of our where string every time. So AFAIS it would be better to call this at the very beginning in the static constructor method "ReportEngine.get(...)" of ReportEngine. My proposal is this (on top of the two commits above):

https://bitbucket.org/tbayen/idempiere/commits/6b7de2e1b1cb9c9a32e039fefda1f57b47187724

(if we agree with this we should format it and integrate it in one single commit before going to trunk)

Your code looks like you also thought about what you did so I am curious about your arguments...

Thomas

Dirk Niemeyer
February 23, 2013, 1:15 PM
Edited

Thomas,

sorry for misleading you. Actually it works here in a specific AD340 version but only because of a patch I had once applied and totally forgot about.

Here it goes:
diff --git a/org.adempiere.base/src/org/compiere/print/DataEngine.java b/org.adempiere.base/src/org/compiere/print/DataEngine.java
— a/org.adempiere.base/src/org/compiere/print/DataEngine.java
+++ b/org.adempiere.base/src/org/compiere/print/DataEngine.java
@@ -166,7 +166,10 @@
// Add WhereClause restriction from AD_ReportView - teo_sarca BF [ 1761891 ]
String whereClause = rs.getString(4);
if (!Util.isEmpty(whereClause))
- query.addRestriction(whereClause);
+ if (whereClause.indexOf("@") == -1)
+ query.addRestriction(whereClause);
+ else // replace context variables
+ query.addRestriction(Env.parseContext(ctx, 1, whereClause.toString(), false));
}
}
catch (SQLException e)

And I removed the handling from ReportEngine:
diff --git a/org.adempiere.base/src/org/compiere/print/ReportEngine.java b/org.adempiere.base/src/org/compiere/print/ReportEngine.java
— a/org.adempiere.base/src/org/compiere/print/ReportEngine.java
+++ b/org.adempiere.base/src/org/compiere/print/ReportEngine.java
@@ -1206,10 +1206,12 @@
{
query = MQuery.get (ctx, pi.getAD_PInstance_ID(), TableName);
}
-
+
+ /* is handled in DataEngine!
// Add to static where clause from ReportView
if (whereClause.length() != 0)
query.addRestriction(whereClause);
+ */

// Get Print Format
MPrintFormat format = null;

Regards,

Dirk

Fixed

Assignee

Carlos Ruiz

Reporter

Thomas Bayen

Labels

Tested By

None

Components

Fix versions

Affects versions

Priority

Minor