This is Part 4 of a 4 part article that covers how to create your own end-user-report writer for Dynamics AX. The previous article can be found here: [https://community.dynamics.com/ax/b/dynamicsax_wpfandnetinnovations/archive/2013/07/28/transactional-report-writing-pt-3.aspx#.UfU-z43FX3Q]
 
When you create an SSRS report definition within Report Builder 3.0 or Visual Studio 2010 you are in effect creating an RDL (or RDLC) file.
 

·         RDL files are created by Report Builder and published to SQL Server for server based report rendering and RDLC files are created by Visual Studio and used for localised report rendering.

 
RDL and RDLC formats have the same XML schema; however, in RDLC files, some report sections (such as query text) are not used because query processing occurs elsewhere. For our solution, we need to use RDLC files because the report-writer will be producing “generic-and-variable” data and as a result the report layout needs to adjust dynamically. This is not possible unless you implement a localised reporting solution.
 

·         You cannot (easily) adjust the server based RDL file from code without a full deployment and even if you do this then you end up adjusting the same report for all users.

 
The first thing you need to do is design your “generic-template”. Create a report using Report Builder with a few columns (any query will do) and some placeholders for the “Company name”, “Report title”, “Criteria” and “Default sorting”.
 
 
Publish this report to the SSRS server and then download the RDL definition from the Report Properties page:
 
 
 
 
If you open up this file then you’ll see that the report-design is all laid out in Xml format. It’s very long winded, but there are some relevant sections that you initially need to pay attention to:
 

1.)   The Dataset section, which details the report fields and their expected data types.

2.)   The Tablix header section, which details the column widths.

3.)   The Individual columns, which detail the binding information and cosmetics for each cell.

 
Xml
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/KK1/SQLServer</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>cca5628c-e88b-4e1a-96ee-bbc0e99a1119</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>select * from custtable</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="ACCOUNTNUM">
          <DataField>ACCOUNTNUM</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
 
 
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>3.67771cm</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>3.88937cm</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>3.81cm</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>3.08208cm</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>2.5cm</Width>
                </TablixColumn>
              </TablixColumns>
 
 
              <TablixRows>
                <TablixRow>
                  <Height>0.6cm</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <UserSort>
                            <SortExpression>=Fields!ACCOUNTNUM.Value</SortExpression>
                          </UserSort>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>ACCOUNTNUM</Value>
                                  <Style>
                                    <FontSize>8pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>WhiteSmoke</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
 
 

·         There are other useful sections (like the headers and report summary) that I have modified but concentrating on these primary sections will giveyou a flavour of whats posisble.

 
What we need to do is replace the Xml in these sections with our own Xml that has been derived from the Query selections made within Dynamics (the previous two articles in this series will show you how to do this).
 
The Xml data and Xml schema is sent to a Managed Host control as class properties. The .Net code then iterates through the schema and determines the query structure. This is then translated into the equivalent Xml to replace the RDL sections that we’d like to make dynamic. The following C# code snippet highlights this principle:
 
C#
public Stream GenerateReport()
{
    int textBoxCounter = 1;
    string xml = "";
    xml += @"<?xml version=""1.0"" encoding=""utf-8""?>
<Report xmlns:rd=""http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"" xmlns:cl=""http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition"" xmlns=""http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name=""dt"">
      <DataSourceReference>dt</DataSourceReference>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name=""dt"">
      <Fields>";
            for (int i = 0; i < fieldsDataTable.Rows.Count; i++)
            {
                xml += @"
        <Field Name=""" + fieldsDataTable.Rows[i]["FieldName"] + @""">
          <DataField>" + fieldsDataTable.Rows[i]["FieldName"] + @"</DataField>
          <rd:TypeName>" + dataDataTable.Columns[i].DataType.ToString() + @"</rd:TypeName>
        </Field>";
            }
            xml += @"
      </Fields>
      <Query>
        <DataSourceName>dt</DataSourceName>
        <CommandText />
        <rd:DataSourceName>true</rd:DataSourceName>
      </Query>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name=""Tablix1"">
            <TablixBody>
              <TablixColumns>";
            for (int i = 0; i < fieldsDataTable.Rows.Count; i++)
            {
                xml += @"
                <TablixColumn>
                  <Width>" + fieldsDataTable.Rows[i]["FieldWidth"] + @"cm</Width>
                </TablixColumn>";
 
This replacement methodology can be applied to all facets of the report. Here, in this snippet you can see that I’m allowing the user to override the “alternating-row-colour” and whether the “cell-has-border” around it:
 
C#
xml += @"<Style>
<Border>
    <Color>LightGrey</Color>
    <Style>" + ((this.GridlinesProperty) ? "Solid" : "None") + @"</Style>
</Border>
<BackgroundColor>= IIf(RowNumber(Nothing) Mod 2 = 0, """ + this.AlternatingRowColourProperty + @""", ""Transparent"")</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>";
 
 
The dataset and schema are loaded using some standard C#:
 
C#
// attempt to load the data table schema
try
{
    dataDataTable.ReadXmlSchema(new StringReader(this.XmlSchemaProperty));
}
catch (Exception ex)
{
    MessageBox.Show("Unable to load xml schema.", "Generic Report");
    return;
}
 
// attempt to load the data table
try
{
    dataDataTable.ReadXml(new StringReader(this.XmlDataProperty));
}
catch (Exception ex)
{
    MessageBox.Show("Unable to load xml data.", "Generic Report");
    return;
}
 
 
The invocation (and rendering) of the report happens from within the rich-client as soon as the user navigates to the last tab on the report wizard. Its on this tab that you need to execute the query and send the parameters for the managed host control:
 
X++
public void pageActivated()
{
    super();
 
    element.executeQuery();
    if (xmlData == '<?xml version="1.0" encoding="utf-16"?><DocumentElement></DocumentElement>')
    {
        Box::stop("No data to print");
        return;
    }
    _managedHost.set_ReportCompanyProperty(reportCompany);
    _managedHost.set_ReportTitleProperty(reportTitle);
    _managedHost.set_CriteriaProperty(reportCriteria);
    _managedHost.set_SortingProperty(reportSorting);
    _managedHost.set_OrientationProperty(pageOrientation);
    _managedHost.set_TableBorderProperty(showTableBorder);
    _managedHost.set_GridlinesProperty(showGridlines);
    _managedHost.set_AlternatingRowColourProperty(alternatingRowColour);
 
    _managedHost.set_XmlSchemaProperty(xmlSchema);
    _managedHost.set_XmlDataProperty(xmlData);
 
    _managedHost.Process();
}
 
 
After setting some report properties on a “Report settings” tab, the final result should look something like this:
 
 
Very nice… you have your own flexible, dynamic (and powerful) report-writer without any further need for the IT team (oh no done myself out of a job L)
 
REGARDS
 
Khalid