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 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.

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/