Rajender kumar Posted June 28, 2017 Share Posted June 28, 2017 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 More sharing options...
Friendly User Posted June 28, 2017 Share Posted June 28, 2017 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 More sharing options...
Rajender kumar Posted June 30, 2017 Author Share Posted June 30, 2017 @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? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now