Hi I'm trying to create an SSRS report from sharepoint list using SOAP query GetListItems method I'm trying to create a report as below Column1 Column 2 Column3 Area1 Stream1 1 Stream2 0 Stream 3 0 Area2 Stream1 2 Stream2 3 Stream 3 0 Area3 Stream1 1 Stream2 0 Stream 3 4 Fig:1 But the report shows up as Column1 Column 2 Column3 Area1 Stream1 1 Area2 Stream1 2 Stream2 3 Area3 Stream1 1 Stream 3 4 Fig:2 since there are no values for the missing rows in my list. How to display a structure report as in Fig1 and assign "0" to count if the rows does not exist in the dataset.

Sugashini, If you can post you SQL Query for this DataSet in that case it will be more clear for us to help you out on this scenario. Normally, these results are fully dependent on your DataSet Output. Thanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Sugashini, Here will be your SQL Query for DataSet SELECT Column1, Column2, Column3 FROM dbo.Table_1 Union All SELECT 'Area1' AS Column1, 'Stream1' AS Column2, 0 AS Column3 Union All SELECT 'Area1' AS Column1, 'Stream2' AS Column2, 0 AS Column3 Union All SELECT 'Area1' AS Column1, 'Stream3' AS Column2, 0 AS Column3 UNION ALL SELECT 'Area2' AS Column1, 'Stream1' AS Column2, 0 AS Column3 Union All SELECT 'Area2' AS Column1, 'Stream2' AS Column2, 0 AS Column3 Union All SELECT 'Area2' AS Column1, 'Stream3' AS Column2, 0 AS Column3 UNION ALL SELECT 'Area3' AS Column1, 'Stream1' AS Column2, 0 AS Column3 Union All SELECT 'Area3' AS Column1, 'Stream2' AS Column2, 0 AS Column3 Union All SELECT 'Area3' AS Column1, 'Stream3' AS Column2, 0 AS Column3 Along with this in your report you have to group on Column1 & Column2. Column3 will be expression =Sum(Fields!Column3.Value) Here is output of my report. . Let me know if you have any issues Thanks, Sandip Please "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Sandip, Thank you for your reply. But I'm using the sharepoint integrated mode, the GetlistItems method in the Lists.asmx webservice. So I do not have the liberty to control the dataset.

Hi Sugashini, In this condition, please consider this work-around: access the data by using GetlistItems method, then export these records into one stage database table, finally, referencing Sandip's suggested query. Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.

Hi Sugashini, In this condition, please consider this work-around: access the data by using GetlistItems method, then export these records into one stage database table, finally, referencing Sandip's suggested query. Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.