SSRS reports that use a data component

  • I'm not a SSRS expert. I know enough to get myself in trouble. Anyway, if memory serves, I recall seeing somewhere a blog post, maybe a Microsoft documentation article, saying that you can use a component of some sort, to provide the data that will be used for the SSRS report. This would be ideal, I think, for two reasons. First, I really don't like VB, which SSRS must use (I think it's VB.NET, but I still don't like it).

    Secondly, and more importantly, the underlying database is still in flux. It very likely that the fields I would start designing a report with today, will change to something tomorrow. So, I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project. It will save me the aggregation of having to re-write the report.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • So use C#.

  • Where can I learn more about using what I'm calling a "component project" to retrieve the data for the SSRS report? Is "Component Project" correct or is there a different term I should search by?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • It sounds like you might be looking for Reporting Services data processing extensions, if you're trying to mediate/alter the source data outside of SQL Server or other RDBMS.

     

  • I think you are looking for RDLC reports - which are standalone Reporting Services reports.  Here are a couple of links to get you started:

    https://www.c-sharpcorner.com/article/beginners-guide-for-creating-standalone-rdlc-reports-with-s/

    https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftRdlcReportDesignerforVisualStudio-18001

    If you search: ssrs 2019 rdlc tutorial - there are more articles and videos available.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Rod at work wrote:

    I'm not a SSRS expert. I know enough to get myself in trouble. Anyway, if memory serves, I recall seeing somewhere a blog post, maybe a Microsoft documentation article, saying that you can use a component of some sort, to provide the data that will be used for the SSRS report. This would be ideal, I think, for two reasons. First, I really don't like VB, which SSRS must use (I think it's VB.NET, but I still don't like it).

    Secondly, and more importantly, the underlying database is still in flux. It very likely that the fields I would start designing a report with today, will change to something tomorrow. So, I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project. It will save me the aggregation of having to re-write the report.

    Hi, Rod,

    What are you trying to do where you think you need to execute some external code to do?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Rod at work wrote:

    I'm not a SSRS expert. I know enough to get myself in trouble. Anyway, if memory serves, I recall seeing somewhere a blog post, maybe a Microsoft documentation article, saying that you can use a component of some sort, to provide the data that will be used for the SSRS report. This would be ideal, I think, for two reasons. First, I really don't like VB, which SSRS must use (I think it's VB.NET, but I still don't like it).

    Secondly, and more importantly, the underlying database is still in flux. It very likely that the fields I would start designing a report with today, will change to something tomorrow. So, I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project. It will save me the aggregation of having to re-write the report.

    Hi, Rod,

    What are you trying to do where you think you need to execute some external code to do?

    I'm basing my idea on an article I saw in some newsletter, from some time ago. And if I remember correctly, it's also from what I recall being in the SSRS report generator tool. What I imagine would happen is this (remember I'm using Visual Studio, as that's the only tool I've ever used for generating SSRS reports) :

    • I'd have a SSRS project, where I'll put all the .RDL files.
    • Then in another project, I'll have the code to retrieve the data from the database. It will be the source for the reports in the SSRS project.

    The reason for doing dividing the functionality into separate parts is because the database schema is still in flux. So, if the report expects to put a Name somewhere on the report, it won't have to concern itself with where that data is coming from, that can be handled by the second project code. I think it will just be easier to update the data fetching code separate from the SSRS code.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Understood but that's not my question.  To be more clear, you stated that...

    I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project.

    What do those "components" actually do?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Rod at work wrote:

    Jeff Moden wrote:

    Rod at work wrote:

    I'm not a SSRS expert. I know enough to get myself in trouble. Anyway, if memory serves, I recall seeing somewhere a blog post, maybe a Microsoft documentation article, saying that you can use a component of some sort, to provide the data that will be used for the SSRS report. This would be ideal, I think, for two reasons. First, I really don't like VB, which SSRS must use (I think it's VB.NET, but I still don't like it).

    Secondly, and more importantly, the underlying database is still in flux. It very likely that the fields I would start designing a report with today, will change to something tomorrow. So, I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project. It will save me the aggregation of having to re-write the report.

    Hi, Rod,

    What are you trying to do where you think you need to execute some external code to do?

    I'm basing my idea on an article I saw in some newsletter, from some time ago. And if I remember correctly, it's also from what I recall being in the SSRS report generator tool. What I imagine would happen is this (remember I'm using Visual Studio, as that's the only tool I've ever used for generating SSRS reports) :

    • I'd have a SSRS project, where I'll put all the .RDL files.
    • Then in another project, I'll have the code to retrieve the data from the database. It will be the source for the reports in the SSRS project.

    The reason for doing dividing the functionality into separate parts is because the database schema is still in flux. So, if the report expects to put a Name somewhere on the report, it won't have to concern itself with where that data is coming from, that can be handled by the second project code. I think it will just be easier to update the data fetching code separate from the SSRS code.

    You should be able to achieve this goal by calling stored procedures from your reports, instead of using direct queries. That way, the underlying structure of the data doesn't influence the reports. The schema could change dramatically, and you'd only have to change the stored procedures, as long as the resulting dataset sent to the reports themselves remain the same. If you want to maintain the code for the stored procedures in a separate project, you can. I guess you could call SQL Stored Procedures "components", if you so desire.  🙂

     

  • It really comes down to how you want to architect your reporting solution.  If you need to embed the reports into your application - then using RDLC reports is a good option.  If you want to host the reports separate from the application then traditional SSRS reports would be a good option.

    Regardless - a changing schema won't affect a report as long as the query/procedure utilized returns the same data elements in the same order with the same signature.  That is - if column lengths are constantly changing, column names are changing - etc... then SSRS is going to break, regardless of methodology.

    One option to handle that is to use stored procedures and cast the columns being returned to the report to a large enough size that any data length changes in the underlying system will not affect the output.  For example, if column1 can increase in size - you could cast that to a varchar(100) in the code so that those changes do not change what SSRS is expecting.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden wrote:

    Understood but that's not my question.  To be more clear, you stated that...

    I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project.

    What do those "components" actually do?

    Oh, thank you for the clarification. What I want the components to do (and I'm sure I'm using the wrong terminology - I just don't know what it's called) is query the database to get the results and return them to the report to use.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod at work wrote:

    Jeff Moden wrote:

    Understood but that's not my question.  To be more clear, you stated that...

    I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project.

    What do those "components" actually do?

    Oh, thank you for the clarification. What I want the components to do (and I'm sure I'm using the wrong terminology - I just don't know what it's called) is query the database to get the results and return them to the report to use.

    I have posted several links that do exactly what you are asking.  If you want to embed the reports in your C# application so that you can 'generate' the data and associate it with a report - then RDLC reports would be the recommended solution.

    Is that not what you are asking for?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    It really comes down to how you want to architect your reporting solution.  If you need to embed the reports into your application - then using RDLC reports is a good option.  If you want to host the reports separate from the application then traditional SSRS reports would be a good option.

    Regardless - a changing schema won't affect a report as long as the query/procedure utilized returns the same data elements in the same order with the same signature.  That is - if column lengths are constantly changing, column names are changing - etc... then SSRS is going to break, regardless of methodology.

    One option to handle that is to use stored procedures and cast the columns being returned to the report to a large enough size that any data length changes in the underlying system will not affect the output.  For example, if column1 can increase in size - you could cast that to a varchar(100) in the code so that those changes do not change what SSRS is expecting.

    Jeffrey, I agree with you that RDLC files are a wonderful solution. I've used it, multiple times in my previous job, with success.

    However, I am working with some co-workers who, for whatever reason, do NOT get it. We've been "trying" to use RDLC files for the last two years in this project. It's a WPF project, written in C#. In my previous job, handling this with RDLC was easy-peasy. All you must do is include the NuGet package Microsoft.SqlServer.Types into the project. That is, it. But where I work, they've got a horrible habit of copying .DLL files from one project into another, where they reference it within the Visual Studio project, then continue development. Because we're all developers, we have Admin privileges on our own dev boxes, so this is not an issue. However, it is always an issue when deploying the app. Since it's a WPF app, we use ClickOnce to deploy the app. ClickOnce cannot be run with elevated privileges. (Or at least it cannot be run with elevated privileges by our users, none of which are admins on their boxes.) Since the ClickOnce deployment is handled by our build system, then those same coworkers blame me. They say the build failed. Doesn't matter that the build log shows a successful build, they claim it is my fault. I have written instructions as to how to use NuGet and not use the Microsoft.SqlServer.Types.dll. I have sent this to them for various projects over at least the last 5 years, I estimate between 10 and 15 times. Those coworkers still go back to putting the Microsoft.SqlServer.Types.dll into the project, resulting in the users being unable to install the app. Then those coworkers go back to blaming me for their actions.

    So, because they either cannot grok it, I'm giving up. Better to just put it into a SSRS Report server, to at least reduce much of the problem. I still suspect that at least one of them will continue to include the Microsoft.SqlServer.Types.dll into a project that doesn't use it, only because it's a habit of theirs to copy and paste DLLs from one project to another.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod at work wrote:

    Jeff Moden wrote:

    Understood but that's not my question.  To be more clear, you stated that...

    I'd rather write components in a project written in C# that yield the results that would be consumed by a SSRS report in the report project.

    What do those "components" actually do?

    Oh, thank you for the clarification. What I want the components to do (and I'm sure I'm using the wrong terminology - I just don't know what it's called) is query the database to get the results and return them to the report to use.

    In that case, I have to agree with what someone said earlier... why not just write one or more stored procedure and use the output from that/those?  I understand that your database is in a bit of flux but I can't see an ORM helping much to avoid a state of flux for reporting.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Not sure how to help then - the choices are pretty clear for SSRS related reporting.  You can build a wrapper in your application around the reporting server - with your own authentication but then reports need to be published to SSRS and the data sources defined for the reports - or you can use RDLC to embed reports directly where you have the ability to define the data source in your code and attach it to the RDLC.

    Use stored procedures as report sources - then you can change the code as needed as long as the input parameters and the columns returned don't change.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply