Can SQL Formula be used to calculate field to chart on?

[hansdebadde]

I have report that charts the number of people per days in length. I am using CR 2008 with the data in a SQL server. The length is calculated using
{@length}=totext(int((sum({DAYS},{NUMBER})-1)/5)*5 + 5,"000")
NUMBER is a unique identifier for each person
DAYS is the number of Days that must be summed
The Y axis is a distinct count of NUMBER
the X axis is @length
The report only includes this graph and i would be okay with using a sql command to pull in the data if i could calculate length in it.
I want to be able to select a range of length but i can't do that because I can't use the calculated value in the select expert. Can this be done or is there an easier way?

 

[lbass]

You could try using a SQL expression {%sumdays} (in the field explorer). Since you didn't identify your actual table(s), I will assume the fields days and number come from the same table called "table":

(
select sum(`days`)
from table A
where A.`number` = table.`number`
)

Not sure what the punctuation is like for SQL Server, so correct that and replace "table" with your table name.

Then you can reference the {%sumdays} in your length formula:

int(({%sumdays-1)/5)*5 + 5

-LB

 

[hansdebadde]

Thanks LB, you have helped me so many times, i feel i should slip you a few bucks! Will i be able to reference the length formula in the select expert and choose a length range?

 

[lbass]

Yes.

-LB

 

[hansdebadde]

Sorry for my stupididy but I don't think i translated your suggestion into the formula correctly, specifically the WHERE statement.
(
select sum("DAYS")from "PAYMENT"
where "PAYMENT"."NUMBER" = "PAYMENT"."NUMBER"
)
Payment is the table name they are both in.

 

[lbass]

(
select sum("DAYS")
from "PAYMENT" A
where A."NUMBER" = "PAYMENT"."NUMBER"
)

-LB

 

[hansdebadde]

LB, that works !!! However, the chart from before and now look slightly different
The chart is "On Change of" {@length} and then per "DATE" ( datetime field grouped per year). It has a "show values" of distinctcount of "NUMBER"
The new graph has the same shape but the totals (distintcount of "NUMBER" are less than half? Any ideas? I will look at the results more closely to see what NUMBERS are missed?

 

[hansdebadde]

I think my old report may have double counted some "numbers". I repeated my chart as a crosstab and some numbers showed up twice. I will look at it some more. Thanks again LB.
Using SQL expressions is one thing i really am new too. Do you know of a good link i could learn some of this it would greatly be appreciated.

 

[lbass]

Almost everything I know about SQL expressions I learned from Tek-Tips. Ken Hamady has an excellent guide that specifically deals with SQL expressions:

http://www.kenhamady.com/tools.html

-LB