Creating Sub Report using TTX in VB

Before creating sub report using TTX in VB, you should have already known how to create report using TTX. Otherwise, you may want to find out how to create report using TTX in my previous post.

Before going to the sub report, it's a good idea to share the code query in Visual Basic used to retrieve the data first.

Private Sub cmdPrint_Click()
   Dim rs As New ADODB.Recordset 'recordset for main report
   Dim rs1 As New ADODB.Recordset 'recordset for subreport
   Dim sql As String
   With CrystalReport1
       .Reset
       'Set data for main report
       sql = "select CustomerID, CompanyName, ContactName, ContactTitle, " & _
             "Address from Customers where Country = '" & cmbCountry.Text & "'"
       Set rs = New ADODB.Recordset
       Set rs = con.Execute(sql)
      
       .ReportFileName = App.Path & "/RptTest.rpt"
       .Destination = crptToWindow
       .SetTablePrivateData 0, 3, rs
       'End set data for main report
      
       'Set data for subreport
       .SubreportToChange = .GetNthSubreportName(0)
       sql = "select a.customerid, d.productname, c.quantity, c.unitprice  " & _
           "from customers a " & _
           "left join orders b " & _
           "on b.customerid = a.customerid " & _
           "left join orderdetails c " & _
           "on c.orderid = b.orderid " & _
           "left join products d " & _
           "on d.productid = c.productid " & _
           "order by a.customerid, d.productname"
       Set rs1 = New ADODB.Recordset
       Set rs1 = con.Execute(sql)
       .SetTablePrivateData 0, 3, rs1
       'End set data for subreport
      
       .WindowState = crptMaximized
       .WindowShowPrintBtn = True

       .Action = 1
   End With
End Sub

Suppose here's the data retrieved in the main report:


And here's the data retrieved in the sub report:


In this case, we would retrieve the data on the main report, while at the same time, show the orders made by each customer.

Note that there has to be one or more linking fields between the report and sub report in this case. In the data shown above, the linker is the field CustomerId.

Steps in creating the sub report:
1. Assume the main report is done, like the following picture (if you have not tried making the main report, you may want to refer to my previous post about making the main report):



2. To insert sub report, choose menu Insert -> Subreport, then a dialog box will be prompted, insert the desired sub report name, then click Report Expert.



3. Another dialog boxes will be prompted, we will have to create new TTX for the sub report. The steps are the same as those used in creating the TTX for the main report.


4. After finished creating the TTX, add the TTX to the report.


5. Then, go to tab Fields, Add the fields to be displayed on the report, then click OK.


6. And here's the report with the sub report embedded in it.


7. To Edit the view of sub report, double click on the report, and arrange it to get desired layout.


8. After done with the layout of the sub report, we have to go back to the main report to link the report and the sub report to get desired data grouped by certain field(s). In the data used above, the linker field is CustomerId. Right-click on the sub report, choose Change Subreport Links. As we're going to link the field CustomerId in the main report and the subreport, choose the field on the main report, then select the linking field in the sub report (Make sure the field linked on the lower right is the field which belongs to the sub report, not the main report). Click OK.


9. Now we're finished with the main and sub report.

After running the program, we will get a report which contains the data of the Customers and the order details of each customer as the picture shown below.



That's all folks... Good Luck ^^

Read More

Playing with Query - Grouping

Query Language : SQL

Simply saying, here are the tables:
table users (userid, name)
table book (bookid, bookname, author)
table review (reviewId, UserId, BookId, Comment, ReviewDate)

Question: Find users who have reviewed 2 different books on the same day!

Query for creating tables:

create table users (userid int , [name] varchar(50))
create table book (bookid int, bookname varchar(50), author varchar(50))
create table review (reviewId int , UserId int, BookId int, Comment varchar(50), ReviewDate datetime)

Query for data sample:

insert into users values (1, 'A')
insert into users values (2, 'B')
insert into users values (3, 'C')

insert into book values (1, 'Book A', 'Author A')
insert into book values (2, 'Book B', 'Author B')
insert into book values (3, 'Book C', 'Author C')

insert into review values (1, 1, 1, 'AAAA', getdate()-5)
insert into review values (2, 1, 2, 'AAAA', getdate()-5)
insert into review values (3, 1, 3, 'AAAA', getdate()-5)
insert into review values (4, 2, 1, 'AAAA', getdate()-4)
insert into review values (5, 2, 2, 'AAAA', getdate()-4)
insert into review values (6, 2, 1, 'AAAA', getdate())
insert into review values (7, 3, 2, 'AAAA', getdate())
insert into review values (8, 3, 1, 'AAAA', getdate())

By using the query below:

select a.userid, a.userid, b.name, a.bookid, c.bookname, a.reviewdate
from review a
left join users b on b.userid = a.userid
left join book c on c.bookid = a.bookid

The resulting records will be:


Answer:

select * from
(
   select userid, bookSum = count(userid), reviewDate = convert(char(10), reviewdate, 105)
   from review
   group by userid, convert(char(10), reviewdate, 105)
) a
where bookSum = 2

or

select userid, bookSum = count(bookid), reviewDate
from review
group by userid, convert(char(10), reviewdate, 105)
having count(bookid) = 2

And the resulting records will be:



PS: Convert to type 105 is only aimed to convert the type of the date to dd-MM-yyyy so that the date to be compared does not include the time.

Read More