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

 

[crystalbeginner15]

I read somewhere that we can use an array as a column in cross-tab(may be I understood wrongly) so I was wondering can I create something like this
Array x = (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) in formula and put this as a column in cross-tab but I don't know how to create the formula. BTW I tried your solution for the second report its looking good but its painful to create the grid manually so I am trying to see is there any way I can use cross-tab.

Thank you.

 

[lbass]

If you magnify the view in design mode and use the guidelines and ruler, you can make an accurate grid. Also try using the feature->format line->extend line to bottom of section when printing.

If you are set upon using a crosstab, then you could create a union all statement by using "Add command" as your datasource. This assumes that the Class1 - 15 fields show volumes.

SELECT
Table.`date`,Table.`Class1`, 1 as Class
FROM
`Table` Table
UNION ALL
Table.`date`,Table.`Class2`, 2 as Class
FROM
`Table` Table
UNION ALL
Table.`date`,Table.`Class3`, 3 as Class
FROM
`Table` Table
//etc.

This would put all the class fields in one column. In the crosstab expert, you could then insert a column on {Command.Class}, use {Command.date} as your row, and sum of{Command.Class1} (since this now contains all volume data as your summary.

-LB

 

[crystalbeginner15]

Thank you.
I haven't implemented your approach yet(cross-tab)
I have a general question can we see the query generated by cross-tab?
2)I have a main report and subreport and also linked subreport with the main report through parameters but when I open the report it promts me to pass the parameters for sub-report too I thought when I pass the parameters for main report it should generate the subreport based on the parameters.
I think I am missing something somewhere .Guide me please.

 

[lbass]

My post of 6 Sep 05 16:29 described how to go about this. If they are linked properly, each parameter will appear only once.

-LB

 

[crystalbeginner15]

Thank you.I did and it was working fine and now when I started the CR and open the report it asked twice the parameters but now when I checked the link the parameters were not there in subreport
I did add them again and it looks good.

 

[crystalbeginner15]

This is regard to my post of 6 Sep 05 10:18 .I have another subreport like this:

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


Sun Mon Tue Wed Thr Fri Sat
Daily Total 7 Day Sum
# of Days MADT
Daily Average

I need to calculate the 7 day sum.I have running totals for Mon.....Sun in subreport 1 . How can I use those totals to get total 7 day sum. how do we used shared variable for runnint totals.


Thank you

 

[lbass]

You can create another running total for the 7-day sum, only this time, don't use the evaluation formula. Instead select evaluate for each record. If you don't specify the day of the week, the running total will add for all days.

Unless you want to work with the 7-day sum in a calculation in the main report, you don't need to use a shared variable.

-LB

 

[crystalbeginner15]

Actually I just came to know that 7 day sum is actually sum of daily average for all 7 days in the first subreport so now I think I have to use shared variable.

I have 7 formulae in the first report to calculate the average for all the days . So I am guessing I have to declare in each of the formula the the average to be shared varible but how should I access those variable in my second subrepot to calcultae the 7 day sum.


Thank you.

 

[lbass]

I am truly lost here, as several approaches have been suggested throughout this thread, and you have not indicated which approaches you took, so this is just a guess. If you have formulas in the report footer of your first subreport that correctly calculate the averages, then you can add a variable to that formula like:

shared numbervar aveMon := <your calculation>;

Then in the second subreport (which must be located in a section below the one in which the first subreport is located), you can reference the shared variables, but using:

whileprintingrecords;
shared numbervar aveMon;
shared numbervar aveTue; //etc.
shared numbervar aveSun;

aveMon+aveTues+aveWed+ //etc.

-LB

 

[crystalbeginner15]

Thank you it works. Sorry for the confusion. I am talking about the report in my first posting.

 

[crystalbeginner15]

with your(lbass) help I am closer to completion of my first report.

I want to use the same template for all my reports .How can I create a template?

Thank you.
Raythalli

 

[lbass]

I would just save a copy of the report under a different name, perhaps identifying it with "template". Then each time you want to use the template, open it and save it under the new report name and then make the necessary changes to it.

I'm not aware of other ways of doing this, although someone else might. There is also a repository for those CR 11 users who have CE or CR Server where you can save formulas to reuse, although I have never used that feature.

-LB

 

[crystalbeginner15]

Now I am done with my first report. Now the next challenging thing is to put this report on web .I have a web site which will have tabs for all the reports when I click one of the tab the page should be directed to the crystal report . like this when I click on run report it should open the report.

Report Name Report Summary
Report 1 Report Summary Details [ Run Report ]
Report 2 Report Summary Details [ Run Report ]
Report 3 Report Summary Details [ Run Report ]

Appreciate your help.

 

[lbass]

You should start a new thread for this.

-LB

 

[crystalbeginner15]

Ok.

 

[crystalbeginner15]

This is regard to my report on posting 6 Sep 05 10:18 .
When the report doesn't fit in one page I want always a line on the top of all page ,similarly on the bottom of the page also

I am getting on the bottom because I have put a line on the page footer.

I have put a title and underlined in the report header
something like this

My First Report
_____________________

 

[lbass]

Put the line and the title in a page header section. If you are already using the page header, insert another page header section and place these there. In design mode, you can click on the gray area to the left of the report canvas and drag page header_b into the page header_a position to make it the topmost section.

-LB

 

[crystalbeginner15]

Yeah I did it looks good but if a cross-tab report doesn't fit into one page if i put the line in Page header i get the cross-tab in next page and then the line. How should I fix this issue.

 

[lbass]

Please start a new thread when starting new topics.

It sounds like you have the crosstab in the report header. You can either move it to the report footer, which will give you a page header and footer on each page, or if you need it to appear first because you are using other report sections, then create a formula that is true for all records, as in:

whilereadingrecords;
1

Insert a group on this formula and then place the crosstab in the group header. Then suppress the report header. You will now have a page header above and a page footer below each page of the crosstab.

-LB

 

[crystalbeginner15]

Where do i See "Add command" to write a sql query.
when I say create new connection I don't see it in Database Expert