Thanks Brad. We are now using this functionality and it has been very helpful. For example, a printing job was decreased from 20+ minutes to 4 minutes and we no longer need to update drop downs in excel that will save a lot of time.
We are using FM 4.4 and have utilized the functionality with Excel 2003 and 2007 as we are in the process of upgrading to 2007. Instructions were fine to follow. We were unable to create code that would allow us to change out two dimensions (org and prod) before printing. However, we simply created and utilized calculated org members that combined org and prod so that we didn't have to change out prod.
Ah, yes! You'd have to use a nested construct to iterate through each org by product combination. Something like this:
For Each o In Range("ORG")
<<your named range>>.value = o.value
For Each p In Range("Product")
<<your named range>>.value = p.value
<<run your print method>>
Obviously this code would need to be fully fleshed out to work, but it should be possible.
I'm glad you mentioned this challenge because it highlights the fact that there is certainly more than 1 way to solve a particular problem. I like your creative use of calculated orgs to accomplish what you need. We use calc orgs as well here at SAS to make some of our internal reporting easier as well.
Can VB code being used with SAS input templates? Our security is by org, if we added cc security, we could create templates that would limit cost centers populated on an input template. We have about 1200 cost centers and I would like to use code to limit # that appear to just those that are relevant for a given user.
Good question. The quick and simple answer is, yes, VB can be used within the context of forms and data entry tables.
However, based on the rest of your post I don't believe that you'll need VB to accomplish the cost center limiting.
SAS FM uses a concept of slice by security where any dimension having security and used in a slicer position on a read-only report or data-entry table will automatically filter available selections to those a user has access. So I'm thinking if you add cost center security and have cost center in a slicer position on your forms it might do the trick. Suggest you run a quick trial on a single user to see if the behavior is what you expect.
Alternatively, if you use cost center as the target hierarchy you could use author assignments to limit access only to cost centers relevant to specific users. That way the end users will see only the specific forms they need on the portal. Depending on how fluid your end user base is you may want to take advantage of the Users tab on the cost center member which allows for consistent, default form assignments that can be overridden on the form set if necessary.
Now, I'm unfamiliar with your FM setup, form set design, what you use as a target hierarchy, if you use workflow, etc...all things that influence providing the best possible insight. So if both of the above options completely miss the mark, then perhaps we could arrange a call and WebEx session to explore further.
Let me know how it goes or if I can provide further information.