Jump to content

  • morlandin
    • Edited on:
    • Version: v8 Product: Jaspersoft® Studio

    Overview

    The tutorial "Creating Charts and Subreports with Jaspersoft Studio" provided a brief introduction to the subreport element, which allows you to insert a report inside another report. This powerful too allows you to create complex layouts within a single document, using multiple data sources and reports.

    In this tutorial we want to create an address book lisitng each person in a company, including their names, phone numbers, and email addresses, as shown in the following image:

    figure_1.png.169075e47d937c56735c7893761cb743.png

    To do this, we need a main report containing two subreports.


    Creation of the Data Adapter

    For this task, we'll need a data source more complex than the sampleDB. For this reason, we need to install JasperReports Server which contains some useful sample databases. To determine which version of Jaspersoft Server you should use with your operating system, check the tutorial Getting Started with Jaspersoft Studio,  and look in the section "Software Requirements and Installation". After you have installed Jaspersoft Studio, use it to create a new Jasper Report using the template "Flower Gray" with the report name "ContactsList". When asked to select a data adapter, click New, then select Database JDBC Connection and click Next.

    1(13).png.fe273d4add16f7999086a4184a23205b.png

    When asked to choose a name for your new data adapter, use "ContactsDataset" and configure it with this data:

    • JBDC Driver: org.postgresql.Driver;
    • JDBC URL: jdbc:postgresql://localhost:5432/sugarcrm;
    • Username : postgres
    • Password: postgres

    After this, use the button "Test" to check if the connection works, and in that case hit "Finish".

    2(10).png.90076efb4f845fd163e755914979521e.png

    Creation of the Main Report

    Now you should be back in the Report Creation wizard, at the step where you are asked to select the data adapter. The new data adapter should be selected automatically; if it isn't, select Contacts Adapter.  In the Insert an SQL query text entry box, enter select id, name, shipping_address_city from accounts. This query returns the name of a company and the city where the store is located. Then click Next.

    3(6).png.69b16cdaf180b84b02d0a82867e880ba.png

    As the next step, you are asked to define groups. Because we don't need them for this tutorial, click >> to add all the fields to the report and click Next. Click Next again, then Finish. The report is created. It should be similar to the one in the image below:

    4(7).png.e82b9b8f1717db74c902c28959259f4b.png

    Creation of the First Subreport

    The next step is to create a subreport to display email addresses. Start a new report by selecting File > New > Jasper Report. Choose the template Blank A4, save it in the same directory of the master report, and call it "EmailReport.jrxml". When asked to choose the data adapter, select the ContactsAdapter created earlier, and use the following query:

    select contacts.email1
    from contacts, accounts_contacts
    where accounts_contacts.account_id = 'placeholder' and accounts_contacts.contact_id=contacts.id
     
    In this query, a static string "placeholder" is used. As suggested by the name, this is not a real value but a placeholder that changes with a parameter described in the next steps. After entering the query, click Next.
    5(5).png.0746389f7ddd538379447816e3f558fe.png
     
    The query locates one field. Add that field to the report by clicking >>, then click Next. Skip the next step by clicking Next, then click Finish to create the report.

    In the Outline view, select the report root node. Then, in the Properties tab, click Edit Page Format.

    6(5).png.2e88acd4051a183b1ee626c0b37d8aa4.png

    In the Page Format window, reduce the width of the page to 270 pixels and remove the margins, since they are not useful in a subreport, then click OK.7(3).png.b1aa5758402ec0c422df917a9e050fda.png

    Because we used a static value as a placeholder, this report does not list any email addresses. We need to retrieve just information for the current person in the master list. To filter the email addresses, we need to use a parameter. In the Outline view, select the report root node. Then in the Property tab, Click Edit query, filter and sort options. In the Dataset & Query Dialog window, select the Parameters tab and click the Add button.

    8(3).png.e11d214280208570e9d4295feff319c8.png

    A new parameter is added with the name "Parameter_1". Change this name to "ACCOUNT_ID", and make sure the type String is applied.

    Next, replace the string placeholder' with the value $P{ACCOUNT_ID}. This query is influenced by a parameter that comes from the main report. Click Ok.

    9(3).png.c2d053459392a04c28f4e2cb65d95ad1.png

    The syntax $P{ACCOUNT_ID} allows the use of a parameter inside a query, in this case to filter the results using a "where" condition.

    The next step is to drag the band column header into the report. Add a Static Text element from the palette and change it to "eMail address". Drag the field email1  from the Outline view into the Detail band, then delete the unused bands (title, page header, column footer, page footer, summary) and resize the Column Header and Detail bands. You should have a layout like the one in the image below:

    10(4).png.d0f89af0a12b97819e2f28a714044de8.png

    Save the report and, if you like, you can run it to see the result.

    Creation of the Second Subreport

    The procedure to create the second subreport it is almost identical to the first one. Start a new report from File > New > Jasper Report. Choose Blank A4 for the template, save it in the same directory as the master, and call it PhoneReport.jrxml. Select  "ContactsAdapter" as the data adapter, and enter the following query:

    select phone_work
    from contacts, accounts_contacts
    where accounts_contacts.account_id = 'placeholder' and accounts_contacts.contact_id=contacts.id

    Add the discovered field to the report, don't define groups and complete the wizard. When the report is created, click Edit Page Format,  remove the margins, and set the width to 270 pixels. In the Edit query, filter and sort expression dialog, switch to the tab parameters and add a new parameter "ACCOUNT_ID". Use this to replace the placeholder in the query, as in the image below:

    11(1).png.0f35ab5c652d491ffa4f49b5cb6291c6.png

    Next, add a Static Text element containing the text "Telephone" in the Column Header band, and add the field "phone_work" to the Detail band. Delete the unused bands and resize these two elements and their bands until you get a report similar to the one shown below:

    12(2).png.1987b3c03f38a2f60f7a3b93d04383a5.png

    Now, save the report and, if you want,  compile it to see how it looks.

    Now it's time to finish the work on the main report.

    Completing the Main Report

    The two subreports have been created. Now, we need to include them in the main report.

    Open the report ContactList and resize the Detail band so it is large enough to include the subreports - start with 50 pixels. Next, place a subreport element from the Palette into the Detail band to launch the wizard.  Choose Select an existing report (selected by default) and click the Select a report file button. A window listing all created reports appears. Select EmailReport.jrxml,  click  Ok, then click Next.

    13.png.aed2020ad850774cb8697f96d7dea537.png

    In the next step, choose the option Use the same connection used to fill the master report (which should be selected by defaut). This ensures the database connection is passed to the subreport, so it can execute its SQL query, then click Next.

    Now we can set an expression for the parameter exposed by the subreport. Click Add to add a new parameter. Double-click to select on the new parameter's name, and enter "ACCOUNT_ID". Select the value field, and and click the button with three dots (the button is visible only if the value field is selected) to open the Expression Editor. In the Expression Editor, double-click to select ID from Fields, then click Finish. When you return to the parameters dialog it should look similar to the one in the image below:

    14.png.90ebac7ea434b276caf0c51beaeee094.png

    Now click Finish. The first subreport is added to the main report.

    From the Palette drag another subreport and repeat the same procedure using as report file PhoneReport.jrxml,  and giving all the other fields the same values. At this point you should have the two subreports into the main report. Resize them to and resize the detail to obtain this result:

    15.png.f7295cd517667608c7414eb19d19f144.png

    Go in the Preview tab to compile. If everything is correct, you should see a result similar to the one below. (Don't worry if the emails are all the same, this is still correct - they are the same in the database we used.):

    16.png.50d11060b57b13fdec652bd7131dc69b.png

    1(13).png.cecdb1c446a071a3e43896abd08407b4.png

    2(10).png.5debed1151a9138ed33e891803e46ef6.png

    3(6).png.c828609c7e06bf9e8587632bc455c1d2.png

    4(7).png.b661370fc91e3a8839514301623151ff.png

    5(5).png.c26f8371330dbf87f1f4ebda2282d235.png

    6(5).png.4d8593fd5dbea53d03718e7f06dc9707.png

    7(3).png.c7374f184f159fe73466e7b8e8502485.png

    8(3).png.54e2ea9133f041053777b65a1aa7b78d.png

    9(3).png.c48014db0a36ebbc373d0ddcdca53f0e.png

    10(4).png.bb233fc108fa6d093a0ece7b66bdec2d.png

    11(1).png.5cdcd8e1062c216317061ed9fb3fc1c4.png

    12(2).png.fc94cd11f446f7398c673eae1ac0f3ed.png

    13.png.935135f47c265c6b415d4d18d40f473a.png

    14.png.06ae00c48983d0a17560142df87e9ef2.png

    15.png.adb39130355a2ab3a619cb0b7f41974a.png

    16.png.339c2912d3a4a4c684867d3ab75f3a13.png


    User Feedback

    Recommended Comments



    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...