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

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

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

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

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

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

GPUG Summit 2012 Seattle – Wrap Up!

With another Summit behind us, it’s just fitting that I close this one out with some remarks about the event, and as it is customary, with all links I could find from people talking about it.

This is indeed my second GPUG Summit as a presenter and I have to say, this event is growing very fast in popularity, with over 550 users, partners, and ISVs from the Microsoft Dynamics GP world. It’s also good to note that over 2,000 participants attended on behalf of all the user groups (AXUG, NAVUG, CRMUG, and of course, GPUG). What also shocked me was the total number of sessions: over 150! Sessions were as diverse as anyone could imagine: from Social Media, to Excel, to Reporting Services, to Business Intelligence, to myriad of sessions on tips (50 tips, 100 tips, 150 product tips, etc., etc.) , Microsoft Dynamics GP functionality, and the upcoming Microsoft Dynamics GP 2013. No, it doesn’t end there either. Attendees got an up close and personal view of the web client, Business Analyzer on Windows 8 and quite a mouthful of GP 2013 features.

No, not quite of Convergence proportions, but certainly a very familiar feeling to the entire event. You see, there’s no corridor or isle you can walk without running into someone you know, someone asking you “how’s your event going?”, or heck, someone inviting you out for a drink or dinner! In addition, this is the only conference where there is no short of presenters willing to pick up a session for another one on a moment’s notice. End-users also get to interact with presenters and subject matter experts on a one-on-one basis, more so than they would at Convergence.

The venue? No slouch here either. The Washington State Convention Center and The Conference Center proved just as worthy as any other venue I have been to. The difference? The proximity of the session rooms made the experience even more enjoyable for attendees.

GPUG Summit 2013 will be held in the beautiful city of Tampa, Florida and you can bet that Kim Peterson and Andy Hafer are probably a step away from wrapping up the details on this. Those two work just as hard as anyone else in this industry to make things happen for the user community.

Tampa, Florida

With that said, here is the Summit from various perspectives:

The Dynamics GP Blogster (this site)

GPUG Summit 2012 Seattle: Getting here
GPUG Summit 2012 Seattle: Day -1
GPUG Summit 2012 Seattle – Day 1
GPUG Summit 2012 Seattle – Day 2
GPUG Summit 2012 Seattle – Day 3

DynamicAccounting.net (Mark Polino)

GPUG Summit Wrap Up

EDI Integration for Microsoft Dynamics (Jon Rivers)

Updates from NAVUG Forum 2012 Opening Session
Thanks AXUG Summit Party Goers for a Great Event at Seattle’s EMP!
GPUG Summit 2012 Kicks Off and Heads Into Busy Day
Where Should You Turn for Answers? The Power of Social Media
Last Day of AXUG Summit 2012, Make it Count!
Experts Abound at GPUG Summit 2013 ‘Ask the Experts’ Roundtable
Record Attendance, Great Week at AXUG Summit, GPUG Summit and NAVUG Forum in Seattle!

Please check The Dynamics GP Blogster on Facebook for pictures from GPUG Summit 2012 Seattle. Also, check back regularly for presentation materials on the Learning Resources page of this site.

Until next post!

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

See the article here:
GPUG Summit 2012 Seattle – Wrap Up!

October 22, 2012 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 234

GPUG Summit 2012 Seattle – Day 3

Day 3 marks the end of GPUG Summit 2012 Seattle. However, this day would go out with a bang and still tons of sessions left.

Kim Peterson, Michelle Spitzer, David Eichner

Next up was the “Ask the Experts” town hall session, where I had a chance to be on stage with fellow Microsoft Dynamics GP MVPs and last Summit’s GPUG All-Stars Bob McAdam, MVP John Lowther, and Zubin Gidwani.

“Ask the Experts” Panelists
Left to right: Andy Hafer (Moderator), MVP Charles Allen, MVP Frank Hamelly, MVP Leslie Vail, MVP Mark Polino, MVP Mariano Gomez, Bob McAdam, MVP John Lowther, Zubin Gidwani

Somehow, the GPUG organization saw fitting that me and my fellow colleagues on stage wear these propeller head caps. I must say, we did look the part. After some introductions, some fun on stage, and the cap fitting, the questions began to poor in from the attendees and we had a wonderful time taking on some challenging ones.

The “Propeller Heads” in action

It was now time to wrap up the circus and poor Michelle Spitzer was left to pick up the pieces. Here she is wrapping up one of the GPUG banners.

Michelle Spitzer is left to reel it in

Up next was my last session at the Summit, GEN05 Where Should You Turn for Answers: The Power of Social Media. This session would be delivered with my friend Jon Rivers (twitter: @jon_rivers) from Data Masons. Jon and I had a blast with this topic and were able to offer Jon’s eBook for free to those attending our session.

It was time to head over to the Grand Hyatt to pick up my luggage and sail to the airport. I had a tight 55 minutes before my flight departure. Unfortunately, I could not stay for the rest of the day and missed the closing session. I will not bore you with the details of my return home, but my trip started with a pleasant ride with Lou Spevack, and would take me from SEA-TAC to MSP and from MSP to ATL. The trip from SEA-TAC to MSP was uneventful, with just a couple rough patches of air here and there. Upon landing in MSP at gate G21, I had to find my way to gate G2. I came across a 4-year old at my departure gate in MSP who wanted to step on my toes for fun. I must say, I was completely wiped out from this event and spent most of the time sleeping on my trip from SEA-TAC to MSP.

The flight to ATL was very pleasant.

As customary, please wait for my wrap up post and, if you attended, hope you had a great Summit. Safe travels home.

Until next post!

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

View article:
GPUG Summit 2012 Seattle – Day 3

October 21, 2012 · Mariano Gomez · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 306