| Groups | Blog | Home |
sql server reporting services : Help With Report That Shows Late Sales Orders
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.
On May 7, 11:50 am, Damon Johnson
[quoted text, click to view] <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. [quoted text, click to view] "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
[quoted text, click to view] <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. [quoted text, click to view] "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
[quoted text, click to view] <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
[quoted text, click to view] <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.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |