OLAP Data Sources

OLAP Data Sources

• robbiesmith79

  Your solution is working for me, many thanks for your post!

  I use AdomdDataAdapter and mdx query to populate a DataTable and add it to LocalReport.DataSources of the ReportViewer. Instead of setting ReportPath, I read the .rdlc (or .rdl) file, manupulate its content and save it as MemoryStream.

  This is how I get the stream:

  Code Snippet

  private MemoryStream GetReportStream( string sPath )

  {

  MemoryStream resStream = new MemoryStream();

  XmlDocument doc = new XmlDocument();

  doc.Load( sPath );

  XmlElement root = doc.DocumentElement;

  XmlNamespaceManager nsmgr = new XmlNamespaceManager( doc.NameTable );

  nsmgr.AddNamespace( "r", doc.DocumentElement.NamespaceURI );

  nsmgr.AddNamespace( "rd", doc.DocumentElement.Attributes[ "xmlns:rd" ].Value );

  XmlNode nodeMdx = root.SelectSingleNode( @"/r:Report/r:DataSets/r:DataSet/r:Query/rd:MdxQuery", nsmgr );

  // Make changes only if the rdl file contains mdx data

  if ( nodeMdx != null )

  {

  XmlNodeList nodes = root.SelectNodes( @"/r:Report/r:DataSets/r:DataSet/r:Fields/r:Field/r:DataField", nsmgr );

  foreach ( XmlNode node in nodes )

  {

  XmlDocument fieldDoc = new XmlDocument();

  using ( MemoryStream ms = new MemoryStream() )

  {

  using ( StreamWriter writer = new StreamWriter( ms ) )

  {

  writer.Write( node.FirstChild.Value );

  writer.Flush();

  ms.Seek( 0, SeekOrigin.Begin );

  fieldDoc.Load( ms );

  string sUniqueName = fieldDoc.DocumentElement.Attributes[ "UniqueName" ].Value;

  if ( fieldDoc.DocumentElement.Attributes[ "xsi:type" ].Value.Equals( "Level" ) )

  {

  node.InnerXml = fieldDoc.DocumentElement.Attributes[ "UniqueName" ].Value + ".[MEMBER_CAPTION]";

  }

  else

  {

  node.InnerXml = fieldDoc.DocumentElement.Attributes[ "UniqueName" ].Value;

  }

  }

  }

  }

  }

  doc.Save( resStream );

  resStream.Seek( 0, SeekOrigin.Begin );

  return resStream;

  }

  This is working even for rdl files that are not using olap data - they just won't be changes when creating the stream.

  I would be happy if I knew some better solution... Although it is working this way, it's ugly and I am sure there should be a better way to use OLAP Datasources with the LocalReport of the ReportViewer Control. If anyone knows anything useful about that, please post some info, just give me a clue. Thank you!

  
  

• C_TO

  Step 1: You have to use an OLE-DB Data connection and run the OLAP SQL query. This returns more data than what you need but it does return the data. Then you load that data onto the local report.

  Step 2: Now for the tricky part which took some hard core figuring out......ready.....You cannot load the local report just by giving the "ReportPath". You actually have to read the file into an "string" and then replace some things and then load it onto the LocalReport dynamically. What you have to replace is the under your olap "Dataset" there are "Field" nodes and in there there is "DataField" nodes. In that data field node there is a "UniqueName" attribute. Ex: <DataField>&lt; xml version="1.0" encoding="utf-8" &gt;&lt;Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /&gt;</DataField>. You need to make it look like this....<DataField>[Measures].[ParameterCaption].[MEMBER_CAPTION]</DataField>. Notice that I added .[MEMBER_CAPTION]. After doing this load it onto the report dynamically and then it should work for you.

  I dont know how I figured all this out but it seems to be working for me... Let me know how if it works for you or not.

  
  

• judo

  Thanks for the info
  
  

• J Davis

  I have successfully created an rdl with an olap database connection. It works great within the Report Server Project. My problem is when I try to load it into a local report it shows no data. This is weird because when I am debugging I see that the datatable which I load into the ReportDataSource structure contains plenty of data. The project I am working on is somewhat large so pasting any code would be really confusing...

  
  

• Hiten

  Update I finally got OLAP to work with and rdl report and LocalReport with reportviewer in ASP.NET 2.0. Man all the code to accomplish this is unreal.
  

• Pieter R

  Did you ever find out an answer to this question
  
  

• gajanand

  Unreal in what way Just want to brace myself for what's to come.
  
  

• mikewo

  I haven't heard any responses on this. Brian / Rajeev any idea / suggestions Thanks.
  
• OLAP Data Sources