Can I do a Order By?

[Sahubba]

Hello,I need to display data in these orders

Group (Customer Name)
ERQ – ER – IP – OPO

Group (Customer Name)
ERQ – ER – OPO – IP

And if it is not in this order then i do not want the customers name to display.

Currently this is what i am getting

Group (Customer Name)
ER – OPO

Group (Customer Name)
ERQ – IP - ER

And this is not correct.
I hope this makes sense and someone can help me with this.
Thanks

 

[IanWaterman]

Is this a field?

ERQ – ER – IP – OPO

If not how is this made up, please show raw data and how its is currently grouped.

Ian

 

[Sahubba]

Hi Ian

This is a field.

In this field there are more then just these status, I just filter it in the report selection.
The customer just want these four status in these orders.
The data should look like this,

ERQ or ERQ
ER ER
IP OPO
OPO IP

Curently I it grouped by Customers.
thanks

 

[Sahubba]

I created a flag for each row and created a formula and insert it into the group selection. By doing that, I now only see all of the customers that have all of these instance. Now what I need to do is to supress all of the customers that does not have these instances in a certain order.

I only have 1 group (Customers)
The flag formula is;
//Flag1
if({customer_instances})=101 then 1
//Flag2
if({customer_instances})=105 then 1
//Flag3
if({customer_instances})=108 then 1
//Flag4
if({customer_instances})=115 then 1

This is what I put in the Group selection
sum({@Flag1},{customers})>0 and
sum({@Flag2},{customers})>0 and
sum({@Flag3},{customers})>0 and
sum({@Flag4},{customers})>0

This is what I am getting that is not in the order that I want, so I would like to supress it
Customer
1
2
1
3
4

Here is a sample of all the records that I want in this order.
Customer
1
2
3
4

 

[lbass]

But your status field does not return 1,2,3,4,5--from your earlier posts. If it did, you could just group on the status field and you would get the correct order. If you have a certain order you want, you can create a formula like this:

if {table.status} = "ERQ" then
1 else
if {table.status} = "ER" then
2 else
{table.status} = "IP" then
3 else
if {table.status} = "OPO" then
4

Then you could group on this, and place the status field in the header instead of the groupname.

Another option would be to group on the status field and use specified order.

-LB

 

[Sahubba]

Hi,
That worked!

One more thing if I wanted to add one more order,
would I need to add a subreport?
Or is the another way?

 

[Sahubba]

Hi,
I ended up validating the data and it is not correct.

This is what i am geting

Customer

1 EDQ 3/27/2011 3:12
2 ER 3/28/2011 4:38
3 IP 3/27/2011 6:18
4 OPO 3/28/2011 12:56

As you can see it is out of sequince.
This customer had status 3 before 2 so this customer should not display.

I only want to see the customers that had their status in these orders

Customer
1 EDQ 3/27/2011 3:12
2 ER 3/27/2011 4:38
3 IP 3/27/2011 6:18
4 OPO 3/28/2011 12:56

or

1 EDQ 3/27/2011 3:12
2 ER 3/27/2011 4:38
3 OPO 3/27/2011 6:18
4 IP 3/28/2011 12:56

Please let me know if this makes sense.
Thanks

 

[lbass]

This is the first time you have indicated that a datetime is determining an order that you want to check for. Can you please clarify whether you need to actually see the details for each customer who meets the criteria or is the point only to identify the customers that meet these criteria?

-LB

 

[Sahubba]

I would need to see the details for each customer who meets the criteria.

 

[lbass]

You should save the report as a subreport and then insert it in the customer group header_a section of the original report and link it on the customer field. Then in the detail section of the sub, add this formula:

whileprintingrecords;
shared stringvar x := x + {table.status} + ",";

In the sub report footer, add this formula:
whileprintingrecords;
shared stringvar x;
shared booleanvar y;
if not(x in ["EDQ,ER,OPO,IP","EDQ,ER,IP,OPO"]) then
y := true;

In the report header and in the customer group footer of the main report, add a reset formula like this:

whileprintingrecords;
shared booleanvar y;
y := false;

Then in the main report section expert->group header_b section (where the regular group header fields are) and then secondly for the detail section->suppress->x+2 and enter:

whileprintingrecords;
shared booleanvar y;
y = true; //note no colon

To make GH_a disappear, suppress all sections WITHIN the subreport, format the subreport (format subreport->subreport tab->suppress blank subreport), and then in the section expert->GH_a->check "suppress blank section". Do NOT suppress GH_a or the subreport object directly.

-LB

 

[Sahubba]

Hi LB,

This still did not work.
Here is what I get.

OPO 1/11/2011 7:18:00PM
EDQ 1/11/2011 7:19:00PM
ER 1/11/2011 9:54:00PM
IP 1/12/2011 4:20:00AM

This order should have been filter out.
Thanks

 

[lbass]

For me to troubleshoot this, you would have to explain exactly how you implemented my suggestion, as it should have worked.

-LB

 

[Sahubba]

Hi Lb,
Here is a description on the subreport

Group 1: Customer

Group 2: Customer Status Effective Time

Details: I Created this formula: whileprintingrecords;shared stringvar x := x +{table.status} + ",";

Report Footer: I Created this formula:whileprintingrecords;shared stringvar x;shared booleanvar y;if not(x in ["EDQ,ER,OPO,IP","EDQ,ER,IP,OPO"]) theny := true;

Everything is suppress but Group 1


For the main report
Report Header: reset formula
Group 1a: the Sub
Group 1b: Customer
Group 2: Customer Status Effective Time
Group Footer 1a: Reset formula

and in the secection expert for group 1b and the details I have this in the supress section:
whileprintingrecords;shared booleanvar y;y = true; //note no colon

Thanks

 

[lbass]

Move the following formula to the status group in the subreport (which I didn't know existed):

whileprintingrecords;
shared stringvar x := x + {table.status} + ",";

Be sure to link the sub on the customer field. Then use the suppression formula on the Group #1 and Group #2 header sections (sounds like you are already suppressing the details).

-LB

 

[Sahubba]

Hello,

Just so I am clear,

Do you want me to use the suppression formula on group 1 and group 2 on the main report?

 

[lbass]

Only on Group header 1_b and Group 2. You must NOT suppress GH1_a

-LB

 

[Sahubba]

When I do that the only thing I get is the customers name.

 

[lbass]

Please show a sample of the data before you add the suppression formulas for at least two customers. Then you show the results you get after adding the suppression formulas for GH1b and GH2.

-LB

 

[Sahubba]

ok, before its supress,

customer name
customer name
101 IP 6/2/2011 11:00:00AM
104 OPO 6/2/2011 6:02:00PM
115 EDQ 6/2/2011 11:26:00PM
103 ER 6/2/2011 11:26:00PM

False


customer name
customer name
104 OPO 6/3/2011 1:00:00AM
115 EDQ 6/3/2011 1:48:00AM
103 ER 6/3/2011 2:17:00AM
101 IP 6/3/2011 5:36:00PM

False

When I supress GH1A and GH2

Customer name

False

Thanks

 

[lbass]

Okay, create a a new formula like this:

whileprintingrecords;
shared booleanvar y;

and place it directly in the GH1b section to see if the value comes back true. This is just a test.

-LB