Solved: Summarize a formula which has Next/Previous

I need to summarize a formula, that is evaluated after another formula that uses NEXT and PREVIOUS.

The formula i want to summarize does not appear in the lst when i click "insert summary"

I am able to use variables for Previous, but really need Next, and i don't think there is a workaround for this.

Any suggestions will be appreciated.

SAP Managed Tags:
SAP Crystal Reports

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

You can try using a global variable to build a running agregation. The variable should be updated in the code that uses the Next/Previous operator according to the desired aggregation calculation.

Regards,

Stratos

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Unless I'm misunderstanding your suggestion, global variables populate from the top down moving from row to row. This is why it's a simple task to use a global variable to carry a value from row 1 down to row 2, thus mimicking the PREVIOUS function. It, however, won't carry a value from row 2 up to row 1.

The only way I know of to do this is in a SQL Command. Here is a simplified example using SQL Server and the Adventureworks database...


SELECT 
sod1.SalesOrderID, 
sod1.SalesOrderDetailID, 
sod2.SalesOrderDetailID AS NextDetailID, 
sod3.SalesOrderDetailID AS PreviousDetailID

FROM Sales.SalesOrderDetail AS sod1
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod2 
	ON sod1.SalesOrderID = sod2.SalesOrderID 
	AND sod1.SalesOrderDetailID = sod2.SalesOrderDetailID -1
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod3 
	ON sod1.SalesOrderID = sod3.SalesOrderID 
	AND sod1.SalesOrderDetailID = sod3.SalesOrderDetailID +1

Here is sample result from that query...


SalesOrderID SalesOrderDetailID NextDetailID PreviousDetailID
------------ ------------------ ------------ ----------------
43659        1                  2            NULL
43659        2                  3            1
43659        3                  4            2
43659        4                  5            3
43659        5                  6            4
43659        6                  7            5
43659        7                  8            6
43659        8                  9            7
43659        9                  10           8
43659        10                 11           9
43659        11                 12           10
43659        12                 NULL         11
43660        13                 14           NULL
43660        14                 NULL         13
43661        15                 16           NULL
43661        16                 17           15
43661        17                 18           16
43661        18                 19           17
43661        19                 20           18
43661        20                 21           19
43661        21                 22           20
43661        22                 23           21
43661        23                 24           22
43661        24                 25           23
43661        25                 26           24
43661        26                 27           25
43661        27                 28           26
43661        28                 29           27
43661        29                 NULL         28

As you can see, it's bringing in the previous & next with the rest of the data set so CR has no issue with it...

HTH,

Jason

The suggestion is to use the following kind of code (NOTE: this is just an example):


global NumberVar var1;
var1:=var1 + Next({MyKeyFigúre})

Regards,

Stratos

The whole point of the thread is to avoid using PREVIOUS or NEXT in the formula so that can be summarized. Combining NEXT with a variable doesn't do anything to solve the problem.

Jason

Thanks for explaining how you see this.

To me the original issue is how to build an aggregation on a formula that cannot be aggregated because it uses the previous/next operators.

Using a global variable would be an alternative.

Regards,

Stratos

Thanks for the discussion and suggestions.

What i need to do is "zero" a value in a formula, when the condition from another(the one which has NEXT and PREVIOUS)

has been met.

This is the formula(Dev size GB) i need to summarize, it is evaluated after {@Shared device }

{

evaluateafter({@Shared device });

if "NEXT" IN{@Shared device } and {SRMARRAYDEVICE.ISSHARED} = "Y" then 0

else IF {SRMARRAYDEVICE.ISMETADEVICE} = "Yes"

THEN {SRMARRAYDEVICE.METADEVICETOTAL}/1024/1024

ELSE {SRMARRAYDEVICE.ARRAYDEVTOTAL}/1024/1024

}

This is the Shared device formula :-

{

global stringvar previousarraylun ;

//GLOBAL NUMBERVAR THISLUNSIZE;

global stringvar nextarraylun := next({SRMARRAY.ARRAYALIAS})+ next({SRMARRAYDEVICE.ARRAYDEVICENAME});

if RECORDNUMBER = 1

then previousarraylun := {SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME}

ELSE

IF previousarraylun = TOTEXT({SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME})

THEN

(

previousarraylun := TOTEXT({SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME});

//thislunsize :=0;

" NEXT SHARED"

)

//OR previousarraylun = nextarraylun

ELSE IF nextarraylun = TOTEXT({SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME})

then

(

previousarraylun := TOTEXT({SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME});

//thislunsize := {STS_DEVICE_ALLOCATION.SD_TOTAL}/1024/1024;

"FIRST SHARED"

)

else

(

previousarraylun := TOTEXT({SRMARRAY.ARRAYALIAS} + {SRMARRAYDEVICE.ARRAYDEVICENAME});

"THIS DEVICE NOT SHARED"

)

}

As it is right now, when i select Insert Summary/Choose field to summarize, the formula @Dev size Gb is not available.

THis is my suggestion (Assuming you need a summary based on summation)

{

evaluateafter({@Shared device });

global numbervar mySummary :=0;

if "NEXT" IN{@Shared device } and {SRMARRAYDEVICE.ISSHARED} = "Y" then mySummary := mySummary + 0

else IF {SRMARRAYDEVICE.ISMETADEVICE} = "Yes"

THEN mySummary := mySummary + {SRMARRAYDEVICE.METADEVICETOTAL}/1024/1024

ELSE mySummary := mySummary + {SRMARRAYDEVICE.ARRAYDEVTOTAL}/1024/1024

}

Instead of creating a summary for Dev size GB just display a formula field with the following definition in your report;


global numbervar mySummary;
mySummary;

Let me know if this works for you.

Regards,

Stratos

Stratos,

I am trying to set a zero size for a device that is the same as previous or next (i.e. a shared device) so it's size is not duplicated. Once the list is prepared and the correct devices are zeroed, i then create a GROUP, based upon host name, and then i want to sum "Dev size GB" for the group. So i mreally do not need to sum throughout the report.

My current work around is to export the complete report, and then export as a csv file, import into a new report and then do the necessary grouping/summation which works fine, but obviously as additional steps.

thanks John

Is there a development issue or technical reason why a formula with NEXT/PREVIOUS cannot be summarized ?

Or can anyone think of a logical workaround ?

thanks in advance, John

As for a reason... My guess would would be that it has to do with evaluation time. The PREVIOUS and NEXT values can't be determined until the records have been sorted...

As for a workaround. The SQL suggestions works just fine...

If you're opposed to SQL I did get the following to work...

It's based on the idea that a concatenated string could be built using the values you want to compare and a corresponding string made up or unique IDs. The strings would be built WhileReadingRecords.

Then a 2nd formula could come along after the strings are build [EvaluateAfter(Formula_1);] and #1 find the record's ID in the ID string and determine it's numerical location in that string. #2 use that number to pull the comparison value out of the string.

So... if x = 5 then...

IF SPLIT(CompString, ",")[x] = SPLIT(CompString, ",")[x+1] THEN 0 ELSE {TableName.Money}

would be the same as...

IF {TableName.CompValue} = NEXT( {TableName.CompValue}) THEN 0 ELSE {TableName.Money}

Here are the formulas... Both formulas will need to be place in the details section... Formula_1 can be suppressed so that's not visible, but it needs to be there so that the strings can populate...


// Formula_1

WhileReadingRecords;

Global StringVar sa := sa & "," & ToText({TableName.SalesOrderID}, "0");
Global StringVar ca := ca & "," & ToText({TableName.CustomerID}, "0");

sa := IF LEFT(sa, 1) = "," THEN MID(sa, 2) ELSE sa;
ca := IF LEFT(ca, 1) = "," THEN MID(ca, 2) ELSE ca;

and


// Formula_2

EvaluateAfter({@Formula_1});

StringVar sa := sa;
StringVar ca := ca;

Local NumberVar x;
Local StringVar s;

x := 0;
WHILE s &lt;&gt; ToText({TableName.SalesOrderID}, "0") Do (
x := x + 1;
s := SPLIT("yyyy," & sa & ",zzz",",")[x];
);

IF SPLIT("yyy," & ca & ",zzz", ",")[x] = SPLIT("yyy," & ca & ",zzz", ",")[x-1] 
OR SPLIT("yyy," & ca & ",zzz", ",")[x] = SPLIT("yyy," & ca & ",zzz", ",")[x+1] 
THEN 0 ELSE {TableName.OrderAmount};

You will be able to summarize Formula_2.

So... workaround found!!! But for performance sake, I'd still go with the Command from my previous post. If you have a lot of records, the report could take a good bit of time to render using the formulas above.

HTH,

Jason

Nope... Scratch that...

The EvaluateAfter({@Formula_1}); didn't work as planned. Sorry.

For it to work correctly, it had to be changed to WhilePrintingRecords;

And that puts you right back to square 1... You can't summarize records that are evaluated during the WhilePrintingRecords pass.

Sorry,

Jason

Jason, thank you very much. I appreciate your help, and i believe the only solution is SQL, which i'm not very good at ! but maybe you could generate the sql ?

This is the "Crystal" SQL statment that brings back the data

SELECT DISTINCT "SRMDEVICEALLOCATION"."HOSTORHBAALIAS", "SRMARRAYDEVICE"."ISSHARED", "SRMARRAYDEVICE"."ARRAYDEVICENAME", "SRMARRAYDEVICE"."ARRAYDEVTOTAL", "SRMARRAY"."ARRAYALIAS", "SRMARRAY"."ARRAYTYPE", "SRMARRAYDEVICE"."METADEVICETOTAL", "SRMARRAYDEVICE"."ISMETADEVICE", "SRMARRAYDEVICE"."SHAREDOBJECTLIST", "SRMARRAY"."ARRAYSERIALNUMBER"

FROM ("EMCSTS"."SRMDEVICEALLOCATION" "SRMDEVICEALLOCATION" INNER JOIN "EMCSTS"."SRMARRAYDEVICE" "SRMARRAYDEVICE" ON "SRMDEVICEALLOCATION"."ARRAYDEVICEKEY"="SRMARRAYDEVICE"."ARRAYDEVICEKEY") INNER JOIN "EMCSTS"."SRMARRAY" "SRMARRAY" ON ("SRMDEVICEALLOCATION"."ARRAYKEY"="SRMARRAY"."ARRAYKEY") AND ("SRMARRAYDEVICE"."ARRAYKEY"="SRMARRAY"."ARRAYKEY")

What the report is designed to do is display a "single" device size for the 1st device

for a list of shared devices, and zero all the other following entries, so when i group/summarize "dev size gb"

it is only for 1 entry.

What i do in Crystal is create a formula "array + Lun" :-

{SRMARRAY.ARRAYALIAS} + " " + {SRMARRAYDEVICE.ARRAYDEVICENAME}

I then sort on this formula, so that all the devices are listed.

I then use next/previous, to generate the device status formula:-

First device is shared - display the device size

Next device is shared - device is shared so zero the device size

Device is not shared (neither previous or next "array + lun" is a match

so if i could bring back a metric, via SQL called "shared device status" = "First Device shared" or "Next Device shared" or "Not Shared". Then in crystal if i see "Next Device shared" i could zero Device size, and then be able to summarize.

So in SQL is this possible ? to generate/make the current entry for "shared device status" dependant upon previous(-1) and next(+1) of "array + lun"

John

I've never used IBM DB2 as a data source so I'm not overly familiar with it's specific SQL syntax, but after a little Googeling, I think this should be pretty close.


WITH
	CTE AS (
		SELECT DISTINCT 
		ROW_NUMBER() OVER(ORDER BY s."ARRAYALIAS", sd."ARRAYDEVICENAME", ) AS RN,
		sa."HOSTORHBAALIAS", 
		sd."ISSHARED", 
		sd."ARRAYDEVICENAME", 
		sd."ARRAYDEVTOTAL", 
		s."ARRAYALIAS", 
		s."ARRAYTYPE", 
		sd."METADEVICETOTAL", 
		sd."ISMETADEVICE", 
		sd."SHAREDOBJECTLIST", 
		s."ARRAYSERIALNUMBER"
		FROM "EMCSTS"."SRMDEVICEALLOCATION" sa 
		INNER JOIN "EMCSTS"."SRMARRAYDEVICE" sd ON sa."ARRAYDEVICEKEY" = sd."ARRAYDEVICEKEY"
		INNER JOIN "EMCSTS"."SRMARRAY" s ON sa."ARRAYKEY" = s."ARRAYKEY" AND sd."ARRAYKEY" = s."ARRAYKEY")
		
SELECT 
x.*,
CONCAT(x.ARRAYALIAS, ' ', x.ARRAYDEVICENAME) AS ArrayLun,
CONCAT(p.ARRAYALIAS, ' ', p.ARRAYDEVICENAME) AS PrevArrayLun,
CONCAT(n.ARRAYALIAS, ' ', n.ARRAYDEVICENAME) AS NextArrayLun,
FROM CTE x
LEFT OUTER JOIN CTE p ON x.RN = p.RN +1 AND x.ARRAYALIAS = p.ARRAYALIAS
LEFT OUTER JOIN CTE n ON x.RN = n.RN -1 AND x.ARRAYALIAS = n.ARRAYALIAS

Jason

Jason, thanks on many levels. The data source is Oracle 10g. Does this change the sql statement ?

John

I don't think so. I know Oracle supports common table expressions (and I'm pretty sure it uses the same syntax as above) and the same thing with the CONCAT function.

Everything else is pretty neutral in terms of syntax.

... and you're welcome.

Jason

Jason, when i have time i will try this. As you well know i have a solution(from my 4076 thread) exporting to .csv/ms access, that works, and i will use that solution for now.

Thank you very much for myour help, John

If it gives you problems you could try wrapping the column aliases in double quotes...


WITH
	CTE AS (
		SELECT DISTINCT 
		ROW_NUMBER() OVER(ORDER BY s."ARRAYALIAS", sd."ARRAYDEVICENAME", ) AS "RN",
		sa."HOSTORHBAALIAS", 
		sd."ISSHARED", 
		sd."ARRAYDEVICENAME", 
		sd."ARRAYDEVTOTAL", 
		s."ARRAYALIAS", 
		s."ARRAYTYPE", 
		sd."METADEVICETOTAL", 
		sd."ISMETADEVICE", 
		sd."SHAREDOBJECTLIST", 
		s."ARRAYSERIALNUMBER"
		FROM "EMCSTS"."SRMDEVICEALLOCATION" sa 
		INNER JOIN "EMCSTS"."SRMARRAYDEVICE" sd ON sa."ARRAYDEVICEKEY" = sd."ARRAYDEVICEKEY"
		INNER JOIN "EMCSTS"."SRMARRAY" s ON sa."ARRAYKEY" = s."ARRAYKEY" AND sd."ARRAYKEY" = s."ARRAYKEY")
		
SELECT 
x.*,
CONCAT(x.ARRAYALIAS, ' ', x.ARRAYDEVICENAME) AS "ArrayLun",
CONCAT(p.ARRAYALIAS, ' ', p.ARRAYDEVICENAME) AS "PrevArrayLun",
CONCAT(n.ARRAYALIAS, ' ', n.ARRAYDEVICENAME) AS "NextArrayLun",
FROM CTE x
LEFT OUTER JOIN CTE p ON x.RN = p.RN +1 AND x.ARRAYALIAS = p.ARRAYALIAS
LEFT OUTER JOIN CTE n ON x.RN = n.RN -1 AND x.ARRAYALIAS = n.ARRAYALIAS

Jason

Jason did you ever find a solution for this problem, I have the same issues and I'm stuck. Jayne

The BEST solution is the SQL Command mentioned earlier in the thread.

The PREVIOUS function can be worked around, if your data is being sorted at the data source. In that case you can variables can be used to carry a value down from a "previous" record for comparison with the current record's value... BUT if you are sorting in CR, You'd need to use "WhilePrintingRecords;" to avoid some goofy results. As soon as you add the "WhilePrintingRecords;" you loose the ability sum the formula.

Jason

The solution i ended up going with, was to use the formula asis, with the next/previous functions.

This creates the data i need, but cannot summarize any formlas.

So i then just export the report as a .csv, linked to a ms access db, and then use the new source, and pull in all the same

fielld, and i am then able to summarize the data field was was created with next/previous funtions. It works fine, and i just have

the extra step of exporting the original data.

Jason is correct, and i can use global varibles for the "previous" values, but not for "next".

John

Answers (0)

Ask a Question

Top Q&A Solution Author

User

Count

Summarize a formula which has Next/Previous

Accepted Solutions (1)

Accepted Solutions (1)

Answers (0)

Re: Moving a part of existing "On-Premise ECC" to ...

Re: Getting data field from Action response using ...

Error while HANA Cloud tenant in Python on SAP HAN...

Re: Is it possible to Import and use a custom fior...

Re: System functions in SAP ABAP