Generating Ad Hoc Reports

Starting from CA Spectrum 10.0 release, Ad Hoc reports use ODBC for accessing reporting database. You must create a data source name (DSN) on the CABI host, which connects to a SRM Host in OneClick.
casp1032
Starting from CA Spectrum 10.0 release, Ad Hoc reports use ODBC for accessing reporting database. You must create a data source name (DSN) on the CABI host, which connects to a SRM Host in OneClick.
The following section describe the procedures to configure DSN in Windows and Linux operating systems.
For Windows:
  1.  Go to Windows Administrative tools
  2. Open ODBC Data Sources (64-bit) and add a System DSN with name “SpecDB” as shown below:
    Configure DSN_Windows.png
  3. Similarly, create another DSN in ODBC Data Sources (32-bit) as well.
    If you do not see the MySQL ODBC driver, please download the connectors from https://dev.mysql.com/downloads/connector/odbc/5.1.html 
  4. Restart CABI Servers.
For Linux:
I. Installing UNIX ODBC Driver
  1. Copy unixODBC-2.3.2.tar.gz package
    Download lin:http://www.unixodbc.org/unixODBC-2.3.2.tar.gz
  2. Unzip and untar unixODBC-2.3.2.tar.gz 
  3. From unixODBC-2.3.2, run the following command to install ODBC manager: 
    'yum install unixODBC unixODBC-devel'
II. Installing Mysql ODBC Driver
  1. Download MySQL ODBC Driver mysql-connector-odbc-5.1.13-linux-el6-x86-64bit.tar 
    Refer site link: https://dev.mysql.com/downloads/connector/odbc/5.1.html 
  2. Untar mysql-connector-odbc-5.1.13-linux-el6-x86-64bit.tar 
  3. Copy the lib and bin folder content to /usr/local/lib64 and /usr/local/bin respectively
III. Setting up ODBC configuration
     Execute the following steps as a CABI Install user
  1. Create an environment variable with ODBCINI 
    ODBCINI=<CABI install directory>/sap_bobj/enterprise_xi40/odbc.ini 
  2. Add or update the LD_LIBRARY_PATH environment variable in 
    <CABI install directory>/sap_bobj/setup/env.sh 
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib64:/usr/lib64
    The path added above must be to the location of the libodbc.so and libodbcinst.so files which are installed with the UNIX ODBC manager.
  3. Create or update the odbc.ini file in the above location to ensure it contains the DSN entry for MySQL ODBC DSN. 
    Before updating, you must take backup of odbc.ini.
    [ODBC Data Sources] 
    SpecDB=Spectrum DSN for WEBI reports 
    [SpecDB] 
    Description=Spectrum DB for Adhoc Reporting 
    Driver=/usr/local/lib64/libmyodbc5.so 
    SERVER=
    <SRM OneClick Server name> 
    PORT=3306 
    USER=WEBI_user 
    PASSWORD=0n3cl1Ck 
    Database=srmdbapi 
    OPTION=3
     
  4. Create Symbolic link in <CABI install directory>/sap_bobj/enterprise_xi40/dataAccess/connectionServer/drivers/lib64  folder 
    ln -s /usr/lib64/libodbc.so ./libodbc.so.1 
    ln -s /usr/lib64/libodbcinst.so ./libodbcinst.so.1
  5. Modify the ODBC driver to 64 bit in mysql.sbo file under folder  <CABI install directory>/sap_bobj/enterprise_xi40/dataAccess/connectionServer/odbc 
    Change unix64 parameter under database tag as mentioned below:
    <Library Platform="Unix64">dbd_ux32odbc3</Library> 
    to 
    <Library Platform="Unix64">dbd_uxodbc3</Library>
  6. Restart all servers from SIA/ using stopservers and startservers command.
Generate Ad Hoc Reports
This section describes the procedure to generate Ad Hoc Reports.
CA Spectrum
Ad Hoc Reporting is a feature which provides flexibility to define your own reports. You can generate the following Ad Hoc reports using Ad Hoc Reporting:
  • Simple Ad Hoc Reports
  • Complex Ad Hoc Reports
The
CA Spectrum
Ad Hoc Reporting feature provides the following capabilities that enable you to define your own reports:
  • Custom selection of data objects
  • Custom selection of chart options
  • Custom layout of report components
To enable custom reporting,
CA Spectrum
Reporting relies on the use of an internally designed BusinessObjects metadata layer referred to as a
universe
. A universe is a data abstraction mechanism that is provided by BusinessObjects to allow data retrieval from a database without a deep understanding of the underlying data structures. A
CA Spectrum
-specific universe provided by CA is deployed to the BusinessObjects Enterprise software to support your Ad Hoc reporting needs.