How to implement group/subreport on this? 1

[excalibr]

Hi all,

We've got a pretty simple table which we want to display in Crystal Reports ...

Year Per Bus Site Dataset Amount
---- --- -------- -------- -------- ----------
2003 10 QXR ANN a 647.50
2003 10 QXR ANN b 0.00
2003 10 QXR ANN b 75.00
2003 10 QXR ANN c 20.70
2003 10 QXR ANN c 397.16
2003 10 QXR ATH a 20.70
2003 10 QXR ATH b 0.00
2003 10 QXR ATH b 75.00
2003 10 QXR ATH c 20.70
2003 10 QXR ATH c 755.56

As a beginner, I'd really appreciate some points as to how we can address this ... We'll have Parameter Fields to filter on Year and Period, then Group by Bus and Site. My main problem at the moment is to structure the groups and their selection criteria. The desired report is something like this:

Thanks in advance, Excalibur

---------------------------------------------------------------
page 1.

-------------------
Bus: QXR Site: ANN <-------- appears every page
-------------------

-------------------------
|DataSet Line Amount |
-------------------------
| A 1 647.50 |
-------------------------

-------------------------
|DataSet Line Amount |
-------------------------
| B 1 0.00 |
| B 1 75.00 |
-------------------------

-------------------------
|DataSet Line Amount |
-------------------------
| C 1 20.70 |
| C 1 397.16 |
-------------------------

---------------------------------------------------------------

page 2

-------------------
Bus: QXR Site: ATH
-------------------

-------------------------
|DataSet Line Amount |
-------------------------
| A 1 20.70 |
-------------------------

-------------------------
|DataSet Line Amount |
-------------------------
| B 1 0.00 |
| B 1 75.00 |
-------------------------

-------------------------
|DataSet Line Amount |
-------------------------
| C 1 755.56 |
-------------------------

---------------------------------------------------------------

 

[dgillz]

Group by Site then by Dataset within Site.

You do know how to insert a group?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[FVTrainer]

I'll briefly hit on the steps for setting up the groups, using the menu choice rather than the icons. I'll assume you already have your data connection and you've added the dataset, line, and amount fields to the details section of the report.

1) Choose Insert | Group. Click on the drop-down box and scroll until you see the Bus field in the list. Select the Bus field. It looks like you want ascending order on the Bus group, so leave the default setting. Click &quot;OK&quot;.

2) Choose Insert | Group again and this time select Site from the drop-down list. Click &quot;OK&quot;

3) To get the page to break by Bus and Site, we need to set up use the Section Expert. Position the mouse cursor over the words &quot;Group Header #2&quot; (or it may show as GH2) in the gray area to the left of the white report design area and right-click the mouse. From the shortcut menu, select &quot;Section Expert&quot; (or &quot;Format Section&quot; if using CR 8.5 or lower).

4) In the section Expert, make sure the Group Header 2 is highlighted and then click on the X + 2 button located to the right of &quot;New Page After&quot; checkbox. In the formula editor that appears, type the following:

Not OnLastRecord

5) Click the save and close icon.

6) I don't think you'll need to see Group Header 1 on your report. So, while you are in the Section Expert, click on the item &quot;Group Header #1&quot;. Then Click in the box next to the &quot;Suppress&quot; option. If you decide you want to use this Group Header later, just reverse this step.

That should get you in the ballpark as far as grouping goes.

 

[excalibr]

Thanks all on the tips re: inserting groups, however I might have over simplified my problem ... the individual datasets should appear as separate boxes, each with separate columns, eg. A: Dataset, Line, Amount, Coverage ..., B: Dataset Line, Amount, Speed ... etc.

I heard there's this thing called subreports. Conceptually if I can create a subreport for each of the dataset, then I'd be ok, but how do I link the subreport selection to the primary report's selection? eg. if the primary report is currently on Year=2003, Period=10, Bus=QXR, Site=ANN, how do I access these values to make sure the subreport's data corresponds?

Thanks again guys, Excalibur

 

[excalibr]

Hi all,

Just found my Crystal Reports Users Guide, and have worked out how to do this (RTFM'ed).

Thanks! Excalibur

 

[FVTrainer]

At the risk of annoying with perhaps info you don't need:

I still don't see why you need subreports in this case. Subreports are to be avoided, if possible, primarily because of the performance hit you take when using them.

In your case, if you can determine that when you are printing the B data, you'll always have a column called speed, but when it is A data, the 4th column is going to be Coverage, well, there's a couple of different ways to address that that would be much faster, in terms of performance, than subreports.

Here's one approach. I'll use just the A and B data sets, but you'll be able to extrapolate from there. I'll assume, too, that the data

A 1 647.50 |

is in the details section of the report (it wouldn't matter if it isn't; the principle is the same).

First, split your Details section into two sections (Put your cursor over the word &quot;Details&quot; in the gray area of the designer and right-click. From the shortcut menu select Insert Section Below). In Section A, place all of the data fields that go with data set A. In section B do the same using the fields that go with data set B. Open the Section Expert and select the Details A section. Click on the X + 2 formula button (aka, the conditional formatting button) to the right of the &quot;Suppress (no drill down)&quot; option. In the formula editor, enter the following formula:

Dataset <> &quot;A&quot;

(of course &quot;A&quot; is whatever A represents in your first example). Save and close the editor. Select the Details B section and click on the conditional formatting button and enter the following:

Dataset <> &quot;B&quot;

Save and Close the editor and click OK to close the section expert.

Now you need the column headings to match up with your data. The process is the same as with the details section. Split your group header into two sections and put the A headings in section A of the group header the B headings in section B. Then do the same conditional suppression you did for the corresponding details sections.

If you chose the subreport approach, just don't fall into the trap of using subreports everytime you encounter something difficult. Creative formatting is very frequently the answer, and always faster the subreports.