Page 8 of 25« First...45678910111213...2025...Last »

The Dynamics GP Blogster: Microsoft Convergence New Orleans …

The Dynamics GP Blogster. “Your Microsoft Dynamics GP world without barriers” …. No comments: Post a Comment · Newer Post Older Post Home. Subscribe to: Post Comments (Atom

Original post:
The Dynamics GP Blogster: Microsoft Convergence New Orleans …

March 17, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 112

Microsoft Dynamics Salary Survey 2013

The folks over at Nigel Frank have published a link to their 5th annual Microsoft Dynamics Salary Survey for YOU to participate and express your views. You can find the survey here:

Microsoft Dynamics Salary Survey

This year the survey added some new interesting questions allowing for more insight into retention and recruitment as well as the general Microsoft Dynamics labor market trends. The report is due to be revealed at Convergence 2013, so you won’t want to miss out.

In addition, it seems like you can enter into the draw for a Microsoft Surface Pro, so really, there’s nothing to lose. Go fill out your survey now!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Originally posted here:
Microsoft Dynamics Salary Survey 2013

March 11, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 194

Visual C++ Runtime Failed while configuring Reporting Extensions for CRM 2011 with SQL 2012

Hi Folks, With the latest update of CRM 2011 we have compatibility with SQL 2012 server. I have got a chance to install CRM application on top of SQL 2012. If you are successful in installing CRM then trying to configure Reporting Extensions for CRM you might encounter the following error:  “Installation of Microsoft Visual C++ Runtime failed.

Originally posted here:
Visual C++ Runtime Failed while configuring Reporting Extensions for CRM 2011 with SQL 2012

March 2, 2013 · turlapativishnu · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 143

SQL view for sales quantities by item by site by year

Below is another variation on my view for sales by item by year and view for sales quantities by item by year. This one shows the total item quantity sold by site by year. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2006 through 2013 as well as adding an overall total column at the end. You can easily change the years or add new ones by following the example in my code.

Some additional resources:

CREATE VIEW view_Sales_Qty_by_Item_Site_Year
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_Qty_by_Item_Site_Year
-- Created Feb 28, 2013 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns total sales quantities fulfilled (invoices less returns) for each item
--    by site by year
-- Only posted invoices and returns are included
-- Quantity is calculated by multiplying by QTYBSUOM column in case other UofM's are
--    used on transations
-- Voided transations are excluded
-- Item Description is taken from Inventory Item Maintenance for all inventory items
--    and from SOP line items for non-inventory items
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT
D.ITEMNMBR Item_Number, D.Item_Description, D.LOCNCODE Site_ID,
sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) 2006_Qty,
sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) 2007_Qty,
sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) 2008_Qty,
sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) 2009_Qty,
sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) 2010_Qty,
sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) 2011_Qty,
sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) 2012_Qty,
sum(case when year(D.DOCDATE) = 2013 then D.Qty else 0 end) 2013_Qty,
sum(D.Qty) Total_Qty

FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR, SD.LOCNCODE,
 coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
 CASE SD.SOPTYPE
 WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
 WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
 END Qty
 FROM SOP30200 SH
 INNER JOIN
 SOP30300 SD
 ON SD.SOPNUMBE = SH.SOPNUMBE
 AND SD.SOPTYPE = SH.SOPTYPE
 LEFT OUTER JOIN
 IV00101 I
 ON I.ITEMNMBR = SD.ITEMNMBR
 WHERE SH.VOIDSTTS = 0
 AND SH.SOPTYPE IN (3,4)
 AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D

GROUP BY D.ITEMNMBR, D.Item_Description, D.LOCNCODE

GO
GRANT SELECT ON view_Sales_Qty_by_Item_Site_Year TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, Sales Order Processing, SQL code

Visit site:
SQL view for sales quantities by item by site by year

February 28, 2013 · Victoria Yudin · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 268

GPUG Georgia Regional Chapter Meet Up

I did not want this to pass me by.

Last Tuesday, February 12, 2013, I had the opportunity to participate as a guest speaker at the GPUG Georgia Regional Chapter meeting, hosted at Prosys Information Systems, a Microsoft Dynamics GP customer.

Our chapter meeting had two topics being discussed for the day:

  • Advanced Analytics for Microsoft Dynamics GP. This first topic was very exciting and was conducted by Dwight Specht, COO of IBIS, Inc. Dwight demonstrated the power of Analysis Cubes for Microsoft Dynamics GP and showed how Excel can be leveraged as a client to interact with these.

  • Where to Go for Answers? I championed this topic and explored some of the options for finding information within the Microsoft Dynamics GP community with all attendees. We had a great deal of discussion around the use of social media from a corporate and personal perspective and how it can be leveraged as a tool for building your professional network and obtaining support.

All in all, it was an exciting afternoon and attendees had a chance to interact with the presenters and discuss how they are taking advantage of analytics and social media in their organizations. I can’t wait for next quarter’s meeting.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

See the original post:
GPUG Georgia Regional Chapter Meet Up

February 18, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 230

The Dynamics GP Blogster’s best articles of 2012

It’s that time of the year again! Time to say goodbye to the outgoing year, 2012, and receive the new one, 2013, making all sort of resolutions that we never seem to be able to achieve and with good measure, since most of us are simply consumed with the daily grind that lifting our heads up to take a breather and see the forest from the trees seems difficult and sometimes downright impossible.

2012 however was a very good year and gave me tons of stuff to write about. In retrospect, 3 conferences, 2 virtual conferences, the release of Microsoft Dynamics GP 2013 and the Web Client are simply a subset of the activities that allowed me to write some really cool stuff that you expressed through your comments to enjoy. Without further due, here is a list of the “best articles of 2012″ written on this site (in no particular order):

1. Microsoft Dynamics Convergence 2012 Houston. Convergence Houston in March of 2012 was a very cool experience and probably one that I will never forget as I was able to meet up with my good friend David Musgrave and his wife, Jennifer. I also had the opportunity to share some time with some of my customers in attendance. As always, the good part is meeting tons of people and the thrill of presenting.

2. Microsoft Dynamics GP Technical Airlift 2012. In the realm of cool, the Technical Airlift in Fargo, North Dakota was a very awesome conference, but also marked the first time I presented without my compadre, David Musgrave. The Tech Airlift also demanded tons of prep work and was built upon cooperation between Microsoft and GPUG’s GPPC and featured a record number of partners in attendance. I also liked the fact that I met up with some of my fellow MVPs.

3. GPUG Summit 2012 Seattle. Seattle has this aroma of coffee mixed with sea all over the city and I have to say the GPUG summit was a very charming conference, with lots of topics for the attending crowd. Seattle, Seattle…

4. Windows 8 and the Microsoft Dynamics GP Web Client Series. Okay, so I confess. I was a nuisance to the Escalation Engineering team testing the Web Client. You see, the Web Client was not supposed to even be working on Windows 8 as a compatible installation platform, but after months of annoying I had my way with this. The best part though was working with the team of Aaron Donat and Jason Lech and understanding the nuances of the deployment process of GP Web Client. Thanks guys for putting up with me and for making this happen.

5. Adding Customer Item User Defined fields to SOP Invoice. I love the Support Debugging Tool and how it can make live easier for things that seem unconceivable. How about this cool article on getting customer item user defined fields onto the SOP invoice? Well, I have to say I had tons of fun getting this to work.

6. How to add a “Cover Letter” page to a Microsoft Dynamics GP Word Template document. Life is not complete without a Word Template article. I hope to keep these coming in 2013 since Word Templates are essential to Web Client.

7. Resizing SmartList panes with the Support Debugging Tool. Now, did I say I love the Support Debugging Tool? David Musgrave leverages some obscure Dex functions to make SmartList a dream tool to use.

8. Leveraging Custom Links to track carrier shipments in Sales Order Processing. You know, I am always amazed at how underutilized GP seems to be sometimes, but my duty as a blogger and consultant is to expose these underutilized features to the community.

9. Microsoft Dynamics GP Add-In for Microsoft Word not enabling despite several attempts to install. I had a number of clients come to me with this issue. Upon doing more research, I realized it was a community wide problem and I had to do something about it.

10. Adding more comment lines to POP Purchase Orders. Building on a previous article written by David Musgrave for SOP, I thought I would show how to accomplish the same in POP – it was all worth it as the community gave praises for the solution.

101 articles throughout the year are simple quite a bit of stuff written. I just hope I can keep the good stuff coming. Happy New Year!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

More here:
The Dynamics GP Blogster’s best articles of 2012

December 31, 2012 · Mariano Gomez · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 232

How to add a sparkline to a report in SSRS

Introduction: This blog post is written by my friend and colleague Mickie Stamm. Mickie is a fabulous Dexterity, SQL and application developer and is one of the great minds behind GP Reports Viewer.


By Mickie Stamm:

In the last GP Reports Viewer newsletter I wrote about using rectangles as containers in SQL Server Reporting Services reports. In this post I would like to demonstrate another example of using rectangles in conjunction with something that I think adds a lot of punch to reports – sparklines. Sparklines were introduced with SSRS 2008 R2, however you can simulate them in 2008 and 2005 by using a regular line chart and removing a lot of the surrounding elements like the chart title, legend, category and value axes, etc.

Sparklines are great little charts that help you to see the trend of your data over a period of time. They are useful in a table, matrix or list because you can view them together and compare them to more easily see which rows in your data are performing better than the others.

The steps below will walk you through building an SSRS report with a sparkline in the 2008 R2 Business Intelligence Development Studio (BIDS).

Our report is going to use the following query to get data from a Dynamics GP company database:

SELECT DATENAME(m, SH.DOCDATE) Month_Name, MONTH(SH.DOCDATE) as Month_Number,
       SD.ITEMNMBR Item_Number, COALESCE(I.ITEMDESC, SD.ITEMDESC) Item_Description,
       SUM(CASE SD.SOPTYPE
          WHEN 3 THEN SD.XTNDPRCE
          WHEN 4 THEN SD.XTNDPRCE*-1
          END) Sales
FROM SOP30200 SH
INNER JOIN
     SOP30300 SD
     ON SD.SOPNUMBE = SH.SOPNUMBE
     AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
     IV00101 I
     ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0
     AND SH.SOPTYPE IN (3,4)
     AND SD.XTNDPRCE <> 0
     AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
     AND YEAR(SH.DOCDATE) = @year
GROUP BY DATENAME(m, SH.DOCDATE), MONTH(SH.DOCDATE), SD.ITEMNMBR,
         COALESCE(I.ITEMDESC, SD.ITEMDESC)

To create the report:

  1. Start by right-clicking on your report project and choose Add New Report to start the Report Wizard: 
  2. Select an existing shared data source or enter in new datasource information and click Next.
  3. Enter in the query string above on the following screen and click Next.
  4. For the report type on the next screen choose the radio button for Matrix.
  5. Design the Matrix as follows: 
  6. Choose a style for the matrix on the following screen (I chose the Corporate theme) and click Finish.
  7. Give your report a Name (I chose Sales by Item) and click Finish.
  8. Run the report and your results should look like this: 
  9. Make the columns for Item Number and Item Description wider.
  10. Click on the textbox for Sum(Sales) and then right-click and choose Add Total > Column
  11. Click on the Month_Name column header to select the column and then right-click and choose Insert Column > Outside Group – Right
  12. Click on the Month_Name column header to select the column and then right-click and choose Delete Columns.  Choose Yes when prompted if you want to delete columns and associated groups.
  13. Click on the textbox in the header row of the new column you added in Step 11 and then right-click and choose Expression…and enter the following expression:  
     =Parameters!year.Value & " Sales" 
  14. Click on the textbox in the details row of the new column you added in Step 11 and then right-click and choose Insert > Rectangle
  15. Next, right-click on the rectangle you added in the last step and choose Insert > Sparkline.
  16. Choose a chart type (I chose the default column chart type) and click OK.
  17. Set your chart Values to be Sales and Category Groups to be Month_Number
  18. Make the chart wider and shorter and adjust the row height to be shorter as well so that it looks something like this: 
  19. Now, when you run the report you will notice that the sparkline chart doesn’t look quite right if the items don’t have sales in certain months. To resolve this issue, go back to design mode, right-click on your sparkline and choose Horizontal Axis Properties… and mark the checkbox for Align axes in: and select the name of your matrix (in my case it is the default name matrix1) from the dropdownlist and click OK.
  20. This will make the bars line up in the right places horizontally, however the next issue we run into is that the vertical maximum is the same height in each row no matter what the sales amount is. To resolve this issue, go back to design mode, right-click on your sparkline and this time choose Vertical Axis Properties… and mark the checkbox for Align axes in: and select the name of your matrix (in my case it is the default name matrix1) from the dropdownlist. Set the Minimum value to 0 (zero) and click OK.
  21. To add tooltips for the individual bars of the sparkline, in design mode click on one of the bar columns of the Sales Chart Series and enter in the following expression the under ToolTip property: 
     =Fields!Month_Name.Value & " : " & Format(Fields!Sales.Value,"C2") 

Those are the basics to setting up a sparkline embedded in a rectangle in your SSRS report. You can explore the other chart properties and tweak things like colors, markers, etc. and even provide actions such as jumping to a detail report for a given line item. The report created with the steps above can be downloaded here.

For more GP Reports Viewer news and Dynamics GP reporting tips, sign up for the GP Reports Viewer newsletter. For additional SQL code, take a look at the GP Reports page on this blog.

Filed under: Dynamics GP, GP Reports Viewer, SSRS Tagged: Dynamics GP, featured, GP Reports Viewer, SQL Server, SSRS

More:
How to add a sparkline to a report in SSRS

November 27, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 1,382