Create Report Parameters Dynamically

Hi there,
I was wandering if it is possible to create report parameters on the
fly.
for example. table returns one moment 3 values, other moment 6 values.
depending how many values i would like to create report parameters...
Could anyone help?
Greetz,
Hippo from [NL]
--
remove xxx from mailIf you want to have a parameter that is a combo box filled from a query
that is not problem. If you want to vary the number of parameters then that
is fixed. In layout tab, click on a part of the report (empty part), go to
the report menu, parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"hippo" <nijlpaard.xxx@.gmail.com> wrote in message
news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> Hi there,
> I was wandering if it is possible to create report parameters on the
> fly.
> for example. table returns one moment 3 values, other moment 6 values.
> depending how many values i would like to create report parameters...
> Could anyone help?
>
> Greetz,
> Hippo from [NL]
> --
> remove xxx from mail|||Hello, i got the same problem, only the following:
Depending on returned values i would like to create a combobox.
For example, my dataset or stored procedure returned [x] names, and each
name has 5 possible values. Depending on [x] i would like to create
dynamically parameters. Is this possible?
"Bruce L-C [MVP]" wrote:
> If you want to have a parameter that is a combo box filled from a query
> that is not problem. If you want to vary the number of parameters then that
> is fixed. In layout tab, click on a part of the report (empty part), go to
> the report menu, parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > Hi there,
> >
> > I was wandering if it is possible to create report parameters on the
> > fly.
> >
> > for example. table returns one moment 3 values, other moment 6 values.
> > depending how many values i would like to create report parameters...
> >
> > Could anyone help?
> >
> >
> > Greetz,
> > Hippo from [NL]
> > --
> > remove xxx from mail
>
>|||Search Books On-Line on the phrase cascading parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RamR0m" <RamR0m@.discussions.microsoft.com> wrote in message
news:D54159D9-4F2D-4BE9-8873-862332EABBCD@.microsoft.com...
> Hello, i got the same problem, only the following:
> Depending on returned values i would like to create a combobox.
> For example, my dataset or stored procedure returned [x] names, and each
> name has 5 possible values. Depending on [x] i would like to create
> dynamically parameters. Is this possible?
> "Bruce L-C [MVP]" wrote:
> > If you want to have a parameter that is a combo box filled from a query
> > that is not problem. If you want to vary the number of parameters then
that
> > is fixed. In layout tab, click on a part of the report (empty part), go
to
> > the report menu, parameters.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> > news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > > Hi there,
> > >
> > > I was wandering if it is possible to create report parameters on the
> > > fly.
> > >
> > > for example. table returns one moment 3 values, other moment 6 values.
> > > depending how many values i would like to create report parameters...
> > >
> > > Could anyone help?
> > >
> > >
> > > Greetz,
> > > Hippo from [NL]
> > > --
> > > remove xxx from mail
> >
> >
> >|||Thank you Bruce for you reply, but this isn't what i wish.
What i understand of Cascading Parameters is that you have to predefine your
parameters before execution.
What i would like is to have is, depending on values returned by the
dataset, vary the amount of visual parameters.
Greetz, RamR0m
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
"Bruce L-C [MVP]" wrote:
> Search Books On-Line on the phrase cascading parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RamR0m" <RamR0m@.discussions.microsoft.com> wrote in message
> news:D54159D9-4F2D-4BE9-8873-862332EABBCD@.microsoft.com...
> > Hello, i got the same problem, only the following:
> >
> > Depending on returned values i would like to create a combobox.
> > For example, my dataset or stored procedure returned [x] names, and each
> > name has 5 possible values. Depending on [x] i would like to create
> > dynamically parameters. Is this possible?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you want to have a parameter that is a combo box filled from a query
> > > that is not problem. If you want to vary the number of parameters then
> that
> > > is fixed. In layout tab, click on a part of the report (empty part), go
> to
> > > the report menu, parameters.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "hippo" <nijlpaard.xxx@.gmail.com> wrote in message
> > > news:00ma515285q2fmmgouguksq2t4l538mlhh@.4ax.com...
> > > > Hi there,
> > > >
> > > > I was wandering if it is possible to create report parameters on the
> > > > fly.
> > > >
> > > > for example. table returns one moment 3 values, other moment 6 values.
> > > > depending how many values i would like to create report parameters...
> > > >
> > > > Could anyone help?
> > > >
> > > >
> > > > Greetz,
> > > > Hippo from [NL]
> > > > --
> > > > remove xxx from mail
> > >
> > >
> > >
>
>|||>From everything I've seen, the parameters are hard coded into the RDL,
and there is no way to dynamically add or remove parameters at report
run time. You may want to see if someone from Microsoft knows if this
is a wishlist item for future versions.
If you are letting Reporting Services prompt the user for parameters,
the best thing I can think of that you can do is define the maximum
number of parameters, then use cascading parameters in a way that the
unnecessary parameters only have a single choice called "Not
Applicable" or "Skip Me". If you have an application that is calling
the report, you could prompt the user only for the parameters that you
really care about, and then behind the scenes fill in the remaining
parameters with dummy values that aren't actually needed to generate
your report data.
Ted

CREATE PROC Question

Gurus help me:
Here's the scenario...
Have a SP in the Master DB that creates a NEW, empty DB using a name I give it on the fly.
I need to Create a SP in that NEW DB.
Everything will be called from a DTS Package.
How to do this?
RobbieDCan you tell us why you are doing this...

Just seems like a very bad idea...

Are you talking about MSDE?|||I'll second the notion that this sounds like a bad idea. It can certainly be done, but there are lots of things that you can do, but shouldn't!

-PatP|||"Location: In front of the computer"

LOL

Moe, Larry look, it's a DBA with a sense of humor...

Why I oughtta...|||Hey Brett:

It's to automate Replication (see my other posts).

We have a subjective DB name @. the Publisher that has to be acquired, then replicated EXACTLY.

This procedure will create the Subscription DB & then create the SP to complete the the subscription itself.

Clear as Mud?!?!

(BTW - GREAT reply to the recruiter. He suddenly became less verbose!)|||Sounds ambitious...how many subscribers do you expect to have...|||Just a single other instance...But we'll do this MONTHLY.

HOWEVER, we have to duplicate the process in reverse later on.

Ambitious pretty much hits the nail on the head!!!|||I wouldn't support that kind of design, but here's your answer:

use model
go
create procedure <your_procedure>...
go|||Just 1?

That's a lot of effort to think outside the box...why complicate things?|||Got any suggestions?|||Did you get it or I have to explain it?|||Sorry rdjabarov:

I see where you're going, but if I want this code in a SP OR for that matter in an ActiveX module of a DTS, I can't get away with "USE".|||But your only replicating monthly?

Why not dump and restore?

MAYBE 10 lines of code

Done!|||robbied111,

You don't call this code from anywhere, you write it in QA. Since you already have the code to create a database, you won't have to worry about creating a procedure every time your ASP code creates a database, the procedure will already be there...Can you try it at least?|||THANKS All.

I'll do some more work & let you know how I fare.

RobbieD

(It's past 5pm here - time to blaze!!!)

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

create login for db access on the fly

Hi,
How can i create logins on the fly to access a database.
I have a login which can access a database through my webapp. The enduser
will access also through my webapp, but they have a different login to the
database.
My question how can this be done? I've tried to execute the following, but
that did not work.
EXEC sp_addlogin 'username','password';
EXEC sp_defaultdb 'username', 'database';
use database;
EXEC sp_grantdbaccess 'username';
EXEC sp_addrolemember 'standard','username';
result:
Server: Msg 15247, Level 16, State 1, Procedure sp_addlogin, Line 17
User does not have permission to perform this action.
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Granted database access to 'jos'.
'username' added to role 'standard'.Use either Windows Authentication or Application Roles.
I'm not sure in what context it would make sense to add users
"on-the-fly". To add a user requires a login to the database with the
db_accesmin role anyway. Normally it's easier to add domain user
groups rather than individual users.
David Portas
SQL Server MVP
--|||Hi David,
I agree with you, but it is for emergency app which needs to create logins
for authenication means, because the user will access the database through
the webapp and doens't know their database password.
The user will be added to a databaserole.
Could you help me build this functionality?
"David Portas" wrote:

> Use either Windows Authentication or Application Roles.
> I'm not sure in what context it would make sense to add users
> "on-the-fly". To add a user requires a login to the database with the
> db_accesmin role anyway. Normally it's easier to add domain user
> groups rather than individual users.
> --
> David Portas
> SQL Server MVP
> --
>|||You need symin, db_accesmin or db ownership to modify users and
permissions. The error message indicated that you didn't have those
permissions so you'll have to use a login that does.
David Portas
SQL Server MVP
--|||Depends what you mean by on the fly, but, if you want the users to access th
e
DB w/o knowing the pwd or accnt they are going in as (ie, they can log into
your site (web-wise) with an accnt and pwd but knowing their accnt and pwd
alone won't give them direct access to the DB) you could set up SQL
Authentication on the DB, capture the users accnt value, modify it (in a
fixed way) and use the modified accnt value and a pwd in a COM or .NET
Assembly to actually access the DB. Means a bit more work on the DB end
tho... Or, you could access the DB with a specific accnt/pwd and pass in th
e
users web accnt as a parameter to your sprocs so you'd know who had requeste
d
the access... not sure what your ultimate goal is.
"Ezeki?l" wrote:

> Hi,
> How can i create logins on the fly to access a database.
> I have a login which can access a database through my webapp. The enduser
> will access also through my webapp, but they have a different login to the
> database.
> My question how can this be done? I've tried to execute the following, but
> that did not work.
> EXEC sp_addlogin 'username','password';
> EXEC sp_defaultdb 'username', 'database';
> use database;
> EXEC sp_grantdbaccess 'username';
> EXEC sp_addrolemember 'standard','username';
> result:
> Server: Msg 15247, Level 16, State 1, Procedure sp_addlogin, Line 17
> User does not have permission to perform this action.
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Granted database access to 'jos'.
> 'username' added to role 'standard'.
>

Create DB on the fly

Hi guys.

Here is what I want to do:

1. create a small desktop application

2. when this application starts, it will check if the file "...mydb.mdf" file exists in a specified folder

3. if the file doesn't exist, I want to create the database and connect to it using User Instance

How can I create the database?

I tried to connect to SQLExpress and run a script using SqlCommand. It didn't work.

Is there any other way to do this?

Or what is the correct way to do it?

Thanks.

Mircea

You can create entire databases in SQL Script using CREATE DATABASE, CREATE TABLE, and other similar commands. Some of them (like CREATE VIEW) must be sent by themselves, but otherwise its pretty simple.

Alternatively, you can embed an empty database in your application and extract it out as needed.

-Ryan / Kardax

|||

Hi Ryan.

I have the script for creating the database. My question is how should I run this script?

Thanks.