dynamic group expression

[domurtag]

Hi,

I'm new to Jasper reports, so please be gentle ;)

I've written a JDBC report which has a group expression, but I want the actual column that is used to perform the grouping to be 'dynamic'. In other words, sometimes the data will be grouped by city, sometimes by user, etc.

In order to achieve this I obviously need a report variable which is set to the name of the grouping column. Presumably I also need to refer to this variable in and also in the 'order by' clause of the SQL to ensure that all rows from the same group are consecutive?

Am I on the right track here, and if so, are they any examples of such 'dynamically grouped' reports available?

Thanks in advance,
Tony

[lucianc]

domurtag wrote:

In order to achieve this I obviously need a report variable which is set to the name of the grouping column. Presumably I also need to refer to this variable in and also in the 'order by' clause of the SQL to ensure that all rows from the same group are consecutive?

That's exactly what you need to do. I'm not aware of any sample that illustrates this, however what you would need to do is something like

Code:

Note that the previous sample assumes that the SQL column names exactly match report field names.

HTH,
Lucian

[domurtag]

Thanks very much for the reply. In the example, I noticed the "!" in the line:

Code:

Is this a typo? If not, what's the difference between $P!{OrderColumn} and $P{OrderColumn}

[lucianc]

domurtag wrote:

Thanks very much for the reply. In the example, I noticed the "!" in the line: Code: Is this a typo? If not, what's the difference between $P!{OrderColumn} and $P{OrderColumn}

 

See this tutorial section on report parameters.

Regards,
Lucian

[domurtag]

I finally had an opportunity to try out your suggestion for performing the 'dynamic grouping'. Unfortunately, it doesn't appear to work - all the rows are shown in the same group. Regarding the following section:

Code:

$P{REPORT_SCRIPTLET}.getFieldValue($P{OrderColumn}) "group of " + $P{REPORT_SCRIPTLET}.getFieldValue($P{OrderColumn}) [/code]

The problem would appear to be in the because if I replace what's shown above with:

Code:

$F{partner}[/code]

everything works fine, but of course the grouping is now fixed to the 'partner' field. It seems like what I need to do is not get the current value of the field named ${OrderColumn}, but rather get the field itself. In other words replace the above with something like:
 

Code:

$P{REPORT_SCRIPTLET}.getField($P{OrderColumn})[/code]

But of course this is just a wild guess on my part. A second theory is that the problem is caused by null values in the grouping column. This didn't seem to be an issue when the grouping was fixed to a specific column, but perhaps it causes problems when the grouping is dynamic?

I've attached my report file just in case you want to take a closer look.

Thanks Again,
domurtag

P.S. lucianc, I accidentally clicked the 'smite' button next to your name earlier - apologies [file name=commission.jrxml size=18931]
Post edited by: domurtag, at: 2008/06/09 18:57

[lucianc]

My bad, $P{REPORT_SCRIPTLET}.getFieldValue($P{OrderColumn}) would not work due to the way the engine evaluates it when it needs to determine whether a group break occurred at a specific record.

 

I'm afraid there is no clean and nice way to do this, but fortunately this is where hacks come into help. You won't be able to do this with the default scriptlet, but you will using a "smarter" generic scriptlet (which extends the default one) that features a new method to evaluate fields:

Code:

public class EvaluationScriptlet extends JRDefaultScriptlet {
	public Object evaluateField(String fieldName) throws JRScriptletException {
		JRFillField field = (JRFillField)this.fieldsMap.get(fieldName);
		if (field == null) {
			throw new JRScriptletException("Field not found : " + fieldName);
		}
		
		StackTraceElement[] stack = new Exception().getStackTrace();
		if (stack.length < 2) {
			throw new JRScriptletException("Unable to obtain current call stack"«»);
		}
		
		String evaluateMethod = stack[1].getMethodName();
		Object value;
		if (evaluateMethod.startsWith("evaluateOld"«»)) {
			value = field.getOldValue();
		}
		else if (evaluateMethod.startsWith("evaluate"«»)) {
			value = field.getValue();
		}
		else {
			throw new JRScriptletException("Unable to determine evaluation type from method name " + evaluateMethod);
		}
		return value;
	}
}

 

Then, of course, you will have to use the new scriptlet in the report:

Code:

[code] <jasperReport class="...EvaluationScriptlet"> ... <groupExpression>$P{REPORT_SCRIPTLET}.evaluateField($P{OrderColumn})</groupExpression>

 

I've attached my report file just in case you want to take a closer look.

 

I'm not sure what the report is for, I don't see any report groups in it.

 

P.S. lucianc, I accidentally clicked the 'smite' button next to your name earlier - apologies

 

That's OK, I didn't feel anything :) And since I gave you a solution that doesn't work, I would say it was deserved.

 

Regards,

Lucian

[domurtag]

It works great, thanks a million. Just for the benefit of any future readers, the following:

 

Code:

<jasperReport class="...EvaluationScriptlet">

 

should of course be

 

Code:

[code]<jasperReport scriptletClass="...EvaluationScriptlet">

[kiskuting]

take a look to : kismiss-report.blogspot.com/2011/02/simple-kismiss-reports.html

[erkustan]

Hi 

Can we do this method for more than one columns in order?

 

thx

[sfriedman]

 Here is a sample report that prompts the user to choose which field/column to use for sorting and grouping. It is built on Jaspersoft v4.5 using the sample foodmart database.

I reattached the sample, re-tested on version 5.5.

Post Edited by sfriedman at 12/15/2011 22:35

[jmsalomr]

Hi,

Sorry, I'm very new on Jaspersoft Studio and I'm not java developer. Although I've tried several things, I'm not able to make it work this scriptlet. The problem is that I'm using Jaspersoft Studio on Mac OS X (Yosemite) and I don't know where should I save the .java file with the class.

How should I do?

Thanks!

[jmsalomr]

Hi,

I know now that I should compile the java file first. I've tried using javac EvaluationScriptlet.java, but I got 8 cannot find symbol errors. I suppose that this is because the compiler cannot find the classes referred in the file, so I'm supposing I should specify some path to the compiler.

As I'm using Mac OS X, I've tried the following:

javac -classpath /Applications//TIBCO Jaspersoft Studio 6.0.1.final/configuration/org.eclipse.osgi/bundles/41/1/.cp/lib/jasperreports-6.0.0.jar EvaluationScriptlet.java

But still got the same errors? How should I compile the scriptlet If I don't want to install a java IDE?

Thanks