SQL and excel functions

Answered

Guillaume Brochet

• May 07, 2021 15:42

Hi,

My scenario is this one :

An SQL query output several columns and a excel formula in the last column (referencing the first columns to compute a result).

If i copy paste  the result of my query from MS-SQL to excel, everything works fine, the formula is directly "interpreted" by excel and value displayed in the cell is as expected.

When the same query is run by dspMigrate and the excel file generated, when i open the excel file, the formula is interpreted as text, and i have to "activate" it (selecting the cell + press enter is enough, but unwanted step).

Is it possible to have dspMigrate generating excel in which cells holding formulas are already active ? 

Any hints appreciated

Best regards

Guillaume

1

• Best answer

  

  Ben Bauer

  • May 17, 2021 12:28

  Hi Guillaume,

  As you may have guessed by the long silence, this is both a tricky question and one we haven't seen in Support before. I know that our SST/DSP product uses a 3rd-party plugin to generate Excel files. However, based on what we've seen, I would guess that our application code is instructing the plugin to use the "text" type on each Excel column. Or perhaps the plugin defaults to that type when none is specified.

  I believe the best place for this request (to have Transform reports with active formulas) is the Syniti Ideas Portal. Ideas registered there can be upvoted by others to communicate their importance. As long as you log in before submitting your Idea, you'll receive email notifications whenever Syniti Product Management reviews or categorizes it.

  Finally, I will note that the simplest solution here should be to update the SQL Server view that is registered as the Report in Transform. Instead of having a column with an Excel formula, it could have a column already computed from the other column values using SQL Server functions (standard or user-defined).

  0

Please sign in to leave a comment.