Array on Fields 1

[LouieGrandie]

In the data source there are seven (7) fields of different type of "owners". Selection Owner, Calculation Owner, Submittals Owners and so on. I need to write a formula that turns these fields into an array. In the report I will need for Crystal to determine if a selected owner is in the array. Is that possible. Does that make sense.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[hilfy]

Please explain a little more about what you're trying to do? Why do you think need an array for this? Based on what you're trying to do, there may be a way to do this without using an array.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[LouieGrandie]

Lets say there is an owner MHess. On any number of records MHess might be in any one the owner fields. The client wants to group of MHess and list all records in which MHEss is present in any of the 7 fields. I was thinking of doing an array of the 7 fields. They need to be able to connect by OLE. They do not want to create DSNs for ODBC.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[hilfy]

The challenge here is when the report is run for multiple owners - any given record will only appear once on the report for the "first" owner in the list that it matches up to - if you just use linked tables in the report. If they want to have a record appear for EACH owner assigned to it, you'll probably have to do a command or a stored procedure.

A command is just a SQL Select statement - you want to include ALL of the fields required for the report so that you're not linking the command to anything else in the report. What I would do is something like this:

Select
'Selection' as OwnerType,
SelectionOwner as Owner,
<other fields needed for report>
from MyTable

UNION

Select
'Calculation' as OwnerType,
CalculationOwner as Owner,
<other fields needed for report>
from MyTable

UNION

Select
'Submittals' as OwnerType,
SubmittalsOwner as Owner,
<other fields needed for report>
from MyTable

UNION

... - one select for each type of owner.

This way you get a data set that has a record for every type of owner. You would then group by the Owner field to get the data you're looking for.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[LouieGrandie]

Dell, the Command statement was the way I ended up going. Thanks for confirming.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc