Report Builder Question

Hi Guys!

Can I use the report builder to create reports to show how each user account is setup, etc.

I am looking for a quick way to see at a glance all details without going into each one individually.

Thanks!

Paul

Re: [Pixels & Beats] Report Builder Question

By Jason - December 9, 2011

Hi Paul,

Yes you can. You can take any valid MySQL query and use that in report builder.

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Report Builder Question

By gadefgaertgqe - December 12, 2011 - edited: December 12, 2011

OK, plugin is bought and going really well, except for one last hurdle.

I've learnt some MySQL but I seem unable to get the last and most important information. I need to know what sections each user has been given access too. After digging around I think I need to do a MySQL join, and have tried all manner of combinations this morning, with no luck. Here is the working MySQL query I have at the moment:

SELECT dealer_title as 'Sub Domain',
fullname as 'Full name',
email as 'Email',
num as 'User No',
disabled as 'Account Live',
isAdmin as 'Admin Status',
DATE_FORMAT(lastLoginDate, '%D %M %Y %T') as 'Last Login'


FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE true
GROUP BY dealer_title


Can anyone give me a hint on how to get the _accesslist joined?

I think I need to use the following to get the full picture:
'userNum' in _accesslist joined with 'num' in accounts

then I need to show any records where 'userNum' in _accesslist and 'num' match then show info from:
'accessLevel'
'maxRecords'
in the _accesslist

I think I have chosen a fairly involved bit of MySQL query to start learning with lol.

Thanks

Paul

Re: [Pixels & Beats] Report Builder Question

By gadefgaertgqe - December 13, 2011 - edited: December 13, 2011

OK, I've kept going as best as I can and I am so very close. My main problem now is that I need to filter out some of the data! I need to only show what the user has access to, but I am able to see what they do and do not have access too..

SELECT dealer_title as 'Sub Domain',
fullname as 'Full name',
email as 'Email',
userNum as 'User No',
disabled as 'Account Live',
isAdmin as 'Admin Status',
DATE_FORMAT(lastLoginDate, '%D %M %Y %T') as 'Last Login',
tableName as 'Access List',
accessLevel as 'Access Level',
maxRecords as 'Max Records'

FROM `<?php echo $TABLE_PREFIX ?>accounts` AS accounts_record
LEFT JOIN `<?php echo $TABLE_PREFIX ?>_accesslist` AS accesslist_record ON accounts_record.num = accesslist_record.userNum
UNION
SELECT dealer_title as 'Sub Domain',
fullname as 'Full name',
email as 'Email',
userNum as 'User No',
disabled as 'Account Live',
isAdmin as 'Admin Status',
DATE_FORMAT(lastLoginDate, '%D %M %Y %T') as 'Last Login',
tableName as 'Access List',
accessLevel as 'Access Level',
maxRecords as 'Max Records'

FROM `<?php echo $TABLE_PREFIX ?>accounts` AS accounts_record
RIGHT JOIN `<?php echo $TABLE_PREFIX ?>_accesslist` AS accesslist_record ON accounts_record.num = accesslist_record.userNum

GROUP BY dealer_title


Any ideas?

Re: [Pixels & Beats] Report Builder Question

By Jason - December 13, 2011 - edited: December 13, 2011

Hi,

You can try returning the table name from the accesslist section as a subquery like this:

SELECT user.dealer_title as 'Sub Domain',
user.fullname as 'Full name',
user.email as 'Email',
user.num as 'User No',
user.disabled as 'Account Live',
user.isAdmin as 'Admin Status',
DATE_FORMAT(user.lastLoginDate, '%D %M %Y %T') as 'Last Login',

(SELECT GROUP_CONCAT(tableName, ', ') FROM
`<?php echo $TABLE_PREFIX;?>_accesslist`
WHERE userNum = user.num
AND accessLevel != 0) as 'Access List'


FROM `<?php echo $TABLE_PREFIX ?>accounts` user
WHERE true
GROUP BY user.dealer_title


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Report Builder Question

Hi Jason,

Thanks for that, I think we are close but I am getting an error of:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'Access List' FROM `cms_accounts` user WHERE true GROUP' at line 12

Paul

Re: [Pixels & Beats] Report Builder Question

By Jason - December 13, 2011

Hi Paul,

Ooops, I missed a closing bracket in my sub-query. I've made this change in the previous post if you want to try that again.

Thanks
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Report Builder Question

Nice one Jason!! Thanks for the help :)

Re: [Pixels & Beats] Report Builder Question

By Jason - December 14, 2011

Hi Paul,

A user is given an access Level of 1 to the "all" table when you select the "By Section" option for a user. This won't give them access to anything that you don't specify by section, so there is no security problem. However, you probably don't want it to show up for everyone, so try this change:

SELECT user.dealer_title as 'Sub Domain',
user.fullname as 'Full name',
user.email as 'Email',
user.num as 'User No',
user.disabled as 'Account Live',
user.isAdmin as 'Admin Status',
DATE_FORMAT(user.lastLoginDate, '%D %M %Y %T') as 'Last Login',

(SELECT GROUP_CONCAT(tableName, ', ') FROM
`<?php echo $TABLE_PREFIX;?>_accesslist`
WHERE userNum = user.num
AND accessLevel != 0
AND accessLevel != 1) as 'Access List'


FROM `<?php echo $TABLE_PREFIX ?>accounts` user
WHERE true
GROUP BY user.dealer_title


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/