Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

dynamic query

Hello friends,

I want to create a dynamic query based on input of the parameter.

If the user passes nothing then all fields should be displayed else use query based on parameter.

I had view sample of MSDN ,but I got error [BC30203].

Is there another way to it ?Please help.

I use:

= iif(Parameters!SQLQuery.Value<>"",Parameters!SQLQuery.Value,"SELECT somecolumn from sometable") as "select statement"

and provide a query in the SQLQuery-Parameter..

You could also use:

= iif(Parameters!SomeID.Value<>"","SELECT somecolumn from sometable where id=" & Parameters!SQLQuery.Value,"SELECT somecolumn from sometable where id=123")

|||

Thanks For Your Reply

But I m still confusing.

I had used the iif (condition) in the generic query designer but i cannot retrive the fields which I want from the query.

The Query is executing but the data set does not contain any fields.

for eg:

="Select Idnummer,.....

iif(parameter is null,nothing,"AND ART IN ( " & parameter.value & ")")

Please reply sooner.

|||

You can't check your query anymore, thats right. When writing the query as ="select .. " & some_condition .. Hitting the "!"-Button has no effect.. This statement is evaluated at runtime.. So, you have to go to Preview-Mode and check if the result looks right..

If you are missing the Fields!.. for report design, the easiest way is to execute a "normal" sql-statement once (this will add the fields) and then transform your sql-string..

|||

Dynamic query should be avoided wherever possible. It is much harder to do (as you have seen).

I do this, I have a parameter that says All and returns a value of All (it could also return a number if a number field, just make it a value that does not exist in the database.

Do this:

selct * from sometable where (somefield = @.MyParam or @.MyParam = 'All') and ...

|||

I dunno if this is better, but should do the same thing:

The query will be:

select * from TableName
where FieldName LIKE (CASE WHEN @.param IS NULL THEN '%' ELSE @.param END)

|||

Hello Sir,

How can I implement "ALL" in my parameter

The table field does not contain 'ALL" .

If the parameter selected is 'ALL',then query should execute with the parameter contaning 'ALL' the values.Then my problem could be solve if the parameter contains 'ALL'.

please give a sample to implement 'ALL' in my parameter and in query.

Please reply soon.

Thanks

|||

u can use the query in this way,

SELECT AreaName, AreaCode
FROM Area
UNION
SELECT ' All' AS Areaname, '' AS AreaCode
ORDER BY AreaName

This will add 'All' n ur drop down box n using case statement in query u can get the desired results.

regards

Satyendra

Dynamic Parameter Date Ranges

I require three parameter fields:
1.)Daterange
2.)EndDate
3.)Start Date
Selecting an option from the DateRange Parameter (eg. option = today)
automatically populates the EndDate and StartDate Fields. I also want to give
the user the option to extend the date range to one which is not defined as a
daterange option of desired by editing the EndDate and StartDate Fields.
Any help is appreciated.Hi,
I have exactly the same problem, did you found a solution?
Thanks,
Elisabeth
"SAcanuck" wrote:
> I require three parameter fields:
> 1.)Daterange
> 2.)EndDate
> 3.)Start Date
> Selecting an option from the DateRange Parameter (eg. option = today)
> automatically populates the EndDate and StartDate Fields. I also want to give
> the user the option to extend the date range to one which is not defined as a
> daterange option of desired by editing the EndDate and StartDate Fields.
> Any help is appreciated.|||No I havent found a solution that works like I want it to.
"Elisabeth" wrote:
> Hi,
> I have exactly the same problem, did you found a solution?
> Thanks,
> Elisabeth
> "SAcanuck" wrote:
> > I require three parameter fields:
> >
> > 1.)Daterange
> > 2.)EndDate
> > 3.)Start Date
> >
> > Selecting an option from the DateRange Parameter (eg. option = today)
> > automatically populates the EndDate and StartDate Fields. I also want to give
> > the user the option to extend the date range to one which is not defined as a
> > daterange option of desired by editing the EndDate and StartDate Fields.
> >
> > Any help is appreciated.|||I would have to play with this but it seems like if you have three
parameters and the second and third parameters have an expression as the
default with the expression referencing the first parameter. I can't try
this right now but it should work.
Bruce L-C
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> No I havent found a solution that works like I want it to.
>
> "Elisabeth" wrote:
>> Hi,
>> I have exactly the same problem, did you found a solution?
>> Thanks,
>> Elisabeth
>> "SAcanuck" wrote:
>> > I require three parameter fields:
>> >
>> > 1.)Daterange
>> > 2.)EndDate
>> > 3.)Start Date
>> >
>> > Selecting an option from the DateRange Parameter (eg. option = today)
>> > automatically populates the EndDate and StartDate Fields. I also want
>> > to give
>> > the user the option to extend the date range to one which is not
>> > defined as a
>> > daterange option of desired by editing the EndDate and StartDate
>> > Fields.
>> >
>> > Any help is appreciated.|||Bruce:
I have tried this before but it doesnt perform as expected...
When you run your report the first time and select the first parameter the
other two dates are populated correctly, but when you change your first
parameter (date range) the other dates are not automatically changed.
"Bruce Loehle-Conger" wrote:
> I would have to play with this but it seems like if you have three
> parameters and the second and third parameters have an expression as the
> default with the expression referencing the first parameter. I can't try
> this right now but it should work.
> Bruce L-C
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > No I havent found a solution that works like I want it to.
> >
> >
> > "Elisabeth" wrote:
> >
> >> Hi,
> >>
> >> I have exactly the same problem, did you found a solution?
> >> Thanks,
> >> Elisabeth
> >>
> >> "SAcanuck" wrote:
> >>
> >> > I require three parameter fields:
> >> >
> >> > 1.)Daterange
> >> > 2.)EndDate
> >> > 3.)Start Date
> >> >
> >> > Selecting an option from the DateRange Parameter (eg. option = today)
> >> > automatically populates the EndDate and StartDate Fields. I also want
> >> > to give
> >> > the user the option to extend the date range to one which is not
> >> > defined as a
> >> > daterange option of desired by editing the EndDate and StartDate
> >> > Fields.
> >> >
> >> > Any help is appreciated.
>
>|||Bruce,
I have tried it also and it didn't work.
I hope you will find time to try it...
waiting,
Elisabeth
"SAcanuck" wrote:
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > >> > automatically populates the EndDate and StartDate Fields. I also want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||OK, I just tried this out and in the development environment it does as you
say. However, when I deploy it and try it from the server then it works as
you would expect. Can you try it after deploying it?
Bruce L-C [MVP SQL Server Reporting Services]
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> Bruce:
> I have tried this before but it doesnt perform as expected...
> When you run your report the first time and select the first parameter the
> other two dates are populated correctly, but when you change your first
> parameter (date range) the other dates are not automatically changed.
> "Bruce Loehle-Conger" wrote:
> > I would have to play with this but it seems like if you have three
> > parameters and the second and third parameters have an expression as the
> > default with the expression referencing the first parameter. I can't try
> > this right now but it should work.
> >
> > Bruce L-C
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > No I havent found a solution that works like I want it to.
> > >
> > >
> > > "Elisabeth" wrote:
> > >
> > >> Hi,
> > >>
> > >> I have exactly the same problem, did you found a solution?
> > >> Thanks,
> > >> Elisabeth
> > >>
> > >> "SAcanuck" wrote:
> > >>
> > >> > I require three parameter fields:
> > >> >
> > >> > 1.)Daterange
> > >> > 2.)EndDate
> > >> > 3.)Start Date
> > >> >
> > >> > Selecting an option from the DateRange Parameter (eg. option =today)
> > >> > automatically populates the EndDate and StartDate Fields. I also
want
> > >> > to give
> > >> > the user the option to extend the date range to one which is not
> > >> > defined as a
> > >> > daterange option of desired by editing the EndDate and StartDate
> > >> > Fields.
> > >> >
> > >> > Any help is appreciated.
> >
> >
> >|||Elisabeth:
Maybe the following link helps you...Im not a SQL Guru (far from it...) so
it doesnt help me much.
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
"Elisabeth" wrote:
> Bruce,
> I have tried it also and it didn't work.
> I hope you will find time to try it...
> waiting,
> Elisabeth
> "SAcanuck" wrote:
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >|||Hi Bruce:
It does work after deploying... slightly annoying though. Thanks for the
effort.
"Bruce Loehle-Conger [MVP]" wrote:
> OK, I just tried this out and in the development environment it does as you
> say. However, when I deploy it and try it from the server then it works as
> you would expect. Can you try it after deploying it?
> Bruce L-C [MVP SQL Server Reporting Services]
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:450111A7-157F-4000-B331-F36758529639@.microsoft.com...
> > Bruce:
> >
> > I have tried this before but it doesnt perform as expected...
> >
> > When you run your report the first time and select the first parameter the
> > other two dates are populated correctly, but when you change your first
> > parameter (date range) the other dates are not automatically changed.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > I would have to play with this but it seems like if you have three
> > > parameters and the second and third parameters have an expression as the
> > > default with the expression referencing the first parameter. I can't try
> > > this right now but it should work.
> > >
> > > Bruce L-C
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > No I havent found a solution that works like I want it to.
> > > >
> > > >
> > > > "Elisabeth" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> I have exactly the same problem, did you found a solution?
> > > >> Thanks,
> > > >> Elisabeth
> > > >>
> > > >> "SAcanuck" wrote:
> > > >>
> > > >> > I require three parameter fields:
> > > >> >
> > > >> > 1.)Daterange
> > > >> > 2.)EndDate
> > > >> > 3.)Start Date
> > > >> >
> > > >> > Selecting an option from the DateRange Parameter (eg. option => today)
> > > >> > automatically populates the EndDate and StartDate Fields. I also
> want
> > > >> > to give
> > > >> > the user the option to extend the date range to one which is not
> > > >> > defined as a
> > > >> > daterange option of desired by editing the EndDate and StartDate
> > > >> > Fields.
> > > >> >
> > > >> > Any help is appreciated.
> > >
> > >
> > >
>
>|||I think something is missing in the link you send to me...
"SAcanuck" wrote:
> Elisabeth:
> Maybe the following link helps you...Im not a SQL Guru (far from it...) so
> it doesnt help me much.
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?pg=4&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=
> "Elisabeth" wrote:
> > Bruce,
> >
> > I have tried it also and it didn't work.
> > I hope you will find time to try it...
> > waiting,
> > Elisabeth
> >
> > "SAcanuck" wrote:
> >
> > > Bruce:
> > >
> > > I have tried this before but it doesnt perform as expected...
> > >
> > > When you run your report the first time and select the first parameter the
> > > other two dates are populated correctly, but when you change your first
> > > parameter (date range) the other dates are not automatically changed.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > I would have to play with this but it seems like if you have three
> > > > parameters and the second and third parameters have an expression as the
> > > > default with the expression referencing the first parameter. I can't try
> > > > this right now but it should work.
> > > >
> > > > Bruce L-C
> > > >
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:FDF1B605-75D3-4BF5-9E05-B28202D35A12@.microsoft.com...
> > > > > No I havent found a solution that works like I want it to.
> > > > >
> > > > >
> > > > > "Elisabeth" wrote:
> > > > >
> > > > >> Hi,
> > > > >>
> > > > >> I have exactly the same problem, did you found a solution?
> > > > >> Thanks,
> > > > >> Elisabeth
> > > > >>
> > > > >> "SAcanuck" wrote:
> > > > >>
> > > > >> > I require three parameter fields:
> > > > >> >
> > > > >> > 1.)Daterange
> > > > >> > 2.)EndDate
> > > > >> > 3.)Start Date
> > > > >> >
> > > > >> > Selecting an option from the DateRange Parameter (eg. option = today)
> > > > >> > automatically populates the EndDate and StartDate Fields. I also want
> > > > >> > to give
> > > > >> > the user the option to extend the date range to one which is not
> > > > >> > defined as a
> > > > >> > daterange option of desired by editing the EndDate and StartDate
> > > > >> > Fields.
> > > > >> >
> > > > >> > Any help is appreciated.
> > > >
> > > >
> > > >

dynamic matrix reports

Hello,
Is it possible to create a report which allows users to select the
fields
for the matrix in a report. To explain in detail, can we allow the
users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total
sales(
data cell) by month (X axis) and by Rep( Y axis). Can we have some
option
so that if the users select Year as the X axis and Company as the Y
axis
then they can view the same report but by company and Year instead of
Month and Rep?
Regards
Jaideepcertainly.
Just use a switch statement or iif statement for the values for the row
and column groups:
switch(parameters!group.value = 'x', fields!month.value,
parameters!group.value = 'y', fields!year.value)
or
iif(parameters!group.value = 'x', fields!month.value,
fields!year.value)
jai wrote:
> Hello,
> Is it possible to create a report which allows users to select the
> fields
> for the matrix in a report. To explain in detail, can we allow the
> users to
> select the X and Y axis for a matrix in a report?
>
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Rep( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and Company as the Y
> axis
> then they can view the same report but by company and Year instead of
> Month and Rep?
>
> Regards
> Jaideep

dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?

Dynamic formatting question

Hello,

I have a dataset that returns one column of data from which some fields are supposed to appear horizontally in the report, while the rest appear line by line vertically. Is it possible to take, for example, three consecutive fields from the dataset and merge the corresponding table rows that hold these fields in the report? Perhaps using an expression or custom code?

Thanks in advance for any help. I'd be happy to provide examples of what I need if it can help clarify the question.

RLGow

Not exactly sure what you are trying to do, but you can write expressions that concatenate the value of multiple fields, such as:

=Fields!ColumnA.Value & Fields!ColumnB.Value & Fields!ColumnC.Value

-- Robert

Dynamic Filter

Hi,
I have a Reporting Services report that I would like to add a dynmaic filter
to. I want to allow the users to filter based on two seperate fields (or
view all data). Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. ignore those with null values). The same would hold true if the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank), I don't want to limit the data.
Is this possible? I would prefer to accomplish this without using dynamic
sql.
Let me know if you have any questions...
Thanks for the help!
-David.are you using embedded sql code, or a stored procedure call?
"David" wrote:
> Hi,
> I have a Reporting Services report that I would like to add a dynmaic filter
> to. I want to allow the users to filter based on two seperate fields (or
> view all data). Example: if the user selects "Field1" from the filter
> drop-down, I only want to display records in the dataset that have a value in
> Field1 (i.e. ignore those with null values). The same would hold true if the
> user would select "Field2". If the user does not select a value for this
> parameter (i.e. it is blank), I don't want to limit the data.
> Is this possible? I would prefer to accomplish this without using dynamic
> sql.
> Let me know if you have any questions...
> Thanks for the help!
> -David.|||Embedded sql...
"Carl Henthorn" wrote:
> are you using embedded sql code, or a stored procedure call?
> "David" wrote:
> > Hi,
> >
> > I have a Reporting Services report that I would like to add a dynmaic filter
> > to. I want to allow the users to filter based on two seperate fields (or
> > view all data). Example: if the user selects "Field1" from the filter
> > drop-down, I only want to display records in the dataset that have a value in
> > Field1 (i.e. ignore those with null values). The same would hold true if the
> > user would select "Field2". If the user does not select a value for this
> > parameter (i.e. it is blank), I don't want to limit the data.
> >
> > Is this possible? I would prefer to accomplish this without using dynamic
> > sql.
> >
> > Let me know if you have any questions...
> >
> > Thanks for the help!
> >
> > -David.|||An example of your code would be helpful. when you say "Filter", are talkign
about reducing the size of your result set by using the defined parameters in
a where clause. it looks like you want to return different columns from a
table depending on which parameters are picked. Which is it? both?
if you are talking columns, use the visibility expression to hide the
unwanted columns based on the parameter value. if you are talking where
clause, then you can get around using dynamic sql by using a case stmt in the
stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
hth!
"David" wrote:
> Embedded sql...
> "Carl Henthorn" wrote:
> > are you using embedded sql code, or a stored procedure call?
> >
> > "David" wrote:
> >
> > > Hi,
> > >
> > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > to. I want to allow the users to filter based on two seperate fields (or
> > > view all data). Example: if the user selects "Field1" from the filter
> > > drop-down, I only want to display records in the dataset that have a value in
> > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > user would select "Field2". If the user does not select a value for this
> > > parameter (i.e. it is blank), I don't want to limit the data.
> > >
> > > Is this possible? I would prefer to accomplish this without using dynamic
> > > sql.
> > >
> > > Let me know if you have any questions...
> > >
> > > Thanks for the help!
> > >
> > > -David.|||If a user selects one of the filter criteria, I want to only show those
records that have a value in that field. I do not want to change the columns
that are returned. In effect, it would reduce the size of the dataset.
However, I just want to filter the data not exclude it from the dataset (to
reduce round-trips to the database). Also, the report parameter will contain
the field name for the users to select from (not the value).
Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. not show those with null values). The same would hold true if
the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank/null), I want to show all records.
"Carl Henthorn" wrote:
> An example of your code would be helpful. when you say "Filter", are talkign
> about reducing the size of your result set by using the defined parameters in
> a where clause. it looks like you want to return different columns from a
> table depending on which parameters are picked. Which is it? both?
> if you are talking columns, use the visibility expression to hide the
> unwanted columns based on the parameter value. if you are talking where
> clause, then you can get around using dynamic sql by using a case stmt in the
> stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
> hth!
> "David" wrote:
> > Embedded sql...
> >
> > "Carl Henthorn" wrote:
> >
> > > are you using embedded sql code, or a stored procedure call?
> > >
> > > "David" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > > to. I want to allow the users to filter based on two seperate fields (or
> > > > view all data). Example: if the user selects "Field1" from the filter
> > > > drop-down, I only want to display records in the dataset that have a value in
> > > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > > user would select "Field2". If the user does not select a value for this
> > > > parameter (i.e. it is blank), I don't want to limit the data.
> > > >
> > > > Is this possible? I would prefer to accomplish this without using dynamic
> > > > sql.
> > > >
> > > > Let me know if you have any questions...
> > > >
> > > > Thanks for the help!
> > > >
> > > > -David.

Dynamic filedlength of varchar fields in source

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You cannot dynamically change the metadata in an SSIS package -- it needs to be fixed at design time.

You could try casting the source fields to the size they will be on the destination, and hope that the source fields never exceed this size. Or, you could castthe source fields to DT_TEXT so that length can be variable, but performance might suffer (there is extra processing going on for long-object types like DT_TEXT).

Thanks
Mak

|||

A. Brosten wrote:

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You can change the SSIS package so that the external metadata stored within there is large enough for all eventualities.

-Jamie

Dynamic fields in SQL

I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

Something like:
create table MY_TABLE (id int, name varchar(32) primary key(id))
create table MY_DYNAMIC_ATTRIBUTES
( id int
, attribute_name varchar(20)
, attribute_type_code char(1)
, attribute_value varchar(255)
primary key (id, attribute_name)
foreign key (id) references MY_TABLE(id)
)

The problem is usually the application reading and writing these tables.
A general report would need crosstabbing.

Specific reports, where you already know which fields are involved is easier, could even be done with a view:

create view MY_DYNAMIC_VIEW as
select
t.id
, t.name
, d1.attribute_value as address
, d2.attribute_value as city
, convert(int, d3.attribute_value) as age
from MY_TABLE t
left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'|||Originally posted by plextoR
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)

Suggestions?

Yeah, don't do it...

Just think what kind of mess you'll end up with...

Ummmm I want a varchar(8000) column...ummm I want another one...and another one...

Booooooooooooooooooooooom

What's business requirement to support, in non tech terms...

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

Dynamic field

Is It possible create reports with dynamic fields ?testt
"alejandro" wrote:
> Is It possible create reports with dynamic fields ?
>
>|||what?
"cayetanob" <cayetanob@.discussions.microsoft.com> escribió en el mensaje
news:FC93FAB4-2725-460B-BD6F-1904D93845D2@.microsoft.com...
> testt
> "alejandro" wrote:
>> Is It possible create reports with dynamic fields ?
>>

Dynamic Data in Page Header?

Hi all,
is there's a way to add fields to the Page Header?
We have to display dynamic data on each page...
Thanks
ThomasThomas,
What kind of data is this? If its a calculation, global parameter,
date, or parameters passed in, this is very easy by adding an
expression(Somehting like: Parameters!Param1.Value).
If its Fields, then you can create a group, and place that field in the
group header - but you must group it by some field.
Then Left Click on that row(the group header) and set
theRepeatOnEveryPage property to true.
I hope this helps.
regards,
Stas K.|||Hi Thomas
No, you cannot add data fields to the Page Header, because it cannot have a
defined dataset. However, depending on the your data and how you want to
display it, here is a another solution:
1) get rid of the page header section of the report,
2) in the body of the report, use a table as the main container and make it
as wide as the width of the report, and make the table top start at the very
top of the report.
3) define the dataset for that table in the properties
4) add a table header row, and use that as the "page header". Make sure to
check the "Repeat on New page" option for the header row properties.
5) select all cells in the header row, and merge them.
6) drop a rectangle in the newly merged header cell, and voila, you have a
freeform container for your cell, so you can drop textboxes in there, etc...
In most of my reports, I have done that, instead of using a page header,
since in most cases I need to use a datafield as part of the header anyways.
You can have several header rows defined for each section in a table kinda
like this:
<table>
<tblHeader1>
<tblHeader2>
<tbleHeader3>
<group1Header1>
<group1Header2>
<group2Header1>
<group2Header2>
<detailRow>
<group2Footer1>
<group2Footer2>
<group1Footer1>
<group1Footer2>
<tblFooter1>
<tblFooter2>
</table>
Hope that helps ya.
--
Regards,
Thiago Silva
"Thomas" wrote:
> Hi all,
> is there's a way to add fields to the Page Header?
> We have to display dynamic data on each page...
> Thanks
> Thomas
>
>

Dynamic column in the query using SQL 2005

Hi All,

I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

Here is the query in bold, the rest if for creating a tmp table with approx values like the ones you use. NB date format is MM/DD/YYYY.

create table #x
(
country varchar(10),
Date datetime,
PRICE1 decimal(9,2),
)
insert #x
select 'USA', '1/1/2006', 3000 union all
select 'SG', '1/1/2006', 2500 union all
select 'USA', '1/2/2006', 2500 union all
select 'SG', '1/2/2006', 1500 union all
select 'USA', '1/3/2006', 1000 union all
select 'SG', '1/3/2006', 7550 union all
select 'USA', '1/4/2006', 500 union all
select 'SG', '1/4/2006', 300 union all
select 'USA', '1/5/2006', 350 union all
select 'SG', '1/5/2006', 400

select
country,
date,
price1 as dayAmount,
(select price1 from #x as b where datediff(dd,b.date,a.date) = 2 and a.country = b.country) as prevDayAmount,
(select sum(price1) from #x as b where datediff(mm,b.date,a.date) < 1 and a.country = b.country) as sumMonthAmount
from #x as a
where date = '01/03/2006'

drop table #x

Dynamic Code

I have the following code which will give me all the tables, but I will need
to dynamically generate the select list and explicitly name the fields -
here is the code I received in another post. I would be grateful if somebody
could give me the information of how I could create the list of fields in
the source table (OldDB) to dynamically insert into the destination table
(NewDB) - I know that there may be fields in the NewDB that are not in the
OldDB, but not vice versa. And I know defaults will handle any fields that
do not make it into the select list. How can this be done? By the way I
have to have this scripted onto the production machines that I do not have
direct access to.
declare @.sql varchar(8000)
declare @.table_name varchar(256)
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
DECLARE table_list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
OPEN table_list
FETCH NEXT FROM table_list INTO
@.table_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
OldDatabase..' + @.table_name + ' ) '
--print @.sql
--EXEC (@.SQL)
FETCH NEXT FROM table_list INTO
@.table_name
END
DEALLOCATE table_listA couple of questions and a few comments.
1. How big is your database? Would a restore/robocopy/litespeed or even DTS
solution not be better seen as code is inherently going to invite error.
2. Why do you have to dynamically instantiate the field names. Are the table
structures not the same in oldDB?
3. You could try a nested loop using your table name variable and iterating
through the following, you will still have to determine the size, collations
and defaults for the table. Its messy but with a bit of work you could do it
.
create table tblMaster_Data_Types(
intData_Type_Id int,
vcData_Type_Desc varchar(50),
chActive char(1))
insert into tblMaster_Data_Types values(1, 'TINYINT', 'Y')
insert into tblMaster_Data_Types values(2, 'FLOAT', 'Y')
insert into tblMaster_Data_Types values(3, 'SMALLINT', 'Y')
insert into tblMaster_Data_Types values(4, 'INT', 'Y')
insert into tblMaster_Data_Types values(5, 'BIGINT', 'Y')
insert into tblMaster_Data_Types values(6, 'DECIMAL', 'Y')
Select SYS_OBJ.NAME,SYS_COL.NAME--,
DATA_TYP.vcData_Type_Desc,
SYS_USR.NAME
From OldDB..sysobjects SYS_OBJ,
OldDB..sysUsers SYS_USR,
OldDB..SYSCOLUMNS SYS_COL,
OldDB..SYSTYPES SYS_TYP--,
OldDB..tblMaster_Data_Types DATA_TYP
Where SYS_OBJ.type = 'U'
AND SYS_OBJ.UID = SYS_USR.UID
AND SYS_OBJ.ID = SYS_COL.ID
AND SYS_TYP.TYPE = SYS_COL.TYPE
AND DATA_TYP.vcData_Type_Desc Collate QL_LATIN1_GENERAL_CP1_CI_AS =
SYS_TYP.NAME collate SQL_LATIN1_GENERAL_CP1_CI_AS
AND DATA_TYP.chActive = 'Y'
ORDER BY SYS_OBJ.NAME
4. Get access to the relevant database.
"Derek Hart" wrote:

> I have the following code which will give me all the tables, but I will ne
ed
> to dynamically generate the select list and explicitly name the fields -
> here is the code I received in another post. I would be grateful if somebo
dy
> could give me the information of how I could create the list of fields in
> the source table (OldDB) to dynamically insert into the destination table
> (NewDB) - I know that there may be fields in the NewDB that are not in the
> OldDB, but not vice versa. And I know defaults will handle any fields tha
t
> do not make it into the select list. How can this be done? By the way I
> have to have this scripted onto the production machines that I do not have
> direct access to.
> declare @.sql varchar(8000)
> declare @.table_name varchar(256)
> SELECT name FROM sysobjects where xtype = 'u' and name <>
> 'dtproperties'
> DECLARE table_list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'u' and name <>
> 'dtproperties'
> OPEN table_list
> FETCH NEXT FROM table_list INTO
> @.table_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
> OldDatabase..' + @.table_name + ' ) '
> --print @.sql
> --EXEC (@.SQL)
>
> FETCH NEXT FROM table_list INTO
> @.table_name
> END
> DEALLOCATE table_list
>
>|||I am going to build these sql statements dynamically using VB.NET.
Is there a way to read a system table to determine if a specific table has
an identity column? I want to run SET IDENTITY_INSERT myTable ON but this
statement errors if the table does not have an Identity field.
Derek Hart
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8C65AFB3-517B-4D28-B40B-8DCBE3B12D62@.microsoft.com...
>A couple of questions and a few comments.
> 1. How big is your database? Would a restore/robocopy/litespeed or even
> DTS
> solution not be better seen as code is inherently going to invite error.
> 2. Why do you have to dynamically instantiate the field names. Are the
> table
> structures not the same in oldDB?
> 3. You could try a nested loop using your table name variable and
> iterating
> through the following, you will still have to determine the size,
> collations
> and defaults for the table. Its messy but with a bit of work you could do
> it.
> create table tblMaster_Data_Types(
> intData_Type_Id int,
> vcData_Type_Desc varchar(50),
> chActive char(1))
> insert into tblMaster_Data_Types values(1, 'TINYINT', 'Y')
> insert into tblMaster_Data_Types values(2, 'FLOAT', 'Y')
> insert into tblMaster_Data_Types values(3, 'SMALLINT', 'Y')
> insert into tblMaster_Data_Types values(4, 'INT', 'Y')
> insert into tblMaster_Data_Types values(5, 'BIGINT', 'Y')
> insert into tblMaster_Data_Types values(6, 'DECIMAL', 'Y')
> Select SYS_OBJ.NAME,SYS_COL.NAME--,
> DATA_TYP.vcData_Type_Desc,
> SYS_USR.NAME
> From OldDB..sysobjects SYS_OBJ,
> OldDB..sysUsers SYS_USR,
> OldDB..SYSCOLUMNS SYS_COL,
> OldDB..SYSTYPES SYS_TYP--,
> OldDB..tblMaster_Data_Types DATA_TYP
> Where SYS_OBJ.type = 'U'
> AND SYS_OBJ.UID = SYS_USR.UID
> AND SYS_OBJ.ID = SYS_COL.ID
> AND SYS_TYP.TYPE = SYS_COL.TYPE
> AND DATA_TYP.vcData_Type_Desc Collate QL_LATIN1_GENERAL_CP1_CI_AS
> =
> SYS_TYP.NAME collate SQL_LATIN1_GENERAL_CP1_CI_AS
> AND DATA_TYP.chActive = 'Y'
> ORDER BY SYS_OBJ.NAME
>
> 4. Get access to the relevant database.
> "Derek Hart" wrote:
>|||The stored procedure sp_columns will display a type of "int identity"
as opposed to "int" in the column Type_name. use it as sp_columns
'tablename'
This way you could do some cursor jiggery pokery and match on type_name
like 'identity%'
Cheers
Will|||The stored procedure sp_columns will display a type of "int identity"
as opposed to "int" in the column Type_name. use it as sp_columns
'tablename'
This way you could do some cursor jiggery pokery and match on type_name
like '%identity'
Cheers
Will|||Derek Hart (derekmhart@.yahoo.com) writes:
> I am going to build these sql statements dynamically using VB.NET.
> Is there a way to read a system table to determine if a specific table has
> an identity column? I want to run SET IDENTITY_INSERT myTable ON but this
> statement errors if the table does not have an Identity field.
objectproperty(id, 'TableHasIdentity')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Within the cursor, you may build a cursor having the column list for
the table you are sitting on in the outer cursor.
SELECT column_name from information_schema.columns where table_name =
@.table_name. Build the string dynamically from that. You may run into
identity insert problems if using them in the db. Use SET
IDENTITY_INSERT table_name ON before the insert if then set it OFF
before continuing on to the outer cursor to the next table. The
following finds identity columns.
select table_name + '.' + column_name, table_name, column_name --,
ordinal_position, data_type
from information_schema.columns
where
--table_schema = 'dbo'
--and
columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by table_name

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.
|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!

Dynamic ad-hoc reports

Is there a way to pass parameters into the Report Builder so that the available fields of an entity can vary depending on a parameter value (for instance, a group or user id)?

We have a situation where each group of users can have a variable set of custom properties to report on and we'd like to let them access these custom properties through the Report Builder. Any suggestions on how to go about it?

Thanks.

This is done thru model item security.

You can define access permissions to model items (entities, attributes, etc) based on user identity. You then create a report that includes an entity and all of its attributes.
When user A runs this report he will only see values of attributes he has access to, otherwise nulls

|||

Is there a way to pass parameters into the Report Builder so that the resulting data set can vary depending on a parameter value (for instance, a vendor id)?

From what I understand, a report model can only use data from tables or views. We have multiple vendors on our database. When they log into reporting services, we need to be able to pull up their data, and their data only (filtered by vendor)

So far, the only way for us to do this, is to have a separate view for each vendor. Which is a pain/nightmare. Any help?

|||Yes, you need to create a filter and specify prompt in the filter dialog|||

When I try to add a filter, the "Specify Prompt" feature is not available- all I see is "Edit As Formula", and "Remove Condition". Any suggestion why this is the case?

In addition, I do not want the end user to be able to change the filter- I want it to be applied automatically.

Is that possible as well?

|||

You want to use security filters in your model. You need to modify your model in model desginer to add security filters.

|||

I have found other threads mentioning security filters, and the "GetUserID" function-

But I still do not understand how to make use of it- is there an example you know of?

Do I need to add a filter to my model, or can I use an existing field and change that into a filter- and if I do make it a filter, how does it actually work at run time?

How do I incorporate the "GetUserID" with the user that logs in? It is not clear how these items work with one another.

|||

I have come up with a solution to this problem that will suffice for the time being.

I am creating a report model for each entitty (group) that needs a separate model.

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace. Each model also references the same set of database views. I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity. My .dsv files are also automatically generated via my app. This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy. All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know. Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

|||

I am searching for this kind of solution only

Can you please tell me how to generate models programmatically?

We have different state level users in our application, and one state user should not see another state user's data. Is there any way to pass the statecode (we can pass GetUserID() but how to pass other fields) as parameter or any other way to solve this problem?

|||

I have almost a solution, i think there is one issue with it, but maybe that's because of working in a citrix environment.

I used the securityfilters functionality. Here are the global steps to do it.

1. Create a new entity displaying the user ID's and the 'customer' (data) they have access to

2. Create a filter (in this new entity) displaying only the records of the current user. In my case the formula is: userid = upper ( RIGHT( GetUserID(),5)). Because the userid's always have five positions and in front of the userid the domain name is displayed, so I only want to compare the last five characters to the userid field in the database.

3. Set the property securityfilter of the new entity to the newly created filter

4. Link the entities in the datasourceview based on the company/customername to this new entity, so that every entity will be filtered based on this criteria. For example each customer record in our application has a dataareaid which i have to link to the new entity.

5. On the created link you have to apply the filter again. (so on the customer entity, you have to select the new role and then apply the securityfilter again.

The result here is that the reportbuilder will only display the customers that I'm allowed to see. I hope you get the idea. I personally think this is the proper way to do this, since maintaining multiple reportmodels is not what you want.

Bye,

Julian

|||I certainly think you are on to something there-

Can you be a little bit more specific in terms of the steps you are doing to accomplish this?

I know you summed up your steps- but it would make it easier if you made the steps more explicit- i.e., give a sample naming of the entities and where you set the right properties. The report model designer is a bit different in terms describing what you are actually modifying.

BTW, the way I dynamically created my reports, was I used an SSIS package that takes in parameters and creates a copy of my .dsv file and my .smdl file using a small application called ssed that can do a find/replace via command line.|||

In my project, i have many users with different roles, each role is called 'level'. One level user cannot access the other level user's data/report.

Actually what i need is:

1. How do i let only few users (role id like 1, 2,3.... from db) to access the report builder through application.

2. How do i let only few users to access the datasource via report builder, i dont want all the data sources to be displayed for all users logging throu my application. also here 'users' means not the logging users of SSRS...but users logging in my application. For eg. If the logged in user has role id 3, then he can access only certain reports/datasources, he cannot access another user's (say role id 2) reports/datasource. .. ..

How do i achieve this?

Can you pls help me.

Thanks in advance

|||

hi

i think that you should use reportviewer control within asp.net or c# and use the reporting services to render the report and don't emmbed parameters within the report try to validate a parameter withing your own application and just send a datatable to the report viewer and render it

if any one have idea about how to make within asp.net a wizard for creating dynamic report throgh the reporting services using report viewer control

i need to do that for a new forex articles website

http://www.123writers.com

and also for http://www.ybizz.com

any idea ?

|||I FINALLY got it to work exactly the way I want it to! I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx

Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as. So for example, if I login as "user@.group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@.")+1,LENGTH(GETUSERID()))"
What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!

After that, go give it a whirl and see if it worked! It totally works for me! Let me know what you think!