How to get Group Sum Summaries to a Variable 1

[archerks]

Using Crystal Reports 9 - SQL 2000 Database...
I have several hundred "accounts" which contain a date, account# and amount. I have used the Select statement to break these accounts into groups as follows.
//{@MakeAcct}
Select {Account}
Case "1001", "1005", "1006":
"Supplies"
Case "1002", "2003":
"Salary"

40 different account groups are created using this Select formula. By using the Crystal Reports Insert - Group command with the formula @MakeAcct I end up with a report that has all the correct accounts from the created groups. In the detail section there is one field named famount. I use a summary to place a Total of the famount for each group and a Grand Total at the report footer. The Select Expert uses a date range for record selection. This report will be used as a subreport.

What I need to do is get/create a Shared Variable for each group Total (40 shared variables - 1 for each group total) that can be used on the Main Report for calculations (an income statement). The summary total does not work for this as it has the same name for all the groups.

Thanks in advance
Dave

 

[lbass]

Please see my solution in thread767-1010507.

-LB

 

[archerks]

Thanks for the reply LB..

Although I haven't had a chance to try your solution, I'm not sure if it is what I need. Will try and create it later today.

I need to get each group total from the subreport, not the grand total of all groups to my main report by the use of a variable. This would be a total of 40 numbervar's, one for each group total.

Dave

 

[lbass]

I think you should describe your main report structure and tell us where the subreport is located. If you need the shared variable for calculations with the corresponding group in the main report, you only need one shared variable, with the subreport linked to the main report on the group field. It would also help to know what you calculations you want to do in the main report and at in one report section.

-LB

 

[archerks]

Sorry if I am not making my problem clear..
My main report is basicaly a blank page. On the main report I am using shared variables that are created from 160 subreports that are placed in the Page Header. The reason for 160 subreports is because there are 40 different groups with 4 different date ranges. So basicaly each subreport is creating 1 shared variable to be used on the main report.

These shared variables are placed on the main report page header. They are placed in a column like format (4 columns - 1 for each date range). I can place these shared variables in what ever order I want and calculate totals. There is no grouping - data retreval from the SQL database on main report. It is just a place holder for the subreports shared variables.

The reason I am now using only one group per subreport is because that is the only way I know how to get a total for the group to the main report with a shared variable. If I put two or more groups on the supreport as described in my opening question, then I don't know of a way to get a shared variable total for each group only the grand total for all groups.

By being able to get shared variables of each group total I could cut my subreports from having to run 160 reports to 4 reports.

Dave

 

[lbass]

My guess is that you are overcomplicating your report. I'm not sure what calculatons you need to make, but I'm not convinced you need any subreports. You could, for example, insert a crosstab and use {@MakeAcct} as a row, and use the date field as a column and group the date using group options. Or perhaps a manual crosstab would be needed, but still there would be no need for subreports.

If you are trying to do an income statement, with all income details followed by all expense details and with four date groups represented horizontally, then you could use the income accounts on the main report, and add a subreport to a footer section with the same format.

To collect data into the correct date column, you would create formulas like:
//{@col1}:
if {table.date} in Calendar1stQtr then {table.amt}
//or define the date period

//{@col2}:
if {table.date} in Calendar2ndQtr then {table.amt}

Then you would right click on these summaries and insert sums on them at the group level and then suppress the details section.

In the subreport, you could create a separate shared variable for each formula, as in:

whileprintingrecords;
shared numbervar col1 := sum({@col1});

Then reference these in the main report for net profit or whatever your further calculations are.

-LB

 

[archerks]

Thanks for the info on a "different way" of doing this, but I need the report to match our parent company's report cosmetic wise and the free-placement of the shared variables allow this.

The way I'm doing this now with the 160 subreports works, but is a hard to make changes to. The report takes about 45 seconds to run.

One problem I run into is that the database has several hundred accounts all as a numeric format (0197011, 2000100, 2000111, etc). I need to filter these accounts into a range (groups) of accounts ("Payroll", "COGS Overhead", "Rent" and so on).In other words "Payroll" may consist of over 50 of the accounts, with each of those accounts having multiple entries of date and $amount. I have done this with the select statement.

So back to my original question.. Is there a way in a subreport to may a shared variable for each group total (not the report grand total) that can be shared with the main report.

If this could be done with cross tabs I am open to suggestions. I am not really familar with crosstabs or formulas with there use.

Thanks again,
Dave

 

[lbass]

Well, there is probably a better way of doing this--using arrays, but I think this will work for you. Within each subreport, insert your group, and create a formula like the following:

//{@accumsubtotals} to be placed in the group footer:
whileprintingrecords;
shared stringvar sub1amt;

sub1amt := sub1amt + totext(sum({table.amount},{table.group}),2,"")+", ";

Then in the main report, you can reference each amount by creating a separate display formula for each subtotal, as in the following:

whileprintingrecords;
shared stringvar sub1amt;

split(sub1amt,", ")[1] //increment the 1 to create each of 40 display formulas

Change the variable name for each of the other three subreports to something like sub2amt, etc.

-LB

 

[archerks]

Thanks LB..I really appreciate the time and effort you devoted to this.

That works great.

One last thing, is there an "elegant" way to convert the text back to currency/number.

Right now I'm using a second formula to do this.

//@textTOnumber
Tonumber ({@Sub1amt})

It would be nice not to have to create the second formula to do it.

Dave

 

[lbass]

Sorry, that should have been the following per display formula:

whileprintingrecords;
shared stringvar sub1amt;

val(split(sub1amt,", ")[1])

-LB

 

[archerks]

Everything was working great UNTIL one of the subreports had a group in it that didn't have a value, no total (no entries in that time frame). This causes the remaining groups values to move up one. The main page formula then puts group 22's value in the group 21 variable and so on.

Dave

 

[lbass]

Do you have a table that contains all accounts, not just those with account activity? If so, within each subreport, you would add the All Accounts table and use a left join FROM that table to your current table (Table2). Then use the All Accounts account field in your grouping formula. Then create a formula {@amount} to convert nulls to zeros (or go to report options-> and check "convert nulls to zeros"):

//{@amount}:
if isnull({table2.amt}) then 0 else {table2.amt}

Use this in your calculations instead of {table2.amt}. This should ensure that there is a summary for every account.

-LB