Issues with Date when converting using convert functionality(text to SQL date)

Hi Vishal,

Thanks for reaching out to support with your question.

I could not initially replicate this behavior - my date conversion -

Could you provide details -

Report setup - is this a drill through / co-display / dashboard? If so does this occur on an individual report as well?

Conversion setup - details re: date formatting of source and report

Filter setup - pre-defined? Hierarchy? cached?

System setup - is there integration or web services in effect here? If so, could you try running the report on Yellowfin directly?

Also, could you provide a compressed copy of the logs folder for analysis? This can be found at /Yellowfin/appserver/logs. You can upload it to our ftp https://ftp.yellowfin.bi using the "upload files" option, just indicate a filename and timestamp of the behavior in a reply here.

Thanks,

Eric

Hi Eric,

What you have done is correct in the view. After this, create a report using this view and used this converted field as a predefined filter.

After creating report, edit the view and check the field which you converted. Its data type will automatically converted back to TEXT.

And the pre-defined filter will not work in report.

Details: Report setup - Drill Down Report. Reports which all the using this view having the issue.

Conversion setup: timestamp Field converted to date using CAST function at SQL. The Applied conversion functionality to Sql date.

Filter setup: predefined filter

System setup: NO

Updated log in : https://ftp.yellowfin.bi

Logs: Vishal Log files, Time: 11.14 pm PST

Hi Eric,

Any update on the above issue. This is the major issue we are facing in our reports. Let me know if any other information required.

Thanks

Hi Vishal,

Thanks for the additional info here. Looking at the logs, all I see arund the timestamp is this error 7/13 11:14 -

YF:2020-07-13 11:14:30: INFO (MIRunReportAction:handleDefault) - MIRunReport entered
YF:2020-07-13 11:14:31: INFO (ColourPickerAjaxAction:execute) - ColourPickerAjaxAction entered
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:41:ERROR (CopyReportProcess:cloneReport) - Did not update drillthrough link format for report: 158032
YF:2020-07-13 11:14:43: INFO (ColourPickerAjaxAction:execute) - ColourPickerAjaxAction entered
YF:2020-07-13 11:15:16: WARN (DBAction:invokeFinalize) - DBAction was not disconnected! Calling disconnect() from finalize(). Connection id = 20

Could you confirm whether report id 158032 is the affected report id in the reportheader table?

Could you provide a screenshot of the timestamp field in question? Would like to see the formatting.

Could you provide details on the Source DB and JDBC driver that is impacted?

Just a guess, but "updating drillthrough link formatting" sounds like YF might be getting field info from somewhere, that is different from what the DB has on file, and it could be getting confused.

Could you provide the details on the CAST SQL process? Is this a freehand SQL view for example? I wonder since the format is getting "converted" from text to sql date, yellowfin needs to call that "converted" format value in a way it is currently not. As a workaround, is there a way to use a column with the desired sql date formatting defined in the report, like a transformation, as opposed to using a conversion process?

Thanks,

Eric

Hi Eric,

The timestamp field used: Image: TimestampFormat.png.

Yes, it's a freehand SQL View.

Facing Issues in every report, created using this view.

Scenario:

created a freehand SQL view with new field 'Date' using the function Cast(enc_timestamp as date): Image: CastView.png

To get the data type as date, used conversion functionality (text to SQL).Image: Conversion.png

Created a report for this view, use the 'date' field as a predefined cached filter.

When I edit the same view for changes, the Date fields lose its conversion: Image: Whathappensafter.png.

Have to repeat this process every time, whenever I edit the view.

We required the same Date format, as there are other reports with the same format used in the dashboard. To Dashboard filter work we have to have the same date format for each report.

Workaround for time being, If we are not editing the view, Once the conversion is performed. In that case, we do not face any issue.

Hi Vishal,

Thanks for the additional info, working on replication at the moment and was wondering what DB type you are experiencing this with?

Thanks,

Eric

Google BIgQuery

Sent from my iPhone

On Jul 21, 2020, at 3:18 PM, Yellowfin Support <support@yellowfin.bi> wrote:



Hi Vishal,

Thanks for the details here, I'll have to request access to a BigQuery instance for testing, which may take a little time.

What's interesting is that I do see a few developer tasks in our system related to Google BigQuery and date fields, noticeably this one for example -

"When using Pre-defined Date Period Filter like Today, Last Calendar Month, Yellowfin receives a syntax error."

So I think since the functionality of a pre-defined date period filter that you are trying to use is currently marked as not working with BigQuery, we would need to address this as well before we could expect this to work as intended, would you agree?

Thanks,

Eric

I apologize, for this particular report the underlying data source is Microsoft SQL Server.

Vishal Agarwal

President & COO CareCognitics

408.910.7132 | vagarwal@carecognitics.com

The information contained in this transmission may contain privileged and confidential information, including patient information protected by federal and state privacy laws. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution, or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.

From: Yellowfin Support <support@yellowfin.bi>

Reply-To: Yellowfin Support <support@yellowfin.bi>

Date: Wednesday, July 22, 2020 at 9:33 AM

To: Vishal Agarwal <vagarwal@carecognitics.com>

Subject: New Comment in "Issues with Date when converting using convert functionality(text to SQL date)"

Hi Vishal,

Thanks for the clarification, again I see a potentially related task that might address this issue -

When using SQL Server (I imagine this applies to other DBs as well though I have not tested), casting into text will be read correctly, but if you cast into a date or timestamp, then it will return as text.

This sounds like you! Maybe you could try this ETL process as test to see if you can replicate?

If this is the case, we might have a solution from the comments -

I've investigated and this seems to be JTDS JDBC driver's fault. For some reasons it does not return the correct data type when casting as date.

I've tried it with the Microsoft JDBC Driver and it works fine

If you are using the JTDS JDBC driver to connect to the SQL DB, could you switch to the MSSQL driver and test?

Thanks,

Eric

Hi Eric,

I have tested the scenario, switched the JTDS JDBC driver to MSSQL driver and it works.

Can you please help with this query:

1: We have more then 25+ views that are using the SQL DB which is connected using JTDS JDBC driver. Does it affect the reports/Views in any way, if we switched the driver to MSSQL?

Thanks

Hi Vishal,

I've asked one of our implementation specialists about potential impacts of making this change, will let you know what I find. From what I gather we used to lean towards recommending jTDS for SQL Server, but with updates this may have changed. Personally, I'd recommend running some system tests after making this change to make sure everything's working as expected.

Thanks,

Eric

Hi Vishal,

Double checked with the team and they said there should be no noticeable differences in performance or functionality between JDBC drivers in this case. Hope that helps!

Before I close this off,I was wondering if you would like to be added to the existing developer task, with the hopes of getting this issue resolved with the jTDS driver?

Thanks,

Eric

Hi Eric,

Thanks for the update. As you recommend we are using MSSQL JDBC driver for connection.

Yes, Eric. That would be great if this issue is resolved with the JTDS driver.

Thanks.

Hi Vishal,

Thanks for the confirmation here. I looked into our existing task a little further, and there's a chance that this behavior might be due to a limitation outside of Yellowfin; I'm going to check with the team to see whether there was some reason this was not implemented over the course of the task, and make sure they think this is something feasible on our end. Will provide an update via this thread shortly.

Thanks,

Eric

Hi VIshal,

After looking into this further, this is a known bug with the jTDS driver, so there's not a lot we can do about this unfortunately.

https://sourceforge.net/p/jtds/bugs/679/

There are some ideas and workarounds people have tried online however. Hopefully the MS SQL JDBC driver will work in the meantime?

Thanks,

Eric

HI Vishal,

Since this is ultimately not a Yellowfin issue, I'm going to go ahead and mark this question as Answered at this time. Feel welcome to reach out in the future.

Thanks,

Eric