SSRS Reports As User Interface Part II

With all disclaimers out of the way, let me describe the first use I had for this idea. One report I had created for a manager allowed him to view time tracking entries for his employees. The report had required that a new table be created in the database to hold a list of all the employees that were HIS employees and thus show up on the report. He viewed this report, and I secured that it was available only to him, through the Report Manager web interface in SSRS. Initially, there was a table called [OurDB].[dbo].[Employees]. It had columns like ID, FName, LName, etc…The new table, [OurDB].[dbo].[ReportEmployees], had only one column, ID, as a foreign key to the [Employees] table. So with SQL something like this:

SELECT e.ID
,e.FName
,e.LName

FROM [dbo].[Employees] e
JOIN [dbo].[ReportEmployees] re ON e.ID = re.ID
JOIN (to some other time tracking tables)
I was able to obtain time tracking data about only those employees in the [ReportEmployees] table. I loaded the [ReportEmployees] table up with his current employees, deployed the report and he was happy. Until he hired a new developer. He asked me to add that new developer to the report. I threw the new developer’s ID into the [ReportEmployees] table and immediately began scheming of a way to avoid that detestable sort of maintenance in the future.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>