fetching crosstab summarized field in formula in Crystal reports XI 1

[kunaljvora]

Hi,

I am trying to fetch the fieldname which identifies the crosstab's grandtotal and subtotal fields which I can use in other formula fields, and also use to show a graph. Since they are not database output fields, but are crosstab autogenerated ones, I cant get hold of the fieldname. Please help:

I am having product wise sales belonging to a group over 5 years, wherein the years are shown horizontally (monthwise and then grouped by year) and the products vertically, grouped by another field in the cross tab.

I have to display the annual summary for each products vertically. The Annual summary column is basically a subtotal generated by cross tab which you can suppress from the cross tab expert. (Subtotal for @yeargroup)

My issue is for Group 1, I have to show the AVERAGE of the annual sales, and for group 2, the TOTAL of the annual sales (in the same crosstab generated subtotal column for @yeargroup).

I can write a logic in the "Display String" formula field of the the same, but I am unable to fetch the required grouped series as a field.

e.g. Display string formula field crystal syntax for the subtotal in the crosstab:

Local Varchar newsales;
Local Varchar total;
Local Varchar avg;

newsales:= currentfieldvalue; // I want currentfield here instead of the currentfieldvalue
total:= sum(newvalues);
avg:=average(newvalues);

If (condition) then
totext(total)
else
totext(avg)

The above doesnt work (obviously) since crystal seeks a field for newvalues, instead of currentfieldvalue. But I dont know how to fetch the field which is a cross tab generated column and not something coming from the database. I can do total:= newvalues + 100; etc but I need a sum/average of the series grouped in the crosstab

Thanks in advance,
Kunal

 

[lbass]

Please identify the actual {table.field} names used in the crosstab expert for rows, columns, and summaries, and identify the type of summary currently used. If any of these are formulas, please show the content of the formulas. I'm guessing by Group 1 and Group 2 you mean two instances of the outer row field.

I think the graph should not be based on the displayed crosstab results, but created independently, if necessary, in a subreport.

-LB

 

[kunaljvora]

Right, I'll try to explain in as much detail as possible:

The data coming in the report is through an oracle package having a stored proc, and the output format is as follows:

Record Value Month
x 120 Jan 11
x 130 Feb 11
x 180 Dec 11

Where x is in the below format

Type_Supplier_Area51_zone1_Level1_product1
Type_Supplier_Area51_zone1_Level1_product2
Type_Supplier_Area51_zone1_Level2_product1
Type_Supplier_Area51_zone1_Level2_product2
Type_Supplier_Area51_zone1_Level3_product1
Type_Supplier_Area51_zone1_Level3_product2

Formula fields:

I am working on the Record string using different formula fields:

@Area groups the similar areas
@zone groups the similar zones
@LEVEL groups levels
@prod just formats the product names


@Yeargroup: Cstr(SP_KUN.Month, "yyyy")

Cross tab:

Columns:
@Yeargroup
SP_KUNAL.MONTH

Rows:
@Area
@zone
@Level
@product

Summarised fields:
Sum of SP_KUN.Values

The above formatting works fine in the cross tab.

In the crosstab, I am supressing the grandtotals but not the Column subtotal based on @Yeargroup.

In this Column Subtotal : I need AVERAGE of the yearly values if LEVEL1 and SUM of the yearly values if LEVEL2,LEVEL3 etc

In the Display String formula, I am trying to achieve the above using If else statement, but either the same value repeats (when using currentfieldvalue) or doesnt work since I cant fetch the FIELDNAME of the CROSSTAB GENERATED SUBTOTAL COLUMN.

If I can get that in xyz, then I can do the following:

If (condition)
then
sum(xyz)
else
average(xyz)

Hope this information helps.

Thanks a lot for replying

Kunal

 

[lbass]

You need to use:

if gridrowcolumnvalue("@Level") = "LEVEL1" then //etc.

However, you can't just plug in the summary function. You need to do the calculation in the contributing rows.

I am having trouble understanding which cells you are referencing. Can you add a little mock crosstab with fake numbers that shows where you want the average and sum?

-LB

 

[kunaljvora]

I tried but cant attach an image to this post.

I hav uploaded it here:

https://picasaweb.google.com/lh/photo/QjbOI3wxZ20FCT6h1HPxzA?feat=directlink

I have to show the annual average when New Users and annual sum when Sales, in the same column - ANNUAL (highlighted), which is a crosstab generated subtotal of @yeargroup

Hope this helps you to understand the layout.

Kunal

 

[lbass]

Your link didn't work. But try the following. First, make sure that your subtotals are at the bottom of the section (not the top). Change this in the customize style tab if necessary.

Then select all inner cells that contribute to the summary (including any row totals you might be displaying), i.e., all but the subtotals. Then right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar sumx := sumx + currentfieldvalue;
numbervar cnt := cnt + 1;
false

Next, select all subtotals->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar sumx;
numbervar cnt;
if gridrowcolumnvalue("@Level") = "LEVEL1" then
totext(sumx,2) else
totext(sumx/cnt,2)

Then while the subtotals are selected, add a reset formula by going to format field->borders->color->background->x+2 and entering:

whileprintingrecords;
numbervar sumx := 0;
numbervar cnt := 0;
crnocolor

-LB

 

[kunaljvora]

Hi,

Thanks for that reply, Tweaking with SQL output and with your reply, I managed to achieve what I wanted.

On a different note, but with the same report - I am struggling with the following:

Cross tab output:

Total 2011 01/11 02/11 .. 12/11 Total 2012 01/12 02/12..

Prod 1
Prod 2
Prod 3

The 01/11, 02/11 represent MM/YYYY which are grouped by @Year and I am generating a cross tab summary of Annual Sum.

The issue is the annual total appears before the beginning of every year (2011, 2012..)

Is there a way to show all the annual summaries on the left and then the MM/YYYY data?

like

total 2011 total 2012 total 2013 | Jan 11 Feb 11.. Dec 13

Prod 1
Prod 2
Prod 3


Please suggest if this is possible with Crystal reports Cross tab

Thanks
Kunal

 

[lbass]

You can't do that with an inserted crosstab, but you could create two crosstabs, the first just with the years, and the second with the months. Then in the second, suppress the row labels and in the customize style tab->format grid lines->remove the related grid lines. Then overlay the first crosstab on the second. Format the section to "relative positions" in the section expert (or something like that).

-LB

 

[kunaljvora]

Thanks a lot! That worked fine, I had tried that but didnt know about the "Relative position" - which eventually solved my problem.

Thanks again!

 

[kunaljvora]

Hi again,

In the same report, there is something I am trying to do:

I have a crosstab having the following structure (data on the right, just giving the row structure):

Supplier A
Group1 Jan 11 Feb 11 ...
Level 1
P1 200 400
P2 300 500 ...
Level 2
P1
P2
Level 3
P1
P2
Level 4
P1
P2
Total@Group

Supplier B ...
.
.
.


Total@Group is the @Group level subtotal within the crosstab (not the grandtotal)

What I need is the Subtotal of the group should add the @Level totals of Level 1 and Level 2 but not Level 3 and 4.

Is there a way to do this in crystal? Can I have a conditional formula on the subtotals in crosstab?

All I can do is either supress the subtotal altogether or have it with the total of Level 1, 2, 3 and 4. But I only want the total to add Level 1 and 2.

Please help!

Thanks in advance

Kunal

 

[lbass]

You can use the same method. Just use a formula like this for the sum (with the subtotals selected):

whileprintingrecords;
numbervar sumz;
if gridrowcolumnvalue("@Level") in ["Level 1","Level 2"] then
sumz := sumz + currentfieldvalue;

Then for the display string, use a formula like this:

whileprintingrecords;
numbervar sumz;
if gridrowcolumnvalue("@Level") in ["Level 1","Level 2"] then
totext(sumz,2) else //two decimals)
totext(currentfieldvalue,2);

-LB