Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Multiple Queries for one objective

    Strategy time!

    If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?

    Just wondering your approach would be?

    I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.

    Thanks!
    V

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I'd start off writing the queries for the various reports, and if you see the same sort of query cropping up, you can consider making a view out of that query. Encapsulating that part of the query in a view may make the rest of your queries look "cleaner" and easier to maintain.

    There is of course a catch. What you should be very leery of, is creating views on top of other views. This generally leads to a rat-hole as far as performance, maintenance, and troubleshooting.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    That's hiliarous we have a master view that joins into 2 other massive views. The logic is brilliant however I am not so sure about the performance Just wanted to toss that out there.

    MC what are you thoughts about using SSRS to blast out a daily quality report?

    I was wondering if there was a way is SSRS to drop a bunch of queries in a report and have them all execute while producing the results in a single exception reports.

    For example - for the sake of simplicity

    SELECT CODE
    FROM Table
    WHERE NOT EXISTS ( SELECT column
    FROM Table2
    WHERE columnt = columnt2 )

    This will product one results for the exception report

    Then you may have something where you union to sets of data

    etc

    This would end up making up one report reporting on any exceptions that are found in the database.

    If you know of any articles or best practices that would be great.

    Thanks MC

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It should be possible. I just don't know how. I don't use SSRS myself.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least based on my experience, as databases grow the tasks associated with managing and maintaining the data get distributed... The bigger the problem, the more problem solvers are needed.

    I try to keep each report laser focused on a specific problem and also keep the size of each report manageable. You may end up with dozens or even hundreds of reports, but as the complexity of the system grows there will be folks that work on security and others that work on inventory, HR, CRM, etc. As the problem grows, you'll eventually have to take the "mega-master report" and break it back down to the component pieces in order to pass them out to the appropriate people... Why spend time to combine them now when you'll spend more time to break them up again later? Build them as separate reports for each issue, and if need be you can create a "master report" to run each of the subsidiary reports and combine them!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat what would be your approach for creating the master report?

    Kick off several queries and just drop them in an excel sheet?

    Right now I have to report on operational metrics. I have 8 tabs in place covering all of our departments. Some departments have one metric due to the nature of their work and others can have 10. Then you have data integrity which is kind of out on it's own. Right now I am running each query and pasting it into the spreadsheet for a monthly dashboard.

    I was hoping get some ideas to help manage this work effort. I like the idea of an all encompassing report automating so I can be left alone to work on other projects. It doesn't seem that at my skill level that is the case, well I'll keep working towards that.

    Anyway I appreciate all the input and loo forward to your replies.
    Last edited by VLOOKUP; 12-26-14 at 22:38.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The mechanics of the process depends a lot on what you want to have for your finished product. Right now you see the output as a spreadsheet with eight tabs that have one or more metrics per tab.

    Based on past experience, my guess is that you're going to eventually want each metric to go out as an email to a group (which may only contain one person) of workers, then each tab go as a summary to a group (which may only contain one person) of managers. By using mail groups (probably in Active Directory), the re-routing of messages becomes a day-to-day operational issue which can be easily delegated to an operator or eventually to an automated process (such as Microsoft ForeFront). Separating the distribution from the report creation is a key factor that people overlook at first because they don't look "down the road" to the point where they might be too busy or "out of pocket" to attend to distribution tasks.

    The technical piece that you're missing is most likely Add a Subreport and Parameters (Report Builder and SSRS). This will give you the ability to take a bazillion focused reports and combine them into a single master report.

    Think a little bit before you get too frisky coding. One of the most overlooked rules in running any business is KISS. Getting a file that you have to open, sift through the tabs, then analyze takes time. Getting an email with a single "to do" item means that you don't have to geek around in order to figure out what work you need to do to keep the business running. You may want to create reports for the managers (since managing the workload is their "to do" item), but emails seem to mt to be a better answer for the people being assigned non-supervisory tasks.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I agree with Pat 100% on each point made here:
    Think a little bit before you get too frisky coding. One of the most overlooked rules in running any business is KISS. Getting a file that you have to open, sift through the tabs, then analyze takes time. Getting an email with a single "to do" item means that you don't have to geek around in order to figure out what work you need to do to keep the business running. You may want to create reports for the managers (since managing the workload is their "to do" item), but emails seem to mt to be a better answer for the people being assigned non-supervisory tasks.
    My 2 cents (maybe worth as much as a nickel, maybe not):

    1. Use the tools built in first before coding. Then, when you have to code, code sparingly and KISS. One thing I have learned it: you code it, you own it. Sounds good now, but 2 years from now, it can haunt you. And, the more custom it is, the harder it is to maintain, grow and pass on to somebody else (each of those elements is important to a thriving business). And I don't care how well you document your code. Two years later, after you have coded thousands of other lines of code, you will lose time scratching your head, trying to remember what you did back when you got fancy.

    2. Managers generally want to see tasks and action items more than buckets of data. The point of the "mega report" or "dashboard" is to provide a very short, concise summary of details and/or actions.

    3. As tempting as it is to mix things together to create fewer objects on the back end (whether because it is "elegant" or seems more efficient or something else), the cleaner and simpler the construct is, the easier it will be to debug and modify - here's the thing about managers and the metrics they ask for: the parameters change over time - sometimes short periods of time.

    4. On the coding side, if there are certain combinations you know will always pop up (such as linking Invoices to Sales Orders for "open orders" and "shipping priority list", Sales Orders to Quotes for "conversion ratios", and MatReq to Inventory to Sales Order to get the "receiving hot list") you can consider making those into table value functions and use then as "sub queries" in some of your other, bigger queries. More efficient than stacking views, but a bit more coding as well.

    Oh, and in my humble opinion: tasks and action items go in an email, dashboard data goes into Excel (you can then create pretty graphs for the manager types). As Pat said, managers want to see actions, but they also like informative pictures. The data for the dashboard can come from stored procedures.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  9. #9
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    A lot of good info, almost to much I'll have to read through both comments a few times to really grasp.

    The piece about you code it you own it makes a lot of sense and I don't want to be held back for promotion due to some weird exception report I built.

    Honestly this is going to get handed off to a BO developer down the road, at least that's what I have been told and the way the dollars are going.

    Since this is a monthly report I am going to keep my queries (backed up) but organized in my SSMS project folder. I'll run them manually and just add the metrics to the data tab. The math is done in SQL so no need for a calculation tab I can go straight to charts.

    Thanks guys this is a great place to learn. WOW


    @ PAT

    My approach now is to create 8 tabs, all for data collection. Time to bill etc.

    Then I chart off of all these tabs into one or two dashboards (excel tabs) one for serious management and the other for my peers and managers. I thought this is the most simple way to consolidate and KISS.

    Thanks again all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •