<< Fit in February – Final Results | Polyglot Programming: Death by a thousand DSLs >>

DNN Tips and Tricks #10: Reports Module

Mar27

For the last year and half I have increasingly turned to one module as my goto solution when building out new capabilities. For me the Reports Module is proving itself to be the Swiss Army knife of the DotNetNuke module world. With some of the features coming up in future releases of the Reports module this will become even more evident to anyone who takes the time to learn how to use the module.

So why do I feel so strongly about the reports module? It is mainly because of the architecture that Andrew Nurse put in place to allow you to create custom data sources and custom visualizers. This architecture makes it easy to get data from almost anywhere and then to have complete control over how it is displayed.

For most people the built in DotNetNuke Data Source, and XSLT Visualizer can handle most of your needs. You can query any data in DotNetNuke and then use XSLT to display that data. If you have not used XSLT before, you are missing out on a very powerful tool. Whether it is displaying a blogroll, the MarketPlace Monitor or even building an entire eCommerce site, XSLT gives you a lot of capability to take XML data and transform it into as simple or complex a page as you can imagine.

Right now there are three core modules with extensive XSLT capability built in: Forms and Lists, XML, and Reports. Forms and Lists is a great module, but it forces you to store data in the tables it defines. XML also has great XSLT functionality, but it only handles XML that is stored in a file or that is retrieved from a URL. If I want to report on Data, no matter where it is stored, my only option is the Reports module. The Reports module includes a custom data source that allows you to read UDT data, and it wouldn’t be hard to write a custom data source to read an XML file.

Even though I love the Reports module, the Forms and Lists, and the XML modules both have some features that are not currently supported by the XSLT engine in the Reports module. They all have their place in your DotNetNuke arsenal.

So if your need is just to display some data on the page, and you want something more than a simple table, then the Reports module is the tool for you. In the rest of this post I’ll walk through a recent requirement I had for our company intranet.

One common requirement in many intranets is that it should be easy for employees to find out basic contact and organizational information on other employees. DotNetNuke includes much of this information in the default user profile. For my purposes I wanted to display the following pieces of employee information:

Name
Photo
Job Title
Department
Manager
Phone Numbers (Work, Cell, Fax)
IM
Email
Location (Region, Country and Time Zone)

With my data elements identified, I can use the built in User Accounts screen to access the Manage Profile Properties page. From here I can add any profile elements I want to capture. The default set of profile elements cover most of my requirements, and it is easy enough to add any fields which are missing. Now employees can edit their own profiles and we’ll use the reports module to display those profiles.

To configure the reports module, you will need to be logged in with a Host account. The reason for this requirement is that the SQL query we are building could potentially access any data in the database. In a multi-portal environment you wouldn’t want one admin access data for another portal to which they didn’t have access. Since hosts have unlimited access to all data in the system, they are needed for defining the data source. Once the data source is defined then any user with edit permissions will be able to configure the visualizer.

Go to the module settings page and navigate down to the Data Source Settings section. For our example, I can use the DotNetNuke Data Source which uses the default dataprovider defined in the web.config. Then I enter my query. Finally, I will use the Show Xml Source link to display the resulting XML from my data source query. I will save this xml to a separate file so that I can test my XSLT during development.

Notice in my query that I just select all of the data from a custom view. You will not have this view on your DotNetNuke installation. One limitation I found very early with the Reports module is that it stores all of its configuration data in the ModuleSettings and TabModuleSettings tables. This limits the amount of text I can use for the query. I know from experience that querying profile data can get very complex and will easily exceed the size limitation of the settings tables. As a result I almost always create a custom view or stored procedure for my query. Because each piece of profile data is stored in a separate row of the UserProfile table, we have to do a join between the User table and UserProfile table for each property we want to include. As you can see from the query below this quickly becomes quite long, although most of the logic is just repeated over and over.

SELECT     
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    u.Username, 
    u.Email, 
    u.DisplayName, 
    up1.PropertyValue AS Region, 
    up2.PropertyValue AS Country, 
    up3.PropertyValue AS Telephone, 
    up4.PropertyValue AS Cellphone, 
    up5.PropertyValue AS IM, 
    up6.PropertyValue AS JobTitle, 
    up7.PropertyValue AS Manager, 
    up8.PropertyValue AS Department, 
    up9.PropertyValue AS Fax
FROM
    dbo.Users AS u INNER JOIN
    dbo.UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
    dbo.Roles AS r ON ur.RoleID = r.RoleID AND 
    r.RoleName = N'Employee' LEFT OUTER JOIN
    (SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Region' AND 
        ppd.PortalID = 0) AS up1 ON u.UserID = up1.UserID LEFT OUTER JOIN
    (SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Country' AND 
        ppd.PortalID = 0) AS up2 ON u.UserID = up2.UserID LEFT OUTER JOIN
    (SELECT     
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Telephone' AND 
        ppd.PortalID = 0) AS up3 ON u.UserID = up3.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Cell' AND 
        ppd.PortalID = 0) AS up4 ON u.UserID = up4.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'IM' AND 
        ppd.PortalID = 0) AS up5 ON u.UserID = up5.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Job_Title' AND 
        ppd.PortalID = 0) AS up6 ON u.UserID = up6.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Manager' AND 
        ppd.PortalID = 0) AS up7 ON u.UserID = up7.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Department' AND 
        ppd.PortalID = 0) AS up8 ON u.UserID = up8.UserID LEFT OUTER JOIN
    (SELECT
        up.UserID, 
        up.PropertyValue
    FROM
        dbo.UserProfile AS up INNER JOIN
        dbo.ProfilePropertyDefinition AS ppd ON 
        up.PropertyDefinitionID = ppd.PropertyDefinitionID AND 
        ppd.PropertyName = 'Fax' AND 
        ppd.PortalID = 0) AS up9 ON u.UserID = up9.UserID

Now that we have our data in an xml format (you did remember to use the Show XML Source and save the output to a file, didn’t you) we can turn our attention to defining our XSLT. Because our final output is html, I like to mockup my output in a web editor. This makes the XSLT much easier, as editing HTML when you have a bunch of XSLT logic interspersed can get a little tricky depending on the complexity. Using Expression web I am able to quickly mockup a simple display using the elements previously described.

As you can see in the HTML and CSS below, it is not too complicated (and it doesn’t use any tables).

<div class="employee">
    <img alt="No Photo Present" height="100" src="No.Photo.jpg" width="75" />
    <div class="organization">
        <h2>Joe Brinkman</h2>
        <p class="title">Technical Fellow, Co-Founder</p>
        <p><span>Department:</span>Engineering </p>
        <p><span>Reports to:</span> Shaun Walker</p>
    </div>
    <div class="contact">
        <h3>Contact Information</h3>
        <p><span>Work Phone:</span> 555-1212</p>
        <p><span>Mobile Phone:</span> 555-2121</p>
        <p><span>IM:</span> <a href="mailto:joe.brinkman@tag-software.net">joe.brinkman@tag-software.net</a></p>
        <p><span>Location:</span>&nbsp; OH, United States</p>
    </div>
</div>

.employee 
{
    padding: 10px;
    border: solid 1px silver;
    margin: 10px;
}
.employee .organization {
    float: left;
    width: 275px;
}
.employee .contact {
    float: left;
    width: 350px;
}
.employee img {
    float: left;
    margin-right: 10px;
}
.employee h2 {
    font-size: 1.2em;
    margin: 5px 0px 5px 0px;
    color: #800000;
}
.employee h3 {
    font-size: 1.1em;
    color: #333;
    margin: 5px 0px 5px 0px;
}
.employee p {
    margin: 0px;
}
.employee p.title {
    font-style: italic;
    margin: -5px 0px 10px 0px;
}
.employee span {
    display: inline-block;
    width: 120px;
    text-align: right;
    padding-right: 5px;
    font-weight: bold;
    color: #808080
}

Now we are ready to create our XSLT. We know what our HTML should look like and we’ll be able to do a quick test as we go to ensure that everything is working correctly. Visual Studio 2008 includes pretty decent XSLT support, but you can use whatever editor with which you are comfortable. I used to use XMLSpy from Altova, but have found the XSLT support in VS 2008 meets most of my needs.

Using my HTML as a template, I am able to quickly put together the XSLT below. Because my final output is going to be HTML, I indicate that in the XSL:Output tag. Then I do a quick match on all elements in “//DocumentElement” which is my root document element. I use this template because I have some html which should only be included on the page one time (the root element by definition only appears once). From there I apply another template for all of the QueryResults nodes in the XML document. You will have one node for each record returned by your query. This second template is where I insert my HTML snippet.

In the QueryResults template it is a simple matter to replace all of the static data in my sample HTML with an <xsl:value-of select="ElementName" /> tag. You can see my first cut at the XSLT below.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" >
    
    <xsl:output method="html" indent="yes"/>

    <xsl:variable name="baseDir">http://extranet.dotnetnuke.com/portals/0/Employee</xsl:variable>
    
    <xsl:template match="//DocumentElement">
        <link href="{$baseDir}/employee.css" rel="stylesheet" type="text/css" />
        
        <xsl:apply-templates select="QueryResults" />

    </xsl:template>
    
    <xsl:template match="QueryResults">
        <div class="employee">
            <img alt="No Photo Present" height="100" src="{$baseDir}/No.Photo.jpg" width="75" />
            <div class="organization">
                <h2>
                    <xsl:value-of select="DisplayName" />
                </h2>
                <p class="title">
                    <xsl:value-of select="JobTitle" />
                </p>
                <p>
                    <span>Department:</span>
                    <xsl:value-of select="Department" />
                </p>
                <p>
                    <span>Reports to: </span>
                    <xsl:value-of select="Manager" />
                </p>
            </div>
            <div class="contact">
                <h3>Contact Information</h3>
                <p>
                    <span>Work Phone:</span>
                    <xsl:value-of select="Telephone" />
                </p>
                <p>
                    <span>Mobile Phone:</span>
                    <xsl:value-of select="Cellphone" />
                </p>
                <p>
                    <span>MSN IM:</span>
                    <xsl:value-of select="IM" />
                </p>
                <p>
                    <span>Location:</span><xsl:value-of select="Region" />, <xsl:value-of select="Country" />
                </p>
            </div>
            <p style="clear:both;"> </p>
        </div>
    </xsl:template>
</xsl:stylesheet>

If you understand the HTML from my original template then the XSLT should be pretty straight forward. Now that I have the XSLT complete I can use Visual Studio to show me the sample rendering. Select the Show XSLT Output from the XML menu.

ReportsXSLTTest

Visual Studio will ask you for an XML file to use – you should select the XML file with the data we created earlier from our data source. Visual Studio will now render the HTML output. If everything goes correctly you are done. If the display is not exactly what you want then you can tweak the XSLT until everything is perfect. To help with this, Visual Studio allows you to debug the XSLT. This means you can set break-points and examine different values as you step through the XSLT. While that is probably not needed in this case, when you get into more complex pages, you will certainly appreciate this feature.

In this example I have left out some of the data elements and I haven’t included any complex logic. If this is your first time with XSLT then I definitely recommend that you start with something easy like the above example. With a little experience you will find that XSLT is a pretty full-featured language. There are some idiosyncrasies to get used to, but once you get into it, you will be amazed at the power and flexibility it provides.

The next version of the Reports module includes support for XsltExtension objects. This basically allows you to call custom .Net code from inside your XSLT. This means you could apply custom business logic in your XSLT that would be impossible to do in XSLT or SQL alone. You could do something like custom filtering of the displayed data based on the current user’s permissions. You could call a webservice in a custom extension and then merge the data with the rest of the data being displayed. You could do browser sniffing to determine how best to format the HTML output. With XsltExtensions you really remove any limitation imposed by the original DataSource or the XSLT language.

Now that you have the final XSLT, you can configure the XSL Transformation Visualizer. In this case I have uploaded my Employee.xsl file and left the rest of the settings with the default values. Depending on your data, you might need to do HTML Encoding or Decoding. Once everything is working correctly, you should definitely enable caching as well.

If you have been following along you should now end up with a display something like the one below. You can substitute your own “nophoto” picture and set the baseDir in the XSLT, but everything else should work without any difficulty.

At this point I hope I have shown you some of the power of the Report module when combined with the XSLT Visualizer. I will leave it as an excercise to the reader to figure out how to handle sorting and paging and how to determine the photo to display. There are several solutions you can choose depending on your skill and creativity. For more help on XSLT and XPATH I highly recommend www.w3schools.com. It has just enough information to give you a strong foundation without overwhelming you. I would definitely be interested to see how you are using the Report module or other XSLT based solutions in your own websites.

UPDATE:

After reading Greg Lahens comment I went and did some research to improve my SQL. I found a great article on Cross tabs and Pivots on SQLServerCentral.com (may require registration). Below is a Query which is much more optimized over the previous version. While there are still a few minor tweaks that could be done, those tweaks would not significantly improve performance and would require the use of SQL 2005+, so I decided to stick with a simple cross-tab query. This query dropped the number of reads to one fifth the amount over the previous query (for a small dataset) and as Greg points out, this version is much more readable and maintainable.

SELECT
    u.UserID, 
    u.FirstName, 
    u.LastName, 
    u.Username, 
    u.Email, 
    u.DisplayName, 
    upd.Region, 
    upd.Country, 
    upd.Telephone, 
    upd.Cellphone, 
    upd.IM, 
    upd.JobTitle, 
    upd.Manager, 
    upd.Department, 
    upd.Fax
FROM 
    dbo.Users AS u INNER JOIN
    dbo.UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
    dbo.Roles AS r ON ur.RoleID = r.RoleID AND r.RoleName = N'Employee' LEFT OUTER JOIN
(SELECT     
    up.UserID, 
    MAX(CASE WHEN ppd.PropertyName = 'Region' THEN up.PropertyValue ELSE '' END) AS Region,
    MAX(CASE WHEN ppd.PropertyName = 'Country' THEN up.PropertyValue ELSE '' END) AS Country,
    MAX(CASE WHEN ppd.PropertyName = 'Telephone' THEN up.PropertyValue ELSE '' END) AS Telephone,
    MAX(CASE WHEN ppd.PropertyName = 'Cell' THEN up.PropertyValue ELSE '' END) AS Cellphone,
    MAX(CASE WHEN ppd.PropertyName = 'IM' THEN up.PropertyValue ELSE '' END) AS IM,
    MAX(CASE WHEN ppd.PropertyName = 'Job_Title' THEN up.PropertyValue ELSE '' END) AS JobTitle,
    MAX(CASE WHEN ppd.PropertyName = 'Manager' THEN up.PropertyValue ELSE '' END) AS Manager,
    MAX(CASE WHEN ppd.PropertyName = 'Department' THEN up.PropertyValue ELSE '' END) AS Department,
    MAX(CASE WHEN ppd.PropertyName = 'Fax' THEN up.PropertyValue ELSE '' END) AS Fax
FROM          
    dbo.UserProfile AS up INNER JOIN
    dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID and ppd.PortalID = 0
Group By up.UserID) as upd on u.UserID = upd.UserID

3419d0c3-11ca-4760-934f-b9a056a5d63a|3|3.7

Tags: dotnetnuke, xslt, reports module
Categories: DotNetNuke, Tips and Tricks
Actions: Permalink | comment

Comments (18) | RSS comment feed

Comment RSS | | Kick it! |

DotNetNuke 4.6.0 RC1 DotNetNuke 4.6.0 is quickly approaching the release date. We have gone through 2 different ...DNN Tips & Tricks #6: Building Modules in VS 2008 DotNetNuke has long supported building modules as separate projects in Visual Studio. With th...DotNetNuke Tips and Tricks #13: Re-using Content with jQuery Over the past 6 years I have watched as DotNetNuke matured into a well-rounded platform that is cap...

Comments

March 27. 2009 15:15

Great article, Joe.
Your homework is a bit tricky. I have done XSLT based paging and sorting within Form and List and also with the XML module, however I have no idea how to handle this within Reports. As far as I know, I am not able to pass in any parameters into XSLT using Reports or to query the querystring within XSLT. Do I miss a secret feature?

Stefan Cullmann

March 27. 2009 15:26

Joe, where did you place your CSS? If you save it inside your XSLT, it will be located inside the body tag which breaks compliance. Adding it to portal.css or skin.css is overkill as it will be included on every page, Module.css isn't even better

Timo raised that also as question during my Form and List presentation, and I already thought about adding CSS definition to the Form and List configuration.

The right place would require a change to the core, so that we are able to save concrete CSS directly to a module inside module settings (ModuleInfo), right near to the header and footer definition. The CSS can then be injected as inline css into the header.

What do you think?

Stefan Cullmann

March 27. 2009 17:15

I agree that there needs to be a core modification to deal with the CSS situation.

As for the homework portion, it is a bit difficult to handle with the existing provider, however since I didn't limit the homework challenge to just be performed in XSLT, I would expect that adding the missing functionality for params would not be too difficult to cobble together. I would love to see the capabilities of the XML module incorporated into the datasource and visualizers within the Reports module as I think that it provides a much better framework to build off of.

jbrinkman

March 28. 2009 03:36

@Stefan - Reports Module "Saltspring" (the next major release, after the 5.1 minor release which I'm just finishing up) will include an extensible parameter system which both the Data Sources AND Visualizers will be able to access. So that should add some of the functionality you're missing .

Awesome article Joe . Thanks for the good press . It's really cool seeing all the things people are doing with the module.

(P.S. I think you got the link to my blog wrong... I'm getting a 404 when I click it )

Andrew Nurse

March 29. 2009 18:56

I know this is an article about the reports module but I have a question/comment about your SQL code. Wouldn't the SQL in your view be much shorter (and faster) if you used a pivot technique?

I know that not all DBMS' support the pivot command, but even in SQL 7 you could do it with a simple GROUP BY clause and CASE statements in your SELECT block. SQL 2005 and 2008 have a nifty PIVOT syntax that makes it even simpler. You would only have 1 join and would be simpler to edit and maintain in the future. To add another value from the UserProfile table would only require 1 additional line in the select block.

Greg Lahens

March 30. 2009 07:39

@Greg - Like many developers, I have a working knowledge of SQL, but I don't claim to always produce the prettiest or most efficient SQL. I would definitely be interested in seeing samples using the GROUP BY or PIVOT clauses.

I think this really highlights one of the problems of modern web development - programmers are often forced to learn multiple languages just to create a single solution. In this blog alone I show 4 different "languages". There are people whose whole career is focused around just SQL, CSS or XSLT. So when a generalist like me comes along we often learn enough to get the job done, but never have enough time to really become true experts.

jbrinkman

March 30. 2009 07:46

This weekend highlighted one of the problem of many Rich Text editors - editing the blog post in the RTE removed formating for the code examples. Hopefully, someday we'll have an RTE that follows the hippocratic oath.

jbrinkman

April 7. 2009 11:12

My SQL is probably as inefficient as yours in terms of performance, but at least it is much shorter! I've tried the PIVOT but that's not what it is for and I could not find any useful use in this case. Basically, the problem is that using one row per property is against the principles of relationnal databases, since it mixes data with metadata (the property name). Not surprising then that there is no elegant solution.

SELECT
a.propertyvalue 'Prénom',
b.propertyvalue 'Nom',
c.propertyvalue 'Rue',
d.propertyvalue 'Code Postal',
e.propertyvalue 'Ville',
f.propertyvalue 'Pays',
g.propertyvalue 'Tel.',
h.email 'Email'
FROM
userprofile a,
userprofile b,
userprofile c,
userprofile d,
userprofile e,
userprofile f,
userprofile g,
users h
where
a.userid=b.userid AND
a.userid=c.userid AND
a.userid=d.userid AND
a.userid=e.userid AND
a.userid=f.userid AND
a.userid=g.userid AND
a.userid=h.userid AND
a.propertydefinitionid = 21 AND
b.propertydefinitionid = 23 AND
c.propertydefinitionid = 26 AND
d.propertydefinitionid = 30 AND
e.propertydefinitionid = 27 AND
f.propertydefinitionid = 29 AND
g.propertydefinitionid = 31 AND
a.userid != 1
order by a.userid

Benoit Sarton

April 7. 2009 12:15

I loved your article Joe. It actually showed me a lot about what certain modules can do and has helped me explore other modules to see what their possibilities are. Could explain some of the other visualizers or at least point me in the direction of where I can find more information on them? I'm having a tough time finding instructions for a lot of these modules. Especially on their settings and what does what in the back end. Any help would be appreciated. Thanks!

Kevin

Kevin Paxton

April 26. 2009 09:02

Trackback from PimpThisBlog.com

DNN Tips and Tricks #10: Reports Module

PimpThisBlog.com

May 5. 2009 15:19

One issue not dealt with is when PropertyText is used instead of PropertyValue.

I'm still confused why there are 2 fields for this but can see once it goes over a certain size it's stored in PropertyText instead of PropertyValue.

David

May 5. 2009 16:34

Well I realise now from searching that the PropertyValue is preferred for efficiency, but sometimes PropertyText will be used... which is a small headache here for someone less experienced with SQL (ie. me).

David

May 5. 2009 17:24

The PropertyText is used when a property size exceeds what can be stored in the PropertyValue field. The text field has a lot of restrictions in SQL Server so it is not used if it can be avoided.

jbrinkman

May 6. 2009 01:48

I've now realised this... though unfortunately it's a User Profile item and others can only too easily exceed that limit - outside our control. Just something for people to bear in mind.

(ps. I hate it when they copy from Word!)

David

May 6. 2009 04:57

Dear Joe,

Is it possible with the report module to ask the Active Directory directly ?
I made that in the past with ASP2 and if it's possible to do that, I could create a phonebook for my intranet which will be ever up to date as Active Directory is the most up to date 'database'.
In fact, reports module don't take the choice to do a LDAP query but maybe exists another solution...
Many thanks !

Jocelyn

Jocelyn Laurin

July 30. 2009 18:52