cancel
Showing results for 
Search instead for 
Did you mean: 

Extract all the values from a Bex measure in formula

Former Member
0 Kudos

Hi,

I am pretty new to crystal and I am not being able to extract all the values from a bex measure inside my new formula. My measure could have one of the following 3 formats:

1) will contain only one entry that of a "#"

2) will contain 2  or more (typically not more than 3-4 entries though) entries, first of which will be a "#" and the rest will be dates in the format mm/yyyy

3) will contain multiple entries all of which will be dates in the above format.

in the formula when I am trying to do {ZMICMAM05_ZWK_AM_ZMICMAM05_Q0013_2.[ZCALMTO]}[1] I do get the first entry but  {ZMICMAM05_ZWK_AM_ZMICMAM05_Q0013_2.[ZCALMTO]}[2] this doesnot give me the second entry instead only a blank.

Thanks for help!!!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Zeeshan,

when you browse the data in crystal report from 'field explorer' it will display distinct limited data.

So from Bex How many # values it is coming we cannot conclude that.

Generally if # values coming into crystal we will restrict them from BEx end. If you would like to see how many values coming from Bex, you can place that field in Details section.

I don't know why you extracting whole data in formula, can you explain more about that.

Regards,

Naga.

Former Member
0 Kudos

Thanks for your response Naga!

The logic that I need to implement is the following:

1) if [ZCALMTO] contains only one entry that of a "#" then perform some calculation inside a report formula that has noting to do with [ZCALMTO].


2) if  [ZCALMTO] contains # followed by a date then pick up the date (ignore the #) and use in a report formula to calculate a date range.


3) if  [ZCALMTO] contains multiple dates then pick up the first date and perform the same calculation as above.

All of this could be easily achieve if I were able to restrict # at the Report Selection Formulas but when I restrict # there for case 1 my report becomes blank for  most of the fields. If I could count the number of values in [ZCALMTO] in Report Selection Formulas and restrict # for case 2) and 3) my problem would be solved. But apparently Count functions don't work in Report Selection Formulas, and therefore I am trying to extract all the values in [ZCALMTO] in my report formula to implement the stated logic.

Thanks!

Answers (1)

Answers (1)

Former Member
0 Kudos

I was able to implement the logic using NthLargest function.

Thanks!