July 30, 2008 at 8:34 am
Hello
I have a slight problem here, when i develop a report which includes order date, ship date or anything thats got to do with date, it displays the oldest date first at the beginning with the most current record at the bottom of the report.
How can i reverse this, I want the most current report to be displayed first at the top of the report page
July 30, 2008 at 9:41 am
Two ways to do it.
1. If you're table in the report has no grouping and no sorting:
Place an ORDER BY clause on your SQL Statement that returns data in reverse order, for example:
ORDER BY OrderDate DESC
2. If you are using grouping or sorting (and this is my preference in any case)
Add a sort statement to your table, if you have no grouping then this is easy, just right click on the table, go to properties, go to sort tab, add the order date and ensure it is marked as descending.
If you have grouping then you need to consider where your order date sorting comes into affect. Let's assume that you have a group on customer, and within each customer you want to have orders shown in reverse date order. You go to the properties of the table, go to groups, go to Details and edit, go to the sort tab, add the order date and ensure it is marked as descending order.
Hope this helps,
Nigel West
UK
July 31, 2008 at 6:54 am
Hello Nigel
Some my report doesn't display in the right format. I am using the following query:
--LOST IN POST REPORT QUERY--
SELECTRIGHT(CONVERT(VARCHAR(10), T_OH.OrderDate, 103), 7) AS [MM/YYYY],
T_OH.OrderDate,
T_OH.OrderID,CustomerID, T_OH.ShipTitle, T_OH.ShipForename, T_OH.ShipSurname,
T_OH.OrderTotal,
T_RR.ReasonID, T_RR.ReasonDesc, T_OH.ShipCounty, ShipCountry
FROMT_OrderHeader AS T_OH, T_ReturnsReasons AS T_RR
WHERET_RR.ReasonID = 'LOST'
ORDER BY T_OH.OrderDate DESC
What i am trying to do is to have all orders within a mth to be group in a descending order, with the most current mth and year appearing at the top, and when that mth group is expanded, it will start with the most current order placed within that mth
(e.g.
07/2008 .....07/31/2008
07/30/2008
07/29/2008
07/28/2008
06/2008.......06/30/2008
06/29/2008
06/28/2008
06/27/2008
July 31, 2008 at 10:16 am
I am not totally sure I understand what your problem is with the report, but one problem I see with your query is that you are not joining the two tables.
Hope this helps.
Steve
August 1, 2008 at 2:15 am
I later re-wrote the query and its something like this:
SELECTDISTINCT
CONVERT(VARCHAR(7), T_OrderHeader.OrderDate, 120) AS [YYYY-MM],
CONVERT(VARCHAR(20),T_OrderHeader.OrderDate, 106) AS OrderDate,
T_OrderHeader.OrderID,T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename, T_OrderHeader.ShipSurname,
T_OrderHeader.OrderTotal, T_ReturnLines.ReasonID, T_ReturnsReasons.ReasonDesc, T_OrderHeader.ShipCountry
FROM T_OrderHeader INNER JOIN
T_Returns ON T_OrderHeader.OrderID = T_Returns.OrderID LEFT JOIN
T_ReturnLines ON T_Returns.ReturnID = T_ReturnLines.ReturnId LEFT JOIN
T_ReturnsReasons ON T_ReturnLines.ReasonID = T_ReturnsReasons.ReasonID
WHERET_ReturnsReasons.ReasonID = 'LOST'
ORDER BY T_OrderHeader.OrderDate DESC
GO
SELECT count(T_OH.OrderTotal)as [total number of orders], sum(T_OH.OrderTotal) as [Total Cost of Order]
from T_OrderHeader AS T_OH, T_ReturnsReasons AS T_RR
WHERET_RR.ReasonID = 'LOST'
--------------
Had to use the distinct keyword to remove duplicate records.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply