Archive

Archive for April, 2009

Business Action World Tour

April 27, 2009 Leave a comment

Business We’ve had some great success with the Business Action World Tour events worldwide.  We’re now reprising that content virtually for those ISVs and developers who could not attend a physical event.  Be sure to register via the links at the bottom of the post.  Feel free to invite others…  you know wake the kids, call the neighbors, etc.  🙂

We know that differentiating your line-of-business applications from those of your competitors has never been more important. If you’ve considered using Dynamics CRM as a line-of-business platform, Silverlight or WPF to build compelling experiences, Office or SharePoint to build on the familiar skills of your end-users or Partner-Hosting or Azure Services to extend you application to the cloud, well then this is the event for you.  We’re hosting a series of 4 online events (across multiple time zones) that will help you understand how we can help you do that as a valued Microsoft Partner.

Because of the anticipated high-demand, we encourage you to register immediately to ensure your attendance at this valuable event.  We’ve provided an overview below and the upcoming event dates and registration links at the bottom of this message. Be sure to register for all 4 online events.

A Line-of-Business Application Platform

CRM Microsoft Dynamics CRM provides the platform for getting your line-of-business applications to market fast. Clearly applicable in CRM-type scenarios, Microsoft Dynamics CRM also supports a wide range of business applications with the essentials required for building, delivering and maintaining them in multi-tenant Software+Services environments.

This event will demonstrate the power of Dynamics CRM as a platform for developing line of business applications. It will also cover how solutions built on the Dynamics CRM platform benefit from other Microsoft technologies to provide compelling experiences, build on familiar skills and give your customers the power of choice.

Compelling Experiences

Silverlight Increase user productivity and accuracy by providing an attractive, easy to use application user interfaces on either the desktop or the web using one set of tools and knowledge. With Windows Presentation Foundation (WPF) you’ll build the next-generation of Windows user experiences with deep support of data visualization, complex text content, rich media, dynamic interactive experiences, and branded or custom look and feel. With Silverlight you increase repeat visits and brand loyalty while dramatically reducing customer support costs through compelling, intuitive, high performance self-service web sites.

Familiar Skills

Office Increase the productivity of the users of your applications by providing a seamless and integrated end to end experience that empowers them to collaborate and get work done in familiar ways. Pull line-of-business processing directly into the Microsoft Office applications your users use day-in day-out. Unified communications across the Dynamics CRM and Office line of products, such as instant messaging, document sharing, and user presence, further promote and simplify ad hoc processes and keep people in the center of the workflow – where they belong.

Power of Choice

azure Whether you are deploying behind the firewall or internet-facing applications, build and deploy those applications quickly and easily by capitalizing on the same personnel, development tools investments, and knowledge that already power your organization. When you build on the Dynamics CRM platform you’ll be prepared to deploy your line-of-business applications on-premises, in partner-hosted facilities or in Microsoft own Dynamics CRM Online data centers. When you build the internet-facing self-service applications that integrate with your line-of-business solution, the Azure Services Platform helps reduce IT-related costs, freeing up time and capital to focus on your core business.

Be sure to register for all 4 events below.

  Line-of-Business Application Framework Compelling Experiences Familiar Skills Power of Choice
Europe Middle East and Africa Click to Register Click to Register Click to Register Click to Register
North and South America Click to Register Click to Register Click to Register Click to Register
Asia/Pacific Click to Register Click to Register Click to Register Click to Register
Note: You are free to register for any time zone you choose. The content is the same for all time zones.

Europe, Middle East and Africa (Mon Apr 27-Thu Apr 30)

  • 9:00 AM UTC – Dublin, London
  • 10:00 AM UTC+1 – Madrid, Paris, Brussels, Amsterdam, Geneva, Copenhagen, Rome, Berlin, Prague, Stockholm, Budapest, Warsaw, Oslo
  • 11:00 AM UTC+2 – Cape Town, Athens, Helsinki, Istanbul, Cairo, Jerusalem, Beirut
  • 12:00 PM UTC+3 – Moscow, Kuwait City
  • 1:00 PM UTC+4 – Tehran, Dubai
  • 2:30 PM UTC+5:30 – Bangalore
  • 4:00 PM UTC+7 – Bangkok, Hanoi, Jakarta

North and South America (Mon Apr 27-Thu Apr 30)

  • 9:00 AM UTC-7 – Vancouver, San Francisco, Los Angeles, Phoenix, Redmond
  • 10:00 AM UTC-6 – Calgary, Denver, Mexico City
  • 11:00 AM UTC-5 – Houston, Minneapolis, Chicago
  • 12:00 PM UTC-4 – Caracas, Toronto, Montreal, Boston, Miami, New York
  • 1:00 PM UTC-3 – Buenos Aires, Sao Paolo

Asia/Pacific (Tue Apr 28 – Fri May 1)

  • 9:00 AM UTC+8 – Singapore, Hong Kong, Beijing, Shanghai
  • 10:00 AM UTC+9 – Perth, Seoul, Tokyo
  • 11:00 AM UTC+10 – Sydney
  • 12:00 PM UTC+11 – Auckland

Cheers,

Ben Riga

Advertisements

New: More Social Partners on Windows Live

April 24, 2009 Leave a comment

The mesh between social software sites and my prose means that one writing ends up on many different places. And the audiences on the various platforms is usually very different.

So I was very happy to see that Microsoft is adding 20 new feed partners and three new contact partners to the growing Windows Live program. As reported by arstechnica.com:

“This means that means that users will be able to invite their friends to Windows Live and vice versa; Facebook and LinkedIn are also to become contact partners “soon.”

Microsoft’s goal with this is quite obvious: to turn Windows Live into the average netcitizen’s main hub for his or her social life, or at the very least to turn Windows Live into a social network.”

Read more… 

The ability to go one place to do my work is a very powerful incentive to looking at yet another solution set. The use of RSS readers is an example of that. I can scan all my fav blogs and see which of the 40 or so have been updated. So in any day there might be five or so new posts and if the title interests me, I can read more. And in this world of information overload, this is just one way to become a more efficient reader.

With these new lash-ups, I can tailor my authoring and publishing environment in a way that targets different reading audiences. And I can potentially get to a wider audience set without taking a lot of time for each post.

How cool is that? Play on bloggers.

Cheers,

JaAG

Solving the Dashboard Dilemma – Choosing the Right BI Tools for your Job

April 22, 2009 Leave a comment

There’s a funny thing about business intelligence: people are baffled by the technologies involved in building insights but the most challenging things about the discipline have little to do with technology. I’ll grant that using PIVOT and GROUPING SET queries is not trivial but any developer can figure those out in an afternoon. Where I see people struggling more is in deciding which tools to use for BI projects.

 

Let’s take dashboards as an example. There are a great many dashboarding tools available: stand alone tools, tools for developers, tools for end users, tools for specific applications and tools that run across enterprise data sources. Deciding which of these tools to use isn’t a matter selecting the best of breed tool or even just deciding on the one that comes with your application; it is a question of which tool is best for your organization.

For any sort of application you should evaluate your organization’s BI capabilities. Let’s take three cases:

1. Capabilities: BI at your business is mostly about reporting. The IT organization is fairly efficient at churning out RDLs on demand.

Dashboard Solution: Use RDL. With a team of developers proficient in building reports you should use their existing skills and leverage existing infrastructure. It’s easy for RDL developers to build dashboards with RDL and with SQL 2008 you can go some really great looking stuff. For users whose KPIs and report insights don’t change very often this is the best choice.

Barry1

An RDL based dashboard from SQL Server Reporting Services 2008

2. Capabilities: End users are good at doing their own analysis or are always changing their minds about what they want to monitor or are not happy with the speed of report development that they get from IT.
Dashboard Solution: Use Excel. Users already have Excel on their desktop and they know how to use it. The native ability for users to link live CRM data to Excel will allow them to build sophisticated pivot tables and charts without learning to use another tool. The flexible layout capabilities in Excel give analytic users a dashboard that they can change frequently, carry with them and work with while disconnected.

Barry2

A highly interactive dashboard in Excel with live links back to CRM data

3. Capabilities: Your BI team has built data cubes for CRM data and has even combined the CRM cube with data from other sources. Your organization has the MOSS Enterprise CAL.
Dashboard Solution: Use Performance Point Server. PPS is now part of the MOSS Enterprise CAL. To take the fullest advantage of it you’ll want to work with OLAP data (though you can use PPS to embed reports into a dashboard). The scorecarding capabilities are great, data from multiple sources can be displayed and the ease of access to SharePoint based dashboards makes this the best solution for organizations that need to get dashboard data in front of managers and executives who won’t start up a business application to get their dashboard.

Barry3

A Performance Point Server dashboard inside of Microsoft Dynamics CRM

None of this guarantees that you won’t ever have to buy another BI tool again – in some cases you should take a look at new BI tools like the awesome Zap Technology cube builder for Microsoft Dynamics CRM or Powerobjects’ slick dashboard tools. The real point is that with BI projects, just as with the CRM implementation itself, success will come when you match the tools to the technical and social environment in which they are being deployed. I’ve described three ways to deliver dashboards for Microsoft Dynamics CRM but the more important information is how you decide which of those tools to use.

 

 

 

Cheers,

Barry Givens

Microsoft Dynamics CRM IFRAME Magic

April 16, 2009 Leave a comment

Microsoft Dynamics CRM allows you to access external web sites from within forms using IFRAMES (Inline Frames). You can configure the IFRAME to take information from the parent record. A pretty standard use of this is a Web tab on the Account form where the Account’s web site URL is passed to the IFRAME and the web site is available from a Web tab.

larry1

To do this, one sets up a little code for the form On Load event that puts the Accounts web site URL into the IFRAME’s URL. It’s actually pretty simple. Here is the code (assumes you already know how to set up an IFRAME). IFRAME_WebSite is the name of the IFRAME and .src references the URL it uses.

// Load web site URL
{
var AccountURL = crmForm.all.websiteurl.DataValue;
if (AccountURL != null)
{
crmForm.all.IFRAME_WebSite.src = AccountURL;
}
}

Having done this, I thought it would be neat to add a tab to display a map of the Account’s location. I used the same process except I stuffed address fields into the IFRAME URL instead of the web site. The code I used is:

// Load Map URL
{
var AccountStreet = crmForm.all.address1_line1.DataValue;
var AccountCity = crmForm.all.address1_city.DataValue;
var AccountState = crmForm.all.address1_stateorprovince.DataValue;
var AccountZip = crmForm.all.address1_postalcode.DataValue;

var MapURL = “http://maps.msn.com/home.aspx?strt1=” + AccountStreet + “&city1=”+ AccountCity+”&stnm1=” + AccountState + “&zipc1=” + AccountZip + “&cnty1=0”;

if (MapURL != null)
   {
      crmForm.all.IFRAME_Map.src = MapURL;
   }
}

The code is really pretty simple as you can see. But the results are pretty neat.

larry2

And the Live Search Maps give you different views such as Bird’s Eye which I love:

Larry3

There are lots of things you can do with IFRAMEs. Hopefully this will get your imagination going.

Cheers,

Larry Lentz

Announcing the Microsoft Dynamics CRM Dev Toolkit

April 14, 2009 Leave a comment

The Microsoft Dynamics CRM team is proud to announce the availability of The Microsoft Dynamics CRM Developer Toolkit. This Visual Studio 2008 plug-in radically expands on the Visual Studio tools introduced in the Microsoft Dynamics CRM 4.0 SDK. The toolkit enables developers and system customizers to take advantage of the tools and knowledge that they already have when engaging in custom development with Microsoft Dynamics CRM 4.0.

With the toolkit, implementers and managers of Microsoft Dynamics CRM solutions can manage schema, engage in form design, update security roles and generate wrapper classes for Microsoft Dynamics CRM. These capabilities will enhance the productivity of organizations, particularly those building and deploying XRM solutions – line of business applications that use the Microsoft Dynamics CRM platform.

Corey O’Brien, whom some of you may know from his work as Development Manager at Sonoma Partners or from his work on the MS Press title Programming Microsoft Dynamics CRM 4.0 told me that “whether you are a reseller of Microsoft Dynamics CRM working on behalf of clients or an IT organization managing the Microsoft Dynamics CRM solution, the toolkit is a great option for helping you get up to speed fast in the management of an XRM application. The toolkit opened our eyes to a new level of CRM integration.”

The Developer Toolkit makes it easy for teams of developers to customize, extend, and maintain an on-premise CRM solution, all from within an integrated Visual Studio 2008 environment. When building a solution with Microsoft Dynamics CRM developers typically design and iterate on an entity model, create custom security roles for accessing those entities and then may generate .Net assemblies to extend the business logic. Once the solution has been developed customizations need to be deployed to test and production environments. With the Microsoft Dynamics CRM Developer Toolkit developers and IT professionals can manage all of those tasks from within Visual Studio 2008.

Users of the Microsoft Dynamics CRM Developer Toolkit can:

View CRM entities using the CRM Explorer

The list of entities is dynamically available from the CRM Explorer within Visual Studio 2008.

b1

Create and Update CRM Entities

The Toolkit allows developers and system customizers to create new entities and update existing entities from within the CRM Explorer experience.

b2

Create Wrapper Classes

A key Toolkit feature is the ability to auto-generate wrapper classes for entities. This exposes the CRM entities and their corresponding attributes as classes and properties respectively to enable development of code to interact with the entities.

b3

Generate Plug-in Code

The CRM Explorer enumerates the available Plug-ins for an entity and generates the code necessary to jumpstart the plug-in development process.

b4

Integrate the Build and Deploy Process

Build scripts provided with the Toolkit simplify the process of building and deploying a CRM solution. They prepare the environment, obtain and publish the latest customizations, perform code analysis, register CRM Plug-ins, deploy the solution and execute Unit Test cases.

Deploy Across Multiple Servers

The deployment tool assists in deployment and maintenance of Windows installer packages across multiple environments.

b5

The Microsoft Dynamics CRM Developer Toolkit is available free of charge from the MSDN Code Galleries at http://code.msdn.microsoft.com/E2DevTkt .

The Microsoft Dynamics CRM team would like to encourage Toolkit users to submit comments and suggestions for the Toolkit. To initiate or participate in Toolkit discussions, go to the MSDN Code Gallery site and click on the Discussions tab at http://code.msdn.microsoft.com/E2DevTkt/Thread/List.aspx .

Cheers,

Barry Givens

Microsoft Connect: One Stop Suggestion Depot

April 14, 2009 Leave a comment

connect

Have a feature/suggestion for the engineering team of Microsoft Dynamics CRM?  Well, the “official” channel to do so is Microsoft Connect located at https://connect.microsoft.com/dynamics.

If you wonder if people from the engineering team actually reads your suggestions I can tell you with a 100% certainty that we do. <smile> I’m personally involved in our “Customer Suggestion Triage” along with dozens of other people and we look at every single suggestion that we receive.  We consider most of the feedback either for the next or subsequent releases and implement as much as we can with every release.

         searchNow                CreateNew

Want to increase your chances to get your suggestion implemented? Help us make it happen!! 

  • Vote for existing suggestions.  I would be lying if I don’t tell you that we tend to pay more attention to suggestions that have better ratings/votes/comments. :o)  Instead of logging a new suggestion, and provided that you can identify an existing one that covers your needs, please vote for it! The more people need the feature the better the chances we will make it happen.
  • Provide enough “Actionable” information; usually a picture is worth a thousand words so if you can attach a picture or a diagram it definitively helps us understand your idea. Also, try to describe your “scenario”… what is it that you are trying to accomplish that the product doesn’t currently do or could do better?
  • If something is “crashing” in your system or you are getting incorrect information/functionality, chances are you are looking at a bug (and we are really sorry about that) and not a feature request (aka suggestion). While we absolutely appreciate your feedback, to better address bugs with current versions of our product (CRM 4.0 or previous) our Support team needs to be involved to properly diagnose your situation and there is no better way to do that than opening a support request instead of logging a suggestion.
    • Also if you need additional information/community support our forums are a great place to get free support. There are tons of people monitoring those forums and you will probably get a quick answer to your inquiry. :o)

You speak, we listen!

Cheers,

Humberto Lezama Guadarrama

CRM Usage Reporting Unleashed

A frequent request we come across is from companies who want to know which users are using CRM and when. The CRM platform provides the facility to gather detailed usage information by writing plug-ins, but a simpler and more general mechanism is to use the Internet Information Services (IIS) logging mechanism.

This article will explain how to derive useful usage metrics from the information provided by IIS logging. The main steps are:

1. Configure IIS logging to allow easy querying of the log data

2. Filter the raw log data to help identify usage patterns

3. Specify time periods to help categorize when people access CRM

4. Present the usage information in a graphical format through reports

Configure IIS logging

IIS logging allow the capture of information about every web request submitted to the server, and it is configured at the web site level. By default the log information is written to text files, but IIS can also write the information directly to a relational database, which allows easier analysis. For the purposes of this post, the simplest setup is to configure IIS 6.0 to write the log data to a SQL Server ODBC data source (links to other options are given below).

To configure an IIS 6.0 server to write log information for the CRM web site:

1. Create a SQL Database. In this database, create a SQL table to hold the log data. IIS has a SQL script that will create the table with appropriate columns – this script will be in %windir%\system32\inetsrv\logtemp.sql

2. Create an ODBC data source on the IIS server with the connection details to the database in step 1. The data source must be a system DSN, and I’d recommend using Integrated authentication to SQL Server

3. In IIS Manager, open the Properties of the Microsoft Dynamics CRM web site. On the Web Site tab, select ODBC Logging in the Active log format dropdown, and set the associated properties to reflect the SQL table and ODBC DSN created in steps 1 and 2. If you specified Integrated authentication in the DSN, then the User name and Password will be ignored, though oddly, you cannot leave the password field blank

Related links:

Filtering the log data

The IIS log will have a record for every request submitted to the web site. This includes requests for images, stylesheets and other supporting files, and can result in more data than you need . For instance, opening a CRM record can result in 30 records in the log table. There are also entries for CRM processes (such as the CRM Asynchronous Service) accessing the CRM platform.

This extraneous data can be filtered out with a SQL view. We use the following view definition:

CREATE view [dbo].[IISLogFiltered]
as
select ClientHost, username, LogTime, processingtime, bytesrecvd, bytessent, servicestatus, operation, target, parameters
from inetlog
where username not in (‘-‘, ‘CRMTEST\CRM4DEV$’)
and parameters <> ‘-‘ and parameters not like ‘lcid=%’

— Replace CRMTEST\CRM4DEV$ with the account used by the CRM services

This assumes the SQL table has the default name (‘inetlog’), and filters out anonymous requests, and those from the CRM services. It also filters out requests with no query string parameters (or where the only parameter is ‘lcid’), which excludes the requests for the supporting files. You can explore the log data in more detail, and adjust the filters as appropriate.

Specifying time periods to categorize access

Even with the SQL view described above, you are still a step away from getting useful information about when users access CRM. The raw data stores the date and time of each request in one field, but it will help to process this into time and date periods. The approach we use is to build a SQL table that contains definable time periods , and a SQL function to match the log time to a time period. The SQL table can be created with the following script:

CREATE TABLE [dbo].[TimePeriod](
[PeriodText] [nvarchar](20) NULL,
[HourStart] [int] NULL,
[MinuteStart] [int] NULL,
[TotalMinuteStart] AS ((60)*[HourStart]+[MinuteStart]),
[TotalMinuteEnd] [int] NULL)

You can then populate it with data for the time periods you want, for example:

PeriodText

HourStart

MinuteStart

TotalMinuteStart

TotalMinuteEnd

00:00 – 08:00

0

0

0

480

08:00 – 09:00

8

0

480

540

09:00 – 10:00

9

0

540

600

10:00 – 11:00

10

0

600

660

11:00 – 12:00

11

0

660

720

12:00 – 13:00

12

0

720

780

13:00 – 14:00

13

0

780

840

14:00 – 15:00

14

0

840

900

15:00 – 16:00

15

0

900

960

16:00 – 17:00

16

0

960

1020

17:00 – 18:00

17

0

1020

1080

18:00 – 00:00

18

0

1080

1440

This splits the day into hour-long periods between 8:00 and 18:00, with one period before and after the hour-long periods. The table can be used to find the PeriodText from a datetime field using a SQL function:

CREATE function GetTimePeriodText (@DateTime datetime) returns nvarchar(20)
as
begin
declare @tm int, @ret nvarchar(20)
set @tm = 60 * datepart(hh, @DateTime) + datepart(mi, @DateTime)
select @ret = max(PeriodText) from TimePeriod where @tm >= TotalMinuteStart and @tm < TotalMinuteEnd
return @ret
end

It will also help to extract the date component, this can be done using the SQL Convert function. The following SQL view applies these functions to the above SQL view used for filtering. It also extracts the CRM organization name from the target (url), and removes the domain part of the username.

Create view IISLogFilteredWithPeriods
as
select ClientHost, username, LogTime, processingtime, bytesrecvd, bytessent, servicestatus, operation, target, parameters
, dbo.GetTimePeriodText(LogTime) as TimePeriod
, convert(nchar(10), LogTime, 103) as DateText — The last parameter defines the format
, convert(nchar(8), LogTime, 112) as DateYYYYMMDD — Useful for sorting dates
, case when charindex(‘/’, target, 2) > 2 then substring(target, 2, charindex(‘/’, target, 2) – 2) else ” end as Organization — Get organization name from target
, rtrim(substring(username, 1 + charindex(‘\’, username), len(username) – 1 + charindex(‘\’, username))) as usernameonly — Remove Domain part of user name
from IISLogFiltered

Note that the IIS log will store data in Universal Time (UTC). You can modify either the data in the TmePeriod table, or the logic in the GetTimePeriodText function, to apply timezone information. Another, though officially unsupported approach, would be to use the fn_UTCToLocalTime SQL function in the MSCRM database.

Creating reports on the log data

Now we’ve got the structure to process the log data, we can present in to users via a SQL Server Reporting Services report. The layout is up to you, but we find an effective way to present the information is in a matrix, with the time periods along the columns, with conditional formatting to highlight periods of light or heavy use. The following is an example, using the report definition below:

      clip_image002

The numbers are the count of requests, with the background colours indicating how heavy the use is (as it’s a test system I’m considering 5 or more requests per hour to be heavy usage).

The SQL statement that produced this report is:

select usernameonly as username
, TimePeriod, DateText, DateYYYYMMDD
, sum(processingtime) as ProcessingTime, count(*) as Requests
from IISLogFilteredWithPeriods
where logtime > dateadd(d, -7, getdate())
group by usernameonly, TimePeriod, DateText, DateYYYYMMDD

For performance reasons, and out of habit, the aggregation is done in the SQL query, rather than in reporting services.

The full report definition follows at the end of this post.

Further thoughts – performance

This post focused on providing an overall process for getting usage information from the IIS logs. There are some performance overheads when using ODBC logging – it is more processor intensive that logging to file or raw formats, and it affects the IIS caching mechanism.

An alternative approach is to log to raw or file formats, and either import the data periodically to SQL Server (see http://support.microsoft.com/kb/296093/ and http://support.microsoft.com/kb/296085/ ), or process it with other tools, such as the LogParser tool (http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en)

You should also consider archiving or deleting old log data. One heavy user of CRM can easily generate over a megabyte of log data per day, so the quantity of data can mount up quickly.

If logging directly to SQL Server, there is a trade-off to consider with respect to SQL indexes. Adding a non-clustered index on the parameters column may help with the analysis performance, but would adversely affect the logging performance. A clustered index on the logtime column is recommended for all circumstances.

Further thoughts – more analysis

The report provided in this post is limited to displaying the number of request per user. Further ideas for analysis are:

  • Using the processingtime data to get an idea of the processing on the IIS server. This is a measure of the elapsed time from starting processing the request in IIS, to submitting the end of the response, and will be affected by the load on the IIS server. This can be useful for identifying when and if requests take a long time to complete
  • Identifying the types of entities accessed. The target can help identify which entity has been requested. This takes a bit of parsing, but the topic ‘URL Addressable Forms and Views’ (http://msdn.microsoft.com/en-gb/library/cc150850.aspx) in the CRM SDK will help
  • IIS logs are not useful for identifying the type of data operation (e.g. create, update, delete, assign) has been performed. If you need this information, you would be advised to use a plugin/callout mechanism for the auditing – see http://blogs.msdn.com/crm/archive/2006/12/05/creating-solutions-a-custom-crm-usage-log.aspx for an example of this
  • The techniques described in this post were developed for CRM 4, but also work on CRM 3. The only difference is that there is no need to extract the Organization name from the target on CRM 3

Source files

The SQL objects, and report RDL described in this post are available for download on the MSDN Code Gallery here (http://code.msdn.microsoft.com/IISLoggingCRM). I’d welcome any comments there, and I can add people as contributors to the code gallery resource if they have further reports or SQL objects they are willing to share.

Full report definition

This is a SQL 2005 Reporting Services report. The Data Source will need to be changed for your environment:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
   3:   <DataSources>
   4:     <DataSource Name="IISLog">
   5:       <rd:DataSourceID>c04397fa-f432-4983-907e-3533dc2dea9c</rd:DataSourceID>
   6:       <ConnectionProperties>
   7:         <DataProvider>SQL</DataProvider>
   8:         <ConnectString>Data Source=crm4dev;Initial Catalog=IISLog</ConnectString>
   9:         <IntegratedSecurity>true</IntegratedSecurity>
  10:       </ConnectionProperties>
  11:     </DataSource>
  12:   </DataSources>
  13:   <InteractiveHeight>11in</InteractiveHeight>
  14:   <rd:DrawGrid>true</rd:DrawGrid>
  15:   <InteractiveWidth>8.5in</InteractiveWidth>
  16:   <rd:GridSpacing>0.25cm</rd:GridSpacing>
  17:   <rd:SnapToGrid>true</rd:SnapToGrid>
  18:   <RightMargin>2.5cm</RightMargin>
  19:   <LeftMargin>2.5cm</LeftMargin>
  20:   <BottomMargin>2.5cm</BottomMargin>
  21:   <rd:ReportID>c13a6342-b76c-4be0-8d0f-8598eac7fa80</rd:ReportID>
  22:   <PageWidth>21cm</PageWidth>
  23:   <DataSets>
  24:     <DataSet Name="IISLog">
  25:       <Fields>
  26:         <Field Name="username">
  27:           <DataField>username</DataField>
  28:           <rd:TypeName>System.String</rd:TypeName>
  29:         </Field>
  30:         <Field Name="TimePeriod">
  31:           <DataField>TimePeriod</DataField>
  32:           <rd:TypeName>System.String</rd:TypeName>
  33:         </Field>
  34:         <Field Name="DateText">
  35:           <DataField>DateText</DataField>
  36:           <rd:TypeName>System.String</rd:TypeName>
  37:         </Field>
  38:         <Field Name="DateYYYYMMDD">
  39:           <DataField>DateYYYYMMDD</DataField>
  40:           <rd:TypeName>System.String</rd:TypeName>
  41:         </Field>
  42:         <Field Name="ProcessingTime">
  43:           <DataField>ProcessingTime</DataField>
  44:           <rd:TypeName>System.Int32</rd:TypeName>
  45:         </Field>
  46:         <Field Name="Requests">
  47:           <DataField>Requests</DataField>
  48:           <rd:TypeName>System.Int32</rd:TypeName>
  49:         </Field>
  50:       </Fields>
  51:       <Query>
  52:         <DataSourceName>IISLog</DataSourceName>
  53:         <CommandText>select usernameonly as username, TimePeriod, DateText, DateYYYYMMDD
  54: , sum(processingtime) as ProcessingTime, count(*) as Requests
  55: from IISLogFilteredWithPeriods
  56: where logtime &gt; dateadd(d, -7, getdate())
  57: group by usernameonly, TimePeriod, DateText, DateYYYYMMDD</CommandText>
  58:         <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  59:       </Query>
  60:     </DataSet>
  61:   </DataSets>
  62:   <Width>16.5cm</Width>
  63:   <Body>
  64:     <ColumnSpacing>1cm</ColumnSpacing>
  65:     <ReportItems>
  66:       <Textbox Name="textbox1">
  67:         <rd:DefaultName>textbox1</rd:DefaultName>
  68:         <Style>
  69:           <Color>SteelBlue</Color>
  70:           <FontFamily>Tahoma</FontFamily>
  71:           <FontSize>20pt</FontSize>
  72:           <FontWeight>700</FontWeight>
  73:           <PaddingLeft>2pt</PaddingLeft>
  74:           <PaddingRight>2pt</PaddingRight>
  75:           <PaddingTop>2pt</PaddingTop>
  76:           <PaddingBottom>2pt</PaddingBottom>
  77:         </Style>
  78:         <ZIndex>1</ZIndex>
  79:         <CanGrow>true</CanGrow>
  80:         <Height>0.91429cm</Height>
  81:         <Value>Log By Date And User - Last 7 days</Value>
  82:       </Textbox>
  83:       <Matrix Name="matrix1">
  84:         <MatrixColumns>
  85:           <MatrixColumn>
  86:             <Width>1.35cm</Width>
  87:           </MatrixColumn>
  88:         </MatrixColumns>
  89:         <DataSetName>IISLog</DataSetName>
  90:         <RowGroupings>
  91:           <RowGrouping>
  92:             <Width>2.53968cm</Width>
  93:             <DynamicRows>
  94:               <Grouping Name="matrix1_DateText">
  95:                 <GroupExpressions>
  96:                   <GroupExpression>=Fields!DateText.Value</GroupExpression>
  97:                 </GroupExpressions>
  98:               </Grouping>
  99:               <Sorting>
 100:                 <SortBy>
 101:                   <SortExpression>=Fields!DateYYYYMMDD.Value</SortExpression>
 102:                   <Direction>Descending</Direction>
 103:                 </SortBy>
 104:               </Sorting>
 105:               <Subtotal>
 106:                 <ReportItems>
 107:                   <Textbox Name="textbox5">
 108:                     <rd:DefaultName>textbox5</rd:DefaultName>
 109:                     <Style>
 110:                       <Color>White</Color>
 111:                       <BackgroundColor>#6e9eca</BackgroundColor>
 112:                       <BorderColor>
 113:                         <Default>LightGrey</Default>
 114:                       </BorderColor>
 115:                       <BorderStyle>
 116:                         <Default>Solid</Default>
 117:                       </BorderStyle>
 118:                       <FontFamily>Tahoma</FontFamily>
 119:                       <FontWeight>700</FontWeight>
 120:                       <PaddingLeft>2pt</PaddingLeft>
 121:                       <PaddingRight>2pt</PaddingRight>
 122:                       <PaddingTop>2pt</PaddingTop>
 123:                       <PaddingBottom>2pt</PaddingBottom>
 124:                     </Style>
 125:                     <CanGrow>true</CanGrow>
 126:                     <Value>Total</Value>
 127:                   </Textbox>
 128:                 </ReportItems>
 129:               </Subtotal>
 130:               <ReportItems>
 131:                 <Textbox Name="DateText">
 132:                   <rd:DefaultName>DateText</rd:DefaultName>
 133:                   <Style>
 134:                     <Color>White</Color>
 135:                     <BackgroundColor>#6e9eca</BackgroundColor>
 136:                     <BorderColor>
 137:                       <Default>LightGrey</Default>
 138:                     </BorderColor>
 139:                     <BorderStyle>
 140:                       <Default>Solid</Default>
 141:                     </BorderStyle>
 142:                     <FontFamily>Tahoma</FontFamily>
 143:                     <FontWeight>700</FontWeight>
 144:                     <PaddingLeft>2pt</PaddingLeft>
 145:                     <PaddingRight>2pt</PaddingRight>
 146:                     <PaddingTop>2pt</PaddingTop>
 147:                     <PaddingBottom>2pt</PaddingBottom>
 148:                   </Style>
 149:                   <ZIndex>3</ZIndex>
 150:                   <CanGrow>true</CanGrow>
 151:                   <Value>=Fields!DateText.Value</Value>
 152:                 </Textbox>
 153:               </ReportItems>
 154:             </DynamicRows>
 155:           </RowGrouping>
 156:           <RowGrouping>
 157:             <Width>4.5cm</Width>
 158:             <DynamicRows>
 159:               <Grouping Name="matrix1_username">
 160:                 <GroupExpressions>
 161:                   <GroupExpression>=Fields!username.Value</GroupExpression>
 162:                 </GroupExpressions>
 163:               </Grouping>
 164:               <Sorting>
 165:                 <SortBy>
 166:                   <SortExpression>=Fields!username.Value</SortExpression>
 167:                   <Direction>Ascending</Direction>
 168:                 </SortBy>
 169:               </Sorting>
 170:               <ReportItems>
 171:                 <Textbox Name="username">
 172:                   <rd:DefaultName>username</rd:DefaultName>
 173:                   <Style>
 174:                     <Color>White</Color>
 175:                     <BackgroundColor>SlateGray</BackgroundColor>
 176:                     <BorderColor>
 177:                       <Default>LightGrey</Default>
 178:                     </BorderColor>
 179:                     <BorderStyle>
 180:                       <Default>Solid</Default>
 181:                     </BorderStyle>
 182:                     <FontFamily>Tahoma</FontFamily>
 183:                     <FontWeight>700</FontWeight>
 184:                     <PaddingLeft>2pt</PaddingLeft>
 185:                     <PaddingRight>2pt</PaddingRight>
 186:                     <PaddingTop>2pt</PaddingTop>
 187:                     <PaddingBottom>2pt</PaddingBottom>
 188:                   </Style>
 189:                   <ZIndex>2</ZIndex>
 190:                   <CanGrow>true</CanGrow>
 191:                   <Value>=Fields!username.Value</Value>
 192:                 </Textbox>
 193:               </ReportItems>
 194:             </DynamicRows>
 195:           </RowGrouping>
 196:         </RowGroupings>
 197:         <Top>0.91429cm</Top>
 198:         <ColumnGroupings>
 199:           <ColumnGrouping>
 200:             <Height>0.53333cm</Height>
 201:             <DynamicColumns>
 202:               <Grouping Name="matrix1_TimePeriod">
 203:                 <GroupExpressions>
 204:                   <GroupExpression>=Fields!TimePeriod.Value</GroupExpression>
 205:                 </GroupExpressions>
 206:               </Grouping>
 207:               <Sorting>
 208:                 <SortBy>
 209:                   <SortExpression>=Fields!TimePeriod.Value</SortExpression>
 210:                   <Direction>Ascending</Direction>
 211:                 </SortBy>
 212:               </Sorting>
 213:               <Subtotal>
 214:                 <ReportItems>
 215:                   <Textbox Name="textbox4">
 216:                     <rd:DefaultName>textbox4</rd:DefaultName>
 217:                     <Style>
 218:                       <Color>White</Color>
 219:                       <BackgroundColor>#6e9eca</BackgroundColor>
 220:                       <BorderColor>
 221:                         <Default>LightGrey</Default>
 222:                       </BorderColor>
 223:                       <BorderStyle>
 224:                         <Default>Solid</Default>
 225:                       </BorderStyle>
 226:                       <FontFamily>Tahoma</FontFamily>
 227:                       <FontWeight>700</FontWeight>
 228:                       <PaddingLeft>2pt</PaddingLeft>
 229:                       <PaddingRight>2pt</PaddingRight>
 230:                       <PaddingTop>2pt</PaddingTop>
 231:                       <PaddingBottom>2pt</PaddingBottom>
 232:                     </Style>
 233:                     <ZIndex>4</ZIndex>
 234:                     <CanGrow>true</CanGrow>
 235:                     <Value>Total</Value>
 236:                   </Textbox>
 237:                 </ReportItems>
 238:               </Subtotal>
 239:               <ReportItems>
 240:                 <Textbox Name="TimePeriod">
 241:                   <rd:DefaultName>TimePeriod</rd:DefaultName>
 242:                   <Style>
 243:                     <Color>White</Color>
 244:                     <BackgroundColor>#6e9eca</BackgroundColor>
 245:                     <BorderColor>
 246:                       <Default>LightGrey</Default>
 247:                     </BorderColor>
 248:                     <BorderStyle>
 249:                       <Default>Solid</Default>
 250:                     </BorderStyle>
 251:                     <FontFamily>Tahoma</FontFamily>
 252:                     <FontWeight>700</FontWeight>
 253:                     <PaddingLeft>2pt</PaddingLeft>
 254:                     <PaddingRight>2pt</PaddingRight>
 255:                     <PaddingTop>2pt</PaddingTop>
 256:                     <PaddingBottom>2pt</PaddingBottom>
 257:                   </Style>
 258:                   <ZIndex>5</ZIndex>
 259:                   <CanGrow>true</CanGrow>
 260:                   <Value>=Fields!TimePeriod.Value</Value>
 261:                 </Textbox>
 262:               </ReportItems>
 263:             </DynamicColumns>
 264:           </ColumnGrouping>
 265:         </ColumnGroupings>
 266:         <Width>9.73968cm</Width>
 267:         <Corner>
 268:           <ReportItems>
 269:             <Textbox Name="textbox3">
 270:               <rd:DefaultName>textbox3</rd:DefaultName>
 271:               <Style>
 272:                 <BorderColor>
 273:                   <Default>LightGrey</Default>
 274:                 </BorderColor>
 275:                 <BorderStyle>
 276:                   <Default>Solid</Default>
 277:                 </BorderStyle>
 278:                 <FontFamily>Tahoma</FontFamily>
 279:                 <PaddingLeft>2pt</PaddingLeft>
 280:                 <PaddingRight>2pt</PaddingRight>
 281:                 <PaddingTop>2pt</PaddingTop>
 282:                 <PaddingBottom>2pt</PaddingBottom>
 283:               </Style>
 284:               <ZIndex>6</ZIndex>
 285:               <CanGrow>true</CanGrow>
 286:               <Value>
 287:               </Value>
 288:             </Textbox>
 289:           </ReportItems>
 290:         </Corner>
 291:         <MatrixRows>
 292:           <MatrixRow>
 293:             <Height>0.53333cm</Height>
 294:             <MatrixCells>
 295:               <MatrixCell>
 296:                 <ReportItems>
 297:                   <Textbox Name="textbox2">
 298:                     <rd:DefaultName>textbox2</rd:DefaultName>
 299:                     <Style>
 300:                       <BackgroundColor>=iif(Fields!Requests.Value &gt; 5, "LightSteelBlue", iif(Fields!Requests.Value &gt; 0, "AliceBlue", "White"))</BackgroundColor>
 301:                       <BorderColor>
 302:                         <Default>LightGrey</Default>
 303:                       </BorderColor>
 304:                       <BorderStyle>
 305:                         <Default>Solid</Default>
 306:                       </BorderStyle>
 307:                       <FontFamily>Tahoma</FontFamily>
 308:                       <PaddingLeft>2pt</PaddingLeft>
 309:                       <PaddingRight>2pt</PaddingRight>
 310:                       <PaddingTop>2pt</PaddingTop>
 311:                       <PaddingBottom>2pt</PaddingBottom>
 312:                     </Style>
 313:                     <ZIndex>1</ZIndex>
 314:                     <CanGrow>true</CanGrow>
 315:                     <Value>=Sum(Fields!Requests.Value)</Value>
 316:                   </Textbox>
 317:                 </ReportItems>
 318:               </MatrixCell>
 319:             </MatrixCells>
 320:           </MatrixRow>
 321:         </MatrixRows>
 322:       </Matrix>
 323:     </ReportItems>
 324:     <Height>2.51428cm</Height>
 325:   </Body>
 326:   <Language>en-US</Language>
 327:   <TopMargin>2.5cm</TopMargin>
 328:   <PageHeight>29.7cm</PageHeight>
 329: </Report>

 

Cheers,

David Jennaway