September 6, 2011 at 5:48 pm
AnthonyR (9/6/2011)
Delivery dates are just one problem with how SSRS handles scheduled reports.Another problem is that it totally pollutes the SQL Server Agent Jobs with GUID-named jobs, making it very difficult to find and maintain legitimate jobs. From a DBA point of view this was extremely frustrating and left me with two choices:
1. Alter the msdb.dbo.sysjobs_view such that anything that was created by sa and has a name like a GUID was hidden from view, or renamed, or
2. Move SSRS to another SQL instance.
Being limited to one report attachment per e-mail is another. The ability to batch up a series of reports on a single e-mail would have been a very useful feature. We wrote our own report robot that does this, which also gave us a third option for the GUID-named jobs problem.
Exactly. Except that this isn't the way "SSRS handles scheduled reports". It's the way Report Manager handles scheduled reports. Report Manager is just a thin front-end layer on RS, and Report Manager is dumb. You've written your own report robot -- which is exactly what I suggested -- and you can, and in many cases should, replace *all* of what Report Manager does with your own, much better, management pieces.
SSRS lets you do this because, in doing so, you're using exactly the same SOAP API that Report Manager has access to. And more, since Report Manager certainly doesn't make use of the whole thing.
This is what we were always supposed to do, folks.
I've written more about this several places, most notably here http://spacefold.com/lisa/post/2011/07/03/Nixing-the-Report-Manager.aspx
September 6, 2011 at 7:32 pm
You've written your own report robot -- which is exactly what I suggested -- and you can, and in many cases should, replace *all* of what Report Manager does with your own, much better, management pieces.
I've had this suggested to me a few times (by Microsoft) as a "solution" when I've posted suggestions/bugs on Microsoft Connect.
The thing is that most of us do not WANT to write our own Report Manager... We want the one that comes with SSRS to be better and useful (and in some cases, just work).
If writing a better Report Manager a solution to many problems, is there one out there that one could use as a starting point that provides at a minimum the functionality of the out-of-the-box SSRS Report Manager?
I've suggested to Microsoft that if their common "solution" to any shortcomings to Report Manager is "write your own", then they should supply source code to Report Manager and let us all use it as a starting point to correct those shortcomings.
(After all, many times when you have criticism of open source projects, that's the response you get - "well, you have source, go fix it or change it yourself".)
September 6, 2011 at 7:56 pm
mannaggia (9/6/2011)
The thing is that most of us do not WANT to write our own Report Manager... We want the one that comes with SSRS to be better and useful (and in some cases, just work).
I get this, I really do -- which is why I wrote the diatribe on my blog (linked to in an earlier post) to say: Report Manager was probably a demo/proof of concept from the dev POV. It's not the product, it's an extended wizard. MS does itself a disservice, here as else where, when the wizard is mistaken for the real functionality of the product.
If writing a better Report Manager a solution to many problems, is there one out there that one could use as a starting point that provides at a minimum the functionality of the out-of-the-box SSRS Report Manager?
This is where things get really hairy, because every single adaption of/improvement of Report front-end functionality that anybody wants or has written has a different definition of what is the "minimal functionality". When you try to define it, chances are somebody is going to disagree that the thing you want to put in "as minimum" is anything like what they think the core functionality is, or should be.
As just an example, in one case we exposed the Report Manager as-is to managers to manipulate the reports and their folder/positions, etc, while hiding all this from the end-users, and custom-wrapping ReportBuilder for end-users to play with. And in another case we actually let the end-users browse the Report Manager, and navigate through it as-is, although we wouldn't dream of giving them Report Builder access, but we build custom tools on the management/administrative side. In one case, SSRS is used primarily for ad-hoc reporting, and scheduled reports are besides the point, but in the other, data-driven subscriptions are critical and must be radically extended.
Which approach is right? Neither or both.
It all depends on your business needs, which is *why* Report Manager as a basis will never be more than an example or demo or proof of concept -- no matter how much you want out-of-the-box functionality to be "at least sufficient". The problem the SSRS devs faced was: sufficient for whom and what use cases? And I bet they got *very* little realistic advice.
But *this* is where things start to get better:
I've suggested to Microsoft that if their common "solution" to any shortcomings to Report Manager is "write your own", then they should supply source code to Report Manager and let us all use it as a starting point to correct those shortcomings.
The API that Report Manager uses is fairly transparent. IMHO it plus the examples of APIU usage you'll find in the docs really is the source for the Report Manager. It allows you to pick and choose; you can leave Report Manager as-is for those functions that actually are okay, as a minimum standard, for your use cases, and then you can build out the areas that are *not* okay in your environment.
The API covers deployment, management, runtime and rendering, publishing everything you need. But realize that these are separate problem domains.
Report Manager does (I agree) a cr*ppy job of exposing all these domains and trying to be "at least there" for all people. But you're going to want to set your requirements for the individual domains separately and build them separately to do the right thing by your users and in your environment.
September 6, 2011 at 8:22 pm
Very good article, although I would agree with Jacques on shared data sets. They're point isn't to replace a view or stored procedure, but to persist between reports data sets that do not change frequently. Kinda like mini report snapshots. I'll have a babble fest about them soon.
Still, well done Andy
September 7, 2011 at 7:17 am
It all depends on your business needs, which is *why* Report Manager as a basis will never be more than an example or demo or proof of concept -- no matter how much you want out-of-the-box functionality to be "at least sufficient". The problem the SSRS devs faced was: sufficient for whom and what use cases? And I bet they got *very* little realistic advice.
Understood. And maybe I misrepresented what we actually need, because our real end user interaction with "Report Manager" is actually just the HTML Viewer. We have intranet pages that have URLs that are used to pass parameters to reports and display them in the HTML Viewer, which I consider part of "Report Manager". We don't send end users to the Report Manager, we just use the URL access to render reports.
Many of our issues are with the HTML Viewer, which can be embedded in an app using the ReportViewer Server Control, not the "Report Manager" per se.
However, to reproduce the behavior of that server control would be a somewhat large task - in particular, rendering the parameter panel.
September 7, 2011 at 7:31 am
I definitely agree that re-rendering the parameter panel as-is would be a big deal. The problem is, nobody should want to do that. 😉
It's one of the most brain-dead pieces of the report viewer, thank goodness you can hide it and put in your own. Just a few examples:
* -- the cascading mechanism (paranmeter dependency) is so inflexible that you sometimes have to params in a completely wrong order, from the user's thought process POV. And sometimes this is just because of the way the prompts are going to *look* (depending on their length)
* -- Booleans as radio buttons, rather than a checkbox?
* -- the "null" checkbox next to a date, which you can't change the prompt for at all
Yes, the parameter panel is "one size fits all", and yes it *has* to be so -- I would never blame the SSRS dev team for this, or complain about it seriously, because they never thought seriously that everybody would *use* it as-is.
Often, FWIW, I do a "mix", where I do show the parameter panel and leave a few items exposed, but hide the rest. For a similar interactivity, it's possible to put an interactive action or link right in the control to call back to re-render with a different parameter set -- I've seen somebody do this -- it's sort of a recursive "jump to report. I am not sure whether the resource use is any different.
I do not mean to argue with you -- everybody has their own pet peeves with Report Manager *and* Report Viewer, and everybody is correct -- but it just goes to show that the Report Manager ... can't be better than it is and "just fix" everybody's needs. We're expected to write stuff.
I *do* want to argue with one tedious point though (I mean I am going to be tedious here <g>): I don't think that ReportViewer is part of Report Manager at all. I consider it part of the functionality that SSRS exposed to us, and that Report Manager uses, in the same way that we can use it ourselves.
Although -- given some reasonable guesses on chronology-- this is probably isn't what really happened, the very first iteration of Report Manager could have been a single page in which some dev was *testing* ReportViewer. 🙂
>L<
September 7, 2011 at 7:41 am
It's one of the most brain-dead pieces of the report viewer, thank goodness you can hide it and put in your own.
I guess that's the problem - yes, it would be better to hide it and use our own, but now you are talking about coding a parameter panel for each report, and now you've expanded the time/money/skill set required for every report. Not to mention testing and maintenance, and re-testing/maintenance when service packs or new SQL Server versions are deployed.
I would imagine in coding a "generic" parameter panel, you would run into the same issues/compromises/quirks that MS ran into when coding the Report Viewer server control.
We also have issues with the export functions (naming of files), and to reproduce that toolbar would be a big task.
September 7, 2011 at 8:09 am
And I thought I was going nuts... Thanks for pointing those out , hope the guys in Redmond are all eyes...
September 7, 2011 at 10:14 am
mannaggia (9/7/2011)[hrI would imagine in coding a "generic" parameter panel, you would run into the same issues/compromises/quirks that MS ran into when coding the Report Viewer server control.
You can do a better job of it when your business situation is known, compared to completely unknown in the MS case. IOW, it doesn't turn out to be one parameter panel per report, it's one parameter panel for each user role, or something like that.
We also have issues with the export functions (naming of files), and to reproduce that toolbar would be a big task.
Yes -- and here the right answer is probably to wrap the extensions rather than changing the toolbar. In some cases you're supposed to be able to fix this just by configuring the report server config information for each extension, but I personally haven't had a great deal of success with that.
September 7, 2011 at 4:18 pm
jnnguyen-636073 (9/6/2011)
Crystal Reports is so far more advanced that SSRS. I'm being forced to try to migrate from CR to SSRS, but I can't find any good reason why SSRS is a better product. It's slow, non-intuitive, and did I mention slow.
You have confused complexity with capability. Crystal Reports is complicated, not advanced. And as a result, it's riddled with bugs. Read this for a laugh.
The dashboard guy is right, it's not a technology pitched at his application.
September 8, 2011 at 2:17 pm
Some that I'd like to add are: embedded code editor, allow the option to show/hide headers/footers for subreports and package an updated ReportViewer control with each new version of SSRS so that it can support all of the features in SSRS. And I'm giving a big "me too" on the Excel issues. That's one of my end users biggest complaints.
MWise
September 8, 2011 at 2:50 pm
Well put. I have spent a huge amount of time trying to figure out why I can't return to the query from a shared dataset. Understand the logic, but I do not like the idea of having to re-create a report just to add another column of data! :w00t:
September 8, 2011 at 11:04 pm
Something else I wanted to add although this an ongoing issue, not new to SSRS 2008 R2...
I've always found it disappointing/annoying/frustrating that for report images, and charts in particular, you can set DpiX and DpiY parameters (X and Y resolution in dots per inch) for the Image Renderer and the PDF Renderer but those controls are not available when using the Excel Renderer and Word Renderer. The Excel, PDF and Word renderers all call on the Image renderer internally after all.
Charts in particular suffer from this as when rendered at 96dpi, which is the default and what you get via the Excel and Word renderers, any text or labels contained within them becomes almost unreadable. Take a 96dpi chart and print it on a 300dpi, 600dpi or higher printer and it just looks plain old awful.
I have worked around this in the past by generating reports to 150 or 300dpi PDF and then for reports that need to be post edited (to add human comments, for example) having a process that converts the PDFs to Word - but this is not optimal as it often has problems with tables and can make any text of the report hard to work with.
September 11, 2011 at 7:18 am
Well hurrah for that. I heartily agree. We didn't feel like buying a whole new version of MSSQL just to get data-driven subscriptions so we tried roll-your-own. The operating system already has a fancy scheduler and the rest was dead easy. The documentation could be better, but it's adequate. Took me one day of coding and two more days for TEST and UAT. I suspect that those unhappy that Microsoft hasn't catered for their every need have spent longer than this complaining about it.
September 12, 2011 at 10:03 pm
In 2005 i was able to highlight as many columns as I wanted and copy then paste and then I would have that many more columns. I don't know what has happened in 2008 but I can only add one column at a time now?
For seme reason in Visual Studio I have to re add the Report data window "View -> Report Data" every time I open a solution. It's supposed to stay there but it doesn't.
I have had so many problems with page breaks especially with tables on lists.
Error messagmassages Security around displaying external images suck!
Has a hissy fit if you return two columns with the same name from a stored proc. Error message useless.
Cheers Brian
Viewing 15 posts - 61 through 75 (of 78 total)
You must be logged in to reply to this topic. Login to reply