% Report - but with - is and is not fields?

[sandy2011]

Hi

I am looking to do a report - I want the report to show a calculated field but with different criteria and then as a percentage. i.e.

show count of fields: trans=like rep* and trans=not like rep*

e,g. rep=25 not like rep =200

to find the % I will need to show the same field but as a percentage of each other...

i.e like rep / not like rep * 100 = rep rate

This is what my final outcome will look like (if by grace of god it works)

20/200*100 = 10% are replacement orders

Any got a clue of how to do in one report?
Currently I can get the two totals but in two different reports then have to manually calculate it?

any help appreciated

 

[vbaInet]

I'm guessing this calculation is based on the records in your report's record source? If this the case then in the report's Footer section the textbox's Control Source will be:

=Count(IIF([Trans] LIKE "rep*", 1, Null)) / Count(*)

Then set the Format property of the textbox to Percent.

 

[sandy2011]

HI vbaInet

many thanks for your help.. (you have helped me with the code for another report where the fields are displayed in the report and also to understand my dilemma/solution much more).

With this one - they are not based on fields in report (report is a summary of count)

however, what i have done is - put both total values in a report / subreport

I now show both values in the one report - yay!

rep=25 (REPTOTAL)
not like rep =200 (ORDERTOTAL)

next thing is to get them to calculate !!!

I Have named the calculated fields and tried :
=Sum([REPTOTAL/ORDERTOTAL])*100

but to no avail... Any ideas on how to get the % to work?

 

[vbaInet]

Two things:

1. You said you have gotten the totals of each cases into seperate textboxes, why are you using Sum() again?
2. Whatever you put inside [] means the whole thing is a control or field name. In your case REPTOTAL/ORDERTOTAL in [REPTOTAL/ORDERTOTAL] is not a control or field name.

Any ideas on how to get the % to work?

This question was already answered -->

Then set the Format property of the textbox to Percent.

 

[sandy2011]

sum(orderqty) based on = rep = field name REPTOTAL
sum (orderqty) based on = not like rep = field name ORDERTOTAL

i want to get % of both these totals (one divided by the other x 100 to get percentage) - sorry didnt explain myself properly... thanks for %formatting i can do this.. its the calculation that i cant... lol
(thats why i used the sum)

i am not very familiar with calculations and reporting (as I am sure you have guessed by now! ..lol)

 

[vbaInet]

Everything was explained in my last post. You just needed to read it over and over again until it sinks in.

=[REPTOTAL]/[ORDERTOTAL]

I explained the significance of square brackets.

 

[sandy2011]

I is a donkey! it has sunken in...

I had FULLTOTAL instead of ORDERTOTAL hence the reason why the square brackets were not working!!

Thanks for all your help!! - all working now!!!! ) I have both totals and the percentage of one compared to the other!!!

----

Also found this useful..


Display a total from a subreport on the main report

Suppose you use a subreport named Orders subreport which contains a text box named Shipping Fee Total, and that text box calculates the sum of the Shipping Fee column. To display the sum from the subreport on the main report, you must add a text box to the main report and then use an expression to refer to the Shipping Fee Total text box on the subreport. You can do this by using the following procedure.

1. Right-click the main report in the Navigation Pane, and then click Design view on the shortcut menu.
2. On the Design tab, in the Controls group, click Text Box.
   

1. On the main report, click where you want to place the new text box.
2. If the property sheet is not already displayed, press F4 to display it.
3. On the Data tab of the property sheet, in the Control Source property box, type the following expression.

=IIf(IsError([Orders subreport].[Report]![Shipping Fee Total]),0,[Orders subreport].[Report]![Shipping Fee Total])
Notes

• In this example, you could use the more simple expression =[Orders subreport].[Report]![Shipping Fee Total] but then, if the subreport doesn't contain any data, the control on the main report displays #Error. Using the IsError function within the IIf function, as shown in the first expression, ensures that the text box on the main report displays a zero (0) if the subreport does not return any data.
• You can use the Expression Builder to create the expression by clicking
  
  in the Control Source property box.

1. On the Format tab of the property sheet, set the Format property to the appropriate value (in this case, Currency).

 

[vbaInet]

Good to hear you got it working!

And thanks for posting your findings for the benefit of others.