Let me preface this post by saying that I've researched this/these topic(s) in just about every way without finding much guidance.
I work for a company that hosts databases for numerous clients and provides online access to reports. We have several hundred clients across multiple servers. Each client has their own database and has a dozens of SSRS reports. I have been tasked with researching possible improvements that can be made to our processes and system. Many of these reports boil down to 12 base reports copied over and over differentiated only by the name of each client and the location of the database. I've thought about passing the name and connection string to one base report. Not sure of all the cons.
Points I've been asked to research:
- Is there a way to use the 12 or so base reports and re-use them for all clients instead of having thousands of copies?
- Can the RDL code be saved to a SQL database and retrieved, customized on the fly, and presented via the report viewer in an ASP.net page? I know that technically on the report server, reports are saved in the content table.
- Is there a way to allow the client to build ad hoc reports using our ASP.net site and save the report for future use?
So, some of my questions are:
- Are there any good resources that anyone could point me to to accomplish any of the above items?
- Can #3 above be accomplished without using Report Builder in any way. We want to use seamless branding in the application. Report Builder would work for us internally, but it doesn't seem like a product meant for a third party.
Any help is appreciated.
Question 1: You an use the dynamic connection strings in the 12 base reports. We can create a parameter to display the database servers and set the data source connection strings expression based on the parameter. In this way, each client user can select its own database server to run the report. Besides, if there are some report items being visibile for specific users, we can set the visibility of the report items based on the "=User!UserID" built-in field. For more information, please see the "Expression-based Connection Strings" section of the folloiwng article:
Data Connections, Data Sources, and Connection Strings (SSRS)
Question 2: To embed full-featured reports to custom applications, we can use the ReportViewer controls. However, the ReportViewer is only a tool to display a report and cannot run a report. So, to display a report in the ReportViewer control, the ReportViewer control must either run in remote processing mode to display a RDL report that is depoyed to a report server or run in local processing mode to display a RDLC report that is hosted by the custom application. In this issue, you want to display the RDL reports in the ReportViewer controls, the reports must deployed to a report server. For more information, please see:
Question 3: Currently, there are two RDL report authoring tools: Report Designer in BIDS, and Report Builder. If you would like add the Report Builder Click-Once button to your custom application, you can use the URL like the one below to access the Report Builder
For more information, please see:
Start Report Builder (Report Builder)
Hope this helps.
TechNet Community Support