When i set up a subscription i get the following error
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files
I have found the following on MSDN
http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
The above details fixes for 2000 and 2003 domains however we are on a NT
domain
When i change the account to a domain account (even administrator) when it
is time for the subscription to run it does not execute
does any body have any ideas on how to fix this
thanks
RegWe had the same issue awhile back. Make sure you're up to date on your
service packs. Also, you can check the log file (ReportServerService) for
more error messages and details.
"Reg Besseling" wrote:
> When i set up a subscription i get the following error
> Failure sending mail: The Report Server has encountered a configuration
> error; more details in the log files
> I have found the following on MSDN
> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
> The above details fixes for 2000 and 2003 domains however we are on a NT
> domain
> When i change the account to a domain account (even administrator) when it
> is time for the subscription to run it does not execute
> does any body have any ideas on how to fix this
> thanks
> Reg
>|||It appears that applying MS05-043 , MS05-038 and MS05-039 caused the issue
to start as all was working before
"daw" <daw@.discussions.microsoft.com> wrote in message
news:CA6823E7-640E-4E86-B59F-65049301D885@.microsoft.com...
> We had the same issue awhile back. Make sure you're up to date on your
> service packs. Also, you can check the log file (ReportServerService) for
> more error messages and details.
> "Reg Besseling" wrote:
>> When i set up a subscription i get the following error
>> Failure sending mail: The Report Server has encountered a configuration
>> error; more details in the log files
>> I have found the following on MSDN
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
>> The above details fixes for 2000 and 2003 domains however we are on a NT
>> domain
>> When i change the account to a domain account (even administrator) when
>> it
>> is time for the subscription to run it does not execute
>> does any body have any ideas on how to fix this
>> thanks
>> Reg
>>
Showing posts with label reports. Show all posts
Showing posts with label reports. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
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
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
Monday, March 19, 2012
Help with query field parameters
Hi everyone,
within one of my reports I would like to take an input parameter, feed this into a dataset which is then used to populate the dataset of the query used in the main body of the report. It won't allow me todo this, could you guys offer any advice on how this may be possible?
E.g
Sub Query:
@.personIds = SELECT personid FROM people WHERE name = @.name;
Main Report Query
SELECT * FROM orders WHERE personId IN (@.personIds)
I cannot change the "main report query" as this is actually a stored procedure from an external application vendor. Please help.
Kind regards
Taz
Is the @.Name the parameter in your report?
If so, then set up the population of that parameter up with the SQL:
Select PersonID, Name from People
with the value = PersonID and the label = Name
and then in your main query, the personid is returned as the parameter value.
Hope that helps.
BobP
|||
Hi Bobp, thanks for the response.
The problem I have is that the SQL (SELECT * FROM people) will return 100s of rows. I dont want to populate the drop down with all of these, instead I want the drop down to have generic options (i.e. "David", "James") which when selected perform the query to get ther relvant IDs and then populate the query that the main report is based on.
Does that clarify at all?
Thanks again for your suggestion, do you have any further ideas?
Kind regards
Taz
Yes, actually... one more idea...
Create parameter named @.Name. This is a string, user type in.
Then create another parameter named @.personIDs. This should be a string, with hidden and multi value selected.
Create a dataset, using the following SQL:
Select personid from people where name like '%' + @.name + '%'
Use this new data set as the Default Values Query for the @.PersonIDs parameter.
Then you can pass personid to your main query like this:
SELECT * FROM orders WHERE personId IN (@.personIds)
Make sure that in the parameter list, the @.Name parameter is first in the list.
Let me know if that works for you. I have tested, and am using that approach in several reports where the select list is too long.
Another way to do this would be to make the @.PersonIDs NOT hidden, and also use the new dataset to populate the available values of it, and allow the user to select an individual. This way, if the user types in DAVID, a second parameters asks the user to select from a list of '%David%'
BobP
|||
Bobp,
fantastic, this worked perfectly! many thanks for your help here.
One further question in regards to thw SQL command IN. i.e.
SELECT * FROM people WHERE peopleid IN(1,2,3,4);
is it possible to easily negate the IN within reporting services filters?
I have something like
=Fields!Name.value IN =Parameters!Names.value
How can I negate this?
Any help appreciated, however what you have done so far is fantastic enough! :)
Kind regards
Taz
No problem at all...
I am not sure what you are trying to do with the filter... Could you give some more detail?
by default, the names that come back should be like the names parameter.
Thanks
BobP
|||BobP - BIM wrote:
No problem at all...
I am not sure what you are trying to do with the filter... Could you give some more detail?
by default, the names that come back should be like the names parameter.
Thanks
BobP
Well, I am finding that I am overloading the input parameter for my stored procedure. It has a limit of 4000 characters, and my dynamic SQL is along the region of 5200 characters.
I can generate the SQL for the stored proc in 2 ways, either get the clients who have bought something (small list) or get the clients who haven't bought something (very long list). The former works fine (small list) however when i try to send in the big list it exceeds the limit of the stored proc and thus falls over.
Thus, I thought maybe I could instead return everything and then create a filter on my dataset where I do something like
Expression:
=Fields!ClientName.Value
Operator:
IN
Value:
=Parameters!ResultsOfTheSQLQueryWeCreatedBefore.Value
This works, i.e. show all the rows where the ClientName appears in the Parameter list. I was wonder if there was a simple way of making it show all the rows where the ClientName does NOT appear in the Parameter list without loading the huge list instad (as this would be slow).
Man this is a difficult one to explain. I hope I was succesful. Thanks for your time Bob!
Taz
Help with Query Field Parameters
Hi everyone,
within one of my reports I would like to take an input parameter, feed this
into a dataset which is then used to populate the dataset of the query used
in the main body of the report. It won't allow me todo this, could you guys
offer any advice on how this may be possible?
E.g
Sub Query:
@.personIds = SELECT personid FROM people WHERE name = @.name;
Main Report Query
SELECT * FROM orders WHERE personId IN (@.personIds)
I cannot change the "main report query" as this is actually a stored
procedure from an external application vendor. Please help.
Kind regards
TazWhat is your exact problem? Can you run the stored procedure from Query
Analyzer?
As I said, I don't really understand the issue but if you are trying to do a
master detail report you should be using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> within one of my reports I would like to take an input parameter, feed
> this into a dataset which is then used to populate the dataset of the
> query used in the main body of the report. It won't allow me todo this,
> could you guys offer any advice on how this may be possible?
> E.g
> Sub Query:
> @.personIds = SELECT personid FROM people WHERE name = @.name;
> Main Report Query
> SELECT * FROM orders WHERE personId IN (@.personIds)
> I cannot change the "main report query" as this is actually a stored
> procedure from an external application vendor. Please help.
> Kind regards
> Taz
>|||Sorry, let me try again.
In my main report I have a drop down parameter with 2 values, lets say its
called "Locations" with 2 posible values
Locations:
Leeds
Wakefield
Based on this I need to run a query that will return all the customers
within that region, i.e.
SELECT customerids FROM customer WHERE location=@.location
I would then like to feed the results of this query into the query used in
the dataset that populates the report (which is actually a stored procedure
not a query). i wanted todo this by setting the Parameter within thr dataset
= to the results of the above query (which I tried to create as a seperate
dataset).
You see, the stored procedure is hard coded with a ... "WHERE IN(@.params)" ,
and I would like my above query to populate the @.params parameter for me.
I hope this clarifies in some way, if not, i can have another bash. Finally,
the sceanrio i gave above is not the one I have, however it represents the
same problem. Such, i cant change it.
Thanks
Taz
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7Jffe95GHA.3952@.TK2MSFTNGP04.phx.gbl...
> What is your exact problem? Can you run the stored procedure from Query
> Analyzer?
> As I said, I don't really understand the issue but if you are trying to do
> a master detail report you should be using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> within one of my reports I would like to take an input parameter, feed
>> this into a dataset which is then used to populate the dataset of the
>> query used in the main body of the report. It won't allow me todo this,
>> could you guys offer any advice on how this may be possible?
>> E.g
>> Sub Query:
>> @.personIds = SELECT personid FROM people WHERE name = @.name;
>> Main Report Query
>> SELECT * FROM orders WHERE personId IN (@.personIds)
>> I cannot change the "main report query" as this is actually a stored
>> procedure from an external application vendor. Please help.
>> Kind regards
>> Taz
>
within one of my reports I would like to take an input parameter, feed this
into a dataset which is then used to populate the dataset of the query used
in the main body of the report. It won't allow me todo this, could you guys
offer any advice on how this may be possible?
E.g
Sub Query:
@.personIds = SELECT personid FROM people WHERE name = @.name;
Main Report Query
SELECT * FROM orders WHERE personId IN (@.personIds)
I cannot change the "main report query" as this is actually a stored
procedure from an external application vendor. Please help.
Kind regards
TazWhat is your exact problem? Can you run the stored procedure from Query
Analyzer?
As I said, I don't really understand the issue but if you are trying to do a
master detail report you should be using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> within one of my reports I would like to take an input parameter, feed
> this into a dataset which is then used to populate the dataset of the
> query used in the main body of the report. It won't allow me todo this,
> could you guys offer any advice on how this may be possible?
> E.g
> Sub Query:
> @.personIds = SELECT personid FROM people WHERE name = @.name;
> Main Report Query
> SELECT * FROM orders WHERE personId IN (@.personIds)
> I cannot change the "main report query" as this is actually a stored
> procedure from an external application vendor. Please help.
> Kind regards
> Taz
>|||Sorry, let me try again.
In my main report I have a drop down parameter with 2 values, lets say its
called "Locations" with 2 posible values
Locations:
Leeds
Wakefield
Based on this I need to run a query that will return all the customers
within that region, i.e.
SELECT customerids FROM customer WHERE location=@.location
I would then like to feed the results of this query into the query used in
the dataset that populates the report (which is actually a stored procedure
not a query). i wanted todo this by setting the Parameter within thr dataset
= to the results of the above query (which I tried to create as a seperate
dataset).
You see, the stored procedure is hard coded with a ... "WHERE IN(@.params)" ,
and I would like my above query to populate the @.params parameter for me.
I hope this clarifies in some way, if not, i can have another bash. Finally,
the sceanrio i gave above is not the one I have, however it represents the
same problem. Such, i cant change it.
Thanks
Taz
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7Jffe95GHA.3952@.TK2MSFTNGP04.phx.gbl...
> What is your exact problem? Can you run the stored procedure from Query
> Analyzer?
> As I said, I don't really understand the issue but if you are trying to do
> a master detail report you should be using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23TPfRN95GHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> within one of my reports I would like to take an input parameter, feed
>> this into a dataset which is then used to populate the dataset of the
>> query used in the main body of the report. It won't allow me todo this,
>> could you guys offer any advice on how this may be possible?
>> E.g
>> Sub Query:
>> @.personIds = SELECT personid FROM people WHERE name = @.name;
>> Main Report Query
>> SELECT * FROM orders WHERE personId IN (@.personIds)
>> I cannot change the "main report query" as this is actually a stored
>> procedure from an external application vendor. Please help.
>> Kind regards
>> Taz
>
Subscribe to:
Posts (Atom)