C#

Project and Time Reporting with Freedcamp and Power BI

There are many project management applications which you can use. Probably the best known is Microsoft Project, the very sophisticated business application which is used by big corporations with many ongoing projects and employees. However, Microsoft Project has so many features that a user needs tens of hours to manage it fully. This means that the app is too complicated (and also too expensive) for small business or personal projects. In addition, it is a desktop app and it cannot be used on mobile devices. Most users nowadays prefer web apps which do not need to be installed and updated on their devices.

To make things easier, many overviews of these apps have been written. I did some research on these articles two years ago to find the best application for my personal projects (like blogging, tutoring, language studying etc.). First I tried Trello. Despite being the most recommended application on the market, I had missed some features there (especially a time tracking) so I decided to delete my account and to try something else. My second choice was Freedcamp and I have been using this app since then. (Just a small note here: I do not want to say that Trello is a bad app, on the contrary, I admit that it has a very nice and intuitive user interface. It just is, like any other app, not ideal for everybody.)

This Spring, developers on Freedcamp have released a beta version of API which allows you to connect Freedcamp to another application. This brings a huge amount of new possibilities of using Freedcamp. In this article, I would like to show you how to use API to get data from Freedcamp and present them in another app. I have selected Power BI, a popular analytics app. Of course, you can select any front-end app you want (such as Tableau, Qlik, Microsoft Excel etc.)

To present data in a Power BI report, I have developed a simple .NET app. The app loads data from Freedcamp and stores them in an SQL database (hosted in Azure). You can see an example of one report in a picture below.

freedcamp-powerbi-1

There are two charts in the picture. The one on the top shows time spent on different projects this year (the columns show months and colours projects). The left down picture presents how many tasks of the projects have been closed this year. The table contains a list of recently finished tasks with an information if the task was closed in time or not.

And one more example. I created a customized Eisenhower matrix with three fields: the Do First field, the Schedule field and the In Case of Free Time. I also added the pie chart with a count of open tasks by project. As you can see, this article is one of two Do First tasks.

freedcamp-powerbi-2.PNG

As you can see, I use both tasks and time in my projects so I need to load projects, tasks and time records data.

Let’s look at the app. I will share the code of the app with a brief explanation.

Though I have experience with T SQL language, I used Entity Framework in the code (mostly to gain more experience with it). Entity Framework creates database structures (mostly tables, but indexes as well) and arranges data loading and saving as the application loads and saves objects instances. (In my opinion, Entity Framework allows extensive usage of LINQ and makes the code shorter and easier to read. On the other hand, it can make debugging much more challenging.)

Freedcamp uses an API key and an API secret to secure your data. If a user wants to have the API key with no expiration date, the API key with a current timestamp hashed with API secret has to be provided. The timestamp is generated by the GetTimestamp function and then encoded by Encode function. Both key and secret are loaded from the app.config file.

Bulk generating SSRS reports by C# application

We send a specific report to our customers every month. This report is generated by SQL Server Reporting Services on SQL Server 2014. The report has a parameter called Category which determines a project for which is the report generated. This parameter is dynamically generated because we send reports only for project with some activity in last month. That’s why we cannot use SSRS standard subscription function. The only standard way how to generate the report for all projects is clicking it by hand. This is time-consuming because there are usually more than one hundred projects to report. The other problem is that user could make an error and skip some project. This error would be hard to track in further process.

I decided to solve this by C# command line application. This application loads all active projects in last month. Then it generates the report in XLS format for each project. The generated files are stored in a program’s directory. The program is quite simple as you can see from the following source code.