Adding subreport values

I have a _very_ simple table with just one row and three columns.
The first column of the row contains a subreport that retrieves a single
value.
The second column of the row contains a subreport that retrieves a single
value.
...and heres where I lose it... :-)
The third column should contain the sum of column1 and column2. Period.
Please help. Thanks :-)
jdjespersenIf all you are doing is returning one value from the sub-report then why
use the sub-report at all in the first place?
Secondly, you can't add two sub-reports together. Just because your
sub-reports only return one value does not mean that the main report will
see it as such. If you plugged a sub-report in to that area that returned
40 rows of data and then tried to add those together with another
sub-reports output what would you expect to see?
So in essence you can't do what you are trying to do since you can't
reference the subreport the way you are trying.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> Subject: Adding subreport values
> Date: Fri, 26 Oct 2007 13:41:07 +0200
> Lines: 17
> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> format=flowed;
> charset="iso-8859-1";
> reply-type=original
> Content-Transfer-Encoding: 7bit
> I have a _very_ simple table with just one row and three columns.
> The first column of the row contains a subreport that retrieves a single
> value.
> The second column of the row contains a subreport that retrieves a single
> value.
> ...and heres where I lose it... :-)
> The third column should contain the sum of column1 and column2. Period.
> Please help. Thanks :-)
> jdjespersen
>
>|||Hi Chris, and thanks for replying...
> If all you are doing is returning one value from the sub-report then why
> use the sub-report at all in the first place?
In short, 'cause i suck. :-( I'll try to explain.
Data is in an Analysis Services database, and MDX is not my strongpoint.
I was hoping that by splitting my queries into seperate datasources, I could
get away with much simpler queries. But, not being able to use data from
two datasets within a single table control, i figured i could do it with
subreports.
Imagine a desired report table layout like this. Not the most complex, i
admit:
Company Last Years Sales Current Sales
Total
Adv.Works 10000 4000
14000
Northwind 3200 2000
5200
Designing the above query may not be rocket science, but as far as MDX goes,
i'm more of a soapbox-car scientist. And not even a good one... :-/
FYI, I do have a time dimension on my datasource.
Any help greatly appreciated.
Jeppe Jespersen
Denmark
> Secondly, you can't add two sub-reports together. Just because your
> sub-reports only return one value does not mean that the main report will
> see it as such. If you plugged a sub-report in to that area that returned
> 40 rows of data and then tried to add those together with another
> sub-reports output what would you expect to see?
> So in essence you can't do what you are trying to do since you can't
> reference the subreport the way you are trying.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
>> From: "Jeppe Jespersen" <jdj@.jdj.dk>
>> Subject: Adding subreport values
>> Date: Fri, 26 Oct 2007 13:41:07 +0200
>> Lines: 17
>> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> format=flowed;
>> charset="iso-8859-1";
>> reply-type=original
>> Content-Transfer-Encoding: 7bit
>> I have a _very_ simple table with just one row and three columns.
>> The first column of the row contains a subreport that retrieves a single
>> value.
>> The second column of the row contains a subreport that retrieves a single
>> value.
>> ...and heres where I lose it... :-)
>> The third column should contain the sum of column1 and column2. Period.
>> Please help. Thanks :-)
>> jdjespersen
>>
>|||Trust me Analysis Services is something I try to stay away from so I feel
your pain. When it comes to MDX I have almost no idea. Try the analysis
services newsgroup and see if they can give you a hand on creating a query
that will pull back the data you need in one dataset. That way you can
avoid the subreports altogether.
Good luck. You'll need it writing those queries ;)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> References: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
<FQVdkw$FIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: Adding subreport values
> Date: Fri, 26 Oct 2007 21:31:32 +0200
> Lines: 87
>
> Hi Chris, and thanks for replying...
> > If all you are doing is returning one value from the sub-report then why
> > use the sub-report at all in the first place?
> In short, 'cause i suck. :-( I'll try to explain.
> Data is in an Analysis Services database, and MDX is not my strongpoint.
> I was hoping that by splitting my queries into seperate datasources, I
could
> get away with much simpler queries. But, not being able to use data from
> two datasets within a single table control, i figured i could do it with
> subreports.
> Imagine a desired report table layout like this. Not the most complex, i
> admit:
> Company Last Years Sales Current Sales
> Total
> Adv.Works 10000 4000
> 14000
> Northwind 3200 2000
> 5200
> Designing the above query may not be rocket science, but as far as MDX
goes,
> i'm more of a soapbox-car scientist. And not even a good one... :-/
> FYI, I do have a time dimension on my datasource.
> Any help greatly appreciated.
> Jeppe Jespersen
> Denmark
>
>
> >
> > Secondly, you can't add two sub-reports together. Just because your
> > sub-reports only return one value does not mean that the main report
will
> > see it as such. If you plugged a sub-report in to that area that
returned
> > 40 rows of data and then tried to add those together with another
> > sub-reports output what would you expect to see?
> >
> > So in essence you can't do what you are trying to do since you can't
> > reference the subreport the way you are trying.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> Subject: Adding subreport values
> >> Date: Fri, 26 Oct 2007 13:41:07 +0200
> >> Lines: 17
> >> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> >> MIME-Version: 1.0
> >> Content-Type: text/plain;
> >> format=flowed;
> >> charset="iso-8859-1";
> >> reply-type=original
> >> Content-Transfer-Encoding: 7bit
> >>
> >> I have a _very_ simple table with just one row and three columns.
> >>
> >> The first column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> The second column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> ...and heres where I lose it... :-)
> >>
> >> The third column should contain the sum of column1 and column2. Period.
> >>
> >> Please help. Thanks :-)
> >>
> >> jdjespersen
> >>
> >>
> >>
> >
>
>|||> Trust me Analysis Services is something I try to stay away from so I feel
> your pain. When it comes to MDX I have almost no idea. Try the analysis
> services newsgroup and see if they can give you a hand on creating a query
> that will pull back the data you need in one dataset. That way you can
> avoid the subreports altogether.
> Good luck. You'll need it writing those queries ;)
Eeeek. And this is one of the simpler queries I'll need.
Thanks for trying to help.
Jeppe Jespersen
Denmark

Adding SSIS to sql 2005 cluster

The basics
Win2k Enterprise
SQL 2005 Enterprise
2 node cluster, single instance.
So I have had these servers up for a little while and would like to add SSIS
to them.
I found this document here about running setup from the command line for a
cluster
http://msdn2.microsoft.com/en-gb/library/ms144259.aspx
this document for installing SSIS from the command line (not in a cluster)
http://msdn2.microsoft.com/en-gb/library/ms144291.aspx
and this about installing SSIS (not in a cluster)
http://support.microsoft.com/kb/913967
and this about how to configure SSIS on a cluster.
http://msdn2.microsoft.com/en-gb/library/ms345193.aspx
As you can see from this last document, step three seems trivial,
"3. Install Integration Services on each node of the cluster individually."
Can anyone help me put this all together.
I think the main thing I need is the command line to install SSIS on a
cluster.
Also, if anyone knows of any good books on HA SQL, that would be nice, I
looked at a stack of SQL2005 books last night and was surprised to find that
HA and Clustering are hardly mentioned.
Chris
The Installer for SSIS is "not" cluster aware.
You will need to install SSIS on each node of the cluster.
If you install it on Node 1 while the SQL server is running on that
node, the installer will install SSIS on Node 1 only.
You will have to run the install a second time on Node 2, preferably
while the SQL is running on Node 2
Once you have SSIS installed on both nodes, you can follow the
instructions in the document on configuring SSIS on a cluster,
remembering that after you make any changes to the MsDtsSrvr.ini.xml
file you need to restart the SSIS service . That should work in your
configuration. It gets real tricky when you have an active/active setup
(I am still struggling with that myself)
Sorry I cant help with the command line, perhaps a more command line
savy person can help with that.
CDSuperG wrote:
> The basics
> Win2k Enterprise
> SQL 2005 Enterprise
> 2 node cluster, single instance.
> So I have had these servers up for a little while and would like to add SSIS
> to them.
> I found this document here about running setup from the command line for a
> cluster
> http://msdn2.microsoft.com/en-gb/library/ms144259.aspx
> this document for installing SSIS from the command line (not in a cluster)
> http://msdn2.microsoft.com/en-gb/library/ms144291.aspx
> and this about installing SSIS (not in a cluster)
> http://support.microsoft.com/kb/913967
> and this about how to configure SSIS on a cluster.
> http://msdn2.microsoft.com/en-gb/library/ms345193.aspx
> As you can see from this last document, step three seems trivial,
> "3. Install Integration Services on each node of the cluster individually."
> Can anyone help me put this all together.
> I think the main thing I need is the command line to install SSIS on a
> cluster.
> Also, if anyone knows of any good books on HA SQL, that would be nice, I
> looked at a stack of SQL2005 books last night and was surprised to find that
> HA and Clustering are hardly mentioned.
> Chris
|||You can visit www.jumpstarttv.com. There is a good video on the site. You
will have to register before you can see the site, but I did and it helped a
lot.
"Jim" wrote:

> The Installer for SSIS is "not" cluster aware.
> You will need to install SSIS on each node of the cluster.
> If you install it on Node 1 while the SQL server is running on that
> node, the installer will install SSIS on Node 1 only.
> You will have to run the install a second time on Node 2, preferably
> while the SQL is running on Node 2
> Once you have SSIS installed on both nodes, you can follow the
> instructions in the document on configuring SSIS on a cluster,
> remembering that after you make any changes to the MsDtsSrvr.ini.xml
> file you need to restart the SSIS service . That should work in your
> configuration. It gets real tricky when you have an active/active setup
> (I am still struggling with that myself)
> Sorry I cant help with the command line, perhaps a more command line
> savy person can help with that.
> CDSuperG wrote:
>

Adding ndf files

Hi I have prod DB about 16GB and only single file we plan to move to enterprise artitecture and will be more used in future so planning to add more data files for parallelism and better performance. My idead is to have additional 20GB ndf files but ho wdo i configure the existing datafile and addiotnal ndf files

Do i restore the DB with a single file and add other files but how do I make sure that the data is spreading across evenly. Can i limit the original file to 20GB and add the other files as 20GB

PLS can anyone give me som ehelp

AdminAnup

restore the database and make sure it's working correctly. Then use 'alter database' statement to add the ndf file to a different filegroup. Then you can re/create a clustered index with drop_existing to move the table(s) to the new datafile.

Take a look at 'alter database' and 'create index' in book online for additional details.

|||yes I understand that part my question was more on the autogrow properties and how data is spread across. All these are in single filegroup

say restoring the DB there is one file 16GB then what are the options for this I mean do I limit it to be 20GB

then I add 2 more ndf files each with 20GB what are the options like do I set autogrowth by 1GB or 10%

I am not sure how data is written in each of these and for each the settings of autogrowth to make sure I have a cnsistent 20GB 3 files

Adding Multiple Values into a row/column help

Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row

basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.

I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.

Thanks!The following example will collect the values from a column in a select statement and create a delimited list from the values.

This will denormalize the values from the source table into a single column so the destination table will not meet the requirements of first normal form. This may be best used for reporting operations, that said:

Two tables are created, one to hold values for the list, and one where the results are inserted.

Test values are inserted into the test table and then a select statement collects the values. (Example supports only 4000 characters)

--Create Test Table
CREATE TABLE dbo.test (
dataField NVARCHAR(10) NOT NULL,
PRIMARY KEY (dataField)
)
GO

--Create Results Table
CREATE TABLE dbo.testResults (
resultId INT IDENTITY (1,1) NOT NULL,
result NVARCHAR(4000) NOT NULL,
PRIMARY KEY (resultId)
)
GO

--Insert Test Data
INSERT dbo.test (dataField) values ('here')
INSERT dbo.test (dataField) values ('there')
INSERT dbo.test (dataField) values ('everywhere')

--Verify Test Data
SELECT dataField FROM dbo.test

--Retrieve colon delimited list of dataField without a cursor
DECLARE @.collectValues NVARCHAR(4000)
SET @.collectValues = ('')

SELECT
@.collectValues = @.collectValues + dataField + ';'
FROM dbo.test

--Verify delimited list
SELECT @.collectValues

--Insert into result table
INSERT dbo.testResults
(result)
VALUES
(@.collectValues)

--Verify inserted data
SELECT resultId, result FROM dbo.testResults

The last select statement should return the result value:
everywhere;here;there;|||Im confused, this doesnt seem like I could get the results correctly from this, You could just use one single select to get all the data like that from that, but what if this works as above, then how would it diferentiate from members and there intrests. Let me give an exampe. Member1 has intrests of fishing,boating,camping, member2 has intrests of fising,hiking,running, how would i basically convert the below

table one

customer intrest

member1 fishing
member1 boating
member1 camping
member2 fishing
member2 hiking
member2 running

go from that data, to this data

table two

customer intrests
member1 fishing;boating;bamping
member2 fising;hiking;running

I dont think the above example can do this can it? Or Am I just missing something? Thanks! hehe|||You are absolutely correct. I misread your intention as wanting the value for a single person (as though you would add this update to a procedure for updating the base table, etc...).

Adding multiple textboxes to PageHeader

I am creating matrix reports programmatically and have come up against a problem where I can only add a single textbox into the pageheader. Writing the report using the report designer software I can add multiple textboxes to the pageheader, however trying to add many of them using the ReportItem object I keep getting a syste.object[] cannot be used in this context. error.

for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };
}

This is the line of code that I am hitting the problem. This works fine, however only inserts the last textbox in the Items Array.

If I change the code to

for (int i = 0; i < Items.Count; i++)

{

reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };

}

It executes, but fails when trying to render it into XML.

Has anyone managed to get multiple textboxes programatically into the pageheader?In the first approach you are overwriting the Items array with each iteration of the for loop. And in the second approach you are setting each entry in the Items array to a new object array, so you end up with an array of arrays. Instead what you should do is just add the object to the array. Try using the following code.

// create a new array for the text boxes in the header
reportItems.Items = new object[Items.Count];

// iterate though all the Items creating a textbox, which is added to the report items object array
for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle);
}

Ian

Adding comments to TEXT field...

Newbie question:

Aside from the single quote (i.e. chr(39)) what other characters can cause
MS-SQL
server to cough-up the insertion / update back in your face?

TIAA. Nonymous (someone@.hotmail.com) writes:
> Aside from the single quote (i.e. chr(39)) what other characters can cause
> MS-SQL
> server to cough-up the insertion / update back in your face?

None, what I can think of. Well if you use the double " as delimiter,
then this is the odd one out. But you can only use " with certain
settings.

Anyway, to include the string delimiter in the string, you double it:

SELECT 'Three o''clock'

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding Carriage Return and Line Feeds to a text box

Hi All,

I am trying to print 10 values in a single cell in a text box.....

To make it readable I put carriage returns and line feeds into the boxes.

These rendered ok in visual studio but did not render ok in explorer....no carriage return or line feed.

This is what I added...

=Fields!segment_sdesc_00.Value & chr(10) & chr(13)

Can anyone advise me as to what is wrong with this? There must be a way to put a cr/lf or newline into a report in a text box.....

Thanks in Advance

Peter

www.peternolan.com

Try using the + sign over the ampersand.

Code Snippet

=Fields!segment_sdesc_00.Value + chr(10) + chr(13)

This has worked for me in Explorer so far.

|||

If you are working with SRS and need to combine two CRM fields together, but having each on its own line, use the following expression:

=Fields!ShipToName.Value & vbCRLF & Fields!ShipToAddress.Value

vbCRLF is a special system constant that contains the values of carriage return and line feed characters.

- Mitch Milam’s Microsoft Discussions

|||

Hi All,

thanks for the tips...will try both....

Peter

|||

Hi Simone,

some of the fields are numbers and from memory it complained about the plus sign when there was a mixture of numeric and string fields...but I will try again.

Thanks

Peter

Adding Article/Table!

Hello All,
I'm using Transactional Replication and it's Push subscription.
Can you plz give me the complete steps to add a single table w/o starting
snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
I just tried to do this:
sp_addarticle 'pubs','jobs','jobs'
I then generated my snapshot - and restarted my distribution agent. Only the
jobs table went over in the new snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Naveed" <nrehman@.marsonssoft.com> wrote in message
news:OqzXJ94qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm using Transactional Replication and it's Push subscription.
> Can you plz give me the complete steps to add a single table w/o starting
> snapshot for all tables.
> And another question,
> BOL says sp_refreshsubscription for Pull and
> sp_reinitsubscription is for Push Subscription.
> But I saw newgroup an it says BOL is wrong sp_refreshsubscription works
for
> both.
> What is the difference b/w sp_refreshsubscription and
> sp_reinitsubscription?
> Thanks in advance.
> Naveed.
>
>

Adding an additional CPU SQL 2005 Enterprise

Hi,
I have 2 SQL 2005 Enterprise servers currently running one a single CPU
license. I have now purchased an additional enterprise CPU license and would
like to `activate` an additional CPU.
How would I do this?
Many thanks
RichardIf you had more than 1 processor on the machine you were violating the
license agreement to begin with. If you have 2 then you need two licenses
regardless of how many you intended to use. The license does not prohibit or
enable the use of the processors.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
> Hi,
> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
> license. I have now purchased an additional enterprise CPU license and
> would like to `activate` an additional CPU.
> How would I do this?
> Many thanks
> Richard|||Ok, thanks for that. So I now have a 2 CPU license I can just add the second
CPU and it will be fine no need to worry about changing settings in SQL at
all.
Thanks a lot,
Richard
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
> If you had more than 1 processor on the machine you were violating the
> license agreement to begin with. If you have 2 then you need two licenses
> regardless of how many you intended to use. The license does not prohibit
> or enable the use of the processors.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>|||By adding the 2nd CPU do you mean physically installing one? If so then
yes, the OS & SQL Server should detect the 2nd CPU and it will know what to
do without changing any settings unless you already changed some from the
defaults.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:31A8B061-7F53-4EB2-AA6A-F96AB2103149@.microsoft.com...
> Ok, thanks for that. So I now have a 2 CPU license I can just add the
> second CPU and it will be fine no need to worry about changing settings in
> SQL at all.
> Thanks a lot,
> Richard
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
>> If you had more than 1 processor on the machine you were violating the
>> license agreement to begin with. If you have 2 then you need two licenses
>> regardless of how many you intended to use. The license does not prohibit
>> or enable the use of the processors.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
>> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>