Use server components to solve web cross-report problems (1)

The most troublesome part of web development is reporting, especially cross-report. I don't know how many weeks it will take to split the seemingly dull data into complex reports. The lengthy SQL statement or several-page-long program code can be seen during the next maintenance, and the system will almost faint.

Recently, this kind of good thing happened to me. As required by the cost statistics, the company should develop a statistical table on the volume of goods transferred between different branches, as shown in. The core of the table is the branch account set name (caller) and the customer name (caller) in the set horizontally. This is a typical cross report.

There are two difficulties in preparing this report:
1. Each branch account set corresponds to a database on the database server and is stored in more than two database servers respectively. Therefore, report data must be queried across servers.
2. Expand the queried data.

It is too complex to directly use SQL statements to complete the query and data expansion, and the efficiency will be very low. If you only use SQL statements to query data, it will be much simpler. You can use SQL Server's Open series functions without considering security issues. If you use code to expand data, it is a very painful task. At this time, I will think of the report control in C/s development, unfortunately, the company did not purchase any Web Report controls, so it had to create one by itself. {===================================================== ======================================
Design by: Peng Guohui
Date: 2007-09-10
Site: http://kacarton.yeah.net/
Blog: http://blog.csdn.net/nhconch
Email: kacarton [a t] sohu.com is the author's original article. repost the article to indicate the source and retain the author's information. Thank you for your support!
========================================================== =====================================}

During the conversation, I took out my visit, baby: Delphi + fast report2.5 (not updated for a long time). Things are old but easy to use. First, create an ActiveX library and Active Server Object.

Then add the showcrossreport method in TLB. The parameters are as follows: constr is the database connection string, command is the SQL query statement, and reportfile is the fastreport report template, which generates the final report.

Then write the showcrossreport code.
{===================================================== ======================================
Design by: Peng Guohui
Date: 2007-09-10
Site: http://kacarton.yeah.net/
Blog: http://blog.csdn.net/nhconch
Email: kacarton [a t] sohu.com

The article is original to the author. Please indicate the source of the article and retain the author's information. Thank you for your support!
========================================================== =====================================}
FunctionTkktwebcrossreport. showcrossreport (ConstConstr, command,
Reportfile: widestring): integer;
VaR
Adoq: tadoquery;
// Frdb: tfrdbdataset;
RPT: tfrreport;
St: tmemorystream;
Ex: tfrhtml2export;
Buf: string;
E: tcomponent;
Begin
E: = tcomponent. Create (Nil);
Try
Adoq: = tadoquery. Create (E );
Try
// Generate the query control and query data
Adoq. Name: = 'adoq ';
Adoq. connectionstring: = constr;
Adoq. SQL. Add (command );
Adoq. open;
{Frdb: = tfrdbdataset. Create (NiL); // The Cross report control of fast report 2.5 can directly read adodataset data
Frdb. Name: = 'frdb ';
Frdb. Dataset: = adoq ;}
St: = tmemorystream. Create; // The report is generated and exported to the internal stream.
// Load the report template ========================================================== ==============================================
Design by: Peng Guohui
Date: 2007-09-10
Site: http://kacarton.yeah.net/
Blog: http://blog.csdn.net/nhconch
Email: kacarton [a t] sohu.com is the author's original article. repost the article to indicate the source and retain the author's information. Thank you for your support!
========================================================== =====================================}

RPT: = tfrreport. Create (E );
Rpt. Name: = 'frport1 ';
// Rpt. Dataset: = frdb;
Rpt. loadfromfile (reportfile );
Rpt. showprogress: = false;
Rpt. preparereport;
St. Position: = 0;
// Create an export control. fastreport has two controls, tfrhtmexport and tfrhtml2export, which are exported to HTML format. tfrhtmexport is exported as a table. By default, there is no table line and there is a data dislocation problem, tfrhtml2export is exported in CSS format and can display table lines in good condition. I use the latter.
Ex: = tfrhtml2export. Create (E );
Ex. Navigator. Align: = haleft;
Ex. showdialog: = false;
// This is the focus. Export the result to the memory stream and send it to the client.
// The advantage of exporting data to the memory stream is that you do not need to generate intermediate files on the server or consider permissions and security issues.
// Fastreport does not have the exporttostream function. I added it. See the description below.
Rpt. exporttostream (ex, St );
St. Position: = 0;
Setlength (BUF, st. size );
St. Read (BUF [1], st. size );
Response. Write (BUF );
St. Clear;
Snapshot t onE: exceptionDo
Response. Write ('<font color = Red> <B> <H2>' + E.Message+ '</H2> </B> </font> ');
End;
Finally
IfEx <>Nil thenFreeandnil (Ex );
IfSt <>Nil thenFreeandnil (ST );
IfRPT <>Nil thenFreeandnil (RPT );
// If frdb <> nil then frdb. Free;
Freeandnil (adoq );
End;
Finally
E. Free;
End;
End;

Finally, the translation project obtains the DLL file, copies it to the server, and registers it. The server component is complete.

Next, we will demonstrate the code.
Create a report template and save it to the server. Name it rpt1.frf.
. {===================================================== ======================================
Design by: Peng Guohui
Date: 2007-09-10
Site: http://kacarton.yeah.net/
Blog: http://blog.csdn.net/nhconch
Email: kacarton [a t] sohu.com is the author's original article. repost the article to indicate the source and retain the author's information. Thank you for your support!
========================================================== =====================================}

Then, call the code (ASP. NET ):
'Use parsesql to generate an SQL query statement
Private function parsesql...

Private sub showreport ()
Dim cmd as string = parsesql ()
If CMD <> "then
Dim crossreport = server. Createobject ("crossreport. kktwebcrossreport ")
Crossreport. showcrossreport (clssql. defaultconstr, CMD, page. mappath (".") + "/rpt1.frf ")
Crossreport = nothing
End if
End sub

Next article: Use server components to solve web cross-report problems (2) -- modify the fastreport source code and support memory stream export.