Results 1 to 10 of 10

Thread: Default values messed up

  1. #1
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Default values messed up

    Hi.

    We changed some column types in our database for example som DateTime became Date because we did not use the time parts.
    This messed up our Dynamic AI reports. Almost all our reports that had predefined filters with a default value stopped working.
    Before we could choose default values from a list and now there is like a function call inside the field like '@mth-2m()'. And the list with all other functions are gone.

    It feels like Dynamic AI don´t accept my date fields being date anymore and threat them as text columns..
    Anyone has any idea about what to do?
    Software Developer Prosmart System AB
    www.prosmart.se

  2. #2
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,720

    Default Re: Default values messed up

    Date field being treated like text fields means the SQL-Client is missing.

  3. #3
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Re: Default values messed up

    Quote Originally Posted by Evertjan Dondergoor View Post
    Date field being treated like text fields means the SQL-Client is missing.
    With SQL client you mean?
    There is a ODBC sql driver installed. 13.1 I think.


    Skickat från min iPhone med Tapatalk
    Software Developer Prosmart System AB
    www.prosmart.se

  4. #4
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,720

    Default Re: Default values messed up

    Yeah, that is what I mean. If you have issues with 'datetime2' or 'date' in 19 out of 20 cases it is because the SQL-client - which indeed isn't called SQL client anymore - is missing. If it is missing, datetime2 and date will behave like it is a string. I'm not up to date with D-AI though.

  5. #5
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Re: Default values messed up

    Quote Originally Posted by Evertjan Dondergoor View Post
    Yeah, that is what I mean. If you have issues with 'datetime2' or 'date' in 19 out of 20 cases it is because the SQL-client - which indeed isn't called SQL client anymore - is missing. If it is missing, datetime2 and date will behave like it is a string. I'm not up to date with D-AI though.
    I looked, and ODBC SQL Server Driver 13 is installed. But there is also some older drivers installed.
    Is Dynamic maybe choosing an older invalid driver for some reason?
    Software Developer Prosmart System AB
    www.prosmart.se

  6. #6
    Join Date
    Feb 2009
    Posts
    1,462

    Default Re: Default values messed up

    Hi Albin,

    You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.

    Your choices are:

    1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.

    2) Cast the date columns back to DateTime in either dyn-views and/or report formulas

    Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.

    Regards
    Bob

  7. #7
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Re: Default values messed up

    Quote Originally Posted by Bob Cergol View Post
    Hi Albin,

    You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.

    Your choices are:

    1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.

    2) Cast the date columns back to DateTime in either dyn-views and/or report formulas

    Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.

    Regards
    Bob
    Ok, thanks Bob.
    I think I will go with option 1.
    Software Developer Prosmart System AB
    www.prosmart.se

  8. #8
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Re: Default values messed up

    Quote Originally Posted by Bob Cergol View Post
    Hi Albin,

    You are correct that Dynamic AI is not seeing the 'DATE' type column as date data. It is a long-standing flaw that I have complained about to Cintac. Grouping and sorting would work just fine on the text date strings but you lose the date-picker and all the built-in date functions.

    Your choices are:

    1) Change the date types back to DateTime. The fact that you don't use the time portion is irrelevant -- they will all be emtpy values. This would be my choice.

    2) Cast the date columns back to DateTime in either dyn-views and/or report formulas

    Microsoft introduced this simpler date data type way back in 2008 so it is hard to explain why Cintac never supported. I am told it has something to do with the ADO driver they use.

    Regards
    Bob
    Bob or anyone else,

    I have one report that refuses to play ball after these date conversions..
    Maybe you coud tell me why because I just don´t get it.

    It is a simple report showing the time we spent on different projects grouped by developers and date.
    If I do a simple SQL View like this:
    Code:
    select User, Date, SUM(Time) as Time from MyTable where User IN ('001', '002', '003', '007', '008', '010')
    GROUP BY User, Date
    ORDER BY Date DESC
    I get a great report showing the data exactly like we want it. Like attached picture 1.
    Click image for larger version. 

Name:	Picture1.JPG 
Views:	20 
Size:	19.7 KB 
ID:	12355
    But since we also want to see this as a Graph (HC Line) we need to let Dynamic do the grouping so we change the SQL View code to:
    Code:
    select User, Date, Time from MyTable where User IN ('001', '002', '003', '007', '008', '010')
    ORDER BY Date DESC
    Then we create a report with HC Line graph and groups on Date, User.
    This results in duplicates.. I don´t get why, but if Dynamic is doing the grouping here we get a couple of duplicate rows per user. Picture below:
    Click image for larger version. 

Name:	Picture2.JPG 
Views:	23 
Size:	62.8 KB 
ID:	12356

    Have you seen anything like this?
    Software Developer Prosmart System AB
    www.prosmart.se

  9. #9
    Join Date
    Feb 2009
    Posts
    1,462

    Default Re: Default values messed up

    Albin,

    Are you saying the very same data gave you a different result before you switched from date-time to date type, than you are getting after you switched back from date to date-time type?

    See what you get if you remove the 'order by date' from your view, and made your date column a row grouper.

    Also, I wonder, depending on the sequence of events with this report's design editing, relative to your changing data type on the backend, if in the metadata for the report it thinks the type is text for the date column versus date-time. Try removing the column from the report, saving it, then adding the column back.

    Bob

  10. #10
    Join Date
    Oct 2013
    Location
    Nässjö Sweden
    Posts
    423

    Default Re: Default values messed up

    Quote Originally Posted by Bob Cergol View Post
    Albin,

    Are you saying the very same data gave you a different result before you switched from date-time to date type, than you are getting after you switched back from date to date-time type?

    See what you get if you remove the 'order by date' from your view, and made your date column a row grouper.

    Also, I wonder, depending on the sequence of events with this report's design editing, relative to your changing data type on the backend, if in the metadata for the report it thinks the type is text for the date column versus date-time. Try removing the column from the report, saving it, then adding the column back.

    Bob
    Hi Bob.

    Sorry for my late response.
    Yes, the very same data gave me a different result before I switched from date-time to date type, than I was getting after I switched back from date to date-time type
    But I tried your example and put the date column as a row grouper, removed the order by date. This gave me the same result. So I also Removed the columns from my report, saved it. Put the columns back in my report and saved again.
    NOW IT´S WORKING! Thanks Bob!
    Software Developer Prosmart System AB
    www.prosmart.se

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •