Formula Help

I have a report that I need to take the average of a total(s)...
For example
I have a field that is a sum of units(number) and then I have a field that is a count of payments(number). I need to average these two fields,so that I have the average # of payments?How can I do this?I would try creating a new formula and expand the "REPORT FIELDS" in the tree within the formula editor (2nd one from left) and pull the applicable summary fields in. I'm not positive that would work as I haven't tried it before.|||I've tried that but it gives me an error of

The Summary/Running Total field cannot be created.|||I just tested it in a report I have and it worked just fine. Where are you trying to use it? You will not be able to use it in the details, but it would have to fall in the same group footer as the summary.|||These calculations are in the group footer. I need to average the grand totals.

What formula are you using? These are the two fields that I need to use

(Sum ({CLCHARGE.CUNITS}, {CLPAYMNT.COMPANY}) and
Count({CLPAYMNT.CHGID},{CLPAYMNT.COMPANY})|||Sorry I didn't ask this in the previous post, but what version of CR are you using? I am on 10, so if you are earlier, maybe it doesn't support those calculations in the same way it does for me. If you are 10 or XI, then I would need to ask - did you create formulas for the summaries or did you use the INSERT / SUMMARY from the menu bar? If you created the summaries by letting CR do it via the INSERT / SUMMARY, then you create a separate formula that drags those summaries in to the formula editor (typing a "/" between them), it should work.|||I am using 8.5|||It may be that you can't do it from 8.5. I'm sorry that I couldn't help you.|||I was afraid of that...Thanks for you help anyway.|||Hi there I have an average formula setup in one of my reports using CR 8.5.

whileprintingrecords;

numbervar TotalTsec;
local numbervar AverageFix:=TotalTsec/DistinctCount ({SW_CASE.swCaseId});
numbervar LenOfDay;

Hope this points you in the right direction !

Many Thanks

formating integer

hello. A question please. I am new on Sqlserver. Could anyone say me how must I format an integer to string. example

I have:

0001 (integer field of 4 positions) and I'd want to see it "0001" (string)

Thanks...

It seems like there is a better way of doing this, but one way is something like:

select right('000'+convert(varchar(4), 1), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0001

|||

Thanks. The problem is that I gave an example for the first field. (0001). But It could be 0010...

|||

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

|||

Waldrop wrote:

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

Nothing. That works fine.

SELECT right('0000' + cast([your_column] as varchar(4)),4)
FROM your_table
|||

I'm NEW !!!! Sorry !!!!. Another question please. I must put your statement in a string var. How can I do the '000' in my string var ?

Thanks...

|||

declare @.myString varchar(4)

set @.myString = right('000'+convert(varchar(4), 7), 4)

set @.myString = right('0000' + cast([your_column] as varchar(4)),4)

set @.myString = '0007'

|||

I'm so sorry. I don't understand. I forgot to say that the statement goes in an store procedure. My problem is how do I put the (') in a string var.

Thanks...

|||

An example of adding quote characters into the string variable:

declare @.myString varchar(6)
declare @.quoteChar char(1)

set @.myString = '''0007''' -- Loads '0007' (including quotes) into @.myString variable
set @.quoteChar = char(39) -- Loads a quote char ' into @.quoteChar variable
set @.quoteChar = '''' -- Loads a quote char ' into @.quoteChar variable

select @.myString as [@.myString],
@.quoteChar as [@.quoteChar]

-- - Output: -

-- @.myString @.quoteChar
-- -
-- '0007' '

declare @.anotherString varchar(4)

set @.anotherString = '0007' -- Loads '0007' (without quotes) into @.anotherString variable
set @.myString = char(39) + @.anotherString + '''' -- concatenates quote characters around '0007'

select @.anotherString as [@.anotherString],
@.myString as [@.myString]

-- - Output: -

-- @.anotherString @.myString
-- --
-- 0007 '0007'

|||Thanks. It works !!!!!