A less common way to create sets is to create sets is through free-hand SQL. Normally you will want to leverage to power of the set engine to design the SQL for you to return the correct results, but in rare cases you might find it helpful to create your own SQL Statements.
Free Hand SQL
The only item that needs to exist in the SELECT clause is ID. If an ID is specified and the SQL is valid, then the records will be imported into the set tables, if not, you will not be able to chose “Parse” to validate your SQL.
For our example, we can create a set called “All Customers” and use the following FreeHand SQL.
select CONTACT_ID as ID from dbo.DW_CRM_CONTACT
The SQL may be as complex as you require. As long as the SQL is valid, you should be able to insert it here and use it with Set Analysis.
I am not aware of a limit for the Free Hand SQL.
Import
Set Analysis also provides the ability to import individual records into the set tables. This is valuable if users have exported information into Excel and manually developed a list of records they wish to add to a set.
You can either store the text file in the BusinessObjects Repository or on the physical filesystem. Unfortunately the schema definition is pretty limited, so I recommend you use one of the sample files below as a guide. As long as the file contains at least 1 column and the key is the FIRST column, the import will work fine.
Click on the Import button and pick your file. Do not type in the name manually. If necessary, you may need to define a file format. I recommend that you use comma delimited files with a double-quote text qualifier (“).
Here is a sample import file of DM Contacts.
NOTE: In order to use the Upload feature, a c:tmp directory must be defined on your system.
NOTE: If you store the file on a filesystem, you may need to make sure that the Sets Query Service (AAQueryMgr.exe) has rights to access the file across the network. By default all BusinessObjects services run as LocalSystem which means by default they will not have access to UNC file paths
.