Load Multiple Signed Assemblies

I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:

CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:\dba\bin\Assembly.dll'

CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey

GRANT UNSAFE ASSEMBLY TO CLRAssembler

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler

REVOKE CONNECT SQL FROM CLRAssembler

Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.

The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.

Is this the only way to do this? Or am I missing something?

First of all I think you mean:

CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE FILE = 'D:\dba\bin\Assembly.dll'

FROM FILE = '...' requires a file that has both the public and private key in it, but an assembly has only the public key in it. Also you should be creating this key in the master database.

In order to use an asymmetic key to enable an assembly to be loaded the asymmetric key must be the master database and include public key, but the private key is not required. When FROM EXECUTABLE FILE = '...' is used the only the public key for the asymmetric key is saved. This key can be used to create a login to grant usafe assembly to. Then, assuming the use has the other appropriate permissions, any assembly signed with this key can be loaded with permission_set = unsafe. A single login is used to load all of the assemblies that are signed with the same key... you can't load the same asymmetric key more than once in the same database. You will have to be sure that Visual Studio is signing all your assemblies with the same key. If you are having to create a new login for each assembly it sounds like Visual Studio is creating a new key for each of these assemblies. When you go to the properties for your visual studio project browse for a common key, don't create a new one.

You can create the asymmetric key directly from the snk file that visual studio creates, for example if myKey.snk is the key pair created by visual studio then:

USE master
GO

CREATE ASYMMETRIC KEY [MyAssemblyKey] FROM FILE = 'c:\keys\myKey.snk'
-- remove the private key, no reason to leave it hanging around.
ALTER ASYMMETRIC KEY [MyAssemblyKey] REMOVE PRIVATE KEY

CREATE LOGIN [LoginMyAssemblyKey] FROM ASYMMETRIC KEY [Key MyAssemblyKey]
GRANT EXTERNAL ACCESS ASSEMBLY TO [LoginMyAssemblyKey]

GO

Once you have done this any assemblies signed with myKey.snk can be deployed from visual studio with unsafe permission set.

Dan

Dan

|||

My mistake. I did mean EXECUTABLE FILE.

I started out trying to sign them all with the same key and then loading them individually and dropping the key and login, however this was producing an error (which I can post once I get back into the office).

Do I need to load them in the same batch or script if I want to use the same login? Because I was running them separately.

If not, how do I specify the login to use? I tried using the AUTHORIZATION command with it and it threw a permissions error.

|||

I'm not sure what you mean when you say you drop the login after creating the assembly.

If you drop the login, or take away the login's USAFE ASSEMBLY permission, you will not be able to use the assembly even though even though it has been created. The login created with the assemblies key is required whenever any function from the assembly is used.

Dan

|||

You need only use CREATE ASSEMBLY. Authorization is used to specify an owner, it is not related to whether or not the assembly can be external acess or unsafe. If the assembly is being created WITH EXTERNAL_ACCESS or UNSAFE, SQL Server will use the key inside of the assembly to find the login created with that key, then check the permissions granted to that login. It, in effect, does this whenever a function from that assembly is used too.

Dan

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help meSome examples here:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
--
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

List Page OverRun

Hi

Is it possible to set a list control not to grow? I have multiple tables in a list box and text boxes it is grouped by a particular column it possible to say that if it the list has to steach to the next page because to much information is to display just don’t go over to the next page just hide it or don’t display the rest of the records

No, list boxes will always grow to accomodate their contents. If an instance of a list does not fit on one page, it will be pushed to the next page. Textboxes and images are the only control that will contrain their contents.|||

Hi Brian,

Thank you for replying, so my if i have to run over to the next page for the same employee so if i am using a lot of ReportItems to have print DataSet Data in the page header and footer for eache page the data changes per employee so is it posible to make the report items repete the same data to the next page if the page is for the same employee so i can have the information for the page header and fotter for that page as well?

Thanks

list of strings passed into a parameter

I am trying to pass multiple values as parameters into my update command:

UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@.selectedusersparam)));

I develop my parameter (@.selectedusersparam) using the following subroutine:

PrivateSub btnAddUsersToDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAddUsersToDeployment.Click

Dim iValAsInteger = 0

Dim SelectedCollectionAsString

SelectedCollection =""

If (lsbUsersAvail.Items).Count > 1Then

For iVal = 0To lsbUsersAvail.Items.Count - 1

If lsbUsersAvail.Items(iVal).Selected =TrueThen

SelectedCollection = SelectedCollection &"," & lsbUsersAvail.Items(iVal).Value

EndIf

Next

SelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))

Session.Item("SelectedCollectionSession") = SelectedCollection

SqlDataSource4.Update()

ltlUsersMessage.Text =String.Empty

'UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (@.selectedusersparam))

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"

Else

ltlUsersMessage.Text ="Select users before adding to deployment. Hold Control for multiselect"

EndIf

EndSub

For some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the query

I don't understand why.

hi,

could you debug your application, i want to know what actual query is being passed. you've for loop for some list, while debugging does it go inside this loop or not.

also if i am not wrong you've commented out following line

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")".

seems like it is the cause as i dont see other line for setting updatecommand.

please check & let me know.

regards,

satish

|||

This works but does not use parameters:

ProtectedSub btnRemoveUsersFromDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnRemoveUsersFromDeployment.Click

Dim iVal2AsInteger = 0

Dim SelectedCollection2AsString

SelectedCollection2 =""

If (lstUsersToRemoveFromDeployment.Items).Count > 0Then

For iVal2 = 0To lstUsersToRemoveFromDeployment.Items.Count - 1

If lstUsersToRemoveFromDeployment.Items(iVal2).Selected =TrueThen

SelectedCollection2 = SelectedCollection2 &"," & lstUsersToRemoveFromDeployment.Items(iVal2).Value

EndIf

Next

SelectedCollection2 = Mid(SelectedCollection2, 2, Len(SelectedCollection2))

Session.Item("SelectedCollectionSession") = SelectedCollection2

SqlDataSourceInCurrentDeployment.UpdateCommand ="UPDATE tblUserDetails SET DeploymentNameID = null WHERE ((EmployeeNumber IN (" + SelectedCollection2 +")))"

SqlDataSourceInCurrentDeployment.Update()

Else

MsgBox("Please select user(s) first")

EndIf

EndSub

|||

hi,

i am not clear mate...in earlier post you were passing parameter for DeploymentNameID in update command now you've removed and used null!!!

i m confused, what is actual problem.

regards,

satish.

|||

I have used the same concept for two different situations.

In the last post I did I was removing the Deployment name ID created by the first update command.

cheers.

Ben.

List grouping

I am needed assistance with getting multiple datasets data in one LIST.
I have 1 parent table with 2 child tables.
The parent table contains the primary key for the 2 child tables.
This is a 1 to many relationship to both the child tables where 1 child
table could have 5 records and the other could have 15.
I need data from the parent as well.
I am having a hard time getting this data grouped on the ID when trying to
nest a list inside a list. It does not seem to work.
Any suggestions?
--
<moojjoo/>Hi
I am not sure I understand the requirement but if it is grouping you
are after , why not use a stored procedure to shape your dataset so
that the parent and child ids are returned together. That way you will
not need multiple lists.
Cheers
Shai
On Nov 30, 1:25 am, Moojjoo <Mooj...@.discussions.microsoft.com> wrote:
> I am needed assistance with getting multiple datasets data in one LIST.
> I have 1 parent table with 2 child tables.
> The parent table contains the primary key for the 2 child tables.
> This is a 1 to many relationship to both the child tables where 1 child
> table could have 5 records and the other could have 15.
> I need data from the parent as well.
> I am having a hard time getting this data grouped on the ID when trying to
> nest a list inside a list. It does not seem to work.
> Any suggestions?
> --
> <moojjoo/>|||Shaikat,
I got it to work using Subreports. Pretty cool solution and I believe that
is the right way to do it, but remember there is always more then one way to
skin a cat. Or program as we all know.
I basically had an ID = RaID (Parent) in table "Risk Assessment"
With two other child tables called - "Control Gap" and "Mitigation Control"
both with the foreign key of RaID.
Now there could have been 20 Control Gaps and 2 Mitigation Controls related
to that RaID foreign key plus i was pulling data from the parent table hence
using joins to return all the data, but as you kow repeating rows were be
returned.
So to solve the problem I basically passed the RaID from the SSRS LIST to 2
sub reports as parameters to only return the data based on the RaId and that
way the data would return properly. I read a number of articles and learned
this was the best way to do this since the control gap data and mitigating
data were both sub data of the Parent table.
I am what you would call a high novice to SQL Server and walking with
Reporting services.
I was wondering are there issues with having numerous datasets for reports.
I have yet to see a performance issue. I have based all my datasets from
stored proces and every thing is running great.
My training - reading books and hacking away for 10 years with Microsoft
Products. Thank you Microsoft for helping me earn a pay check. Maybe one
day I will make it as an MVP.
--
<moojjoo/>
"shaikat.das@.gmail.com" wrote:
> Hi
> I am not sure I understand the requirement but if it is grouping you
> are after , why not use a stored procedure to shape your dataset so
> that the parent and child ids are returned together. That way you will
> not need multiple lists.
> Cheers
> Shai
> On Nov 30, 1:25 am, Moojjoo <Mooj...@.discussions.microsoft.com> wrote:
> > I am needed assistance with getting multiple datasets data in one LIST.
> >
> > I have 1 parent table with 2 child tables.
> >
> > The parent table contains the primary key for the 2 child tables.
> >
> > This is a 1 to many relationship to both the child tables where 1 child
> > table could have 5 records and the other could have 15.
> >
> > I need data from the parent as well.
> >
> > I am having a hard time getting this data grouped on the ID when trying to
> > nest a list inside a list. It does not seem to work.
> >
> > Any suggestions?
> >
> > --
> > <moojjoo/>
>|||Glad to see you found a solution :-)
Cheers
Shai

List Control - Group Header RS2000

I need to modify a report that has a list containing other controls,
subreports..etc that can span multiple pages. How can we show a "group
header" on each page rendered withing the list? We tried moving the list
contents into a table cell but the group headers won't repeat on each page.
ThanksI have a similar situation like yours.
Did you find an answer for the question you posted?
Thanks in advance
Narayanan
"gsi" wrote:
> I need to modify a report that has a list containing other controls,
> subreports..etc that can span multiple pages. How can we show a "group
> header" on each page rendered withing the list? We tried moving the list
> contents into a table cell but the group headers won't repeat on each page.
> Thanks
>
>|||Unfortunately no. It's a bit suprising, one would think this should be a
pretty straight foward task.
"Narayanan" <Narayanan@.discussions.microsoft.com> wrote in message
news:4F5DDF64-499D-4AD3-A03C-59DF4AB4E364@.microsoft.com...
>I have a similar situation like yours.
> Did you find an answer for the question you posted?
> Thanks in advance
> Narayanan
>
> "gsi" wrote:
>> I need to modify a report that has a list containing other controls,
>> subreports..etc that can span multiple pages. How can we show a "group
>> header" on each page rendered withing the list? We tried moving the list
>> contents into a table cell but the group headers won't repeat on each
>> page.
>> Thanks
>>|||Using RS2000...
I need to modify a report that has a list containing other controls,
subreports..etc that can span multiple pages. How can we show a "group
header" on each page rendered withing the list? We tried moving the list
contents into a table cell but the group headers won't repeat on each page.
Thanks

List Box - Multiple Columns

Hi,
Any ideas on how to split data from a row set over two columns in a list box or table?
For example I have a data set with 10 rows and I want to display 5 rows in one column and the other 5 in the second column.
I tried creating two tables and filtering the first table to only show the even rows and the second to show odd rows. However the RowNumber(Nothing) function is not allowed in filters - 'RowNumber cannot be used in filters'
Thanks
KevinCould you use a multi-column report and limt the number of rows in each
column?
The major restriction is that you must use a "paged" rendered (PDF, Print
Preview) to see the multiple columns. Following your original posting is a
sample RDL that shows how this is done.
Note the table group expression.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> Hi,
> Any ideas on how to split data from a row set over two columns in a list
box or table?
> For example I have a data set with 10 rows and I want to display 5 rows in
one column and the other 5 in the second column.
> I tried creating two tables and filtering the first table to only show the
even rows and the second to show odd rows. However the RowNumber(Nothing)
function is not allowed in filters - 'RowNumber cannot be used in filters'
> Thanks
> Kevin
--
Sample Multi-Column Report - 5 records per column
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Franklin Gothic Medium</FontFamily>
<FontSize>12pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.11458in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.25in</Height>
<Width>3in</Width>
<CanGrow>true</CanGrow>
<Value>Sample Multiple Column Report</Value>
<Left>1.5in</Left>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.5in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Info</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<rd:DefaultName>Country</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.5in</Height>
<Columns>2</Columns>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT CompanyName, City, Country
FROM Customers</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
<PageFooter>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.125in</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>2in</Width>
<CanGrow>true</CanGrow>
<Value>="Page " & Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.375in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Kevin,
Did you get your last idea implemented in code? If so, would you share it
with me?
Thanks,
Ming
"Kevin Wilson" wrote:
> Thanks for your time on this Bruce. The multi coloumn report works fine but it isn't really appropriate in this case as I need to view the data rendered in HTML.
> My last idea is to change the the stored proc so each row is identified with a unique number. Create two tables each with a filter - table1 returns odd rows Fields!ID_NUM MOD 2 = 1 and table 2 returns even rows.
> Cheers
> Kevin
> "Bruce Johnson [MSFT]" wrote:
> > Could you use a multi-column report and limt the number of rows in each
> > column?
> > The major restriction is that you must use a "paged" rendered (PDF, Print
> > Preview) to see the multiple columns. Following your original posting is a
> > sample RDL that shows how this is done.
> > Note the table group expression.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> > > Hi,
> > > Any ideas on how to split data from a row set over two columns in a list
> > box or table?
> > > For example I have a data set with 10 rows and I want to display 5 rows in
> > one column and the other 5 in the second column.
> > > I tried creating two tables and filtering the first table to only show the
> > even rows and the second to show odd rows. However the RowNumber(Nothing)
> > function is not allowed in filters - 'RowNumber cannot be used in filters'
> > > Thanks
> > > Kevin
> >
> > --
> >
> > Sample Multi-Column Report - 5 records per column
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <PageHeader>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontFamily>Franklin Gothic Medium</FontFamily>
> > <FontSize>12pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.11458in</Top>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <Height>0.25in</Height>
> > <Width>3in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Sample Multiple Column Report</Value>
> > <Left>1.5in</Left>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.5in</Height>
> > </PageHeader>
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>11pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Company Info</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > <RepeatOnNewPage>true</RepeatOnNewPage>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="CompanyName">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>CompanyName</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!CompanyName.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="City">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>City</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!City.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Country">
> > <rd:DefaultName>Country</rd:DefaultName>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Country.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>Northwind</DataSetName>
> > <TableGroups>
> > <TableGroup>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> >
> > <GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
> > >
> > </GroupExpressions>
> > <PageBreakAtEnd>true</PageBreakAtEnd>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>2.75in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>1.5in</Height>
> > <Columns>2</Columns>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=localhost;initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>2.75in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="CompanyName">
> > <DataField>CompanyName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="City">
> > <DataField>City</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="Country">
> > <DataField>Country</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT CompanyName, City, Country
> > FROM Customers</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
> > <PageFooter>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.125in</Top>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <Width>2in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>="Page " & Globals!PageNumber</Value>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.375in</Height>
> > </PageFooter>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> >
> >

List as report parameter

Any hint how to build a report that prompts user to select multiple values
from a lookup table and uses the multiple values in WHRERE myfield IN
(<user-selected-list>) to select the data?
ThanksReporting Services does not provide this functionality ... supposedly coming
in a future release.
For now, you can just make the parameter a text box so the user can type in
a comma separated list ... then parse the parameter in the filter.
--
Shaun Beane, MCT, MCDBA, MCDST
http://dbageek.blogspot.com
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Basically you can't - multi value lists are not natively support in the
current version.
You can however roll it yourself, either by dynamically building the sql
string to use JobID In (@.somecommadelimitedlist) or if you have some SQL
skills you can create a function in SQL Server to take a comma separated
list and return a table for use in a query.
Check this out:
http://www.windowsitpro.com/Article/ArticleID/26244/26244.html?Ad=1
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Chapter 11 of the book "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" provides a work-round to have a multi-select pick list in the
parameter area.
It is quite well explained, starting out with a comma-separated textbox, but
involves some careful editing...
HTH