Search code examples
crystal-reportsformatting

Crystal Reports report formatting across different pages

I have a question about Crystal Report. I have an existing report, with its data taken from a stored procedure. The data may look like this:

Division|Group Level 1|Group Level 2      |Group Level 3|Value
--------+-------------+-------------------+-------------+--------
IT      |Assets       |Current Asset      |Cash         |100
CORP    |Assets       |Current Asset      |Cash         |200
IT      |Assets       |Current Asset      |Receivables  |300
CORP    |Assets       |Current Asset      |Receivables  |400
IT      |Assets       |Fixed Asset        |Land         |500
CORP    |Assets       |Fixed Asset        |Land         |600
IT      |Liabilities  |Current Liabilities|Wages        |100
CORP    |Liabilities  |Current Liabilities|Wages        |200
IT      |Liabilities  |Current Liabilities|Taxes        |100
CORP    |Liabilities  |Current Liabilities|Taxes        |100
IT      |Liabilities  |Long-Term          |Bonds        |300
CORP    |Liabilities  |Long-Term          |Bonds        |400

The actual data may have many more divisions (not just two). In the new report, I want the report to look like the following:

                          |  IT     | CORP
Current Asset
Cash                      | 100     | 200
Receivables               | 300     | 400
Total Current Asset       | 400     | 600

Fixed Asset
Land                      | 500     | 600
Total Fixed Asset         | 500     | 600

Total Assets              | 900     | 1200

Current Liabilities       
Wages                     | 100     | 200
Taxes                     | 100     | 100
Total Current Liabilities | 200     | 300

Long-Term
Bonds                     | 300     | 400
Total Long-Term           | 300     | 400

Total Liabilities         | 500     | 700

So, the report will be expanded to the right, according to the number of divisions. Assume that one page can hold up to 10 divisions. Then if there 15 divisions, then the first page will show division 1 to 10, the second page will show 11 to 15. The items shown on the first and second page will be the same, just for different divisions. The number of divisions are flexible. And also the items are quite a lot (there can be a lot of current assets, liabilities, etc).

For now, I tried to do some formatting in the stored procedure, so the returned data will be like:

Page No | Group Level 1 | Group Level 2 | Group Level 3 | Div 1 | Value 1 | Div 2 | Value 2
--------+---------------+---------------+---------------+-------+---------+-------+--------
1       | Assets        | Current Asset | Cash          | IT    | 100     | CORP  | 200
1       | Assets        | Current Asset | Receivables   | IT    | 300     | CORP  | 400

and so on. For division 11 to 15, I set the page no to be 2. And then in the Crystal Report, I will group by : Page No, Group Level 1, Group Level 2, and Group Level 3. So the Crystal report will show everything on a page based on the page number.

The problem is that:
- If there are a lot of items, then it may not fit in one page as well. For example, assume one page it can fit up to 30 lines, then if I have 40 lines, the 10 lines will be shown in the second page. But I want the second page to still show the first 30 items for division 11-15, and the third page will show the last 10 lines for division 1-10, and the fourth page will show the last 10 lines for division 11-15.
- The running total in Crystal Report will be reset on each change of the group. Let say I have 40 assets. Then after the 40 assets, which is on the third and the fourth page, it should show the total of the assets. How do I calculate the running total so that it shows up correctly? (Considering I can not just sum it up directly, as third and fourth page should show the total of different divisions).

Is there any solution for this problem or a better approach to format the data?

Thanks.

Solution

  • In general, this is how I finally solve the problem:

    1. Define how many divisions (NumColumn) to be displayed in a single page.
2. Create a table to store the mapping of the division. The table has column that stores PageOffset and ColumnNo. PageOffset stores the number of page to be added when displaying a particular division. For example, if there are 15 divisions, and a page can only accommodate 10 divisions, then the first 10 divisions will have `PageOffset = 0` and the last 5 divisions will have `PageOffset = 1`. The ColumnNo is the position of a division in the report column. So first division will have value of 1, second division will have value of 2 and so on. 11th division will have the value of 1, etc.
3. Create a result table. For each column in the report, we have 2 database field, value and total.
4. Loop for each record, sorted by the group.
    Select the position of the division from the table in step 2. If `ColumnNo = 1` insert a row in the result table. E.g. for division 1 and 11 it will create rows in result table.
    After that update the row data, based on the position of the division. So for division 1-10 will update the row created by division 1, division 11-15 will update row created by division 11.
5. Count the number of items in each group and store it into a table.
6. Define how many rows (RowsInPage) to be displayed in a single page.
7. Set PageAdd = 0 and RowsLeft = RowsInPage
8. Loop for each group
    Retrieve the number of rows needed for this group
    Add the page number of each row with PageAdd
    Decrement RowsLeft
    If RowsLeft = 0
        Increment PageAdd
        Set RowsLeft = RowsInPage
    End If
9. Loop for each group
    Loop from 1 to NumColumn
        Update the total field for each division. This total field value will be put in the report as the running group total for that particular division.