Help With Report That Shows Late Sales Orders

Hello Everyone,
I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
reports is coming from a SQL 2000 EE database. All running on Server 2003.
I've been asked to create a report that shows all sales orders that are past
thier due date. I'm having some difficulty in coming up with a way to do this.
The table I'm pulling the information from is named oe_hdr (Order Entry
Header)
In the table there is a field named req_date (required date). This field is
used by the sales staff to enter in the date that the order is due to ship
from our facility. There is also a field named complete. This field is
checked when the order is invoiced, I think.
The sales staff wants a report that shows all orders that have not shipped
by thier due date.
Based on the info I've provided does anyone have an idea how I can make this
report. Maybe an expression or something. If this is not enough info, please
let me know.
Thanks.On May 7, 11:50 am, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Hello Everyone,
> I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> reports is coming from a SQL 2000 EE database. All running on Server 2003.
> I've been asked to create a report that shows all sales orders that are past
> thier due date. I'm having some difficulty in coming up with a way to do this.
> The table I'm pulling the information from is named oe_hdr (Order Entry
> Header)
> In the table there is a field named req_date (required date). This field is
> used by the sales staff to enter in the date that the order is due to ship
> from our facility. There is also a field named complete. This field is
> checked when the order is invoiced, I think.
> The sales staff wants a report that shows all orders that have not shipped
> by thier due date.
> Based on the info I've provided does anyone have an idea how I can make this
> report. Maybe an expression or something. If this is not enough info, please
> let me know.
> Thanks.
So you want the report to ONLY show late orders and nothing else?
Put a where clause that includes this:
select *
from oe_hdr
where getdate() >= req_date and complete = ?
I'm not sure what data is put into that complete field, but whatever
indicated that is it NOT complete, then it should be put where I
placed the question marks. The script will look for any orders where
the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
req_date and the order has not completed - therefore it is overdue.
I hope this helps. A helpful tip, it might be better for your
company's privacy to make up names for tables. You never know how
people may use your information.|||Thanks Ayman,
Since writing my query, I found this in Visual Studio help;
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
What this does is compare the value of the importantdate with todays date
and if the importandate is greater than a day old, it will format the date
font as red, otherwise blue. This gets me closer to what I'm looking for.
I'll massage this and see what i come up with. I will also give your
suggestion a go as well.
Thanks for the privacy info.
"Ayman" wrote:
> On May 7, 11:50 am, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Hello Everyone,
> > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > I've been asked to create a report that shows all sales orders that are past
> > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > The table I'm pulling the information from is named oe_hdr (Order Entry
> > Header)
> > In the table there is a field named req_date (required date). This field is
> > used by the sales staff to enter in the date that the order is due to ship
> > from our facility. There is also a field named complete. This field is
> > checked when the order is invoiced, I think.
> >
> > The sales staff wants a report that shows all orders that have not shipped
> > by thier due date.
> >
> > Based on the info I've provided does anyone have an idea how I can make this
> > report. Maybe an expression or something. If this is not enough info, please
> > let me know.
> > Thanks.
> So you want the report to ONLY show late orders and nothing else?
> Put a where clause that includes this:
> select *
> from oe_hdr
> where getdate() >= req_date and complete = ?
> I'm not sure what data is put into that complete field, but whatever
> indicated that is it NOT complete, then it should be put where I
> placed the question marks. The script will look for any orders where
> the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> req_date and the order has not completed - therefore it is overdue.
> I hope this helps. A helpful tip, it might be better for your
> company's privacy to make up names for tables. You never know how
> people may use your information.
>|||On May 7, 12:25 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Thanks Ayman,
> Since writing my query, I found this in Visual Studio help;
> =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> What this does is compare the value of the importantdate with todays date
> and if the importandate is greater than a day old, it will format the date
> font as red, otherwise blue. This gets me closer to what I'm looking for.
> I'll massage this and see what i come up with. I will also give your
> suggestion a go as well.
> Thanks for the privacy info.
> "Ayman" wrote:
> > On May 7, 11:50 am, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Hello Everyone,
> > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > I've been asked to create a report that shows all sales orders that are past
> > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > Header)
> > > In the table there is a field named req_date (required date). This field is
> > > used by the sales staff to enter in the date that the order is due to ship
> > > from our facility. There is also a field named complete. This field is
> > > checked when the order is invoiced, I think.
> > > The sales staff wants a report that shows all orders that have not shipped
> > > by thier due date.
> > > Based on the info I've provided does anyone have an idea how I can make this
> > > report. Maybe an expression or something. If this is not enough info, please
> > > let me know.
> > > Thanks.
> > So you want the report to ONLY show late orders and nothing else?
> > Put a where clause that includes this:
> > select *
> > from oe_hdr
> > where getdate() >= req_date and complete = ?
> > I'm not sure what data is put into that complete field, but whatever
> > indicated that is it NOT complete, then it should be put where I
> > placed the question marks. The script will look for any orders where
> > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > req_date and the order has not completed - therefore it is overdue.
> > I hope this helps. A helpful tip, it might be better for your
> > company's privacy to make up names for tables. You never know how
> > people may use your information.
Thats a good way to show all the data and filter it by color. Hint:
you can put nicer colors by using their numbers like "#dedab5" in
place of red or blue. If you select the entire data row, then put
that IIF command in the background color box under properties. It will
make the entire row change color as opposed to one cell - usually
easier to see.|||Ayman,
Do you have any suggestions on how to use the Datediff in my query to pull
the reports that are a day late?
Thanks.
"Ayman" wrote:
> On May 7, 12:25 pm, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Thanks Ayman,
> > Since writing my query, I found this in Visual Studio help;
> >
> > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> >
> > What this does is compare the value of the importantdate with todays date
> > and if the importandate is greater than a day old, it will format the date
> > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > I'll massage this and see what i come up with. I will also give your
> > suggestion a go as well.
> >
> > Thanks for the privacy info.
> >
> > "Ayman" wrote:
> > > On May 7, 11:50 am, Damon Johnson
> > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > Hello Everyone,
> > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > > > I've been asked to create a report that shows all sales orders that are past
> > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > Header)
> > > > In the table there is a field named req_date (required date). This field is
> > > > used by the sales staff to enter in the date that the order is due to ship
> > > > from our facility. There is also a field named complete. This field is
> > > > checked when the order is invoiced, I think.
> >
> > > > The sales staff wants a report that shows all orders that have not shipped
> > > > by thier due date.
> >
> > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > report. Maybe an expression or something. If this is not enough info, please
> > > > let me know.
> > > > Thanks.
> >
> > > So you want the report to ONLY show late orders and nothing else?
> > > Put a where clause that includes this:
> > > select *
> > > from oe_hdr
> > > where getdate() >= req_date and complete = ?
> >
> > > I'm not sure what data is put into that complete field, but whatever
> > > indicated that is it NOT complete, then it should be put where I
> > > placed the question marks. The script will look for any orders where
> > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > req_date and the order has not completed - therefore it is overdue.
> >
> > > I hope this helps. A helpful tip, it might be better for your
> > > company's privacy to make up names for tables. You never know how
> > > people may use your information.
> Thats a good way to show all the data and filter it by color. Hint:
> you can put nicer colors by using their numbers like "#dedab5" in
> place of red or blue. If you select the entire data row, then put
> that IIF command in the background color box under properties. It will
> make the entire row change color as opposed to one cell - usually
> easier to see.
>|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.sql