we have more than 15 users & 6 Groups Right now & permissions to all is assigned per folder or file.
now i want some report kind of thing which give consolidate information for the permission assigned to the individual user...
is it possible ?
Just adding my support to this query, as I would really like to have a way to do this as well.
I would start with the Enterprise PDM Report Generator. First, import the supplied Reports, search for .crp files. Then do a little reasearch and hopefully you can write a report that will return the results you are looking for.
Try these T-SQL queries.
Thanks for the files. Are these queries for 2005 or 2008 SQL server? Does it matter? I'm seeing errors in 2008.
I am now!
Thanks again, very helpful.
how to use all this ...
i m not able to do anything with these attachments..
Google SQL Server Query and SQL Server Management Studio, the management studio comes with and is installed with the SQL Server application.
I was able to crack the code this way...
First looking at Group Folder Permissions:
Since I cannot access the SQL Server directly, I use the following file in report generator:
@[GroupRightsQuery]§Name [GroupRights]§Version [1.1]§Sql[SELECT G.* FROM GroupRights G /* Only include real WF */]/******************* ********************/
I export to a file named GroupRights.csv
Using MS Access 2007, I link to GroupRights.csv file as a table.
Then I need a cross-reference table, two custom functions, and three queries to obtain a "pivot table" of permissions.
The "Permissions" cross reference table is...
The first function is used to convert the decimal permission code to binary:
Public Function CBin(ByVal Nr As Long) As String Do Until Nr = 0 CBin = CStr((Nr Mod 2)) + CBin Nr = Nr \ 2 Loop CBin = CBinEnd Function
'A second function is also used...
Public Function BitSetting(strPermission As String, strSetting As String)'Test to see if the permission Binary Number contains a particular permission settingDim iPosition As LongiPosition = Len(strPermission) - Len(strSetting) + 1If iPosition <= 0 Then BitSetting = 0Else BitSetting = Mid(strPermission, iPosition, 1)End If
The first query is called "GroupRights Query"
SELECT GroupRights.GroupID, GroupRights.ProjectID, GroupRights.Type, cbin([Type]) AS pBinaryFROM GroupRights;
The second query is called "GroupRights Detail Query"
SELECT Groups.GroupID, Groups.Groupname AS [Group], Projects.ProjectID, Projects.Name AS Folder, gr.pBinary, Len([gr].[pBinary]) AS len1, P.pName, bitsetting([gr].[pBinary],[p].[pBinary]) AS setting, P.pBinary, Len([p].[pBinary]) AS len2FROM Permissions AS P, (Groups INNER JOIN [GroupRights Query] AS gr ON Groups.GroupID = gr.GroupID) INNER JOIN Projects ON gr.ProjectID = Projects.ProjectIDWHERE (((P.pClass)="Folder"));
The last query is called "GroupRights Crosstab Query"
TRANSFORM Max([GroupRights Detail Query].setting) AS MaxOfsettingSELECT [Folder]="PDM_VAULT_NAME" AS [Top], [GroupRights Detail Query].Folder, [GroupRights Detail Query].GroupFROM [GroupRights Detail Query]GROUP BY [Folder]="PDM_VAULT_NAME", [GroupRights Detail Query].Folder, [GroupRights Detail Query].GroupORDER BY [Folder]="PDM_VAULT_NAME", [GroupRights Detail Query].Folder, [GroupRights Detail Query].GroupPIVOT [GroupRights Detail Query].pName;
I can output the results to excel and use a conditional font to hide the zeros. The result looks like this...
Group Status/Transition Rights are done using a similar process.
Fantastic job, thanks for sharing.
You mentioned 'import the supplied Report', Are there some preset reports included with EPDM? If so where are they located?
I found the supplied reports.
1. Where should we keep (locate) the pulic function ?...
2. how to compile this functions?.
Ok - I created the DB with a link table to the outputed csv file. I also created the permissions table. I am unclear to how/where to apply the two functions and three querys.
The public function resides in a module that you store in MS Access.
For instance, go to the visual basic editor from within your MS Access session (ctrl+G).
Select an existing module or Insert/Module.
Place the public function there.
Nothing to compile.
You will need to copy and paste the two functions into a module that you store in MS Access.
Go to the visual basic editor from within your MS Access session (ctrl+G).
Place the functions there.
The three queries utilize the functions.
Paste each into a new query by doing the following:
Create/ Query Design. (Close the Show Table dialog).
click on View/SQL.
Paste the contents of the first query.
Save as. Give it the name I suggested. The queries call one another so the name is important.
Repeat for the other two queries.
Run the GroupRights Crosstab Query. It calls the other two. It uses the two functions as well.
Make sure you have a source table called GroupRights. (Either directly from EPDM or by exporting one using the Report Generator.)
Let me know how this works for you. A little luck is also needed. Here you go...
I'm closer, thanks ... but no dice. for some reason it is not seeing the cbin function when running the first query and snow balls from there.
attached is a zip with the db and table, if you see anything glaringly wrong - let me know and i will correct.
alittle luck, naa I need miracles LOL
Many Thanks - Pat
I see that you gave your modules the same name as your functions.
Try creating a module named Permissions_Code and put both functions in there.
Delete the other two modules and try the queries again. There seems to be a conflict with having the same names for modules.
ok - running the crosstab query errors .... "it cannot find the input table or query ' Groups'
btw - i am no longer getting the error regarding cbin (after your suggestion)
Well I declare! There's one other source table I forgot to mention.
It is looking for the EPDM table called Groups
Here is the .crp file for the Report Generator...
§Description[This is a test query.]
§Sql[SELECT G.* FROM Groups GORDER BY Groupname /* Only include real WF */]
Run this Report and export to a .csv file. Link to the .csv file with MS Access and call it Groups.
BTW, before I link the csv files, I always edit them with a text editor first and delete the heading row. Without doing this, you may import a numeric field as text.
You must be asking me that for a reason.
@[Projects Query]§Name [Projects]§Description[This is a test query.]§Version [1.1]§Arguments§Sql[SELECT P.* FROM Projects P /* Only include real WF */]/******************* ********************/
Ok - i think we satisfied it with all the tables it needs. But now when I run the crosstab query, the error message is "Type mismatch in expression"
My GroupRights table is defined as numeric.
I noticed your GroupRights is defined as text.
I suspect you may not have stripped out the heading rows of each of your .csv files with a text editor prior to linking them.
If you delete the links you made and try that it should work better. Otherwise, you could change the data types on your local tables to text so they match but I don't recommend that. There is too much going on that needs to be numeric.
Good code and it gives a huge thought regarding the SQL reports
Can i do this in case of Status Name also?????
The requirment is a report consists folder name, group name, their rights, status name with their respected workflow which applied in that specific folder name.
i have a example which is inspired with your example but it not giving the correct result [please see the attachment]
in waiting of your quick reply .....
Retrieving data ...