Custom report - Aging Vendors by Project

I am building a custom report and I am wondering if there is a way to get the container associated with an existing report.

The aging vendor report is what I need but I need it by project.   I am setting up a report that will show projects and then show all of the aging vendors associated with each project.  I may be missing something, but I don't see anything built in that covers this need. 

I am working on using intelligence to create a new report, but wondered if there was a simple way to start with what a report is already pulling for data instead of working with everything.

I am new to the intelligence tool so I am very open to and comments or directions on anything I might be missing.

Thanks

  • 0

    Hi Garvin.

    Each report in the Report Manager has a container associated with it which holds the query that is executed against your database.   You can access the container using the Connector module.   The Connector requires a separate license and I would check whether you have this first.

    If you would like to go directly to the container of a report from the Report Manager, you can do so by double clicking on the report, double clicking on the Source Container item which shows the associated container, right clicking on the container name and then selecting Go to Container in Connector.

    It's always a good idea to make copies of your containers and reports before making changes to them.   If you take a look at the Getting ahead with Sally section under the following link, there is a comprehensive set of videos on using the Connector.   Although it's provided under the 50 US section, most of the content will apply to 50 CAN as well.

    http://learning.sageintelligence.com/sage-50-advanced/visual-learning/

  • 0

    Hi Garvin.

    What I suggest is that instead of unlocking the containers you switch output mode in the Report Manager.   To do this, right clicking on the home object and select Switch Output Mode.   Then select SQL Debug.

    Now, when you run a report, the full query that gets executed against the database will be output to a window.   You can copy this, take it back to the Connector, create a new container, and paste the code to it.   You can then work from there.

    Remember to switch the output mode back to Excel once finished.

  • 0

    Hi Peter,

    I had to set this project aside for a while, however I am back at it full force.   I have worked through the reports in report manager and am able to see the associated containers. I have found that one container I am particularly interested in but it is locked (project analysis) so that one appears to not actually be available to me.    

    I was wondering if there is a way to see the containers associated with the standard reports in Sage, not just the specialized ones in Report builder.   I am mainly thinking about the "Vendor Aged".   If I could use that container and add in the project ID associated with each of the invoices showed in the detailed information, I think could fairly easily resort the data to provide the output we need.

    If not, it appears that I will have to create all of my own table joins and sql coding manually to get an export of the needed data and then build from there.   I'm not looking forward to this line as I have spent a fair bit of time reading through the DataDic.pdf file that lays out all of the tables and fields and it looks like a fair bit of spaghetti logic :) (I'm sure it makes sense once you have spent a bunch of   time with)  

    The learning videos were a great help, thank you for the link.

    By the way, I did notice there is an "accounts payable by project" template for pervasive that looks almost exactly like what I need.   Unfortunately, I'm guessing there isn't much I can get out of it for my purposes.

    Thanks for all the great help, these forums help a lot in the learning process.

  • 0

    Very nice feature.   Thanks Peter!   I'll let you know how I do with the report.

  • 0

    Hi Peter, Interesting find, the "project analysis detail" report will not provide the sql debug, it will only give me the screen view of the data.   I made the change to the output as you suggested above, and indeed other reports in report builder will show up as the sql, but not the locked one.    

    One other quick question that I have not been able to get a solid answer on and you will likely know, can I get a hold on of the more back end of any of the standard reports?   I am thinking about the aged vendor report that is one of the canned reports in Sage 50.   Basically, if I could add the project data into it, I would be set.

    Thanks

  • 0

    Hi Garvin.

    I'm going to inquire with someone but not sure what the outcome will be.   At the same time I suggest posting the question on the 50 CND forum on Sage City and see if you get any response there.

    https://sagecity.na.sage.com/ca/sage_50_accounting_ca/f

  • 0

    Thanks again for all of the help, I now have the containers in place and the reports exporting all of the data properly to the excel template.

    One thing I can not find, perhaps someone can help me.   I need to know how/where to find a paid flag for the bills I am working with?   I have spent a lot of time going through the DataDict.pdf but I can not isolate a paid flag field.   I am currently working with the titrec, titluip, titlu and tproject tables.   I am guessing I am just missing the field in another table.

    Any thoughts would be great.

    Thanks

  • 0

    Hi Garvin.

    It is possible that you're looking at an older version of the data dictionary.   If you don't find an answer on Sage City then I suggest getting in touch with a report writer:

    https://www.sageintelligence.com/consultants/

  • 0

    Thanks Peter,

    I just finished posting in Sage City.   I'll give it a little bit, then I'll touch base with a report writer if I can't get a reply on this one.    

    Hopefully it is simple, but I know from other databases I have dealt with, it is sometimes much more complex than you expect.

    Thanks again for all your help