Custom Database Queries and Reports

Hello,

Just a general question, wondering if there is anyone out there that creates custom reports/applications using the ESC database. I know we have done a lot of work creating custom reports and tools to extract information that helps us know what we need to know. Not sure if anyone else has done some of these things, but I would love to hear if they have or maybe even share ideas for community growth. 

Also, if anyone has done any cool tuning to the database as well for performance it would be neat to hear and I'm sure other would benefit. 

0

Comments

7 comments
  • It's on my very full list of things to do.  I'm starting to look into the software, etc.  I may have more in a couple of weeks.

     

    0
    Comment actions Permalink
  • We are interested in learning how to create custom queries and reports.  Any insight would be very helpful.

    0
    Comment actions Permalink
  • Hey sorry for not getting back. Didn't see this till I scrolled through some questions. We use SQL server management studios (SSMS) to create custom queries and explore the data and SQL server reporting studio (SSRS) to create our custom reports. It requires a bit of know how to generate the queries but if someone has an idea on how to get it setup I don't mind helping to craft queries to find the data you are looking for or helping them along the journey

    0
    Comment actions Permalink
  • We are using Crystal Reports and Microsoft Access for customer reporting. It would be helpful to have an explanation of all the information included in each table, but we have been able to figure out enough of the tables to make it work for us.

    0
    Comment actions Permalink
  • I am not very familiar with Crystal Reports, but I don't think it is too far off from what I use with is SSRS (SQL Server Reporting Services). A great place to start when it comes to knowing what information you have is to know what tables you use. There are a lot of tables in ESC, but not all of them are used based on the services you subscribe to. Below is a query that will generate all the tables with how many records they each have. This allows you to know where you should begin looking to determine which tables you should dig into. Once you examine those tables you will get to understand the relationships between the used tables and be able to craft better queries. 

    Let me know if you have success running this query in Crystal Reports, it is only a select statement so there is no concern for damaging data (no update or delete in it). Again, I am uncertain where you put in queries in Crystal Reports, but it should have that somewhere. 

    I am more than happy to talk or discuss more on this topic as it is one of my favorites :)


    SELECT 'Table Name'=convert(char(25),t.TABLE_NAME),
    'Total Record Count'=max(i.rows)
    FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
    WHERE t.TABLE_NAME = object_name(i.id)
    and t.TABLE_TYPE = 'BASE TABLE'
    GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
    HAVING max(i.rows)>=0

    1
    Comment actions Permalink
  • We have figured out a majority of our needs through trial and error. It just would have been nice to have a data dictionary of some sort to reference since the standard reporting in ESC does not address the ad hoc necessity. Crystal reports has some similarities to Access so its not terribly difficult to work in. Some of the running totals, grouping, and formatting can be difficult to understand but we always validate against some of the information from the standard reports in ESC. 

    0
    Comment actions Permalink
  • I have been creating some SSRS reports.   Also some trial and error to make sure we are hitting the right tables to get the data we are looking for but over all has been pretty good.  A data dictionary would be nice. The good thing about SSRS over Crystal is SSRS comes with SQL Server and has a web portal.  You can setup a subscription in SSRS to email the reports or use a 3rd party app to do it.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post