If you want to have a nice look for the matrixes that you create in Performancepoint you want to use the Matrix Styles as this gives you a nice and easy way to make sure that all the matrixes have the same formats. But what do you do when you don’t agree with the build-in styles? Say you would like to change the colors to better match your organisation.

It is pretty easy, but might also take some time to get just the right matrix style. The matrixes styles are based on the “PivotTable Style” in Excel 2007.

The first thing you need to do is to create a small PivotTable otherwise you wouldn’t get the right selects of formats that you want to change.

When you are standing in you PivotTable you have to use the  “Format as Table” in the Home Ribbon and in the Styles section to change/create a PivotTable Style:

The best thing is to select one of the existing formats and select the duplicate function in the right click menu. Now you can change the styles you need to get the Matrix Style you want for your Performancepoint matrix with a little trial and error.

In some difficult situations you might want to use NativeSQL to implement a business rule. It gives you the full power of SQL Server.
 
First you need to create the rule in the Business Modeler to host the NativeSQL business rule. You can use parametres and even use members of dimensions as parameters. When it is time to execute the business rule the user gets a nice interface where the user is able to select the parameter in the dimension.
 
In the SQL it is the memberId of the dimension that is passed into the function and they are of the type int. A start of an implementation can be seen here:
 
create proc Calc.[sp$procedure] @entity int, @scenario int, @startTimePeriod int, @endTimePeriod int as
 
It looks almost as a normal stored procedure in SQL. The only difference is that the name is a preprocessor commands and the Performancepoint translate this to a real stored procedure name, when it is created by the Performancepoint in the SQL Server. The rest of the parametres is just like a normal stored procedure.
 
You implement your stored procedure just like you normally would with transactions, select, insert and delete statements. It can be a good idea to use SQL Server Management Studio to do some testing on the implementation before you add it to Performancepoint. In this implementation it is possible to use more preprocessor commands to reference different kind of columns.
 
When it is time to deploy you have to have the NativeSQL listed as a InActive rule for you to be able to deploy it with the Business Modeler. After this is done you have to have database access to change the bit field in the “RuleSetsOrRules” table from false to true. Then you have to go back to the Business Modeler to do a deployment of the rules again (after a refresh) and it at this time the the Performancepoint creates the stored procedure and if there are any errors in the sql like “Ambiguous column name” or “Invalid column name” they can be found in the Event Viewer. If all this goes well the Business rule is ready to be executed.
 
You can see the implementation of the Business Rule by expanding your stored procedures in you application database and check under the name “Calc.bsp_GeneratedCalc_[Businessmodel label]_Rule:[Business rule label][ModelId]”.
 
A small tip: Check out the the NativeSQL implementation of “Chart_CarryForward”, “Chart_GAP_Movement”, “Chart_Batch_GAP_Movement” and “Chart_Seeding” in  the Financial Model without shares calculation for some tip on how NativeSQL can be used

What do you do when you want to use the description field in the time dimension in Performancepoint Server 2007?

The reason for this question is that I need to be able to show in an Excel input form which days are Easter each year, when is summer vacation and so on. They need this information to do better planning. The most logical answer would be to add this information to the Time dimension as it is information that is closely related to the dates in the Time dimension. You can't add a new field to the Time dimension, so I thought I use the description field which is already in the time dimension. The only problem is that you can't change the description field in the Planning Business Modeler.

I tried to change the table directly in the SQL Server and then did a Deploy Model Site in the Planning Business Modeler. You can see the result here:

This isn’t supported by Microsoft as you go and change something that isn’t change with the normal API or user interface, but it would be a nice change to have in the future 

I don’t know if this will be the final solution, but in case the solution changes or if I find out any side effects from this. I will update this blog entry.

Management Reporter is a new add-on product for Performancepoint Server 2007. It is a product that is a part of the licens that you pay, when you pay for a Performancepoint Server. Management Reporter is used to get output out of solutions like Performancepoint Planning part and other financial systems.

Management Reporter is a product that has to major parts. One part is the Report Designer and the other part is the Report Viewer. It is pretty easy to imagine what each part is used for. The product also have a backend as there are a database and a web-service that the client applications uess to communicate with the database.

Reports based on report definitions can be viewed in the viewer or exported to Excel or Reporting Services format.

In the Report Designer it is possible to create building blocks that can be used to create reports (Report Definitions). These building blocks are:

• Row Definitions
• Column Definitions
• Reporting Tree Definitions

Row Definitions

The row definitions works much like Excel, where it is possible to define which rows that is going to be in the report. For each row it is possible to edit a number of metadata that is used in processing of the row against the data:

A short description of the possiblity of each column are:

• (A) Row Code = A unique identifier of the row
• (B) Description = the description. This could be the name of an asset or something else.
• (C) Format Code = For rows that doesn't have link to financial data it is possible to use a code for the row. Like a "Total" row, calculation row, and much more
• (D) Related Formulas / Rows/ Units = Possible to add formulas that adds different other rows by using the row code. If the row is linked to a Financial data it is possible to use a Reporting Tree definition to possible filter the row
• (E) Format Override = To format the data much like you can format a cell in Excel
• (F) Normal Balance = If the number in the financial data should be reversed
• (G) Print Control = What to do when printing. Possible to remove lines in the print and more
• (H) Column Restriction = Can be used to indicate which column the data should be placed in
• (G) Row Modifier = can be used to which kind of data that is used (YTD, scenario and more)
• (H) Link to Financial Data Source = the account number(s) that is the source for the row

Column Definitions

The column definitions is used to describe which columns that are used. This could be a number of years, budget vs actual and so on:

The column definitions have a number of meta data as the row definitions:

• Header 1-3 = Used to add some header information just like in a document
• Column Type = What kind of column it is. To display financial data, where to place the description from the row definitions and more
• Scenario / Attribute category = What scenario to display data from
• Fiscal Year = The fiscal year to display
• Period = A period specification. Link to the Fiscal Year
• Time Range = If it is going to be period, year to date or more
• Formula = Formula to work with the columns, like maybe adding to column to each other
• Column Width = the width of the column
• Extra Space Before Column = To add space before the column
• Format / Currency Override =  Same as on the row definition
• Print Control = Much like the row definition
• Column Restriction = Restriction to what to display in the column
• Reporting Unit = Restrict a column to a specifict reporting tree definition
• Currency Source = where the currency information is coming from
• Currency Filter = to filter against a currency
• Account Filter = To filter the financial data
• Attrribute Filter = Restriction based on attributes
• Start Date = Restriction to specifict date
• End Date = same as start data
• Justification = the justification of the column (left, center, right)

Reporting Tree Definitions

Reporting Tree definition is used both in row and column definition, but are more visible as the use sees them in the final report. The report tree definition could be taken from the dimensions:

A short description of each of the metadata options:

• (A) Entity = The datasource
• (B) Unit Name = The name of the unit
• (C) Unit Description = The description of the unit
• (D) Structure = related to account
• (E) Row Definitions = A link to a row definition
• (F) Row Link = Link in the financial data
• (G) External Link = Path to external Excel file
• (H) External File = the name of the Excel file
• (I) Page Options = If it is printed
• (J) Rollup % = Allocate against parent
• (K) Unit Security = Which users can see the information
• (L) Additional Text = addtional text can be seen