2. PHPRunner General questions
  3. Is this possible with a reporting?
This topic is locked

Is this possible with a reporting?

 		8/28/2009 10:12:45 AM
PHPRunner General questions
C
 chinwag author

Hi
I have two tables, one called CARS which has fields: ID, MANUFACTURER, MODEL and UNIT PRICE. The other table called LOCATION_STOCK which contains ID, CITY, MANUFACTURER, MODEL and lots of other fields that aren't relevant here.
The MANUFACTURER and MODEL in the LOCATION table are dependant drop downs so BMW gives the options of 3-series, 5-series etc
I would like to produce a report that can total-up (sum) the amount of money being held as stock in each CITY. So I would like to be able to see for example:
LONDON - BMW - 3SERIES = 2 items = $95,000

  • 5SERIES = 1 items = $50,000

    MADRID - BMW - 3SERIES = 10 items = $475,000
  • 5SERIES = 1 items = $100,000
    How do I make a report like this that uses info from two tables as phprunner only lets me select one table when creating a report?
C
 		chinwag author 9/3/2009

Anyone?
It works great when I just use LOCATION_STOCK which contains ID, CITY, MANUFACTURER, MODEL
but when I add the other table using only the UNIT PRICE field with the ID, CITY, MANUFACTURER, MODEL from above, the number of records counted is completely random.
Do i need to use a 'group by' or anything?
A
 		alang 9/3/2009

One way would be to create a view as a combination of your tables and then create a report on that.
C
 		chinwag author 9/4/2009

Thanks Alan,
Do i need to follow what is written here http://www.xlinesoft.com/articles/search_sort_lookup_values.htm
Is Jane on holiday!!?? <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=43869&image=1&table=forumreplies' class='bbc_emoticon' alt=':lol:' />
C
 		chinwag author 9/5/2009

I would REALLY appreciate some help with this!
The report works perfectly when I use ID, CITY, MANUFACTURER, MODEL from the first table "LOCATION_STOCK" ...
but as soon as I add the UNIT PRICE field from the second table "CARS" to the same report to sum up the money, it gets the counts completely wrong even for what was correct originally above.
I would like some ideas to figure this out myself.... surely its got to be something to do with how the tables are related to eachother?
J
 		jdu001 9/9/2009



Hi
I have two tables, one called CARS which has fields: ID, MANUFACTURER, MODEL and UNIT PRICE. The other table called LOCATION_STOCK which contains ID, CITY, MANUFACTURER, MODEL and lots of other fields that aren't relevant here.
The MANUFACTURER and MODEL in the LOCATION table are dependant drop downs so BMW gives the options of 3-series, 5-series etc
I would like to produce a report that can total-up (sum) the amount of money being held as stock in each CITY. So I would like to be able to see for example:
LONDON - BMW - 3SERIES = 2 items = $95,000

  • 5SERIES = 1 items = $50,000

    MADRID - BMW - 3SERIES = 10 items = $475,000
  • 5SERIES = 1 items = $100,000
    How do I make a report like this that uses info from two tables as phprunner only lets me select one table when creating a report?


Hello,
Why do you use redundant data (manufacturer and model) in both table ?

It is easier to use a foreign key relationship: make an attribute CAR_ID in the LOCATION_STOCK table and make a foreign key from CAR_ID to ID in the CARS table.
The advantage: The data about a car is stored in one place and linking tables is easier.
Then use the LOCATION_STOCK in the report:

In the query window add the CARS table.

Draw an connection from CAR_ID in LOCATION_STOCK to ID in CARS.

Rightclick on the line.

Choose All on the LOCATION_STOCK side.

Choose Matched only on the CARS side.
Look for other examples in the help-documentation (SQL query page, Query Designer) about linking tables.
Success,
Jo