How to get the folder wise security info of all the users?

Hi,

The Report Manager portal has many folders. For each folder there are specific users with different roles.

I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID

The above query fetches all the users and their roles.

The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.

TIA

The security settings appear to be in XML. See the GetPolicy stored procedure.

select xmldescription from secdata

You can see the security on each folder by running rsscripter utility and looking at the resulting code in the fldr.rss files.

'Set Folder security
Dim pol(4) As [Policy]
Dim sersec As Serialization.XmlSerializer = New Serialization.XmlSerializer(GetType(Policy))
Dim tsec0 As New StringReader("<?xml version=""1.0"" encoding=""utf-16""?><Policy xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""> <GroupUserName xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">BUILTIN\Administrators</GroupUserName> <Roles xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices""> <Role> <Name>Content Manager</Name> </Role> </Roles></Policy>")

If I was trying to reverse-engineer how to get the security roles, I would look into SQL Profiler to see what calls are being made when setting/getting security on folders.

You may have more luck using SOAP & Web Service to get the information you want.

regards,

Andrew

How to get Specific rows from Table

hi

i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200

HI,

do it as

for n to n1 records do as

select top n1-n * from

(

select top n1 * from

)

order by key desc

|||

If your total result set is small enough (> 1000 records) you could insert into a table variable with an identity column and then select back out referencing the identity column in the where clause. For example:

DECLARE @.tblTable TABLE

(

TableID INT IDENTITY(1,1)

,OtherID INT

,Value VARCHAR(50)

)

INSERT @.tblTable (OtherID, Value) VALUES (1, 'One')

INSERT @.tblTable (OtherID, Value) VALUES (2, 'Two')

INSERT @.tblTable (OtherID, Value) VALUES (3, 'Three')

INSERT @.tblTable (OtherID, Value) VALUES (4, 'Four')

INSERT @.tblTable (OtherID, Value) VALUES (5, 'Five')

DECLARE @.Start INT, @.End INT

SELECT @.Start = 1, @.End = 3

SELECT *

FROM @.tblTable

WHERE TableID BETWEEN @.Start AND @.End

|||

It seems that you are trying to do pagination in database.

Below is the code snippet for a stored procedure. This takes page number and numbers of records in a Page.

CREATE PROCEDURE Pagination
@.Page int,
@.Size int
AS

DECLARE @.Start int, @.End int
BEGIN TRANSACTION GetDataSet
SET @.Start = (((@.Page - 1) * @.Size) + 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
SET @.End = (@.Start + @.Size - 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @.@.ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
-- Any kind of select statement is possible with however many joins
-- as long as the data selected can fit into the temporary table.
IF @.@.ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @.Start) AND (Row <= @.End)
IF @.@.ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @.@.ERROR

Regards

Sachin

How to get specific number of rows from each group

I need a SQL query to get 2 items from each catergory.
And if possible, 2 items with price < $100 and 1 item with price >= $100
from each caterogy.
Table: tblItems
Fields: ItemID, CategoryID, ItemName, ItemPrice
A stored procedure with multiple queries also works.
Thanks,
--
GeeviSelect * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price
Union
Select Top 1 PK From Table
Where Price >- 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Sorry, Union won't work because you can;t use Order by in parts of a Union..
.
So you have to use separate In Predicate conditions...
Select * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||aaaghhh!! Forgot to alias the first Table...
Select * From Table As T -- Left off the "As T" Before
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Burying the TOP .. ORDER BY should work:
Select * From Table as T
Where PK In (
select PK from (
Select Top 2 PK From Tbl
Where Price < 100
And Category = T.Category
Order By Price
) PartA
union
select PK from (
Select Top 1 PK From Tbl
Where Price >= 100
And Category = T.Category
Order By Price Desc
) PartB
)
-- I don't know how to get 2 items, of which 2 have prices < $100
-- and one has price >= $100.
Steve Kass
Drew University
CBretana wrote:
> Sorry, Union won't work because you can;t use Order by in parts of a Union
..
> So you have to use separate In Predicate conditions...
>
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price)
> Or PK In
> (Select Top 1 PK From Table
> Where Price >= 100
> And Category = T.Category
> Order By Price Desc)
>
> "Geevi" wrote:
>|||Great! It works!
I could not use the Order By "Price" as "ORDER BY items must appear in the
select list if the statement contains a UNION operator."
But this is a big time saver for me.
Thanks CBretana, for the solution!
"CBretana" wrote:
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price
> Union
> Select Top 1 PK From Table
> Where Price >- 100
> And Category = T.Category
> Order By Price Desc)
>
>
>
> "Geevi" wrote:
>

How to get overall total and total of specific instance?

I'm pretty new to SSAS, so I looked through the forum to see if this had been answered. I couldn't find it, but if its here please let me know.

I have a simple fact table of vendors and products purchased: Vendor, Part, Qty

Multiple Vendors can carry the same Part. I want to display the total Qty over all Vendors and the total Qty by a specific vendor for a specific Part purchased.

Example:

Vendor Part Qty TotalQTY

001 5x 3 10

002 4c 1 8

003 4c 7 8

004 6q 9 15

001 6q 6 15

What would be the best way to go about this?

Thanks in advance!

This 1st row of sample data is confusing, because it seems to be the only row for Part 5x; but maybe a row is missing:

Vendor Part Qty TotalQTY

001 5x 3 10

Anyway, assuming that there are Vendor and Part dimensions and a [Qty] "sum" measure, [TotalQty] could simply be like:

([Measures].[Qty], [Vendor].[Vendor].[All Vendors])

|||Thank you very much! I can see why you are an MVP.

how to get last year revenue up to a specific month

Please help

i have a office web component pivot table that pulls revenue for 2005 ([Measures].[Rev],[Date].[2005]). This shows all revenue to May 2005. I also want to display all revenue for 2004 (up to may).

I was trying to hard code the month but was unsuccessful. Here's what I tried:

([Measures].[Rev], ([Date].[2004].[Quarter 1].[January],[Date].[2004].[Quarter 1].[February],[Date].[2004].[Quarter 1].[March],[Date].[2004].[Quarter 2].[April],[Date].[2004].[Quarter 2].[May]))

..

|||You have to make a calculated member.

Try this
MEMBER [Measures].[Rev To May 2004] AS ' SUM({[Date].[2004].[January]:[Date].[2004].[May]}, [Measures].[Rev]) '
If you want is to always show the same period you should make something like this.
MEMBER [Measures].[Rev To May 2004] AS ' SUM({PARALLELPERIOD([Date].[Year], 1, [Date].CURRENTMEMBER.FIRSTCHILD.FIRSTCHILD : PARALLELPERIOD([Date].[Year], 1, [Date].CURRENTMEMBER}, [Measures].[Rev]) '

(This works if your dimension has these levels year, quarter, month, day
I hope it helps you.