Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Custom Reporting with Lookup Table Drop-Down Menu MS Project 2010

Antwoord Custom Reporting with Lookup Table Drop-Down Menu MS Project 2010

  • Thursday, 31 May, 2012 7:14 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am trying to transfer macros written for MS Project 2007 over to Project 2010.  I was able to transfer the macros, tables, filters, and custom reports from my 2007 global template to my 2010 global template.  However, I am running into an issue with running the custom reports. 

    I set up lookup tables for two columns whose values are used in the custom reporting (select project name; if not project name, select sub-project name, and the report populates based on this criteria).  There are two dialog boxes that pop up during the macro, and these dialog boxes should have drop-down menu options with either the project or sub-project name. 

    However, when I run the macro (which initiates a report - VBA code to follow below), the dialog boxes pop up, but the drop-down menus are blank.  When I apply the filter on its own,  the drop-down menus in the dialog boxes are populated with the correct options.  I can't figure out why the dialog boxes are populating the correct values when I use the filter and not when I run the report.

    Here is the macro - very basic and I don't see anything in here that would cause the lookup table issue: 

    Sub Past_Due_Tasks()
    ' Past Due Tasks Macro
        projname = ActiveProject.Name
        Application.DisplayAlerts = False
        OrganizerMoveItem Type:=4, FileName:="Global.MPT", ToFileName:=ActiveProject.Name, Name:="Past Due Tasks Report"
        OrganizerMoveItem Type:=2, FileName:="Global.MPT", ToFileName:=ActiveProject.Name, Name:="Past Due Tasks"
        OrganizerMoveItem Type:=1, FileName:="Global.MPT", ToFileName:=ActiveProject.Name, Name:="Project Plan Template"
        SelectSheet
        OutlineShowSubTasks
        OutlineShowSubTasks
        OutlineShowSubTasks
        OutlineShowSubTasks
        OutlineShowSubTasks
        OutlineShowSubTasks
        OutlineShowSubTasks
        Application.DisplayAlerts = True
        ReportPrintPreview Name:="Past Due Tasks Report"
    End Sub

    Any help and/or insight would be greatly appreciated.  At first I thought this was due to a bug but I installed the service pack and it said my version of the software wasn't impacted by the service pack.  Apologies if any part of this description is confusing - I am happy to supply additional detail as necessary.  Many thanks in advance for your help!


     

All Replies

  • Friday, 1 June, 2012 2:47 PM
     
     
    Sign In to Vote
    1

    NewToProjectMacros,

    Yes there is something that is confusing. You mention something about two dialogue boxes that pop up when the macro is run. I don't see anything in your code that would generate any kind of dialogue box. All I see in the code is a transfer of a Report, a Filter, and a Table from the Global to the active file. Then the file is expanded and print preview is called.

    Just for reference, it sounds like you are working with a dynamic master file. I assume your multiple "OutlineShowSubTasks" is an attempt to expand the master and all subprojects. There is an easier way to do that, all in one fell swoop. Try

    OutlineShowTasks expandinsertedprojects:=True

    John

     
  • Friday, 1 June, 2012 3:14 PM
     
     
    Sign In to Vote
    0

    Hi John,

    Thanks so much for your reply - the OutlineShowTasks code is exactly what I was trying to do, so I will update the macro.

    I think the reason the dialogue boxes popped up is because the filter opened up as it moved over (maybe? this is just a guess - to be honest I was always confused about how it worked as I inherited this reporting/macro). 

    The dialogue boxes pop up because of this part of the filter:

    For some reason, this works when the macro runs in 2007 but not in 2010.  Hopefully this clarifies at least a bit.  Is there a way to write dialogue boxes into a macro instead of using the filter?

    Again, many thanks for your help!

     
  • Friday, 1 June, 2012 5:16 PM
     
     
    Sign In to Vote
    1

    NewToProjectMacros,

    If it is an interactive filter (i.e. the Value argument contains something like "Enter name"?), then you will get a pop-up but it won't contain a drop down, only a message with an entry box. Maybe it's an auto-filter but I don't understand you comment about the question mark box. So I still don't quite understand what you have.

    If it would be possible to send me your file, I can get a better idea of what might be happening. Zip the file(s) and send them to me at the address below. I will ask some questions. Note: if the files are larger than say 1M, we'll have to take another approach (my mail server has limited capacity).

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies and the 7th character is an alpha)

     
  • Friday, 1 June, 2012 8:05 PM
     
     
    Sign In to Vote
    0

    John,

    Is this comment: "If it is an interactive filter (i.e. the Value argument contains something like "Enter name"?), then you will get a pop-up but it won't contain a drop down, only a message with an entry box" true even if I have lookup tables enabled for the columns?  I tried to post a picture in my previous response but it looks like it didn't show up. 

    I do have an interactive filter but the columns where I use the interactive filter have a limited number of possible entries (e.g., "test project" in Text2 and "test-sub-project1" and "test-sub-project2" in Text3) because of the lookup tables I created. 

    My issue is that the lookup table values will populate if apply the filter, but don't populate when I run the macro (which also applies the filter).  It works in 2007, but not in 2010, even though the macro is identical.  I can't figure out why it's working in 2007 and not in 2010. 

    I can see about sending this to you but am doing this at work and trying to avoid that from a confidentiality/security perspective if at all possible.  I was hoping there was some quick fix I was missing but it doesn't sound like that's the case...?  Bottom line, I guess, is if I have an interactive filter that is part of a macro, how do I get lookup table values to appear in the dialogue box that pops up when the macro runs?

    Hopefully this provides a little extra clarity but if you (or others) need to see the file to get a solution, I'll set up an email account and create a generic version that I can send.

     
  • Friday, 1 June, 2012 8:43 PM
     
     
    Sign In to Vote
    0

    NewTo,

    Whether or not you have customized extra fields with a look-up table has nothing to with an interactive filter. The filter will ask for some type of input from the user. If you enter a value that you already know is one of the look-up values then you will see some results from the filter. However you could just as well enter a value into the interactive filter entry box that is not one of the look-up values and you will get a null set from the filter (i.e. no tasks).

    What I don't quite understand is what you mean by, "... the lookup table values will populate if I apply the filter...". Lookup table values "populate" when the user selects the customized field. The values appear and the user selects one. It has nothing to do with a filter unless you activate an autofilter.

    With regard to sending you file, I fully understand the proprietary issue. A generic sample file would be fine.

    John

     
  • Tuesday, 5 June, 2012 9:35 PM
     
     Answered
    Sign In to Vote
    0

    Hi John,

    Apologies for the delay - I am sending the file momentarily.  I'm sending it to jensenlj, as it looks like you meant the @ was the 8th, not 7th, character?  Let me know if not and I'll re-send.

    Thank you again for your help!
     

     
  • Sunday, 24 June, 2012 4:21 PM
     
     
    Sign In to Vote
    0

    NewToProjectMacros,

    Update 6/24/12: I have forwarded this issue to a contact at Microsoft. I don't know if anything will happen as a result, but if it does, I'll post back.

    John

     
  • Friday, 17 August, 2012 4:25 PM
     
     
    Sign In to Vote
    0

    I was wondering if there had been any update on this?  I am currently experiencing a problem with the drop down lists in custom reports as well.  I create my custom field, filter, then report, and the list is blank, however the drop down list for the filter is populated.

    Thanks!


    Shane

     
  • Monday, 20 August, 2012 2:32 PM
     
     
    Sign In to Vote
    0

    Hi Shane,

    To my knowledge, no updates so far - we were never able to resolve the issue.  The closest "resolution" was that you can type in the value in the drop-down menu dialog box, but I realize that's not in any way ideal. 

    Hope that helps!

     
  • Monday, 20 August, 2012 5:10 PM
     
     
    Sign In to Vote
    0

    Thank you!  I've been wracking my brain along with a co-worker to get that to function properly.  Guess we'll wait for SP2 lol.

    Shane