Jorn Lindhard Mortensen's blog

PingBack from http://www.easycoded.com/my-office-accounting-report%e2%80%99s-aging-filters-need-to-add-new-columns

Hello Jorn,

Your post was a big help, thank you.  But now I have another question that is related to this post.  It seems that when the design is changed on the fly, the column sequence is affected.  

For example, the report starts out with the following columns:  "Invoice #", "Due Date", "1-30", "31-60", "61-90", "Over 90".

If I change to aging interval to 20 days the columns become: "1-20", "21-40", "41-60", "61-80", "81-90", "Invoice #", "Due Date", "Over 90".

I am creating the report columns in the following order: "Invoice #", "Due Date" and then looping through the aging interval to create the other columns.

It appears like the report interface is displaying new columns first. Is there a way where I can refresh the report interface or control the sequence order?

Thanks in advance.

Rich

Jorn, here are some code snippets related to the report design:

public void CreateDesign(IReportDesignV2 iReportDesign)

{

   // Check object references

   if (iReportDesign == null)

       return;

   // Add a title to the report

   iReportDesign.ReportHeader.ReportTitle.Text = this.reportTitle;

   iReportDesign.ReportColumns.CreateReportColumn("Type", "Type", ReportColumnType.RowLabel, ReportColumnDataType.Text);

   iReportDesign.ReportColumns.CreateReportColumn("PrimaryContact", "Primary Contact", ReportColumnType.Data, ReportColumnDataType.Text);

   iReportDesign.ReportColumns.CreateReportColumn("Phone", "Customer Phone", ReportColumnType.Data, ReportColumnDataType.Text);

   groupidColumn = iReportDesign.ReportColumns.CreateReportColumn("GroupId", "GroupId", ReportColumnType.GroupID, ReportColumnDataType.NumberInt);

   iReportDesign.ReportColumns.CreateReportColumn("Date", "Invoice Date", ReportColumnType.Data, ReportColumnDataType.Date);

   iReportDesign.ReportColumns.CreateReportColumn("Number", "No.", ReportColumnType.Data, ReportColumnDataType.Text);

   iReportDesign.ReportColumns.CreateReportColumn("ReferenceNumber", "Reference", ReportColumnType.Data, ReportColumnDataType.Text);

   iReportDesign.ReportColumns.CreateReportColumn("Void", "Void", ReportColumnType.Data, ReportColumnDataType.Boolean);

   iReportDesign.ReportColumns.CreateReportColumn("PaymentTerm", "Payment Term", ReportColumnType.Data, ReportColumnDataType.Text);

   iReportDesign.ReportColumns.CreateReportColumn("DueDate", "Due Date", ReportColumnType.Data, ReportColumnDataType.Date);

   iReportDesign.ReportColumns.CreateReportColumn("Amount", "Amount", ReportColumnType.Data, ReportColumnDataType.Currency);

   iReportDesign.ReportColumns.CreateReportColumn("Total", "Total", ReportColumnType.Data, ReportColumnDataType.Currency);

   iReportDesign.ReportColumns.CreateReportColumn("Current", "Current", ReportColumnType.Data, ReportColumnDataType.Currency);

   globalReportDesign = iReportDesign;

}

public DataView CreateData(IReportFiltersV2 reportFilters, IReportEngineV2 engine)

{

   //------Passes retrieved filter values (code not shown) into a method and accesses a custom stored procedure (similar to spRptArAgingQuickZoom) and populates DataTables------------------

   AccessStoredProc(asofValue, showvoidValue, amountoperatorValue, nameValue, namelistValue, customergroupValue, customergrouplistValue, amountValue, agingperiodValue, agingintervalValue, engine);

   //------Loops and creates new aging columns based on aging information ------------------------------------------------------------------------

   for (int i = 1; i <= agingperiodValue; i += agingintervalValue)

   {

       int j = i + agingintervalValue - 1;

       if (j > agingperiodValue)

           j = agingperiodValue;

       string columnname = "#" + i + "#" + j + "#";

       string columncaption = i + " - to - " + j;

       globalReportDesign.ReportColumns.CreateReportColumn(columnname, columncaption, ReportColumnType.Data, ReportColumnDataType.Currency);

   }

   globalReportDesign.ReportColumns.CreateReportColumn("Over#" + agingperiodValue + "#", "Over " + agingperiodValue, ReportColumnType.Data, ReportColumnDataType.Currency);

   //------Creates a DataView from fieldsTable, valuesTable and extendedvaluesTable DataTables populated from the stored procedure

   DataView view;

   foreach (DataRow row in fieldsTable.Rows)

       if ((row["ParentColumnId"].ToString() != ""))

           valuesTable.Columns.Add(row["ColumnName"].ToString(), typeof(Decimal));

   foreach (DataRow extendedvaluesRow in extendedvaluesTable.Rows)

   {

       DataRow[] fieldRow = fieldsTable.Select("Identification = '" + extendedvaluesRow["ColumnId"].ToString() + "'");

       DataRow[] valuesRow = valuesTable.Select("RowId = '" + extendedvaluesRow["RowId"].ToString() + "'");

       if (valuesRow != null)

       {

           valuesRow[0][fieldRow[0]["ColumnName"].ToString()] = extendedvaluesRow["Amount"];

       }

   }

   view = valuesTable.DefaultView;

   return view;

}

Let me know if this doesn't make sense or if I am doing something wrong.

Thanks.

Hi Jorn,

Again thanks for your help.  Your solution solved the column order problem.  But I now have a new problem.  It seems that I can't change the visibility of a column once it has been intially loaded into the report.  So in your example above, if column #5 is initially not visible, it stays not visible after an aging option is selected.  I have stepped through the code and confirmed that the ReportColumnType is correct and IReportColumnV2.Visible is true and it still comes up as not visible in the report.  

Is there a way to refresh the report viewer itself or to delete all of the columns and then recreate them so the report sees them all as new?

Thanks again,

Rich

As a quick follow-up to my question.  How does the A/R Aging Detail report do this, because that is exactly the type of column behavior I am trying to duplicate.  Thanks.

Jorn,

I am using ReportColumnType.NotVisible for columns that are initially not used, and then changing it to ReportColumnType.Data when I need them, but they stay invisible.

Thanks,

Rich