How to use a SQL view in SmartList Builder

April 20, 2009 by Victoria Yudin 43 Comments


Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList.  I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3:

 

STEP 1  –  create SQL view

To start we need a  SQL view.  I have published a number of views for getting GP data, so I will use one of my favorites – Payables Apply Information.  You can see the list of other SQL views I have published on my GP Reports page.

To create the view in SQL: 

  1. Open SQL Server Management Studio and connect to your GP SQL Server
  2. Click on the New Query button at the top left
  3. Copy the SQL code into the new window that opens (on my blog, I put the code to copy between tildes: ~~~~~)
  4. In the drop-down, select your GP company database:change-db
  5. Click Execute
  6. You will get a message saying “Command(s) completed successfully.”
  7. If you want to use your SmartList in multiple companies, repeat steps 4 and 5 for each company database

 

 STEP 2 – set up SmartList security for SQL view

Before we can use a SQL view in SmartList Builder we need to grant SmartList Builder security to that view:

  1. Log into GP as a user that has all the proper security access
  2. Open the SQL Table Security window by going to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security
  3. Choose Views at the top
  4. Click on your company database on the left
  5. Find the view on the right and select it:sql-table-security
  6. If you created the SQL view in multiple company databases, repeat steps 4 and 5 for each company database

 

STEP 3 – create SmartList

Now we are ready to create the SmartList:

  1. Open SmartList Builder by going to Microsoft Dynamics GP > Tools > SmartList Builder > SmartList Builder
  2. Type in a SmartList ID and Name (the Item Name will default to the SmartList Name, I recommend leaving these the same to avoid confusion)
  3. Choose what Product and Series to put your SmartList in – this is a great new option in GP 10.0 and allows you to group SmartLists as you want.  This is also something that can be changed later, so some people like to create all new SmartLists is a separate place until they are satisfied with them, then move them to their ultimate location.  Up to you on this one.
  4. Click on the + sign to the right of Tables and choose SQL Server Table:slb01
  5. Check the Use Company Database checkbox if you are creating a SmartList that should run against whatever company database the user is logged into (or if you are planning that for the future)
  6. Click on the company database on the left
  7. Select Views and click on your view on the right
  8. Click on the + sign to the right of Key Fields and add Key Fields (these are used determine the current record selected when opening Go To’s) – at least one Key Field is required:slb02
  9. Click Save to return to the SmartList Builder window
  10. Every column in the view will be brought in as a field for the SmartList, at this point you can decide what fields to show, change formatting, etc., for now I will simply add all the fields available to the SmartList:slb03
  11. Once you’re done configuring all the additional options for your SmartList, click Save
  12. To finish creating the new SmartList, go to SmartList, and say Yes to the following message:slb04

 

Next steps & additional resources

Below are some additional resources for creating SmartLists and dealing with SmartList security in GP:

If you know of any other resources to add to this list, please let me know.

 

Dynamics GP, GP SQL scripts, SmartList Builder
, , , , ,

43 Responses to “How to use a SQL view in SmartList Builder”

  1. Jennifer Streetman Reply August 21, 2020 at 6:55 pm

    Thanks for this! Just what I needed.

    Like

  2. Andrew Reply September 23, 2014 at 2:10 pm

    I got to step 1 number 5, but got this message instead of what number 6 says I should have see: Msg 2714, Level 16, State 3, Procedure view_AP_Apply, Line 22
    There is already an object named ‘view_AP_Apply’ in the database.

    Any ideas? Could it have anything to do with my smartlist builder being greyed out under tools logged in as sa? I only ran the execute once. I’ve contact my GP partner to get the right keys so I can view smartlist builder. Thanks for your help in advance!

    Like

    • Andrew Reply September 23, 2014 at 2:13 pm

      PS…I just tried with your view_ap_distributions and I got the “command(s) completed successfully”…maybe I need to wait and see when I get smartlist builder access if there really is already an object names view_ap_apply in the database?!?!

      Like

  3. Julien Reply February 24, 2014 at 12:02 pm

    Hi Victoria,

    Whenever I tried a SQL View in my Smartlist, my Views list is empty.

    I did grant DYNGRP to select on my view.

    Any idea how to fix that?

    Regards,

    Julien

    Like

  4. Mona Kazzi Reply January 23, 2013 at 9:43 am

    Dear Victoria,

    Thank you very much for the tutorial.
    I have been using smartlist for a while now.
    But I am now facing a very strange issue and I need your help.

    We are working on 3 companies (company1, company2, company3). And since our go live, I have created a smartlist that returns the journal entry number, audit trail code, source document number and the transaction date on company1 and company2.
    2 days ago I have been asked to add the company3 to this smartlist.
    I did it and granted the select on this view and added it to the smartlist.
    But still, the data shown in the smartlist are from company1 and company2.

    I thought that I could solve it if I create an SQL view on DYNAMICS DB which is a consolidated report as follows:
    SELECT ‘COMPANY1’ AS COMPANY, *
    FROM COMPANY1.dbo.AUDIT_TRAIL_CODE
    UNION
    SELECT ‘COMPANY2’ AS COMPANY, *
    FROM COMPANY2.dbo.AUDIT_TRAIL_CODE
    UNION
    SELECT ‘COMPANY3’ AS COMPANY, *
    FROM COMPANY3.dbo.AUDIT_TRAIL_CODE

    ALSO I granted the select on this view and added it to the smartlist.
    BUT NOW THE SMARTLIST IS RETURNING NOTHING AT ALL!!!

    Your help is most appreciated.
    Thank you in advance

    -Mona

    Like

    • Victoria Yudin Reply January 23, 2013 at 10:20 am

      Mona,

      There are a number of things that could be going wrong here, but the first thing I would check is whether you get any data in the SmartList when you log into GP as ‘sa’. Second thing would be to try it in all 3 companies…does it return no data in all 3?

      -Victoria

      Like

      • Mona Kazzi Reply January 23, 2013 at 11:09 am

        Victoria,

        You’re a 100% right. I did check the smartlist with the sa user and in the 3 companies… same same… no data returned.

        Actually Victoria I think that this may be caused by the the view that I have added for the third company.
        because the smartlist only returns data from company1 and company2 when am using each company’s view not the consolidated one that i have created in Dynamics DB.

        What else maybe causing this issue?
        Thank you Victoria

        -Mona

        Like

        • Victoria Yudin Reply January 23, 2013 at 11:19 am

          Mona,

          Did you create a new SmartList for this or update the existing one? If you updated your existing one, most likely it is pointing to your view in each company. Can you try creating a new SmartList, point it to the Dynamics database and make sure the Use Company Database is unchecked?

          -Victoria

          Like

          • Mona Kazzi Reply January 23, 2013 at 12:04 pm

            Victoria,

            This is the solution 🙂

            I was actually updating the existing one.
            I deleted it then created a new one and it worked!!

            Thank you very much
            Regards

            -Mona

            Like

            • Victoria Yudin Reply January 23, 2013 at 12:14 pm

              Mona,

              Excellent! I have seen that happen quite a bit, where updating existing SmartLists just does not work how you think it will. I always test with a new one when making major changes like this. Glad you’ve got it working now. 🙂

              -Victoria

              Like

    • Calvin Lee Reply May 16, 2013 at 3:32 pm

      Hi Victoria,
      I created a SLB from a view and can see it only as sa and not as any other user even though their setup as POWERUSER and the task for the Smartlist object permissions is assigned. Do you have any suggestions?

      thanks,
      Cal

      Like

  5. Erica Reply August 30, 2012 at 1:51 pm

    Hi Victoria

    I was able upload my SQL View with in Smartlist Builder and the report looks good. However, I now need to add a few more rows to my SQL query and thus update the Smartlist that is now in GP10.0. Can the SQL view that was uploaded in the GP be modified?

    Like

  6. Gerry Reply September 13, 2011 at 2:09 pm

    Hi Victoria,
    I’m learning more and have enjoyed you site. It is of great help.

    I’m using Smartlist Builder and trying to link the GL00201 table with the GL11110 table to create an actual vs. budget GL Smartlist. The issue is if there is data only appears if data exists in both tables for a GL Account for a specific period.

    Would this require a SQL view to do this, or is there anyway around this using Smartlist Builder.

    Your help is much appreciated,
    Gerry

    Like

    • Victoria Yudin Reply September 13, 2011 at 2:22 pm

      Hi Gerry,

      When you’re linking the tables, are you using left join on the ACTINDX? If so, and it’s still not working, then you will most likely need to create a view in SQL first, then use that in SmartList Builder.

      -Victoria

      Like

  7. Sameer Reply July 15, 2011 at 6:04 pm

    I admire the work you did and posted in the website. You saved me several times. 🙂

    Sorry if you have answered this before:
    Where can I find the list of all smart builber report names with folder nams in the tables structure

    Thanks,

    Like

  8. May Reply August 11, 2010 at 3:33 am

    Hi Victoria,
    Do you have a list of all the common tables used on Extender; which one stores the Extender definition and which store the actual data?

    I find that the Import/Export function for extender only covers limited type, e.g. Forms, Windows etc but not other type such as View, Import, Menu etc.

    I had multiple databases created in a server and require to duplicte the Extender definition done from 1 master database to the rest of the databases. Since the import/export function does not covers all, I had resorted to using SQL integration services to copy files to the other databases. I would like to copy only the definition files and not the data files.

    Appreciate your kind advise.

    May
    Thanks

    Like

  9. Kal Sadra Reply May 31, 2010 at 11:48 am

    Thanks a lot for putting up this.

    Like

  10. Stu Reply April 12, 2010 at 8:36 am

    Victoria – GP9 Sp3
    I have a smartlist that was created using some views. We have a number of test companies and it seems that one of the views was linked by using one of the test companies in view selection, instead of live. How can I change this to point to live as opposed to the test company?

    As long as the view exists in live, is it as simple as exporting, replacing the database name in the xml file ( In the section ‘SQL Database Name’) and then re-importing?

    Issues that occur because of this are:
    If updating field names, have to replicate in test company as opposed to just live;
    If adding fields, again, have to do in test as opposed to live;
    If we removed the test company for any reason what repurcussions would there be on the smartlist.

    Help is always appreciated.
    Stu

    Like

    • Victoria Yudin Reply April 12, 2010 at 12:32 pm

      Stu,

      It’s a little difficult to advise on this without seeing the exact setup of this as there are so many components that all need to work together. In general what I typically recommend is that all SmartLists are created with the Use Company Database checkbox selected – that way they will work in all companies and you don’t have to go crazy when you restore a current copy of live database over the test database.

      You could try making sure your view exists in the live company, granting security access to the live company view in SLB and then modifying your SmartList to have Use Company Database selected. This didn’t use to work very well in older versions of SLB, but I believe it should work in 9.0 SP3. If not, you could also try your method, but I am not sure that would work, I have not done that on the same server, only when ‘moving’ a SmartList from one server to another.

      -Victoria

      Like

  11. Sivakumar Venkataraman Reply April 22, 2009 at 9:07 am

    Hi Victoria

    Take a look at the URL of my blog where I have posted an article on Smartlist Security.

    http://cvakumar.com/msdynamics/2008/12/23/smartlist-builder-objects-using-sql-tables-and-views/

    This was created after I had this discussion with Mariano on his blog article Smartlist Builder Security for SQL Server Views.

    You can add this URL to your list as well. 🙂

    Regards
    Siva

    Like

Trackbacks/Pingbacks

  1. Fix Viewer Builder Error Signing Failed Windows XP, Vista, 7, 8 [Solved] - December 11, 2014

    […] How to use a SQL view in SmartList Builder | Victoria Yudin – Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList. I am going to walk through this step by …… […]

    Like
  2. How to use a SQL view in SmartList Builder | Victoria Yudin : Interesting Findings & Knowledge Sharing - September 16, 2013

    […] Continued here: How to use a SQL view in SmartList Builder | Victoria Yudin […]

    Like
  3. Using a SQL view in SmartList Builder « Dynamics GP Builders & Reporting - May 17, 2010

    […] Yudin – How to use a sql view in Smartlist Builder  Pay special attention to the “Use Company” checkbox.  This is VERY important, and […]

    Like
  4. How to use SQL view in SmartList Builder « Microsoft Dynamics GP Discussions - May 11, 2009

    […] May 11, 2009 by smathew23 Victoria Yudin has a great article explains how to use SQL views in Micrsoft Dynamics GP – SmartLIst Builder. This is a must to read article if you use SmartList Builder. Here is the link to Victoria Yudin’s “How to use SQL views in SmartList Builder“ […]

    Like
  5. SQL View in SmartList Builder - DynamicAccounting.net - April 21, 2009

    […] View in SmartList Builder Victoria Yudin has a new post up on using SQL View's in SmartList Builder. Published: Monday, April 20, 2009, 11:00 […]

    Like

Leave a comment