Using SubSelect in SQL Query in Jaspersoft Studio

Home  >>  Jaspersoft Studio  >>  Using SubSelect in SQL Query in Jaspersoft Studio

Using SubSelect in SQL Query in Jaspersoft Studio

17
Oct,2014

2

I bumped into a question in Stackedoverflow. It looks as a simple issue, but confuses Jaspersoft Studio user. The issue is getting an error while using subselect statement in SQL. So I created a sample report with subselect statement.

1) I used the Sample Database to create the report.

2) My Query is as follows.

SS_Img1

3) Notice that there is a red underline mark under subselect. The error message is as follows

SS_Img2

4) But if we Ignore this error and click on “Read Fields”, Jaspersoft Studio will read the columns as followsSS_Img3

5) Now I click on OK and drag and drop the fields onto my detail section. (as follows)

SS_Img4

6) and The preview as follows.

SS_Img5

So, when using the subselect in SQL Query, ignore the error message and run the report.

2 Comments so far:

  1. Saleh says:

    Hello,
    I am searching the web for a solution on reporting a total invoices per day for a restaurant ,
    but I have a problem in jasper report that I am trying to make the report for all the invoices issued in certain day the row should have only the invoice number and total invoice and under the row will be the items listed for each invoice and at the end of the report will be the sum of all invoices like the follwing

    In#: 1 Total : 45 $
    Coca Cola 2 pcs 1 $ each peice 2$ total
    Pastry ??? 5 pcs 2 each Piece 10 $ total

    Inv#: 2 Total : 15 $
    pepsi cola 10 pcs 1 $ 10 $
    ????? ?????? ??? ???

    === == ========= Total invoices 60$

    note the data base is Mysql and the programming language is Java,
    and note I am not asking you to write the project for me , but I need a tutorial to do the report in Jasper studio 6.1 community edition

    • rajeshsirsikar says:

      Hi Saleh,

      You may have to use subreports to achieve what you want.

      Main Report : Will have Invoice Number and Total amount rows and Create a subreport for each transaction as per the invoice number. and place it in main report.

      Thanks
      Rajesh Sirsikar

Leave a Reply

Your email address will not be published. Required fields are marked *