need help with crystal reports 1

[salooo]

Hi,

I am using Visual Studio.NET and deploying crystal reports using the C# language. I would like to create a report to that would help save paper. I need to know how I can create a report that would display the data in the following fashion:

order # Order Date Amount order # Order Date Amount
1 10/15/03 1254.30 2 10/15/03 1358.30
3 10/15/03 125.00 4 10/15/03 1111.50
5 10/15/03 1254.30 6 10/15/03 1358.30
.............................
...............etc

I would really appreciate if someone can tell me the steps to create such a report in crystal. I would like to have 9 columns on each page, where after every 3 columns the following records are displayed as shown above.

Thanks.

Salmaan.

 

[obislavu]

1. If your data to is from a DB you have to create an intermediate table with the structure as you described and generate a .rpt file from this table.
2. If the data is not from a database then you have to create an intermediate text delimitted file and generate a .rpt file from this text file using ODBC Text Driver with a schema.ini file. The schema.ini file should be stored in the same folder as the table file (e.g. text delimitted file). Here is a schema.ini for your file:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;Let be datareport.dat the intermediate text file
;Lines with ; are comments
; this is SCHEMA.INI for datareport.dat
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[datareport.dat]
ColNameHeader=False
Format=Delimited(
MaxScanRows=25
CharacterSet=ANSI
Col1=ORDER_NUM1 Char Width 4
Col2=ORDER_DATE1 Char Width 10
Col3=ORDER_AMOUNT1 Currency
Col4=ORDER_NUM2 Char Width 4
Col5=ORDER_DATE2 Char Width 10
Col6=ORDER_AMOUNT2 Currency
Col7=ORDER_NUM3 Char Width 4
Col8=ORDER_DATE3 Char Width 10
Col9=ORDER_AMOUNT3 Currency


The next step is to execute .rpt file under Crystal Reports Engine (CRPE32.DLL).
In C, C++ you have to write some code to execute .rpt file.
In C# , you a ReportClass is generated and you can use Crystal Report Designer to generate .rpt file from an intermediate table stored in a DB or from a delimitted text file using ODBC Text Driver (which handle delimitted text files as tables).

-obislavu-

 

[salooo]

Hi obislavu,

Thank you for your quick response. Yes I am getting all the info from a DB, you mentioned " If your data to is from a DB you have to create an intermediate table with the structure as you described and generate a .rpt file from this table."

How do I actually come about doing this ??????

Traditionally I am used to creating my reports from the crystal report expert. As you know in order for me to show data in my report I have to have fields in the detail section of my report. The thing that I have no clue about is how can I show data in seperate columns that come from the same field???

Please help me out here and tell me what I have to do to solve this issue. I would really appreciate if you would kindly describe the solution in a step by step manner.

Thanks a lot.

 

[obislavu]

1.
Assume you have a table with the following structure or you grab data from somwhere in a table or view like this:
_tOrder Table
-----------------
Col Name DataType
Order_Num int
Order_Date varchar
Order_Amount money

Example:

Order_Num Order_Date Order_Amount
----------- ---------- ---------------------
1 01/01/2003 100.0000
2 02/01/2003 300.0000
3 12/03/2003 145.0000
4 13/07/2003 278.0000
5 01/12/2003 500.0000
6 12/09/2002 1000.0000
7 01/04/2001 2000.0000
8 01/01/1999 3000.0000

From this table will be populated the table with the following structure:

_tOrderReport
--------------
Col Name DataType
Order_Num1 int
Order_Date1 varchar
Order_Amount1 money
Order_Num2 int
Order_Date2 varchar
Order_Amount2 money
Order_Num3 int
Order_Date3 varchar
Order_Amount3 money

In order to understand I will build it by steps:

Create a view object named _aView1 as follows:
SELECT *, (Order_Num -1)/3 as IndexList
FROM dbo._tOrder
WHERE ((Order_Num - 1) % 3 = 0)
It will select all orders numbers 1, 4, 7, 10, ...
and will assign an index (IndexList) to each record.

Create a view object named _aView2 as follows:
SELECT *, (Order_Num -1)/3 as IndexList
FROM dbo._tOrder
WHERE ((Order_Num - 2) % 3 = 0)

It will select all orders numbers 2,5,8,11, ...
and will assign an index (IndexList) to each record.

Create a view object named _aView2 as follows:
SELECT *, (Order_Num -1)/3 as IndexList
FROM dbo._tOrder
WHERE ((Order_Num - 3) % 3 = 0)
It will select all orders numbers 3,6,9,12, ...
and will assign an index (IndexList) to each record.

Create a store proc or use directly the following to join the above views and populate the _tOrderReport table:

INSERT INTO _tOrderReport
SELECT dbo._aView1.Order_Num AS Order_Num1, dbo._aView1.Order_Date AS Order_Date1, dbo._aView1.Order_Amount AS Order_Amount1,
dbo._aView2.Order_Num AS Order_Num2, dbo._aView2.Order_Date AS Order_Date2, dbo._aView2.Order_Amount AS Order_Amount2,
dbo._aView3.Order_Num AS Order_Num3, dbo._aView3.Order_Date AS Order_Date3, dbo._aView3.Order_Amount AS Order_Amount3
FROM dbo._aView3 RIGHT OUTER JOIN
dbo._aView2 ON dbo._aView3.IndexList = dbo._aView2.IndexList RIGHT OUTER JOIN
dbo._aView1 ON dbo._aView2.IndexList = dbo._aView1.IndexList


Example of running that:

Order_Num1 Oder_Date1 Order_Amount1 Order_Num2 Order_Date2 Order_Amount2 Order_Num3 Order_Date3 Order_Amount3
----------- ---------- --------------------- ----------- ----------- --------------------- ----------- ----------- ---------------------
1 01/01/2003 100.0000 2 02/01/2003 300.0000 3 12/03/2003 145.0000
4 13/07/2003 278.0000 5 01/12/2003 500.0000 6 12/09/2002 1000.0000
7 01/04/2001 2000.0000 8 01/01/1999 3000.0000 NULL NULL NULL

The last records could contains NULL fields where the number of records is not multiple of 3. You should handle NULL fields in the Crystal Report.

Now you can use this table to generate Crystal report using your DB.
The result is a .rpt file that should display under the Crystal reports the above info.
Integrate .rpt file in your C# program.

2. Text file delimited solution.
-Connect with the DB .
-Use a DataAdapter object to grab all data needed to be displayed in the report into a DataSet object.
-Iterate the Rows collections and create a flat delimitted text file in a given folder (local machine or remote machine) with the structure like the _tOrderReport table.
-Define a schema.ini like as described previously and place it in the same folder as the text file.
-Define an DSN file for the ODBC Text Driver (which comes with MS Office) to point to the folder where text file and schema.ini are stored.
-Connect Crystal Reports tool with the text file using the DSN file and design the report from the text file which is interpreted like a table. The result is a .rpt file and should display exactly what I obtained above
-Integrate .rpt file in your C# class and

-obislavu-

 

[salooo]

Hi obislavu,

Thanks a lot for your kind help buddy. I really do appreciate your help for taking out the time to develop these steps. Thanks a bunch....I posted the same question on another forum and someone mentioned the following solution. May be you can use it in the future.

------------------------
Here are the steps to get the data formatted.
1.Put the fields you want in the detail section.
2.Right click on the detail section.
3.Under the Common Tab click the option Format with Multiple Columns
4.Go to the layout tab and fill in the following fields:
Detail Size: Width = 3.00
Gap between details: Horizontal = 2.00
Printing direction = Across then down
5. click ok

This should make your data go across then down.
--------------------------

Thanks again.

Chow.

Salmaan.