Programmatically generate an SSRS report via Kinetic Function

[rhuebnerNNI (Robert Huebner)]

Hello,

I am trying to create a function that generates an SSRS report that is defined as a report data definition and report style.

I’m currently having issues attempting to execute the TransformDynamicCriteria function. Unfortunately, this function is defined as taking a parameter of type object with the name ds, so I cannot tell what exactly it is expecting in regard to type. I can call TransformDynamicCriteria outside of the function via Postman and the REST API and it functions properly. I am using the same dataset that is created in my code below with this REST API and it is functioning properly.

Here are the different things I’ve tried:
1. Use Ice.DatasetAdapter.ConvertToGenericDataset and pass the tableset that is retrieved when calling GetNewDynamicCriteriaReportParam and pass that to the function
2. Serialize the generic dataset to JSON and pass that to the function
3. Create a non-generic dataset, DynamicCriteriaDataSet, and use Ice.DatasetAdapter.CopyTSTableToDataTable to copy both the DynamicCriteriaParam and ReportStyle tables to the new dataset, then pass that to the function

Here is the code from my function that I have been attempting to use. Currently, the parameters are hard coded for testing purposes, and the reportID is passed as a parameter to the function. When executing this function via Postman, an exception is thrown, which I am capturing.

Exception contents: “InvalidDataset (Parameter ‘ds’)”

Code:

try
{
  this.CallService<Ice.Contracts.DynamicCriteriaSvcContract>(bo =>
  {
      Guid guid = Guid.NewGuid();
      Ice.Tablesets.DynamicCriteriaTableset tsDynamicCriteria = bo.GetNewDynamicCriteriaReportParam(this.reportID);
      
      tsDynamicCriteria.DynamicCriteriaParam[0].ArchiveCode = 1;
      tsDynamicCriteria.DynamicCriteriaParam[0].AutoAction = "SSRSGENERATE";
      tsDynamicCriteria.DynamicCriteriaParam[0].SSRSRenderFormat = "PDF";
      tsDynamicCriteria.DynamicCriteriaParam[0].WorkstationID = "web_manager"; //callContextBpmData.Character01;
      tsDynamicCriteria.DynamicCriteriaParam[0].TaskNote = guid.ToString();
      
      tsDynamicCriteria.ReportStyle[0]["SSRSRenderFormat"] = "PDF";
      
      Ice.IceColumn paramColumn1 = Ice.IceColumn.CreateExtensionColumn("dateFrom", typeof(System.DateTime));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn1);
      
      Ice.IceColumn paramColumn2 = Ice.IceColumn.CreateExtensionColumn("dateTo", typeof(System.DateTime));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn2);
      
      Ice.IceColumn paramColumn3 = Ice.IceColumn.CreateExtensionColumn("salesRepCode", typeof(System.String));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn3);
      
      tsDynamicCriteria.DynamicCriteriaParam[0]["dateFrom"] = "07/01/2023";
      tsDynamicCriteria.DynamicCriteriaParam[0]["dateTo"] = "08/05/2024";
      tsDynamicCriteria.DynamicCriteriaParam[0]["salesRepCode"] = "SALESREP";
    
      this.ds = (System.Data.DataSet)bo.TransformDynamicCriteria(Ice.DatasetAdapter.ConvertToGenericDataset(tsDynamicCriteria, "", null, true));
      
      string defaultTaskAgentID = "";
      this.CallService<Ice.Contracts.SysAgentSvcContract>(svc1 =>
      {
          svc1.GetDefaultTaskAgentID(out defaultTaskAgentID);
      });
    
      bo.SubmitToAgent(tsDynamicCriteria, "", 0, 0, "Erp.UIRpt.TestForm");
      
      bool isReady = false;
      while (!isReady)
      {
          var sysRptLstQuery = this.Db.SysRptLst.FirstOrDefault(t => t.RptNote == guid.ToString());
          
          if (sysRptLstQuery != null)
          {
             isReady = true;
          }   
      }   
      
      this.CallService<Ice.Contracts.ReportMonitorSvcContract>(svc2 =>
      {
          bool morePages = true;
          Ice.Tablesets.ReportMonitorTableset tsReportMonitor = svc2.GetRows($"RptNote='{guid.ToString()}'", 1, 1, out morePages);
          
          if (tsReportMonitor.SysRptLst.Count > 0)
          {
              Ice.BO.ReportMonitorDataSet dsReportMonitor = new Ice.BO.ReportMonitorDataSet();
              DatasetAdapter.CopyTSTableToDataTable<Ice.Tablesets.SysRptLstRow, Ice.BO.ReportMonitorDataSet.SysRptLstDataTable>(tsReportMonitor.SysRptLst, dsReportMonitor.SysRptLst);       
                           
              //System.IO.MemoryStream ms = new System.IO.MemoryStream(svc2.GetReportBytes(tsReportMonitor.SysRptLst[0].SysRowID));
              //System.IO.StreamWriter sw = new System.IO.StreamWriter($"OUTPUTPATH");
              //ms.WriteTo(sw.BaseStream);
              //sw.Flush();
              //sw.Close();
              
              this.ds = dsReportMonitor;
          }
      });
  });
}
catch(Exception ex)
{
  this.outputStr = ex.Message;
}

[Hally (Simon Hall)]

@rhuebnerNNI

     //sw.Close();
              
              this.ds = dsReportMonitor;
          }

I’m asuming here you will want to define the argument/variable or signature variable to be of type ReportMonitorDataSet.

To do this when you go to select the type you will need to use the “Select Type Option”

The Service or assembly has to be added to the function library first…

I hope that helps.

[rhuebnerNNI (Robert Huebner)]

Unfortunately, my function doesn’t get to execute that portion of code, it’s getting stuck at this line, which throws an exception.

this.ds = (System.Data.DataSet)bo.TransformDynamicCriteria(Ice.DatasetAdapter.ConvertToGenericDataset(tsDynamicCriteria, “”, null, true));

[rhuebnerNNI (Robert Huebner)]

I updated my code to change this.ds to this.outDS to help differentiate the exception from this out parameter as I do not believe it is related.

try
{
  this.CallService<Ice.Contracts.DynamicCriteriaSvcContract>(bo =>
  {
      Guid guid = Guid.NewGuid();
      Ice.Tablesets.DynamicCriteriaTableset tsDynamicCriteria = bo.GetNewDynamicCriteriaReportParam(this.reportID);
      
      tsDynamicCriteria.DynamicCriteriaParam[0].ArchiveCode = 1;
      tsDynamicCriteria.DynamicCriteriaParam[0].AutoAction = "SSRSGENERATE";
      tsDynamicCriteria.DynamicCriteriaParam[0].SSRSRenderFormat = "PDF";
      tsDynamicCriteria.DynamicCriteriaParam[0].WorkstationID = "web_manager"; //callContextBpmData.Character01;
      tsDynamicCriteria.DynamicCriteriaParam[0].TaskNote = guid.ToString();
      
      tsDynamicCriteria.ReportStyle[0]["SSRSRenderFormat"] = "PDF";
      
      Ice.IceColumn paramColumn1 = Ice.IceColumn.CreateExtensionColumn("dateFrom", typeof(System.DateTime));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn1);
      
      Ice.IceColumn paramColumn2 = Ice.IceColumn.CreateExtensionColumn("dateTo", typeof(System.DateTime));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn2);
      
      Ice.IceColumn paramColumn3 = Ice.IceColumn.CreateExtensionColumn("salesRepCode", typeof(System.String));
      tsDynamicCriteria.DynamicCriteriaParam.Columns.Add(paramColumn3);
      
      tsDynamicCriteria.DynamicCriteriaParam[0]["dateFrom"] = "07/01/2023";
      tsDynamicCriteria.DynamicCriteriaParam[0]["dateTo"] = "08/05/2024";
      tsDynamicCriteria.DynamicCriteriaParam[0]["salesRepCode"] = "SALESREP";
    
      this.outDS = (System.Data.DataSet)bo.TransformDynamicCriteria(Ice.DatasetAdapter.ConvertToGenericDataset(tsDynamicCriteria, "", null, true));
      
      string defaultTaskAgentID = "";
      this.CallService<Ice.Contracts.SysAgentSvcContract>(svc1 =>
      {
          svc1.GetDefaultTaskAgentID(out defaultTaskAgentID);
      });
    
      bo.SubmitToAgent(tsDynamicCriteria, "", 0, 0, "Erp.UIRpt.TestForm");
      
      bool isReady = false;
      while (!isReady)
      {
          var sysRptLstQuery = this.Db.SysRptLst.FirstOrDefault(t => t.RptNote == guid.ToString());
          
          if (sysRptLstQuery != null)
          {
             isReady = true;
          }   
      }   
      
      this.CallService<Ice.Contracts.ReportMonitorSvcContract>(svc2 =>
      {
          bool morePages = true;
          Ice.Tablesets.ReportMonitorTableset tsReportMonitor = svc2.GetRows($"RptNote='{guid.ToString()}'", 1, 1, out morePages);
          
          if (tsReportMonitor.SysRptLst.Count > 0)
          {
              Ice.BO.ReportMonitorDataSet dsReportMonitor = new Ice.BO.ReportMonitorDataSet();
              DatasetAdapter.CopyTSTableToDataTable<Ice.Tablesets.SysRptLstRow, Ice.BO.ReportMonitorDataSet.SysRptLstDataTable>(tsReportMonitor.SysRptLst, dsReportMonitor.SysRptLst);       
                           
              //System.IO.MemoryStream ms = new System.IO.MemoryStream(svc2.GetReportBytes(tsReportMonitor.SysRptLst[0].SysRowID));
              //System.IO.StreamWriter sw = new System.IO.StreamWriter($"OUTPUTLOCATION");
              //ms.WriteTo(sw.BaseStream);
              //sw.Flush();
              //sw.Close();
              
              this.outDS = dsReportMonitor;
          }
      });
  });
}
catch(Exception ex)
{
  this.outputStr = ex.Message;
}

Unfortunately, the exception is still the same after changing this: “InvalidDataset (Parameter ‘ds’)”

[Hally (Simon Hall)]

Sorry I missed that first reference… Interestingly you try to assign it, but it is never used then you try to assign it again with the ReportMonitor dataset.

You might find this post helpful

C# Help with DynamicCriteria Report

Hi I’ve got the following code working, by taking examples from the forum and then using BL Tester and looking at the .dll to work out the relevant method calls and properties. It works, but it’s not dynamic because I can’t work out the XML bit for UserCriteria.

Working Code - but static

private void RunCOAReport(string orderNum) { string agentID = ""; try { Session otSession = (Session)oTrans.Session; //Set the workstationID string workStation = Ice.Lib.Report.…

[rhuebnerNNI (Robert Huebner)]

Thank you for the post, that looks promising. I noticed that that post doesn’t use the function I’m having issues with. I think this is because the XML is being populated manually. I will try manually populating the XML and see if that helps my issue.

Also, the out parameter that you’re seeing is mainly for testing to ensure the correct data is being generated, that’s why it’s not being used in between assignments.

[klincecum (Kevin Lincecum)]

I can’t remember if I shared my functions that have this documented. I will check when I get back home.

[rhuebnerNNI (Robert Huebner)]

I was able to get my function to work by defining the XML myself, thank you for all the help.

[Hally (Simon Hall)]

Sometimes it just take a bit of poking here and there to get things to work…