Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Ideas for dynamic reports

    Not really sure if this is the correct forum for this.

    I would appreiciate some ideas on dynamic reports. I am programming a UI in php and have reached the stage where I need to give the user the ability to create reports.

    I can create static reports but I think there is probably a better way of doing this.

    I was thinking about giving the user the ability to choose different fields from the database and run a report based on their selections.

    Any ideas?

    EDIT: Also, if dynamic reports are possible how would I create the dynamic sql?

    Thanks,

    Frank
    Last edited by Frunkie; 09-14-07 at 22:37.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by fjm1967
    Any ideas?
    Teach them SQL
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    Teach them SQL
    LOL .. You're such a smart-ass, George.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    There are many products available to facilitate this sort of thing. One I know if is Business Objects. I believe that's quite expensive; there are probably cheaper and more basic alternatives available.

    Unless you know what you're doing and have lots of time this probably isn't something you want to write yourself as it gets complex. For example, if the user chooses to see "Deparment Name" and "Emplioyee Name", do they mean "show me the departments and all the employees in those departments" or do they mean "show me the departments and the employees who manage them"? And what if they wany both?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    When designing a dynamic reporting package from scratch, you need to take the proper steps in the proper order... Start by taking two or three generous shovel fulls of LSD, mix in some amphetimnes, and wash the load down with large quantities of alchohol (I'm sure that you'll be very thirsty). Give this mix about an hour to properly take affect, and play "Dueling Sitars" repeatedly while waiting.

    This should have you in a state where you can clearly understand what the users want, and also give you the ability to appreciate their timelines as well as their grasp of relational concepts.

    Please report back on the results... I always love a good read like that!

    On a (very slightly) more serious note, I think that there is a PHP package that does exactly what you are suggesting. I think that it is available at SourceForge if I remember correctly. Snoop around a bit, and let me know if you can't locate it. I'm going to be going like a wildman for a couple of days, but Thursday I'm going to be more or less confined to the house (long story) so I can do some serious hunting if you haven't found anything suitable by then.

    -PatP

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by Pat Phelan
    but Thursday I'm going to be more or less confined to the house
    And with that comes the stampese of students

    And btw Frank, I was actually serious with my suggestion!
    It may be easier to teach someone to drive then it is to design a vehicle that will do it for them!
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by andrewst
    There are many products available to facilitate this sort of thing. One I know if is Business Objects. I believe that's quite expensive; there are probably cheaper and more basic alternatives available.

    Unless you know what you're doing and have lots of time this probably isn't something you want to write yourself as it gets complex. For example, if the user chooses to see "Deparment Name" and "Emplioyee Name", do they mean "show me the departments and all the employees in those departments" or do they mean "show me the departments and the employees who manage them"? And what if they wany both?
    Hi Tony, thanks for the response. I have checked into BO and you are right, it is way too expensive..

    I'm not even sure I can use BO with php anyway.

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by Pat Phelan
    When designing a dynamic reporting package from scratch, you need to take the proper steps in the proper order... Start by taking two or three generous shovel fulls of LSD, mix in some amphetimnes, and wash the load down with large quantities of alchohol (I'm sure that you'll be very thirsty). Give this mix about an hour to properly take affect, and play "Dueling Sitars" repeatedly while waiting.

    This should have you in a state where you can clearly understand what the users want, and also give you the ability to appreciate their timelines as well as their grasp of relational concepts.

    Please report back on the results... I always love a good read like that!
    LOL. That's true Pat.. I remember back when I was doing a lot of IT work for the real estate industry and one client in particular had this mysql database he found. He was truly an Ignoramus because he and his staff would call me and want to know "How do we connect the boxes in the Excel query window".

    I'm actually thinking it may be better to just write some static reports and be done with it.

    On a (very slightly) more serious note, I think that there is a PHP package that does exactly what you are suggesting. I think that it is available at SourceForge if I remember correctly. Snoop around a bit, and let me know if you can't locate it. I'm going to be going like a wildman for a couple of days, but Thursday I'm going to be more or less confined to the house (long story) so I can do some serious hunting if you haven't found anything suitable by then.

    -PatP
    Thanks, I'll look into it but I am almost convinced that static is the way to go. I have a friend that is also a php programmer with more experience than me. He laughed at me when I asked him about dynamic reporting and said... "Don't do it, you'll be sorry". That is pretty much the concensus that I am finding. Besides, I'm lazy; it sounds like more work than I want to put into this project at this point.

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    And with that comes the stampese of students

    And btw Frank, I was actually serious with my suggestion!
    It may be easier to teach someone to drive then it is to design a vehicle that will do it for them!
    You have a point George but remember.. We are talking about people that can't find the M$ control panel or even find a file that they downloaded onto their computer. These are generally not computer literate people which was one of the main reasons that I thought that dynamic reporting would be the way to go. I think that may actually complicate things for them instead of helping them.


  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    It's because no one has shown them how to do any of it before!
    Some people have to be led, whereas some people are good at finding their own way. I have written a tonne of help files at work simply because once the user has been shown in simple "click here" terms then they stop calling!
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    It's because no one has shown them how to do any of it before!
    Some people have to be led, whereas some people are good at finding their own way. I have written a tonne of help files at work simply because once the user has been shown in simple "click here" terms then they stop calling!
    If I were your age, it wouldn't be so bad. The older I get, I seem to lose more patience with computer illiterate people. We have been in a computer age for the past 20 years.

    In the local newspaper, I see job announcements that say stuff like, “Must be proficient with Microsoft Word and Excel” huh?

    Amazing, really.

    Pat made a great point when he said:
    Quote Originally Posted by Pat
    This should have you in a state where you can clearly understand what the users want, and also give you the ability to appreciate their timelines as well as their grasp of relational concepts.
    I have worked in the IT field long enough to know exactly what he means by that so... If I give the users static reports, I could always tell them that technology has not yet progressed to the level that would allow reports as they would like them. Its all in the verbal delivery Georgie..

    Last edited by Frunkie; 09-18-07 at 01:27.

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Perhaps this is just me being naive, but as a practical solution :
    Here are a couple of assumptions :
    1) You have a lookup file for key relations between tables.
    2) You can read the database schema (information_schema in MySQL or whatever else is available from other dbs)

    Thus, read your lookup file, let the users select the "tables" (really they're not tables but relations you know about), select the columns from each of the tables in question and input some lookup params for each column they have selected.

    Build your SQL query based on what you have been given, and "run". Output your data in a grid format with the selected columns.

    This is probably far from the truth but puts forward "some" ideas about how I might go about getting it going.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    When designing a dynamic reporting package from scratch, you need to take the proper steps in the proper order... Start by taking two or three generous shovel fulls of LSD, mix in some amphetimnes, and wash the load down with large quantities of alchohol (I'm sure that you'll be very thirsty). Give this mix about an hour to properly take affect, and play "Dueling Sitars" repeatedly while waiting.
    ROFLMAO

    too short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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