Create Calendar style reports in SQL Reporting Services

Sometimes it is easier to visualize the activity related information in a calendar view. Here are a few scenarios where Calendar style report provides a better view of information than a standard table or list.

- number of outbound telephone calls by salesperson or department in a telemarketing company

- billable and non-billable hours by consultant/department in a consulting company

- Internet sales on the e-Commerce web site

You can create a calendar style report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.

In this article, I will explain the steps required to create such a report from AdventureWorksDW database (available for download on Codeplex). I have also provided a link to download the report definition (RDL) file below.

Here are the key points to note for creating a calendar style report using SSRS.

1. Dates or Calendar table: We will use this table to include date, week number and day of the week information in the data set. This table contains all dates in each calendar year you will be reporting for. The AdventureWorksDW has the DimDate table that contains this information. If you do not have such as table in your database, you can create a temp table and use it in the query.

2. Outer Join: We will need to use the Outer Join between the dates table and the fact table so that all dates are included in the data set whether there is data or activity for that date or not.

3. Week Number: We will need this field to generate the matrix. This field has erroneous data in the AdventureWorksDW database. Therefore, I had to create a calculated field to calculate this value. Please see below for details.

4. Use Matrix to create the Calendar view. The Matrix has the WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. These fields are used only for grouping and should be hidden in the final report design. The WeekNumber can be any number from 1 through 53. The DayNumberOfWeek can be any number from 1 through 7 – 1 being Sunday and 7 Saturday.

5. The matrix can have from 4 to 6 rows depending on the number of weeks in the month and the day on which the month starts. It always has 7 columns – one for each day of the week.

The screen shot below shows the query used for the data set used in the matrix. Note the LEFT OUTER JOIN between DimDate and FactInternetSales tables.

As mentioned earlier, the week number field in the DimDate table in AdventureWorksDW has erroneous data. Therefore, I created a calculated field WeekNumber as follows:

=DatePart(DateInterval.WeekOfYear, CDate(Fields!FullDateAlternateKey.Value))

The following screen shot shows the report in the design mode. As you will notice, the matrix uses WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. EnglishDayNameOfWeek is used to display the days – Sunday through Saturday.

The following screen shot shows the sample output for the month of June 2007. This month has 5 rows representing 5 weeks and 7 columns representing Sunday through Saturday.

The following screen shot shows the sample output for the month of September 2007. Note that this month has 6 rows representing 6 weeks.

The report definition (RDL) file is available for download here.

Please also see the related post to display gauge indicator on the calendar style report.

Scribe Insight 7.0 supports multiple targets

Scribe Software recently announced the availability of Scribe Insight 7.0. One of the most important features of this release is the ability to connect to more than one target in the same DTS.

The blog post on Scribe's web site describes various scenarios where this feature can be valuable. These include:

1. If the source record was triggered via a publisher, then the update source feature is not available since the source for that DTS is a message queue and not the original application which triggered the integration.
2. The source system uses an adapter which does not support the update source feature (for example, Scribe adapter for Dynamics CRM).
3. The source query is a complex join which update source cannot support.
4. You need to be able to update multiple fields on the source record.

The scenarios 1 and 2 above are applicable in most closed-loop integrations between Dynamics CRM and another ERP system.

For example, because the previous release of Scribe Insight did not support multiple target connections in the same DTS, Dynamics GP to Dynamics CRM template has to use two separate DTSs for CRM to GP Sales Order integration as follows:

1. OrderToERP.dts: Sends submitted orders from Dynamics CRM to Dynamics GP
2. OrderToERP_Result.dts: Updates the Sales Order in Dynamics CRM based on the result of the OrderToERP.dts

Now, with Scribe Insight 7.0, you can combine both of the above steps in one DTS using both Dynamics GP and Dynamics CRM as targets.

This new feature will be very valuable for addressing many Dynamics CRM integration needs including the one above.

Create Top 10 Products Charts and show them inside Account form

In this article I will walk you through the steps to create the Top 10 Products charts and integrate them with the Account form via IFrame.

1. Create a SQL Server Reporting Services report containing the charts. You can download the report using this link. Here are a few things to keep in mind. Because we will be passing the object type code and unique identifier as parameters to the report via IFrame, we need to add those parameters to the report. In addition, I have added the CRM_URL parameter to the report so that a user can click on the bar in the chart to view the selected product in CRM. Please download the report (.rdl) file below for details.

2. Upload the report to the Reports area in Dynamics CRM.

3. Publish the report for external use.

4. Add a tab to the Account form.

5. Add a section on the newly created tab.

 

6. Add the IFrame to the newly created section as below.

Use the following URL to open the published report from the Report server. Note the rc:Toolbar=false parameter to hide the toolbar. Use the appropriate URL to reflect your server name and report name.

http://crm/ReportServer?%2fcontoso_MSCRM%2fTop+10+Products+Charts&rs%3aCommand=Render&rc:Toolbar=false

7. Save the form changes and publish your customizations.

8. Open the Account to test your charts. (Note: You may need to create some invoices to test the charts).

Cick on one of the bars on the first chart. You should see the Product form for the product you clicked. You can run the other product related reports from here for further analysis.

Download the report file here.

Key Performance Indicators (KPIs)

In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business performance. For example, a retail business may use “Year over year revenue growth” as a KPI to gauge its performance. KPIs are typically shown as part of a dashboard or business scorecard to provide managers and analysts with at-a-glance picture of the state of business.

Technically KPIs may be implemented using any tool or technology. For example, you may be able to create a dashboard using Excel or SQL Server Reporting Services by retrieving the relevant data from a relational database or even entering the data manually into the spreadsheet and creating necessary calculations or formulas based on the data. However, this approach relies on the KPIs being created consistently by different users and developers, which may not be the case.

There are several advantages of using SQL Server Analysis Services to implement KPIs. First, it enables you to define a KPI once and use it in multiple dashboards and reports in multiple contexts. Second, it ensures that the users see the single version of the truth without you having to rely on the users and developers for the correct calculation.

Let us see how it would work using an example. You can create the “Internet Sales Growth” KPI using the following MDX expression in SSAS:

IIF(
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],
ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember)), [Measures].[Internet Sales Amount]
)=0,
"NA",
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],
ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember)), [Measures].[Internet Sales Amount]
) - 1
)

The above expression takes advantage of the MDX function ParallelPeriod to get a member from a prior period in the same relative position as the current member (month, quarter, semester, etc.). For example, a parallel period for FY 2004 Q3 is FY 2003 Q3, which will be used to calculate the year over year sales growth.

Once you have created a calculated measure and a KPI based on this calculation, developers and end users can use it in any spreadsheet, report or dashboard in any context. SSAS calculates the KPI automatically as the users slice and dice the data.

For example, users can use this KPI in any of the following:
- report showing year over year sales growth by territory that displays years across the columns and sales territories down the rows
- dashboard showing querterly sales and sales growth over the same quarter in the previous year by product category
- Excel PivotTable showing top 10 customers by sales that also includes sales growth rates for those customers. When the user slices the data further by product, SSAS automatically calculates the sales growth by customer and product.

Create browser based Dashboards using Excel Services

In an earlier post, I discussed how you can create free form Excel reports using the cube functions in Excel 2007. This is a great way to create reports, charts, KPIs (Key Performance Indicators) and dashboards using the tool you are already familiar with.

However, you may want to make these reports and dashboards available to a wider audience via a Web browser. You want to enable users to filter the worksheets and charts to suit their needs. You also want to prevent your master Excel workbook from being edited by certain users while allowing only specific users to edit the master workbook. What is the solution?

Welcome to Excel Services! Excel Services is an interactive Web view for spreadsheets - a way for people to see data in their Web browser instead of using Excel. In fact, to look at the report or dashboard in Excel Services, users do not even need Excel installed. However, if they do have Excel installed, they can use the “Open Snapshot in Excel” feature of Excel Services to open the limited version of the workbook data in Excel and use the standard Excel features such as auto sum, formulas and charts for further analysis. However, this does not change the master version of the workbook saved in Excel Services.

In order to make your Excel workbook available in Excel Services, you publish your Excel workbook to Excel Services. To publish workbooks to Excel Services, you need:

• Excel Services set up with SharePoint Server 2007 Enterprise.
• A SharePoint site based on SharePoint Server 2007 Enterprise. This is where you can save your workbooks, and where others can see your workbook data by using their Web browsers.
• Excel 2007 installed on computers through one of the following: Office Professional Plus 2007, Office Enterprise 2007, or Office Ultimate 2007.

Again, to look at a worksheet in Excel Services, users don't need Excel installed at all. All they need is a Web browser.

If you would like to learn how to set up Excel Services and publish an Excel workbook to Excel Services, there is a good tutorial available here.

Create Calendar View in SQL Reporting Services

Note: I have posted an updated article on this topic here.

Sometimes it is easier to visualize the activity related information in a calendar view. For example, you may want to view the number of telephone calls and emails each day by department and/or salesperson during the month in a calendar view (rather than as a tabular report).

You can create a calendar view report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.

You would first create a temporary table with a row for each day of the month. Each row has the following columns:

Day of the month: Use this to display the date in each cell of the matrix.

Week of the month: Use this as a row header of the matrix (you can hide this field in the actual report because it is not needed for the calendar view)

Day of the week: Use this as a column header of the matrix.

Activity count (or whatever data you want to aggregate for each day): Use this as a cell data element of the matrix.

You can calculate the day of the week and week of the month in SQL as follows:
DECLARE @d_date DATETIME,
@d_dow SMALLINT,
@d_weeknum SMALLINT,
@d_dow1 SMALLINT

SET @d_dow = DATEPART(dw, @d_date)
By default Sunday is the first day of the week.

SET @d_date = @mnthbeg_date
SET @d_dow1 = DATEPART(dw, @d_date)
SET @d_weeknum = ((@d_dow1 + DATEPART(d, @d_date) - 2) / 7) + 1
As you will notice from the above SQL statements, @d_dow1 is the day (Sunday through Saturday) on which the first day of the month falls.
Once you have this temporary table populated, you can simply select it and use it as a dataset for the matrix.

You can further enhance the report using the following default filters:

Month: Default to current month using =Month(Now)

Year: Default to current year using =Year(Now)

Salesperson/User: You can populate this list with the dataset from SystemUser filtered view. You can also default the user to the current CRM user using the CRM function dbo.fn_FindUserGuid(). If you are creating this report for a non-CRM application, you can simply use the User!UserID global variable to get the current Windows user.
In addition, you can create a drill-down report that displays the details of activities when the user clicks on a specific date in the report.

Create re-usable Dynamic PivotTable Reports for Dynamics CRM

As you are probably aware, you can export any list in Dynamics CRM 4.0 as a dynamic Excel PivotTable. You have an option to select the columns you want to include in the PivotTable. Once you have exported the list as a dynamic PivotTable, you can then slice and dice the data for further analysis and save the PivotTable report for the future use.

You can open the saved Excel workbook at a later time and refresh it (Data > Refresh from CRM or Refresh All) to update the PivotTable to reflect the up-to-date information from Dynamics CRM 4.0.

If you want to make this PivotTable report available to the other CRM users, you can upload the Excel workbook containing your report to Dynamics CRM. You do this using the following steps:

1. Workplace > Reports > New
2. Report Type: Existing File
3. Browse to the folder where you saved your Excel workbook and select the workbook file
4. Save and Close

Your Excel report containing the dynamic PivotTable is now available to the other users (of course subject to their privileges set in CRM). For example, if you create a PivotTable report containing opportunities by salesperson and territory, the user viewing this report would see only the opportunities he/she can otherwise see in CRM.