How to create a Report using 3.0

1

I am using Microsoft Sql Server Report Builder 3.0 and need to pull in an Xml data field node. My Datasource1 is the main table and the xml is in the column XmlDataField. The query below extracts the node I am interested in. Do I have to create a second datasource to retrieve the Xml or is there an easier way?

Select XmlDataField.value('(//*[local-name()="Text2Size50"])[1]', 'nvarchar(max)')
From tblDataPermit
Where Type = '72'

2
  1. You could create a stored procedure or make a direct call using that sql statement from within your report form
Select XmlDataField.value('(//*[local-name()="Text2Size50"])[1]', 'nvarchar(max)') as DisplayText
From tblDataPermit
Where Type = '72'
  1. In your report builder on the left hand side you will see a list of nodes
    Built-in Fields
    Parameters
    Data Sources. --> in this node right click and choose Add Data Source
  2. On Data Source Properties screen choose one of the two data sources
  3. After creating a data source then in DataSets node right click and Add Dataset..
  4. Then in Dataset Properties select Use a dataset embedded in my report. Choose the Data source you created in step #2 and under Query Type: choose stored procedure and find your stored procedure. Click Refresh Fields.
  5. After clicking OK. You will see your dataset and DisplayText field from your stored procedure
3

OK, I created DataSet2 and this seems to have worked, but if I try to add the field to the tablix in report builder I get an error message: Only fields from the current DataSet1 can be added.

4

you need DataSource that feeds your DataSet. so your dataset has to point to your DataSource.

  1. what do you have as DataSource? post here?
  2. What does your dataSet look like

Post as images that might help better see whats up

5

datasource.jpg1081×373 40.3 KB

6

ok back to the drawing board. Everytime you are posting your requirements are changing, hard to work with shifting requirements :slight_smile:

Lets take it from the top. What are you trying to do?

Do you solemnly (swear/affirm) that you will tell the truth, the whole truth, and nothing but the truth, (so help you God/under pains and penalties of perjury)? :grin:

What is the relationship between DataSet1 and DataSet2

7

I have the blindfold on and am standing against the brick wall, cigarette in mouth.

DataSet1 are column names from the Main Table. DataSet2 is the Xml column in the same table.

The Xml column contains info such as contact name, contact phone, etc.

I just want to pull selective data from the xml column and add it to the table.

In Dataset1 you can see the Xml column: XmlDataField.

8

ok time to take off the blindfold. time to see the light, haleluyer!

Only use one DataSet especially if it is from same table. As far as I know you cannot use 2 DataSets in one matrix.

9

Change DataSet1 from a direct table to a query - in that query include all of the columns you need for your report and the additional XML column.

Then reference that one dataset in your report.

It is also possible to create a derived column and add it to a dataset - that will be a bit trickier because you then have to parse the XML data using .NET code embedded in the expression but is doable (I believe). However - I wouldn't bother as long as you can get the data using a SQL query.