Data Truncation in SSRS from Stored Proc.
HI,
I am calling SP into SSRS, The SP has one field, it generates XML code. I need to shred the XML data into rows but somehow my SP pulls only little amount of data. When I executed same SP in Management Studio, I am able to get all XML code.
Is there any way to shred XML data in SSRS coming from Stored Proc.
Please let me know if any body had similar issue before. Thank you.
August 31st, 2011 3:31pm
Hi Neo_Deep,
I had replied to your thread you posted. To assist you to solve your issue, I would demonstrate you step by step, I have tested
it in my testing environment, and it works fine. Please refer to the steps below:
Create procedure in your database.
CREATE
PROCEDURE [dbo].[Usp_Personaddress]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT
ON;
-- Insert statements for procedure here
Select*
From Person.Address
for xml
raw,type,root('Rows')
END
Create Dataset to retrieve Xml code.
1. Right-click the datasouce, select Add Dataset .
2. Name it as ds_Xml, specify the Query Type as Stored procedure and Procedure name as Usp_Personaddress.
3. Click Ok.
Create xml data source.
1. In
the Report Data, right-clickData Sources.
2. Then click Add Data Source.
3. In the Shared Data Source Properties dialog, name it as dataSource_Xml, select type XML, and leave the connection string blank.
4. Go to Credentials tab, check Use Windows Authentication (Integrated Security).
Create xml Parameter.
1. In the Report Data, right-click Parameters, select Add Parameter.
2. Name it as para_Xml, check the Internal item below Select parameter visibility category.
3. Switch to Default Values tab, Check the Get values from a query item, specify the dataset to ds_Xml, value to the Xml Code filed (such as ID).
4. Click ok.
Create dataset to show on report body.
1. Right-click the dataSource_Xml, select Add Dataset.
2. Name it as ds_Data, keep the Query type to Text.
3. Click the fx button right adjacent to the Query textbox, type in the expression below:
="<Query><XmlData>"+Parameters!para_Xml.Value+"</XmlData></Query>"
4. Switch to Fields, Click Add to add the fileds you want to query, and then click ok.
Field Name
Field Source
AddressID
AddressID
AddressLine1
AddressLine1
City
City
…
…
I hope this can help you, if you have anything unclear, please feel free to let me know.
Thanks,
Bill Lu
Please remember to mark the replies as answers if they help and unmark them if they provide no help.If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2011 4:55am
Hi Bill Lu, Thanks a lot for reply. It worked. I am able to bring XML Data from SP to SSRS. But I am loosing some data in some columns. I am getting waring:
[rsMissingFieldInDataSet] The dataset ‘XMLFields’ contains a definition for the Field ‘City’. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The dataset ‘XMLFields’ contains a definition for the Field ‘City’. The data extension returned an error during reading the field. There is no data for the field at position 8.
To avoid above warning I used following function:
Function NullAsZero(ByRef F As Field) As Double
If F.IsMissing Then
Return 0
Else
If F.Value Is Nothing Then
Return 0
Else
Return F.Value
End If
End If
End Function
Then I got bellow warning:
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘City.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.
September 6th, 2011 10:36am
Hi Bill Lu
This is my actual data:
<Materials>
<item
id="88"
name="I88">
<Materialtype
id="09"
name="Rubber" />
<MaterialStatus
id="21"
name="In complete" />
<StateGroup
id="31"
name="cvs" />
<TotalAmount>0.0000</TotalAmount>
<MaterialCost>0.0000</MaterialCost>
<LaunchDate>2013-06-01T00:00:00</LaunchDate>
<CostPerUnit>0.0000</CostPerUnit>
<MCost>0.0000</MCost>
<NumOfUnits>0.0000</NumOfUnits>
<NumOfProducts>0</NumOfProducts>
<MDCost>0.0000</MDCost>
<OverHeadCost>0.0000</OverHeadCost>
<RegulatoryCost>0.0000</RegulatoryCost>
<OtherCost>0.0000</OtherCost>
<CostComplete>0</CostComplete>
<Brand>Reebok, Nike (USA)</Brand>
<Attachment>0</Attachment>
</item>
<item
id="95"
name="P95">
<MaterialType
id="77"
name="Plastic" />
<Buy
id="21"
name="Micer" />
<MaterialStatus
id="10"
name="Launched" />
<StateGroup
id="32"
name="EMS" />
<Response
id="110"
name="No-Way" />
<TotalAmount>0.0000</TotalAmount>
<Cost>0.0000</Cost>
<Decision
id="81"
name="NP" />
<MaterialClass
id="41"
name="New" />
<LaunchDate>2019-02-20T00:00:00</LaunchDate>
<FileDate>2012-01-24T00:00:00</FileDate>
<Rationale
id="10"
name="Map 5" />
<CostPerUnit>0.0000</CostPerUnit>
<MCost>0.0000</MCost>
<NumOfUnits>0.0000</NumOfUnits>
<NumOfProducts>0</NumOfProducts>
<MDCost>0.0000</MDCost>
<OverHeadCost>0.0000</OverHeadCost>
<RegulatoryCost>0.0000</RegulatoryCost>
<OtherCost>0.0000</OtherCost>
<CostComplete>0</CostComplete>
<Category>4 – Maya Mfg.</Category>
<Brand>Nike (USA), Reebok (UK),
Puma (AUS)</Brand>
<Attachment>0</Attachment>
</item>
</Materials>
I tried your method, but in some columns I am not getting data at all(Just blank). I think it is due to two attributes in some elements. Is there any way solve this? Please let me know.
Warnings I got:
[rsMissingFieldInDataSet] The dataset ‘XMLFields’ contains a definition for the Field ‘MaterialStatus’. This field is missing from the returned result set from the data source.
[rsErrorReadingDataSetField] The dataset ‘XMLFields’ contains a definition for the Field ‘MaterialStatus’. The data extension returned an error during reading the field. There is no data for the field at position 8.
To avoid above warning I used following function:
Function NullAsZero(ByRef F As Field) As Double
If F.IsMissing Then
Return 0
Else
If F.Value Is Nothing Then
Return 0
Else
Return F.Value
End If
End If
End Function
Then I got bellow warning:
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘MaterialStatus.Paragraphs[0].TextRuns[0]’ contains an error: Unable to cast object of type 'System.String' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.
Free Windows Admin Tool Kit Click here and download it now
September 6th, 2011 11:52pm
Hi Neo_Deep,
I too had a similar situation wherein i tried to use element path to solve this. You might not get data into columns which have id and name because we have element nodes "item, Materialtype and Material status" all of which are having the same attributes
id and name. So try to keep an element path like below to get data from all attributes:
<ElementPath IgnoreNamespaces="true"> Materials{}/item{@id,@name,MaterialStatus{@id, @name}, Materialtype{@id, @name},StateGroup{@id,@name},TotalAmount,MaterialCost,Buy{@id,@name},LaunchDate,CostPerUnit,MCost,NumOfUnits,NumOfProducts,MDCost,OverHeadCost,RegulatoryCost,OtherCost,CostComplete,Brand,Attachement,Decision{@id,@name},MaterialClass{@id,@name},FileDate,Rationale,Category}</ElementPath>
Please refer to this link if you need help regarding elemental path:-
http://msdn.microsoft.com/en-us/library/ms365158.aspx
had similar question at :http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/7be34661-e90f-4f8c-b914-e21647d0af6d
Thanks Raj Lanka
September 12th, 2011 1:01pm