We are using Velixo to create a report showing current percent complete and forecasting the percent complete per month till the end of the project. The problem I have is when pulling the Budget Forecast into the spreadsheet I need the RevisionID of last update. I have pulled that field into the GI but it wont show up. It doesnt even show up, but its on the results grid. Any idea how I could get this number to show?
Answer
RevisionID on the BudgetForecast screen wont appear on a GI
Best answer by Gabriel Michaud
Hi Michael,
I just created a test GI that returns the most recent forecast revision ID by project and I am able to retrieve it just fine from Excel:
I used the self-join trick that I described in this post to get the most recent revision based on the LastModifiedDateTime; if you want to look at the Revision ID instead you can just use a simple Group By with a MAX aggregate on the Revision ID.
In Excel, I would recommend using =GILOOKUP() instead of =GI() for this particular use case. You’ll first need to setup the keys in the Workbook Options like that:
Then, you can retrieve the most recent revision for a given project this way: =GILOOKUP("Demo", "ForecastLastRevision","Revision",<project or reference to a cell>). Here’s a sample in a real report:
Do not hesitate to reply to this or to contact support@velixo.com if you need additional assistance with this request.
+11Hi Michael,
I just created a test GI that returns the most recent forecast revision ID by project and I am able to retrieve it just fine from Excel:
I used the self-join trick that I described in this post to get the most recent revision based on the LastModifiedDateTime; if you want to look at the Revision ID instead you can just use a simple Group By with a MAX aggregate on the Revision ID.
In Excel, I would recommend using =GILOOKUP() instead of =GI() for this particular use case. You’ll first need to setup the keys in the Workbook Options like that:
Then, you can retrieve the most recent revision for a given project this way: =GILOOKUP("Demo", "ForecastLastRevision","Revision",<project or reference to a cell>). Here’s a sample in a real report:
Do not hesitate to reply to this or to contact support@velixo.com if you need additional assistance with this request.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.