setting up parameter fields and passing value to multiple formulas 1

[ppatel2]

Hello All,

I am trying to create a report in 8.5 that will give me four columns of data. Two (one actual the other a percent) columns reporting on the current month and the other two reporting on year to date totals (actual and percentage). All this data is in one table.

I tried to create a parameter field and then use that parameter field in the formulas to limit record retrival to a specific period (month). It seems to not be working correctly for counts but works fine for summing dollar amounts. I think I over simplified the process. I've pasted the two formulas (one for count and one for sum) below.

Count on the number of lines (first did the selection and then used a summary to do the actual count):

if {CT Period 1 2008 with ORG CODE Descr and Username - University.Period}={?Fiscal Period} then {CT Period 1 2008 with ORG CODE Descr and Username - University.DESCRIPTION}

Sum of amount where posting key equals 40 (formula does selection, used summary field to gather totals):

if {CT Period 1 2008 with ORG CODE Descr and Username - University.Posting Key}=40 and {CT Period 1 2008 with ORG CODE Descr and Username - University.Period}={?Fiscal Period} then ({CT Period 1 2008 with ORG CODE Descr and Username - University.Amount})

Any feedback would be much appreciated. Thanks in advance.

 

[lbass]

If you insert a count on a formula, you will get the number of times the formula executed, not the count of records that meet your criteria. Instead, change your formula to:

if {CT Period 1 2008 with ORG CODE Descr and Username - University.Period}={?Fiscal Period} then 1

Insert a SUM on this formula, not a count. If you don't have record inflation, this should give you the correct result.

-LB

 

[ppatel2]

Thanks lbass.

I have rewritten the formula and used the sum option. The calculations have come out correctly. I also have a distinct count I am trying to do on a similar field.

I need to count all unique documents #s for a specific group for a given period. The period has been defined by the parameter field, the group has been defined in crystal.

Once again, any feedback would be much appreciated. Thanks in advance.

 

[lbass]

You can handle this in one of two ways. Insert a running total, using a distinctcount, evaluate using a formula (enter your parameter criterion there), reset on change of group.

Or you can create a formula {@null} by opening and saving a new formula without entering anything and then creating a conditional formula:

if {CT Period 1 2008 with ORG CODE Descr and Username - University.Period}={?Fiscal Period} then {table.documentno} else tonumber({@null})

If the docno is a string, remove the tonumber(). You can then insert a distinctcount on this formula.

-LB