How to calculate multiple columns in one Hoc view?

[Rajender kumar]

 Hello, 

I am using TIBCO Jaspersoft : Ad Hoc Editor to create an ad Hoc view, where I 

have to calculate some columns and generate reports. 

I have created domain by joining some tables (Master, Activities) and using that domin for creating an hoc ad view.

I have data a source where I have two data sets as following :- 

1. In data set one I have MasterId,MasterName

2. In data set  two have multiple record corresponding to MasterId. Ex. 

ActivityId,Name,Type,StartTime,EndTime,MasterId 

(FK from MasterTable.MasterId) 

 

I have to calculate a column 1stActivity-Delay-hours, where I have to show 

the difference between 2 dates in HH:MM format (12horus 39 minutes) 

 

for that I have used create calculated field option and place the formula 

ElapsedMinutes("EndTime", "StartTime")/60  and here I got 12.65 instead of 12:39. When I tried to calculate MOD of minutes 759 % 60 the MOD sign is working as precentage function here insted of doing MOD (1st issue).  To filter the data I have created new measure and place the formula 

like-

tblActivity.StartTime < 12-12-2017 1:10 and tblActivity.EndTime >11-12-2017 2:20 

 

its working fine and showing the correct output except the HH:MM format. 

 

But when I need my second column 2ndActivity-Delay-hours in the same view with different condition 

(filter) for example: where tblActivity.StartTime > 12-12-2017 1:10 and 

tblActivity.EndTime < 11-12-2017 2:20 and Activity.Name='act1' 

it will mess up with 1stActivity-Delay-hours and I am not getting the required output for 2ndActivity-Delay-Hours because of the condition  for the 1stActivity-Delay-hours (tblActivity.StartTime < 12-12-2017 1:10 and tblActivity.EndTime >11-12-2017 2:20 ) is there already on the same datasource.

To solve problem like this, I think we need multiple data-source in our view so the 1stActivity-Delay-Hours and 2ndActivity-Delay-hours will not get affected because of eachother filter conditions.

or I can created a view per column (one column in one view) and at last there should be an option to join them togather to generate reports.

 

As per my understanding we can only have one data-source per view, and we 

can't merge multiple view with different data-source. can anybody suggest 

me how can I achieve this? how can I add multiple data-source in single 

view or merge my multipe view to form main view where I can show all columns togather. 

 

I can do this calculation in SQL server easily by using temp table(#table). For 

instance I will calculate the 1stActivityDelay column and store the 

delay-hours in temp table and then calculate the 2ndActivity delay hours 

with different where condition and again store the result in the temp table and at 

the end I will select all the rows from temp table to show all column calculation as one result-set. But in this tool I am not 

able to find any way to do this. 

 

Thanks.

[Friendly User]

Hello,

one ad hoc view references one data source. If you need two or more data sources to be used by one domain -> ad hoc view then you can use Virtual Data Source feature and unite different data sources under one, create one domain, create calculated fields in that domain and then create a view based off that.

Alternatively you can create JRXML topics that use stored procedures and do all the complicated calculations inside a stored procedure instead.

[Rajender kumar]

@Friendly User, I don't have multiple data-base I have multiple tables. I have Jaspersoft BI JasperReports Server Free license,So is it possible to create JRXML topics with this license?