New columns not getting updated after updating View

Jose Rivera shared this problem 8 months ago
Resolved

Hi, I want to add a new column to a report, therefore I need to edit the view that grabs the data from the source. OK, I do this but the new column doesn't appear in the list of columns available when editing the report.

image


image


As you can see "jobTitle" doesn't appear in the list of columns.


The report's view was created from a SQL database and the problem manifests itself no matter what method for creating the View you use: gui or freehand sql. Copies from the modified view do not present the new column either.


System info:

Application Version:7.4.6

Build:20180712

Java Version:1.7.0_191

Operating System:Linux 4.14.72-68.55.amzn1.x86_64 (amd64)

Comments (5)

photo
1

Hi Jose,

Thanks for reaching out. What you're experiencing is expected behavior. Views are intended to contain all relevant fields and column formatting before reports are built.

Reports are built on top of a View and any new columns or changes to column formatting are not reflected at the report level because these settings are overwritten at the report level. You can still add new rows of data to existing columns, however. This is all expected and indeed preferred behavior because in a majority of cases, as one example, say you have a Date Column in your view. It'd be beneficial to have the View format be setup the most generic way. Perhaps one region would want it to display as 28-Mar-18, while another prefers Mar-28-18, etc. If you changed the formatting at the View level, you'd be forcing every user of each report to use that new formatting instead.

This may going in too deep here, but this can actually be seen in the config db, if you're curious about the internal technical workings here - The EntityTypeCode of 'REPORTFIELD' signifies the formatting has been overridden at the Report Level, which is why if you look at the 'reportfieldparameter' table, you'll see every entry with an EntityTypeCode of 'REPORTFIELD' has an associated ReportId, whereas 'REPORTFIELDTEMPLATE' does not. It's essentially a View formatting setting vs. one that's been overridden at the report level. The EntityTypeCode changing to 'REPORTFIELD' happens when you edit and publish a report, making it so that new columns and changes to column formatting will no longer be reflected in the report.

The only way to get your new columns into a report is by creating a new report. If you were just changing column formatting, you could drag out then back in the corresponding column(s), but I'm afraid there's no other option for newly added columns at the View level. Please do your best to ensure you won't need any additional columns in any of your reports so that this won't be an issue moving forward.

Hopefully this clears things up a bit. Please let me know if you have any further questions.

Regards,

Mike

photo
1

Thanks a lot Mike for your detailed answer.

I get it, I think the problem was that in our team we were considering the View as a selection of concrete columns of raw, denormalised SQL data for a report, when in reality it should be as generic as possible at the column selection.


One last question that I still have. You're saying that the only way to get the new columns into a report is by creating a new report. So, if I modify the view, you're saying that the old report will not get the new columns but new reports will? We'll try to avoid changes in the views by going as generic as possible with the queries, but in the eventual case we have to, this means we just have to create a new report, rather than creating a new view with the modified query. Am I right?

Thanks a lot,

Jose

photo
1

Hi Jose,

Thanks for your reply.

To answer your two questions: "The old report will not get the new columns but new reports will?" That is correct.

"this means we just have to create a new report, rather than creating a new view with the modified query. Am I right?" That is correct.

Considering the rest of your reply, these would be good steps to try avoiding these issues going forward.

Please let me know if you have any other questions.

Regards,

Mike

photo
1

Thanks a lot Mike, all clear now. I'll communicate this to the rest of my team.

photo
1

Hi Jose,

Great. I'll go ahead and close this case out then. I'd also just like to point you to our Wiki article on Views. It features some good info on overall View Design and is worth a read-through.

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo