Follow

Using the Override Data Type feature

If you want to decide what data type a certain field has, this is the feature that can give you this.

The issue could be this. You have a Excel data source with some sheets that contains a field with only numbers. Even though it actually is a Item Id, it gets treated as a numeric value.

The issue could also be this. You have a data source connection to a database with a datetime field that contains both nulls and dates lower than 1753-01-01.

In both cases the overwrite feature could help. You can set up a general rule, or a specific rule.

Set up a general rule

Start by opening the data source settings menu and go to the Override Data Type pane.

As standard all data sources already have one rule set up. It is that all unknown data types will be converted to varchar(50)

Override_Menu.PNG

To create a new rule you start by pressing Add. If you want it to be towards a specific Schema, table or Field then set the selector to one that fits the rule you want to apply.

Choices.PNG

After this you choose what data type you want to convert to.

Choices2.PNG

Say you want to convert all varchar fields to nvarchar(50). It would look like this.

Setup_of_change.PNG

Remember that the schema needs to be the one that are in the data source, not the one you created in this system.

After this you synchronize and the changes should be seen.

Set up a specific rule

Say your issue is more specific. You have added a new excel sheet and it translates a integer field into a numeric.

Like this excel sheet

Excel_data.PNG

Becomes this.

Overwiev.PNG

First step is to find the field you want to change in the data source. Right click on the field and press Override Data Type.

Change_the_data_type_1.PNG

Find the data type you want it to become.

Change_the_data_type_2.PNG

Set the type of field you want, like unicode if it is a text type.

Change_the_data_type_3.PNG

The next step is to synchronize with the data source.

Change_the_data_type_4.PNG

You will then be notified about the changes you made.

Change_the_data_type_5.PNG

If you look at the Data type overrides pane again, you now have a new rule.

Change_the_data_type_6.PNG

 

Was this article helpful?
1 out of 1 found this helpful

6 Comments

  • 0
    Avatar
    Sten Lomme

    This does not seem to work on CData data sources.

  • 0
    Avatar
    Thomas Lind

    Hi Sten

    Can you send the CData provider you used and a detailed description of what you did as a support ticket?

    It works for the ones I have tried it on.

  • 0
    Avatar
    Sten Lomme

    I tested on a JSON source.

  • 0
    Avatar
    Thomas Lind

    Hi Sten

    Ok that can be an issue with that type of file/uri. Can you write to our support function regarding this?

  • 0
    Avatar
    Gert Knoops

    Hi Thomas

    This does not work when using CData Google Analytics connector. The option "override data type" does not exist.

     

    Edit: It does appear when selecting existing query's. The option does not appear when creating custom query's. Even so, the data type overrides do not work in either case. Everything stays String(2000) even after manually entering in the tab data type overrides.

    Edited by Gert Knoops
  • 0
    Avatar
    Thomas Lind

    Hi Gert

    You can't do datatype overrides on Query Tables. You are supposed to do those changes in the custom query.

Please sign in to leave a comment.