First, let’s look at a standard report. Note that different sets of numbers appear in separate columns:

In this example, let’s say I wanted the information to appear in rows instead of columns, like this:

Row modifiers allow you to control the account and book code settings for each row to produce a report like the one above. Row modifiers are applied in the row format of your report.

To apply a row modifier to a row, simply double-click in column one for the row you want to change. This opens the Row Modifier window, where you can choose to apply an account, book code or attribute modifier to the selected row in your row format.

The account modifer and book code modifier drop-down lists display the available options:

Use an account modifier to override the column layout and display YTD, current period or beginning balances for the row. Account modifier also can be used to offset the base period if, for example, you want to display a number of periods before or after the base period for the report.

Use a book code modifier to display budget values instead of actuals on the report. Again, this will override what is specified in the column format. In addition, you can use an attribute filter to restrict results in the row based on values for any of the available attributes:

Row modifiers allow you to expand your reporting capabilities with additional formatting and presentation options.

BKD Technologies clients can contact our Microsoft Dynamics GP support center for further assistance in using row modifiers with Management Reporter 2012.

Register Here

Do you have difficulty creating informative and easy-to-read dashboard reports? This webinar will demonstrate how to use Microsoft Power View to do just that. Power View is a SharePoint-based application that non-programmers can use to build professional-looking dashboards. Join us to learn how to use this powerful tool, which is part of the Microsoft Business Intelligence toolset.

Learning Objectives

Upon completion of this webinar, participants will be able to:

• Describe Power View
• Identify where data for Power View resides
• Explain how to use Power View to build simple dashboard reports in SharePoint

Audience

Business analysts, controllers, CFOs, IT, financial reporting

Documents

Presentation (Coming Soon)
Group Attendance Sheet (Coming Soon)

Questions?

Contact Dane Ryals at 417.865.8701 or dryals@bkd.com.

CPE Information

Setting the Stage

The old expression is, “A picture is worth a thousand words,” meaning people can get a lot of information from a picture. Some people prefer raw data, but the beauty of pictures is they help many of us understand the data much more quickly.

Sparklines and data bars are two ways you can add pizzazz to your reports and help readers visually grasp what the data holds. Instead of just showing numbers by period, you can show charts in your table demonstrating how well the salespeople are doing.

Sparklines

A sparkline is a graphical representation of data over time. For example, you may want to show sales trend of sales over a year. The difference between a sparkline and a regular graph is that a sparkline is placed inside a table, whereas a graph stands alone.

Here is a table showing sales by month:

We want to introduce a sparkline and remove the months of sales to succinctly illustrate how the salespeople are doing.

First, delete the matrix displaying the numbers by month and replaced it with a simple table grouped by salesperson.

The next step is to add a sparkline from the toolbar; the column will be named “Performance for the Year.”

Click on Sparkline and then in the textbox for the footer. When we click in the box, a number of choices appear.

We’re going to select the line type.

We then have to add some properties to the sparkline.

These properties tell the report to graph the subtotal using the SaleMonth column.

When running the report, the system creates a point for each sale month the salesperson has data.

Notice from the original chart that Nancy B. had four months of sales, and the last month was a large increase. Compare that data to the report above, which shows fairly flat sales and then an increase in the last data point.

Notice how the sparkline tells a story for each salesperson.

Data Bars

Sparklines are just one way to visualize data. Data bars provide another way. A data bar is a way to indicate a single value at a time for each person. For example, what if you want to display a bar showing the value of the subtotal for each salesperson so you can visually compare total sales?

We’re going to start with the same report above, showing each salesperson and the total sales.

On the toolbar, select Data Bar.

Click in the textbox. In this case, I’ve created a blank textbox to hold the data bar.

The end result looks like the image below.

Another way to display the data is with the number in the same cell as shown below.

This view may be a little cluttered for your taste, but the great thing about the system is you can choose either method.

We’ve just scratched the surface on adding visualizations using sparklines and data bars to your SSRS reports. In just a few steps, you can add these visual elements to your reports. Feel free to experiment using Report Builder or Visual Studio.

For more information, please contact our Microsoft Dynamics GP Support Center.

• In order to deploy Data Mart, a functional currency must be defined in every GP company. During the Data Mart setup, it will set up all GP companies (you don’t get a choice during the installation), so the functional currency must be set up in all of them. This support article explains how to do that.

• Because companies are created during the Data Mart setup, you cannot manually add companies to Management Reporter; any new companies you create will be integrated automatically.

• If Management Reporter 2012 is running with the Data Mart provider, you will not be able to restore GP company databases without first stopping the Management Reporter services through the Configuration Console. The Data Mart continually reads data from GP’s company databases, preventing the exclusive use that a SQL restore needs.

This webinar will demonstrate Microsoft PowerPivot, a free add-in for Excel 2010 and 2013. It provides analysis capabilities on millions and even billions of data rows. BKD Technologies’ Dynamics GP professionals will walk through several examples of analysis methods using this powerful new tool.

Learning Objectives

Upon completion of this webinar, participants will be able to:

• Recognize the purpose and benefits of Microsoft PowerPivot
• Describe how to access data
• Identify how to create pivot tables and reports

Audience

CFOs, controllers, financial reporting staff

Questions?

For program information, contact Dane Ryals at 800.472.2745 or dryals@bkd.com.

Documents

Presentation 

CPE Information

Setting the Stage

The majority of readers probably have used a map. You certainly used one to plan a trip or find nearby restaurants or figure out the names of those states in the Midwest. But have you ever wanted to visualize your GP data on a map? For example, you may have customers all over the state, country or world—it would be nice to step back and see where they are so you can develop strategies for marketing to them or building sales in other locations.

We’re going to take advantage of the maps feature to walk you through building a report using sales order processing invoice history. We’ll define the data, build the report, deploy the report and view it.

Defining the Data

As any report writer knows, a very important phase in building a report is defining the data. Using SQL Server Reporting Services (SSRS) parlance, we would say we are going to define the data set, which defines the information used by the report.

For our sales map, we’re going to pull our data from an SQL view called “SalesTransactions.” This view contains all transactions entered in Sales Order Processing, posted and unposted. For our report, we will need to restrict the data to posted invoices. In addition, our report is going to provide users the ability to enter a date range.

To map the data, we’ll use the state information found in the data. The mapping feature will use that information to plot our sales data on a map. We’ll also use the “Subtotal” column, so we exclude items like sales taxes and freight.

Building the Report

For this report, we are going to use Report Builder 3.0, released with SQL Server 2008 R2. It gives report writers a lot of functionality that required the Business Intelligence Development Studio in prior versions.

Upon launch of Report Builder, we get the “Getting Started” wizard.

Look! There is a Map Wizard built into the tool. How cool is that? Click the “Map Wizard” icon.

You get a number of choices. Without going into the details of each, we are going to use the Map gallery. Notice there are a set of predefined maps from which to choose. For our report, we’ll select the USA by State Exploded option. You can click on each map type to see a preview.

Once you select the map type, you’ll be able to choose some additional options for your map, including adding Bing features. On another page of the wizard, you can select how the data should appear:

Basic Map – The name says it all. It provides a multihued data view and is good for reporting by territory or region.

Color Analytical Map – This map enables you to view data using colors. For example, you can use green for areas of growing sales and red for problem areas.

Bubble Map – This map displays data as bubbles on the map. The bubbles can vary in size based on the data. This is the map type we’ll use.

To define our data set, select the columns and filters as shown here.

The next screen is the key to mapping the data. Notice the three fields:  STATEFP, STUSPS and STATENAME. Looking at the types of data for each, you’ll quickly see we want to map to the STUSPS column, because that’s the type of data we have.

You’ll get another window to pick a theme and some other properties. After selecting your options, the last screen shows the report in design mode. Click “Run” to view the report.

You can modify and remove the components on the report. For example, you can remove the legend and make the map bigger, and you can move items around.

Deploying the Report

Once the report is designed the way you want, save it to the “Charts and KPIs” folder on the Report Server. It’s important to note that you need to ensure the SalesTransaction view is assigned to the report roles for the users who should view the report.

Using the Report

Log in to Dynamics GP.

Customize the home page and add the “Sales by State Map” report to the selected metrics.

Click “OK” and refresh your home page. Voila! Just like that, we have a map of our sales data.

It looks like we’re doing very well in some areas.

Conclusion

We’ve walked through using the mapping data visualization feature of SQL Server 2008 R2 and 2012, using Report Builder 3.0 and its built-in mapping wizard to create the report, deploying the report to the Report Server website and viewing it on our GP home page.

If your company wants to view its data on a map, consider this feature for your toolbox. Next time, we’ll examine the sparklines and data bars functionality.

Join BKD Technologies for this complimentary webinar demonstrating the latest features in Management Reporter 2012, including the Data Mart, a database for speeding up reporting, and integrating reports with SharePoint.

Presentation

To create the Startup Global Script, open the custom data dictionary using Dexterity and click on the “Scripts Resource” folder; then click the “New…” button, and a blank Global Script window will open.

Next, change the Procedure name to “Startup,” then select “System for the Core.”

In our example, we are looking to create a Field Focus trigger. The Field Focus triggers are activated by focus events occurring within the application.

You can create focus triggers that respond to the following types of objects and focus events:

Focus triggers require the developer to identify the object activating the trigger, the event to activate and the Global Script to run in response to the focus event. The fields on Dynamics GP windows have three focus events, but the push-button fields only activate triggers for the change focus event.

In our example, we will trigger on the event when the GP user clicks on the push button called “Post Button” on the Sales Batch Entry window. The Global Script BKD_Update_Sales_Dist_Before_Post will execute before the standard GP code runs.

Global Script

BKD_Update_Sales_Dist_Before_Post

<div style=”background-color: #f4f4f4; border: 1px solid gray; color: #3333ff; font-size-adjust: none; font-stretch: normal; font: 12px/12px Georgia, Garamond, Serif; height: 107px; overflow: scroll; width: 640px;”>

<pre>

local long l_item_default_actindx;

local integer virtual_key;

local long l_sequence_number;

local string l_batchnumber;

l_sequence_number = 300000;

open form Progress_Control;

‘(L) File Processed’ of window Progress_Window of form Progress_Control = “Updating SOP Sales Distributions”;

‘(L) Record Processed’ of window Progress_Window of form Progress_Control = “Please Wait……”;

‘(L) Percent Complete’ of window Progress_Window of form Progress_Control = 100;

l_batchnumber = ‘Batch Number’ of window SOP_Batch_Entry of form SOP_Batch_Entry;

{*** Begin Remove All Sales Distributions for each SOP Invoice in this Batch **}

range clear table BKD_SOP_HDR_WORK;

‘Batch Source’ of table BKD_SOP_HDR_WORK = “Sales Entry”;

‘Batch Number’ of table BKD_SOP_HDR_WORK = ‘Batch Number’ of window SOP_Batch_Entry of form SOP_Batch_Entry;

‘SOP Type’ of table BKD_SOP_HDR_WORK = 3;

clear field ‘SOP Number’ of table BKD_SOP_HDR_WORK;

range start  table BKD_SOP_HDR_WORK by number 4;

‘Batch Source’ of table BKD_SOP_HDR_WORK = “Sales Entry”;

‘Batch Number’ of table BKD_SOP_HDR_WORK = ‘Batch Number’ of window SOP_Batch_Entry of form SOP_Batch_Entry;

‘SOP Type’ of table BKD_SOP_HDR_WORK = 3;

fill ‘SOP Number’ of table BKD_SOP_HDR_WORK;

range end  table BKD_SOP_HDR_WORK by number 4;

get first table BKD_SOP_HDR_WORK by number 4;

while err() <> EOF do

range clear table BKD_SOP_Distribution_WORK_HIST;

‘SOP Type’ of table BKD_SOP_Distribution_WORK_HIST = ‘SOP Type’ of table BKD_SOP_HDR_WORK;

‘SOP Number’ of table BKD_SOP_Distribution_WORK_HIST = ‘SOP Number’ of table BKD_SOP_HDR_WORK;

‘Distribution Type’ of table BKD_SOP_Distribution_WORK_HIST = 1;

clear field ‘Account Index’ of table BKD_SOP_Distribution_WORK_HIST;

range start table BKD_SOP_Distribution_WORK_HIST by number 2;

‘SOP Type’ of table BKD_SOP_Distribution_WORK_HIST = ‘SOP Type’ of table BKD_SOP_HDR_WORK;

‘SOP Number’ of table BKD_SOP_Distribution_WORK_HIST = ‘SOP Number’ of table BKD_SOP_HDR_WORK;

‘Distribution Type’ of table BKD_SOP_Distribution_WORK_HIST = 1;

fill ‘Account Index’ of table BKD_SOP_Distribution_WORK_HIST;

range end table BKD_SOP_Distribution_WORK_HIST by number 2;

remove range table BKD_SOP_Distribution_WORK_HIST;

get next table BKD_SOP_HDR_WORK by number 4;

end while;

{*** End Remove All Sales Distributions for each SOP Invoice in this Batch **}

assign virtual_key as key for table BKD_SOP_LINE_WORK with

KEY_OPTION_ALLOW_DUPLICATES using ‘SOP Type’ of table BKD_SOP_LINE_WORK,

‘SOP Number’ of table BKD_SOP_LINE_WORK,

‘Item Number’ of table BKD_SOP_LINE_WORK with

KEY_SEGMENT_OPTION_DESCENDING;

clear table BKD_Proj_Account;

release table BKD_Proj_Account;

get first table BKD_Proj_Account by number 1;

while err() <> EOF do

range clear table BKD_SOP_LINE_WORK;

‘SOP Type’ of table BKD_SOP_LINE_WORK = 3;

clear ‘SOP Number’ of table BKD_SOP_LINE_WORK;

‘Item Number’ of table BKD_SOP_LINE_WORK = ‘Item Number’ of table BKD_Proj_Account;

range start table BKD_SOP_LINE_WORK by number virtual_key;

‘SOP Type’ of table BKD_SOP_LINE_WORK = 3;

fill ‘SOP Number’ of table BKD_SOP_LINE_WORK;

‘Item Number’ of table BKD_SOP_LINE_WORK = ‘Item Number’ of table BKD_Proj_Account;

range end table BKD_SOP_LINE_WORK by number virtual_key;

get first table BKD_SOP_LINE_WORK by number virtual_key;

while err() <> EOF do

if ‘Item Number’ of table BKD_SOP_LINE_WORK = ‘Item Number’ of table BKD_Proj_Account then

clear table BKD_SOP_Distribution_WORK_HIST;

release table BKD_SOP_Distribution_WORK_HIST;

set ‘SOP Type’ of table BKD_SOP_Distribution_WORK_HIST to ‘SOP Type’ of table BKD_SOP_LINE_WORK;

set ‘SOP Number’ of table BKD_SOP_Distribution_WORK_HIST to ‘SOP Number’ of table BKD_SOP_LINE_WORK;

set ‘Sequence Number’ of table BKD_SOP_Distribution_WORK_HIST to l_sequence_number;

change table BKD_SOP_Distribution_WORK_HIST by number 1;

set ‘Distribution Type’ of table BKD_SOP_Distribution_WORK_HIST to 1;

set ‘Account Index’ of table BKD_SOP_Distribution_WORK_HIST to ‘Account Index’ of table BKD_Proj_Account;

if ‘Extended Price’ of table BKD_SOP_LINE_WORK > 0.00 then {** Positive Quantity Debit Amount Credit to Sales **}

set ‘Credit Amount’ of table BKD_SOP_Distribution_WORK_HIST to ‘Credit Amount’ of table BKD_SOP_Distribution_WORK_HIST

+ ‘Extended Price’ of table BKD_SOP_LINE_WORK;

set ‘Originating Credit Amount’ of table BKD_SOP_Distribution_WORK_HIST to

‘Originating Credit Amount’ of table BKD_SOP_Distribution_WORK_HIST + ‘Extended Price’ of table BKD_SOP_LINE_WORK;

end if;

if ‘Extended Price’ of table BKD_SOP_LINE_WORK < 0.00 then {** Negative Quantity Credit Amount Debit to Sales **}

set ‘Debit Amount’ of table BKD_SOP_Distribution_WORK_HIST to ‘Debit Amount’ of table BKD_SOP_Distribution_WORK_HIST

+ (‘Extended Price’ of table BKD_SOP_LINE_WORK * -1);

set ‘Originating Debit Amount’ of table BKD_SOP_Distribution_WORK_HIST to

‘Originating Debit Amount’ of table BKD_SOP_Distribution_WORK_HIST + (‘Extended Price’ of table BKD_SOP_LINE_WORK * -1);

end if;

set ‘Currency Index’ of table BKD_SOP_Distribution_WORK_HIST to 1007;

{** Begin Find out Which Batch }

clear table BKD_SOP_HDR_WORK;

release table BKD_SOP_HDR_WORK;

set ‘SOP Number’ of table BKD_SOP_HDR_WORK to ‘SOP Number’ of table BKD_SOP_Distribution_WORK_HIST;

set ‘SOP Type’ of table BKD_SOP_HDR_WORK to ‘SOP Type’ of table BKD_SOP_Distribution_WORK_HIST;

get table BKD_SOP_HDR_WORK by number 1;

if ‘Batch Number’ of table BKD_SOP_HDR_WORK = l_batchnumber then

save table BKD_SOP_Distribution_WORK_HIST;

l_sequence_number = l_sequence_number + 16384;

end if;

{** End Find out Which Batch }

release table BKD_SOP_Distribution_WORK_HIST;

end if;

get next table BKD_SOP_LINE_WORK by number virtual_key;

end while;

get next table BKD_Proj_Account by number 1;{BKD_Proj_Account}

end while;

close form Progress_Control;

</pre></div>

The example code above removes the Sales Distributions for all of the SOP Invoices associated with the batch number on the Sales Batch Entry window. Next, the Sales Distributions are re-created using a companion table. Once all the Sales Distributions are added, the standard GP code will run to complete the posting process.

One important thing to consider when developing focus triggers is the table buffers, which are attached to the form when the trigger is activated. The table buffers are available until the form is closed. The table buffers are in the same state as the last time the last trigger Global Script accessed them. Since there could be other third-party applications accessing the same table buffer, you may need to clean up any table buffers before your Global Script completes executing.

After completing your customizations, you will need to create your chunk file to install the changes into your GP client.

Start | Microsoft Forecaster Data Transfer

General Ledger information:

Select the server.

Log in as “sa” using SQL authentication and click “Connect.”

Select the GP company.

Forecaster information:

Select the server and click “Connect.”

Log in as “sa” using SQL authentication and click ”Connect.”

Select the Forecaster database.

It is important to log into each database as “sa.”

Single company mapping – one company in Forecaster = one company in GP

Simple account mapping – all of the centers in Forecaster match the segments in GP

Complex account mapping – different segments between GP and Forecaster

Always enter the GL Account Start Position, even if it’s 1.

Save the mapping; next time, you will load mapping and not have to recreate it.

Select the input set in Forecaster and the General Ledger budget in GP.

Map the periods from Forecaster periods to GP fiscal periods.

Click “Clear Errors” to clear any previous errors.

Click “Clear Budget” to clear the budget before reimporting.

Click “Transfer,” and your Forecaster data should be loaded.