Cross -tab subreport 1

[crystalbeginner15]

Hi,
I am new to Crystal Reports.I am working on a report. I was able to create the main report using cross tab but I am not sure how to create the subreport(may be a cross tab). Some help will be appreciated. Here are the details - We are collecting volume data for all 30 days for 24 hours each day. I got the report for each hour every day and the total also using cross tab.
but for the subreport i need to calculate the the number of Sudays,Mondays........Saturday and the total and average volume for each week day.

Date Day Beginning Hour Total
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
12/12 M
12/13 T
12/14 W
12/15 R
12/16 F
12/17 S
12/18 S
12/19 M
12/20 T
12/21 W
12/22 R
12/23 F
Total

This is the subreport -
Sun Mon Tue Wed Thr Fri Sat
Daily Total
# of Days
Daily Average

 

[lbass]

What version of CR are you using?

-LB

 

[crystalbeginner15]

CR 11

 

[lbass]

Because you want an average of a summary (daily total), I think you should use a manual crosstab. Create seven running totals like {#SunVol}, one for each day of the week where you choose sum of {table.vol}, evaluate using a formula:

dayofweek({table.date}) = 1//for Sun, 2 for Mon, etc.

Reset never.

Then create a second set of seven running totals where you choose distinctcount of {table.date}, evaluate using a formula:

dayofweek({table.date}) = 1//for Sun, 2 for Mon, etc.

Reset never. Then create seven formulas like:

//{@Sunave}:
{#SunVol}/{#SunDays}

Place these in the report footer.

-LB

 

[crystalbeginner15]

Is manual crosstab different from the other cross-tab. If Yes,Can you please tell me how to create one.
Thank you.

 

[lbass]

My previous thread described how to do this. I only left out that you should suppress the detail section. You just position each set of the running totals horizontally in the subreport report footer and add the row of formulas that show the average. Add text boxes to show the column labels.

Also, be sure that the subreport has the same selection criteria as the main report. If you only have the inserted crosstab in the main report (no other data), you could create the manual crosstab in the main report.

-LB

 

[crystalbeginner15]

Thank you.
I want to generate the subreport using the parameters passed for main report. How do I do it? When I tried to create a subreport there was a tab "link" on the screen when I clicked on it should I select the parameter fields on the report fields and will it automatically create the subreport based on the parameters passed for main report.
Help me.

 

[lbass]

I'm assuming that you want the subreport in the report footer of the main report. Create the same parameters in the subreport as in the main report, add them to your subreport record selection criteria, and then link the parameters to each other in the linking screen. To do this, you would use the dropdown to select {?parm} instead of {?pm-?parm} for the subreport end of the link. I can't remember how the screen in laid out in XI, but in 8.0, you would use the dropdown in the lower left corner to do this.

-LB

 

[crystalbeginner15]

When I add a subreport with the same parameters as main report and link them in the report footer of the main report then when I preview the main report should the subreport show on the same page ? I want it to show on the same page as main report but I see it on a different page.
Guide me.

Thank you.

 

[lbass]

Can we assume that your inserted crosstab is in the report header and that the subreport is in the report footer? You should make sure you have suppressed the detail section. For the subreport, which should only be four rows tall (headings plus the three summary rows), make sure you have all unused subreport sections suppressed. If there is room, the subreport should not move to the next page.

-LB

 

[crystalbeginner15]

I got it.
Thank you

 

[crystalbeginner15]

If I need a report with grid what should I use other than
cross tab. I have multiple columns so I am guessing I can't use cross-tab . here is the other sample report
the columns are 1-15

Day\Axle Class 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Total
01/01/05 (S) 243 123 234 464 654 987 132 243 123 45 654 876 456 987 132 1235
01/02/05 (S) 132 798 654 45 45 654 876 456 876 845 132 798 654 45 345 3400
01/03/05 (M) 654 987 132 243 132 798 654 45 68 4 654 987 132 243 86 1891
01/04/05 (T) 243 123 234 464 654 987 132 243 123 45 654 876 456 987 132 1235
01/05/05 (W) 987 243 123 5 245 23 454 654 987 132 798 654 45 243 123 1758
01/06/05 (R) 654 987 132 243 132 798 654 45 68 4 654 987 132 243 86 1891
01/07/05 (F) 234 464 654 987 654 987 132 243 123 654 987 132 243 798 123 1674
01/08/05 (S) 243 123 132 798 123 234 464 654 987 987 243 123 5 74 68 1644
01/09/05 (S) 464 654 987 132 654 987 132 243 123 234 464 654 987 456 987 1255
01/10/05 (M) 654 987 987 243 987 243 123 5 243 243 123 132 798 132 987 1688
01/11/05 (T) 53 243 243 123 243 123 132 798 4 565 243 132 798 654 45 1255
01/12/05 (W) 654 987 132 243 132 798 654 45 68 74 654 987 132 243 86 1674
01/13/05 (R) 243 123 234 464 234 464 654 987 132 464 654 987 132 876 456 1644
01/14/05 (F) 5 243 243 123 243 123 132 798 4 565 243 132 798 654 45 1255
01/15/05 (S) 987 132 464 654 464 654 987 132 243 123 798 123 987 132 243 1688
01/16/05 (S) 234 464 654 987 654 987 132 243 123 654 987 132 243 798 123 1674
01/17/05 (M) 132 798 654 45 45 654 876 456 876 845 132 798 654 45 345 3400
01/18/05 (T) 654 987 132 243 132 798 654 45 68 4 654 987 132 243 86 1891
01/19/05 (W) 243 123 234 464 654 987 132 243 123 45 654 876 456 987 132 1235
01/20/05 (R) 987 243 123 5 245 23 454 654 987 132 798 654 45 243 123 1758
01/21/05 (F) 654 987 132 243 132 798 654 45 68 4 654 987 132 243 86 1891
01/22/05 (S) 234 464 654 987 654 987 132 243 123 654 987 132 243 798 123 1674
01/23/05 (S) 243 123 132 798 123 234 464 654 987 987 243 123 5 74 68 1644
01/24/05 (M) 464 654 987 132 654 987 132 243 123 234 464 654 987 456 987 1255
01/25/05 (T) 654 987 987 243 987 243 123 5 243 243 123 132 798 132 987 1688
01/26/05 (W) 243 123 234 464 654 987 132 243 123 45 654 876 456 987 132 1235
01/27/05 (R) 53 243 243 123 243 123 132 798 4 565 243 132 798 654 45 1255
01/28/05 (F) 654 987 132 243 132 798 654 45 68 74 654 987 132 243 86 1674
01/29/05 (S) 243 123 234 464 234 464 654 987 132 464 654 987 132 876 456 1644
01/30/05 (S) 464 654 987 132 654 987 132 243 123 234 464 654 987 456 987 1255
01/31/05 (M) 654 987 987 243 987 243 123 5 243 243 123 132 798 132 987 1688
Totals 2341 32423 234 23423 32456 9796 5769 0897 6854 4768 7805 5433 5688 9543 87443 256451
Distribution Summary 5.0% 5.0% 0.1% 5.0% 4.5% 10.0% 6.0% 5.0% 5.0% 0.1% 5.0% 4.5% 10.0% 6.0% 5.5%

 

[lbass]

I'm not following your question. Why wouldn't you use an inserted crosstab with grid for this?

If you are asking about creating a grid for a manual crosstab, you can accomplish that by inserting boxes and lines as needed.

-LB

 

[crystalbeginner15]

To create the above sample report with cross-tab I have to put 15 fields in the column section of a cross-tab which I think I can't do(multiple columns) correct me if I am wrong. Basically the intersection of the row and column in the above report is the sum of volume_number for class1........clas15 for that particular day.

 

[lbass]

You could just lay the classes out in the detail section and then add the boxes and lines, assuming the classes fields hold the volume for the day.

Or, you could create a union all statement that combines all classes into one column so that you could use an inserted crosstab.

-LB

 

[crystalbeginner15]

1)for each day I have 96 records but I want the sum of all the 96 records in one row but when I create a standard report i get 96 records for the same day how can I sum the data to show the sum in one row.
2)for the other report for same data I have to breakdown each day's data per lane in each directionsomething like this -
Day\Axle Class 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Total
01/01/05 (S)
Lane 1 – N 132 798 654 45 45 654 876 456 876 845 132 798 654 45 345 3400
Lane 2 – N 654 987 132 243 132 798 654 45 68 4 654 987 132 243 86 1891
Lane 1 – S 243 123 234 464 654 987 132 243 123 45 654 876 456 987 132 1235
Lane 2 – S 987 243 123 5 245 23 454 654 987 132 798 654 45 243 123 1758
01/02/05 (S)
Lane 1 – N 243 123 132 798 123 234 464 654 987 987 243 123 5 74 68 1644
Lane 2 – N 464 654 987 132 654 987 132 243 123 234 464 654 987 456 987 1255
Lane 1 – S 654 987 987 243 987 243 123 5 243 243 123 132 798 132 987 1688
Lane 2 – S 53 243 243 123 243 123 132 798 4 565 243 132 798 654 45 1255
01/03/05 (M) Some Ideas will be appreciated. With your help I was able to create the subreports.

 

[crystalbeginner15]

I forgot to ask that what do you mean by union of all statements for cross-tab
Thank you

 

[lbass]

Insert a group on date and then suppress, insert summaries on your detail fields, and then suppress the detail sections. In the case of the lanes, insert a second group on lanes, and insert summaries at that group level, too.

A union all is probably unnecessary here. It would involve using add command as your datasource, and I think you're better off not starting from scratch.

-LB

 

[crystalbeginner15]

Can I create an array of 15 classes and pass it as column in cross tab.If yes how should I create an array .

Thank you

 

[lbass]

I don't think so. Why would you want to?

-LB