[MYSQL with clause in Jasperreport]

MYSQL with clause in Jasperreport  and Jasper Soft Studio

WITH
  tb1 AS (SELECT a, b FROM table1),
  tb2 AS (SELECT c, d FROM table2)
SELECT b, d FROM tb1 JOIN tb2
WHERE tb1.a = tb2.c;

I need to use query like above example in MySql dataset, can i able to use?

I have tried like this but i couldn't able.

Thank You in advance

[Doubt on crosstab]

In below example I need red lined measure tab should be in yellow colured column 03/10/20 as well as gery lined 

beacause in 01/10/20 there is two result "01/10/20"and "01/10/20" 

respectively 

in 03/10/20 there is two result "03/10/20"and "03/10/20"

I need in first two column of measure...."there will be many result it should need to start from first"

is there any idea 

[How to sum calculation in crosstab]

how to sum HH:mm:ss time in crosstab

in table or in main report some where we can variable to sum no of field data

but i couldn't able to do the same in crosstab could any1 help

 

[How to change color ( style ) of the field]

I can change Color of the field by inserting conditional style.

My question is if my field value is with AB-CD

i need to split AB and CD and change AB with one color and CD with another color

i can able to split value but i could'nt able to change the style

is there is any method to todo?

Thanks in Advance

[IN and NOTIN Clauses with $X{...}]

tnq u :-)

[Jasperreport export to xlsx]

To exclude header and footer for xls and xlsx use these property shown on desc:

https://community.jaspersoft.com/wiki/how-can-i-suppress-page-headers-and-footers-when-exporting-xls

just change from xls to xlsx in property name if you need xlsx report or for xls just use same property

it will work...

[IN and NOTIN Clauses with $X{...}]

The clause is used to check whether a particular value is present in a discrete set of values. Here is an example:

SELECT * FROM ORDERS WHERE SHIPCOUNTRY IS IN ('USA','Italy','Germany')

The set here is defined by the countries USA, Italy and Germany. Assuming we are passing the set of countries in a list (or better a java.util.Collection) or in an array, the syntax to make the previous query dynamic in reference to the set of countries is:

SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}

where myCountries is the name of the parameter that contains the set of country names. The $X{} clause recognizes three parameters:

Type of function to apply (IN or NOTIN)

Field name to be evaluated

Parameter name

Above example works perfectly...

My questions is?

how to dynamically change Field name by passing parameter?

i have tried by passing parameter for field name $P{...} but couldn't

 

[Report filling is too slow]

Sry, are you talking about query? i have embedded query directly into report by dataset

[Report filling is too slow]

I'm using JDBC database as dataadapter, it almost take 2min and 10 sec for 2000-3000 rows. Is there any tips or method to make report filling faster for JDBC dataset in Jaspersoft studio as well as in jasperreport while exporting

[Can we create group dynamically using JasperReport Library?]

I have read JRGroup and i have tried but it could'nt. Can anyone help me?