Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
Back to discussions
Expand all | Collapse all

DataModule support for "bit" datatype?

  • 1.  DataModule support for "bit" datatype?

    Posted Thu January 28, 2021 03:29 PM
    I'm using Cognos 11.1.6.

    I'm creating a new DataModule based on tables and views from an SQL Server database. I'm noticing the DataModule does not support columns that are "bit" datatype in the database, instead of displaying data, it shows "<unsupported: bit>" message (see below).

    Any ideas how to fix this? Any idea how to change the datatype from within the module to "integer"?

    Thanks!

    #CognosAnalyticswithWatson


  • 2.  RE: DataModule support for "bit" datatype?

    Posted Thu January 28, 2021 04:35 PM

    The suggested workaround is create a view in the database which casts the unsupported type to a supported type.

    https://www.ibm.com/support/pages/apar/PH06816



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 3.  RE: DataModule support for "bit" datatype?

    Posted Thu January 28, 2021 05:15 PM
    Hi Ian,

    Thank you for your reply.

    Was wondering if there is a different workaround that can be done inside the DataModule, as opposed to on the DB side?
    Original Message


  • 4.  RE: DataModule support for "bit" datatype?

    Posted Fri January 29, 2021 03:00 AM
    Hi Richard,

    as of my knowledge there are no other alternatives.

    Regards
    Gertrud

    #CognosAnalyticswithWatson


  • 5.  RE: DataModule support for "bit" datatype?

    Posted Fri January 29, 2021 04:00 AM
    Hi Richard, 

    You can probably try creating a table in data module using direct SQL casting bit to integer and use that table in data module instead of the database table. This is similar to using a view, but you will be creating in data module and not the database. There is no workaround to change the data type for the column directly in data module for a database table. 

    Regards,

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------

    Original Message


  • 6.  RE: DataModule support for "bit" datatype?
    Best Answer

    Posted Fri January 29, 2021 04:29 AM
    I just verified it works using pass-through SQL and converting boolean data, that isn't supported by Cognos, type to int, so believe it works for bit as well.


    ------------------------------
    Sandeep Dhirad
    Elkjøp Nordic
    ------------------------------

    Original Message


  • 7.  RE: DataModule support for "bit" datatype?

    Posted Fri January 29, 2021 11:18 AM
    Thank you @Sandeep Dhirad

    That seems to be working, even if I don't ​use the to_number() function
    Original Message


  • 8.  RE: DataModule support for "bit" datatype?

    Posted Fri May 21, 2021 05:05 PM
    I am having the same problem in 11.1.7.  I think it's odd that when I use cast(columnname, int) I get...
    Conversion failed when converting the varchar value '<unsupported: bit>' to data type int.
    ...instead of Bit data type is unsupported.  The data the data module is getting is a varchar containing the value '<unsupported: bit>' rather than 1 or 0, true or false?
    In the Data Module editor, I don't see the option to create a table based on pass-through SQL.  Both the plus sign at the top of the "Data module" pane and the "Create custom table" button on the Custom tables tab want me to select tables that are available through the data source.  How do I get to that screen?  Do I need to change a setting somewhere to get that option?

    ------------------------------
    Douglas Pulse
    ------------------------------

    Original Message


  • 9.  RE: DataModule support for "bit" datatype?

    Posted Tue May 25, 2021 11:57 AM


    The answer to your question, "The data the data module is getting is a varchar containing the value '<unsupported: bit>' rather than 1 or 0, true or false?" is yes.


    The method to create a SQL table is as follows:

    1. Select the module node of the metadata tree.
    2. Click on the more button and bring up the context menu.
    3. Choose table with SQL.


    4. Write the SQL which you want to use.
    5. Select the SQL type from the SQL type drop down list.


    6. Select the data source from the data source drop down. If the data source is not in the drop down list it probably means that there is
    nothing in the module which uses that data source so you would need to add something from it to the module.


    This is second best to having a view where the column whose data type is unsupported has been cast to a supported data type.  This is because the computation of the conversion has already been done when the view is created.



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 10.  RE: DataModule support for "bit" datatype?

    Posted Sat January 30, 2021 08:29 AM

    See https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/c_ca_data_types.html

    While in FM you can define an expression which is capable of type casting to a supported type, Data Modules will impede you.

    Hence, choices (as of 11.1.7) would be:

    - type case in a database view

    - use type-in SQL object

    - define an expression using pass-thru expression syntax

    I'll grant, each of these are not appealing from various perspectives.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 11.  RE: DataModule support for "bit" datatype?

    Posted Mon February 01, 2021 05:39 PM
    Hi @NIGEL CAMPBELL and @Sandeep Dhirad

    We have 11.1.6, we implemented this using pass-through SQL, however we are noticing it is very slow..

    Is this to be expected? anything can be done to make it faster?

    ​​​
    Original Message


  • 12.  RE: DataModule support for "bit" datatype?

    Posted Tue February 02, 2021 03:06 PM
    Any feedback regarding this issue?

    We have 11.1.6, we are using pass-through SQL in our DataModule, (user defined SQL query), however we are noticing it is very slow, much slower that using the tables in the module.

    Is this to be expected? anything can be done to make it faster?
    Original Message


Related Content