Search results for query: *

Hi, Check out thread1462-1740899, it shows how to write a query that generates some sort of a calendar. Dana

Hello, This is a nice piece of code (posted by somebody on Oracle forum, don't recall the name) that generates the calendar between two dates: SELECT C.REF_DATE, TO_CHAR(C.REF_DATE, 'WW') AS WEEK_NO, TO_CHAR(C.REF_DATE, 'MM') AS MONTH_NO FROM (SELECT TRUNC({?StartDate}) - 1 + N.DAY_COUNTER AS...

Using Bubble sort: whileprintingrecords; stringvar array MyValues := locarr; numbervar i; numbervar j; stringvar temp; for i:=1 to ubound(MyValues)-1 do ( for j:=1 to ubound(MyValues)-i do( if MyValues[j] > MyValues[j+1] then ( temp :=...

Ofcourse! Some times we just miss the obvious. Dana

Hi, Create a running total: count of rsaStatus, evaluate on condition rsaStatus = 'OnTime' or create a formula: @StatusFlag if rsaStatus = 'OnTime' then 1 else 0 inser a summarry on this formula Dana

H, Try this: ( If trip.end_tpid = timepoint.tpid then false //filter row else true ) Dana

hi, Try this in group selection formula: (if isnull(Command.CommentDate}) then true else Command.CommentDate} = maximum({Command.CommentDate},{Command.Id}) ) Dana

Hi Dan, This formula only works in the group footer. To get it in the header, maybe use a subreport to do the concatenation. Dana

Hello Dan, Try this to check for duplicates and print them on different lines: Whileprintingrecords; Stringvar Ref; if instr({Order.Ref}, Ref) = 0 then Ref := Ref + chr(13) + {Order.Ref} else Ref Dana

Something around this: formula to identify invoices not older that 3 years for each customer: if InvoiceAgeInYears <= 3 then 1 else 0 make a sum on this formula for each customer, and select only customers that have the sum = 0 Dana

Do you have each line in its own subsection?

Hello KC, You can display it on the group footer, you'll need three formulas: //place in groupheader section @ResetVar whileprintingrecords; stringvar vcontractlist := '' //place in detail section @ContractsList whileprintinrecords; stringvar vcontractlist := vcontractlist + table.contractID...

Make sure to check for nulls first if you use fields from AMOUNT table in the formulas

Very glad to be of help! Dana

I don't think a formula would help. You could use an Excel file that has all the dates. If your database is Oracle 9i or higher (you need analytical functions), and you can write SQL commands in Crystal, you could write a query that will generates all dates. You would have to use an SQL command...

Hello TC, Change the last if like this: if {?Run Time Option} = 'Last 12 Months' then Date(DateAdd("m", -12, Date(Year(CurrentDate), Month(CurrentDate), 1))) to Date(Year(CurrentDate), Month(CurrentDate), 1) else {?Date Range}) D

One way around would be if in the main report you can calculate the totals for each month. Then if the total for a month is 0 or null, then you'll know to suppress the coresponding on demand subreport. Dana

Hi, Try to insert crosstabs in the report footer, one for top5 and one for top10; delete the rows and only show the columns totals. Dana

Hi Brian, <I worry that I need to make my left join reference table include all Customer x Skus x AP x dates and join on all of those columns to get it to show up correctly. There has to be the "right" way that I'm not thinking of.> You are correct, that's how you have to do it. Maybe you don't...

Hi DeviousDi, Replace ?datefrom with SYSDATE-7, and ?datetill with SYSDATE Dana