How can I programmatically produce multiple copies of Crystal Reports (Details Section)?

Question

I have a web page that asks the user for an input number and based on the input, an SQL query is executed and the results is then fetched to fill a Crystal Reports page. Here is the code that searches the database and fills the report: (For simplicity, I removed a lot of query string that does not add value to the question, same goes for Crystal Reports fields)

Dim sql As String = ""
sql = " SELECT * from table1 where input_id = '" & INPUT_NUMBER & "'"

Dim ds As DataSet = getTable(sql)
Dim orpt As New CrystalReport3
CrystalReportViewer1.ReportSource = orpt
orpt.DataDefinition.FormulaFields("fldMaterialID").Text = """" & ds.Tables(0).Rows(0).Item("MATERIAL_ID").ToString & """"
orpt.DataDefinition.FormulaFields("fldMaterialID2").Text = """" & ds.Tables(0).Rows(0).Item("MATERIAL_ID").ToString & """"
orpt.DataDefinition.FormulaFields("fldBar").Text = """" & ds.Tables(0).Rows(0).Item("MATERIAL_ID_BAR_CODE").ToString & """"
orpt.Refresh()

I am trying to allow the user to view multiple copies of the report. I already tried:

 orpt.PrintToPrinter(2, False, 0, 1)

But that did not work. I searched, but all the solutions I got were related to Crystal Reports with Saved Queries which is not an option for me.

Answer 1

I finally did it.

For those who would like to know how, or for a better StackOverFlow.com experience, I am glad to share the solution.

After reading this great article, I was inspired by the solution and was able to accomplish the desired result by

1-Replacing the FormulaFields with an ADO dataset containing the same number of columns I did that by creating a new DataSet in VS.2008 and named it adoDataSet. Then added all required column names to it (No linking to the actual data at this point as the data will be pulled dynamically later. This is just like a template for the data only). By default, all columns will be String typed, but that okay for my case.

3- In Crystal Reports, I used DataBase Expert to pull the mentioned dataset into the report and replaced the locations of the formula fields with the columns from the adoDataSet

4- Then in my code to populate the formulafields with data, I just called the same function that takes an SQL query and returns a normal OracleClient data set (so it might return multiple rows as desired)

5- This dataset however can not be used directly with Crystal Reports, so it must be first converted to the same type of the adoDataSet created earlier. so a simple TryCast did that for me.

Dim sql As String = ""
sql = " SELECT * from table1 where input_id = '" & INPUT_NUMBER & "'"

Dim ds As DataSet = getTable(sql)
Dim orpt As New CrystalReport3
CrystalReportViewer1.ReportSource = orpt
dim ds1 as New adoDataSet
ds1 = TryCast(ds,adoDataSet)   ' ds is based on OracleClient data set
                        ' while adoDataSet is the one CrystalReports likes to use
orpt.SetDataSource(ds1)

from there I was able to generate as much copies as I wanted because I own the data, and can cause the data to appear as many times as I like.

Answer 2

I've handled situations like this with these steps:

1. Create a dummy table with a field called key containing the values 1, 2, 3, 4, 5, ... up to some reasonable upper bound. (I use 200.)
2. Add this table in Database Expert. (You'll get a warning about multiple starting points; see below.)
3. In Select Expert, provide a formula like {dummytable.key} <= {?numcopies}.
4. If you're using a field value, instead of using Select Expert you can do the filtering in Database Expert at the join level.

You'll get a copy of your detail record for key = 1, 2, ... , {?numcopies}.

In my application, every record has a field called Company which all contain the same value, so I use that field to join the tables, avoiding Crystal's warning about multiple starting points. To replicate this, add a master key to both tables, containing the same value every record of both tables, and in Database Expert add a join on that field.

Answer 3

here i am creating a crystal reports using Visual Studio 2013 and using ADO Dataset for database field on report. now i want to set default 2 copies of bill on print button on crystal report viewer. so i written code on crystal report viewer load is

private void button1_Click(object sender, EventArgs e)
        {
Report.Bill objRpt = new Report.Bill();
objRpt.SetDataSource(DT1);
objRpt.PrintToPrinter(2, true, 0, 0);
crystalReportViewer1.ReportSource = objRpt;
crystalReportViewer1.Refresh(); 
}