I'm about to start a new SSRS project. I haven't done one in a few years, so I've forgotten some of the basics. I've added the SSRS extension for Visual Studio 2019, as that's the environment I'll be developing in. The report server is on a SQL 2016 instance. When I go to create the project and search for it in VS 2019, I'm presented with two project types:
The Report Server Project is more barebones; however I seem to remember that the last time I did this some years ago, the Report Server Project would be the better choice. Am I correct about that? Or is there some compeling reason to use Report Server Project Wizard?
Kindest Regards, Rod Connect with me on LinkedIn.
November 24, 2021 at 9:44 pm
With most SQL related things, I try to avoid the wizards. I find that while they may work, it usually ends up making things in a way I don't want and could have saved a lot of time by avoiding the wizard in the first place.
SSRS reports are in the same bucket with me. The wizard MAY help with some very basic reports, but I find that even with those I can do it faster without the wizard.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 24, 2021 at 10:02 pm
I would say it depends on the type of report you are going to create - if it fits in with a general tabular or matrix type report, then using the wizard to create the outline for the report can be a good starting point. If you have something completely different - then a blank report might be easier.
The wizard is going to either create a tablix or matrix object based on your selections. I find this to be a good starting point - that I can then use to extend as needed, changing fonts - sizes - column headers - etc.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 29, 2021 at 2:55 pm
Thank you, Brian, and Jeffrey, for your great feedback. It leads me to ask would it be better to create a new SSRS project for each report? I had intended to create just 1 SSRS project in Visual Studio 2019, then create the dozens of reports that must be duplicated from the MS Access app. (They had 15+ years to create lots of reports.) But now I'm not as certain that's the way to approach this. What is the correct way to approach doing several SSRS reports? One project for them all, for a separate project for each report?
Kindest Regards, Rod Connect with me on LinkedIn.
My opinion - I do 1 solution per folder in SSRS on a per-subfolder basis.
What I mean is if my SSRS layout is like:
home/
-- Inventoy/
-- invReport1.rdl
--invReprot2.rdl
-- Warehouse/
--WarehouseReport1.rdl
I would have 1 solution for "SSRS_root" and 1 solution for SSRS_Inventory. In SSRS_root, I would have a project called Inventory. In SSRS_Inventory, I would have a project called Warehouse.
This does end up getting a bit messy to maintain, but it also helps with management of the reports.
I WOULD do 1 over-arching solution for all of SSRS, but this leads to 2 different problems:
1 - source control <-- merge conflicts become more common the larger the project gets and the more hands that touch it, so I try to keep the projects more specific to reduce the chance of a merge conflict
2 - Visual Studio doesn't allow subfolders for SSRS projects <-- With my above example, you couldn't have a project like "Inventory\Warehouse" as VS doesn't allow that. BEST you could do would be Inventory_Warehouse
Now, is this the correct way - I am not sure; I just find this way easiest to manage. Minor pain in the butt as we have multiple SSRS solutions this way AND we need to find the report on the report server prior to grabbing it from git, but it has been ages since we had a merge conflict, and it allows us to deploy directly from VS to the SSRS server. I still don't usually do that as I am RARELY updating multiple reports at once and like having the control that I do over the reports by doing them 1 at a time.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 29, 2021 at 3:59 pm
I would not use a single project for all reports - like Brian I would create separate projects that are separated either by folder or by type.
There are other reasons to combine like reports into a project, for example - you can setup and use report parts and shared datasets in a single project within VS. However, if you have any report parts or shared datasets in another project that is needed in this project - things get much more difficult.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 29, 2021 at 5:02 pm
Brian and Jeffrey, this is great feedback, thank you! I didn't know that Visual Studio (VS) doesn't work well with folders in SSRS projects. I think it would be best for me to break things up by function or type (if they're different). There's a chance that one of my colleagues will join me on this project. (At least I hope so, because the 60 reports that they want done will take me years to finish. And I'm not interested in becoming "the report writer" guy. I've not done it in years.)
Anyway, Brian your discussion about merge conflicts is a particularly good one, thank you! The coworker who may help me with this doesn't seem to grok collaboration. I like this coworker, but he has a nasty habit of never doing a "get latest" on the TFS repo. (They've been using TFS longer than I've worked there.) TFVC allows people to just stomp over the top of other people's work. I've worked with this guy, on and off, on different projects over the years. Because he never does a get latest (git's pull) he'll go along, with an old version of the VS solution on his machine. Then he'll add a class, new page or window, work on that for a few weeks, then check it all in. Since he's made changes to the solution and since his is the latest check-in, it wipes out everything done by everyone else whose worked on the application. I've witnessed pages, modules, classes, all being removed, because of this one guy's poor collaboration habits. Git isn't as forgiving as TFVC, so because of that I started this project using Git instead of TFVC. Also, having different VS projects, in different Git repos, will help prevent my coworker's bad habit from ruining other people's work.
Jeffrey, as far as shared datasets, I'm hoping that can be separated to just 1 project. I am not experienced enough to know what you're talking about concerning "report parts". Never heard of them.
Kindest Regards, Rod Connect with me on LinkedIn.
November 29, 2021 at 5:28 pm
Here is the documentation on report parts: https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-parts-report-builder-and-ssrs?view=sql-server-ver15
Essentially - this is a way of publishing just a part of a report that can be reused in other reports. So, instead of recreating the same chart in multiple reports you can create the chart once, publish it - and reuse it in multiple reports.
The problem is when you have multiple projects in VS - you cannot reference the report parts or shared datasets in the other project. You can only reference those from the report server or in the project where it was created.
With that said, a single solution in VS can contain many projects - as Brian outlined. I may or may not include all reports in a single solution - depending on the requirements. In some cases, I will have a single solution that includes both SSIS and SSRS projects which will have multiple packages/reports in each project. This allows for a single solution to contain all relevant objects - for example, the SSIS packages that extract/load data and the reports associated with that data (as well as other types).
I would not limit myself to a particular strategy - instead I would allow for solutions with just reports, solutions with just SSIS packages, solutions with mixed. But - within each project I would have one or more packages/reports that are all related so they could benefit from shared data sources, shared datasets, report parts, etc.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 29, 2021 at 5:57 pm
None of the members of my team, including myself, have any experience with writing SSIS packages. No knowledge as to how to do that. We don't (to my knowledge) even have the tools installed to do write SSIS packages. We're developers, not DBAs. And our DBAs would rather keep SSIS package creation to themselves. So, OK, if that's what they want, fine.
As an aside, I don't understand why we, the developers, are writing SSRS reports. Maybe I'm wrong about this, but I've considered report writing, either SSRS or PowerBI reports, to be something better suited for DBAs or data engineers, than developers. However, when I suggested this to my supervisor he was vehement that the DBAs would not do any report writing and that I should never mention the topic/suggestion again.
Kindest Regards, Rod Connect with me on LinkedIn.
November 29, 2021 at 6:54 pm
Personally, I agree with you Rod. I think that report writing is more of a "business analyst" role than a DBA or DB Developer role. BUT that being said, I was a DBA who did a lot of DB Development work too. I've done a lot of things in SQL and I've done a lot of things in .NET as well as SSIS and reporting.
To me, I feel that SSIS and SSRS go hand in hand (same with SSAS and PowerBI and to some levels SSRS). SSIS gets the data ready for SSRS, which is why it is nice to have them tied to each other. Nothing more annoying in report work than having your ETL process not do the T and L part needed for your report and you end up with some odd calculations baked into your SSRS report just to make the data work. OR find out your SSIS load is missing a column you need for the report, so now your report is joining 2 data sets (live data and data warehouse) so you can capture everything you need.
I think in the end, it depends on the size of the teams as to who does it... and the skillsets of the teams. My team is TINY for a company of about 500 and locations all around the globe and we do development plus DBA plus ETL plus reporting... So I get your frustration with "who should be doing this" vs "who is doing this". I know I am more of a "doer" than a "planner". You tell me what columns you need in the report and the required filters and I'll make it happen as long as the data exists... But the DBA part is what I enjoy the most from the database side.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 29, 2021 at 7:36 pm
I find it interesting that some think SSIS is a DBA tool - and others believe it is a developer tool - and yet others think in is strictly a BI tool. I believe it is more of a BI/developer tool than a DBA tool since its primary usage is to support ETL type processes. Normally, a DBA isn't involved in the processes to populate a table - rather they are more concerned with the structure (creating tables, indexes, etc.).
But that really depends on the organization - and I have had to wear many hats, and therefore have had to be involved in creating SSIS packages, SSRS reports, application code, stored procedures, etc.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2021 at 2:59 pm
Brian, I work for a state government department of over 3000 people. Its somewhere between 3000 and 3500. But organizational structure is somewhat confusing.
I don't know SSIS, but I was left with the opinion that it was a way of doing ETL better than using SQL Server's Import and Explore Tool. However, when you say that you've used SSIS in conjunction with SSRS, to produce columns not in the database/table, I'm very surprised! I'm ignorant enough of SSIS to think that it had to take data from source A, then translate/transform it and store it in source B. I'd never thought it could do something like generate some intermediate results which will be used by a report. That would be very useful. Too bad I can't use it and it's also too bad I can't work with one of the DBAs to have him/her generate the necessary SSIS for me to use. Where I work the walls between dev and ops are skyscraper high. They must never be lowered nor breached.
Kindest Regards, Rod Connect with me on LinkedIn.
November 30, 2021 at 3:03 pm
Where I work SSIS is viewed as a DBA tool. Developers are never to use it, so by practice SSIS is a DBA tool.
One of my colleagues has total disdain for SSIS. He would much rather write his own C# console app, than use SSIS, even if he knew how to use SSIS. I'm of the opinion that one should use a tool that already exists, rather then try to reinvent the wheel. But, what do I know?
Kindest Regards, Rod Connect with me on LinkedIn.
November 30, 2021 at 4:41 pm
I think my representation of SSIS was a bit off. What I meant was that SSIS would be used for the data pull from source, the transform of the data to be used by reporting, and the load into reporting tables. But there are times where report requirements change and my reporting tables are missing newly decided key columns and I need to update the database table to have the additional column, update SSIS to pull in/calculate those new columns, then have the report use those columns. In theory, the SSIS package should pull across all of the relevant data the first time around, but we all know how requirements can change...
Your environment sounds like a pain in the butt to work with. I like mine where me and my team basically do everything. If it touches SQL Server in any way, we have a hand in it. SSIS, SSRS, SSAS, DBA, .NET... we do it all.
I also agree with you 110% on using SSIS vs an in-house C# console app for a few reasons. First, it works out of the box AND is supported. Second, updates to SQL server update SSIS along with it so my packages should continue to work with updates. Third, logging. SSIS has some pretty extensive logging built in. Forth, It is fairly easy to scale and customize. It supports C# code INSIDE an SSIS package (for example) and can do some pretty complicated transforms out of the box. And if it is missing a feature, there are addons you can buy or make. CDATA has a LOT of SSIS addons for a lot of different systems (I do not work for them and haven't used their tools, just investigated them at one point). And lastly, it integrates nicely with SQL Server and SSMS. Scheduling jobs to run an SSIS package is EASY to do. Using your own C# console app, you run into a lot of risks like what if you update SQL Server and the app can't work with that version of SQL and to fix it, you need to break support for an older version? Not likely, but could happen. Or what if that C# console app doesn't support the transform you are trying to do?
Or an even worse scenario - he leaves the company and doesn't train anyone on that app. Now you (or someone) needs to convert all of the jobs set up by his app to use a supported system. You either get to learn how his tool works and support it going forward OR convert everything to a supported platform like SSIS. Hiring a replacement or even a secondary for him to support that app will also be a challenge. What if he wants vacation for a month and someone needs to modify a data pull?
Hiring a replacement for an SSIS developer is a LOT easier. To me, your colleague sounds like he is trying to create job security by making himself irreplaceable which is incredibly dangerous for a company. We ran into that with our company ages back when we needed to downsize. Layoffs happened and some people were let go and then contracted back in to offer training and support for specific systems that they were the only ones who knew how it worked. We have been working for a while to have less "in-house" software and more "out of box" solutions where possible. We will likely never be 100% out of box, but the closer we can get to that, the easier it is to hire new staff as the company grows. And the company is growing a lot too. Back when we downsized, we had 2 office locations, now we have about 10! I would not want to be the sole supporter of an in-house application that is critical to part of the company (reporting). What if IT updates .NET on all PC's and as a result, older insecure versions of .NET get removed which causes his app to break?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2021 at 3:06 pm
Thank you, Brian, for explaining more what it is that SSIS can do. It makes more sense that it would move data from tables (or wherever, because I believe it can also read some flat file formats), into a destination table specific for reports. I've got a question about that. Is this sort of approach useful for reports not often run? For example, I can imagine that if a report is run hundreds of times a minute that this approach wouldn't work. But it would work if the report were only run occasionally.
There are aspects of where I work which make it a pain. Yesterday I was coordinating the effort of a job which failed, between a third-party app that is vital to our business, the user who is the only contact allowed to talk with the vendor, the other users, the DBAs, etc. It took over 3 hours to fix something that could have been done, if the walls between groups weren't skyscraper high.
That developer who loves his C# console apps is the one guy on my team that's the biggest pain. Thinking about it I believe that, for him, C# is his hammer, and all problems are nails. He writes C# console apps for other things. For example, we've been working on replacing old MS Access apps with WPF apps. To start a new project in Visual Studio (VS), the conventional way is using a VS project template. I will admit that making a VS project template isn't easy, but it is the common way of creating new projects. This coworker decided to write a C# console app, which requires creating specifically named folders, providing dummy classes uniquely named, etc. i.e.: a LOT of ceremony just to make it work. But that's what we must do. Once I tried creating a VS project template to simply the process for my teammates. This one guy's response was to change the project created by his console app, thus invalidating the project template I'd made. So, we're back to having to do all the ceremony. And I'm not going to change the template I created, because I see this guy will only change how a project is to be created to defeat what I do. Discouraging.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply