11 Replies Latest reply: May 2, 2014 3:16 PM by Billy Gladky RSS

    SAGE 50 Accounts and QlikView

      Has anyone managed to link SAGE 50 accounts to qlikview via SQL 2005? The tables in SAGE are DTA files, I am struggling to bring SAGE through via ODBC link.  Any help would be appreciated, Mandy

        • Re: SAGE 50 Accounts and QlikView
          Mike Boreham

          Hi Mandy,

           

          I am struggling with the same thing.  Did you ever find an answer to your question?  There seems to be a thundering silence about anything to do with Sage on these forums.

           

          Regards

           

          Mike

            • Re: SAGE 50 Accounts and QlikView

              Hi Mike - yes I managed to crack it with a lot of patience and trial and error, and using your DAT back up files of SAGE rather than trying to link directly.

               

              The key is linking the ACCDATA folder, which contains the DAT files via ODBC as follows:

               

              Go to administrative tools from the control panel - select data sources (ODBC)

              Go to system dsn and add the latest Sage Line 50 version (should automatically appear on the list)

              click finish - give your data source a name you will remember, and add the data path - if you go to my computer and look to where your ACCDATA files are stored - use the properties of this as the data path.

               

              If you are asked for login codes use the administrator ones rather than individual user codes.

               

              If you can get this bit to work you then need to create qvd files before you can begin to create your dashboards.

               

              Let me know how you can get on - I can let you know the key data tables to use for your QVD's - we have a great dashboad that we use to help us analyse our sales trends.

               

              Mandy

            • Re: SAGE 50 Accounts and QlikView
              Mike Boreham

              Hi Mandy,

               

              Thanks for your help.  I still can't get this to work.  The whole ODBC setup and administration seems to not work rather than your suggestion of how to do it so I would like to thank you personally for trying.  It seems you have  a unique skill in getting Sage to connect to Qlikview!  Good luck.

               

              mike

                • Re: SAGE 50 Accounts and QlikView

                  That's dissappointing - dont give up.  If you want to email me directly I can provide screen shots to try and help you?  Are you working on the server computer? ie the computer that the back up files are stored within?

                   

                  I note from another post that you made that you are wanting to look at invoice data - thats one of the pages within the dashboard that I've set up - the company are now finding that they use QlikView when dealing with customers and suppliers rather than SAGE because it is so easy to bring data from different reports through to the same screen.  It would be a shame if you didnt finish it - believe me if I can do it any one can!

                   

                  Code to create the invoice QVD's is below, the passwords and user id's are pulled through from the ODBC set up - use the admin codes.  Remember to build it on a computer that has access to the server files, ie where the backups are stored.  Good Luck, Mandy

                   

                  code.jpg

                   

                  sample dashboard.jpg

                    • Re: SAGE 50 Accounts and QlikView
                      Mike Boreham

                      Mandy,

                       

                      You're a miracle, thank you so much for your patience and support.  I have now managed to load the Sage data via my laptop into Qlikview and can start working on the Qlikview code that you kindly shared with me.

                       

                      In case anybody else subsequently reads this thread, my problem was with the ODBC driver.  As Mandy correctly points out, this only works on the physical computer onto which the full Sage software is loaded.  Simply running the installation routine to load it onto your PC or laptop does not run a complete installation. Again in the name of completeness, the error messages I got included references to data mismatches and a message saying ODBC driver not found when I tried to remove the Sage driver from my laptop.  I got around this by loading the whole Sage 50 Accounts 2013 application onto my laptop then running the ODBC installation from the ODBC32 subdirectory.  You can then totally ignore the Sage installation and access the Sage data on the shared drive on the server.  The files can be found, as Mandy says, in the driveletter:\Accounts\ACCDATA in a series of DTA files.  Save these as QVDs as described in Mandy's post.

                       

                      I'm not sure anybody will ever read that but it could save them the three weeks it took me to work it out if they do.

                       

                      Mike

                      • Re: SAGE 50 Accounts and QlikView
                        Peter Atherton

                        Hi Mandy,

                         

                        Your post was very useful to me, I've established connections to sage and am loading the data after following your driver advice.  I was wondering if you are able to share any information on key data tables, schema and join fields?

                         

                        Thanks

                        • Re: Re: SAGE 50 Accounts and QlikView

                          hello Mandy,

                          can you share your dashboard, we have sage 50 and want to learn as much as we can about this?  We started a dashboard, but need to get correct ideas and table links, let me know if thats possible

                      • Re: SAGE 50 Accounts and QlikView

                        Hi Mandy,

                         

                        I'm also working with Sage50 and I'm running into a problem in finding how Sage50 calculates the Cost Of Good Sold.

                        They are using GetPeachRowAmountv2() function that calculate the Cost of Sale on the fly/

                        Do you know anything about it?

                         

                        Neta