Parameter problem

HI, I have a report that users select a parameter value from a dropdownbox.

I want to be able to limit the number of parameter options depending on who is logged in.

user A can only see values 'ABC', 'DSC', 'BHT''

whereas user B can see values 'MKP','NHJ','BLP'

is this possible? if so how?

TIA

Reporting services provides the "User!UserID" global parameter in all its reports. This populates with the windows user name if I'm not mistaken. You could pass this value as a parameter to the dataset that you're using to populate the dropdown box.|||If the datasource is driven by a view or a stored procedure or a query you can implement row-level security using a mapping table which locks ( locks up down the appropiate values which should not be seen by some people. If you have a static list, this would be too hard to implement / impossible (Nothing is impossible, impossible just takes longer :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I was hoping to be able to use custom code to populate the parameter dropdownlist/ set the parameter values.

Is this possible? can I reference datasets in my code?

|||

I created a view then set a filter on the dataset to return values based on the User!UserID=UserName field from dataset

then set the parameter to 'From Query' chose the dataset and appropriate field from dataset and everything aok.

|||Welcome to rowlevel security :-)|||

I am trying to do the something similar. In my report i created a stored procedure that stored procedure is passed a parameter CaseNumber. (I am using the Report Veiwer Control in Remote Mode). In Report Designer, I created to datasets the first dataset populates the dropdown list box and it is displayed in the toolbar. The Second dataset takes this parameter (CaseNumber) and brings back the report. Everything works fine in the Report Manager; however, when I run it from frmReport.aspx page the report does not show.

If I run the report without any parameters from the frmReport.aspx page (still using the stored procedure w/o using parameters) the report shows. Can any body help me with this.

Zachary

|||

Fixed the problem - thanks

Zachary

paging query in sql server compact edition

Hi,

I want to write query to implement paging in sql server ce 3.0. But it seems it does not support TOP or LIMIT keyword.

Can someone suggest alternative way to write a custom paging query in sql server ce 3.0.

Thanks,

van_03

You are correct, there is no intrisic support for paging the results from the query processor. What I do is use the SqlCeResultSet which has the ability to page through a range or records once you create the result set.

Darren

|||

Actually I have about 1 million rows in my table the sql sercer ce database. The table has no numeric field and no identity column either. I want to pick chunks of rows, may be about 100000 at a time. Then next chunk and so on. Can you suggest some way of acheiving this ?

Thanks,

van_03

Paging in SQL Server

Hiii all

SQL Server 2000 or 2005 dose not support the LIMIT statement like mySQL. So plz can anyone tell me tht how to do paging in SQL Server? Without using CLR Integration...

Hi,

First, I'd said LIMIT keyword is not supported in T-SQL.

To paginate data on SQL Server 2000, you can read that article describing few solutions :

http://www.codeproject.com/aspnet/PagingLarge.asp

SQL Server 2005 introduce some T-SQL enhancements to support pagination with function ROW_NUMBER().

Here is how to use it paginating Contact table of AdventureWorks database.

Code Snippet

With Contacts As

(

SELECT c.FirstName, c.LastName

,ROW_NUMBER() OVER(Order By Firstname) AS 'RowNumber'

FROM Person.Contact c

)

Select * From Contacts

where RowNumber Between 10 and 20

Jean-Pierre Riehl

http://blog.djeepy1.net

|||Wrong forum. Moving to Transact-SQL from SSIS.

page overflow

How much of a performance hit do I take if I need to take advantage of the overflow area for a varying character that exceeds that 9 K page limit? Is it best to stay away from this except for emmergencies?

the cost of overflow is relative. It all depends on how much your data is fragmented. Run showcontig to determine the fragmentation.

In general, sql2k5 is much better at handling large data than sql2k. However, if you do not need blob, don't use it.