Report Builder Question

11 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: December 15, 2011   (RSS)

By gadefgaertgqe - December 9, 2011

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

By gadefgaertgqe - December 13, 2011

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

By gadefgaertgqe - December 13, 2011

Nice one Jason!! Thanks for the help :)

Re: [Pixels & Beats] Report Builder Question

By gadefgaertgqe - December 14, 2011

One more quick question. I am seeing the table name 'all' being output against all users as something they have access to.

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.