Using multiple stored procedures in one report

Using multiple stored procedures in one report

• RickK

  I'm not sure if i understand your question. I don't understand "they used stored procedures in one stored procedure and used in one report" or how this description differs from what you want to accomplish.
  
  It is very easy to use two stored procedures in one report. You simply need to define two datasets, each with one stored procedure. When you create your report, you link report items ( tables, lists, etc ) to a single dataset as the default, but you can use the results from any of the other datasets if you wish.
  
  It is not possible, evidently, to do any table joins from the results of your datasets. These are best done on the db/stored procedure level.
  
  Am i getting close

  
  
  

• Josh Smith

  hi catz,

  i am also not able to use two or more result set returned from a stored procedure. i am new to reporting services.

  in the above u have mentioned xml schema and with the help of that schema u are able to store more than one table in a dataset.

  could u please tell me the steps sothat i ll also configure my Reportdataset to handel multiple table.

  please help me in this regard.

  Thanks a ton in advance.

  
  
  

• jm47048

  Hi Ray, Actually no. A single stored proc. might return 8 or 9 tables in a single XML file in one hit (because its all for one report )

  We have these "Health Check" reports where a single health check report might be made up of 8 or 9 subreports, but the business treats it as all one report. Below is a example of a (partial) data file a Sproc might return. The first datatable might have 100 records, the next one 500 and so on. So if you're saying that Reporting Services will only cope with the first datatable below then this will have a major impact on how we do these reports under SSRS. Crystal has no problem reading these types of datasets. With Crystal the first datatable below corresponds to one sub-report, second datatable to 2nd sub-report and so on. One data file, one .rpt file, one web-service call, one report.

  With SSRS sounds like it will be 8 or 9 xml data files, 8 or 9 .rdl files, 8 or 9 web-service calls, 8 or 9 little reports. Bit disappointing considering what we've had in the past.

  It's not much to ask, is it Mr. Microsoft Gates, that your SSRS software is able to read valid xml data files and display all the fields across all datatables (ready for me to drag and drop)

  thanks anyway Ray - appreciate the input,

  Catz

  < xml version="1.0" standalone="yes" >
  <ReportDataSet>
  <xs : schema id="ReportDataSet" xmlns="" xmlns : xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn : schemas-microsoft-com : xml-msdata">
  <xs : element name="ReportDataSet" msdata : IsDataSet="true" msdata : Locale="en-AU">
  <xs : complexType>
  <xs : choice maxOccurs="unbounded">
  <xs : element name="ReportDataTable">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="CONTRACT_ID" type="xs : string" minOccurs="0" />
  <xs : element name="LMR_CD" type="xs : string" minOccurs="0" />
  <xs : element name="LMR_DESC" type="xs : string" minOccurs="0" />
  <xs : element name="ESA_CD" type="xs : string" minOccurs="0" />
  <xs : element name="ESA_DESC" type="xs : string" minOccurs="0" />
  <xs : element name="ORG_CD" type="xs : string" minOccurs="0" />
  <xs : element name="ORG_DESC" type="xs : string" minOccurs="0" />
  <xs : element name="SITE_CD" type="xs : string" minOccurs="0" />
  <xs : element name="SITE_DESC" type="xs : string" minOccurs="0" />
  <xs : element name="CONTRACT_TYPE" type="xs : string" minOccurs="0" />
  <xs : element name="ESA_TYPE" type="xs : string" minOccurs="0" />
  <xs : element name="EXTRACT_DT" type="xs : string" minOccurs="0" />
  <xs : element name="PERFORMANCE_PERIOD" type="xs : short" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  <xs : element name="ReportDataTable1">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="SQ" type="xs : string" minOccurs="0" />
  <xs : element name="FN" type="xs : string" minOccurs="0" />
  <xs : element name="PP1" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP2" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP3" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP4" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP5" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP6" type="xs : decimal" minOccurs="0" />
  <xs : element name="CTD" type="xs : decimal" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  <xs : element name="ReportDataTable2">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="EQ" type="xs : string" minOccurs="0" />
  <xs : element name="EN" type="xs : string" minOccurs="0" />
  <xs : element name="EP1" type="xs : decimal" minOccurs="0" />
  <xs : element name="EP2" type="xs : decimal" minOccurs="0" />
  <xs : element name="EP3" type="xs : decimal" minOccurs="0" />
  <xs : element name="EP4" type="xs : decimal" minOccurs="0" />
  <xs : element name="EP5" type="xs : decimal" minOccurs="0" />
  <xs : element name="EP6" type="xs : decimal" minOccurs="0" />
  <xs : element name="CTD" type="xs : decimal" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  <xs : element name="ReportDataTable3">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="SQ" type="xs : string" minOccurs="0" />
  <xs : element name="FN" type="xs : string" minOccurs="0" />
  <xs : element name="PP1" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP2" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP3" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP4" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP5" type="xs : decimal" minOccurs="0" />
  <xs : element name="PP6" type="xs : decimal" minOccurs="0" />
  <xs : element name="CTD" type="xs : decimal" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  <xs : element name="ReportDataTable4">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="SQ" type="xs : string" minOccurs="0" />
  <xs : element name="FN" type="xs : string" minOccurs="0" />
  <xs : element name="CTD" type="xs : decimal" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  <xs : element name="ReportDataTable5">
  <xs : complexType>
  <xs : sequence>
  <xs : element name="RQ" type="xs : string" minOccurs="0" />
  <xs : element name="RN" type="xs : string" minOccurs="0" />
  <xs : element name="CTD" type="xs : decimal" minOccurs="0" />
  </xs : sequence>
  </xs : complexType>
  </xs : element>
  </xs : choice>
  </xs : complexType>
  </xs : element>
  </xs : schema>

  
  

• lx

  Same here, I've got the same problem, the only suggestion I got was to add a parameter on the stored proc to determine which result set to return. Therefore the report datasource ends up with one dataset for each datatable or result set
  
  

• martin911

  Its possible to use stored procedures in rdl file.U can use any number of stored procedures.

  For that, you have to create datasets.For each dataset, you can call one stored procedure

  or simple Query text.

  Am working on Reporting service from last five months.We are using Plsql stored procedures

  for Oracle database.

  
  
  

• Quicker

  A Reporting Services dataset will only display the first table returned by a stored procedure, so you will have to create a dataset for each table you want created. I assume the Stored Proc accepts a parameter to determine which table you want to display or something similar

  cheers

  Ray

  
  

• Aero.NET

  Hi lalit_2007,

  
  In the Stored procedure we just have multiple SELECT statements. When the stored procedure runs each Select returns a table, so we end up with multiple tables in the final dataset.

  There are inbuilt Microsoft .NET XML functions that enable you to write the XML dataset out. The code snippett below shows how.

  But like you I'm still wondering how to use multi-tabled datasets in SSRS.

  regards,

  catz

  
  ...
  txtQuery = this.txtSELECT.Text.Trim() + " AND " + this.txtWHERE.Text.Trim() + " " + this.txtPOST.Text.Trim();
  DataSet ds = new DataSet("ReportDataSet");
  SqlConnection cn = new SqlConnection(strCon);
  cn.Open();
  SqlDataAdapter da = new SqlDataAdapter(txtQuery, cn);
  da.Fill(ds);
  NameTables(ref ds);//see fn below
  ds.WriteXml(this.rtbFile.Text.Trim(), System.Data.XmlWriteMode.WriteSchema);
  cn.Close();

  ...

  
  private void NameTables(ref DataSet ds)
  {
  int i = 0;
  foreach(DataTable tbl in ds.Tables)
  {
  if (i > 0)
  {
  tbl.TableName = "ReportDataTable" + i.ToString();
  }
  else
  {
  tbl.TableName = "ReportDataTable";
  }
  i++;
  }
  }
  

  
  

• wicked_mk

  Hi,

  You say that you can define 2 datasets, each with one stored procedure. That's a good feature and I understand the concept.

  In my case we have some stored procedures that have multiple (8 or 9) SELECT statements in them. Therefore a single stored procedure might return 8 or 9 tables within a single dataset. These tables correspond to the data for 8 or 9 subreports in Crystal. Crystal can intrinsically read these types of datasets and displays all the fields from all the tables in the designer - so its really easy.

  Will SSRS handle this type of multi-tabled single dataset from a sproc

  I'm thinking that I might have to define 8 or 9 datasets, each one pointing at the same stored procedure, but a different table wityhin the stored procedure - will this work

  thanks in advance,

  catz

  
  
• Using multiple stored procedures in one report