How can I get the current groupname?

[MRR77]

Hi @all,
(Crystal Reports 8.0)
My report (based on a query) has 5 groups, in each group the sum of 102 fields is calculated (17 rows, 6 columns). I need to calculate the ratio in 4 rows (i.w. 6x4 calculations) in each group. At the moment I get the result by using a formula like
(for the inner group(5)):
If Sum ({Query.myField1}, {Query.Group5})<>0 Then Sum ({Query.myField3}, {Query.Group5})/Sum ({Query.myField1}, {Query.Group5})*100
(for group1):
If Sum ({Query.myField1}, {Query.Group1})<>0 Then Sum ({Query.myField3}, {Query.Group1})/Sum ({Query.myField1}, {Query.Group1})*100

Is there a way to get the current groupname so that my formula can be used in a &quot;universal&quot; formula like
If Sum ({Query.myField1}, {@CurrentGroupname})<>0 Then Sum ({Query.myField3}, {@CurrentGroupname})/Sum ({Query.myField1}, {@CurrentGroupname})*100

It would be a great help if someone has a solution because I'd like to avoid creating 120 separate formulas...
Thanks a lot in advance!
Matthias (from Germany)

 

[synapsevampire]

120 formulas sounds suspect...

Rather than explaining how you've designed the report and looking for a means to make that work, consider posting your database version, connectivity used, example data and expected output.

-k

 

[MRR77]

Hi synapsevampire,
Database is Informix (DSS).
The query gets its data from a Select Fields From Table1 ... UNION ALL Select Fields From Table2
The following example is only a part of the SQL-statement which is about 30 KB (!)

Example:

SELECT q.u_biljhr AS BILANZJAHR, q.u_invjhr AS INVJAHR, 
Sum(CASE WHEN q.u_mvcnr IN ("711","765","773","791","962","969","705","885","887") THEN q.u_btg_whg_euro ELSE 0 END) AS PUE_EINGANG,
0 AS PUE_EINGANGR,
Sum(CASE WHEN q.u_mvcnr IN ("105","194") THEN q.u_btg_whg_euro ELSE 0 END) - Sum(CASE WHEN q.u_mvcnr IN ("193") THEN q.u_btg_whg_euro ELSE 0 END) AS PRM_PTFE_EINTR,
0 AS PRM_PTFE_EINTRR,
Sum(CASE WHEN q.u_mvcnr IN ("711","765","773","791","962","969","705","885","887","105","194") THEN q.u_btg_whg_euro ELSE 0 END) - Sum(CASE WHEN q.u_mvcnr IN ("193") THEN q.u_btg_whg_euro ELSE 0 END) AS PRM_EINGAENGE,
0 AS PRM_EINGAENGER,
Sum(CASE WHEN q.u_mvcnr IN ("110","112","140","190","192") THEN q.u_btg_whg_euro ELSE 0 END) - Sum(CASE WHEN q.u_mvcnr IN ("135","189","191") THEN q.u_btg_whg_euro ELSE 0 END) AS PRAEMIE,
0 AS PRAEMIER,

FROM q68t082_1 q 
WHERE (q.u_biljhr in ("01","02","03","04")) 
GROUP BY 1, 2 
 
UNION ALL SELECT r.r_biljhr AS BILANZJAHR, r.r_invjhr AS INVJAHR, 
0 AS PUE_EINGANG,
Sum(CASE WHEN r.r_mvcnr IN ("711","765","773","791","962","969","705","885","887") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) AS PUE_EINGANGR,
0 AS PRM_PTFE_EINTR,
Sum(CASE WHEN r.r_mvcnr IN ("105","194") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) - Sum(CASE WHEN r.r_mvcnr IN ("193") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) AS PRM_PTFE_EINTRR,
0 AS PRM_EINGAENGE,
Sum(CASE WHEN r.r_mvcnr IN ("711","765","773","791","962","969","705","885","887","105","194") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) - Sum(CASE WHEN r.r_mvcnr IN ("193") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) AS PRM_EINGAENGER,
0 AS PRAEMIE,
Sum(CASE WHEN r.r_mvcnr IN ("110","112","140","190","192") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) - Sum(CASE WHEN r.r_mvcnr IN ("135","189","191") THEN r.r_fre_r_whg_bkurs_euro + r.r_gru_r_whg_bkurs_euro ELSE 0 END) AS PRAEMIER,

FROM q68t748 r 
WHERE (r.r_biljhr in ("01","02","03","04")) 
GROUP BY 1, 2 
ORDER BY 1, 2;

Connectivity over ODBC (under Windows 2000)
Example data:

                   Gross2002   Retro2002   Net2002    Gross2003     Retro2003   Net2003
Prämie (premium)    47345.83         0     47345.83    57435.55          0      57435.55
Prov CTG_ETC        10589.65         0     10589.65    14537.18          0      14537.18
Ratio                  22.37         0        22.37       25.31          0         25.31

There are 17 positions like &quot;Premium&quot;, &quot;Prov CTG_ETC&quot;, Gross2002, Retro2002, Gross2003 and Retro2003 are fields from the database, Net2002 and Net2003 has to be calculated by Gross2002-Retro2002.
4 of the rows have to calculate the ratio like:
If Premium<>0 Then Prov CTG_ETC / Premium
10589.65 / 47345.83 = 22.37%

These calclations are repated in each of the 5 groups.
I hope that my explanations are clear enough to get an first expression of what I'm doing or trying to do... ???

 

[Madawc]

In Crystal 8.5, running totals are available and can be changed for each group. This sounds like what you need.

Madawc Williams
East Anglia, Great Britain

 

[MRR77]

Hi Madawc,
my posting to your suggestion from yesterday has not be stored - so here again.
The problem is not to create running totals - it is the ratio that must be calculated for each group, using 2 of the sum-fields in the group that have to be devided:
If {Query.Premium} <> 0 Then {Query.Prov CTG_ETC} / {Query.Premium}
10589.65 / 47345.83 = 22.37%
If I sum die ratio-fields, the result will be anything but correct:
22.37% + 31.56% + 42.31% + 33.85%
instead of
Prov CTG_ETC / Premium
with values
123,456.87 / 896,563.51 = 13.77%

Any idea?

 

[Madawc]

Is there a reason why you can't sum {Query.Premium} and {Query.Prov CTG_ETC}? And then calculate the ratio from the accumulated figures?

Running totals can apply formulas and could exclude {Query.Prov CTG_ETC} when premium was zero, if that's what you want.

Madawc Williams
East Anglia, Great Britain

 

[MRR77]

Hi Madawc,
I can sum the two fields {Query.Premium} and {Query.Prov CTG_ETC} and calculate the ratio - that is not the problem while working within ONE group:

If Sum ({Query.Premium}, {Query.GROUP5})<>0 Then Sum ({Query.Prov CTG_ETC}, {Query.GROUP5})/Sum ({Query.Premium}, {Query.GROUP5})*100

As you can see, the ratio is calculated by using a &quot;Where&quot;-clause: Sum field Premium when within group5.
I would like to construct only once a &quot;universal&quot; formula that should be copied into the other four groups:
Formula in group5:

If Sum ({Query.Premium}, {@GetCurrentGroupname})<>0 Then Sum ({Query.Prov CTG_ETC}, {@GetCurrentGroupname})/Sum ({Query.Premium}, {@GetCurrentGroupname})*100

(Same) formula in group4:

If Sum ({Query.Premium}, {@GetCurrentGroupname})<>0 Then Sum ({Query.Prov CTG_ETC}, {@GetCurrentGroupname})/Sum ({Query.Premium}, {@GetCurrentGroupname})*100

In one group I have 24 formulas with a similar construction:

If Sum ({Query.Premium}, {@GetCurrentGroupname})<>0 Then Sum ({Query.Profit}, {@GetCurrentGroupname})/Sum ({Query.Premium}, {@GetCurrentGroupname})*100

So, if that idea should work (to get the current groupname) I would only have to construct 24 formulas that can be copied into the other (four) groups. (This example is only one of those which must be created for various reports.)

I hope that my explanations can be understand... (???)
Greetings, Matthias

 

[Madawc]

Sorry, I don't follow at all. All of your examples are summary totals: a running total is something rather different.

Madawc Williams
East Anglia, Great Britain

 

[MRR77]

Hi Madawc,
sorry, I should give more comments ...
Let's say the data may be the following (source is a qry-file named query):

                  Gross2002  Country  Section
Premium            47345.83    DE      4110
Prov CTG_ETC       10589.65    DE      4110
Premium            12121.11    DE      4110
Prov CTG_ETC        9929.77    DE      4110
Premium             4444.44    DE      5000
Prov CTG_ETC        1111.11    DE      5000
Premium             8484.66    DE      5000
Prov CTG_ETC        4545.45    DE      5000
Premium            13456.11    US      4110
Prov CTG_ETC        5677.34    US      4110
Premium            34343.44    US      4110
Prov CTG_ETC        6777.77    US      4110
Premium            56677.33    US      5000
Prov CTG_ETC       10333.33    US      5000
Premium            84848.66    US      5000
Prov CTG_ETC       53332.13    US      5000

What I want, is:
1. Make one (inner) group for “Section” and an outer group for “country” (no problem).
2. In each group sum for “premium” and “Prov CTG_ETC” (no problem)
3. In each group make a calculation: Sum(Prov CTG_ETC) / Sum(Premium).

Number 3 is my problem. Up to number 2 the report looks like this:

Country-group header DE: empty
Section-group header 4110: empty

Section-group footer 4110:
                  Gross2002
Sum(Premium)       59466.94
Sum(Prov CTG_ETC)  20519.42

Section-group footer 5000:
Sum(Premium)       12929.10
Sum(Prov CTG_ETC)   5656.56

Country-group footer DE:
Sum(Premium)       72396,04
Sum(Prov CTG_ETC)  26175,98



Country-group header US: empty
Section-group header 4110: empty

Section-group footer 4110:
Sum(Premium)       47799.55
Sum(Prov CTG_ETC)  12455.11

Section-group footer 5000:
Sum(Premium)      141525.99
Sum(Prov CTG_ETC)  63665.46

Country-group footer US:
                  Gross2002
Sum(Premium)      189325,54
Sum(Prov CTG_ETC)  76120,57

In each group I have to calculate the ratio:
Sum(Prov CTG_ETC) / Sum(Premium)

My formula for this calculation in group “section” is:
If Sum ({Query.Premium}, {Query.Section})<>0 Then
Sum ({Query.Prov_ctg_etc}, {Query.Section}) / Sum ({Query.Premium}, {Query.Section})

Formula in group “country”:
If Sum ({Query.Premium}, {Query.Country})<>0 Then
Sum ({Query.Prov_ctg_etc}, {Query.Country}) / Sum ({Query.Premium}, {Query.Country})

As you can see, I only have to change the second parameter of the Sum-function. I would like to avoid those changes! If I can get the NAME of the current group by using something like a formula, I’ll need only ONE function for each column (here: Gross2002):

If Sum ({Query.Premium}, {@GetCurrentGroupname})<>0 Then
Sum ({Query.Prov_ctg_etc}, {@GetCurrentGroupname}) / Sum ({Query.Premium}, {@GetCurrentGroupname})

In my report I have 6 columns, not only Gross2002 but also Retro2002, Net2002, Gross2003, Retro2003, Net2003.
The ratio has to be calculated for four different fields – that is 24 calculations per group. Using 5 groups means to create 120 (different) formulas. Aaaaaaarrgghhhh! And this is only one report. There are about 20 reports with a similar construction.
HELP!
Thanks!

 

[nagornyi]

Did you consider using crosstab for your task? Seems that multilevel aggregation you are doing can be done with crosstab, and its totals and subtotals can be referred in the cells to get the percentage.

 

[MRR77]

Hi nagornyi,
nope, no chance. We tried to get results in other reports that have similar problems. Our data is to complex to get the percentage on this way. The Seagate-support-service here at our company can't give any solution. It might be possible in a newer version - but we don't know if or when we will get version 9 or 10.
So, I'm still looking for a way... Meanwhile, I have contructed 144 formulas for this report, but as I told before, there are waiting other reports to be designed - and they will have the same problems...

Thank you for your answer!

 

[nagornyi]

If there are many reports like this, I'd consider upgrading to Crystal Reports 9. Then you could create crosstab, place &quot;country&quot; and &quot;section&quot; to rows, first column (not sure what its name is) to columns, gross2002 to summary field. The crosstab creates all possible totals and subtotals in every slice of the data. You just need the ability to refer those totals in crosstab cells to get percentage, and this feature comes with CR9.

 

[MRR77]

OK, thanks a lot, we will look forward for CR9 or 10 - this will come in a few weeks...
Wish you (all) a &quot;Merry Christmas&quot;!

 

[jyoke]

Try this: GroupName({Query.Country})

 

[MRR77]

Hi jyoke,
your suggestion only gives the value of the current group. What I'm looking for is an oppertunity to get the name of the current group, so that my function will be used in various groups, always working with those fields that are in this group. So I'm still looking for an answer (meanwhile I have constructed 144 formulas and the next report has to be designed...)
Thanks a lot!