I have created a BAQ that has Order Date, Cust ID, Order #, Line, Part #, Prod Code, Ref Category, Job Code. Then I have three calc fields of Count (where it just puts a 1 for each line), Reorder (where a 1 is placed for the Reorder mark we have set up), and New Order (where 1 is placed for the new order mark). Now I am trying to do a (Reorder + New Order)/Sum(Count)–where Count is the total number of all listed in the BAQ.
I’ve tried this in the BAQ and get a error saying “operand data type varchar is invalid for sum operator” and it doesn’t work in the report as it set up by part number as parent. Ideas on how to do this?
You can use a prefix for every variable such as s for Strings, n for Numeric and you can name nCount for that formula. COUNT is a reserved word on SQL, it counts the numbers of records. It is a Formula, such as SUM. Those are named: Aggregate Functions.
I recommend you to copy/paste the SQL Sentence next time to review it.
all SQL formula syntax can not be used when defining calculated variables in BAQ, TOTAL, MAX, MIN, etc you need to change it slightly as @ggrimaldo suggested.
Ok, Corrected that. Thanks for the info! Now, I am trying to get a total for all parts using the nCount on each line of every part. Remember the part is parent. When I use Sum(nCount) it only sums the amount on that line and not all the lines. Is there a way to do that?