Expanding the Functionality of SAS FM using VB (Brad Lawrence)

Reply
SAS Employee
Posts: 15

Expanding the Functionality of SAS FM using VB (Brad Lawrence)

Brad's presentation and Code Samples.
N/A
Posts: 0

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

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.
SAS Employee
Posts: 3

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

Hey Bob,

Glad you're deriving value from the code samples!

Couple questions if you have a moment:

1. FM 4.3 or 4.4?
2. Excel 2003 or 2007?
3. I presume the instructions were sufficient that you were able to deploy the code samples in your environment without too much trouble?

If anything needs updating or if you found anything particularly confusing, please let me know so I can refine the documentation.

Thanks again,
Brad
N/A
Posts: 0

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

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.
SAS Employee
Posts: 3

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

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>>

Next p
Exit For
Next f
Exit For

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.

Thanks for sharing!
Brad
N/A
Posts: 0

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

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.
SAS Employee
Posts: 3

Re: Expanding the Functionality of SAS FM using VB (Brad Lawrence)

Hey Bob,

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.

Thanks,
Brad
Ask a Question
Discussion stats
  • 6 replies
  • 371 views
  • 0 likes
  • 3 in conversation