Need to create <Query> statement programmatically

Need to create <Query> statement programmatically

• tyaramis

  Hi Videl,
  
  There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.
  
  Example Dataset Query string: =Parameters!QueryParameter.Value
  
  Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.
  
  
  Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.
  
  
  

• SteveBouffard

  When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick' When previewing the report, they get wiped out of the dataset once in a while.
  
  

• ComputerSolutions

  Hi Ian,

  OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

  ="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

  When I preview the report, I get the following error message:

  An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

  It looks like it is not finding the data before it tries to populate the fields on the report.

  I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code Thanks!!!

  Marlene

  
  

• Xtian

  Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.
  
  When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"
  
  Try using these custom dataset field.
  
  

  FieldName	Type	Value
  ReportID	Database field	ReportID
  ReportName	Database field	ReportName
  ReportFriendlyName	Database field	ReportFriendlyName

  
  
  More information on custom dataset fields:
  http://msdn2.microsoft.com/en-us/library/ms189677.aspx
  
  
  
  

• MeghaP

  I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.
  
  Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.
  
  Ian
  
  
  
  
  

• JaJ

  Hi,

  I didn't quite understand that.

  I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

  <ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

  When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

  I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

  FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
  -------------------------------------------

  <Query>
  <SoapAction>http://tempuri.org/GetDataset</SoapAction>
  <Method Namespace="http://tempuri.org/" Name="GetDataset">
  <Parameters>
  <Parameter Name="sql" Type="String">
  <DefaultValue></DefaultValue>
  </Parameter>
  </Parameters>
  </Method>
  <ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
  </Query>
  -------------------------------------------
  Here is a sample output from my webservice
  -------------------------------------------
  < xml version="1.0" encoding="utf-8" >

  - <DataSet xmlns="http://tempuri.org/">

  - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

  - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

  - <xs:complexType>

  - <xs:choice minOccurs="0" maxOccurs="unbounded">

  - <xs:element name="Results">

  - <xs:complexType>

  - <xs:sequence>

  <xs:element name="ReportID" type="xs:int" minOccurs="0" />

  <xs:element name="ReportName" type="xs:string" minOccurs="0" />

  <xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

  <xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

  <xs:element name="Display" type="xs:boolean" minOccurs="0" />

  <xs:element name="DataViewName" type="xs:string" minOccurs="0" />

  <xs:element name="Example1" type="xs:boolean" minOccurs="0" />

  </xs:sequence>

  </xs:complexType>

  </xs:element>

  </xs:choice>

  </xs:complexType>

  </xs:element>

  </xs:schema>

  - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

  - <NewDataSet xmlns="">

  - <Results diffgr:id="Results1" msdata:rowOrder="0">

  <ReportID>10</ReportID>

  <ReportName>Reports_ByReportID1</ReportName>

  <ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

  <ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

  <Display>true</Display>

  <DataViewName>Reports</DataViewName>

  <Example1>false</Example1>

  </Results>

  </NewDataSet>

  </diffgr:diffgram>

  </DataSet>
  -----------------------------------

  And here are all my dataset fields:

  FieldName Type Value
  ReportID Database field =Fields!ReportID.Value
  ReportName Database field =Fields!ReportName.Value
  ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

  RowID Database field =id

  
  

• Vinny_1973

  Hi Ian,

  I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

  Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up

  
  

• Aghashahi

  Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query Since this is a dataset being returned, can you try adding the following custom dataset field,
  
  Name = RowID, DatabaseField = id
  
  This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,
  
  <ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>
  
  
  
  
  
  
  

• Renaud Martinon

  You are the best!!!!
  
• Need to create <Query> statement programmatically