Cant Remember how to pass int value to datasource

I have know this error but have completely forgotten how to take care of it. How do I pass the integer values to the SQL statement?

ERROR: Syntax error converting the nvarchar value 'Label' to a column of data type int.

WHERE (LinkInfo.L_State = @.State) AND (LinkInfo.CG_ID > @.CatIDLow) AND (LinkInfo.CG_ID > @.CatIDHigh)

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text" />
<asp:ControlParameter ControlID="Label2" DefaultValue="0" Name="CatIDLow" PropertyName="Text" />
<asp:ControlParameter ControlID="Label3" DefaultValue="1899" Name="CatIDHigh" PropertyName="Text" />
</SelectParameters>

CODE BEHIND

public partial class TopandBottomSelect : System.Web.UI.Page
{
int CG_IDlower;
int CG_IDupper;

protected void Page_Load(object sender, EventArgs e)
{
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
CG_IDlower = 0;
CG_IDupper = 1900;
Label1.Text = Convert.ToString(CG_IDlower);
Label2.Text = Convert.ToString(CG_IDupper);
}
else
{
CG_IDlower = 1899;
CG_IDupper = 3000;
Label1.Text = Convert.ToString(CG_IDlower);
Label2.Text = Convert.ToString(CG_IDupper);
}
GridView1.DataBind();
}
}

Thank you

Add the datatype to the Parameter declarations:

<asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text"Type="Int32" />

|||

The state value is not the problem, just the CatID High and low. I have tried the below with no success.

NEW EDITED CODE Below - same error

ERROR: Input string was not in a correct format.

WHERE (LinkInfo.L_State = @.State) AND (LinkInfo.CG_ID > @.CatIDLOW) AND (LinkInfo.CG_ID < @.CatIDHIGH)">

<asp:ControlParameter ControlID="Label2" Name="CatIDLow" PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="Label3" Name="CatIDHigh" PropertyName="Text" Type="Int32" />

with this Code Behind:

int CG_IDlower;
int CG_IDupper;
int CatIDLOW;
int CatIDHIGH;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
CG_IDlower = 0;
CG_IDupper = 1900;
Label2.Text = Convert.ToString(CG_IDlower);
Label3.Text = Convert.ToString(CG_IDupper);

CatIDLOW = Convert.ToInt32("Label2.Text");
CatIDHIGH = Convert.ToInt32("Label3.Text");
}
else
{
CG_IDlower = 1899;
CG_IDupper = 3000;
Label2.Text = Convert.ToString(CG_IDlower);
Label3.Text = Convert.ToString(CG_IDupper);
CatIDLOW = Convert.ToInt32("Label2.Text");
CatIDHIGH = Convert.ToInt32("Label3.Text");
}
GridView1.DataBind();
}

|||

I am not sure but I think this may have something to do with the object type coming out of the radiobuttonlist control. don't know where to go from where I am.

thank you,

Cant pass 0 in stored procedure parameter

Hi
I have an if clause in my code to add the final parameter value to send to the database.

If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@.C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@.C_ID", Session("C_ID"))
End If

If the user is in the role, the error is triggered saying that @.C_IDis expected by the stored procedure. If i then change the value from 0to 10, the stored procedure works fine.
Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?
Thanking you in advance.Because the parameters.add is overridden based on what is passed (look at them when you hit the left parens). Since you are passing a numerical value of a low number, it thinks that it is the sqldbtype. You will need to cast the value to a string cStr(YOURVALUEHERE).
Nick

Can't install SQL Express

It fails because it can't find sqlncli.msi. I looked in the temp directory and it was there

Log excerpt:
User policy value 'SearchOrder' is 'nmu'
MSI (s) (B4:A4) [20:53:04:843]: User policy value 'DisableMedia' is 0
MSI (s) (B4:A4) [20:53:04:843]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (B4:A4) [20:53:04:843]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Looking for sourcelist for product {50A0893D-47D8-48E0-A7E8-44BCD7E4422E}
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Adding {50A0893D-47D8-48E0-A7E8-44BCD7E4422E}; to potential sourcelist list (pcode;disk;relpath).
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Now checking product {50A0893D-47D8-48E0-A7E8-44BCD7E4422E}
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Media is enabled for product.
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Attempting to use LastUsedSource from source list.
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Trying source c:\3a0fb7a85678459f3572f2\setup\.
MSI (s) (B4:A4) [20:53:04:859]: Note: 1: 2203 2: c:\3a0fb7a85678459f3572f2\setup\sqlncli.msi 3: -2147287037
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.
MSI (s) (B4:A4) [20:53:04:859]: Note: 1: 1706 2: -2147483647 3: sqlncli.msi
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Processing net source list.
MSI (s) (B4:A4) [20:53:04:859]: Note: 1: 1706 2: -2147483647 3: sqlncli.msi
MSI (s) (B4:A4) [20:53:04:859]: SOURCEMGMT: Processing media source list.
MSI (s) (B4:A4) [20:53:05:921]: SOURCEMGMT: Trying media source E:\.
MSI (s) (B4:A4) [20:53:05:921]: Note: 1: 2203 2: E:\sqlncli.msi 3: -2147287038
MSI (s) (B4:A4) [20:53:05:921]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.
MSI (s) (B4:A4) [20:53:05:921]: Note: 1: 1706 2: -2147483647 3: sqlncli.msi
MSI (s) (B4:A4) [20:53:05:921]: SOURCEMGMT: Processing URL source list.
MSI (s) (B4:A4) [20:53:05:921]: Note: 1: 1402 2: UNKNOWN\URL 3: 2
MSI (s) (B4:A4) [20:53:05:921]: Note: 1: 1706 2: -2147483647 3: sqlncli.msi
MSI (s) (B4:A4) [20:53:05:921]: Note: 1: 1706 2: 3: sqlncli.msi
MSI (s) (B4:A4) [20:53:05:921]: SOURCEMGMT: Failed to resolve source
MSI (s) (B4:A4) [20:54:31:734]: Product: Microsoft SQL Server Native Client -- Error 1706. An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.I found an old post that led me to the solution. The proposed solution was to extract the files from SQLEXPR and install SQLNativeClient by hand and then un-install it. When I ran the .msi file, it found an apparent stealth installed SQLNativeClient (Add/Remove did not show it).

Installation completed

Rusty

Cant insert the value NULL into column

I'm trying to modify the data type in a field on a table. I rec'd this message:
Unable to modify tblship_sched
Can't insert the value NULL into column mfg_qty table qc.dbo.Tmp_tblship_sched; Column doesn't allow nulls.
Insert fails.

What is or where is the Tmp table?

Thank you.I am not sure what you mean when you ask where the table is.

SELECT * FROM SYSOBJECTS WHERE NAME = 'Tmp_tblship_sched'

If the table exists then I suggest you check the column constraint for the table in question. Looks like it has a NOT NULL constraint.

You can disable it for your insert.

Good Luck!

Can't I dynamically read from any file? - Connection Manager does not exist

I have a Parent package and a child package.

I have hard coded the value for the User::FilePath variable in the parent package.

I am mapping this variable to the value of the same variable in the Child Package.

I created a directory and sql file: "C:\temp\sqlb.sql". I have verified that the path variable value is passed to the child package by using a Script task with a messgbox call.

How do I define an execute sql task to execute sql file: @.[User::FilePath] & "sqlb.sql". I'm using this expression for the SqlStatementSource property. I have entered the OLEDB server information and specified the SQLSourceType = fileconnection.

However I get the error:

[Execute SQL Task] Error: Connection manager "D:\rlewisdev\ZZ\sqlb.sql" does not exist.

What am I doing wrong?

SQLStatementSource in this case points to a connection manager, not a file. You'll have to build a connection manager, call it "SQLFile" or something and use that as the SQLStatementSource. Then you'll have to change the ConnectionString of the connection manager named "SQLFile" (or whatever you decide to call it.)|||

Hi boston..,

You have to create a connection manager based on file path,

you can do the following steps:

1-create connection manager myfilepath

2-right click on this connection and click properties

3-in properties click on the button ... behind expression property

4-In property expression editor ,select the connection string property, and click button ... in the right column

5-In expression builder window expand the variable tree and select ur path variable and drag it to the expression container.

6-click on evaluate expression

NB: you can save your configuration setting in xml file or regsitry or sql server from "package configuration wizard".

Thanks,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

Can't get sum to work with 2 groups.

Hello, I've got 2 groups (on county, and on precinct) and my Dataset looks
like this:
row1 : county = 1, precinct = 1, count = 500, value = a
row2 : county = 1, precinct = 1, count = 500, value = b
row3 : county = 2, precinct = 3, count = 700, value = c
row4 : county = 2, precinct = 3, count = 700, value = d
The first group is on county, and the second group is on precinct, so it
would look like this:
County 1
Prec 1 Count 500
Value a
Value b
County 2
Prec 2 Count 700
Value c
Value d
What I want is a total --
1200
which sums up the above rows. It has to be on the county group to appear at
the bottom of the county, but it's adding all of the rows. So I get 2400
instead of 1200. I can't provide the scope of the precinct group, because
it's not a containing group (i.e. it's at a lower level). How can I just add
up the displayed values in column? Thanks.This question has been asked various times on this group. I know. I asked
it once. I have not seen an answer or have been able to accomplish this
using Report Designer.
--
Adrian M.
MCP
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hello, I've got 2 groups (on county, and on precinct) and my Dataset looks
> like this:
> row1 : county = 1, precinct = 1, count = 500, value = a
> row2 : county = 1, precinct = 1, count = 500, value = b
> row3 : county = 2, precinct = 3, count = 700, value = c
> row4 : county = 2, precinct = 3, count = 700, value = d
> The first group is on county, and the second group is on precinct, so it
> would look like this:
> County 1
> Prec 1 Count 500
> Value a
> Value b
> County 2
> Prec 2 Count 700
> Value c
> Value d
> What I want is a total --
> 1200
> which sums up the above rows. It has to be on the county group to appear
> at
> the bottom of the county, but it's adding all of the rows. So I get 2400
> instead of 1200. I can't provide the scope of the precinct group, because
> it's not a containing group (i.e. it's at a lower level). How can I just
> add
> up the displayed values in column? Thanks.
>|||I agree. Before posting the question I searched and found a couple of very
similar posts with no clear answer. I would think it's an important issue,
and I have a hard time believing it can't be done. Does anyone have any
ideas?
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This question has been asked various times on this group. I know. I
asked
> it once. I have not seen an answer or have been able to accomplish this
> using Report Designer.
> --
> Adrian M.
> MCP
>
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > Hello, I've got 2 groups (on county, and on precinct) and my Dataset
looks
> > like this:
> >
> > row1 : county = 1, precinct = 1, count = 500, value = a
> > row2 : county = 1, precinct = 1, count = 500, value = b
> > row3 : county = 2, precinct = 3, count = 700, value = c
> > row4 : county = 2, precinct = 3, count = 700, value = d
> >
> > The first group is on county, and the second group is on precinct, so it
> > would look like this:
> >
> > County 1
> > Prec 1 Count 500
> > Value a
> > Value b
> >
> > County 2
> > Prec 2 Count 700
> > Value c
> > Value d
> >
> > What I want is a total --
> > 1200
> >
> > which sums up the above rows. It has to be on the county group to appear
> > at
> > the bottom of the county, but it's adding all of the rows. So I get 2400
> > instead of 1200. I can't provide the scope of the precinct group,
because
> > it's not a containing group (i.e. it's at a lower level). How can I just
> > add
> > up the displayed values in column? Thanks.
> >
> >
>|||Are you wanting specific groups added together or are you just wanting a
grand total?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
>I agree. Before posting the question I searched and found a couple of very
> similar posts with no clear answer. I would think it's an important issue,
> and I have a hard time believing it can't be done. Does anyone have any
> ideas?
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> This question has been asked various times on this group. I know. I
> asked
>> it once. I have not seen an answer or have been able to accomplish this
>> using Report Designer.
>> --
>> Adrian M.
>> MCP
>>
>> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
>> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
>> > Hello, I've got 2 groups (on county, and on precinct) and my Dataset
> looks
>> > like this:
>> >
>> > row1 : county = 1, precinct = 1, count = 500, value = a
>> > row2 : county = 1, precinct = 1, count = 500, value = b
>> > row3 : county = 2, precinct = 3, count = 700, value = c
>> > row4 : county = 2, precinct = 3, count = 700, value = d
>> >
>> > The first group is on county, and the second group is on precinct, so
>> > it
>> > would look like this:
>> >
>> > County 1
>> > Prec 1 Count 500
>> > Value a
>> > Value b
>> >
>> > County 2
>> > Prec 2 Count 700
>> > Value c
>> > Value d
>> >
>> > What I want is a total --
>> > 1200
>> >
>> > which sums up the above rows. It has to be on the county group to
>> > appear
>> > at
>> > the bottom of the county, but it's adding all of the rows. So I get
>> > 2400
>> > instead of 1200. I can't provide the scope of the precinct group,
> because
>> > it's not a containing group (i.e. it's at a lower level). How can I
>> > just
>> > add
>> > up the displayed values in column? Thanks.
>> >
>> >
>>
>|||Using the below example (sorry, this one is more accurate than the first
one), I would like a grand total of the Precinct Count values, displayed at
the county level.
Data Source
row1 : county = 1, precinct = 1, count = 500, value = a
row2 : county = 1, precinct = 1, count = 500, value = b
row3 : county = 1, precinct = 2, count = 600, value = c
row4 : county = 1, precinct = 2, count = 600, value = d
row5 : county = 2, precinct = 1, count = 700, value = e
row6 : county = 2, precinct = 1, count = 700, value = f
County 1
Precinct 1 Count 500
Value a
Value b
Precinct 2 Count 600 Value c
Value d
--
1100 //This is what I want
County 2
Precinct 1 Count 700
Value c
Value d
--
700 //This is what I want
By placing this in the County group footer, the location of the grand total
is correct, but the numbers are doubled. They don't show as 1100, and 700,
but as 2200 and 1400 because (I'm assuming) it's adding all of the rows in
the county group. I can't specify the scope of the sum function to be the
"Precinct" group because it's not a containing group. Any ideas? Thanks.
Jason A.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you wanting specific groups added together or are you just wanting a
> grand total?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> >I agree. Before posting the question I searched and found a couple of
very
> > similar posts with no clear answer. I would think it's an important
issue,
> > and I have a hard time believing it can't be done. Does anyone have any
> > ideas?
> >
> > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> >> This question has been asked various times on this group. I know. I
> > asked
> >> it once. I have not seen an answer or have been able to accomplish
this
> >> using Report Designer.
> >>
> >> --
> >> Adrian M.
> >> MCP
> >>
> >>
> >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> >> > Hello, I've got 2 groups (on county, and on precinct) and my Dataset
> > looks
> >> > like this:
> >> >
> >> > row1 : county = 1, precinct = 1, count = 500, value = a
> >> > row2 : county = 1, precinct = 1, count = 500, value = b
> >> > row3 : county = 2, precinct = 3, count = 700, value = c
> >> > row4 : county = 2, precinct = 3, count = 700, value = d
> >> >
> >> > The first group is on county, and the second group is on precinct, so
> >> > it
> >> > would look like this:
> >> >
> >> > County 1
> >> > Prec 1 Count 500
> >> > Value a
> >> > Value b
> >> >
> >> > County 2
> >> > Prec 2 Count 700
> >> > Value c
> >> > Value d
> >> >
> >> > What I want is a total --
> >> > 1200
> >> >
> >> > which sums up the above rows. It has to be on the county group to
> >> > appear
> >> > at
> >> > the bottom of the county, but it's adding all of the rows. So I get
> >> > 2400
> >> > instead of 1200. I can't provide the scope of the precinct group,
> > because
> >> > it's not a containing group (i.e. it's at a lower level). How can I
> >> > just
> >> > add
> >> > up the displayed values in column? Thanks.
> >> >
> >> >
> >>
> >>
> >
> >
>|||One way to solve this is a sub report. Create a report that takes county as
the report parameter. Then have the report have a single field in it. Make
the report totally minimal. Test it and when it is working drag and drop it
into the cell for the total, then do a right mouse click, parameters and set
the report parameter to the county. When I do this I hide the subreport in
listview so the users don't ever go to it directly. It takes a little
playing with the sub report to get it formatted and lined up the way you
want but it will work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Using the below example (sorry, this one is more accurate than the first
> one), I would like a grand total of the Precinct Count values, displayed
at
> the county level.
> Data Source
> row1 : county = 1, precinct = 1, count = 500, value = a
> row2 : county = 1, precinct = 1, count = 500, value = b
> row3 : county = 1, precinct = 2, count = 600, value = c
> row4 : county = 1, precinct = 2, count = 600, value = d
> row5 : county = 2, precinct = 1, count = 700, value = e
> row6 : county = 2, precinct = 1, count = 700, value = f
> County 1
> Precinct 1 Count 500
> Value a
> Value b
> Precinct 2 Count 600 Value c
> Value d
> --
> 1100 //This is what I want
> County 2
> Precinct 1 Count 700
> Value c
> Value d
> --
> 700 //This is what I want
> By placing this in the County group footer, the location of the grand
total
> is correct, but the numbers are doubled. They don't show as 1100, and 700,
> but as 2200 and 1400 because (I'm assuming) it's adding all of the rows in
> the county group. I can't specify the scope of the sum function to be the
> "Precinct" group because it's not a containing group. Any ideas? Thanks.
> Jason A.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Are you wanting specific groups added together or are you just wanting a
> > grand total?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > >I agree. Before posting the question I searched and found a couple of
> very
> > > similar posts with no clear answer. I would think it's an important
> issue,
> > > and I have a hard time believing it can't be done. Does anyone have
any
> > > ideas?
> > >
> > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > >> This question has been asked various times on this group. I know. I
> > > asked
> > >> it once. I have not seen an answer or have been able to accomplish
> this
> > >> using Report Designer.
> > >>
> > >> --
> > >> Adrian M.
> > >> MCP
> > >>
> > >>
> > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > >> > Hello, I've got 2 groups (on county, and on precinct) and my
Dataset
> > > looks
> > >> > like this:
> > >> >
> > >> > row1 : county = 1, precinct = 1, count = 500, value = a
> > >> > row2 : county = 1, precinct = 1, count = 500, value = b
> > >> > row3 : county = 2, precinct = 3, count = 700, value = c
> > >> > row4 : county = 2, precinct = 3, count = 700, value = d
> > >> >
> > >> > The first group is on county, and the second group is on precinct,
so
> > >> > it
> > >> > would look like this:
> > >> >
> > >> > County 1
> > >> > Prec 1 Count 500
> > >> > Value a
> > >> > Value b
> > >> >
> > >> > County 2
> > >> > Prec 2 Count 700
> > >> > Value c
> > >> > Value d
> > >> >
> > >> > What I want is a total --
> > >> > 1200
> > >> >
> > >> > which sums up the above rows. It has to be on the county group to
> > >> > appear
> > >> > at
> > >> > the bottom of the county, but it's adding all of the rows. So I get
> > >> > 2400
> > >> > instead of 1200. I can't provide the scope of the precinct group,
> > > because
> > >> > it's not a containing group (i.e. it's at a lower level). How can I
> > >> > just
> > >> > add
> > >> > up the displayed values in column? Thanks.
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Thanks for the suggestion Bruce, but there is a problem with this solution -
the table that the subreport has to hit has several million rows. We've
already pulled the data once to display the information and it takes a
couple of seconds, which is acceptable. However if we have to query the data
50 times for 50 counties, the report becomes too slow. Is there any way to
access the data source in the "Code" section of the report so I can loop
through and add the values up myself? I've tried creating a global variable
and incrementing it based on the precinct id, but the problem with this is
that the "County" footer gets evaluated before the precincts grouping, so
it's incorrect. Any other ideas? Thanks.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> One way to solve this is a sub report. Create a report that takes county
as
> the report parameter. Then have the report have a single field in it. Make
> the report totally minimal. Test it and when it is working drag and drop
it
> into the cell for the total, then do a right mouse click, parameters and
set
> the report parameter to the county. When I do this I hide the subreport in
> listview so the users don't ever go to it directly. It takes a little
> playing with the sub report to get it formatted and lined up the way you
> want but it will work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > Using the below example (sorry, this one is more accurate than the first
> > one), I would like a grand total of the Precinct Count values, displayed
> at
> > the county level.
> >
> > Data Source
> > row1 : county = 1, precinct = 1, count = 500, value = a
> > row2 : county = 1, precinct = 1, count = 500, value = b
> > row3 : county = 1, precinct = 2, count = 600, value = c
> > row4 : county = 1, precinct = 2, count = 600, value = d
> > row5 : county = 2, precinct = 1, count = 700, value = e
> > row6 : county = 2, precinct = 1, count = 700, value = f
> >
> > County 1
> > Precinct 1 Count 500
> > Value a
> > Value b
> >
> > Precinct 2 Count 600 Value c
> > Value d
> > --
> > 1100 //This is what I want
> > County 2
> > Precinct 1 Count 700
> > Value c
> > Value d
> > --
> > 700 //This is what I want
> >
> > By placing this in the County group footer, the location of the grand
> total
> > is correct, but the numbers are doubled. They don't show as 1100, and
700,
> > but as 2200 and 1400 because (I'm assuming) it's adding all of the rows
in
> > the county group. I can't specify the scope of the sum function to be
the
> > "Precinct" group because it's not a containing group. Any ideas? Thanks.
> >
> > Jason A.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > Are you wanting specific groups added together or are you just wanting
a
> > > grand total?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > >I agree. Before posting the question I searched and found a couple of
> > very
> > > > similar posts with no clear answer. I would think it's an important
> > issue,
> > > > and I have a hard time believing it can't be done. Does anyone have
> any
> > > > ideas?
> > > >
> > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > >> This question has been asked various times on this group. I know.
I
> > > > asked
> > > >> it once. I have not seen an answer or have been able to accomplish
> > this
> > > >> using Report Designer.
> > > >>
> > > >> --
> > > >> Adrian M.
> > > >> MCP
> > > >>
> > > >>
> > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > >> > Hello, I've got 2 groups (on county, and on precinct) and my
> Dataset
> > > > looks
> > > >> > like this:
> > > >> >
> > > >> > row1 : county = 1, precinct = 1, count = 500, value = a
> > > >> > row2 : county = 1, precinct = 1, count = 500, value = b
> > > >> > row3 : county = 2, precinct = 3, count = 700, value = c
> > > >> > row4 : county = 2, precinct = 3, count = 700, value = d
> > > >> >
> > > >> > The first group is on county, and the second group is on
precinct,
> so
> > > >> > it
> > > >> > would look like this:
> > > >> >
> > > >> > County 1
> > > >> > Prec 1 Count 500
> > > >> > Value a
> > > >> > Value b
> > > >> >
> > > >> > County 2
> > > >> > Prec 2 Count 700
> > > >> > Value c
> > > >> > Value d
> > > >> >
> > > >> > What I want is a total --
> > > >> > 1200
> > > >> >
> > > >> > which sums up the above rows. It has to be on the county group to
> > > >> > appear
> > > >> > at
> > > >> > the bottom of the county, but it's adding all of the rows. So I
get
> > > >> > 2400
> > > >> > instead of 1200. I can't provide the scope of the precinct group,
> > > > because
> > > >> > it's not a containing group (i.e. it's at a lower level). How can
I
> > > >> > just
> > > >> > add
> > > >> > up the displayed values in column? Thanks.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>|||One thing I do a lot is have a total field like that on each line and then
just show it once. For instance I do this (usually this is done in a stored
procedure).
row1 : county = 1, precinct = 1, count = 500, value = a, countytotal = 1100
row2 : county = 1, precinct = 1, count = 500, value = b, countytotal = 1100
row3 : county = 1, precinct = 2, count = 600, value = c, countytotal = 1100
row4 : county = 1, precinct = 2, count = 600, value = d, countytotal = 1100
row5 : county = 2, precinct = 1, count = 700, value = e, countytotal = 700
row6 : county = 2, precinct = 1, count = 700, value = f, countytotal = 700
Then in the group footer for county you reference the field, you don't do a
sum or anything on it.
In your case have a stored procedure, put your existing data in a temp table
then do a
insert #temp2 select county, precinct, distinct(count) from #temp group by
county, precinct
etc
Anyway, you can do this without hitting the base tables a second time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:O01Lax0RFHA.904@.tk2msftngp13.phx.gbl...
> Thanks for the suggestion Bruce, but there is a problem with this
solution -
> the table that the subreport has to hit has several million rows. We've
> already pulled the data once to display the information and it takes a
> couple of seconds, which is acceptable. However if we have to query the
data
> 50 times for 50 counties, the report becomes too slow. Is there any way to
> access the data source in the "Code" section of the report so I can loop
> through and add the values up myself? I've tried creating a global
variable
> and incrementing it based on the precinct id, but the problem with this is
> that the "County" footer gets evaluated before the precincts grouping, so
> it's incorrect. Any other ideas? Thanks.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> > One way to solve this is a sub report. Create a report that takes county
> as
> > the report parameter. Then have the report have a single field in it.
Make
> > the report totally minimal. Test it and when it is working drag and drop
> it
> > into the cell for the total, then do a right mouse click, parameters and
> set
> > the report parameter to the county. When I do this I hide the subreport
in
> > listview so the users don't ever go to it directly. It takes a little
> > playing with the sub report to get it formatted and lined up the way you
> > want but it will work.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > > Using the below example (sorry, this one is more accurate than the
first
> > > one), I would like a grand total of the Precinct Count values,
displayed
> > at
> > > the county level.
> > >
> > > Data Source
> > > row1 : county = 1, precinct = 1, count = 500, value = a
> > > row2 : county = 1, precinct = 1, count = 500, value = b
> > > row3 : county = 1, precinct = 2, count = 600, value = c
> > > row4 : county = 1, precinct = 2, count = 600, value = d
> > > row5 : county = 2, precinct = 1, count = 700, value = e
> > > row6 : county = 2, precinct = 1, count = 700, value = f
> > >
> > > County 1
> > > Precinct 1 Count 500
> > > Value a
> > > Value b
> > >
> > > Precinct 2 Count 600 Value c
> > > Value d
> > > --
> > > 1100 //This is what I want
> > > County 2
> > > Precinct 1 Count 700
> > > Value c
> > > Value d
> > > --
> > > 700 //This is what I want
> > >
> > > By placing this in the County group footer, the location of the grand
> > total
> > > is correct, but the numbers are doubled. They don't show as 1100, and
> 700,
> > > but as 2200 and 1400 because (I'm assuming) it's adding all of the
rows
> in
> > > the county group. I can't specify the scope of the sum function to be
> the
> > > "Precinct" group because it's not a containing group. Any ideas?
Thanks.
> > >
> > > Jason A.
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > > Are you wanting specific groups added together or are you just
wanting
> a
> > > > grand total?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > >I agree. Before posting the question I searched and found a couple
of
> > > very
> > > > > similar posts with no clear answer. I would think it's an
important
> > > issue,
> > > > > and I have a hard time believing it can't be done. Does anyone
have
> > any
> > > > > ideas?
> > > > >
> > > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > > >> This question has been asked various times on this group. I
know.
> I
> > > > > asked
> > > > >> it once. I have not seen an answer or have been able to
accomplish
> > > this
> > > > >> using Report Designer.
> > > > >>
> > > > >> --
> > > > >> Adrian M.
> > > > >> MCP
> > > > >>
> > > > >>
> > > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > > >> > Hello, I've got 2 groups (on county, and on precinct) and my
> > Dataset
> > > > > looks
> > > > >> > like this:
> > > > >> >
> > > > >> > row1 : county = 1, precinct = 1, count = 500, value = a
> > > > >> > row2 : county = 1, precinct = 1, count = 500, value = b
> > > > >> > row3 : county = 2, precinct = 3, count = 700, value = c
> > > > >> > row4 : county = 2, precinct = 3, count = 700, value = d
> > > > >> >
> > > > >> > The first group is on county, and the second group is on
> precinct,
> > so
> > > > >> > it
> > > > >> > would look like this:
> > > > >> >
> > > > >> > County 1
> > > > >> > Prec 1 Count 500
> > > > >> > Value a
> > > > >> > Value b
> > > > >> >
> > > > >> > County 2
> > > > >> > Prec 2 Count 700
> > > > >> > Value c
> > > > >> > Value d
> > > > >> >
> > > > >> > What I want is a total --
> > > > >> > 1200
> > > > >> >
> > > > >> > which sums up the above rows. It has to be on the county group
to
> > > > >> > appear
> > > > >> > at
> > > > >> > the bottom of the county, but it's adding all of the rows. So I
> get
> > > > >> > 2400
> > > > >> > instead of 1200. I can't provide the scope of the precinct
group,
> > > > > because
> > > > >> > it's not a containing group (i.e. it's at a lower level). How
can
> I
> > > > >> > just
> > > > >> > add
> > > > >> > up the displayed values in column? Thanks.
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I think I am going to go with this solution. We were trying to avoid passing
back the redundant totals, but it's looking like the best option. For the
record however I was able to find another solution that I will try to
explain for posterity.
I created two Vb.Net functions in the "Code" section of report properties.
These functions are below:
--Code
Public Dim CurrentPrecinct As String
Public Dim PrecinctTotal As Integer = 0
//Add up the value from the first instance
//of each precinct in the precincts grouping
public Function TotalPrecinctValue(Name as String, Value As Integer)
//If the strings are equal just return the value
If String.Compare(Trim(Name), Trim(CurrentPrecinct)) = 0 Then
Return Value
End If
//If the strings are not equal,
//save the string as the CurrentPrecinct,
//add the total and return the value
CurrentPrecinct = Name
PrecinctTotal = PrecinctTotal + Value
Return Value
End Function
//Reset the PrecinctTotal value
public Function ResetTotalPrecinctValue() As String
PrecinctTotal = 0
return ""
End Function
--End Code
Set the expression on the PrecinctCount value to be:
=Code.TotalPrecinctValue(Fields!Precinct.Value, Fields!Count.Value)
This will always return the same value passed in, but it will add the value
to a global variable if it's a new precinct.
Add a new row beneath the "Precinct" grouping. Under the Count value column,
set the expression for the field on the new row to be:
=Code.PrecinctTotal
This is the value of the global variable. Next set the visibility on the new
row to be:
=IIf(RowNumber("County") = CountRows("County"), False, True)
This makes sure the row is only displayed if it is the last row.
Finally, somewhere in the County row, set one of the fields to be:
=Cose.ResetTotalPrecinctValue()
so that each county group will reset the running precinct total.
Simple, right :) The only problem with this is that when the county is
collapsed, the total appears below the county:
County 1
1100
and our clients are just picky enough to care. So I think we'll go with your
suggestion so that we can just drop the totals on the county line. Thanks
for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23nS4E60RFHA.2356@.TK2MSFTNGP14.phx.gbl...
> One thing I do a lot is have a total field like that on each line and then
> just show it once. For instance I do this (usually this is done in a
stored
> procedure).
> row1 : county = 1, precinct = 1, count = 500, value = a, countytotal =1100
> row2 : county = 1, precinct = 1, count = 500, value = b, countytotal =1100
> row3 : county = 1, precinct = 2, count = 600, value = c, countytotal =1100
> row4 : county = 1, precinct = 2, count = 600, value = d, countytotal =1100
> row5 : county = 2, precinct = 1, count = 700, value = e, countytotal = 700
> row6 : county = 2, precinct = 1, count = 700, value = f, countytotal = 700
> Then in the group footer for county you reference the field, you don't do
a
> sum or anything on it.
> In your case have a stored procedure, put your existing data in a temp
table
> then do a
> insert #temp2 select county, precinct, distinct(count) from #temp group by
> county, precinct
> etc
> Anyway, you can do this without hitting the base tables a second time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:O01Lax0RFHA.904@.tk2msftngp13.phx.gbl...
> > Thanks for the suggestion Bruce, but there is a problem with this
> solution -
> > the table that the subreport has to hit has several million rows. We've
> > already pulled the data once to display the information and it takes a
> > couple of seconds, which is acceptable. However if we have to query the
> data
> > 50 times for 50 counties, the report becomes too slow. Is there any way
to
> > access the data source in the "Code" section of the report so I can loop
> > through and add the values up myself? I've tried creating a global
> variable
> > and incrementing it based on the precinct id, but the problem with this
is
> > that the "County" footer gets evaluated before the precincts grouping,
so
> > it's incorrect. Any other ideas? Thanks.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> > > One way to solve this is a sub report. Create a report that takes
county
> > as
> > > the report parameter. Then have the report have a single field in it.
> Make
> > > the report totally minimal. Test it and when it is working drag and
drop
> > it
> > > into the cell for the total, then do a right mouse click, parameters
and
> > set
> > > the report parameter to the county. When I do this I hide the
subreport
> in
> > > listview so the users don't ever go to it directly. It takes a little
> > > playing with the sub report to get it formatted and lined up the way
you
> > > want but it will work.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > > > Using the below example (sorry, this one is more accurate than the
> first
> > > > one), I would like a grand total of the Precinct Count values,
> displayed
> > > at
> > > > the county level.
> > > >
> > > > Data Source
> > > > row1 : county = 1, precinct = 1, count = 500, value = a
> > > > row2 : county = 1, precinct = 1, count = 500, value = b
> > > > row3 : county = 1, precinct = 2, count = 600, value = c
> > > > row4 : county = 1, precinct = 2, count = 600, value = d
> > > > row5 : county = 2, precinct = 1, count = 700, value = e
> > > > row6 : county = 2, precinct = 1, count = 700, value = f
> > > >
> > > > County 1
> > > > Precinct 1 Count 500
> > > > Value a
> > > > Value b
> > > >
> > > > Precinct 2 Count 600 Value c
> > > > Value d
> > > > --
> > > > 1100 //This is what I want
> > > > County 2
> > > > Precinct 1 Count 700
> > > > Value c
> > > > Value d
> > > > --
> > > > 700 //This is what I
want
> > > >
> > > > By placing this in the County group footer, the location of the
grand
> > > total
> > > > is correct, but the numbers are doubled. They don't show as 1100,
and
> > 700,
> > > > but as 2200 and 1400 because (I'm assuming) it's adding all of the
> rows
> > in
> > > > the county group. I can't specify the scope of the sum function to
be
> > the
> > > > "Precinct" group because it's not a containing group. Any ideas?
> Thanks.
> > > >
> > > > Jason A.
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > > > Are you wanting specific groups added together or are you just
> wanting
> > a
> > > > > grand total?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > > >I agree. Before posting the question I searched and found a
couple
> of
> > > > very
> > > > > > similar posts with no clear answer. I would think it's an
> important
> > > > issue,
> > > > > > and I have a hard time believing it can't be done. Does anyone
> have
> > > any
> > > > > > ideas?
> > > > > >
> > > > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > > > >> This question has been asked various times on this group. I
> know.
> > I
> > > > > > asked
> > > > > >> it once. I have not seen an answer or have been able to
> accomplish
> > > > this
> > > > > >> using Report Designer.
> > > > > >>
> > > > > >> --
> > > > > >> Adrian M.
> > > > > >> MCP
> > > > > >>
> > > > > >>
> > > > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > > > >> > Hello, I've got 2 groups (on county, and on precinct) and my
> > > Dataset
> > > > > > looks
> > > > > >> > like this:
> > > > > >> >
> > > > > >> > row1 : county = 1, precinct = 1, count = 500, value = a
> > > > > >> > row2 : county = 1, precinct = 1, count = 500, value = b
> > > > > >> > row3 : county = 2, precinct = 3, count = 700, value = c
> > > > > >> > row4 : county = 2, precinct = 3, count = 700, value = d
> > > > > >> >
> > > > > >> > The first group is on county, and the second group is on
> > precinct,
> > > so
> > > > > >> > it
> > > > > >> > would look like this:
> > > > > >> >
> > > > > >> > County 1
> > > > > >> > Prec 1 Count 500
> > > > > >> > Value a
> > > > > >> > Value b
> > > > > >> >
> > > > > >> > County 2
> > > > > >> > Prec 2 Count 700
> > > > > >> > Value c
> > > > > >> > Value d
> > > > > >> >
> > > > > >> > What I want is a total --
> > > > > >> > 1200
> > > > > >> >
> > > > > >> > which sums up the above rows. It has to be on the county
group
> to
> > > > > >> > appear
> > > > > >> > at
> > > > > >> > the bottom of the county, but it's adding all of the rows. So
I
> > get
> > > > > >> > 2400
> > > > > >> > instead of 1200. I can't provide the scope of the precinct
> group,
> > > > > > because
> > > > > >> > it's not a containing group (i.e. it's at a lower level). How
> can
> > I
> > > > > >> > just
> > > > > >> > add
> > > > > >> > up the displayed values in column? Thanks.
> > > > > >> >
> > > > > >> >
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>