Symptom
WARNING
|
The following resolution is intended for advanced users of
Crystal Reports. This solution is offered as a tip to our customers
but is not supported by Business Objects Customer Assurance. Ensure
you read all notes and warnings throughout this article.
|
Resolution
WARNING
|
Crystal Reports is not recommended as a tool to write to,
update or delete from a database. Database clients or applications
designed specifically to edit databases should be used instead.
However, now that CR has full SQL functionality it is possible to
write to, update and delete from databases. This article is
provided as a tip.
Before attempting to implement the steps in this article,
consult your Database Administrator (DBA). Your DBA needs to grant
specific rights to your database. Your DBA may also be able to
guide you in creating an appropriate SQL statement for your
database. The syntax provided in this article is specific to
Microsoft SQL Server.
|
CAUTION
|
The following resolution involves editing the registry. Using
the Registry Editor incorrectly can cause serious problems that may
require you to reinstall the Microsoft Windows operating system.
Use the Registry Editor at your own risk. It is strongly
recommended that you make a backup copy of the registry files
before you edit the registry. For information on how to edit the
registry key, view the 'Changing Keys And Values' online Help topic
in the Registry Editor (Regedit.exe).
|
Create a Table of Contents
1. Create a new table in your database called TableOfContents. Create three fields in this table:
" Grouper (String data type)(Ensure this field is at least as large as your longest group field value)
" Page (Number data type)
" DateTime (DateTime data type)
2. Assign rights to write to, delete, and update this table to any users of this report. The TableOfContents table will be updated as the report changes.
CAUTION
|
Use INSERT INTO, DELETE or UPDATE statements at your own risk.
Once a command object containing an INSERT INTO, DELETE or UPDATE
statement has run, the database has changed and the changes may not
be reversible.
|
3. On a grouped report (for this example the report is grouped on the ProductName field), in the 'Insert' menu, click 'Subreport'. Name this subreport 'Table Of Contents'. Connect this subreport to the new TableOfContents table and insert the TableOfContents.Grouper and TableOfContents.Page fields in the Details section. Sort this subreport by the TableOfContents.Page field.
4. Insert the 'Table of Contents' subreport in the Report Header section.
5. Right-click the gray area to the left of the Report Footer, and then click 'Insert Section Below'. There will now be 'Report Footer a' (RFa) and 'Report Footer b' (RFb).
6. In the Main report, on the 'Insert' menu, click 'Subreport'. Name this subreport 'UpdateTOC'. In the 'Database Expert' dialog box, browse to your Table of Contents data source, then double-click 'Add Command'. The 'Add Command to Report' dialog box will appear.
7. Click the 'Create' button to create a parameter. Name the parameter 'LinkReceiver' and click 'String' from the 'Value Type' drop-down box. Create another parameter named 'DateTime' with a 'DateTime' value type.
8. In the 'Add Command to Report' dialog box enter a SQL Query similar to the following:
{?LinkReceiver}
DELETE FROM "TableOfContents"
WHERE "TableOfContents"."DateTime" <> {?DateTime};
SELECT * FROM "TableOfContents"
This command object will add new GroupName and PageNumber data to the TableOfContents table and delete any existing data based on the ?DateTime parameter.
IMPORTANT |
This SQL Query (and any other SQL syntax in this article) is
written for a Microsoft SQL Server database. Consult the database
manual or Database Administrator for the correct syntax for your
data source. The command object syntax must be in the databases
accepted format or it will generate errors in Crystal
Reports.
It is strongly recommended that you populate
the TableOfContents with some data. When that table is empy, you
may experience syntax errors when running the report. When the
TableOfContents table does not contains any details then Crystal
Reports cannot give a valid answer when comparing the DateTime to
CurrentDateTime.
For assistance with creating the SQL query for your command
object, refer to your DBA. Business Objects Customer Technical
Support does not provide support for writing SQL queries, because
the syntax in use is database specific.
|
9. Insert the Grouper and the Page fields in the subreport Details section. Suppress all of the subreport sections.
10. Insert the 'UpdateTOC' subreport in the Report Footer B (RFb) section. (This is to ensure that the formula used to link the subreport is processed before the subreport is processed.)
11. Right-click the 'UpdateTOC' subreport, and then click 'Format Subreport'. Click the 'Border' tab, and then click 'None' from the 'Left', 'Right', 'Top' and 'Bottom' drop-down boxes. Click 'OK' to close the 'Format Editor' dialog box.
12. In the main report, create a formula named 'DateTime' with the following syntax:
CurrentDateTime
13. Insert this formula in the main report's Report Header section.
14. Create another formula named 'LinkMaker' with the following syntax and place it in the Group Header section of the main report:
WhilePrintingRecords;
StringVar link;
If Not InRepeatedGroupHeader Then
link := link &
"INSERT INTO TableOfContents VALUES ('" &
GroupName ({Product.Product Name}) & "'," &
ToText(PageNumber,0) & ",'" &
ToText({@DateTime}, "yyyy-MM-dd HH:mm:ss") &
"')" & chr(10);
INSERT INTO TableOfContents VALUES
('Bikes',1,'2003-04-04 12:32:01')
INSERT INTO TableOfContents VALUES
('Locks',2,'2003-04-04 12:32:01')
15. Create another formula named 'LinkPasser' with the following syntax and place it in the Report Footer A (RFa) Section of the main report:
WhilePrintingRecords;
StringVar link;
17. Click the '@DateTime' Formula field from the 'Available Fields' box and move it to the 'Field(s) to link to' box. In the 'Subreport parameter field to use' drop-down box, click the '?DateTime' parameter.
18. Repeat step 15, connecting the '@LinkPasser' Formula field to the '?LinkReceiver' parameter. Click 'OK' to close the 'Subreport Links' dialog box.
19. In the 'Field Explorer' of the main report, click 'Special Field'. Insert the 'PageNofM' field in the Page Footer.
20. Preview the report to write the Table of Contents data to the database.
21. Refresh the report again and the Table of Contents will display the group and page number values based on your report.
NOTE |
If changes are made to the report that affect the page numbering, refresh the report twice to update the TableOfContents table. If the number of pages used by the Table of Contents changes, the report must be refreshed a third time to update the Table of Contents subreport. |
Keywords
TABLE OF CONTENTS C2019321 ADD COMMAND PAGE NUMBERS CATALOGUE DIRECTORY Crystal Reports Table of Contents Database write-back , c2011950 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem