Questions about how people use Report module

We had the same issue. Reporting module can only show basic reports. 

I managed to create an Excel which can download the data from XTRF automatically, and create the report we need. I can help you.

Are you using periodic jobs to run these automatically? Or the API? I'd love to hear what you're doing. Thanks!

I think the Report module is very useful and you can create pretty much any type or report you like. However, the main issue is that everything has to be preconfigured and you do not have a UI where you can change things on the fly and see the results immediately (like adding/removing columns & filters, etc).

Thanks. So what types of reports do you find helpful? I'd love to know how other are using the module.

We have added many custom ones, so I am not sure which are built in anymore. 

Monthly/quarterly profit and turnover are very useful, as are turnover by salesperson, number of projects/turnover by project manager, turnover by customer, by language combination, customer satisfaction averages, projects delivered on time, amounts quoted per month come to mind. 

i agree with you Mark. The reporting module is very limited. In order to get detailed reports, we need to export views and then rework and finetune our exported formats. I'm sure this module can be made smarter as well.

I found the same thing. It seems that you can only cross tab two variables and not all of them. But maybe there is a way to use it so it is more useful.

If you want complex reports you have to use Excel or Power BI. With API automatic refreshing can be developed.

So Victor, so you connect to Power BI with the API? Or just download into Excel?

I did both of them. Power BI seems to be better for this.

I agree with Viktor, the solution is to use the Home API and pull reports from views, since XTRF reporting is very limiting. Views are also a bit frustrating because we don't have access to all columns, but as Vikto said elsewhere it is possible to develop custom columns.

Just to give you an idea of how we are doing it currently, we have a script running on a local server that uses the Home API to pull a CSV of a view at a 30 minute interval. That CSV is then stored in a DropBox folder. We then use the IMPORTDATA function in Google Docs to import that CSV into Google Sheets. We have a script in Google Sheets forcing it to refresh every 30-60 minutes. So it is almost realtime, but not quite. We then write custom reports and functions to show us information about that data and give us what we need.

Issues with this approach are that Google Sheets gets tired of refreshing so often with the large view required to do what we are doing, and the complex functions (lots of IF and IFS) slow it down. We are looking at PowerBI, but have not gone that route yet because it looks like PowerBI won't let us automate a constant refresh from a CSV source. We'd have to load it into a database or have a direct database connection. Viktor is that what you have found? We are looking at using PowerBI professional, not desktop, so the whole team can see data live.

I did it with Power BI and Power BI Desktop too. The online version seems to be better. Hourly refresh can be done but I used the Web.content datasource, not the CSV one. It's a little bit complicated but it works.

I find this to be hugely intersting. Can you elaborate on how to connect to Power BI including automatic refresh using web.content datasource?

Hi Cori, 
Why don't you use google Doc/Sheet to access XTRF's data directly via  Home API?

The delay woudl be smaller and you could reduce the number of components involved

D.

Do you have any solution/scripts to connect XTRF from google spreadsheet? XTRF addon can't be use for automatic update.

Yeah, I agree we don't have any automated connection to google sheet. We currently use the API to pull a CSV to dropbox every 15 min, and then use IMPORT() function to bring it in to google docs. The issue is, this breaks quite often. Maybe we should use Google App Scripts to import it more stabily. But the other issue is that Google Docs breaks under huge amounts of data and lots of functions, so we are looking for somehting more powerful.

What does "huge amounts of data" mean? We just started using XTRF so we have a very small initial database. And if you connect the API directly to the google data studio, are the limits higher than if using a connection to google docs?

We have to do the report based on tasks, not projects, since some tasks might not be invoiced etc. So there are over 16,000 rows, 21 columns of data, and then some columns that calculate columns off of that. We then have a summary report that gives us totals for various important stages for each month, quarter, etc. Helps us track revenue and projections.

The limits are higher, but we have run into some issues with the direct integration to Studio that we are still playing with. Right now, some of the functions and settings in Studio don't seem to be working with Dominik's connector, whereas they work with a static CSV file.  Also, the issue of how often it refreshes is something we are concerned about.

@XTRF, is a refresh of the report module in the workplan? Seems that there are a number of people that are feeling the limitations of this module.

So I've decided to take Viktor's example and run with it. I've decided that I'm not ready to connect an API for a visual reporting module, but rather I'm going to set up weekly periodic jobs to send out CSV files of data views. I'll cut and paste these into a google sheet to update a Google Data Studio report (a Google version of BI). I just started so it's rough, but I think the possibilities are pretty powerful.

I just posted a scrubbed version with fake sample data if anyone wants to take a look. 

It's located here:

https://datastudio.google.com/open/0B2A5oH47M4dHSG9NVmlVMzVFX0k

That's really pretty, but it needs a lot of administration to copy the data. I prefer the automatic version more.

Automation would be great. I'm not there yet. But I timed it... With a periodic job, it takes us less than six minutes a week to receive, open, cut and paste data, and refresh. For me, this is worth it until we decide to invest in developing the API connection.

Hi Mark,

That's looking great. I have one question: you show the number of first time clients on the report. How did you get tht count?

that was very cool.  I have something similar that I share at monthly team meetings. 

It's a bit rough. You can export the number of projects that a client has done. So, on the project report page, I am counting clients that have a number of closed projects with a value of one.

Hi Mark,

Google Studio looks pretty impressive. You’ve inspired me to connect XTRF to this platform directly via API. The very first version of the connector is ready and looks very promising. Would you be interested to test it and share your ideas/comments.?

Warm regards

Dominik

Hi Dominik, we had just started our own process of connecting to the Google Data Studio API, and would also love to test and give feedback on your integration. Let me know if it is possible.

I would love to see it and check it out! I'll have lots of questions. My biggest first question is if all of the variables that you want are available on the API. I found that a limiting factor when I connected the quote form to our website. Please do keep me informed.

Hi Dominik, we were just looking at doing the same thing. We are very interested and eager to to test your connector. Please let me know about the details on how we could do that.

Great to hear - I'll send you details on priv. Do hope  to back to this thread with a final solution soon

I think this question isn't answered yet. We are still searching for the best solution.

sorry for being too hasty

:)

I just want to give a quick update. I've been working with Dominik to test out the API connector to Google Data Studio. It works moderately well with a few hiccups and bugs. But essentially, we now have a series of reports that is based on live data from XTRF. Very useful. I'm hopeful that we'll be able to do a demonstration in the early part of 2018.

Interesting. I tried it and for some reason could not get it to work. But have not worked on the newest version. We are looking at using the API to export reports into tables in a mysql server so that we can use Power BI, since the online version doesn't refresh well on static files.

Where did it go wrong for you? Maybe I can give some suggestions? But if you're going to use Power BI, that's more powerful and hopefully should work well.