Introduction This article introduces the implementation ideas and solutions of a Web report design tool, which can be connected to a database, allowing users to design their own reports and obtain report data from the database, the modified data can be stored in the database. This tool can be used to design reports in any form (regular and irregular). It can be used not only as a final product, but also as a developer, you can also develop your own products as a design tool for reports and databases. One goal is to prevent developers (at least themselves) from "report hard work ". Preface and question proposal I encountered a lot of design reports during the software development process of C/S structure and B/S structure. Users have a large variety of reports to implement, and some reports need to be implemented after system acceptance. When developing a C/S structure report, you can use tools such as Pb, Excel, and Formula One to prepare tables and use ASP to develop reports of B/S organizations, XML, XSL, and so on can generate tables in one cell. The style design of the report has been completed for a long time, so that you can take a rest, but not yet. You still need to design a database, design the method and function for generating reports, and design the interface for modifying reports and the function for saving data. Each report must be manually submitted by developers. the user can easily submit the report for testing: the format of a table is incorrect, the formula of a table is incorrect, the size of a table is incorrect, and several new tables are added ...... This makes people feel dizzy, just a little sense of accomplishment, a little relaxed and sighed, alas rushed to run ...... It would be nice to let users do these dirty work on their own! Just after receiving a Web Office System Project of the financial system, there is a thick stack of reports to be implemented. This is only a preliminary requirement. Simply put, we will design a "report design tool ", we hope to release the report work in the future. Of course, it is implemented on the web. As a report design tool, I used to design a C/S structure system and implemented it (with pb6). The user used it quite well. But there are disadvantages: first, it is not easy to transplant and use on the Web. Second, it is only possible to display reports, not to modify data in reports, but not to add data. How can we implement it on the web? Implementation Method To use a web report design tool, you must first know what the report is? How is it produced and implemented? Let's take a look. What is a report? In this case, some data is stored in tables in a fixed position in a fixed format. Since it is a table, there is a "table" with a "Grid ", first, we need to create a "Grid", a "Grid", and a "row" or a "column", and then a "row (column)" and a "row (column) to create a table. Although it is also "spelled out" by the grid, some tables are easy to draw and some are difficult to draw, as shown in figure 1 and figure 2: figure 1 Figure 2 clearly the report shown in map 1 is better "painted" than the report shown in Figure 2. We call a table of Type 1 "Regular Report ", the report type in Figure 2 is called an irregular report ". After talking about the nonsense for a long time, how is the report generated and implemented? I think there are two methods: "painting" and "Filling ". "Drawing a table" is to generate a grid and put the data in a grid by format, that is, the format data is generated at the same time. As a result, the entire table is finished and the data in the table is also available, the report is generated. This method is suitable for generating regular reports so that you can draw a report row by row or column by column. This method has the advantages of Fast Report Generation (batch data processing) and processing. Program Simple (a loop statement is enough ). "Filling in a table" is completely different from "drawing a table". You must first have a table format and then enter the specified data in the specified table location. This method is applicable to both generating irregular reports and generating regular reports. The advantage of this method is that it has a wide range of applicability, but the disadvantage is that it is slow because it needs to obtain the table sample first and then retrieve the data one by one, fill in the cells one by one. Which implementation method should be adopted? There are two solutions: 1. separate processing: when designing a report, let the user determine whether the report is regular (this is very simple). If it is regular, the report will be generated by "drawing a table, otherwise, it is generated by filling in the table. 2. unified processing: all data is generated by filling in the table. I think the computer speed is still improving rapidly. The hardware speed can reduce the software speed, but it is mainly to simplify the software design and processing, the second method is used: Fill in the table. I decided on the implementation method and began to consider some details and technical matters: what issues should I consider to implement this Web report design tool and what problems should I solve? Problems to be Solved Now we need to consider how to implement it. At the beginning, I thought a little bit about it. Don't I solve these problems? 1. How can I create a table by drawing a table? Must be implemented in the browser! -- Simple: Use DHTML and XML/XSL. 2. How to save the table sample? -- Simple: they all exist in the text field of the database. 3. How to connect to the database? -- Simple: Define an attribute to indicate data. 4. How to display data? -- Simple: DHTML technology and XML/XSL can be used. 5. How to modify data? -- Simple: Can I detect events! 6. How to save data? -- Simple: Write it in the database. After careful consideration, I found that there are many problems: 7. How to draw a table? What painting is used? In particular, it must be implemented in a browser. 8. How to design a table style? Such as bold, underline, italic, decimal words, merged tables, and deleted tables. 9. How to save the table sample? In what format? Where does it exist? 10. How is the data display position defined? How to define it? How to save the defined results? Where does it exist? How to fill in data? 11. How is the display format of a single data defined? How to define it? How to save the defined results? Where does it exist? 12. How to connect a report to a database? How to obtain report data? How do I define conditions for getting data? How to define it? How to save the defined results? Where does it exist? 13. If you want to add a new report without a ready-made database table, how can you design a database? How should we design a database? 14. How do users know the meaning of database definitions? How do I know which field and record in the database table should be taken for a grid? 15. How do I know that the report data has been modified (Added) by the user? How does the data correspond to fields in the database table? How can we know which cells of data are combined as a complete record in the database table? How can we know that the user input data is sufficient to form a complete record? How can we save the modified (Added) data? 16. How do I save data that does not need to be created separately in a database table? 17. Design of the data balancing relationship of reports? How to define it? How to save the defined results? Where does it exist? 18. How to Implement the automatic calculation formula? How to define it? How to save the defined results? Where does it exist? 19. How to extract data from other reports to generate a new report? How to define it? How to save the defined results? Where does it exist? 20. How to define a Summary of reports (data from branches generates data from the General Organization )? How to save the defined results? Where does it exist? 21. How to implement data reporting-that is, data format conversion? How is the conversion program designed? How to save it? Where does it exist ?...... The more you think about the problem, the more troublesome you will feel. simply give up and forget it. For example, if you are a slow person like yourself, you may have finished designing this tool, A large number of reports have long been completed. Looking back at the notes and collected data written for the design of this report tool, I am unwilling to do it; then I will renew the report size, which is too heavy and too much-is it hard to work? No! It's worth a bit of trouble for your future! But how should we start with so many problems that need to be solved? What are the key issues?Key Issues Look at the scenery outside the window, wait for a moment to calm down, and then look at the listed problems. One problem and one problem are measured and compared one by one, the first problem to be solved is how to implement the "Table Sample design tool. Because all the operations such as report design, display, modification, and storage are implemented in the tool environment, database Connection, Table Sample storage, and so on have to be implemented and adjusted around the expression and interface form of the tool. Now that you have the idea of a job, start designing it! Don't be busy. The design of the "tool" is not fixed yet! Comparison of several solutions There are many ways to design a table sample on the Web: 1. use XML/XSL/DHTML/html technology to implement 2. use the webbrowser ActiveX Control of IE to implement 3. write ActiveX controls by yourself. 4. write a Java Applet by yourself. which of the following solutions is better for me to use third-party controls? First, let's take a look at the features of each solution. The results are listed as follows:
| No. |
Solution name |
Advantages |
Disadvantages |
| 1 |
Implemented Using XML/XSL/DHTML/html Technology |
1. You can customize it by yourself. 2. Low browser requirements. 3. Easy table format implementation. 4. easy data format conversion. |
1. Long development cycle 2. All functions should be developed 3. computing formulas are not easy to implement 4. Irregular tables are not easy to implement |
| 2 |
Use the webbrowser ActiveX Control of IE to implement |
1. Fast Development 2. Customizable functions 3. Easy table format implementation |
1. Data format conversion is not easy to implement 2. calculation formulas are not easy to implement 3. All functions should be developed 4. Irregular tables are not easy to implement |
| 3 |
Write ActiveX controls by yourself |
1. You can customize it by yourself. 2. Easy table format implementation. 3. Easy Data Control. 4. easy data format conversion. 5. Easy computing formula implementation. |
1. Long development cycle 2. All functions must be developed 3. Difficult format conversion |
| 4 |
Write your own Java Applet implementation |
1. You can customize it by yourself. 2. Low browser requirements. 3. Easy table format implementation. 4. Easy Data Control. 5. Easy computing formula implementation. |
1. Long development cycle 2. All functions must be developed 3. Difficult format conversion |
| 5 |
Implemented using existing third-party controls |
1. Fastest development 2. Most of the required functions are available |
1. Purchase fees may be required. 2. Restricted Functions |
Table 1. The Filtering Scheme principles are as follows: 1. meeting work requirements 2. short development cycle 3. low development costs 4. easy to publish and use on the Web. If you can obtain free third-party software, it is undoubtedly the best solution of 5th. Check whether there is. The sheetspace control in Microsoft Office Web Components is found. Although it is not free (MS Office2000 + must be supported), all users use MS Office, which not only reduces development costs, the user is also used to the operation, and selects the sheet Space Control in Microsoft Office Web Components. Solution Since the key problems have been solved, the other problems have been solved. The following lists the solutions to the problems: 1. How to Create a table by drawing a table? Must be implemented in the browser! -- Design a table sample in the sheetspace control of Microsoft Office Web Components, or design a table sample in MS Excel, and then import it to the sheetspace control of Microsoft Office Web Components; design a program for importing MS Excel. The data display format, paging, Fixed Header, font size, color, and so on are all designed in the table sample. 2. How to save the table sample? In what format? Where does it exist? -- Save the result of the sheet Space Control in Microsoft Office Web Components to the text field of the database. The format of the sample is the HTML format of MS excel, which can be directly obtained by the sheet Space Control in Microsoft Office Web Components. 3. How to connect to the database? -- Define a field attribute to indicate the database table corresponding to the report. 4. How to display data? -- Extract report data from the database and save it to the client in XML format. The data is displayed in the sheetspace control of Microsoft Office Web Components and the obtained report data is filled in the specified cell. 5. How do I know that the report data has been modified (Added) by the user? How does the data correspond to fields in the database table? How can we know which cells of data are combined as a complete record in the database table? How can we know that the user input data is sufficient to form a complete record? -- Check the change event of the sheetspace control in Microsoft Office Web Components. Compare the current cell value with the original data. If the value is different, modify the original data and set the modification flag. The correspondence between the data and the database field is obtained according to the formula. In this way, we can know which cells of data are combined as a complete record in the database table. For the added data, a dedicated input tool is designed to indicate which data you need to enter is complete. 6. How to save data? -- Submit data with the modify (ADD) flag in XML to the server. The server processes the data and then modifies the corresponding data in the database. 7. How to design a table style? Such as bold, underline, italic, decimal words, merged tables, and deleted tables. How is the display format of a single data defined? How to define it? How to save the defined results? Where does it exist? -- The sheetspace control in Microsoft Office Web Components provides these operation functions. These functions are displayed on the page in the form of menus and provide user operations. The results of user operations are saved in the table sample, and stored in the database together when the table sample is saved. 8. How is the data display position defined? How to define it? How to save the defined results? Where does it exist? How to fill in data? -- Design a formula design and interpreter. The data to be displayed is saved in the table sample in the form of a formula. The location where the formula is located is the location where the data is to be displayed, the format of the cell where the formula is located is the data display format (such as font and color ). The formula is stored together with the table sample. When displaying data, obtain the data according to the formula and fill it in the cell. 9. How to obtain report data? How do I define conditions for getting data? How to define it? How to save the defined results? Where does it exist? -- Each report defines the "query condition" attribute to obtain a complete report data. This query condition directly corresponds to the database field. This attribute is stored in the database together with the report table sample. 10. If you want to add a new report without a ready-made database table, how can you design a database? How should we design a database? -- Design a database design tool on the Web page so that users can design the database structure according to their actual needs. 11. How do users know the meaning of database definitions? How do I know which field and record in the database table should be taken for a grid? -- Because a database design tool is provided, the database structure designed with this tool requires that the title of this field be entered, that is, the description of the field; when you define a formula, it is not a boring English field name, but the field title, which is easy to understand. 12. How do I save data that does not need to be created separately in a database table? -- Separate the reports that do not require separate database creation. All the reports are stored in a common table) the data in includes both the report style and report data. 13. How to Design the data balance relationship of reports? How to define it? How to save the defined results? Where does it exist? -- The data balance between reports is fully achieved using the Excel Formula. The formula is stored together with the table sample. The sheet Space Control in Microsoft Office Web Components supports most MS Excel formulas. The formula usage and syntax are exactly the same as those in MS Excel. 14. How to Implement the automatic calculation formula? How to define it? How to save the defined results? Where does it exist? -- The formula of MS Excel is used for implementation, such as sum and AVG. The formula is stored together with the table sample. 15. How to extract data from other reports to generate a new report? How to define it? How to save the defined results? Where does it exist? -- A report has a property called the "fetch program", which is used to extract data from other database tables and insert it into the database table corresponding to this table. 16. How to define a Summary of reports (data from branches generates data from the General Organization )? How to save the defined results? Where does it exist? -- A report has a property called "summary program", which is used to collect data from reports of lower-level organizations to generate report data of the Organization. 17. How to implement data reporting-that is, data format conversion? How is the conversion program designed? How to save it? Where does it exist? -- Reporting data is actually a conversion of data formats. It can be easily designed using XSL. The XSL Conversion Program is saved as a text file in a specified directory. The file name corresponds to the report one by one. To generate report data, convert the report data into a text file, and save it to the user's specified location on the client machine. The full definition of a report is stored in the data. The structure of the database table is as follows:
| Serial number |
Field name |
Title |
Data Type |
Null allowed |
Keywords |
Remarks |
| 1 |
ID |
Report No. |
Int |
No |
Yes |
|
| 2 |
Title |
Report Name |
Varchar (100) |
No |
No |
|
| 3 |
Dbtable |
Corresponding database table |
Varchar (100) |
No |
No |
Name of the corresponding database table |
| 4 |
Style |
Report Style |
Text |
Yes |
No |
|
| 5 |
Style2 |
Compile Style |
Text |
Yes |
No |
Compiled Report Style |
| 6 |
Address |
Formula address |
Text |
Yes |
No |
Formula address, separated |
| 7 |
Query |
Query Fields |
Varchar (400) |
Yes |
No |
Field -- title pair, separated by | |
| 8 |
Orders |
Sorting Field |
Varchar (400) |
Yes |
No |
Sort fields, separated by | signs |
| 9 |
Program |
Summary Program |
Text |
Yes |
No |
|
| 10 |
Program2 |
Fetch Program |
Text |
Yes |
No |
|
| 11 |
Program3 |
Other programs 1 |
Text |
Yes |
No |
|
| 12 |
Program4 |
Other programs 2 |
Text |
Yes |
No |
|
| 13 |
Createdate |
Creation Time |
Smalldatetime |
Yes |
No |
|
| 14 |
Flags |
Flag |
Char (10) |
Yes |
No |
Reserved unused |
| 15 |
Status |
Status |
Int |
Yes |
No |
0: Invalid; 1: VALID |
| 16 |
DAC |
Data Verification Code |
Varchar (20) |
Yes |
No |
|
| 17 |
Appendix |
Additional instructions |
Varchar (200) |
Yes |
No |
|
Table 2: The report definition structure adds the batch processing function for the convenience of operations, allowing you to extract and summarize data in batches to simplify your operations. The entire report processing process is as follows: 1. design Database Definition 2. design Report Style 3. insert number formula 4. set query conditions 5. design summary program 6. design the fetch program 7. query reports 8. summary report 9. batch summary report 10. extract report data 11. batch extract report data 12. generate Report File Design Results Now, the coding work has been completed for more than a month. The database design interface 3 is shown, and the report design interface 4 is shown. Figure 3: Database Design Interface Figure 4: The report design interface is for trial use. The results are good and dozens of reports can be easily solved. Huang Tian is not "bitter. In this way, I used another week to design a chart design tool, and the user responded very well. The chart design page is shown in Figure 4. Figure 4: chart design page Summary This article discusses how to implement an effective web report design tool. Users who need to design a large number of reports, especially those who frequently change the report format and quantity, can use the reports conveniently. Developers can also save a lot of work. |
|
