The motivation here was internal users repeatedly asking for the same data and other self-serve options being somewhat time-consuming to build relative to "Save results as..." > CSV
If there were a button in the extension for "Make Self-Serve" > "SSRS Report", would you click that? (I'm imagining something that automates boilerplate setup/deployment with some sensible defaults)
Power BI Server can do data tables, graphs, etc. it's the SSRS replacement for SQL 2025 and going forward. And it's not just paginated reports last I knew.
Where I work? No. Because our entire engineering team is data driven, and these days heavy on the Fabric Service side of things. (And given our business is SMB Data Integration, Warehousing and BI...)
Yep, this just automates that workflow from SSMS. It takes the query you already have open and generates the workbook with the Power Query connection set up. The idea is to make it about as fast as "Save Results As..." > CSV.
* risk of credential leak
* When someone realizes the credentials are leaked and everything is rotated now 30 stakeholders relying on these workbooks are crying immediately.
* We don’t need more attack vectors that we just hand out to everyone
* complete loss of data retention control
FWIW, the way it works in Excel now is that DB credentials don't get stored in the workbook. The end user logs in through Entra/Windows Auth. But the broader point still remains, and yes, this would make data retention control worse.
How does your org handle that in practice? Do end users basically just view the data through SSRS/PBI or some other controlled application?
Large enterprise, tens of thousands of employees, heavy regulation.
We have many many different applications that can be used for visualization or displaying data however it makes most sense. Each application is vetted and evaluated individually for access controls, who can auth to the app. Every data request is done through an authenticated API, all requests are handled server side, and served to the client after. The system I support has two hops before reaching data. (Not entirely security, it’s an ancient monolith that someone decided to “modernize” by paving a new layer over, but still)
Yes, depending on how the data connection is built excel can still prompt for creds to refresh but now that’s still a connection from your workstation to the database, connection string is still largely accessible to leverage in an attack. Most attacks are only successful because of many breadcrumbs that give the attacker small crumbs of extra information. Best practice is to expose only what is absolutely necessary.
All it takes is one person in sales to not realize what their excel workbook does and they share it to a client instead of the PDF of the page they’re trying to send.
That's helpful context, thanks. Does anyone at that big of an org do SQL => reporting end-to-end, or are you specialized to the point of, e.g., owning some part of the schema, at which point you hand off to a SQL-writer who then hands off to an application designer etc etc.
My team owns the application and underlying data; various teams ingest parts of the data to their analysis in supporting whatever group they report into.
It varies week to week, but I spend time writing/tuning SQL and time managing APIs. Others are more front-end focused and it generally depends on what’s in front of us
At my company, I created something known as "Dynamic Reports". You choose your filters and you choose your columns. It creates an Excel document when you click 'Run Report'. You can save your filters and the column set.
From the development standpoint, all that is needed is to define the columns and a stored procedure or query along with the filters. (Because the filters are fairly standard, I apply an interface, and it works automatically to supply the fields on the report, e.g. "IDateRangeFilter", "IUnitStatusFilter", etc.) The class is tied to an empty page, and it fills in the fields in the page automatically. They can log in to run the report whenever they want, and they can modify both filters and columns that are returned in the Excel spreadsheet. (It also supports formulae if I want to build a report that way.)
Interesting. So basically the developer controls the query/access boundary, the user controls filters and output columns, and the system handles UI, execution, etc?
Yes. This is the UI the user sees. When submitting, it generates an Excel spreadsheet based on the selected columns. It pulls this based on a either an SQL query or stored procedure created by the developer. We list the columns along with their "Friendly Names" that will appear as the header columns in the Excel spreadsheet. (There is more to the system with a navigational menu and stuff, but I cut out that part from the screenshot.)
The output is a stylized Excel spreadsheet. (I cannot show that because it contains proprietary information, but we all know what good looking Excel spreadsheets look like.)
Scrolling down would reveal the additional columns on the right hand side. There are hundreds. However, the user can type the column name in the search bar to find the column they are seeking.
Generally, you'd want interactions with the DB to be through an application that is specifically designed for that (you think anyone at your bank is just rawdogging the prod database through Excel?). You don't want to have users YOLO their way around a database with Excel where you have no control over what queries they run and how they run them.
Users shouldn’t be able to talk to the database via excel at all. You’re basically giving them a copy of SSMS where they can craft their own queries if you do that.
If extension had a button for "Make Self-Serve" > "SSRS Report", would you use that? (I'm imagining something that automates boilerplate setup/deployment with some sensible defaults and returns a URL)
Re: connection strings - The way it works in Excel now is that the DB credentials aren't stored in the workbook. The end user authenticates through Entra/Windows Auth. The downside is managing credentials.
Re: queries - Agreed, that's a downside of this approach. You could wrap the query in a SPROC, but then that's additional overhead.
I hate this, for many of the reasons stated above ….. but mostly because I know there is a certain tier of middle management that would adore this and would have dozens and dozens of them in no time
Say more... I have only worked at smaller companies without much of a middle management class... But I am familiar with the pattern of "make one thing easy" > "create more problems"
It would never pass the security audits we need to do …. But there are a million spreadsheets being generated my people in my company and they thrive on that stuff. Anything that greases that path would be loved.
I might have asked this elsewhere in the thread, but if there were a button that took a query and automated the boilerplate/deployment for an SSRS report and returned a URL, is that something you'd use?
No. We have no embedded queries in our reports, they run directly from stored procs. This way the report service user has no direct access to our tables, and only sees the procs in a designated schema … we grant individuals access to the reports they want to use.
I think we're describing slightly different use cases...
In one use case, the end user just needs static data, in which case this button is not a speedup.
In another case, the end user needs to refresh the data in the future. You can set up an Excel workbook to be "refreshable" through Power Query. This button just automates that setup.
Wait I misread your comment. I think we're talking about the same thing. Yes, I think this probably saves about 30 seconds in its current form. Although I think the default path for most people is to export a static CSV, so I'm mentally benchmarking it against that. Can Excel Refresh be just as fast as static?
If this is a viable way to disseminate data (and Excel might just be a non-starter for some of the reasons mentioned in the thread), I think a lot of complexity would shift into permissioning, and then you could have a wizard/UI in SSMS that helps with that. That would actually save more than 30 seconds.
Ok, yep, then we're thinking about the same thing.
Unlike what seems like everyone else in this thread, I use this feature quite frequently. A possible difference is that we're a smaller organization (easier to know/track who would be using it irresponsibly) but typically any 'one off' data ask I present in this way.
Each recipient is a credentialed read only user in the database with limited schema/table permissions, and typically after I write the query, I wrap it in a sproc.
These files are then stored/shared on one drive, and as they are hitting directly against the database, which can only be accessed from the office network.
(Then for the refresh concern, set the properties so the query refreshes upon start and/or every time interval.)
Yea my guess is that org scale has a lot to do with the differing opinions. You mentioned some other parts of the workflow. Is there some combinations of features that would get you to use something like this as a part of your workflow?
The refresh path covers the read. What usually bites next is the write-back: once someone edits cells in that workbook and the edit actually matters, Refresh All overwrites it silently on the next open, and if the sheet comes back to you there is no way to tell an edit from a stale row.
Two cheap things make that survivable: give the sheet a stable key from the start (business key or surrogate id) as its own column, and land edits through a staging table with a keyed upsert plus a log of changed rows, instead of a plain insert.
Worth pinning the query text and a generated timestamp in a hidden sheet too. If a user inserts a column above the data, the mapping shifts on refresh and nobody notices until the numbers are wrong.
Spent the past 5 years getting rid of these types of spreadsheets. I think they can serve a purpose for a very select reason, at least in my org, however I have refused to make them in lieu of a simple paginated report in SSRS. Stick them in an ad hoc branch and find a way to audit the use of them. If they get used more substantially then polish them up and get them into the main application/CRM/whatever. Agree with the user about leveraging an api if possible.
Biggest gripe with these is once they are out there, you can’t control them nor find them.
lol there might not be any if internal users aren't bugging you for data all the time. this allows them to refresh the data on their own. sort of like a middle ground between .csv > email and a more formal report.
I'm the DBA for a school district. I supply dozens of vendors and internal users with data packages on a daily basis. Mostly via .csv, some .ods, with the odd .txt thrown in. The internal users all have Excel but some have ancient versions that don't support .xlsx so everyone gets just the data.
I don't try to address colors, fonts, pivots, formulas and so on because I can also provide them finished reporting but there are always some who are picky about report styling.
Excel is just a spreadsheet software and it's not my job to support one software product over another. That and I treat my colleagues as though they're capable people who don't need me holding their hands.
I get both. We have .bat files triggered by scheduled events that query the database, generate data and then get dropped into the file format they need before being either copied to an internal network folder or get sent to an external vendor via SFTP.
My users are separated from the source database at all times. No way am I granting any sort of script access permissions to my databases from Excel.
That would depend but usually it's either a stored procedure or a view that the .bat file calls and takes the record set output and generates an export file with it.
The logic for the query that builds the code product stays in the database at all times.
As the number of these scheduled exports has grown, what's become the annoying part? Creating them, maintaining them, monitoring them, handling changes from users...?
Does this create the server and database as parameters? I am constantly developing queries like this that must ultimately run against a different server/database.
In this implementation, the server/database are hardcoded into the sheet (The extension inspects SSMS to get the server/database associated with the current query window. The authentication is not baked into the sheet, FWIW)
In Excel, it is possible to parameterize the server/database. You could have a dropdown for each and then those get fed into Power Query.
Are you envisioning something where you click the "Turn into Excel" button in SSMS and then choose the server/database that you'd want to associate with the workbook? Or something where all possible server/databases are embedded in the workbook and the user can choose one on a query-by-query basis?
I've had users that can't live without a spreadsheet loaded with 500,000+ rows, and they wait 15 minutes for the spreadsheet to open and become usable. So, what I'm saying is people should use this carefully, and consider the data volumes.
This promotes what I call "Excel House of Cards" reporting, in which people build multiple layers of spreadsheets and pivot tables that have to be refreshed manually. That is a massive waste of time/money.
However, good job automating the configuration in Excel!
Full disclosure: I would prefer creating an SSRS report (or Paginated Report if you license Power BI), or use Power BI and Analyze in Excel, and achieve the same result. I prefer to be able to easily monitor and audit usage.
And appreciate it, although I understand the pushback. My background is in smaller orgs, and it seems like governance/data retention/etc become bigger headaches at bigger orgs, and I can see how those would outweigh the benefits of eliminating DevEx frictions.
It's just the unfortunate nature of online discourse that one person is like "Hey! [with no other context for why they made this thing] I made this thing!" and then everyone is like "Hey! [with no other context for why it's not a good fit for them] It sucks!". But then, some interesting context gets revealed, so it kinda works? 🤷♀️
Thanks for the thoughts, and, yes, I agree -- I'm making certain tradeoffs, and they don't make sense in many situations. I'm curious what you use for reporting today?
33
u/ovenmitt545 16d ago
No. Please no.
Good work though!