Reporting Based on SSRS

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics AX 2012 Reporting Cookbook

Microsoft Dynamics AX 2012 Reporting Cookbook — Save 50%

Over 50 recipes to help you build Dynamics AX reports faster by simplifying your understanding of the report model with this book and ebook

$29.99    $15.00
by Kamalakannan Elangovan | September 2013 | Cookbooks Enterprise Articles

In this article, by Kamalakannan Elangovan, the author of Microsoft Dynamics AX 2012 Reporting Cookbook, we will understand and acquire the skills necessary to be good at reporting based on SSRS.

This article will cover the following recipes:

  • Creating a matrix report
  • Creating a multicolumn matrix report
  • Creating a column chart
  • Creating a line chart

The legacy reporting system in Dynamics AX had very limited capabilities of how you can render data. Something as simple as adding an image and placing it right was a mammoth task, while things such as graphs and charts were not imaginable. SSRS takes away this pain and makes it easy to represent data in different formats. SSRS reports help create easier and convenient representation of data graphically that is easy for the end user to assimilate. This article will discuss recipes that cover the different kinds of controls other than the table layout discussed so far that can be used to represent data, such as matrix, charts, and gauges. The reader will be familiarized with the different controls and how they can be put to use in reports through this article.

(For more resources related to this topic, see here.)

Creating a matrix report

A matrix is an interesting representation format with a two-dimensional view of the data, allowing capabilities to consolidate by row and column. This recipe will discuss how to add and use a matrix data region in reports. Also discussed is the totaling capabilities in matrix reports.

How to do it…

This recipe is broken down into two sections. In the basic report design section we will build a simple RDP that will be used in this recipe as well as in other recipes found in this article and the actual recipe is in the second section.

Basic report design

  1. Before we start on this recipe we will build an RDP class that can be used as a source for all the recipes in this article. This RDP will be used as the source of a dataset for all the following reports.
  2. Create a query as seen here. The InventItemGroupItem table uses the exists join and is added for the purpose of limiting the sales lines data to certain item groups.
  3. The goal of the RDP is to run across the sales line in the system and then retrieve the item, item group, and the shipping date confirmed. The shipping date confirmed is also split into multiple parts such as years, months, and days.

    This RDP can be time consuming if you have a huge database of sales orders, so limit your data to certain item groups or a certain period as required in the RDP's processReport method.

  4. This RDP will fill a temporary table shown here:

  5. The RDP shown here will fill the temporary table by running through all the sales lines in the system.

    The general approach for analysis like this is to use OLAP so that it is faster and provides multiple dimensions of consolidations but using an OLAP to demonstrate these report controls might stop several from practicing. This is because the majority of AX developers are not BI experts. Keeping this in mind this RDP has been used to demonstrate the examples.

    [ //bind query - shows in the report dialog SRSReportQueryAttribute(queryStr(PktSalesLine)) ] class PktItemSalesHistoryDP extends SRSReportDataProviderBase { PktItemSalesHistoryTmp salesHistoryTmp; } [ SRSReportDataSetAttribute(tableStr(PktItemSalesHistoryTmp)) ] public PktItemSalesHistoryTmp getItemSalesHistoryTmp() { select salesHistoryTmp; return salesHistoryTmp; } private void insertTmpTable(SalesLine _salesLine) { Qty qty; date shipDate; InventItemGroupItem groupItem; qty = _salesLine.QtyOrdered; shipDate = _salesLine.ShippingDateConfirmed; groupItem = InventItemGroupItem::findByItemIdLegalEntity( _salesLine.ItemId, _salesLine.DataAreaId); salesHistoryTmp.clear(); salesHistoryTmp.ItemId = _salesLine.ItemId; salesHistoryTmp.ItemGroupId = groupItem.ItemGroupId; salesHistoryTmp.Price = _salesLine.salesPrice; salesHistoryTmp.Amount = _salesLine.SalesPrice * Qty; salesHistoryTmp.Qty = qty; salesHistoryTmp.Year = year(shipDate); salesHistoryTmp.MonthOfYearId = mthOfYr(shipDate); salesHistoryTmp.Days = dayOfMth(shipDate); salesHistoryTmp.insert(); } [ SysEntryPointAttribute(false) ] public void processReport() { Query query; QueryRun queryRun; SalesLine salesLine; InventItemGroupItem itemGroup; query = this.parmQuery(); queryRun = new queryRun(query); while (queryRun.next()) { salesLine = queryRun.get(tableNum(salesLine)); this.insertTmpTable(salesLine); } }

Creating a matrix report

  1. Create a report in Visual Studio named PktMatrixReport and add the RDP provider as a dataset.
  2. Set the Dynamic filter property to false.
  3. Create a new precision design and name it matrixDesign, and then double-click to open up the editor.
  4. Right-click and insert a new matrix data region:

  5. Use the field selector to set the fields as shown in the following screenshot. Choosing Qty in the data section will automatically add the Sum function to it:

  6. To add the total quantity for each row, on the Row Groups option at the bottom, click on the small arrow and then navigate to Add Totals | After.
  7. To add the total quantity for each column, on the Column Groups option at the bottom, click on the small arrow and then navigate to Add Totals | After.
  8. There are three total boxes in the design. Each represent the row total, column total, and the grand total. Grand total tallies the sum of rows and sum of columns.
  9. As you can see in the following screenshot that the grand total block in the title row is colored and the font is also set to bold. This will give a better appearance for the matrix:

  10. The header for the row is present but there is no similar header for the column group. So right-click on the first column and navigate to Insert Row | Outside Group – Above. This will give a header for the column. Enter the expression string ItemGroupId using the label ID or the static text. The design appears as shown here:

  11. Save the report and preview it:

How it works…

A matrix data region is actually a Tablix control behind the hood. The Tablix control combines the behavior of table, list, and matrix reports. Though the UI has table, matrix, and list controls, they are the same controls under the hood; they open up with a different configuration. The matrix data region has both row and column group, whereas a table control has only column group. Matrix helps create summary type reports.

Microsoft Dynamics AX 2012 Reporting Cookbook Over 50 recipes to help you build Dynamics AX reports faster by simplifying your understanding of the report model with this book and ebook
Published: September 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Creating a multicolumn matrix report

This recipe will show how a multicolumn matrix can be implemented. Here we will expand our report to see how we can dissect it into detail where the rows are further split by months following years and the columns will also show the average price other than the quantity.

Getting ready

This recipe requires that you complete the recipe Creating a matrix report in this article.

How to do it…

  1. Create a new design as in the previous recipe or extend the design created in the previous recipe with the steps detailed here.
  2. From the Report Data toolbar, drag the control MonthOfYearId1 to the Row Group control next to Year. When you drop it a blue bar appears and it must face the Year column as shown here:

  3. From the Report Data toolbar drag the control Price to Column Group next to Qty. A vertical blue bar appears and it must face the Qty column as seen here:

  4. The price field, by default, gets placed in as a Sum operation. Change it to Average by right-clicking on the cell and choosing Expression. In the expression field modify the sum and set it to average.
  5. Since price can have decimal values it is important to set the decimal ranges; otherwise, it ends up in an improper formatting. Right-click on price control and choose Textbox properties .
  6. On Textbox properties set the Formatting to Number and ensure Decimal places is set to 2:

  7. If you are extending the previous recipe drop the column totals before doing this; otherwise, directly move to the column group at the bottom and click on the arrow button and then navigate to Add Totals | After. This will add the totals for the quantity and price fields.
  8. The report design appears as shown here. Save the report and preview it.

  9. The first screenshot shows preview with limited data (the Load dataset fully option is not activated):

  10. The second screenshot shows the preview with the full dataset loaded:

How it works…

Here in this report, the summary is broken down by years and months while the columns summarize multiple values than just one value. In this way, a matrix can be used to build detail-drilled summary reports.

The report preview option in Visual Studio always loads a limited sampled data and doesn't bring the entire data behind the system. This is not realized in the case of small datasets. When using big datasets, as in the case in this article, you will notice that data changes every time. In this case, to confirm if everything is loaded, activate the Load data set fully option available in the right corner of your preview window.

Creating a column chart report

This recipe will guide you to create chart-based reports in Dynamics AX. Charts are interesting pictorial representations of data and the SSRS reports support a multitude of chart types. It is v to switch between chart types in SSRS. In this recipe we will create a column chart that represents the total sale of quantity over a couple of years.

Getting ready

Complete the RDP defined in the Getting ready section of the recipe Creating a matrix report in this article.

How to do it…

  1. Since the RDP class can return a large number of item groups, it is ideal to limit it to two item groups to test this recipe. This will make it faster and easier to work through this recipe.
  2. Modify processreport in the RDP to add ranges for the item group in the query.
  3. In Visual Studio create a new report PktColumnChartReport and link the RDP class to it. Remember to set the Dynamic filter property in the dataset to false.
  4. Create a new precision design and name it ChartDesign.
  5. Open the editor, right-click and then navigate to Insert | Chart. In the prompting dialog window that shows the different chart types available, choose the Column chart:

  6. Resize the chart to a required size and double-click on the chart area. This will show additional square boxes around the chart image for category, series, and data as seen in the following screenshot. Drag the following fields to the specified region accordingly:

    Field

    Drop area

    Year

    Category

    ItemGroupId

    Series

    Qty

    Data

  7. As fields are dropped in the chart area you can see the report changing at design time. Though this may not reflect the exact data, it will give you a feeling of how it looks at runtime.
  8. Modify the chart title and the axis title. Save the report design.

  9. The report preview appears as seen here:

How it works…

The report seen here is a multi-series chart, which means the category is applied across the series. A static series is where we represent a single series item; for example, item group. In this case if the item group is not restricted, then each year would be showing up for all the item groups in the system. Care has to be taken in what is added to the series so that the comparison makes sense to the user.

Creating a line chart

This recipe will discuss another chart-based report. Here we will try to show the monthly quantity trend for the item groups over the years. The line chart is the best option to reveal trends. This recipe will also focus on the aesthetical properties of charts, such as color and axis design.

Getting ready

This recipe requires the Creating a column chart report recipe in this article to be completed beforehand.

How to do it…

  1. Create a new precision design for the report. Call it line design.
  2. Open the editor and insert a new chart.
  3. Double-click on the chart area to open up the field editor. On the field editor drop the following fields as specified:

    Field

    Drop area

    Year, MonthOfYearId

    Category

    ItemGroupId

    Series

    Qty

    Data

  4. The next few steps will deal with how the aesthetics of a chart can be made better. On the General Properties window, set Palette to SeaGreen. This will apply a different set of colors for each series.
  5. To set up markers for the series on the value points, right-click on the chart area and select Series Properties. On the Markers tab set the type of marker as Square.

  6. The axis may not show all the labels for the category axis. To make sure that it shows all the months as labels right-click on an axis and open the Axis properties form. In the property form on the Axis options tab under Set axis scale and style, set the interval to 1. This will ensure that all the labels are visible.
  7. Right-click on the Legend option and choose Legend properties. On the General tab under the Legend position choose from the circular radio buttons the position of the legend:

  8. To disable the quantity axis right-click on any axis, uncheck Show Value Axis, and then the design should be similar in style to the screenshot here:

  9. Save the report and preview it.

How it works…

This recipe details how the appearance of a report can be controlled through the various associated properties. This recipe discusses only limited options while there are tons of more options that you can explore.

There's more…

Though the chart design shown here is from precision design, auto design also supports charts.

Chart reports in auto design

In auto design the category, series, and data appear as group nodes as seen here. The chart type can be changed from the Properties node of the chart control. The fields can be dragged into it to from the datasets.

Precision design is more flexible and convenient than auto design for the following reasons:

  • Modify and preview the chart in design time
  • Clarity to understand the field and its corresponding axis
  • Extent of customization and visibility of the available charts
  • More control on where and how the data is rendered, such as the marker and legend

    Prefer using precision design over auto design when it comes to charts.

Summary

This article dived in to using the other types of report formats, such as chart, lines and matrix reports.

Resources for Article:


Further resources on this subject:


Microsoft Dynamics AX 2012 Reporting Cookbook Over 50 recipes to help you build Dynamics AX reports faster by simplifying your understanding of the report model with this book and ebook
Published: September 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Kamalakannan Elangovan

Kamalakannan Elangovan has over eight years of development experience in Dynamics AX. He shares a passion for product development and has pioneered multiple ISV solutions on Dynamics AX. In the past he worked with Innovites to create the first multidimensional ISV solution for cable industries called "Innovites for cable". Building the solution from scratch, he has gained great insights into building, selling, and promoting the product among customers and partners in the Microsoft Ecosystem. You can learn more about him at http://about.me/casperkamal.

He is enthusiastic about sharing his learning with the community which led him to create one of the first few blogs for AX in 2006. It is currently available at http://kamalblogs.wordpress.com. He is active through twitter and the community, popularly by his pseudonym Casperkamal.

Books From Packt


Implementing Microsoft Dynamics AX 2012 with Sure Step 2012
Implementing Microsoft Dynamics AX 2012 with Sure Step 2012

Microsoft Dynamics AX 2012 Development Cookbook
Microsoft Dynamics AX 2012 Development Cookbook

Microsoft Dynamics AX 2012 Services
Microsoft Dynamics AX 2012 Services

Microsoft Dynamics AX 2009 Development Cookbook
Microsoft Dynamics AX 2009 Development Cookbook

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration

Microsoft Dynamics AX 2009 Programming: Getting Started
Microsoft Dynamics AX 2009 Programming: Getting Started

 Extending Microsoft Dynamics AX 2012 Cookbook
Extending Microsoft Dynamics AX 2012 Cookbook

 Microsoft Dynamics AX 2012 Security How-To [Instant]
Microsoft Dynamics AX 2012 Security How-To [Instant]


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
h
1
u
Z
P
A
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software