none
RDL Viewer w/Web Service RRS feed

  • Question

  • For our application (VS2010 Web Application) we use a flavor of the RDL Viewer from the GotReportViewer site, to run some embedded reports via the ReportViewer in Local Mode for modularity and customization purposes.  It also allows us to write there reports at a SQL Server 2008 level regardless the backend database. 

    In any case we have run across a situation where we need to populate some of the data via Web Service calls.  We could in the GetData() method parse out the XML request and bind these services to the application since there are only about 10 service methods, but I was wondering if there was a technical way we could take the native query command from the BI developer:

    <Query>
      <Method Name="QueryRangeOfValues" Namespace='http://TagService' >
     <Parameters>
       <Parameter Name="argTagList" type="string">
    	<DefaultValue>NetWorth</DefaultValue>
       </Parameter>
       <Parameter Name="argStartTime" type="dateTime">
    	<DefaultValue>2011-05-01T00:00:10.0000000-05:00</DefaultValue>
       </Parameter>
       <Parameter Name="argEndTime" type="dateTime">
    	<DefaultValue>2011-05-01T00:10:10.0000000-05:00</DefaultValue>
       </Parameter>
       <Parameter Name="argMinutesPerInterval" type="int">
    	<DefaultValue>1</DefaultValue>
       </Parameter>
     </Parameters>
     </Method>
     <ElementPath IgnoreNamespaces="true">*</ElementPath>
    </Query>
    

    And process that into a DataTable just as we do with the SqlCommand processing?

    Thanks, Shane

    Wednesday, May 11, 2011 5:23 PM

All replies

  • So I figured out how to take the query command from the RDL and get data for a Web Service via the code below.  My struggle now is taking the result objects and porting them to a compatible DataTable.  I have tried multiple ways including serializing result objects to XML, then Read into a dataset and then pull the DataSet.Tables[0] for table.  The problem is, some of the web service queries have cascading result objects that get serialized to multiple datatables and in SSRS, this is flattened (I have details on an example below). 

    First lets post my modified GetData method to make the service call.  In this case I am getting a resultObject but not porting to a DataTable so the last few lines of code would be adjusted based on the second half of this post:

        public DataTable GetWebServiceData(string argDataSetName, ReportParameterInfoCollection argReportParams)
        {  DataSetInfo dataSetInfo = quickRefDataSetDictionary[argDataSetName];
          if (dataSetInfo.objDataTable == null)
          {  DataSourceInfo dataSourceInfo = quickRefDataSourceDictionary[dataSetInfo.dataSourceName];
            if (dataSourceInfo.serviceObject == null)
            {  // Establish Dynamic Web Service Reference
              XmlTextReader xmlReader = new XmlTextReader(new Uri(dataSourceInfo.connectString) + "?wsdl");
              ServiceDescription srvDescription = ServiceDescription.Read(xmlReader);
              ServiceDescriptionImporter srvImporter = new ServiceDescriptionImporter();
              srvImporter.ProtocolName = "Soap";
              srvImporter.AddServiceDescription(srvDescription, null, null);
    
              CodeNamespace ns = new CodeNamespace();
              CodeCompileUnit compUnit = new CodeCompileUnit();
              compUnit.Namespaces.Add(ns);
              ServiceDescriptionImportWarnings warnings = srvImporter.Import(ns, compUnit);
              if (warnings == 0)
              {  CompilerParameters compParams = new CompilerParameters(new[] { "System.Web.Services.dll", "System.Xml.dll" }) { GenerateInMemory = true };
                CodeDomProvider domProvider = CodeDomProvider.CreateProvider("C#");
                CompilerResults compResults = domProvider.CompileAssemblyFromDom(compParams, compUnit);
                dataSourceInfo.serviceObject = compResults.CompiledAssembly.CreateInstance(srvDescription.Services[0].Name);
              }
            }
    
    
            // Build input dictionary for both types of calls
            IDictionary<string, string> parmValues = new Dictionary<string, string>();
            if (dataSetInfo.queryParameters != null)
            {  foreach (QueryParameterInfo queryParameterInfo in dataSetInfo.queryParameters)
              {  string valueExpression = queryParameterInfo.valueExpression;
                const string starting = "=Parameters!";
                const string ending = ".Value";
                if (!valueExpression.StartsWith(starting) ||
                  !valueExpression.EndsWith(ending))
                {  throw new Exception("Can't parse query parameter expression: " + valueExpression);
                }
                int parameterNameLen = valueExpression.Length - starting.Length - ending.Length;
                string parameterName = valueExpression.Substring(starting.Length, parameterNameLen);
                string reportParameterValue = argReportParams[parameterName].Values[0];
                if (reportParameterValue == null)
                  throw new Exception("Report parameter " + parameterName + " has no value set");
                parmValues.Add(queryParameterInfo.parameterName, reportParameterValue);
              }
            }
    
            XmlDocument serviceQueryDoc = new XmlDocument();
            serviceQueryDoc.LoadXml(dataSetInfo.query);
            XmlNode methodNode = serviceQueryDoc.SelectSingleNode("//Method");
            string methodName = methodNode.Attributes.GetNamedItem("Name").InnerText;
            Type serviceType = dataSourceInfo.serviceObject.GetType();
            MethodInfo serviceMethod = serviceType.GetMethod(methodName);
            List<object> methodParams = new List<object>();
            foreach (ParameterInfo pi in serviceMethod.GetParameters())
            {  methodParams.Add(Convert.ChangeType(parmValues[pi.Name], pi.ParameterType));
            }
            try
            {  object resultObject = serviceMethod.Invoke(
                dataSourceInfo.serviceObject, methodParams.ToArray());
            }
            catch { }
          }
          return new DataTable();
        }
    

    As I said before I have tried multiple different ways to port objects to a datatable but I can't seem to deal with a couple of scenarios.  In SSRS it maps the DataQuery to the following fields:  xmlns, Timestamp, Message, Name, Value, TagDesc, UOM, Group, PrefSortSeq, TagValueType and the XML from the serialized objects from my process results in:

    <?xml version="1.0" encoding="utf-16"?>
    <ArrayOfTagReadings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <TagReadings>
      <Timestamp>2011-05-01T00:00:00Z</Timestamp>
      <TagValues>
       <TagValue>
        <Name>Sine</Name>
        <Value>0</Value>
        <TagDesc>Sine Wave Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
       <TagValue>
        <Name>Ramp</Name>
        <Value>-500</Value>
        <TagDesc>Ramp Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
      </TagValues>
      <Message />
     </TagReadings>
     <TagReadings>
      <Timestamp>2011-05-01T00:05:00Z</Timestamp>
      <TagValues>
       <TagValue>
        <Name>Sine</Name>
        <Value>430.8507726643</Value>
        <TagDesc>Sine Wave Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
       <TagValue>
        <Name>Ramp</Name>
        <Value>-164.5833333125</Value>
        <TagDesc>Ramp Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
      </TagValues>
      <Message />
     </TagReadings>
     <TagReadings>
      <Timestamp>2011-05-01T00:10:00Z</Timestamp>
      <TagValues>
       <TagValue>
        <Name>Sine</Name>
        <Value>-429.102384996871</Value>
        <TagDesc>Sine Wave Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
       <TagValue>
        <Name>Ramp</Name>
        <Value>166.666666642857</Value>
        <TagDesc>Ramp Readings</TagDesc>
        <UOM>Deg. F</UOM>
        <Group>None</Group>
        <PrefSortSeq>0</PrefSortSeq>
        <TagValueType>VALUE</TagValueType>
       </TagValue>
      </TagValues>
      <Message />
     </TagReadings>
    </ArrayOfTagReadings>
    

    If you ReadXML this to a dataset it produces 3 datatables so I can't just pop off Table[0] since it only will have 3 columns and fails to cascade to TagValues.  Like I said before, I know I can produce an XSD or change the service call itself, but I don't want to change this for all my service calls and would rather develop a generic way to mirror the SSRS behavior.  Any other suggestions on how to flatten this out in a generic way to match the SSRS reported fields? 

     

    Sorry about the code dump, but wanted to be more specific on the problem,

    Shane

     

    Friday, May 13, 2011 4:22 PM