Jump to content
JasperReports Library 7.0 is now available ×

How to calculate multiple columns in one Hoc view?


Rajender kumar

Recommended Posts

 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.

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...