Need help on Count,Running Totals

[vega83]

Friends I have data like below
Source PO SKU Rtoal(count PO where source='PM')
WM 454 34234 0
PM 454 76876 1
IB 454 NUll 1
TM 454 5131 1

I want to checK If PO is in WM or PM or IB or so
. But when in the first line If I do ruuning total to count po only for PM it will show o in first line and then only 1 from 2nd line. But is there any way that if there is PM in line 4 or 5 it should give count no 1 evev in first line?

Like below

Source PO SKU Rtoal(count PO where source='PM')
WM 454 34234 1
PM 454 76876 1
IB 454 NUll 1
TM 454 5131 1

 

[IanWaterman]

YOu can only get the RT to evaluate after it has encountered the condition. YOu will have to arrange your data so that PM appears at top of list.

YOu can do this by grouping data on Source and then doing in specified order such that PM appears first and the rest in say alphabetic order.

Ian

 

[vega83]

Ian thank you for quick response, But I have to check smilarly on other source data too basically for every line of data I have to check if the PO is in all the 5 sources, for example above I want to check if PO 454 is in WM,PM,IB,TM or not for every line. Any help please?

Thanks a ton for all your time.
V.

 

[IanWaterman]

What are you trying to achieve ultimately. How should your final report look. There may be a better way to achieve what you are trying to do as RTs will not work as you want them too.

Ian

 

[vega83]

I want to add a formula with condition to report,
Suppose PO has Source WM PM bit dont have any record IB and TM then say"STAUS NOT FOUND IN IB OR TM" , If PO has record with source only TM: say not founf in other 3, so on.
Basically for a given PO I have to check acorss all SOURCE , even in different lines and give Status message which will be consitant across all the line sof data

Thanks so much ,
V.

 

[IanWaterman]

YOu can do that by grouping report by PO. Set up the RTs for each Source..

In Group Footer add a formula

@Status
If IBRT =0 and TMRT = 0 and WMRT=1 and PMRT=1 then "STATUS NOT FOUND IN IB OR TM"
else
If IBRT =0 and TMRT = 0 and WMRT = 0 and PMRT=1 then "STATUS NOT FOUND IN WM, IB OR TM"
else
.....

Build up formula to cater for all permutaions and messages you need.

Ian

 

[vega83]

Ian,
I have 7 groups in My report now and PO is 2nd grp. Is there any other way i can achieve this?

 

[lbass]

What does the number of groups have to do with this? Please explain.

Wouldn't it make more sense to show the message about statuses in a PO group section instead of for every record (detail)?

-LB

 

[vega83]

LB, I have created a store proc with 5 union statements each query has different logic.

Coming to report, I have 7 fields on report and 7 groups.I am displaying all the information on GF7.

Suppose if the Source has value PM in line 3 I will count it as 1, I want to display this PM count as 1 on all other lines too it should show was count 1 on first line to where the source in WM.
If I use above formula it would still be 0 untill i get to line where source is PM.

 

[lbass]

Write a formula like this:

//{@PM}:
if {table.source} = "PM" then 1

Then write a second formula and place it in the detail section:

maximum({@PM},{table.group7field})

-LB

 

[vega83]

LB, thanks so much thats a wonderful idea!. It worksm I donkt know why I did not think fo that . Thanks a lot.

V.

 

[vega83]

LB, sorry but there is small problem here,I also have date field on this and it is different on each record(source)
and it is first grp field, if I do max, I am still getting Zero.

 

[lbass]

I think you should spend more time explaining what you are trying to do and showing a sample that is more representative of your data and what you are trying to do.

The formulas I showed you would work for the detail section if there was a "PM" within group #7. If you are checking for the presence of "PM" in a higher order group, you would need a new formula that uses the group #1 condition.

-LB

 

[vega83]

LB I am sorry All the above data that i showed you above is in from GRPfooter 7. As I get duplicate data I had to group on all the 7 fields which are being used on report.

Is there a way i can wrtie formula based on Grp footer 7 values?, or summarize the sumarries accros group footer line of data?

 

[vega83]

LB, other possible scenario I am thinking is use a subreport and without grouping I will apply your formula to shared varibale bring it to main report and based on tthat create new formula. Please let me know if there is any other way, because subreports will slow donw my performance . Thanks again!
V

 

[lbass]

You are not clearly stating what you need to do. If you want to summarize the maximum formulas, you can use variables, but you need to be more specific. What kind of summary? At what report level do you want the summary? What exactly are you trying to count? Please show some sample data that includes your groupings.

I'm wary of making any suggestions, because I don't think you are providing the whole picture, or else your requirements have changed. Earlier you seemed to want some kind of message displayed as a result of a formula. Now you seem to want to summarize. This is not hard stuff--it's just hard following what you are trying to do! Please spend some time detailing your requirements and providing samples, so that we don't waste time trying to figure them out.

-LB

 

[vega83]

LB here is my sample data in deatils

datetime PO vendor ASN BOL Source Message
4/16/2009 456 red 4353 44234 WM
4/16/2009 456 orange 2342 Null IB
4/16/2009 456 blue 1243 5645 PM
4/16/2009 456 green Null 98673 TM

All these data is on my grp footer 6. I did grouping on all those above items .
Message should be formula, I should check if PO has WM,IB,PM,TM as source acorss of the data. Suppose if PO has all the sources(WM,IB,PM,TM) acorss multiple lines then In message is hould display as VALID PO

datetime PO vendor ASN BOL Source Message
4/16/2009 456 red 4353 44234 WM Valid PO
4/16/2009 456 orange 2342 Null IB Valid PO
4/16/2009 456 blue 1243 5645 PM Valid PO
4/16/2009 456 green Null 98673 TM Valid PO

But in case I have sample data like below
datetime PO vendor ASN BOL Source Message
4/16/2009 456 red 4353 44234 WM
4/16/2009 456 orange 2342 Null IB
4/16/2009 456 blue 1243 5645 PM
4/16/2009 456 green Null 98673 WM

Not above data dont have TM as source for the PO, so I should display message PO miss TM
datetime PO vendor ASN BOL Source Message
4/16/2009 456 red 4353 44234 WM PO miss TM
4/16/2009 456 orange 2342 Null IB PO miss TM
4/16/2009 456 blue 1243 5645 PM PO miss TM
4/16/2009 456 green Null 98673 WM PO miss TM

I was able to implent your maximum formula in my subreport ,since I did grouping only on PO. I used those shared varibale and used the shared varaible(max value for each condition if Source ='PM' then 1 else 0 so on)) in main report to Diplay message(I compared like If sharev variable1,2,3,4,5 =1 then Valid PO). But I was wondering If i can do do without sub reports. I am sorry again If I have confused you, please let me if this is clear.

Thanks so much,
V

 

[lbass]

I am unclear on the need for a subreport--unless you need to assess the PO across some outer group? If so, you need to share which fields are grouped on in what order. Otherwise, you should be able to use a formula like this in the detail section (I only carried this out for 3 values PM, WM, and IB) where {@PM] is a formula like:

//{@PM}:
if {table.source} = "PM" then 1

...and so are {@WM} and {@IB}:

numbervar x := 0;
numbervar y := 0;
numbervar z := 0;
if maximum({@PM},{table.PO}) = 1 then
x := 1;
if maximum({@WM},{table.PO}) = 1 then
y := 1;
if maximum({@IB},{table.PO}) = 1 then
z := 1;
if x + y + z = 3 then
"Valid PO" else
if x + y + z = 0 then
"Missing All" else
if x + y = 2 then
"Missing IB" else
if x + z = 2 then
"Missing WM" else
if y + z = 2 then
"Missing PM" else
if x = 1 then
"Missing WM and IB" else
if y = 1 then
"Missing PM and IB" else
if z = 1 then
"Missing PM and WM" else
"Error"

-LB