Using Arrays within Crystal Reports

[ZanyCat]

Does anyone have any examples of using arrays in Crystal?<br><br>I know this is probably the kind of stuff you guys do in your sleep but I would appreciate it greatly!<br><br>I am trying to write a report to calculate commission rates for salespeople and I am stuck on using an array to calculate which commission rate the salespeople will get for each sale.&nbsp;&nbsp;The commission rates are subject to certain conditions ...i.e which commission group the product sold belongs to and which commission group the salesperson falls under. The matrix which is currently used to manually calulate the commission percentage is a grid which has product groups down one side and salesperson commission groups across the top. These are then cross referenced to get the correct commission percentage.<br><br>I hope this is as clear as mud!!<br><br>Thanks in anticipation<br><br><br>Zany<br>x<br>

 

[MalcolmW]

This is more of a deflection of your question rather than an answer.<br>Can you put your array into a table, and link that into the report? <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[ZanyCat]

Thanks for your prompt reply Malcolm!<br><br>I have been able to export the current excel workbook which has the commission info on it to an Access database. <br><br>I can link that to the crystal report but cant get my head around the logic required to get crystal to look at both the salesperson id and the product group and then cross reference both of these to arrive at the correct commission %. Looking at my crystal manual it would seem that I need to use arrays but I am totally stuck as to how I am going to get something that is 10 columns x 14 rows into an array as the examples in the manual only seem to use one level i.e Monday, Tuesday etc...for days of the week. I am looking at more of a 3D solution if you see what I mean. I hope I am making sense.&nbsp;&nbsp;<br>Thanks for your time&nbsp;&nbsp;&nbsp;<br>

 

[squared]

Too bad crystal doesn't support multi-dimensional arrys. But here is a suggestion for simulating multi-dimensions the hard way in Crystal Syntax. In your case you would need 10 seperate 14 element arrays but here is a simple example using two 3-element arrays to simulate a 2X3 array<br><br>Local Numbervar Array col_one;<br>Local Numbervar Array col_two;<br><br>col_one := [1,2,3];<br>col_two := [4,5,6];<br><br>col_one[1] // will return the value 1<br>or<br>col_two[3] // will return the value 6<br><br>sort of messy, but maybe it will give you some ideas.<br><br>Jeff<br><br>

 

[MalcolmW]

I may be missing something here, but I think it would be a lot easier to set this up as a database structure so that you won't be saddled with maintaining reports if (when) the commission matrix changes.<br><br>How about a Sales table with<br>SalesPersonID, SalesCommissionGroup<br>(you likely aleady have something like this)<br>and a Products table with<br>ProductID, ProductCommissionGroup<br>(again, you likely already have something like this)<br>and lastly a Rates table<br>SalesCommissionGroup, ProductCommissionGroup, Rate<br><br>So you could get<br>SELECT SalesPersonID, ProductID, Rate<br>FROM Sales<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER JOIN Rates ON Sales.SalesCommissionGroup = Rates.SalesCommissionGroup<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER JOIN Products ON Products.ProductCommissionGroup = Rates.ProductCommissionGroup<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>