formula field for all branches 1

[gagani]

My crystal report has 3 parameter fields(branchname, fromdate, todate). it gives the orders sold between two dates for a particular branch. I am trying to create a formula field on the same report which does the count of orderids sold between two dates but for all branches.
count(orderid) gives for particular branch selected. But I need a formula field which does the query for all branches.
can anyone help please

 

[lbass]

You would have to allow all branches into the report, not just selected ones, unless you added a subreport. Assuming you don't add a sub, but instead allow all branches in, you could insert a group on branch, and insert counts at the group and report level--without using formulas. Just right click on the order ID in the detail section and insert counts at the group and/or grand total level.

-LB

 

[lbass]

Just thought of another way, without a subreport. Allow all branches into the report, but use group selection (report->selection formula->GROUP) like this:

{table.branch} = {?Branch}

This will display only the selected branch, but if you insert a grand total count, it will pick up the non-selected branch records also. You should still group on branch and then the group total will be correct for the selected branch.

-LB

 

[gagani]

sorry, i am new to crystal reports. I am unable to get the point of how to allow all branches on to report. I have a branchtable which has got so many branches with branchid which however is linking with other tables through branchid.

 

[lbass]

Do not add any criteria for branches in your record selection formula.

-LB

 

[gagani]

in the same report, so far we have created count for selected branch and the count of all branches. I have to create one more count for the date range of 'beginning of the year'.
that is, if the user selects, let us say fromdate =21/10/2011 and todate = 21/11/2011. I dont have to consider the fromdate , but should consider the date 01/01/2011 as fromdate and todate is 21/11/2011.

if the user selects 21/10/2010 and 21/11/2010 then my date range should actually be 01/01/2010 and 21/11/2010. my count of orderids should be for that range for the selected branch and for all branches.

 

[lbass]

Then you have to use a record selection formula like this:

{table.date} in date(year({?FromDate}),1,1) to {?ToDate}

Then if you need to show counts also for the selected period, create a conditional formula like this:

if {table.date} in {?FromDate} to {?ToDate} then
1

Then place this in the detail section and insert a SUM (NOT a count) on the formula at the group and/or grand total level.

You could still have the group selection formula in place so that only the selected branch shows.

-LB

 

[gagani]

I would need the distinct sum for the above records since there are duplicate order ids.
could you please help with it.

 

[lbass]

Then change the formula to the following:

if {table.date} in {?FromDate} to {?ToDate} then
{table.OrderID} else
tonumber({@null}) //remove the tonumber() if orderID is a string

...where {@null} is a new formula you create by opening and saving a formula without entering anything.

Then insert a distinctcount on this.

-LB

 

[gagani]

thank you for your support.

I introduced two more formulas for the efficiency
of the branch during the dates selected and also for
year through.
1. if({statustable.STATUS})='success' and
{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate}
then 1 else 0
2. if({statustable.STATUS})='success' then 1 else 0

I take the count of each and then divide through the
other formula which we discussed before. that's working
fine.

sample table:
orderid personid personefficiency status
1 100 success
2 100 failure
3 101 failure
4 102 success
5 102 success
6 102 success

Now I need the person efficiency for the date range
selected and also for the year through.

The output should be

orderid personid personefficiency status
during daterange
1 100 50% success
2 100 50% failure
3 101 0% failure
4 102 100% success
5 102 100% success
6 102 100% success

like say, personid 100 involved in two orderids, but could
able to make only one success. so his efficiency is 50%

 

[lbass]

And your question is? What are you having trouble with?

-LB

 

[gagani]

My current table is like this:
orderid personid personefficiency status
1 100 success
2 100 failure
3 101 failure
4 102 success
5 102 success
6 102 success

personefficiency is missing in the table. I need to calculate the efficiency of each person based on the success over the number of orders he handled.

REQUIREMENT: MY TABLE SHOULD BE AS FOLLOWS:
orderid personid personefficiency status
1 100 50% success
2 100 50% failure
3 101 0% failure
4 102 100% success
5 102 100% success
6 102 100% success

I need a table a which also shows the efficiency of the personid.

 

[gagani]

FOR EACH PERSON I NEED TO FIND HOW MANY ORDERS HE DEALT DURING THE DATE RANGE SELECTED, OUT OF WHICH HOW MANY HE MADE SUCCESS.
THEN FINDING THE EFFICIENCY BY TAKING THE RATIO OF IT

 

[lbass]

Please don't use caps. What is it you don't know how to do? You already showed some formulas that might work--what is going wrong? Do you again have duplicate data?

-LB

 

[gagani]

It's not the duplicate data.
with the formulas I have I could only able to find branch efficiency. that is how many successes are there for total number of orders.
forget about what I found.
My requirement is , I need to find efficiency of each person. I have only the first table with me from above. It does not have percentages calculated for each person.
I need to calculate the efficiency of each person.
I need to find percentages of each person and should show in the table.
I have first table with me. what formula should I introduce to make the first table above to become second table.

 

[gagani]

consider i have this table
orderid personid status
1 100 success
2 100 failure
3 101 failure
4 102 success
5 102 success
6 102 success

I have to introduce one more column which also shows the efficiency of each personid.
i need a formula to find the efficiency of each personid
lets say, for personid 100, he involved in two orders but could make only one success, so his efficiency is 50%. the report itself should show with percentages calculated for each personid.

 

[gagani]

sorry to trouble you. I just need a formula which calculates the efficiency of each personid

 

[lbass]

Insert a group on person ID and then insert summaries on the same earlier formulas at the new person group level. You can suppress the group header and footer if you wish.

-LB