Using Excel Expressions within E.Spreadsheet Designer Functions

Edward Patterson

<p>I'm converting old actuate reports from v. 8 to v 11 and having to redo the data ranges and data in general.</p><p>Within my cells of interest I have references to "names" which are created by going to the Insert tab at the top of the GUI and scrolling down to "Name..."</p><p> </p><p>Problem 1:</p><p> </p><p>I'm trying to use the "SUMPRODUCT" excel function within Birt E.Spreadsheet v.11 with variables from my query. Example below:</p><p> </p><p>v.8: =SUMPRODUCT(--(CountAssignedUserCode=[AssignedUserCode]),--(CountStatus="Open"),--(CountRequestDescription=[RequestDescription]),--(CountRequestCount))</p><p> </p><p>v.11 (what i have so far): #query_GeneralOpenData:formula("SUMPRODUCT(" &'query_CountsData:write(AssignedUserId)' = AssignedUserId), "(query_CountsData:write(CountStatus)" =Open,( & "query_CountsData:write(CountRequestDescription)"=RequestDescription & "),(& "query_CountsData:write(CountStatus)")</p><p> </p><p>'query_CountsData:write(AssignedUserId) - defined as a Name "CountAssignedUserId"</p><p>query_CountsData:write(CountStatus) - defined as a Name "CountStatus"</p><p> </p><p> </p><p>Question 1: How I do properly reference a defined "name"?</p><p> </p><p>Question 2: How do I properly represent excel functions within e.spreadsheet designer, specifically SUMPRODUCT?</p><p> </p><p>Question 3: How does one reference a row within the spreadsheet that is not on the same column or row as the cell one is trying to define?</p><p> </p><p>Your help is greatly appreciated. Please and Thank you.</p>