Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Adjusting Employee Hours

I wrote a post earlier, but my manager has asked that I do it
differently. I have a table with employee hours for particular
projects. (I work for a telemarketing company)
Table1:
Project Task Emp Hours
A1 TPV 1 5
A1 TPV 2 2
A3 AUDIT 3 4
TOTAL: 11
A2 ITM 4 10
A2 ITM 5 15
TOTAL: 25
I need to figure out the adjusted hours for these employees. The end
result should look like this:
Project Task Emp Hours AdjHours
A1 TPV 1 5 0
A1 TPV 2 2 0
A3 AUDIT 3 4 0
A2 ITM 4 10 14.4
A2 ITM 5 15 21.6
I need to add the hours for the TPV and Audit records and adjust
against the ITM records by doing the following calculation:
Emp1, 2, 3 hours (tpv and audit) = 11 hours
Emp4, 5 hours = 25 hours
Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
(project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
14.4
Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
(project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
21.6
I cannot hardcode anything. I need to use the table2 to figure out the
projects I am totaling and the projects to adjust against.
Table2:
Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%
Billable = 1 tells me that projects A1 and A3 need to be adjusted
against project A2.
Please, can anyone help?
Thanks,
NinelHi
The principles are the same as your previous post.
SELECT p.Project, p.Emp, p.Task, p.Hours,
CASE WHEN p.Task in ('Audit','TPV') THEN 0 ELSE p.hours + (p.Hours/t.total1)
* t.total2 END
FROM #ProjectTime P,
( SELECT CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN 0 ELSE Hours END)
AS DECIMAL(8,3)) as Total1,
CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN Hours ELSE 0 END) AS
DECIMAL(8,3)) as Total2
FROM #ProjectTime ) t
John
"ninel" wrote:

> I wrote a post earlier, but my manager has asked that I do it
> differently. I have a table with employee hours for particular
> projects. (I work for a telemarketing company)
> Table1:
> Project Task Emp Hours
> A1 TPV 1 5
> A1 TPV 2 2
> A3 AUDIT 3 4
> TOTAL: 11
> A2 ITM 4 10
> A2 ITM 5 15
> TOTAL: 25
> I need to figure out the adjusted hours for these employees. The end
> result should look like this:
> Project Task Emp Hours AdjHours
> A1 TPV 1 5 0
> A1 TPV 2 2 0
> A3 AUDIT 3 4 0
> A2 ITM 4 10 14.4
> A2 ITM 5 15 21.6
> I need to add the hours for the TPV and Audit records and adjust
> against the ITM records by doing the following calculation:
> Emp1, 2, 3 hours (tpv and audit) = 11 hours
> Emp4, 5 hours = 25 hours
> Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
> (project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
> 14.4
> Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
> (project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
> 21.6
> I cannot hardcode anything. I need to use the table2 to figure out the
> projects I am totaling and the projects to adjust against.
> Table2:
> Project Task AdjProject Billable PercentBillable
> A1 TPV A2 1 100%
> A2 ITM 0 0
> A3 AUDIT A2 1 100%
> Billable = 1 tells me that projects A1 and A3 need to be adjusted
> against project A2.
> Please, can anyone help?
> Thanks,
> Ninel
>

Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

Addtion of Table in merge replication

Hi,
I am dealing with merge replication (104 tables/database).
I need to add some new tables in replication. There are two options
1. Stop the replication and publish all tables including new tables then
again set the replication.
2. Create a new subscription for these new tables alone
Advise me, Which one is better?
Thanks,
Soura.
SouRa,
you can just add the new table to the existing publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This will cause an complete snapshot to be generated and distributed. A new
publication might be the answer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uCxBq%23hsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> SouRa,
> you can just add the new table to the existing publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||AFAIR, although the new snapshot will include all tables, only the new table
will get distributed.
Cheers,
Paul
|||Hi,
I think u can add additional articles by using the System SP
sp_addmergearticle .
After you've added it, you run the snapshot agent. This will generate a
complete snapshot but only the new article will be propagated by the merge
agent.
If ur having the MR option as NotSync then have this table exists in
Subscriber also.
Once u tested in test server, u can implement in production.
regards,
Herbert
"SouRa" wrote:

> Hi,
> I am dealing with merge replication (104 tables/database).
> I need to add some new tables in replication. There are two options
> 1. Stop the replication and publish all tables including new tables then
> again set the replication.
> 2. Create a new subscription for these new tables alone
> Advise me, Which one is better?
> Thanks,
> Soura.
>

Address table structure

Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql

Address table structure

Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter
.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:

> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country
.
> State is specific to US, in Canada they have provinces, other contries hav
e
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...[vbcol=seagreen]
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>

AddNew then getting Unique ID

I have an auto-incremental field in my sql database table. After I add a
new record I need to get that ID. My below code adds the record with no
problems but the ID field I request always comes back empty. If I look in
the table the new record is there with the auto ID field.
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
hr = pRstPubInfo->Open("messages",
_variant_t((IDispatch*)pConnection,true)
,
adOpenKeyset,adLockOptimistic,adCmdTable
);
pRstPubInfo->AddNew();
hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
hr = pRstPubInfo->Update();
_variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
//DBID = EMPTY.Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
are not optimal for performing inserts!
Anyway, then you could do this in one transaction and retrieve the output
variable:
CREATE PROCEDURE dbo.AddRow
@.value VARCHAR(32),
@.idOut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT table(column) SELECT @.value;
SELECT @.idOut = SCOPE_IDENTITY();
END
GO
(Alternatively, you could use a scalar/resultset or, since it is an INT, you
could buck standard practice and use the return value.)
A
"Bob" <msgdev@.hotmail.com> wrote in message
news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>I have an auto-incremental field in my sql database table. After I add a
>new record I need to get that ID. My below code adds the record with no
>problems but the ID field I request always comes back empty. If I look in
>the table the new record is there with the auto ID field.
>
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
> hr = pRstPubInfo->Open("messages",
> _variant_t((IDispatch*)pConnection,true)
,
> adOpenKeyset,adLockOptimistic,adCmdTable
);
> pRstPubInfo->AddNew();
> hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
> hr = pRstPubInfo->Update();
> _variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
> //DBID = EMPTY.
>|||I am adding a binary object to the database. It could be very large so I
thought using AddChunk would be better. Is there a way to add binary data
using stored procedures? Is there a way to add chunks? May be I am doing
this all wrong. Any help would be appreciated.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
> Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
> are not optimal for performing inserts!
> Anyway, then you could do this in one transaction and retrieve the output
> variable:
> CREATE PROCEDURE dbo.AddRow
> @.value VARCHAR(32),
> @.idOut INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT table(column) SELECT @.value;
> SELECT @.idOut = SCOPE_IDENTITY();
> END
> GO
> (Alternatively, you could use a scalar/resultset or, since it is an INT,
> you could buck standard practice and use the return value.)
> A
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||AppendChunk can be used for Parameter objects as well as Field objects.
Bob wrote:
> I am adding a binary object to the database. It could be very large
> so I thought using AddChunk would be better. Is there a way to add
> binary data using stored procedures? Is there a way to add chunks?
> May be I am doing this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE PROCEDURE sp_InsertBLOB
@.ID int = NULL OUT,
@.BLOB image = NULL
AS
SET NOCOUNT ON
INSERT INTO BLOBTable ( BLOB ) VALUES( @.BLOB )
SELECT @.ID = SCOPE_IDENTITY()
END
GO
The strange thing to me is that you are worried about the efficiency of
sending a "large binary object" to the server, but you are willing to pull
down an entire table full of them just to perform an insert?
John
"Bob" wrote:

> I am adding a binary object to the database. It could be very large so I
> thought using AddChunk would be better. Is there a way to add binary data
> using stored procedures? Is there a way to add chunks? May be I am doing
> this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
>
>

Addition of strings

How can i make a sum (concatenation) of strings of one column in a table.

for example i have a table like this

field1 field2

1 abc

1 bcd

2 sdf

2 sdd

I want to get these strings added group by field 1

Thanks

This cannot be done in a single query unless you employ some tricky hack. There is no obvious and reliable solution to do this in a single query. You'll have to use a cursor and a table variable for this purpose.

|||

Hi,

with .Net 2.0 it is very easy. You can write "User defined aggregate" called for example StringConcat and use it in your queries like this

SELECT dbo.StringConcat(field2)FROM MyTableGROUP BY field1

and here is the code for your aggregate

C#

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;using System.IO;using System.Runtime.InteropServices;[Serializable][SqlUserDefinedAggregate( Format.UserDefined,// user-defined serialization IsInvariantToDuplicates =false,// duplicates matter IsInvariantToNulls =true,// don't care about NULLs IsInvariantToOrder =false,// order matters (ignored) IsNullIfEmpty =false,// don't yield NULL if empty set MaxByteSize = 8000)]// maximum size in bytespublic struct StringConcat : IBinarySerialize{private StringBuilder sb;public void Init() {this.sb =new StringBuilder(); }public void Accumulate(SqlString s) {if (s.IsNull) {return;// skip NULLs }else {this.sb.Append(s.Value); } }public void Merge(StringConcat Group) {this.sb.Append(Group.sb); }public SqlString Terminate() {return new SqlString(this.sb.ToString()); }public void Read(BinaryReader r) { sb =new StringBuilder(r.ReadString()); }public void Write(BinaryWriter w) {if (this.sb.Length > 4000)// limit sb to 8000 bytes w.Write(this.sb.ToString().Substring(0, 4000));else w.Write(this.sb.ToString()); }}// end StringConcat

VB

Imports SystemImports System.DataImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextImports System.IOImports System.Runtime.InteropServices' user-defined serialization' duplicates matter' don't care about NULLs' order matters (ignored) ' don't yield NULL if empty set' maximum size in bytes is 8000<Serializable(), _ SqlUserDefinedAggregate( _ Format.UserDefined, _ IsInvariantToDuplicates:=False, _ IsInvariantToNulls:=True, _ IsInvariantToOrder:=False, _ IsNullIfEmpty:=False, _ MaxByteSize:=8000)> _Public Structure StringConcatImplements IBinarySerializePrivate sbAs StringBuilderPublic Sub Init()Me.sb =New StringBuilder()End Sub Public Sub Accumulate(ByVal sAs SqlString)If s.IsNullThen Return' skip NULLsElse Me.sb.Append(s.Value)End If End Sub Public Sub Merge(ByVal GroupAs StringConcat)Me.sb.Append(Group.sb)End Sub Public Function Terminate()As SqlStringReturn New SqlString(sb.ToString())End Function Public Sub Read(ByVal rAs BinaryReader) _Implements IBinarySerialize.Read sb =New StringBuilder(r.ReadString())End Sub Public Sub Write(ByVal wAs BinaryWriter) _Implements IBinarySerialize.WriteIf Me.sb.Length > 4000Then' limit sb to 8000 bytes w.Write(Me.sb.ToString().Substring(0, 4000))Else w.Write(Me.sb.ToString())End If End SubEnd Structure' end StringConcat

I hope this helps.

Let me know if this worked for you.

|||

thanks, But I am looking for something within the sql

|||

Hi,

After compiling the class into a DLL, you can import the DLL as a SQL Server assembly using either the Visual Studio 2005 Deploy option or manually using the CREATE ASSEMBLY statement and CREATE AGGREGATE statement as is shown in the following listing:

 
CREATE ASSEMBLY StringConcatFROM'C:\StringConcat.dll'GOCREATE AGGREGATE StringConcat(@.inputnvarchar)RETURNSnvarcharEXTERNALNAME StringConcat.StringConcatGO
 

Using CLR objects (functions, aggregates...) for string manipulations or complex calculations is way faster than using nested queries or Cursors.

I have used this aggregate in my projects and it works fine.

|||

Hi, jiju-kj-

Of course it is possible to achieve this within the SQL. But you will need nested cursors the first cursor will iterate through distinct values in field1 and the inner cursor will iterate through all values from field2 for the current value in field1. I don't recommend you using cursors. But will decide which way is better for you. Let me know if you have problems with creating the cursors.

Cheers,

Paul

sql

Addition of a number to an INT column

Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?

I guess I could do:

UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12

and keep going like this until I have all the values updated.

Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.

I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2

:D|||Of course - thank you - how could I miss it??

Woods for the trees and all...

Adding/Previewing custom table style

Hi,

I need to add and preview custom table styles. I was able to add custom style by editing the StyleTemplates.xml file under "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles' as per one of the postings here.

Now, how should I add the ability for the user to preview this custom style. I can write a form similar to MS "Choose table style" proobably. Is there a way to launch a particular page of the report wizard programatically?

Thanks,

-Surendra

I have the same problem, Have you find the solution?

Adding/Previewing custom table style

Hi,

I need to add and preview custom table styles. I was able to add custom style by editing the StyleTemplates.xml file under "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles' as per one of the postings here.

Now, how should I add the ability for the user to preview this custom style. I can write a form similar to MS "Choose table style" proobably. Is there a way to launch a particular page of the report wizard programatically?

Thanks,

-Surendra

I have the same problem, Have you find the solution?

Adding visibility toggle to a table group

Hi!
I'm trying to add a visibility toggle to a table group in my report. The
table has one main group and a subgroup. I want to hide initially details
from the subgroup. So I changed the visibility property of this subgroup to
"Hidden" and then choosed that visibility can be toggled by another report
item (which is in my case a textbox containing the name of a subgroup in the
subgroup header). When I generated this report in Excel-format I got 2 spread
sheets: a Document map containing links with the names o my subgroups and a
report without '+/-' toggle boxes. Is it something I'm doing wrong?I didn't see anything wrong. Can you reproduce this issue by creating a new
report which uses the sample database Adventureworks2000 as its data
source? If you can repro, please post the exact steps and then we'll be
able to investigate this issue further.
Also, if you export the sample report "Territory Sales Drilldown" to an
Excel file, do you still have this issue?
What service pack level is your RS at?
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Adding visibility toggle to a table group
>thread-index: AcVUm584XIOzJcqORpunHlK8qwgPdg==>X-WBNR-Posting-Host: 62.97.217.178
>From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>Subject: Adding visibility toggle to a table group
>Date: Mon, 9 May 2005 06:33:01 -0700
>Lines: 10
>Message-ID: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43164
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi!
>I'm trying to add a visibility toggle to a table group in my report. The
>table has one main group and a subgroup. I want to hide initially details
>from the subgroup. So I changed the visibility property of this subgroup
to
>"Hidden" and then choosed that visibility can be toggled by another report
>item (which is in my case a textbox containing the name of a subgroup in
the
>subgroup header). When I generated this report in Excel-format I got 2
spread
>sheets: a Document map containing links with the names o my subgroups and
a
>report without '+/-' toggle boxes. Is it something I'm doing wrong?
>|||Thank you for your answer.
I compared my report with "Territory Sales Drilldown", it uses query string
as datasource and performs grouping there, while I'm using a stored
procedure. Territory sales drilldown report works fine. Does it mean that for
getting this toggle-functionality I have to use query string?
I'm using Service Pack 2 in my reporting services.
Regards,
Anna
"William Wang[MSFT]" wrote:
> I didn't see anything wrong. Can you reproduce this issue by creating a new
> report which uses the sample database Adventureworks2000 as its data
> source? If you can repro, please post the exact steps and then we'll be
> able to investigate this issue further.
> Also, if you export the sample report "Territory Sales Drilldown" to an
> Excel file, do you still have this issue?
> What service pack level is your RS at?
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Adding visibility toggle to a table group
> >thread-index: AcVUm584XIOzJcqORpunHlK8qwgPdg==> >X-WBNR-Posting-Host: 62.97.217.178
> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
> >Subject: Adding visibility toggle to a table group
> >Date: Mon, 9 May 2005 06:33:01 -0700
> >Lines: 10
> >Message-ID: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43164
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >Hi!
> >I'm trying to add a visibility toggle to a table group in my report. The
> >table has one main group and a subgroup. I want to hide initially details
> >from the subgroup. So I changed the visibility property of this subgroup
> to
> >"Hidden" and then choosed that visibility can be toggled by another report
> >item (which is in my case a textbox containing the name of a subgroup in
> the
> >subgroup header). When I generated this report in Excel-format I got 2
> spread
> >sheets: a Document map containing links with the names o my subgroups and
> a
> >report without '+/-' toggle boxes. Is it something I'm doing wrong?
> >
> >
>|||Hi Anna,
I don't think you have to use a query string as the dataset. In the sample
db Adventureworks2000, I created a stored procedure and use it as the
dataset for the "Territory Sales Drilldown" report, deployed it and then
exported the report to an Excel file, the report still worked fine.
Therefore I believe this issue is specific to your report. Can you managed
to create another report using Adventureworks2000 as its data source and
reproduce this issue? You can then send the report to me so that I will
able to test this issue on my end and see find out what has been wrong. My
e-mail address is v-rxwang@.microsoft.com.
Sincerely,
William Wang
Microsoft Online Partner Support
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Adding visibility toggle to a table group
>thread-index: AcVVb0YGcz2tQamgShu62D42ca+yyA==>X-WBNR-Posting-Host: 62.97.217.178
>From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>References: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
<9rqBBERVFHA.3052@.TK2MSFTNGXA01.phx.gbl>
>Subject: RE: Adding visibility toggle to a table group
>Date: Tue, 10 May 2005 07:48:05 -0700
>Lines: 77
>Message-ID: <0E31FF65-52E9-43D8-B395-EF13AE6A77C8@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43296
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Thank you for your answer.
>I compared my report with "Territory Sales Drilldown", it uses query
string
>as datasource and performs grouping there, while I'm using a stored
>procedure. Territory sales drilldown report works fine. Does it mean that
for
>getting this toggle-functionality I have to use query string?
>I'm using Service Pack 2 in my reporting services.
>Regards,
>Anna
>
>"William Wang[MSFT]" wrote:
>> I didn't see anything wrong. Can you reproduce this issue by creating a
new
>> report which uses the sample database Adventureworks2000 as its data
>> source? If you can repro, please post the exact steps and then we'll be
>> able to investigate this issue further.
>> Also, if you export the sample report "Territory Sales Drilldown" to an
>> Excel file, do you still have this issue?
>> What service pack level is your RS at?
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> --
>> >Thread-Topic: Adding visibility toggle to a table group
>> >thread-index: AcVUm584XIOzJcqORpunHlK8qwgPdg==>> >X-WBNR-Posting-Host: 62.97.217.178
>> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>> >Subject: Adding visibility toggle to a table group
>> >Date: Mon, 9 May 2005 06:33:01 -0700
>> >Lines: 10
>> >Message-ID: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.reportingsvcs:43164
>> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> >
>> >Hi!
>> >I'm trying to add a visibility toggle to a table group in my report.
The
>> >table has one main group and a subgroup. I want to hide initially
details
>> >from the subgroup. So I changed the visibility property of this
subgroup
>> to
>> >"Hidden" and then choosed that visibility can be toggled by another
report
>> >item (which is in my case a textbox containing the name of a subgroup
in
>> the
>> >subgroup header). When I generated this report in Excel-format I got 2
>> spread
>> >sheets: a Document map containing links with the names o my subgroups
and
>> a
>> >report without '+/-' toggle boxes. Is it something I'm doing wrong?
>> >
>> >
>>
>|||Hei William!
I've also created a stored procedure in db Adventureworks2000 and then
created a report based on that. It works fine when I run it in preview, but
not when I exporting it to Excel. I send you the project to your mail
address, so you can see what I'm doing wtrong.
"William Wang[MSFT]" wrote:
> Hi Anna,
> I don't think you have to use a query string as the dataset. In the sample
> db Adventureworks2000, I created a stored procedure and use it as the
> dataset for the "Territory Sales Drilldown" report, deployed it and then
> exported the report to an Excel file, the report still worked fine.
> Therefore I believe this issue is specific to your report. Can you managed
> to create another report using Adventureworks2000 as its data source and
> reproduce this issue? You can then send the report to me so that I will
> able to test this issue on my end and see find out what has been wrong. My
> e-mail address is v-rxwang@.microsoft.com.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> >Thread-Topic: Adding visibility toggle to a table group
> >thread-index: AcVVb0YGcz2tQamgShu62D42ca+yyA==> >X-WBNR-Posting-Host: 62.97.217.178
> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
> >References: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
> <9rqBBERVFHA.3052@.TK2MSFTNGXA01.phx.gbl>
> >Subject: RE: Adding visibility toggle to a table group
> >Date: Tue, 10 May 2005 07:48:05 -0700
> >Lines: 77
> >Message-ID: <0E31FF65-52E9-43D8-B395-EF13AE6A77C8@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43296
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >Thank you for your answer.
> >
> >I compared my report with "Territory Sales Drilldown", it uses query
> string
> >as datasource and performs grouping there, while I'm using a stored
> >procedure. Territory sales drilldown report works fine. Does it mean that
> for
> >getting this toggle-functionality I have to use query string?
> >
> >I'm using Service Pack 2 in my reporting services.
> >
> >Regards,
> >
> >Anna
> >
> >
> >"William Wang[MSFT]" wrote:
> >
> >> I didn't see anything wrong. Can you reproduce this issue by creating a
> new
> >> report which uses the sample database Adventureworks2000 as its data
> >> source? If you can repro, please post the exact steps and then we'll be
> >> able to investigate this issue further.
> >>
> >> Also, if you export the sample report "Territory Sales Drilldown" to an
> >> Excel file, do you still have this issue?
> >>
> >> What service pack level is your RS at?
> >>
> >> Sincerely,
> >>
> >> William Wang
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >> --
> >> >Thread-Topic: Adding visibility toggle to a table group
> >> >thread-index: AcVUm584XIOzJcqORpunHlK8qwgPdg==> >> >X-WBNR-Posting-Host: 62.97.217.178
> >> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
> >> >Subject: Adding visibility toggle to a table group
> >> >Date: Mon, 9 May 2005 06:33:01 -0700
> >> >Lines: 10
> >> >Message-ID: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
> >> >MIME-Version: 1.0
> >> >Content-Type: text/plain;
> >> > charset="Utf-8"
> >> >Content-Transfer-Encoding: 7bit
> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >Content-Class: urn:content-classes:message
> >> >Importance: normal
> >> >Priority: normal
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:43164
> >> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >> >
> >> >Hi!
> >> >I'm trying to add a visibility toggle to a table group in my report.
> The
> >> >table has one main group and a subgroup. I want to hide initially
> details
> >> >from the subgroup. So I changed the visibility property of this
> subgroup
> >> to
> >> >"Hidden" and then choosed that visibility can be toggled by another
> report
> >> >item (which is in my case a textbox containing the name of a subgroup
> in
> >> the
> >> >subgroup header). When I generated this report in Excel-format I got 2
> >> spread
> >> >sheets: a Document map containing links with the names o my subgroups
> and
> >> a
> >> >report without '+/-' toggle boxes. Is it something I'm doing wrong?
> >> >
> >> >
> >>
> >>
> >
>|||Hi Anna,
From your report, I see that you have correctly set the Visibility
properties (Hidden and ToggleItem) of the Sales_SalesPerson Header and the
Detail row. However, for each of the two rows, click its row handler, and
then click Edit Group. On the Visibility tab, there are incorrect settings.
You just want to choose the Visible option under "Initial visibility" and
clear the "Visibility can be toggled by another report item" check box.
After that, save the report to an Excel file. This time the Excel file
should be fine.
Feel free to let me know if anything is unclear.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Adding visibility toggle to a table group
>thread-index: AcVWBmyJjdj51eYgT8OKveDvIzn2oA==>X-WBNR-Posting-Host: 62.97.217.178
>From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>References: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
<9rqBBERVFHA.3052@.TK2MSFTNGXA01.phx.gbl>
<0E31FF65-52E9-43D8-B395-EF13AE6A77C8@.microsoft.com>
<4dmYVsdVFHA.3928@.TK2MSFTNGXA01.phx.gbl>
>Subject: RE: Adding visibility toggle to a table group
>Date: Wed, 11 May 2005 01:50:04 -0700
>Lines: 144
>Message-ID: <F4A9DDEC-E0FB-4FAB-8C2C-13AC07DEFAD1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43397
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hei William!
>I've also created a stored procedure in db Adventureworks2000 and then
>created a report based on that. It works fine when I run it in preview,
but
>not when I exporting it to Excel. I send you the project to your mail
>address, so you can see what I'm doing wtrong.
>"William Wang[MSFT]" wrote:
>> Hi Anna,
>> I don't think you have to use a query string as the dataset. In the
sample
>> db Adventureworks2000, I created a stored procedure and use it as the
>> dataset for the "Territory Sales Drilldown" report, deployed it and then
>> exported the report to an Excel file, the report still worked fine.
>> Therefore I believe this issue is specific to your report. Can you
managed
>> to create another report using Adventureworks2000 as its data source and
>> reproduce this issue? You can then send the report to me so that I will
>> able to test this issue on my end and see find out what has been wrong.
My
>> e-mail address is v-rxwang@.microsoft.com.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> --
>> >Thread-Topic: Adding visibility toggle to a table group
>> >thread-index: AcVVb0YGcz2tQamgShu62D42ca+yyA==>> >X-WBNR-Posting-Host: 62.97.217.178
>> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>> >References: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
>> <9rqBBERVFHA.3052@.TK2MSFTNGXA01.phx.gbl>
>> >Subject: RE: Adding visibility toggle to a table group
>> >Date: Tue, 10 May 2005 07:48:05 -0700
>> >Lines: 77
>> >Message-ID: <0E31FF65-52E9-43D8-B395-EF13AE6A77C8@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.reportingsvcs:43296
>> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> >
>> >Thank you for your answer.
>> >
>> >I compared my report with "Territory Sales Drilldown", it uses query
>> string
>> >as datasource and performs grouping there, while I'm using a stored
>> >procedure. Territory sales drilldown report works fine. Does it mean
that
>> for
>> >getting this toggle-functionality I have to use query string?
>> >
>> >I'm using Service Pack 2 in my reporting services.
>> >
>> >Regards,
>> >
>> >Anna
>> >
>> >
>> >"William Wang[MSFT]" wrote:
>> >
>> >> I didn't see anything wrong. Can you reproduce this issue by creating
a
>> new
>> >> report which uses the sample database Adventureworks2000 as its data
>> >> source? If you can repro, please post the exact steps and then we'll
be
>> >> able to investigate this issue further.
>> >>
>> >> Also, if you export the sample report "Territory Sales Drilldown" to
an
>> >> Excel file, do you still have this issue?
>> >>
>> >> What service pack level is your RS at?
>> >>
>> >> Sincerely,
>> >>
>> >> William Wang
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
so
>> >> that others may learn and benefit from your issue.
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >>
>> >> --
>> >> >Thread-Topic: Adding visibility toggle to a table group
>> >> >thread-index: AcVUm584XIOzJcqORpunHlK8qwgPdg==>> >> >X-WBNR-Posting-Host: 62.97.217.178
>> >> >From: "=?Utf-8?B?QW5uYQ==?=" <mercatus2004@.online.nospam>
>> >> >Subject: Adding visibility toggle to a table group
>> >> >Date: Mon, 9 May 2005 06:33:01 -0700
>> >> >Lines: 10
>> >> >Message-ID: <859FB97C-86DE-47F7-AFB7-0F595B90C040@.microsoft.com>
>> >> >MIME-Version: 1.0
>> >> >Content-Type: text/plain;
>> >> > charset="Utf-8"
>> >> >Content-Transfer-Encoding: 7bit
>> >> >X-Newsreader: Microsoft CDO for Windows 2000
>> >> >Content-Class: urn:content-classes:message
>> >> >Importance: normal
>> >> >Priority: normal
>> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >> >Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >> >Xref: TK2MSFTNGXA01.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:43164
>> >> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> >> >
>> >> >Hi!
>> >> >I'm trying to add a visibility toggle to a table group in my report.
>> The
>> >> >table has one main group and a subgroup. I want to hide initially
>> details
>> >> >from the subgroup. So I changed the visibility property of this
>> subgroup
>> >> to
>> >> >"Hidden" and then choosed that visibility can be toggled by another
>> report
>> >> >item (which is in my case a textbox containing the name of a
subgroup
>> in
>> >> the
>> >> >subgroup header). When I generated this report in Excel-format I got
2
>> >> spread
>> >> >sheets: a Document map containing links with the names o my
subgroups
>> and
>> >> a
>> >> >report without '+/-' toggle boxes. Is it something I'm doing wrong?
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>sql

Adding value in a query

I am trying to insert a value numeric + 1 in to db table but i get error when i do this

this is the code

Const SQLAsString ="INSERT INTO [PageHits] ([DefaultPage]) VALUES (@.defaultP)"

Dim myCommandAsNew Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@.DefaultP" +"1", DefaultP.Text.Trim())

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

The Error:

Must declare the variable '@.defaultP'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Must declare the variable '@.defaultP'.

The code you wrote will send a parameter called @.DefaultP1 to the database.

Did you want to increase an existing value in the database? If so, you must use an UPDATE query.sql

Adding Users to a Role in Bulk

I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.

Here is the proc
CREATE proc sp_MyProc
@.DBName varchar(256)
As

Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name

Open UID_Cursor
Declare @.Name as varchar(256)
Declare @.TempString as varchar(8000)

Fetch Next from UID_Cursor into @.Name

While (@.@.Fetch_Status <> -1)
Begin

Set @.TempString = 'sp_addrolemember ''db_datareader'', ''' + @.Name + ''''
Exec(@.TempString)

Fetch Next from UID_Cursor Into @.Name
End

Close UID_Cursor
Deallocate UID_CursorYou may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.|||Originally posted by MCrowley
You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.

It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.

My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .|||OK. I found the issue, which leads to another question.

I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.

My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.

This leads to another issue:
How do I let sp_addrolemember know which database I want it to affect?|||have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):

exec master.dbo.sp_configure 'allow', 1
reconfigure with override
go
declare @.roluid smallint, @.ruidbyte smallint, @.ruidbit smallint
select @.roluid = uid from sysusers
where name = 'db_datawriter' and issqlrole = 1

select @.ruidbyte = ((@.roluid - 16384) / 8) + 1
, @.ruidbit = power(2, @.roluid & 7)
-- update u set roles = convert(varbinary(2048),
select roles = convert(varbinary(2048),
substring(convert(binary(2048), roles), 1, (((@.roluid - 16384) / 8) + 1)-1)
+ convert(binary(1), (@.ruidbit) | substring(convert(binary(2048), roles), @.ruidbyte, 1))
+ substring(convert(binary(2048), roles), @.ruidbyte+1, 2048-@.ruidbyte) ),
updatedate = getdate()
from sysusers u inner join master.dbo.sysxlogins x
on u.sid=x.sid
go
exec master.dbo.sp_configure 'allow', 0
reconfigure with override
go

Adding Unique data

Dear Folks,
I have a table like
Invno Amount
55 100
55 200
56 50
57 150
57 300
57 50
I need to query to make it as
55 - 300
56 - 50
57 - 500
Please help me...
Thanks for your replies in advance.
Regards,
Selvarathinam.SELECT InvNo, SUM(Amount)
FROM Table
GROUP BY InvNo
Selvarathinam wrote:
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.|||Select invno, sum(amount) group by invno
"Selvarathinam" <s.selvarathinam@.gmail.com> wrote in message
news:1149799832.127814.236410@.y43g2000cwc.googlegroups.com...
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.
>|||Thanks a ton...
itz working
Tracy McKibben wrote:
> SELECT InvNo, SUM(Amount)
> FROM Table
> GROUP BY InvNo
>
> Selvarathinam wrote:

Adding trigger or Check contraint

We are using SQL 2K with sp4 and push subscription.
If I add a trigger or a Check contraint (or other contraints) for a column
in a table that is already published in the publishing database, do or don't
I need to do anything in the subscriber database? Please explain the
consequence if I do or don't do anything in the subscriber database.
Wing
That depends on what you are trying to do. If you don't use the check
constraint and there could be DML occurring on the subscriber this check
constraint will not be enforced as it is not there.
Regarding the trigger. Do you need it fired on the subscriber as well? If
the trigger cause DML on tables which are also being replication the DML
will be replicated there as well. If not, you might want to put and fire the
trigger on the subscriber as long as all objects are there as well and the
required data is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:9F33E6B3-86D1-428E-8F9B-05F3707AEFB7@.microsoft.com...
> We are using SQL 2K with sp4 and push subscription.
> If I add a trigger or a Check contraint (or other contraints) for a column
> in a table that is already published in the publishing database, do or
> don't
> I need to do anything in the subscriber database? Please explain the
> consequence if I do or don't do anything in the subscriber database.
> Wing
|||Ok, I guess I have two scenerios.
1 Say I don't need any check contraints or triggers happening on the
subscriber server, does it mean I don't need to do anything to the subscriber
table(s). Even thought the table between the publisher and subscriber is
defferent because of the trigger and contraints, it won't cause any problem.
Am I right?
2. Say if I do want the check contraints and triggers happening on teh
subscriber server, then I will have to manually create them plus any
depending objects in the subscriber server. This is a manual process unless
I reinitialize and do a snap shot again. Am I right?
Another question too, is there a setting somewhere in replication in EM or
using sp_ stored procedure to replicate triggers or contraints?
"Wingman" wrote:

> We are using SQL 2K with sp4 and push subscription.
> If I add a trigger or a Check contraint (or other contraints) for a column
> in a table that is already published in the publishing database, do or don't
> I need to do anything in the subscriber database? Please explain the
> consequence if I do or don't do anything in the subscriber database.
> Wing

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

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

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

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

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

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

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported