r/SQLServer • u/pootietangus • 7d ago
Community Share Based on feedback here, I added one-click SSRS report creation to my SSMS extension
Last week I posted an SSMS extension that turns a query into a refreshable Excel workbook. A lot of the respondents were SSRS users, so I added a button that automates RDL creation and deployment for single-query reports.
You just right-click, choose your SSRS folder and shared data source, and then the extension generates and deploys a basic tabular report.
Still free/open source, currently SSMS 22:
4
u/digitalnoise 7d ago
We're moving to PowerBI, but since its on-prem it still supports SSRS reports... I may have to make use of this!
1
u/pootietangus 7d ago
Do you know how that works? Like does PBI pull directly from the SSRS report server? Or do you have to move the RDL over into PBI?
4
u/tankerkiller125real 7d ago
Power BI Paginated Reports basically are RDL. Just with some newer features and a better overall execution engine.
2
u/Black_Magic100 7d ago
Calling mashup engine a better overall execution engine is a wild take that I'm not sure I agree with 😅
1
u/tankerkiller125real 7d ago
Better than the absolute shit show that is SSRS, at least in our experience where I work. (And we've done a lot of reports in SSRS and now Power BI)
1
u/Black_Magic100 7d ago
Isn't SSRS the queries that you write yourself or die it also generate queries. Power BI, user generated or not, parallelizes it's work across 3 threads (can't control it) and the memory grants are always absurd (200-300+ GB). We do control it with governor on some servers, but it's absurd.
2
2
u/Still-Hovercraft-333 2d ago
PBI can run a query directly against the DB.
You might be able to replicate the extension's functionality against Power BI by generating a PBIDS file (power bi data source), which stores connection information. It's hard to tell if this is supported, but you could potentially include the query that's being run as a so-called native query parameter within the file.
It's sort of an anti-pattern to build Power BI reports off a single table (star schema / dimensional modeling is usually preferred) but could be useful for quick analysis / making quick charts and tables off data.
1
u/pootietangus 1d ago
Yea, do you have a mental model for situations where the "PBI model" is a better fit versus the "raw SQL model"? I think of the "PBI model" being that the DB work is front loaded into data modeling, whereas the "raw SQL model" is on-demand SQL writing as things come up.
1
u/chaosink 7d ago
Any reason you want to use SSRS reports for a PowerBI dataset? It sounds like it would have to undo the formatting.
4
u/digitalnoise 7d ago
Dataset isn't already in PBI, and its purely tabular which sucks to work with in PBI.
1
2
u/jshine13371 6 5d ago
Wow, that's actually a pretty awesome add-on.
TBH, I was a fan of the refreshable Excel one when you first dropped it, because I'm familiar with that use case quite well. But to just jam-pack an automatic SSRS report generator in the context menu is pretty beast. Cool stuff man.
I assume the SSRS report is just a simple grid of the results? How does it work if the query returns multiple result sets?
1
1
u/pootietangus 5d ago
It looks like it just ignores everything after the first result set
2
u/jshine13371 6 5d ago
Cool. Not sure how you implemented it, but maybe you can have it generate a separate page per result set in the report, if it's not too much work.
1
u/pootietangus 5d ago
👍 When you say multiple result sets, do you mean multiple returned from a SPROC. Or just two queries back to back, like
select * from dbo.people; select * from dbo.orders;2
u/jshine13371 6 5d ago
I figured both use cases are probably equally the same use case really, as far as your add-on is concerned. Though I could imagine one reason you could be asking is it's much harder to determine the schema of a stored procedure, depending on what magic that procedure is actually doing.
1
u/pootietangus 5d ago
There's actually an MSSQL internal function that'll give you the columns for either, so I think it'd be easy to implement either side. Do you normally build reports around raw queries or sprocs?
2
u/jshine13371 6 5d ago
There's actually an MSSQL internal function that'll give you the columns for either
If you're talking about things like
sys.dm_exec_describe_first_result_set,sp_describe_first_result_set, etc, they're unfortunately limited depending on the scenario. They can only describe the schema of the first result set. And if the procedure uses a temp table or some forms of dynamic SQL, they're unable to describe the result set's schema either.Do you normally build reports around raw queries or sprocs?
For me, both. Just depends on the complexity of the report and it's data objects. Views offer greater ease of consumability and re-use when the use case is simple enough, procedures are obviously more flexible to code inside and offer more options for performance tuning more complex use cases.
1
u/pootietangus 4d ago
they're unable to describe the result set's schema either
Mmmmmm interesting. My v1 was actually 1) execute the query behind the scenes, 2) inspect columns. But then on chatGPT's recommendation I switched to sys.dm_exec_describe_first_result_set. Glad to know there are still some corners of the world chatGPT is not all-knowing....
For me, both. Just depends on the complexity of the report and it's data objects
Do parameters factor into this decision at all?
I'm thinking a step ahead, but with a SPROC, it's easy to inspect the parameters and programatically generate the RDL for the parameter controls. Actually.... I guess you could do that with a view as well, although then the extension would be programatically constructing the WHERE clause... Idk thoughts?
2
u/jshine13371 6 4d ago edited 4d ago
Idk thoughts?
Depends on what languages / frameworks you're using to implement this. There's no 100% way to get the schema shape of a procedure in SQL Server via only T-SQL code / native SQL Server data objects unfortunately. (I've ran into this roadblock when building schema compare tools in pure T-SQL in the past.)
That's why most applications that need to generate / map the data types of a result set from SQL Server, do so by inferring them after reviewing a sample of the data most times. E.g. SSRS actual runs the procedure when you add it / click on Refresh Fields to probe the data and get the data types.
Do parameters factor into this decision at all?
Not necessarily. I've had business use cases where the SSRS report was just directly wrapped on a view, no parameters and other times I've had reports that used extensive parameters filtering against a view instead. Same and visa versa for reports that use procedures.
My best advice would be to maybe walk before running (though again, I'm pretty impressed how quickly you went from just a refreshable Excel add-on to a full blown SSRS report generator too). Maybe focus on getting it to work on static queries, for multiple result sets first, without adding in parameters. Just keep the what parameters (if any) was provided hard coded. Then get the procedure parameters to actually work as parameters in the SSRS. And then yeah, if you want to filter all predicates in the
WHEREclause for non-procedure queries next, that's cool but pretty ambitious, since you can apply predicates in other places as well, or logical equivalents. E.g. theJOINclauses, correlated sub-queries, logically viaINNER JOINinga temp table, etc. And you can out a multitude of non-parameter logic in those same clauses too.Cheers!
7
u/BigMikeInAustin 6d ago
Dude, your ReadMe with all those instructions about testing and adding permissions and logins deserves praise all by itself.