We have below requirements in jaspersoft studio,could you please help us to fulfill them?

[imtiyazm]

Hi Jaspersoft team,

can you help us with the below requirement for our jasper report requirement:

1)Our basic requirement is to combine data from multiple data sets and generate a crosstab report for all together. we have used multiple mongo aggregation queries for creating these datasets. but we are not able to combine all these data sets together to generate a single report.

2)In another report we have a requirement to display the report according to the week range. Here we have used the mongo aggregation query. we need to loop around this query for each week.is there any facility to loop around the mongo aggregation query so that we could generate the report for every week?

3)Also is there any feature available in Jaspersoft studio which can help us to perform custom calculations during report generation.

[yama818]

1)
You may be able to use a virtual data source if you have the commercial version.
I haven't tried it with MongoDB, but for example, you can combine Oracle and MSSQL data sources and still get them.
Therefore, I thought it might be possible to combine different MongoDB data sources.
https://community.jaspersoft.com/documentation/jasperreports-server-administration-guide/v561/virtual-data-sources

2)
You may be able to do this by setting the From-To date in the input control and using 'relative date (WEEK+1)' as the initial value.
https://community.jaspersoft.com/wiki/how-use-relative-dates-tibco-jaspersoft-studio-report
Available relative dates: DAY, WEEK, MONTH, QUARTER, SEMI, YEAR

3)
You may be able to use Scriptlet.
Available at: https://community.jaspersoft.com/blog/scriptlet-example-jaspersoft-6x-begineers-hello-world-sample-example

[imtiyazm]

Hi,

Thank you for taking the time and effort, it helped us very much.

Your answer for custom calculations using scriptlets is worked in our scenario. 

For Our first question we have one data source and say we have 3 queries query1,query2,query3, we are generating a data-set for each query, our interest is to combine all these data sets for 3 queries together to generate a single crosstab report. 
could you please suggest the solution for this?

For the second question related to week range, say the user selects week1 to week 5 then we execute the query 5 times for week 1 to week 5 and generate the report. you can check in the image attachment for better understanding. with your current solution, we could possibly generate a report only for the range of weeks but not for an individual week.

[imtiyazm]

.

[yama818]

1)
I see, it's not 'data source', but 'data set'.
I misunderstood the question. I'm sorry.
I don't know of any way to create a single crosstabulation from multiple datasets.
However, I assume that you can combine elements of multiple datasets to create a single dataset, from which you can create a cross-tabulation.
Of course, I don't know the detailed requirements, so I can't say for sure.

2)
I saw the attached image. However, I could not understand the requirement.
Therefore, it is just a guess.
If the idea is to repeat the SQL execution 5 times, I think that would be difficult.
If you can take the approach of creating a SQL that retrieves the data for 1-5 weeks at a time, you may be able to solve the problem.

3)
That's good to know！

 

 

[Tom C]

One data source and 3 queries - Assuming those three queries produce identicle meta data result set, you can combine them together to product one result set:

query1unionquery2unionquery3[/code]

 

[Tom C]

For week range report - You need to break your data into five items based on the date range then display them horizontally using a list element. Something like:

1) Main report query gives the unique work/compaings record

2) Use list element to get the details of those work/compaings record into five rows then lay them out horizontally. The subdataset feeding the list could be something like:

with workdata as (select * from your report_table where work_compaings = $P{work_compaigns})select 1 as week, * from workdata where {week is in week 1 range}union allselect 2 as week, * from workdata where {week is in week 2 range}union allselect 3 as week, * from workdata where {week is in week 3 range}union allselect 4 as week, * from workdata where {week is in week 4 range}union allselect 5 as week, * from workdata where {week is in week 5 range}[/code]

 

[Tom C]

Custom calculations during report generation - if the calculation is simple, you can use in line Java expression and ternary operator in a report variable to meet your requirement.

The tricky part is the evaluation time if your calculation is iteration specific across several rows. In such a case, scriptlet can help but you still need to put the call in the correct report location to get the information that you need. You can also rely on sub components (sub report, list, etc.) to conduct the calculation to avoid coding and implementation hassle with the scriptlet approach.

In summary, it is all depending upon what you need to determine the simplest approach to meet your reporting requirement.