Using output of 1 data set and using it in another dataset, finding differences of 2 date type fiel

Hi,
 
I am new to BIRT designer and I need to finish the task ASAP. Below are my queries.
 
1) I have a dataset that has field event that is displayed on a page and Also, I want to show text corresponding to that event(another dataset) on the same page. So every page has event no(from 1 dataset) and corresponding text(another dataset). Please let me know how to do so. If there are some demos present. Its urgent.
 
 
2) I have 1 dataset and in that query i have used case to find difference of 2 dates and change the output accordingly of another field. It is not working. So how to manage that at designer level. How can I calculated differencec of 2 dates in a record and based on that change ouput of another field in the same record.
Please suggest with a demo or link.
 
 
 
Thanks in Advance.

Find more posts tagged with

Comments

pricher

Hi,
 
For 1) use a join data set to join your two data sets based on the common field.
 
For 2) can you send your report design?
 
Regards,
 
P.

workforperfection

Hi,
 
 
Thankd for the reply,
 
But, I am not sure how 2 datasets work, can u give some example.
 
 
2) I cannot share the design. but would give example, in my data set query we have used
this case
CASE WHEN DATEDIFF(DAY, xdate, ydate) >=10 THEN 'ppp' ELSE 'ffff' END AS third_field FROM table,
 
and in my design i have dataSetRow["third_field"] in one of the columns, but it is not working according to the query, on the sql server it is working fine,
 
but on birt it is not calculating acc to the above query, so i want to myself want to put some expression in the datafield so that i get ppp if differnce of two dates is > 10 else ffff. please suggest

pricher

Hi,
 
I am sending you 2 examples based on Classic Models
 
JoinDataSet.rptdesign shows an example of a joined data set. In this case, it joins data from Customers and Orders on field customernumber.
 
DiffDate.rptdesign shows an example of adding 2 computed columns to the data set. The first computed column, DiffDays, uses the BIRT DateTime function DiffDays to calculate the number of days between two days. It looks like this:
 
BirtDateTime.diffDay(row["ORDERDATE"],row["SHIPPEDDATE"])
 
The second computed column, OverTwoDays, is a boolean that will get the value true if the difference between two dates is greater than 2. The expression looks like this:
 
if (BirtDateTime.diffDay(row["ORDERDATE"],row["SHIPPEDDATE"]) > 2) 
true 
else 
false
 
Hope this helps,
 
P.

workforperfection

Thanks a lot for the solution.
 
Date difference is fine.
 
for 1st one where joining dataset solution is given. I still needs to explore it using subreport as
 
in page layout, I would have eventid and text field.
 
I have 1 query SELECT EVENTID FROM table where ORDER BY EVENTID; where on every page eventid is displayed and other query
 
SELECT text from table1 WHERE table1.EVENTID = ? (here previous query eventid is used)
 
so on every page, event id and corresponding text would appear. I doubt if using joining dataset would return all the rows on every page.
 
Please advice.
 
 
Thanks,

workforperfection

Hi,
 
Thanks,
 
I got the subreport informaiton from the link
<a data-ipb='nomediaparse' href='http://help.eclipse.org/luna/index.jsp?topic=/org.eclipse.birt.doc/birt/rp-HowToDefineADataSetParameter.html'>http://help.eclipse.org/luna/index.jsp?topic=/org.eclipse.birt.doc/birt/rp-HowToDefineADataSetParameter.html</a>
 
Do I need to create report again using list as unrelated tables doesnt show column binding option in data set parameter binding box.
 
Thanks

workforperfection

Hi,
 
I started doing the 1st issue by using List, I created a table which used 1 data set and then another table which used 2nd dataset and I bounded column to output column of 1st dataset but now issue is that for all elements of dataset1 appear in 1st iteration contrary to mine that is 1st iteration should display 1st entry in 1 table correponding to 1st dataset and 1st entry in 2nd table correponding to 2nd dataset which uses 1st dataset output. problem here is I have used 2 tables instead i could have used only text fields for 1st dataset and only table for 2nd dataset. But placing text fields side by side is problem as table provides layout.
 
please help.
 
not sure how it would be solved.

pricher

Hi,
 
I really have a hard time understanding or visualizing the output you want. Can you sketch what the end result should look like?
 
P.

workforperfection

workforperfection

<blockquote class="ipsBlockquote" data-author="workforperfection" data-cid="132560" data-time="1418994699">
<div>
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=10912'>pro.png</a>
</div>
</blockquote>
Hi , 
 
I have attached the output screen screeshot. following is its description. 
I have 3 tables, which has eventid as primary key, 2nd table has eventid as foreign key with some text information field corresponding to that eventid. There can be mulitple rows in the table, with same eventid and different information text field. Similiary there is 3rd table which has eventid as foreign key with some text information field corresponding to that eventid. There can be mulitple rows in the table, with same eventid and different information text field. 
As shown in the page , so a page would have eventid fetched from 1 query(1st dataset mentioned in the screenshot ) with its related information below it. and some information corresponding to that event id on the same page below it(1st dataset_status,1st dataset_description etc ) as shown in the screenshot. 2nd dataset description should show data corrresponding to that eventid in the datafield(as 2nd table have multiple rows with different text for same event id, all that needs to be captured below 2nddatasetdesciption). Similiary 3rd dataset desription field would have all the information corresponding to the eventid(1st table) on that page. 
please let me know if you need more information. 
please help how to do so.

pricher

Hi,
 
I am sending you a new report design example that should solve your problem. The example is based on Classic Models, but I believe it has the same structure as the report you are trying to build.
 
In my example, there are three datasets: ProductLines, Products and Sales. The common key between the 3 data sets is Productline.
 
There are three tables in the report, one for each data set. The outermost table, named Product Line Table, is bound to the ProductLines dataset and it is grouped on field ProductLine. Note that I have removed the extra sections such as report header and footer, group footer and details in that table, and added two more group header sections.
 
The first row of the group header displays the ProductLine and TextDescription from dataset ProductLines. For readability, that section has a light gray background.
 
In the second row of the group header (with a medium gray background), I have inserted a second table, named Products Table, bound on Products. To ensure that I get only the data for the current ProductLine, I added a filter to the Products Table. The filter syntax is: row["PRODUCTLINE"] = row.__outer["PRODUCTLINE"]. The same technique is used for the third row of the group header, but this time, the table is bound to the Sales dataset.
 
Hopefully this is clear!
 
P.

workforperfection

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="132566" data-time="1419000275">
<div>
Hi,
 
I am sending you a new report design example that should solve your problem. The example is based on Classic Models, but I believe it has the same structure as the report you are trying to build.
 
In my example, there are three datasets: ProductLines, Products and Sales. The common key between the 3 data sets is Productline.
 
There are three tables in the report, one for each data set. The outermost table, named Product Line Table, is bound to the ProductLines dataset and it is grouped on field ProductLine. Note that I have removed the extra sections such as report header and footer, group footer and details in that table, and added two more group header sections.
 
The first row of the group header displays the ProductLine and TextDescription from dataset ProductLines. For readability, that section has a light gray background.
 
In the second row of the group header (with a medium gray background), I have inserted a second table, named Products Table, bound on Products. To ensure that I get only the data for the current ProductLine, I added a filter to the Products Table. The filter syntax is: row["PRODUCTLINE"] = row.__outer["PRODUCTLINE"]. The same technique is used for the third row of the group header, but this time, the table is bound to the Sales dataset.
 
Hopefully this is clear!
 
P.
</div>
</blockquote>
Thanks a lot for your valuable inputs. I was busy in some other work,so couldnt reply.
 
Thanks & Regards

workforperfection

Hi Pricher,
 
Above solution works good. I have noticed a issue , in your given example, If we are displaying outermost table Product Line's key PRODUCTLINE on every page, so if sometime page data exceeds normal page size ,in that case data is shifted to next page but the PRODUCTLINE is not shown on 2nd page or subsequent pages as it is already displayed on 1st page for uniq productline. Next productline would be displayed on 1st page but if data exceeeds for that particular productline that productline is not shown on 2nd page.
Do we have any property to be set for the above case. I tried to put productline into the tables header, but then same productline is shown on every page, so what can be done in this case?
 
 
With Regards,
Pranshu

pricher

Hi,
 
Using a List works better. I have attached a new example.
 
Regards,
 
P.

workforperfection

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="132669" data-time="1420037392">
<div>
Hi,
 
Using a List works better. I have attached a new example.
 
Regards,
 
P.
</div>
</blockquote>
Thanks a lot for the help ! Pricher