Linking Options and default messages 1

[jadepatel]

Hi,

I have created a report from 2 tables:

Menu (MealName, MealCost)
Meal Bookings (MealName, MealQty, BookingDate)

The report is supposed to show all the meals and the amount of times they have been booked. between a specified date range.
Therefore i want it to show the all the meals on the menu even if some of them have not been booked.

At present the report only shows the meals that have been booked. How can i change this to show even those that havnt been booked.

I Tried changing the linking option to do a Right outer join on: MealBooking.BookingNO = Menu.BookingNo
but this did not work. does anyone know what im doin wrong?

A second question i have is how i display a default message when the results of the record selection does not return any data???

Any help is appreciated

 

[Turkbear]

Hi,
If meals is the leftmost table in your linking expert, then use a LEFT Outer Join to meal bookings, I think..

 

[jadepatel]

I tried this but it didnt work for some reason. Im i right in saying that i set this option by right clicking on the 'database fields' and selecting 'Visula linking expert'. I then right clicked on the link and selected 'linking options'. I tried to select both the right join and the left join but neither had any effect on my report result!!

 

[kchaudhry]

Have you checked your record selection formula? You might have something over there which is preventing the report from displaying all the records.

Kchaudhry

 

[lbass]

As kchaudry suggested, once you have created the left join which should be FROM {menu.bookingno} TO {meals.bookingno}, you should not limit the records by adding any selection criteria using fields from the meals table--this would in essence cancel the left join. Instead use a detail level formula like:

if {meals.bookingdate} in {?date} then 1 else 0 //where {?date} is a discrete date range parameter

Then you can insert summaries (SUMs, not counts) on the formula to get the results you need.

This will allow all entries from the menu table to appear even with no bookings or with bookings from dates outside the specified date range.

-LB

 

[jadepatel]

Hey Ibass,

I have the following record selection formula:

{Meal_Bookings.RoomDt} in ({?DateRange})

i tried changing this formula to what u suggested but i get the following message:
The result of a record selection formula must be a
boolean.

What am i doin wrong?
This is what i typed:
if {Meal_Bookings.RoomDt} in ({?DateRange}) then 1 else 0

 

[lbass]

The formula should be created in the formula editor, NOT in the record selection formula area. As I mentioned, if you limit records in the mealbookings table in the selection formula, it will undermine the left join, so that not all of the menu records will be returned. Remove the selection criteria (for any mealbookings fields) from the select statement and then create the formula in the formula editor and add it to the report canvas.

-LB

 

[mysorian]

Your SQL query should look like this for showing meal names not on order:
-------------------------------------------------------
SELECT
MealOrder.`MealName`, MealOrder.`MealQty`, MealOrder.`Order_date`,
MName.`MealName`
FROM
`MealOrder` MealOrder right OUTER JOIN `MName` MName ON MealOrder.`MealName` = MName.`MealName`

 

[jadepatel]

Hey lBass,

Im almost there. I have removed the record selection formula and i have added the formula and created a right Join From

Menu to MealBookings

The report nopw correctly shows all the maels from the menu table. )

The only problem i have now is that the report does not seem to be filtered for the date specifed in the date range!!! It does not seem to take the parameters into consideration. Is there something im missing??

Thx guys for all your help

 

[lbass]

You need a LEFT join from menu to mealbookings if you want to show all meals in the menu table. Let's say your date parameter is set for February 1 to February 29, 2004. Put the formula I suggested earlier (let's call it {@BookedInPeriod} on the report. You will now see the following:

MealName BookedDate BookedInPeriod
Steak 03/13/04 0
Fish 02/14/04 1
Chicken 01/23/04 0
Liver 0
Veggie 0
Tacos 02/17/04 1
Total Meals = 6 Total Booked in Period = 2

If you wanted to, you could suppress the booked date if it is not in the period, by using a field suppression formula of {@BookedInPeriod} = 0.

If you have an equal join you would get instead:

MealName BookedDate BookedInPeriod
Steak 03/13/04 0
Fish 02/14/04 1
Chicken 01/23/04 0
Tacos 02/17/04 1
Total Meals = 4 Total Booked in Period = 2

...and you would be missing two meals.

If you have a left join but add date criteria to the record selection, you would get:

MealName BookedDate BookedInPeriod
Fish 02/14/04 1
Tacos 02/17/04 1
Total Meals = 2 Total Booked in Period = 2

...and you would be missing four meals.

If you have a left join and make your date period selection criteria: isnull({mealbookings.date}) or {mealbookings.date} = {?date}, you would get:

MealName BookedDate BookedInPeriod
Fish 02/14/04 1
Liver 0
Veggie 0
Tacos 02/17/04 1
Total Meals = 4 Total Booked in Period = 2

...and again you would be missing two meals. That's why you should not put any selection criteria on the mealbookings table.

-LB

 

[jadepatel]

Hello lBass,

I have tried both the left and right join. The left join from Menu.MealName = MealBookings.MeanName works fine. I can see all the meals listed on my report even where the meal has not been booked.
Thx for detailed explanation above )

The problem i have is more to do with the record selection.
Since i got rid of the record selection formula and replaced it with a normal formula
(if {meals.bookingdate} in {?date} then 1 else 0),
the report does not seem to be filtered correctly. Instead of showing the meals booked between a certain date range, i get all the meal bookings shown!!
I have added the formula to the report canvas as u advised. The date panel shows up when i run the application and i select a begin and end date but this does not seem to have any effect on the results obtained!!!

Is there anything else i need to do???
M<any thx

 

[Turkbear]

Hi,
As long as the date field is in mealbookings table and you use the Left outer join, the use of that field in the selection criteria will cause problems..

If you can edit the Sql directly yoiu can try this:
( Oracle syntax, the only one I know)

{?date} = {mealbookings.date}(+)

In other words indicate a left outer join on that selection criterion as well..

 

[lbass]

What do you want your final display to look like? As long as you want all meals to show up, you need to not place any selection on the mealsbookings table. But, if you want to only show the booking dates for those meals booked in the period, then right click on the booking date field->format field->common->suppress->x+2 and enter:

not({mealsbooking.date} in {?date})

You can also suppress {@BookedInPeriod} when zero by using the format->number->customize->suppress when 0. Using my first example and using the month of Feb as your parameter date range, this would give you a display like:

MealName BookedDate BookedInPeriod
Steak 0
Fish 02/14/04 1
Chicken 0
Liver 0
Veggie 0
Tacos 02/17/04 1
Total Meals = 6 Total Booked in Period = 2

If this is not what you are looking for, please provide a sample of what you would like to see.

-LB

 

[jadepatel]

Hello lBass

Here is alittle background on my data.....

This is what my 2 tables look like:
Menu (MealName(PK), MealCost)
-contains a list of all meals provided by the
company.

Meal Bookings (RoomNo(PK), BookingDate(PK), Session(PK),
MealName(PK), MealQty)
- contains only a list of meals that have been booked.
Session can be either - morning, afternoon, evening.
A guest has to book the entire room for a session.
i.e a guest can book restaurant1 for the
morning session on the 12/1/2004. That guest can
then book many meals withing that booking.
No other guest can now book restaurant1 for the
morning session on 12/1/2004.

What i want to display on my report is the number of meals booked between a certain date range. I will therefore need to use BookingDate from the Meal_Bookings table as a parameter for my record selection somewhere in this report!!
I want all the meals displayed on the report whether they have been booked in that period or not so that it is easy to see what meals are not selling therefore can be eliminated from the menu.

Lets assume my full menu contains the follwing meals:
Chicken Burger,
Chips,
Pizza,
Steak,
Pie,
Ice cream

My report currently will only display the meals that have been booked for a specifed date range (i.e if i choose
1/12/2004 - 31/12/2004 my report will show all meal bookings within that range:

01/12/2004
Ice cream 22% Count
Pizza 58% Count
Chicken Burger 20% Count
------
SUM

15/12/2004
Pizza 100% Count
------
SUM



I want it to show all of the meals:
01/12/2004
Chicken Burger 20% Count
Chips 0% Count
Pizza 58% Count
Steak 0% Count
Pie 0% Count
Ice cream 22% Count
------
SUM

15/12/2004
Pizza 100% Count
Chicken Burger 0% Count
Chips 0% Count
Steak 0% Count
Pie 0% Count
Ice cream 0% Count
------
SUM

At present i have grouped on BookingDate and MealName.

 

[lbass]

I'm not sure what the remaining problem is--maybe you should explain what you think is not working. If you follow my suggestions and then insert sums on {@BookedInPeriod} to get your group summaries and grand total and also use this formula in your percentage calculations, the report should work as you wish.

-LB