Personal fields in Export/visual reports

[pascalbidouille]

Hello,



I am using one text field to determine some "accounts" on which work will be billed.
As I want to able to perform some extra calculation outside of project I try to export the data.
For this I am using the visual reports/export.
I select excel and select the fields I want as shown bellow.


But when excel opens I do not have access to my field "texte19" selected above only to cost(cout) and work(travail) selected on top list.
Any idea ?

[JulieS]

Custom fields are not time-phased so they will not show in the Field List picker on the Right.

However, if you have added the field to the visual report, right click on a Task in the list and select "Show Properties in Report" to show the data next to the tasks.

[pascalbidouille]

Thanks that works fine !

[JulieS]

You're welcome and thanks for the feedback.

[pascalbidouille]

Still have a problem with that.
In fact my problem is simple :
I would like to get for each day/weeks the total of work done on each account (as said before defined in a text field)
I can build a graph in MS Project that will display this, I can display the result table bellow this graph. But i do not succeed to export these data.
I expected to to it with the report export feature as explained above. Bu even if i can get this field displayed in a table, is is always dependent on a task. No way to get my text field as main field in the pivot table .

So ok, I can always copy the table and use excel to start another new pivot table. But I quite sure there should be a better solution.

[JulieS]

Again, those fields have no meaning unless they are tied to a task. You could use the Visual Report, convert the visual report to formulas (breaking the link with the OLAP cube) and then perform any work in Excel that you would like.

An alternative is the export the data through VBA. But I am not the person to help you with that, sorry. If you have skills in VBA, search for excerpts from Rod Gill's book on Project VBA on the MSDN site.

As far as being sure there should be a better solution - if I knew of one I'd share it. Project is NOT Excel and the fields and options are set by the database structure.

[pascalbidouille]

yeah, I was thinking in dealing with that with VBA. I know it quite well with excel.
But pivot tables are bit of nightmares...
What surprises me is that I can see the result table I want using a graph in reports (and showing graph table bellow it) so it would be logical to be able to be able to extract these data...
That's frustrating

[JulieS]

To the best of my knowledge, the graph tables in the reports are not timescaled. Can you post a screen shot of what you are seeing in the report?

[pascalbidouille]

Hello,

Here is on example. I used field text19.

As you can see I cat use in te graph my personal field text19 to sum up data and display the result table. But i do not find a way to extract this table.

[JulieS]

If converting the visual report (in Excel) into formulas doesn't work - the best I can suggest is to export to Excel using VBA.

I'd start with : https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx

and you can buy Rod's updated book on Amazon.

[pascalbidouille]

Hi Julie, happy you're back. you had good holidays ?

I did investigate on the vba side and manage a macro to do it.
Info in your link would probably have saved me some time, but I still succeeded in extracting data.

Here is my code if anyone may need it

Sub lire_tache_heure()

Dim tache As Task
Dim taches As Tasks
Dim ressource As Assignment
Dim projet As Project
Dim jour As Date
Dim debut As Date
Dim fin As Date
Dim book
Dim feuille
Dim l As Long
Dim c As Long

Set projet = ActiveProject

Set taches = ActiveSelection.Tasks
Set tache = projet.Tasks.UniqueID(0)
debut = tache.Start
fin = tache.Finish
Set excelappli = CreateObject("Excel.application")
excelappli.Application.workbooks.Add
Set feuille = excelappli.Application.activeworkbook.sheets(1)
feuille.cells.ClearContents
l = 1
c = 5
excelappli.Visible = True
jour = debut
feuille.cells(1, 1).Value = "tache"
feuille.cells(1, 2).Value = "ressource"
feuille.cells(1, 3).Value = "ref1"
feuille.cells(1, 4).Value = "ref2"
While jour < (fin - 1)
    feuille.cells(1, c).Value = jour
    jour = jour + 1
    c = c + 1
Wend

For Each tache In projet.Tasks
    If tache.UniqueID <> 0 Then
        For Each ressource In tache.Assignments
            l = l + 1
            c = 5
            'Debug.Print tache.Name; ressource.ResourceName;
            feuille.cells(l, 1).Value = tache.Name
            feuille.cells(l, 2).Value = ressource.ResourceName
            feuille.cells(l, 3).Value = tache.Text2
            feuille.cells(l, 4).Value = tache.Text3
            jour = debut
            
            While jour < fin - 1
                feuille.cells(l, c).Value = Val(ressource.TimeScaleData(StartDate:=jour, EndDate:=jour + 1, Type:=8, TimeScaleUnit:=4, Count:=1).Item(1).Value) / 60
                
                jour = jour + 1
                c = c + 1
            Wend
        Next
    End If
Next

End Sub

[JulieS]

Thanks. Sadly, not on holidays - just distracted by work. (No one on these forums is paid to answer questions - we are 100% volunteers). Glad you have it sorted and thanks for posting your code.