Need help with User Account report

patriot

I need a little help with a report I am creating. I would like to list various settings information for user accounts such as AccountID, the various "Allow" settings, home page, and any account limitations that may be assigned. My challenge is the JOIN statements that are necessary to collate information from the Accounts, Limitations, and LimitationTypes tables. For instance, I want to show that name of things like the Home Page and Account Limitation, rather than a number.

Can anyone help me with joins? I appreciate it very much.

Here is a list of fields that I want in my report:

AccountID AS UserID, AccountEnabled AS Enabled, MenuName AS MenuBar, AllowAdmin AS Admin, AllowNodeManagement AS Manage_Nodes, AllowCustomize AS Customize, AllowReportManagement AS Manage_Reports, LimitationID1 AS Limitation, HomePageViewID AS Home_Page, NodeDetailsViewID AS Node_Details_View

Accounts.LimitationID1 AS Limitation, Limitations.LimitationID AS Limitation_ID, Limitations.LimitationTypeID AS Type_ID, LimitationTypes.LimitationTypeName AS Limitation_Name

LadaVarga

Hello,

Do you need exact limitation or only type of limitations?

With exact limitation + title of homepage:

select AccountID AS UserID, AccountEnabled AS Enabled, MenuName AS MenuBar, AllowAdmin AS Admin, AllowNodeManagement AS Manage_Nodes, AllowCustomize AS Customize, AllowReportManagement AS Manage_Reports,

ISNULL(L1.whereclause,'empty') as Limitation1, ISNULL(L2.whereclause,'empty') as Limitation2,ISNULL(L3.whereclause,'empty') as Limitation3,

V.viewtitle AS Home_Page, NodeDetailsViewID AS Node_Details_View from accounts AS A

LEFT join Views as V on v.viewid=A.HomePageViewID

LEFT join Limitations as L1 on A.limitationID1=L1.limitationid

LEFT join Limitations as L2 on A.limitationID2=L2.limitationid

LEFT join Limitations as L3 on A.limitationID3=L3.limitationid

Of course these exact limitation is not good readable.

Thanks

Lada