Forum

Search Home

1
2
3
4
5

Home

Public

3rd Party Modul...

Reports Module - HTML visualiser and Table

11/18/2013 11:57 PM

Dave

Joined: 6/28/2012

Posts: 76

Reports Module - HTML visualiser and Table

Hi Aderson

In your videos you show how to use the HTML Visualizer to format the data so that results are shown as a link

I would like to achieve the same but to also have some additional data next to the link in a tabular form

e.g.

Name (click for details)	Phone No.
Aderson	(555) 123 4567
Dave	(555) 999 4321

Thanks

Dave

11/19/2013 4:23 AM

Aderson Oliveira

Joined: 12/28/2009

Posts: 1011

Re: Reports Module - HTML visualiser and Table

Hi Dave,

This is a bot more complex to achieve as data like "Phone" and others from the user profile are not part of the Users table. They are extended properties that can only be found under the "UserProfile" table. There are a few different ways to skin this cat but here here is one of the ways you can go about this:

1 - First you need to find the PropertyDefinitionID from the field that you would like to display. To do that run the following SQL under Host/SQL:

select *
from ProfilePropertyDefinition
where PortalID = 0

- PortalID most likely will be 0 (ZERO) if you just have 1 portal on your DNN install

2 - Once you run the query above, just go through the results and find the "Telephone" row. In my case, the phone row has 31 as its PropertyDefinitionID

3 - With that id you can now run a new query on your report:

SELECT Users.UserID,
Users.Username,
Users.DisplayName,
Users.Email,
CONVERT(VARCHAR(10), UserRoles.EffectiveDate, 7) AS StartDate,
CONVERT(VARCHAR(10), UserRoles.ExpiryDate, 7) AS EndDate,
(SELECT PropertyValue FROM UserProfile WHERE UserProfile.UserID = Users.UserID and PropertyDefinitionID = 31) AS Phone
FROM Users INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID
WHERE (UserRoles.RoleID = @url_roleid)

- Notice that we are filtering by PropertyDefinitionID = 31 as the Phone. If you want another field, just change the ID and create a new line in the report query.

I hope this helps.

Cheers,

Aderson

11/19/2013 5:31 AM

Dave

Joined: 6/28/2012

Posts: 76

Re: Reports Module - HTML visualiser and Table

A big sorry. Did not make myself clear

It's the formatting I'm interested in not the data

I'm trying to achieve something along the lines you see here BBC Sport Football

The table will change order based on results but if you click on a team name you get more info about that team

So I'm looking at SQL along the lines of

SELECT Team, Points FROM Results ORDER BY Points DESC

Then to have an HTML template to display the results but that the Team provide a link to another page / report

Hope that makes sense :-)

Regards

Dave

11/19/2013 6:33 AM

Aderson Oliveira

Joined: 12/28/2009

Posts: 1011

Re: Reports Module - HTML visualiser and Table

Hi Dave,

I think you will have a very hard time to make this work with the reports module. My recommendation would be to explore a module like xMod instead. With xMod you can have listings and detail views which can be precisely what you need here.

Best regards,

Aderson

11/19/2013 7:42 AM

Dave

Joined: 6/28/2012

Posts: 76

Re: Reports Module - HTML visualiser and Table

Sorry again - maybe I gave a particularly complex examples

On this page www.dorsetgolfleagues.co.uk there are 2 examples, one is "coded" and the other at the bottom is the Reports module with HTML visualiser

Using an example of the code you produced

<div>
<a href="/our-matches?TeamName=[TeamName]">[TeamName]</a>
</div>

I can add extra information e.g. points, captains name etc but as it's not in a table it formats to

<div>
<a href="/our-matches?TeamName=[TeamName]">[TeamName]</a>, [Points], [Captains Name]
</div>

but the format is

Arsenal, 25, Arsene Wenger

Liverpool, 23, John Smith

Totenham Hotspur, 16, Elvis Presley

rather than

Team	Points	Manager
Arsenal	25	Arsene Wenger
Liverpool	23	John Smith
Totenham Hotspur	16	Elvis Presley

The format of the table is NOT important just the alignment would be key, even losing the headers would not be a particular issue

I've tried this as a solution which nearly works but every line of data has a heading row

<table>
<tbody>
<tr>
<td><a href="/performance?PlayerName=[PlayerName]">[PlayerName]</td>
<td>[Starting]</td>
<td>[Next Time]</td>
</tr>
</tbody>
</table>

Any thoughts?

Unlike the Dorset Golf Leagues site I need this for an expanding group of players, so the hard coded option doesn't work, if all else fails then then just the name linking to extra info will suffice.

Page 1 of 2

1

Reports Module - HTML visualiser and Table