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.
add comment
1 Answer:
Posted on June 28, 2017 at 4:49pm
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.
