[Frunkie]

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

[georgev]

Originally Posted by fjm1967

Any ideas?

Teach them SQL

[Frunkie]

Originally Posted by georgev

Teach them SQL

LOL .. You're such a smart-ass, George.

[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?

[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!

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

[georgev]

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!

[Frunkie]

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.

[Frunkie]

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.

[Frunkie]

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.

[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!

[Frunkie]

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:

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..

[aschk]

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.

[Brett Kaiser]

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