creating a set of spreadheets from ssis (maybe ssrs?) with data and formulas

Question

he we run 2014 enterprise. we are still vetting the requirements for this project but we believe one option we may want to follow is to create a set of hundreds of spreadsheets from ssis in a folder that not only has initial budget figures for each client, but formulas too.

That way for the budgets that out finance folks want to change, the resulting horizontal and vertical totals would change automatically too. any thoughts from the community?

one other approach i'm thinking about is an ssrs subscription whose reports are saved to excel but i dont know how ssrs would save formulas.

Answer 1

i dont know how ssrs would save formulas.

It can't. An export from SSRS is a plain export of fix figures.

Answer 2

thx zoehui and olaf. Its just a regular report with 4 small tablix's all sourced by various sql server table rollups.

The tablix's are fixed sizes. one of the tablix's has totals both horizontally and vertically. its those cells that finance would like to see retotaled in excel if they save the report to excel and then adjust any of the associated detail cells in excel.

i believe this kind of thing can be done in VB and c#. I believe ssrs is extensible thru a language that either is or is similar to VB. And of course ssis is extensible via scripts in both languages.

what i might do as a poc is to create a spreadsheet template from the report, introduce my own formulas, and see if thru ssis i can update the detail cells, open the spreadsheet and see if the totals are recalc'd.

Answer 3

i got this ssis based c# command script to update a cell in my spreadsheet after i entered a sum function in one spreadsheet total cell and changed the definition of the data region to "table" using excel's insert table functionality but specifying headers yes and renaming the worksheet to "worksheet1". but i believe without HDR=NO as shown below in the script, this doesnt work.

there were a number of weird things going on. The weirdest was that i couldnt get the script to run unless i had the spreadsheet open on my pc. otherwise i got an "external table in wrong format" error. even after switching to 32 bit for debugging.

but the formula did kick in when i changed a cell and then clicked outside the region, in spite of the fact that i think excel stores what looks like numbers originating in my ssrs report as "textual" (not sure).

i would be worried about usability of a spreadsheet with one of 4 grids changed to "table" instead of what i think they call "range". it makes me wonder if other namespaces/classes (eg office) beside oledb would have been a better choice of classes to use. I ddnt know how to add the office assembly but actually started down that road first.

anyway the idea here is that i could create spreadsheets in mass using something like what i did here knowing that when the user changes values, the formulas will kick in. I am a little uneasy though.

            var cnnStr2 = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\\my file name.xlsx';Extended Properties='Excel 8.0;HDR=NO'");
            var cnn2 = new OleDbConnection(cnnStr2);

            cnn2.Open();

            string sql2 = "UPDATE [worksheet1$C21:C21] SET F1= 1.0";
            OleDbCommand objCmdSelect = new OleDbCommand(sql2, cnn2);
            objCmdSelect.ExecuteNonQuery();

            cnn2.Close();

            Dts.TaskResult = (int)ScriptResults.Success;

Answer 4

you can see at https://learn.microsoft.com/en-us/answers/questions/863679/getting-an-error-trying-to-use-office-interop-name.html?childToView=871257#answer-871257 how a solution was arrived at without oledb.

For whatever reasons (including creating an excel "table", spreadsheet needing to be open on desktop etc) , I'm much more comfortable with the solution there. It ran just fine and seems more stable to me than the oledb based approach I tried to use here.

I'm not going to pursue this any further right now but i suspect SSRS could have added the formula thru some sort of extension. But i'm even more confident that i can use ssis to generate reports, save them to excel and then thru ssis scripts add the formula instead. My goal here wa to prove that en masse I can generate ssrs based spreadsheets with a formula so that when the user goes into the spreadsheets and adjusts certain numbers, other numbers adjust automatically.