New Topic

Community Topic Question Data Sources

Ryan Carrie

NA Business Team

Assigned

Category Data Sources

Product Version 7.3

Fields in use are blocking the movement of columns to files

Vincent Beets shared this question ago

Answered

Dear all,

I have replaced and added some columns in a dimension on which a view is based.

I understand that the replaced or deleted columns must be removed from any reports or replaced with active columns from the dimension.

However, I currently have three columns which, as far as I know, are not used in any report but still give an error when I try to move them from one file to another in the view builder.

The error that is given says that it is impossible to move these columns because they are used in a report. Therefore I want to check my reports' used columns again.

Is there a quick way to see which views are used by reports and which columns are used by reports? An SQL-code, if it is availible, would be perfect.

The same question

Replies (1)

Ryan Carrie ●

Hello Vincent,

Thanks for reaching out with your question. I'm happy to offer some guidance in forming queries that may assist in this process.

When running any queries directly against the Yellowfin Configuration Database it's possible to cause irrevocable data loss. This process isn't directly supported nor recommended unless otherwise advised to do so. Before running any queries against your Yellowfin Configuration Database, please make sure you have full, up-to-date backups of both your Yellowfin Configuration Database and your Yellowfin Installation folder. This way, if something goes wrong you can revert back to your current state.

The first step would be acquiring some unique ID for the field(s) affected. To do so, let's first isolate your 'ViewId' for the View you're wanting to edit.

SELECT * FROM ReportView WHERE ViewStatusCode NOT IN ('REPLACED','DELETED');

Browse through these results and located the View in question. Take note of the 'ViewId' for use in further queries.

Now, let's have a list of the Fields in that View:

SELECT ColumnName,FieldTypeCode,DataTypeCode FROM ReportFieldTemplate WHERE ViewId=$ViewID;

Where your $ViewId is the ID pulled from above. Keep note of the 'FieldTemplateId' for the fields you need to adjust. Now you can move towards report isolation.

SELECT ReportId FROM ReportField WHERE ReportFieldTemplateId IN ($Id1, $Id2);

We can then use these to identify the reports in a readable format:

SELECT * FROM ReportHeader WHERE ReportId IN ($Id1, $Id2);

This should give you a list of Reports where the fields are still being listed as active.

Let me know if this helps,

-Ryan

Hello Vincent,

Thanks for reaching out with your question. I'm happy to offer some guidance in forming queries that may assist in this process.

The first step would be acquiring some unique ID for the field(s) affected. To do so, let's first isolate your 'ViewId' for the View you're wanting to edit.

SELECT * FROM ReportView WHERE ViewStatusCode NOT IN ('REPLACED','DELETED');

Browse through these results and located the View in question. Take note of the 'ViewId' for use in further queries.

Now, let's have a list of the Fields in that View:

SELECT ColumnName,FieldTypeCode,DataTypeCode FROM ReportFieldTemplate WHERE ViewId=$ViewID;

Where your $ViewId is the ID pulled from above. Keep note of the 'FieldTemplateId' for the fields you need to adjust. Now you can move towards report isolation.

SELECT ReportId FROM ReportField WHERE ReportFieldTemplateId IN ($Id1, $Id2);

We can then use these to identify the reports in a readable format:

SELECT * FROM ReportHeader WHERE ReportId IN ($Id1, $Id2);

This should give you a list of Reports where the fields are still being listed as active.

Let me know if this helps,

-Ryan

Reply URL

Vincent Beets ●

Hi Ryan,

This was very helpfull, but did not solve the issue.

Rather, the issue has expanded.

When I add new fields from my DHW into exsisting views, I cannot move these fields to an exsisting file in the 'Prepare' stage of the viewbuilder. The error that pops up is the same as described above.

This should not be the case, as the fields are new and are not used in any reports.

Do you have any ideas what could be blocking these fields?

Kind regards,

Vincent

URL

Ryan Carrie ●

Hi Vincent,

Thanks for the reply. Have you tried adding the fields, saving the View, and then editing the View once more to try to classify the Fields into a Field Category? Are you cloning your View? Do you experience the same behavior if you make a copy of your View instead of cloning or editing?

I tried to replicate this behavior but had no success. The next step would be to acquire a backup of your Yellowfin Configuration Database and a SQL Script to generate the empty tables of your data source to try to replicate this behavior in order to raise it as a Defect.

Let me know your thoughts and how you'd like to proceed.

Thanks,

Ryan

URL

Ryan Carrie ●

Hi Vincent,

I wanted to reach out on this case and see if you've had a chance to review my reply.

Thanks,

Ryan

URL

Ryan Carrie ●

Hi Vincent,

I haven't heard back on this for some time. I'm going to mark this as answered due to inactivity.

Thanks,

Ryan

URL