Adjusting reportwidth dynamically.. please help me

hi,

I am new to reporting services.I am using sqlserver Reporting services to generate Report.I have designed a static RDL file based on a view which contains 40 columns..i included all the 40 column in rdl file..i have used hidden field expressions for hiding columns..Now at run time if i select three columns for generating report, The remaining hidden column spaces are still there in the report ... The report width is not adjusting to the selected column width... how to eliminate the hidden column spaces in the report.Please help me..it is really urgent for me..

Thanks in advance...

You can change the width of the ReportViewer in the Load() event like so...

ReportViewer1.Width=500

I think if you try to do it in another event it has no effect becasue the size has already been rendered.

Good luck.

|||

try to set the width = "auto"

Ad-hoc Report Generation Based on the Input Query!

Hi all,

I want to generate the ad-hoc report using SQL Reporting Services 2000 or SQL Reporting Services 2005. But the Database Server will be SQL Server 2000. So, Can I send the query at run-time and then attach the data source to the actual report. In this case there will not be any columns etc. at design time with the value field mentioned in it. I know this is not the requirement for which this report tool is developed for. But still if this is possible through some other way round then pls. let me know. Actually we want one generic report which will satisfy all the tables, with diff. conditions mentioned in the select clause and where clause. So, is this possible with this reporting tool? Is there any way out where we want to come up with only one such report by manipulating the datasource of the report at run-time? Pls. let me know.

Thanks & Regards,

Dipali.

There may be a better way to do this in 2005 but the way I acheived dynamic columns in 2000 was to have a maximum number of columns (say 20) that take the value Column01, Column02....Column20 and make sure your data source returns the values under those names. Then you can also pass in the column names as a parameter. That's the only way I could come up with for producing a completely dynamic report.sql

Address layout-no blank line

I'm using SQL Server Reporting Services and need to generate a report listing
addresses. I have to accomodate two address lines. How can I set it up so
that if there is no second address no blank line appears on the reportOn Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report
I'm assuming you are using a table data region. Set the Hidden
property for the second line/row to somthing like this:
=IIF(addressline2 = Nothing, True, False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
HTH
toolman|||I'm VERY NEW to this product. How do you set up a table data region?
Otherwise your reply makes perfect sense.
Thanks!
"toolman" wrote:
> On Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report
> I'm assuming you are using a table data region. Set the Hidden
> property for the second line/row to somthing like this:
> =IIF(addressline2 = Nothing, True, False)
> Make sure you set the property for the row and not individual cells
> otherwise it will not close the white space left by the hidden row.
> HTH
> toolman
>|||I have figured out how to set up a data table, and placed the iif statement
on the address2 line. But when I preview the report I get this message: The
table is in a list that has no group expressions defined for it. The ous a
data region in a list, the list must have group expressions".
Can you advise me which property sets the group expressions?
Thanks!
"Jeannie" wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report|||On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I have figured out how to set up a data table, and placed the iif statement
> on the address2 line. But when I preview the report I get this message: The
> table is in a list that has no group expressions defined for it. The ous a
> data region in a list, the list must have group expressions".
> Can you advise me which property sets the group expressions?
> Thanks!
>
> "Jeannie" wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report- Hide quoted text -
> - Show quoted text -
Jeanie,
This would be much easier if you based your entire report on a table
as opposed to a bunch of text boxes in a list. In my experience with
reporting, tables are the way to go. If you're not sure whether you
can do this, post an example of how you want the report to look and
maybe I can help.
Assuming we can do this in just a table:
Let's start from a blank slate.
In Layout mode, delete the table, then delete the list so that your
report body is empty.
Now, instead of placing your table inside a list, just place a table
directly into the report body.
Drag and drop your fields from the Datasets pane into the table. You
can add additional rows and columns as necessary.
Once you've got all the fields in place, right click the detail row
handle (gray box with three horizontal lines in it on the far left
side of the table) that contains your second address.
>From the drop down, select Properties to open the Properties pane.
Find and expand the Visibility property by clicking the plus sign
box.
Now click the right side of the Hidden property where it says False.
Then click the drop down arrow and select the <Expression> option.
In the text window of the resulting Edit Expression dialog, overwrite
'False' with the IIF statement. Make sure to include the = sign.
Click OK and then preview the report.
With any kind of luck it should perform as we hope.|||I've managed to create the table, but the blank address lines are still
printing.
Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
True, False). I've attached this to the row property, not the field.
I want the report to look like this
ABC Recycling
555 Adams Drive
Marietta, GA 30303 USA
Since there is no partneraddress2, the line is hidden( There is actually a
partneraddress3, but I assume I would follow the same procedure as address2)
Thanks for your help
Jeannie
"toolman" wrote:
> On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I have figured out how to set up a data table, and placed the iif statement
> > on the address2 line. But when I preview the report I get this message: The
> > table is in a list that has no group expressions defined for it. The ous a
> > data region in a list, the list must have group expressions".
> >
> > Can you advise me which property sets the group expressions?
> > Thanks!
> >
> >
> >
> > "Jeannie" wrote:
> > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > addresses. I have to accomodate two address lines. How can I set it up so
> > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > - Show quoted text -
> Jeanie,
> This would be much easier if you based your entire report on a table
> as opposed to a bunch of text boxes in a list. In my experience with
> reporting, tables are the way to go. If you're not sure whether you
> can do this, post an example of how you want the report to look and
> maybe I can help.
> Assuming we can do this in just a table:
> Let's start from a blank slate.
> In Layout mode, delete the table, then delete the list so that your
> report body is empty.
> Now, instead of placing your table inside a list, just place a table
> directly into the report body.
> Drag and drop your fields from the Datasets pane into the table. You
> can add additional rows and columns as necessary.
> Once you've got all the fields in place, right click the detail row
> handle (gray box with three horizontal lines in it on the far left
> side of the table) that contains your second address.
> >From the drop down, select Properties to open the Properties pane.
> Find and expand the Visibility property by clicking the plus sign
> box.
> Now click the right side of the Hidden property where it says False.
> Then click the drop down arrow and select the <Expression> option.
> In the text window of the resulting Edit Expression dialog, overwrite
> 'False' with the IIF statement. Make sure to include the = sign.
> Click OK and then preview the report.
> With any kind of luck it should perform as we hope.
>|||On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> I've managed to create the table, but the blank address lines are still
> printing.
> Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> True, False). I've attached this to the row property, not the field.
> I want the report to look like this
> ABC Recycling
> 555 Adams Drive
> Marietta, GA 30303 USA
> Since there is no partneraddress2, the line is hidden( There is actually a
> partneraddress3, but I assume I would follow the same procedure as address2)
> Thanks for your help
> Jeannie
>
> "toolman" wrote:
> > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > wrote:
> > > I have figured out how to set up a data table, and placed the iif statement
> > > on the address2 line. But when I preview the report I get this message: The
> > > table is in a list that has no group expressions defined for it. The ous a
> > > data region in a list, the list must have group expressions".
> > > Can you advise me which property sets the group expressions?
> > > Thanks!
> > > "Jeannie" wrote:
> > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> > > - Show quoted text -
> > Jeanie,
> > This would be much easier if you based your entire report on a table
> > as opposed to a bunch of text boxes in a list. In my experience with
> > reporting, tables are the way to go. If you're not sure whether you
> > can do this, post an example of how you want the report to look and
> > maybe I can help.
> > Assuming we can do this in just a table:
> > Let's start from a blank slate.
> > In Layout mode, delete the table, then delete the list so that your
> > report body is empty.
> > Now, instead of placing your table inside a list, just place a table
> > directly into the report body.
> > Drag and drop your fields from the Datasets pane into the table. You
> > can add additional rows and columns as necessary.
> > Once you've got all the fields in place, right click the detail row
> > handle (gray box with three horizontal lines in it on the far left
> > side of the table) that contains your second address.
> > >From the drop down, select Properties to open the Properties pane.
> > Find and expand the Visibility property by clicking the plus sign
> > box.
> > Now click the right side of the Hidden property where it says False.
> > Then click the drop down arrow and select the <Expression> option.
> > In the text window of the resulting Edit Expression dialog, overwrite
> > 'False' with the IIF statement. Make sure to include the = sign.
> > Click OK and then preview the report.
> > With any kind of luck it should perform as we hope.- Hide quoted text -
> - Show quoted text -
Now I'm perplexed. I know it works, having done it in most of my
address list reports. I even copied and pasted your expression
directly into one of my existing reports and it worked (all I changed
was the field name). You might try ="" instead of =Nothing but if
your field is truly empty, this shouldn't make a difference. Beyond
that I'm stumped. Sorry|||Sory about the delay in responding. But i didn't return to the client's site
until today. I am happy to say that with your help, I figured it out. The
reason it didn't work is because the cells, that appeared to be null,
actually had 40 spaces in them! Today I ran a query and looked at the data
and noticed that all the 'empty" cells had a block highlighted when I
positioned my cursor on the cell. So I changed the Iif statement to
acknowledge the spaces and it worked.
Thank you so much for your help!
"toolman" wrote:
> On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> > I've managed to create the table, but the blank address lines are still
> > printing.
> >
> > Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> > True, False). I've attached this to the row property, not the field.
> >
> > I want the report to look like this
> > ABC Recycling
> > 555 Adams Drive
> > Marietta, GA 30303 USA
> >
> > Since there is no partneraddress2, the line is hidden( There is actually a
> > partneraddress3, but I assume I would follow the same procedure as address2)
> >
> > Thanks for your help
> > Jeannie
> >
> >
> >
> > "toolman" wrote:
> > > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > > wrote:
> > > > I have figured out how to set up a data table, and placed the iif statement
> > > > on the address2 line. But when I preview the report I get this message: The
> > > > table is in a list that has no group expressions defined for it. The ous a
> > > > data region in a list, the list must have group expressions".
> >
> > > > Can you advise me which property sets the group expressions?
> > > > Thanks!
> >
> > > > "Jeannie" wrote:
> > > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Jeanie,
> > > This would be much easier if you based your entire report on a table
> > > as opposed to a bunch of text boxes in a list. In my experience with
> > > reporting, tables are the way to go. If you're not sure whether you
> > > can do this, post an example of how you want the report to look and
> > > maybe I can help.
> > > Assuming we can do this in just a table:
> > > Let's start from a blank slate.
> > > In Layout mode, delete the table, then delete the list so that your
> > > report body is empty.
> > > Now, instead of placing your table inside a list, just place a table
> > > directly into the report body.
> > > Drag and drop your fields from the Datasets pane into the table. You
> > > can add additional rows and columns as necessary.
> > > Once you've got all the fields in place, right click the detail row
> > > handle (gray box with three horizontal lines in it on the far left
> > > side of the table) that contains your second address.
> > > >From the drop down, select Properties to open the Properties pane.
> > > Find and expand the Visibility property by clicking the plus sign
> > > box.
> > > Now click the right side of the Hidden property where it says False.
> > > Then click the drop down arrow and select the <Expression> option.
> > > In the text window of the resulting Edit Expression dialog, overwrite
> > > 'False' with the IIF statement. Make sure to include the = sign.
> > > Click OK and then preview the report.
> > > With any kind of luck it should perform as we hope.- Hide quoted text -
> >
> > - Show quoted text -
> Now I'm perplexed. I know it works, having done it in most of my
> address list reports. I even copied and pasted your expression
> directly into one of my existing reports and it worked (all I changed
> was the field name). You might try ="" instead of =Nothing but if
> your field is truly empty, this shouldn't make a difference. Beyond
> that I'm stumped. Sorry
>

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding attributes to the root element.

Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
..
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advanceVankayala wrote:

> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Adding attributes to the root element.

Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
...
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advance
Vankayala wrote:

> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Adding all values in one column

I posed this problem a few days ago, but havent been able to generate the results i need.
Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:
Lastname totalsales orderID
--------
doe 1403 510
doe 500 680
doe 200 701

using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a new
field?
netsportsI am not clear on what you are looking for. Can you show an example of the desired output?
|||DECLARE @.sum int
SELECT
@.sum = SUM(TotalSaleS)
FROM
Accounts
This should work.

|||

Terri:

Below is my older post regarding this. I have since put the sql statement in a stored procedure, and got rid of the inline coding:

Dinakar:
How would I use your code in my stored procedure for this?

==

I am trying to get all the sales reps and their combined sales totals for a given queried date, in which i loop thru (using C# while loop) the available sales reps to get their rep IDs, then match it up with their sales results for the day. The portion of my code below is successful in retrieving all the necessary rep IDs; now I have to loop thru the rep IDs and match their sales total for the given day, whereas the each individual sale is represented with the Order_ID. When running this in my Query Analyzer, i notice that it only gives me just one sale per sales rep on the given date, and not the second or third sale if multiple sales exist for the sales rep on this date. Since this is being looped with the While control statement (and it reads the data reader until there are no more available sales rep IDs), what can I add to the While statement to make sure it grabs all the Order_Ids, and then adds them up in the aggregate SUM statement?
string sqlRep = "SELECT SalesRep.ID as repID , SalesRep.LName " +
"FROM SalesRep " +
"WHERE (Terminated IS NULL) AND (tblSalesRep.StartDate < '" + QueriedDay + "') " +
"order by SalesRep.ID asc ";

SqlCommand objCommandDR = new SqlCommand(sqlRep, objConn);
objConn.Open();
///
////-/ = SqlCommand.ExecuteReader();
SqlDataReader drRep = objCommandDR.ExecuteReader();

// main query

while (drRep.Read())
{
repID = drRep.GetInt32(drRep.GetOrdinal("repID"));
LName = drRep.GetString(drRep.GetOrdinal("LName"));
strSQL = "SELECT SalesRep.ID, SalesRep.LName, SUM(Accounts.totalsales) AS totalsalesQueriedDay " +
"FROM SalesRep INNER JOIN " +
"Orders ON SalesRep.ID = Orders.SalesRep_ID INNER JOIN " +
"Accounts ON Orders.ID = Accounts.Order_ID " +
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = '" + repID + "' " +
"GROUP BY SalesRep.ID, SalesRep.LName " +
"HAVING (SUM(Accounts.totalsales) >= 0) " +
"ORDER BY SalesRep.LName";
////-/Debug.WriteLine(strSQL);

// create an instance of the command-connxt object
SqlCommand objCommand = new SqlCommand(strSQL, objConn2);
objConn2.Open();

SqlDataReader drTotalsales = objCommand.ExecuteReader();

}

|||In your code if SalesRepID is numeric you dont need to put quotes around it.
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = " + repID +
Better yet, use Parameterized Queries. Besides syntax issues, it also helps you against SQL Injection Attacks.|||

The Sql has been set to a stored procedure now with parameters correctly coded and pointed to the sproc. I'm just wondering if there is any help you can provide in my questions I have outlined in this thread.
thanx in advance, netsports

|||Please provide the new code and restate your question. I, for one, have gotten lost.
|||Before I get started about redundancy and waste of bandwidth andprocessing, I am going to start by saying that I'm making the widestassumption in what you're trying to do.
We're all aware of the saying that to Assume makes an ass out of youand me, so please bear with me as I make myself look foolish.
I'm assuming SalesRep.ID is the Primary Key, SalesRep.LName hasabsolutely no bearing on the record's uniqueness, andAccounts.totalsales can actually have negative values.
I'll also rewrite the sql statement within the unnecessary loop thatcreates yet another connection to the same database.. again, I won'tget into that.
SELECT
SalesRep.ID,
SalesRep.LName,
SUM(Accounts.totalSales) AS totalsalesQueriedDay
FROM
SalesRep INNER JOIN Orders
ON SalesRep.ID = Orders.SalesRep_ID
INNER JOIN Accounts
ON Orders.ID = Accounts.Order_ID
WHERE
Accounts.TDate = @.QueryDay -- assuming again that this is a procedure
AND SalesRep.ID = @.SalesRepID -- there I go assuming again
GROUP BY
SalesRep.ID,
SalesRep.LName
HAVING
(SUM(Accounts.totalsales) >= 0)
ORDER BY
SalesRep.LName -- I think this is strange to have a salesrep id with multiple last names, but that's just me.

What I'd suggest is to execute this in Query Analyzer with thedata in place of the parameters to ensure that the data exists, and youcan validate the output. I'm not sure since I never notint.ToString(), or things like that, but you could have an exceptionsomewhere that's halting your process.
Basically, you'd want a resultset that states
EmployeeID EmployeeLastName SumOfSales
100254 WhoCares 10000.00
258642 JoMomma 200000.00

Right?
|||

All SQL Server aggregate functions ignore NULL except COUNT(*) so if totalsales allow NULL add COUNT(*) to SUM. Another option is supper aggregate GROUPING with ROLLUP and CUBE operators. The text below is from the BOL (books online). Hope this helps.
(This example groups royalty and aggregate advance amounts. The GROUPING function is applied to the royalty column.

USE pubs
SELECT royalty, SUM(advance) 'total advance',
GROUPING(royalty) 'grp'
FROM titles
GROUP BY royalty WITH ROLLUP

The result set shows two null values under royalty. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the total advance amounts for all royalty groups and is indicated by 1 in the grp column.)