Ricky Spears has a great post on opening SharePoint links in a new window. Once again the Content Editor Web Part and javascript to the rescue.
Widening (Fixing) the Tree View for Document Libraries
I find the Tree View a beautiful idea which leaves users unsatisfied. It is too narrow to be very functional. Using SharePoint Designer and a custom Master Page it is quite easy to fix this problem. So take a minute and follow along.
Master Page
You will need a custom Master Page, so if you have one, skip to the Tree View below. If you need to create one, Greg Galipeau does a great job explaining how to create a Master Page here. So take some time to read, understand and create your own custom Master Page. Then move on to the Tree View section below.
Tree View
To change the width of the Tree View follow these few simple steps:
- Using SharePoint Designer open your site.
- Then open your custom Master Page by navigating in the Folder List to _catalogs > masterpage > customMasterPage.master
- Find the SharePoint:SPRememberScroll tag.
- Change the width from 150px to 200px or your preference.
- Click the save button.
- Refresh your SharePoint page
Voila the Tree View becomes wider and more useful.
Exposing Reporting Services Reports to the Internet
By using the ASP.NET ReportViewer control on a web page exposed to the internet, it is possible to display Reporting Services reports. The trick is in turning on Anonymous Access on the ReportServer Virtual Directory and creating the anonymous user as a Reporting Services user and granting that user permission to view the reports you want to share.
In the ReportViewer control configure the ReportView Tasks as follows:
- Choose Report:
- Report Server Url: the path to the ReportServer Virtual Directory
- Report Path: the path to the report beginning with ‘/’
On the web server hosting Reporting Services configure the ReportServer Virtual directory as follows:
- Turn on Anonymous Access
- Remember the Anonymous Access user
In Report Manager do the following:
- Configure the Anonymous Access user as a Reporting Services user
- Grant that user access to the necessary folders and/or reports
Deploy your web page with the ReportViewer control to your internet server and you are good to go.
Displaying Teams in Data Manager
Displaying teams in Data Manager is simply based on the current user’s Team access. Change their Team membership using BPC Administration.
BPC Drillthrough for CMS Actuals
Actuals Activity
Current year Actuals data is pulled from 1 table:
GTRAN – G/L Transactions
For previous year Actuals, data is pulled from:
GTLYN – Last Year G/L Transactions
Opening Balances
Retrieved from GLMT
FinanceDrillThrough Table
For Activity and Opening Balances, data is written to the FinanceDrillThrough table which looks like this:
| Column_name | Type | Length | Nullable |
| Entity | nvarchar | 64 | no |
| Account | numeric | 9 | no |
| Period | numeric | 5 | no |
| JournalType | nvarchar | 2 | yes |
| TranDate | datetime | 8 | yes |
| TranSource | nvarchar | 4 | no |
| Amount | numeric | 9 | no |
| RefNum | nvarchar | 20 | yes |
| RefDesc | nvarchar | 60 | no |
| RevEntry | nvarchar | 2 | yes |
| Century | numeric | 5 | yes |
| Year | numeric | 5 | yes |
| PostDate | datetime | 8 | yes |
| Batch | numeric | 5 | yes |
| MinorSalesCode | nvarchar | 6 | yes |
| MajorSalesCode | nvarchar | 6 | yes |
Rollups
The GetFinanceDrillThrough stored procedure calls functions that explode rollups into base level members.
Pulling Data From CMS into BPC
Several Files (tables) in CMS are used when pulling data.
MAST – G/L Chart of Account Header
GLMT – G/L Chart of Account Totals for Year
Most of the data comes from GLMT. That is, activity and opening balance comes from GLMT. Properties like account type and company are retrieved from MAST.
Retrieving data from any system requires the same thing. Pulling Company(Entity), Account, Account Type (P&L, BS), Opening Balance and Activity. In some cases, Company is broken up as well such as Payroll and Operations. In those cases that breakdown must be retrieved as well.
BPC Security in a Nutshell
Security is broken down to 4 aspects:
- Users
- Basic component of security. User, Email, IM, etc.
- Teams
- Basic grouping of users
- You can assign Task and Member Access Profiles to Teams
- Task Profiles
- Application related security
- User/Team Activities and roles specified here
- There is a finite number of Tasks configured in BPC. See Task Profile Descriptions in the BPC Administration Help
- Member Access Profiles
- Dimension and member related security
- Example, you can specify which periods are ready to receive forecasts
Security Assignment:
Users–>
Task Profiles
Teams–>
Users–>
Member Access Profiles
Teams–>
Changing Account Rollups in BPC
To change the rollup for an account in BPC do the following:
- Change PARENTH1 on the account to the new rollup
- Process the dimension (Do a Full Process)
- Make the Application available
- Click on the Noble node
- Click on Set application set status
- Select Available
- Click Update application set status
- Refresh dimension members on the client.
- eTools -> Refresh Dimension Members
This will probably work for any dimension, not just accounts.
Find Which Table(s) Contain a Column and the INFORMATION_SCHEMA Namespace.
To find which table(s) contain the PersonID column try this:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%PersonID%’
In the AdventureWorks DB this is what you see:
So of course you can search for any column, not just PersonID.
Another interesting tidbit is that any ANSI compliant DBMS provides the INFORMATION_SCHEMA namespaces. From this namespace you have access to the metadata on any DB object. You can look up information on Stored Procedures and Functions using INFORMATION_SCHEMA.ROUTINES or use any of the many different objects within this namespace: COLUMNS, ROUTINES, CHECK_CONSTRAINTS, PARAMETERS, TABLES, etc.
Enjoy
Modifying Forecast Input Templates
OPEN INPUT TEMPLATE FOR EDITING
1. eTools -> Open Dynamic Templates
2. Input Schedules -> 09 Input Templates
3. Open ‘Forecast Input Template 2009.xlt’
UPDATE FORECAST TEMPLATE TO REFLECT NEW MONTH OF ACTUALS
1. Change Cell A4 and BA6 to the new FCST Month
MODIFY EVGTS FORMULAS
1. If we are modifying to prepare for October Month End, then we will copy Cells Q15:Q233 to R15:R233
2. Update Cells R12 and R13 to say ACT and OCT in Black Font
MODIFY EVSEND FORMULAS
1. If we are modifying to prepare for October Month End, then we will copy Cells BI15:BI233 to BJ15:BJ233