45
208
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between #1/1/1994# And #1/1/2001#));"
2.
The next step is to identify the parts of the SQL statement that will be turned into parameters. In this
example, the parameters are the dates in the WHERE clause, shown above in boldface. Let's call these
parameters Beginning Date and Ending Date. Since these names contain spaces, they need to be enclosed in
square brackets:
strSQL = "SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
3.
Finally, the parameters must be identified in the beginning of the SQL statement with a PARAMETERS
clause, that includes the parameter name, type, and default value:
strSQL = "PARAMETERS [Beginning Date] DateTime 1/1/1994, " & _
"[Ending Date] DateTime 1/1/2001;" & _
"SELECT DISTINCTROW * FROM Employees " & _
"INNER JOIN (Orders INNER JOIN [Order Subtotals] " & _
"ON Orders.OrderID = [Order Subtotals].OrderID) " & _
"ON Employees.EmployeeID = Orders.EmployeeID " & _
"WHERE (((Orders.ShippedDate) " & _
"Between [Beginning Date] And [Ending Date]));"
When this statement is executed, the control shows a dialog box prompting the user for Beginning Date and Ending
Date values. The values supplied by the user are plugged into the SQL statement and the report is generated only
for the period selected by the user.
The dialog box is created on-the-fly by C1Report. It includes all parameters in the query, and uses controls
appropriate to the parameter type. For example, check boxes are used for Boolean parameters, and date-time
picker controls are used for Date parameters. Here is what the dialog box looks like for the SQL statement listed
above:
The syntax for the PARAMETERS clause consists of a comma-separated list of items, ending with a semi-colon.
Each item describes one parameter and includes the following information:
Parameter name. If the name contains spaces, it must be enclosed in square brackets (for example,
[Beginning Date]). The parameter name appears in the dialog box used to collect the information from the
user, and it also appears in the WHERE clause for the SQL statement, where it is replaced with the value
entered by the user.
Parameter type. The following types are recognized by the control:
Type Name
ADO Type
Read PDF in Web Image Viewer| Online Tutorials from PDF documents; Extract images from PDF documents; Add, reorder pages in PDF files; Save and print PDF as you wish; More PDF Reading
rearrange pdf pages in preview; reorder pages pdf
53
209
Date
adDate
DateTime
adDate
Bit, Byte, Short, Long
adInteger
Currency
adCurrency
Single
adSingle
Double
adDouble
Text, String
adBSTR
Boolean, Bool, YesNo
adBoolean
Default value. This is the value initially displayed in the dialog box.
The easiest way to build a parameterized query is incrementally. Start with a simple query that works, then add the
PARAMETERS clause (don't forget to end the PARAMETERS clause with a semi-colon). Finally, edit the
WHERE clause and add the parameter names at the proper place.
You can use the GetRecordSource method to retrieve a proper SQL statement (without the PARAMETERS
clause) from a parameter query. This is useful if you want to create your own recordset using data contained in the
report.
Note: Instead of using a parameter query, you could write code in Visual Basic or in C# to create a dialog box,
get the information from the user, and fix the SQL statement or set the DataSource object's Filter property as
needed. The advantage of using parameter queries is that it places the parameter logic in the report itself, and it
is independent of the viewer application. (It also saves you from writing some code.)
Unbound Reports
Unbound reports are reports without an underlying source recordset. This type of report can be useful in two
situations:
You create documents that have a fixed format, and only the content of a few fields that change every time
you need to render the document. Business forms are a typical example: the forms have a fixed format,
and the field values change.
You want to consolidate several summary reports into a single one. In this case, you would create an
unbound main report, and you would add bound subreports to it.
As an example of a simple unbound report, let's create a simple newsletter without a source recordset. This is done
with the C1ReportDesigner application, except that you leave the ConnectionString and RecordSource properties
blank and add placeholder fields to the report. The placeholder fields are simple labels whose contents will be set
by an application.
Assuming you created a report with six placeholder fields named "FldHeadlineXXX" and "FldBodyXXX" (where
XXX ranges from 1 to 3), you could use the following code to render the report:
Visual Basic
Private Sub MakeReport()
' find report definition file
Dim path As String = Application.StartupPath
Dim i As Integer = path.IndexOf("\bin")
If i > -1 Then path = path.Substring(0, i)
path = path & "\"
45
210
' load unbound report
c1r.Load(path & "Newsletter.xml", "NewsLetter")
' set field values
c1r.Fields("FldHeadline1").Text = "C1Report Launched"
c1r.Fields("FldBody1").Text = "ComponentOne unveils…"
c1r.Fields("FldHeadline2").Text = "Competitive Upgrades"
c1r.Fields("FldBody2").Text = "Get ahead …"
c1r.Fields("FldHeadline3").Text = "C1Report Designer"
c1r.Fields("FldBody3").Text = "The C1Report Designer..."
' done, show the report
c1ppv.Document = c1r.Document
' and/or save it to an HTML document so your subscribers
' can get to it over the Web
c1r.RenderToFile(path & "Newsletter.htm", FileFormatEnum.HTML)
End Sub
C#
private void MakeReport()
{
// find report definition file
string path = Application.StartupPath;
int i = path.IndexOf("\bin");
if ( i > -1 ) { path = path.Substring(0, i)
path = path + "\";
// load unbound report
c1r.Load(path + "Newsletter.xml", "NewsLetter");
// set field values
c1r.Fields["FldHeadline1"].Text = "C1Report Launched";
c1r.Fields["FldBody1"].Text = "ComponentOne unveils…";
c1r.Fields["FldHeadline2"].Text = "Competitive Upgrades";
c1r.Fields["FldBody2"].Text = "get { ahead …";
c1r.Fields["FldHeadline3"].Text = "C1Report Designer";
c1r.Fields["FldBody3"].Text = "The C1Report Designer...";
// done, show the report
c1ppv.Document = c1r.Document;
// and/or save it to an HTML document so your subscribers
// can get to it over the Web
c1r.RenderToFile(path + "Newsletter.htm", FileFormatEnum.HTML);
}
Here's what this issue of ComponentOne's newsletter looks like. Notice that our simple program does not deal with
any formatting at all; it simply supplies the report contents. The report definition created with the
C1ReportDesigner application takes care of all the formatting, including a headline with a logo, page footers,
fonts and text positioning.
Separating format from content is one of the main advantages of unbound reports.
22
211
Custom Data Sources
By default, C1Report uses the ConnectionString and RecordSource properties to create an internal DataTable
object that is used as a data source for the report. However, you can also create your own recordsets and assign
them directly to the Recordset property. In this case, C1Report uses the recordset provided instead of opening its
own.
You can assign three types of objects to the Recordset property: DataTable, DataView, or any object that
implements the IC1ReportRecordset interface.
Using Your Own DataTable Objects
The main reason to use your own DataTable objects is in situations where you already have the object available,
and want to save some time by not creating a new one. You may also want to implement security schemes or
customize the object in some other way.
To use your own DataTable object, simply assign it to the RecordSet property before you render the report. For
example:
Visual Basic
Private Sub CreateReport(strSelect As String, strConn As String)
' fill a DataSet object
Dim da As OleDbDataAdapter
da = new OleDbDataAdapter(strSelect, strConn)
Dim ds As DataSet = new DataSet()
da.Fill(ds)
44
212
' get the DataTable object
Dim dt As DataTable = ds.Tables(0)
' load report
c1r.Load("RepDef.xml", "My Report")
' render report
c1r.DataSource.Recordset = ds.Tables(0)
c1ppv.Document = c1r.Document
End Sub
C#
private void CreateReport(string strSelect, string strConn)
{
// fill a DataSet object
OleDbDataAdapter da;
da = new OleDbDataAdapter(strSelect, strConn);
DataSet DataSet ds = new DataSet();
da.Fill(ds);
// get the DataTable object
DataTable dt = ds.Tables[0];
// load report
c1r.Load("RepDef.xml", "My Report");
// render report
c1r.DataSource.Recordset = ds.Tables[0];
c1ppv.Document = c1r.Document;
}
The code above creates a DataTable object using standard ADO.NET calls, and then assigns the table to the
Recordset property. Note that you could also create and populate the DataTable object on the fly, without relying
on and actual database.
Writing Your Own Custom Recordset Object
For the ultimate in data source customization, you can implement your own data source object. This option is
indicated in situations where:
Your data is already loaded in memory.
Some or all of the data is calculated on demand, and does not even exist until you request it.
The data comes from disparate data sources and you don't have an easy way to create a standard
DataTable object from it.
To implement your own data source object, you need to create an object that implements the IC1ReportRecordset
interface. This interface contains a few simple methods described in the reference section of this document.
After you have created the custom data source object, all you need to do is create an instance of it and assign that
to the Recordset property.
Sample Report Available
46
213
For the complete project, see the CustomData sample, which is available for download from the ComponentOne
HelpCentral Sample page.
Data Security
Data security is an important issue for most corporations. If you plan to create and distribute a phone-directory
report for your company, you want to show employee names and phone extensions. You probably don't want
people to change the report definition and create a report that includes people's salaries. Another concern is that
someone could look at the report definition file, copy a connection string and start browsing (or hacking) your
database.
These are legitimate concerns that affect all types of data-based applications, including C1Report. This section
discusses some measures you can take to protect your data.
Using Windows NT Integrated Security
One of the strengths of Windows NT is the way it handles security. When a user logs on, the system automatically
gives him a set of permissions granted by the system administrator. After this, each application or service can query
Windows NT to see what resources he can access. Most popular database providers offer this type of security as an
option.
Under this type of scenario, all you need to do is make sure that the people with whom you want to share your
data have the appropriate permissions to read it. In this case, the ConnectionString in the report definition file
doesn't need to contain any passwords. Authorized users get to see the data and others do not.
Building a ConnectionString with a User-Supplied Password
Building a connection string with a user-supplied password is a very simple alternative to protect your data. For
example, before rendering a report (or when the control reports a "failed to connect" error), you can prompt the
user for a password and plug that into the connection string:
Visual Basic
' build connection string with placeholder for password
Dim strConn
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SecureData\People.mdb;" & _
"Password={{THEPASSWORD}};"
' get password from the user
Dim strPwd$
strPwd = InputBox("Please enter your password:")
If Len(strPwd) = 0 Then Exit Sub
' build new connection string and assign it to the control
strConn = Replace(strConn, "{{THEPASSWORD}}", strPwd)
vsr.DataSource.ConnectionString = strConn
C#
// build connection string with placeholder for password
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\SecureData\People.mdb;" +
"Password={{THEPASSWORD}};";
// get password from the user
string strPwd = InputBox("Please enter your password:");
if (strPwd.Length == 0) return;
47
214
// build new connection string and assign it to the control
strConn = Replace(strConn, "{{THEPASSWORD}}", strPwd);
c1r.DataSource.ConnectionString = strConn;
Creating Application-Defined Aliases
Another possible scenario is one where you want to allow certain users to see the reports, but you don't want to
give them any special authorizations or information about where the data is stored.
There are two simple ways to achieve this with C1Report. One is by using embedded reports. Load the report
definition into your application at design time, using the Load Report dialog box, and the report will be embedded
in the application. This way, you don't have to distribute a report definition file and no one will have access to the
data source information.
The second way would be for your application to define a set of connection string aliases. The report definition file
would contain the alias, and your application would replace it with the actual connection string before rendering
the reports. The alias would be useless in any other applications (such as C1ReportDesigner). Depending on how
concerned you are with security, you could also perform checks on the RecordSource property to make sure no
one is trying to get unauthorized access to certain tables or fields in the database.
The following code shows how you might implement a simple alias scheme:
Visual Basic
Private Sub RenderReport(strReportName As String)
' load report requested by the user
c1r.Load("c:\Reports\MyReports.xml", strReportName)
' replace connection string alias
Dim strConn$
Select Case c1r.DataSource.ConnectionString
Case "$$CUSTOMERS"
Case "$$EMPLOYEES"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SecureData\People.mdb;" & _
"Password=slekrsldksd;"
Case "$$SALES"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SecureData\Numbers.mdb;" & _
"Password=slkkdmssids;"
End Select
' set connection string, render report
c1r.DataSource.ConnectionString = strConn
ppv1.Document = c1r.Document
End Sub
C#
private void RenderReport(string strReportName) {
// load report requested by the user
c1r.Load("c:\Reports\MyReports.xml", strReportName);
// replace connection string alias
string strConn$;
switch (i) { c1r.DataSource.ConnectionString;
Documents you may be interested
Documents you may be interested
