generate amount due pie chart

hi,

i want to create a pie chart that shows the amount due from each table in my database.

basically i have 6 tables which are product tables, each have an amount column in them and a a datepaid column.

i need to show a graph which add up all the amounts in table 1 and gives a value and same for table 2 etc and displays it in a pie chart. it also needs to check that the date paid column is null as this needs to show before it is paid.

i need to of these graphs, one unique to the userid who has logged in and only shows his clients amount due and another that shows all the users in the databases totals for their clients.

can someone help me configure this please, if need be a support ticket to work on and complete this project as i am desperate to get this done.

thanks again for your support

Well... I wouldn't start with the chart.

Start by just creating a recordset that returns the correct data. It will be a matter of writing the correct SQL statements to get the data that you want.

Unfortunately it will be difficult for anyone to help you without knowing exactly what your database structure is and what data you are trying to get precisely from what columns.

You will probably have to give it some sort of try first, then post the SQL statement you are using and what does and doesn't work with it and somebody can probably help from there. There really isn't enough specific information about your database to give you the specific information you are looking for about how to get the information out of your database.

hi ray,

my post with the query to get amount due on datassist forum/search all clients is the recordset i would be using to generate the graph.

could you help?

thanks again ray.

I see... perhaps a UNION JOIN was correct then ;) hard to give good advice out of context I guess.

haha thanks ray, so when generating my graph can i add this custom query in somewhere to generate?

thanks again

Yeah, you would be able to edit the recordset on the data page that is used to generate the chart.

The hard part is getting the correct query, from there using it to chart should be pretty easy.

thanks ray so can you create the chart from a recordset? is this through the wizard or does it need to be edited in the files generate by dynamic web charts?

so do you think i should change the query you gave me to union now?

thanks again

Yeah, if you are doing a pie chart you will want to use a UNION.

Instead of joining in the user data, I'd probably pass in the userid through a url parameter and create a separate recordset for the user information.

Then add where clauses to each of your union select statements to filter those results by user as well.

thanks ray, so if i was not using a pie chart just use JOIN?

also where do i add it to generate the graph? is it in the files generate and add in manually?

thanks again ray

Yeah, just use the joins to get data if you aren't charting it. UNION SELECTS would be needed to get the separate rows for each data set that charting would require.

You could just create a static graph or a basic recordset driven graph using the wizard, then you would open the data file to add your recordsets and minipulate the xml from there.