December 19, 2008 at 3:56 am
Hi. I have been asked to develop a report that returns quote data. The user enters a value for the start date & end date (quote effective date).
The added a group to the report in Visual Studio and included all fields as the user only wants to return a single line for each order.
My query currently looks as follows:
SELECT SalesOrders.SalesOrderId AS BidNumber,Users.FirstName, Users.LastName,EffectiveDate AS Date,
Customers.CustomerName AS Customer,Countries.CountryId AS DestinationSite,
SUM(SalesOrderItems.CurItemValue) AS 'Value', CurItemValue / 2 AS FactoredValue,SalesOrders.DueDate AS OrderExpectedDate
FROM SalesOrderItems
INNER JOIN SalesOrders ON SalesOrderItems.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN Countries ON Customers.Country = Countries.Country
INNER JOIN Users ON SalesOrders.CreatedUser = Users.UserName
WHERE SalesOrders.SystemType = 'Q' AND EffectiveDate BETWEEN @StartDate AND @EndDate AND Customers.CustomerName NOT LIKE 'MyCompany%'
GROUP BY SalesOrders.SalesOrderId,Users.LastName,Users.FirstName,EffectiveDate,Customers.CustomerName,Countries.CountryId,CurItemValue,SalesOrders.DueDate
How would I sum Order Value by MONTH in the group footer based on the values entered in the report parameters?
Kind Regards,
Phil.
PS: Is there a better way to filter out: AND Customers.CustomerName NOT LIKE 'MyCompany%'
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 4:11 am
OK there is a flaw in my formatting! If I group every column then add to the group footer:
=MONTH(First(Fields!Date.Value, "dsBidReport")) & " " & Sum(Fields!Value.Value, "dsBidReport")
The Value 12 + Sum of Order Value is displayed under every record.
I would like the sum for Order Value to appear only once as: December: £25,795
Can any of you guys guide me in the right direction?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 7:04 am
OK moving on............The user either wants to select Orders by date range or simply return all Orders.
="TOTAL FOR MONTH:" & MONTH(First(Fields!Date.Value, "dsBidReport"))
Should display as December & not 12.
I have tried a few variations only managing to return errors!
Thanks,
Phil
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 7:22 am
I have managed to format the month:
="TOTAL FOR MONTH:" & MonthName(MONTH(First(Fields!Date.Value, "dsBidReport")))
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply