Today I want to make a post aside from the ones I have been lately. Much of this has been learned in an effort to automate a report and I want to write it down here. First, an introduction to the objective and then a choice of steps and a discussion on their tradeoffs followed by a possibly expedient solution.
The objective in this case was to generate daily automated mails of a pivot table. The table comes from a TFS query that lists work items assigned to owners with the priority of items say 1, 2, 3. The generated pivot table has the count of work items for each priority listed for every owner. This table should flow daily to a distribution list of subscribers so that they can get it in their inbox.
Although there could be several ways to do this task, I will list the things that need to be considered.
The data could change every day.
The pivot table format could change.
TFS queries may not support both the listing of the data and the pivoting.
THis is a custom report. By that I mean we cannot use the canned reports from a TFS connection because they don't pivot the data.
The items in a pivot table should be in HTML format for mail out.
The transformation of the data from tables to xslt to any other reporting or the invocation of tools on dedicated local servers are subject to maintenance and add to costs.
Ease of use such as with Excel to pivot the table or odor charts will help with changes in the format of the output over time.
Server that sends out the mails should preferably do this daily. It's alright to do it more than once and to a designated person so that one of the can be selected to send out to a wider audience. This lets some amount of manual control if desired. This is optional.
The solutions could include and in no particular order:
1) write SQL server reporting services RDL reports so that they can be viewed from the TFS server
2) write custom tool to generate the output and the mail using libraries and plugins. The executable can be scheduled to be invoked with a task scheduler
3) TFS queries can be saved and made accessible over the Internet at which point the source table is already in HTML and available via web requests
Out of the three, the tradeoffs are in terms of flexibility to changes, availability of data and report online and the cost of ownership involved. Users who are familiar with these will spot them right away and for those who are new, this could be explained as the more moving pieces there are, the more it costs to own them or transform the data flow between them.
Lastly, the solution that could be considered expedient is to have the excel application publish just the pivot table item to and not the sheets or the workbook. This item could appear directly on say a share point website which provides http access to the pivot tables. This can be requested with a web request and mailed out with a script that is invoked by the task scheduler or a reporting server.
The objective in this case was to generate daily automated mails of a pivot table. The table comes from a TFS query that lists work items assigned to owners with the priority of items say 1, 2, 3. The generated pivot table has the count of work items for each priority listed for every owner. This table should flow daily to a distribution list of subscribers so that they can get it in their inbox.
Although there could be several ways to do this task, I will list the things that need to be considered.
The data could change every day.
The pivot table format could change.
TFS queries may not support both the listing of the data and the pivoting.
THis is a custom report. By that I mean we cannot use the canned reports from a TFS connection because they don't pivot the data.
The items in a pivot table should be in HTML format for mail out.
The transformation of the data from tables to xslt to any other reporting or the invocation of tools on dedicated local servers are subject to maintenance and add to costs.
Ease of use such as with Excel to pivot the table or odor charts will help with changes in the format of the output over time.
Server that sends out the mails should preferably do this daily. It's alright to do it more than once and to a designated person so that one of the can be selected to send out to a wider audience. This lets some amount of manual control if desired. This is optional.
The solutions could include and in no particular order:
1) write SQL server reporting services RDL reports so that they can be viewed from the TFS server
2) write custom tool to generate the output and the mail using libraries and plugins. The executable can be scheduled to be invoked with a task scheduler
3) TFS queries can be saved and made accessible over the Internet at which point the source table is already in HTML and available via web requests
Out of the three, the tradeoffs are in terms of flexibility to changes, availability of data and report online and the cost of ownership involved. Users who are familiar with these will spot them right away and for those who are new, this could be explained as the more moving pieces there are, the more it costs to own them or transform the data flow between them.
Lastly, the solution that could be considered expedient is to have the excel application publish just the pivot table item to and not the sheets or the workbook. This item could appear directly on say a share point website which provides http access to the pivot tables. This can be requested with a web request and mailed out with a script that is invoked by the task scheduler or a reporting server.
No comments:
Post a Comment