Deleting a calculated field from a view not possible (used in report) - but this is wrong

Stefan Hall shared this problem 15 months ago
Completed

Hi,
I have two calculated fields in a view that I cannot delete. YF claims they are still used in a report. But this is definitely wrong, both fields are not used (anymore).

I checked it with the following SQL, the result is clear. There is no report that still uses this field and YF is wrong.
How can I delete the two fields from the view? I have not found a knowledge article on this.


SELECT a.reportId, c.reportname, c.reportstatuscode
FROM ReportField a
INNER JOIN ReportFieldTemplate b ON (a.fieldtemplateid = b.fieldtemplateid)
INNER JOIN ReportHeader c ON (c.reportId = a.reportId)
WHERE b.shortdescription =  'calculated field name';

RESULT
102209	REPORT_NAME	DELETED

Replies (7)

photo
1

Hi Stefan,

Thanks for reaching out to Yellowfin Support.

Before any backend modifications to the Yellowfin database are done, it would be good to confirm whether it is being used in a report or not. To do this the FieldTemplateID of the Freehand SQL Calculated Field must be obtained, this can be accomplished by running the following query:


SELECT FieldTemplateId FROM ReportFieldTemplate WHERE ShortDescription = '<insert column name here>'Then once in possession of the FieldTemplateId it is possible to find out which report(s) Yellowfin thinks is using the unused Freehand SQL Calculated Field by running the following query:


SELECT ReportId FROM ReportField WHERE FieldTemplateId = <insert FieldTemplateId here>
Then having confirmed that the Freehand SQL Calculated Field is not being used, it may be removed from the view by running the following query:


DELETE FROM ReportFieldTemplate WHERE FieldTemplateId = <insert FieldTemplateId here>
and just to be tidy also run the following 2 queries (they mightn't be required). The ViewId is also available from the ReportFieldTemplate table:

DELETE FROM ReportConstraint WHERE ViewId = <insert ViewId here> AND ConstraintEntityTypeCode = 'COLUMN' AND ConstraintEntityId = <insert FieldTemplateId here>
and this one:


DELETE FROM ReportFieldParameter WHERE ViewId = <insert ViewId here> AND EntityTypeCode = 'REPORTFIELDTEMPLATE' AND EntityId = <insert FieldTemplateId here>
Hopefully after performing these steps you will have been able to remove your unwanted column, please let me know how it goes.

Also, because you are playing around in the backend of your Yellowfin repository, please make sure to back it up immediately beforehand!

Regards,

Sri Vamsi

photo
1

Hi Vamsi,

I have already answered your first question with the SQL output. It will use in a deleted report.

In this respect YF makes a mistake here and the hint "used" is wrong.
What exactly is YF checking here? Report = DELETED should be sufficient, right?

Regards
;) Stefan

photo
photo
1

Hi Stefan,

Thanks for your reply.

I have raised a jira task to the development team. I'll provide an update here as soon as I receive an update from them.

Regards,

Sri Vamsi

photo
1

Hi Vamsi,

is "awaiting reply" the correct status? I always think I still have to reply and look at the ticket unnecessarily often.
We are waiting for an internal reply from the YF Dev team after all.

;) Stefan

photo
photo
1

Hi Stefan,

I have reviewed your problem and I would like you to try the following steps below :

If you see that the calculated fields are still being used in a report, but you are certain that they are not, it's possible that the references to those fields exist in unpublished or hidden reports. You could try the following steps to see if that's the case:

  1. Go to the "Manage Content" page in Yellowfin.
  2. Click on the "Reports" tab.
  3. Use the search bar to look for reports that might be using the calculated fields in question.
  4. If you find any reports that might be using the fields, check if they are unpublished or hidden. You can see this information in the "Visibility" column of the report list.
  5. If the report is unpublished or hidden, click on the report name to edit it.
  6. Once you are in the report editor, search for the calculated fields in the "Fields" panel on the right side of the screen. If the fields are being used, you should see them listed here.
  7. Remove the fields from the report if they are present.
  8. Save and publish the report, if necessary.
  9. Try deleting the calculated fields from the view again.

If you still cannot delete the calculated fields from the view, even after following the steps above, you could try creating a copy of the view, and then deleting the calculated fields from the copy.

Regards,

Sri Vamsi

photo
1

Hi Stefan,

Hope things are good on your end. Just wanted to check in to make sure you had what you needed here.

Regards,

Sri Vamsi

photo
1

Hi Vamsi,

is there already a new status from the development team? I had the "error" another time - a deleted report locks a field in the view. From my point of view this should not be the case.

;) Stefan

photo
1

Hi Stefan,

I already raised a Jira task to Dev Team. I'll let you know once I receive an update from Dev team.

Regards,

Sri Vamsi

photo
photo
1

Hi Stefan,

Greetings for the Day!

Just want to let you know that due to unavailability of Vamsi who is no longer working at Yellowfin, I'm stepping in on behalf of him since I was looking out a few outstanding tickets. I understand you brought up this issue months ago and you did not hear back from us. However, I wanted to make sure that things will go into the right directions.

Test Performed

We added a few calculated fields to a view, published it, then used those fields to build a report. We then attempted to remove the fields from the view, as expected we got an error that selected fields are being used by reports and cannot be deleted.

566986dff1bb6ed7b529f332421c9c70

Then we removed those fields from a report and tried to delete those Feilds from a view and we were able to delete them.

As product team is proactively working on this case, we would like to know few more details as a part of investigation.

1. Could you please share the exact message which you see while deleting a calculated field from a view.
2. Please verify if the field is used in report/view filters.
3. If possible, screen recording would be helpful here to replicate it locally.

Regards

Nishant

photo
1

Hi Nishant,

It's not as simple as I had already written.
The error occurs when you delete a report, then sometimes not all field references are released. This is exactly when the error I described occurs.

;) stefan

photo
1

Hello Stefan,

I hope all is well,

I'm checking with product team to see if they required more information to replicate it. I will update here once I hear back from them. Meantime let me know if you have any questions.

Regards

Nishant

photo
photo
1

Hello Stefan,

I hope all is well,

We have few updates by the product team, since we were unable to replicate this issue. We would need below information to proceed further.

  1. A video record with replication steps.
  2. Screenshot of exact message which you see while deleting a calculated field from a view.
  3. If possible, copy of the config db and data source where the issue is reproduced.
  4. Exported report where view level filter is used and after deleting of which the issue occurs.

Regards

Nishant

photo
1

Hi Nishant,

It makes no sense to request further details after more than a year for a problem that existed at that time. You simply have to react much faster.

I then cleaned up the DB myself and solved the problem. At the time, I thought it would be good if you looked and fixed a possible error in your code. But you weren't interested, I currently work very little with Yellwofin, so I don't have an acute case.

Just close the ticket and maybe I'll get back to you if the problem occurs again. But probably not, the effort for me is too high and it also takes far too long to get help. I think I'll just correct it again myself at DB level and the error will remain in the product. It's not your fault, I know.

Have a nice weekend and thanks for your support
;) Stefan

photo
photo
1

Hello Stefan,

I hope all is well,

It is unfortunate that we were unable to reproduce the issue in our environments, but glad that at least you were able to find a workaround. We will continue to work on improving errors at DB level and that we will be here in case they need help again in the future.

Regards

Nishant

Leave a Comment
 
Attach a file