search

afs1jes / Clarins-US-IR-Migration

0 stars 0 forks source link

Transaction Report - external #65

Open AVHuntley opened 1 year ago

AVHuntley commented 1 year ago

This one had a similar problem as the non-external "Transaction Report." The SQL tables replicated existing column headers to add calculated columns on the right. SQL server is happy to just add "1" after duplicated column headers, and it'll do that on the first build. However, on subsequent builds, since the query uses "@fields", it throws an error on the non-existing columns being requested.

Fixed by making the SQL query reference specific columns, then added four empty alias columns that will get populated with formulas on build.

Image

There's room for improvement. A lot of columns are not referenced by any formulas. Those could get removed from the query.

AVHuntley commented 1 year ago

Okay this template contains the VBA for 'CreatePivot', but that call is commented out, even in Prod. This function does function in the non "...- external" version of this template ("Transaction Report")

I reenabled the CreatePivot call in the _OnBuild function. However, I also disabled client-side building. This avoids issues around saving a built model whose pivot table has already been created.