Help writing a SQL command to for CR XI

[TomBoardman]

My CR report is intended to show water delivery activity for a given week but I also need to show the YTD deliveries, by month for the account. I have an account group in the report. Since CR is only going to step through my tables for a given week, I think I need to derive the YTD delivery info with a SQL command. I have been trying to write one using INNER JOIN or UNION but no success.

My report has 2 tables. Table 1 contains all account #s. Table 2 contains an account #, delivery date, and volume. Not all accounts may exist in table 2. I'm no SQL expert and this seems like a simple problem for someone with SQL savy ;-)

Thanks for any help

Tom

 

[lbass]

While you could use a command, I don't see a particular need for one. Use a record selection formula like:

{table2.deldate} in yeartodate

This should work as long as every account has some activity during the current year. Then add a group#1 on {table2.deldate} and select "print on change of month" and then insert a second group on the same field "on change of week". Then insert summaries on your volume field at each group level, drag the results into the group header and then suppress the detail section. Suppress GH#2 in the section expert using a formula like:

{table2.deldate} < currentdate-dayofweek(currentdate)+1

...assuming that the weekly amount you want to show is the for the current week. Adjust as necessary.

-LB

 

[TomBoardman]

Thanks for the quick response, LBass. I think I understand your suggestion and will give it a try later today. The reason I was thinking of using a command was I assumed it would be faster, ie it seems a SQL Sum function would be quicker than having CR build each month's total by stepping through each record. My dataset consists of about 600 accounts and about 30,000 rows of delivery data for all accounts combined by the end of the year.What do you think?


Do you still think I should go with your original suggestion? If so, can I change the yeartodate function to work with a Oct-Sep fiscal year?

 

[lbass]

That doesn't seem so large. I would still try this. I just realized you probably want your group #1 to be on account. As for the record selection, you can just set it up with a fiscal year parameter. If you your fiscal year is defined by the ending year, use a record selection formula like:

{table2.deldate} in date({?fiscal year}-1,10,1) to date({?fiscal year}),9,30)

-LB

 

[TomBoardman]

Thanks once again LBass. If you have the patience, I have another question regarding the groupheader/detail/groupfooter layout. As you noticed my group#1 will be on the accounts table(Table 1). But my report will have essentially 3 sections in this order.
1 A list of water purchases or other transactions for the report week from Table 2 followed by summary totals.
2)The YTD summary by month derived from Table 3
3)Delivery activity during the report week derived from Table 4 followed by summary totals.

For Section1 my thought is to create a detail section under the first detail section and copy the summary fields into it. If so, how can I keep the summary fields from printing more than once?

For Section 2, I am thinking of creating a detail section under sect#1 summary, with the data derived like you suggested in the previous post.

For Section 3, I suppose I would make 2 more detail sections and set them up similar to Sect #1

Any thoughts for a CR dork?

 

[lbass]

You are now talking about two additional tables--where did these come from? How about showing some sample results, indicating the layout with related time periods?

-LB

 

[TomBoardman]

Sorry about the surprise. I guess I boiled my report down too much at first. Below is a schematic layout for the report. The report is to be printed for any account in Table 1 that has activity in either Section A or C for the subject week. The table in brackets is the source for the fields in that section

Account # [Table 1] Group #1
Name, Address

-Section A (Weekly listing of Transactions) [Table 2]
Date Purchase_amount Transfer Row_Total
------------------------------------------
Sum(Row_Total

-Section B (YTD Delivery Summary) [Table 3]
Oct monthly_total
Nov "
etc "
Sep _____
sum(monthly_total)

-Section C (Delvery Detail for week) [Table 4]
Date Meter# Volume
----------------------
sum(Volume)

To give you a sense for the length of a typical report, Section A could contain 0 to 40 rows, and Section C could have 0-100 rows in any given week.

 

[lbass]

That's a good description of the display, but what are the fields in Tables 3 and 4? How do they link to the other tables? It is unclear for example whether table3 contains fields that are already summaries or whether you are working with details contributing to the dispalyed summaries.

It looks likely that subreports will make the most sense. I think you could insert a subreport in the account group footer for the Table 3 summary, link it on account, and use a crosstab for speedy results there. I'm not sure how Table 4 relates to the weekly info in the first section, so I'd need more info before recommending an approach.

-LB

 

[TomBoardman]

I need to correct myself a bit. Section B is actually derived from the same Table 4 used Section C - not Table 3, so forget about table 3.

Tables 2 and 4 are linked by account to table 1. In order to derive the monthly totals in Section 2, I was envisioning CR stepping thru each record in Table 4 for the given account and building a summary total for eaach month (or use a command statement to summarize if it now makes sense after all I've dumped on you).

If a subreport is the way to go, is it possible to keep section A & B on the main report? Also I assume CR can print the main and sub sequentially. I really appreciate your time on this LBass!

Tom

 

[lbass]

The problem is your "or" requirement. A union might be desirable in this case. I can't tell what Table2 relates to--can you give it a descriptive name? Table4 seems to be about deliveries. Please list the fields you need on the report from each table, along with their datatype.

-LB

 

[TomBoardman]

Table 1
Account single
Name string
Address "
City "
State "
Zip integer

Table 2 holds the transaction data for water purchases, transfers, or allocations that the account has made or received from the water district itself or from other accounts during the fiscal year. An account's water balance in Table 2 is checked for overdraft before water is delivered to the account's delivery point. All deliveries are recorded in Table 4.

Table 2 (I've ommitted superfluous fields)
ID autoincrement integer
Account single
TransDate date/time
Transfer single
Allocated single
Purchase single

Table 4
ID autoincr. integer
Account single
Lateral integer
Turnout string
MeterReading single
DelDate date/time
Volume single

 

[lbass]

I don't know what you mean by "single". Anyway, on second thought, because the fields bear so little relation to each other, it might be better to start by using Table1 as your main report, with a group on Account # and with no record selection criteria. Add a subreport in a group header section that uses Table2 and limit the records to the week you are concerned with using a parameter.

Add a second subreport that uses Table 4 to the group footer (or another group header section), and suppress the detail section in the main report. In this subreport, use the record selection formula we mentioned earlier, and also create a date parameter with the same name as the one in the first subreport.

In the main report, create the same-named date parameter, and link the two subreports on the acct and on the date parameter (using the lower left dropdown box to select the parameter).

In the second subreport, try my earlier suggestion of grouping by month and then also by week, but use conditional suppression on the week groups using the date parameter (don't use it in the record selection formula), but instead use it in the suppression formula (this assumes that you are reporting data through the currentdate, otherwise you could use it in your record selection formula to delimit the end of the date range). It would help if you explained what the particular week that you are reporting on is--the most recent full week or?--so that we could determine the best type of date parameter to use.

Then the subreports should be formatted to suppress if blank, and the main report group sections should also be formatted to suppress if blank. If you put the account info (wtih table1) in your first subreport and use an equal join within the subreport between the two tables, then if there is no purchase info, the sub should suppress. Don't put any actual data in the main report other than inserting the group on account.

-LB

 

[TomBoardman]

Regarding subreport 2, I'm still a bit unclear how to get the monthly values to total next to the month using the 'print on change of month'. Will the month totals need to be formula fields that filter with a date range?

Also, I understand that the main report will not print if both subreports are blank, but since the YTD info is contained in subreport 2 it will never be blank once one of the months in the YTD totals, Section B becomes positive. I want to skip the account statement if both Sections A & C are blank. Any suggestions?

The week period will have a date range, but I'm not too worried about that since I will update the record selection formula from within VB.NET. With the report embedded in VB.NET, I can change most any of the report properties through code, so I don't have to deal with parameters, or at least CR's GUI for parameter prompts.

 

[lbass]

In sub 2, insert summaries on the fields in the detail section at both the month and the week levels, and then drag the results into the group headers for each and suppress the group footers and detail section. You then have to apply the suppression formula in the section expert->group header(week group) so that only the results for the specified week show, by using a formula like this:

not({table.date}in {?daterange})//where {?daterange} specifies the week of interest

To suppress the monthly summaries if there are no records in the week of interest, you can create a formula like {@inweek}:

if {table.date} in {?daterange} then 1

Then use a conditional formula in the group header for month supression area like:

sum({@inweek},{table.date},"monthly") = 0

-LB

 

[TomBoardman]

I think I understand. I plan on implementing your suggestions later today. I really appreciate ALL of your time with my questions, LBass.

I'll let you know how it works out

Tom

 

[TomBoardman]

OK, I have decided to go with 2 subreports in my main report. The main is grouped by account. I set up a shared boolean var for each sub that is true if records appear in the sub. If false, the section with the sub is suppressed. I am using a technique described in a book by Brian Bischof CR for VB.NET. The problem I am having is getting the proper boolean value in the main from the sub. If both boolean vars are false, I want to skip printing the main for the account. I understand that shared vars can only be used in the WhileRecordsPrinting pass. If so, it seems that by the time the shared boolean in the sub is set to false (while printing), the main has already been printed to the point of the section with the sub. Am I missing something?

 

[lbass]

I don't think you should have to use shared variables if you make sure that all sections within the subreports are suppressed if no detail records appear (suppress headers with a formula like the following:

isnull({table.recurringfield})

...and if you format the subreports to suppress if blank (format subreport->suppress blank subreport) and if you also format the main report sections containing the subreports to "suppress blank sections". This assumes that you are displaying no fields in the main report.

-LB