Report Builder Question
11 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: December 15, 2011 (RSS)
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
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
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_titleCan 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
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_titleAny ideas?
Re: [Pixels & Beats] Report Builder Question
By Jason - December 13, 2011 - edited: December 13, 2011
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_titleHope 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
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
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
Re: [Pixels & Beats] Report Builder Question
I am wondering why this is? The Admin account shows 'all' as it's only one (which is of course expected).
Just curious, and also cautious in case there is an issue I am unaware of.
Re: [Pixels & Beats] Report Builder Question
By Jason - December 14, 2011
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_titleHope 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/