Wednesday, October 14, 2009

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 ^^
Share:

Friday, October 2, 2009

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

You may be intersted in

Related Posts

Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone. There...

About Me

My photo
Is an ordinary man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing.

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Leave a Message