2 Replies Latest reply on Feb 12, 2014 3:54 PM by zackm

    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

        • Re: Need help with User Account report
          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

          • Re: Need help with User Account report
            zackm

            you could also try this one for a little more information in one report:

             

            Orion_Account_Audit_Loop1Systems.OrionReport

             

             

            - ZackM

            http://www.loop1systems.com