Subreports within table/matrix cells are ignored.
I have Report with subReport,in the designer its looked well, but when I export the report to excell format I get this error,is it poosible export report with sub report to excell?thanks!
April 25th, 2007 6:48pm

I do not believe subreports are exported to Excel. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=287789&SiteID=1
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2007 7:47pm

I read in some places that put the subReport in List and then the problem fixed.I never use the list.I look for a visual Article on list in report services.thanks!
April 26th, 2007 9:11am

Subreports within table/matrix are not exported to Excel in SSRS 2000/2005. The closest you can get is to look into changingthe design of the report to use lists / nested lists or not using subreports if you want to export to Excel also. This limitation of the Excel renderer will be removed in a future SSRS release. -- Robert
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2007 2:47am

Robert I need your help with something that is really causing me problems. My customer insists on having a subreport placed alongside the main table. The subreport is linked through 3 params. How can I resolve this? If I put the subreport in the details row of the main table, it shows fine (since it is grouped by cost center) if there is only one cost center. If there are more it repeats (not good). If I put both subreport and main table in a list, the main table repeats according to cost center. I need the subreport to be side-to-side next to the main table. I also thought maybe just eliminating the subreport all together and just pulling the full data from two different datasources, but that doesn't work, either. How can I accomplish this? I have looked through tones of websites, etc. and can not find what to do. Your prompt reply would be truly appreciated.
May 23rd, 2007 10:18pm

Hey Robert, do you have any idea when this Excel renderer limitation will be fixed? I need to weigh redesigning some reports or waiting to upgrade! Thanks, Mike
Free Windows Admin Tool Kit Click here and download it now
October 6th, 2007 6:27pm

SSRS 2008 (shipped as part of SQL Server 2008) provides a full solution to this, since it has a new engine and rendering extensions. Excel rendering will now always render subreports contents. You can try this for instance already in the July CTP: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395 Note that the report viewer controls in VS 2008 RTM is based on an updated versionof the 2005 engine, but not the new engine yet. See the following thread for more details on the viewer control road map: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2069531&SiteID=1 -- Robert
October 8th, 2007 3:22am

I experience this problem too. My company is a late technology adopter meaning thatnew versions have tohave been released for a year before we can begin to utilize it. Plus, there is always the issue of the work regarding migrating everything...We cannot even begin to consider version 2008 as a solution to anything. Is there any solution that is current with 2005 versions?
Free Windows Admin Tool Kit Click here and download it now
December 18th, 2007 8:18pm

We need this fix. Any news whether 2005 will have it? Or are we forced to upgrade?
September 12th, 2008 11:42pm

IS IT RESOLVED IN SSRS 2008 BECAUSE I MADE A REPORT WITH SUBREPORT CONTAINING CHARTS NOW WHEN EXPORTED TO XLS CHARTS ARE NOT DISPLAYED.... WE WILL MIGRATE TO SSRS 2008 IF THIS IS NOT POSSIBLE..
Free Windows Admin Tool Kit Click here and download it now
September 19th, 2008 3:20pm

USE THE LIST BOX and paste the subreport in it... and it will be exported to XLS. but there will be issues making a multipage report...
December 2nd, 2008 12:12pm

Hi, There is a work around for your problem.First place your sub report in a container like (List view or rectangle) then place this container in metrics.There you go run the report and export to excel, it'll surly work.ThanksSaurabhSaurabhkantdixit
Free Windows Admin Tool Kit Click here and download it now
March 18th, 2009 12:31am

Hi, There is a work around for your problem.First place your sub report in a container like (List view or rectangle) then place this container in metrics.There you go run the report and export to excel, it'll surly work.ThanksSaurabh Saurabhkantdixit It's not work for me, Saurabhkantdixit.
September 7th, 2009 4:59am

Hi, There is a work around for your problem.First place your sub report in a container like (List view or rectangle) then place this container in metrics.There you go run the report and export to excel, it'll surly work.ThanksSaurabh Saurabhkantdixit It's not work for me, Saurabhkantdixit. I works for me. Thanks a million
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2009 1:37am

Hi, There is a work around for your problem.First place your sub report in a container like (List view or rectangle) then place this container in metrics.There you go run the report and export to excel, it'll surly work.ThanksSaurabh Saurabhkantdixit But, We must resolve the problem about layout of REPORT. :|
January 8th, 2010 10:32am

Hi!My solution to this problems was very simple. I don't know is suit for all situations but, you'll could try, and tell me what do you think about this.I was using a Table, my main report use 4 subreports, I created 1 row and merge the columns, put a rectangle and after put all subreports in the rectangle, and that is.When I export the report to excel, all subreports were shownRegards,Alex
Free Windows Admin Tool Kit Click here and download it now
January 28th, 2010 8:44pm

SSRS 2008 (shipped as part of SQL Server 2008) provides a full solution to this, since it has a new engine and rendering extensions. Excel rendering will now always render subreports contents. You can try this for instance already in the July CTP: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395 Note that the report viewer controls in VS 2008 RTM is based on an updated version of the 2005 engine, but not the new engine yet. See the following thread for more details on the viewer control road map: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2069531&SiteID=1 -- Robert hello is there any update or solution for SQL Server 2000 ?
April 21st, 2010 6:10pm

Hi All, I have one report in 2005, where I have used Sub Report in Table. When I am Exporting It to PDF It is showing Subreport Data, but When I am exporting it to Excel, Sub Report Data is not coming to Excel and it showing error (Subreports within table/matrix cells are ignored.) in excel. Please anyone can suggest how to resolve this issue. My requirment is different. I have a group by for Sub report. If I will take it out from Table, sub report data in coming only at the end of the report, not for each grouped values. For Example: Name Dept Name Group by ID Name value Dept Name value Sub Report (Group by ID) See without List Box, Table Or matrix I can't achieve this but If I am using List Box, Table Or matrix, getting same error (Subreports within table/matrix cells are ignored.) Suggest me any work around for this. Thanks Shiven:)
Free Windows Admin Tool Kit Click here and download it now
October 20th, 2010 2:32pm

i have tried using the list box in my report. but still i have the problem of getting subreport exported in main report to excel.
February 16th, 2011 10:02am

Hi All, I have one report in 2005, where I have used Sub Report in Table. When I am Exporting It to PDF It is showing Subreport Data, but When I am exporting it to Excel, Sub Report Data is not coming to Excel and it showing error (Subreports within table/matrix cells are ignored.) in excel. Please anyone can suggest how to resolve this issue. My requirment is different. I have a group by for Sub report. If I will take it out from Table, sub report data in coming only at the end of the report, not for each grouped values. For Example: Name Dept Name Group by ID Name value Dept Name value Sub Report (Group by ID) See without List Box, Table Or matrix I can't achieve this but If I am using List Box, Table Or matrix, getting same error (Subreports within table/matrix cells are ignored.) Suggest me any work around for this. ANS: Hi, I got one work around for this. Take sub report out of table and put it separate and then table and sub report keep in List Box and do required grouping. I have noticed, for few system even you keep sub report in list and put this list in table it works. Hi Use below code to create report in 2005. I have used Adventure Works Data base. Code: Main Report code which is Calling Sub report: <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="AdventureWorks"> <rd:DataSourceID>2762b2b5-660d-4bb8-989f-ca179e9b7184</rd:DataSourceID> <DataSourceReference>AdventureWorks</DataSourceReference> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>1mm</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>f5add235-4253-4575-821c-65ce1cf77ff7</rd:ReportID> <DataSets> <DataSet Name="AD_WORK"> <Fields> <Field Name="AddressID"> <DataField>AddressID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="AddressLine1"> <DataField>AddressLine1</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="AddressLine2"> <DataField>AddressLine2</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="City"> <DataField>City</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="StateProvinceID"> <DataField>StateProvinceID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="PostalCode"> <DataField>PostalCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="rowguid"> <DataField>rowguid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="ModifiedDate"> <DataField>ModifiedDate</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>AdventureWorks</DataSourceName> <CommandText>SELECT TOP (20) AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, rowguid, ModifiedDate FROM Person.Address</CommandText> </Query> </DataSet> </DataSets> <Code /> <Width>7.37062in</Width> <Body> <ReportItems> <List Name="list1"> <Left>0.07875in</Left> <DataSetName>AD_WORK</DataSetName> <ReportItems> <Table Name="table1"> <ZIndex>1</ZIndex> <DataSetName>AD_WORK</DataSetName> <TableGroups> <TableGroup> <Grouping Name="table1_Group1"> <GroupExpressions> <GroupExpression>1</GroupExpression> </GroupExpressions> </Grouping> </TableGroup> </TableGroups> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!AddressID.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!City.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox6"> <rd:DefaultName>textbox6</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=Fields!ModifiedDate.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Details> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> </Style> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>Address ID</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox2"> <rd:DefaultName>textbox2</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>4</ZIndex> <CanGrow>true</CanGrow> <Value>City</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value>Modified Date</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> <TableColumns> <TableColumn> <Width>1.19792in</Width> </TableColumn> <TableColumn> <Width>1.75in</Width> </TableColumn> <TableColumn> <Width>1.65375in</Width> </TableColumn> </TableColumns> <Height>0.5in</Height> <Left>0.00688in</Left> </Table> <Subreport Name="subreport1"> <Top>0.58375in</Top> <ReportName>SUB_REPORT</ReportName> </Subreport> </ReportItems> <Top>0.07875in</Top> <Width>4.60854in</Width> <Grouping Name="list1_Details_Group"> <GroupExpressions> <GroupExpression>1</GroupExpression> </GroupExpressions> </Grouping> <Height>0.89875in</Height> </List> </ReportItems> <Height>4.1025in</Height> </Body> <Language>en-US</Language> <TopMargin>1in</TopMargin> </Report> Thanks Shiven:)
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2011 2:09am

Create main report and sub report in 2005 and run the report and export it in Excel. You will not get error message. Sub Report code: <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="AdventureWorks"> <rd:DataSourceID>2762b2b5-660d-4bb8-989f-ca179e9b7184</rd:DataSourceID> <DataSourceReference>AdventureWorks</DataSourceReference> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>1mm</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>f5add235-4253-4575-821c-65ce1cf77ff7</rd:ReportID> <DataSets> <DataSet Name="AD_WORK"> <Fields> <Field Name="AddressID"> <DataField>AddressID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="AddressLine1"> <DataField>AddressLine1</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="AddressLine2"> <DataField>AddressLine2</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="City"> <DataField>City</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="StateProvinceID"> <DataField>StateProvinceID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="PostalCode"> <DataField>PostalCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="rowguid"> <DataField>rowguid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="ModifiedDate"> <DataField>ModifiedDate</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>AdventureWorks</DataSourceName> <CommandText>SELECT TOP (20) AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, rowguid, ModifiedDate FROM Person.Address</CommandText> </Query> </DataSet> </DataSets> <Width>4.8825in</Width> <Body> <ReportItems> <Textbox Name="textbox7"> <rd:DefaultName>textbox7</rd:DefaultName> <Top>0.11813in</Top> <Width>1in</Width> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.43312in</Left> <Height>0.25in</Height> <Value /> </Textbox> Thanks Shiven:) <DataSetName>AD_WORK</DataSetName> <Top>0.66938in</Top> <Width>4.625in</Width> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!PostalCode.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!City.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox6"> <rd:DefaultName>textbox6</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=Fields!ModifiedDate.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Details> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> </Style> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>PostalCode</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox2"> <rd:DefaultName>textbox2</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>4</ZIndex> <CanGrow>true</CanGrow> <Value>City</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value>Modified Date</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> <TableColumns> <TableColumn> <Width>1.125in</Width> </TableColumn> <TableColumn> <Width>1.75in</Width> </TableColumn> <TableColumn> <Width>1.75in</Width> </TableColumn> </TableColumns> <Left>0.125in</Left> </ReportItems> <Height>1.16938in</Height> </Body> <Language>en-US</Language> <TopMargin>1in</TopMargin> </Report>
February 24th, 2011 2:46am

Sub Report code <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="AdventureWorks"> <rd:DataSourceID>2762b2b5-660d-4bb8-989f-ca179e9b7184</rd:DataSourceID> <DataSourceReference>AdventureWorks</DataSourceReference> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>1mm</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>f5add235-4253-4575-821c-65ce1cf77ff7</rd:ReportID> <DataSets> <DataSet Name="AD_WORK"> <Fields> <Field Name="AddressID"> <DataField>AddressID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="AddressLine1"> <DataField>AddressLine1</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="AddressLine2"> <DataField>AddressLine2</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="City"> <DataField>City</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="StateProvinceID"> <DataField>StateProvinceID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="PostalCode"> <DataField>PostalCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="rowguid"> <DataField>rowguid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="ModifiedDate"> <DataField>ModifiedDate</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>AdventureWorks</DataSourceName> <CommandText>SELECT TOP (20) AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, rowguid, ModifiedDate FROM Person.Address</CommandText> </Query> </DataSet> </DataSets> <Code /> <Width>4.725in</Width> <Body> <ReportItems> <Table Name="table1"> <DataSetName>AD_WORK</DataSetName> <Top>0.07875in</Top> <Width>4.625in</Width> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!PostalCode.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!City.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox6"> <rd:DefaultName>textbox6</rd:DefaultName> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontFamily>Verdana</FontFamily> <FontSize>8pt</FontSize> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=Fields!ModifiedDate.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Details> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> </Style> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>PostalCode</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox2"> <rd:DefaultName>textbox2</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>4</ZIndex> <CanGrow>true</CanGrow> <Value>City</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <Color>White</Color> <BackgroundColor>Maroon</BackgroundColor> <BorderStyle> <Default>Solid</Default> </BorderStyle> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value>Modified Date</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> <TableColumns> <TableColumn> <Width>1.125in</Width> </TableColumn> <TableColumn> <Width>1.75in</Width> </TableColumn> <TableColumn> <Width>1.75in</Width> </TableColumn> </TableColumns> <Height>0.5in</Height> <Left>0.03937in</Left> </Table> </ReportItems> <Height>0.63in</Height> </Body> <Language>en-US</Language> <TopMargin>1in</TopMargin> </Report> Thanks Shiven:)
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2011 12:31am

Hi S Kumar Dubey, Thanks for your immediate response. but i have doubt regarding the sub report issue. The main report and the sub report data is rendering seperately. In my main report, i m getting few columns from sub report. so, i m getting main report data first and in the last pages i m getting the sub report data. can you help me out. thanks a lot.
March 3rd, 2011 11:25am

Hi Awadbensaleh, Please can you provide me the Layout of report? 1st keep your main report and below main report, you keep subreport and put both main and sub report in List box and provide group to list box if there is any else give dummy group to list like 1 or 0.Thanks Shiven:)
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2011 1:07am

aHi Shiven , Please find the code below For main report. <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="CRM_Data"> <rd:DataSourceID>3ef44b2e-4401-449d-aa22-08db1d3d1b8e</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=.;Initial Catalog=MSCRM</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <ReportParameters> <ReportParameter Name="CRM_FilterText"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FullName"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>userinfo</DataSetName> <ValueField>fullname</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="CRM_CalendarType"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>CalendarType</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatDate"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>DateFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatTime"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>TimeFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="StartDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>Start Date</Prompt> </ReportParameter> <ReportParameter Name="EndDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>End Date</Prompt> </ReportParameter> <ReportParameter Name="CRM_FilteredSystemUser"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select systemuser0.* from FilteredSystemUser as systemuser0</Value> </Values> </DefaultValue> </ReportParameter> <ReportParameter Name="CRM_FilteredAppointment"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select appointment0.* from FilteredAppointment as appointment0</Value> </Values> </DefaultValue> </ReportParameter> </ReportParameters> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>0.03125in</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>b9fb94db-3087-456a-833e-0ed0735eb704</rd:ReportID> <DataSets> <DataSet Name="userinfo"> <Fields> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select fullname from FilteredSystemUser where systemuserid = dbo.fn_FindUserGuid()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="DSNumbandCurrency"> <Fields> <Field Name="DateFormat"> <DataField>DateFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="TimeFormat"> <DataField>TimeFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberLanguageCode"> <DataField>NumberLanguageCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CalendarType"> <DataField>CalendarType</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_0_Precision"> <DataField>NumberFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_1_Precision"> <DataField>NumberFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_2_Precision"> <DataField>NumberFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_3_Precision"> <DataField>NumberFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_4_Precision"> <DataField>NumberFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_5_Precision"> <DataField>NumberFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_0_Precision"> <DataField>CurrencyFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_1_Precision"> <DataField>CurrencyFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_2_Precision"> <DataField>CurrencyFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_3_Precision"> <DataField>CurrencyFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_4_Precision"> <DataField>CurrencyFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_5_Precision"> <DataField>CurrencyFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select * from dbo.fn_GetFormatStrings()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="ds_Contacts"> <Fields> <Field Name="businessunitidname"> <DataField>businessunitidname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="systemuserid"> <DataField>systemuserid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="eu_reporthubname"> <DataField>eu_reporthubname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="activityid"> <DataField>activityid</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="subject"> <DataField>subject</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText> declare @EndDate_str varchar(30); set @EndDate_Str=@EndDate+' 23:59:59'; declare @SQL varchar(4000); SET @SQL = 'SELECT UserSys.businessunitidname, UserSys.systemuserid, UserSys.eu_reporthubname, Convert(VarChar(50), AP.activityID) as [activityid], UserSys.fullname, AP.subject FROM ('+@CRM_FilteredSystemUser+') AS UserSys LEFT OUTER JOIN ('+@CRM_FilteredAppointment+') AS AP ON UserSys.systemuserid = AP.ownerid WHERE (UserSys.eu_securityrole LIKE ''%RAM%'') and UserSys.eu_reporthub is not null and AP.scheduledend &gt;= '''+cast(@StartDate as varchar(30))+''' and AP.scheduledend &lt;= '''+@EndDate_Str +''' UNION ALL SELECT SU.businessunitidname, SU.systemuserid, SU.eu_reporthubname, null as [activityid], SU.fullname, null as [subject] FROM ('+@CRM_FilteredSystemUser+') AS SU Inner join ('+@CRM_FilteredAppointment+') AS AP ON SU.systemuserid = AP.ownerid where (SU.eu_securityrole LIKE ''%RAM%'') and SU.eu_reporthub is not null ' EXEC (@SQL) </CommandText> <QueryParameters> <QueryParameter Name="@CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </QueryParameter> <QueryParameter Name="@CRM_FilteredAppointment"> <Value>=Parameters!CRM_FilteredAppointment.Value</Value> </QueryParameter> <QueryParameter Name="@StartDate"> <Value>=Parameters!StartDate.Value</Value> </QueryParameter> <QueryParameter Name="@EndDate"> <Value>=Parameters!EndDate.Value</Value> </QueryParameter> </QueryParameters> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> <Code> Function CalcPct(Num, Den) Dim RetVal as Decimal if Den = 0 then RetVal = 0 else RetVal = Num/Den End if Return RetVal End Function </Code> <Width>11.4375in</Width> <Body> <ReportItems> <List Name="list1"> <ZIndex>1</ZIndex> <Left>0.09375in</Left> <DataSetName>ds_Contacts</DataSetName> <ReportItems> <List Name="list2"> <ZIndex>1</ZIndex> <Left>6.375in</Left> <DataSetName>ds_Contacts</DataSetName> <ReportItems> <Table Name="table1"> <DataSetName>ds_Contacts</DataSetName> <Top>0.0625in</Top> <TableGroups> <TableGroup> <Grouping Name="table1_Group1"> <GroupExpressions> <GroupExpression>=Fields!eu_reporthubname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!eu_reporthubname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> </TableGroup> <TableGroup> <Grouping Name="table1_Group2"> <GroupExpressions> <GroupExpression>=Fields!businessunitidname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!businessunitidname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> </TableGroup> <TableGroup> <Grouping Name="table1_Group3"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!fullname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Subreport Name="subreport8"> <Parameters> <Parameter Name="ownerid"> <Value>=Fields!systemuserid.Value</Value> </Parameter> <Parameter Name="fromdate"> <Value>=Parameters!StartDate.Value</Value> </Parameter> <Parameter Name="todate"> <Value>=Parameters!EndDate.Value</Value> </Parameter> <Parameter Name="businessunitidname"> <Value>=Fields!businessunitidname.Value</Value> </Parameter> <Parameter Name="eu_reporthubname"> <Value>=Fields!eu_reporthubname.Value</Value> </Parameter> <Parameter Name="CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </Parameter> </Parameters> <ReportName>Calls per field Day_SubReport_ListBox</ReportName> <Style> <Color>White</Color> <BorderColor> <Default>#9e9e9e</Default> </BorderColor> </Style> </Subreport> </ReportItems> </TableCell> </TableCells> <Height>0.1875in</Height> </TableRow> </TableRows> </Footer> </TableGroup> </TableGroups> <Width>0.89062in</Width> <TableColumns> <TableColumn> <Width>0.89062in</Width> </TableColumn> </TableColumns> <Height>0.1875in</Height> <Left>0.14063in</Left> </Table> </ReportItems> <Top>1.46875in</Top> <Width>1.17708in</Width> <Style> <BorderColor> <Default>#9e9e9e</Default> </BorderColor> </Style> <Grouping Name="list2_Details_Group"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> <Height>0.3125in</Height> </List> <Table Name="table2"> <DataSetName>ds_Contacts</DataSetName> <Top>0.64063in</Top> <TableGroups> <TableGroup> <Grouping Name="ReportingHub"> <GroupExpressions> <GroupExpression>=Fields!eu_reporthubname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!eu_reporthubname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox11"> <rd:DefaultName>textbox11</rd:DefaultName> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>14</ZIndex> <CanGrow>true</CanGrow> <Value>=iif( IsNothing(Fields!eu_reporthubname.Value), "No Hub Assigned", "Hub: " &amp; Fields!eu_reporthubname.Value)</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox16"> <rd:DefaultName>textbox16</rd:DefaultName> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>13</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox14"> <rd:DefaultName>textbox14</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>12</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> </TableGroup> <TableGroup> <Grouping Name="BusinessUnit"> <GroupExpressions> <GroupExpression>=Fields!businessunitidname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!businessunitidname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>11</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!businessunitidname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox19"> <rd:DefaultName>textbox19</rd:DefaultName> <Style> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>10</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>9</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox47"> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!businessunitidname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Subreport Name="subreport2"> <Parameters> <Parameter Name="fromdate"> <Value>=Parameters!StartDate.Value</Value> </Parameter> <Parameter Name="todate"> <Value>=Parameters!EndDate.Value</Value> </Parameter> <Parameter Name="businessunitidname"> <Value>=Fields!businessunitidname.Value</Value> </Parameter> <Parameter Name="eu_reporthubname"> <Value>=Fields!eu_reporthubname.Value</Value> </Parameter> <Parameter Name="CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </Parameter> </Parameters> <ReportName>Calls per field Day_SubReport_ListBox</ReportName> <Style> <Color>White</Color> <BorderColor> <Default>#9e9e9e</Default> </BorderColor> </Style> <ZIndex>4</ZIndex> </Subreport> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox50"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>g</Format> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value>=CountDistinct(Fields!activityid.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>8</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>7</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox13"> <rd:DefaultName>textbox13</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>6</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.125in</Height> </TableRow> </TableRows> </Footer> </TableGroup> <TableGroup> <Grouping Name="RAM"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!fullname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox7"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!fullname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Subreport Name="subreport1"> <Parameters> <Parameter Name="ownerid"> <Value>=Fields!systemuserid.Value</Value> </Parameter> <Parameter Name="fromdate"> <Value>=Parameters!StartDate.Value</Value> </Parameter> <Parameter Name="todate"> <Value>=Parameters!EndDate.Value</Value> </Parameter> <Parameter Name="businessunitidname"> <Value>=Fields!businessunitidname.Value</Value> </Parameter> <Parameter Name="eu_reporthubname"> <Value>=Fields!eu_reporthubname.Value</Value> </Parameter> <Parameter Name="CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </Parameter> </Parameters> <ReportName>Calls per field Day_SubReport_ListBox</ReportName> <Style> <Color>White</Color> <BorderColor> <Default>#9e9e9e</Default> </BorderColor> </Style> <ZIndex>1</ZIndex> </Subreport> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox17"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>g</Format> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=CountDistinct(Fields!activityid.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.3125in</Height> </TableRow> </TableRows> </Footer> </TableGroup> </TableGroups> <Width>5.75in</Width> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox32"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>17</ZIndex> <CanGrow>true</CanGrow> <Value>RAM</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox33"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>16</ZIndex> <CanGrow>true</CanGrow> <Value>Total # of days on the field</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox35"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>15</ZIndex> <CanGrow>true</CanGrow> <Value># of Calls</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <TableColumns> <TableColumn> <Width>3.59375in</Width> </TableColumn> <TableColumn> <Width>1.03125in</Width> </TableColumn> <TableColumn> <Width>1.125in</Width> </TableColumn> </TableColumns> <Height>1.4375in</Height> </Table> </ReportItems> <Top>1.34375in</Top> <Grouping Name="list1_Details_Group"> <GroupExpressions> <GroupExpression>0</GroupExpression> </GroupExpressions> </Grouping> </List> <Rectangle Name="rectangle1"> <ReportItems> <Textbox Name="txtReportHeaderLabel"> <Top>0.0625in</Top> <Width>3.5in</Width> <Style> <FontFamily>tahoma</FontFamily> <FontSize>14pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.3125in</Height> <Value>Calls per field day</Value> </Textbox> <Rectangle Name="rectangle4"> <Visibility> <Hidden>=IIF(IsNothing(Parameters!CRM_FilterText.Value ) or ( Parameters!CRM_FilterText.Value ) = "" or (Parameters!CRM_FilterText.Value) = " ", True, False)</Hidden> </Visibility> <ReportItems> <Textbox Name="txtFilterSummaryValue"> <Visibility> <Hidden>true</Hidden> <ToggleItem>txtFilterSummary</ToggleItem> </Visibility> <Top>0.34375in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>=Parameters!CRM_FilterText.Value</Value> </Textbox> <Textbox Name="txtFilterSummary"> <Top>0.03125in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>Filter Summary</Value> </Textbox> </ReportItems> <Top>0.4375in</Top> <Width>4.25in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> </Rectangle> </ReportItems> <Width>7.875in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> <Height>1.125in</Height> </Rectangle> </ReportItems> <Height>5.875in</Height> </Body> <CustomProperties> <CustomProperty> <Name>Custom</Name> <Value>&lt;MSCRM xmlns="mscrm"&gt;&amp;lt;ReportFilter&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredSystemUser"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="systemuser"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredAppointment"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="appointment"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;/ReportFilter&amp;gt;&lt;/MSCRM&gt;</Value> </CustomProperty> </CustomProperties> <Language>en-US</Language> <PageFooter> <PrintOnFirstPage>true</PrintOnFirstPage> <ReportItems> <Textbox Name="txtPageCount"> <Top>0.01172in</Top> <Width>1.8125in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Right</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Left>6in</Left> <Height>0.21875in</Height> <Value>=String.Format("Page {0} of {1}", Format(Globals!PageNumber, "N0"), Format(Globals!TotalPages, "N0"))</Value> </Textbox> <Textbox Name="txtUserIDLabel"> <Top>0.01172in</Top> <Width>2.5in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>2.75in</Left> <Height>0.21875in</Height> <Value>=String.Format(System.Globalization.CultureInfo.InvariantCulture, "Prepared by: {0}", Parameters!CRM_FullName.Value)</Value> </Textbox> <Textbox Name="txtExecutionDateTimeLabel"> <Top>0.01953in</Top> <Width>1.85935in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>=Parameters!CRM_FormatDate.Value + " " + Parameters!CRM_FormatTime.Value</Format> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> <Calendar>=Parameters!CRM_CalendarType.Value</Calendar> </Style> <CanGrow>true</CanGrow> <Height>0.21875in</Height> <Value>=Now()</Value> </Textbox> </ReportItems> <Height>0.25in</Height> <PrintOnLastPage>true</PrintOnLastPage> </PageFooter> </Report> Sub Report Code : There is only one TextBox in the sub report. and i m passing parameters to my sub report from main report. <?xml version="1.0" encoding="utf-8"?><Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="CRM_Data"> <rd:DataSourceID>4bf0cd9d-7a3a-44f2-b15a-187e7292bc8d</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=*;Initial Catalog=MSCRM</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> </DataSource> </DataSources> <InteractiveHeight>0.25in</InteractiveHeight> <ReportParameters> <ReportParameter Name="CRM_FilteredSystemUser"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select systemuser0.* from FilteredSystemUser as systemuser0</Value> </Values> </DefaultValue> <Prompt></Prompt> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="ownerid"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> <Prompt>ownerid</Prompt> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="fromdate"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>1/1/2010</Value> </Values> </DefaultValue> <Prompt>fromdate</Prompt> </ReportParameter> <ReportParameter Name="todate"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>9/13/2010</Value> </Values> </DefaultValue> <Prompt>todate</Prompt> </ReportParameter> <ReportParameter Name="businessunitidname"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> <Prompt>businessunitidname</Prompt> </ReportParameter> <ReportParameter Name="eu_reporthubname"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> <Prompt>eu_reporthubname</Prompt> </ReportParameter> <ReportParameter Name="CRM_FullName"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>userinfo</DataSetName> <ValueField>fullname</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt></Prompt> </ReportParameter> <ReportParameter Name="CRM_FilterText"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> <Prompt></Prompt> </ReportParameter> </ReportParameters> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>1.25in</InteractiveWidth> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>af3fdba9-ccec-47ee-8e49-4aff59dbe6aa</rd:ReportID> <PageWidth>1.25in</PageWidth> <DataSets> <DataSet Name="CRM_fieldsDay"> <Fields> <Field Name="NOOfWorkingDays"> <DataField>NOOfWorkingDays</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText> declare @SQLbu varchar(100) SET @SQLbu = '' declare @SQLown varchar(100) SET @SQLown = '' declare @SQLhub varchar(100) SET @SQLhub = '' IF @businessunitidname IS NOT NULL SET @SQLbu = @SQLbu + ' AND SU.businessunitidname = '''+@businessunitidname+''' ' IF @ownerid IS NOT NULL SET @SQLown = @SQLown + ' AND filteredEU_attendanceentry.ownerid = '''+@ownerid+''' ' IF @eu_reporthubname IS NOT NULL SET @SQLhub = @SQLhub + ' AND SU.eu_reporthubname = '''+@eu_reporthubname+''' ' declare @SQL varchar(4000); SET @SQL = ';with cte as ( select ''eu_mondayamid'' as WeekName,1 as WeekVal union all select ''eu_mondaypmid'',1 union all select ''eu_tuesdayamid'',2 union all select ''eu_tuesdaypmid'',2 union all select ''eu_wednesdayamid'',3 union all select ''eu_wednesdaypmid'',3 union all select ''eu_thursdayamid'',4 union all select ''eu_thursdaypmid'',4 union all select ''eu_fridayamid'',5 union all select ''eu_fridaypmid'',5 ) ,cte1 as ( select * FROM ( SELECT businessunitidname, filteredEU_attendanceentry.* FROM filteredEU_attendanceentry INNER JOIN ('+@CRM_FilteredSystemUser+') as SU ON filteredEU_attendanceentry.OwnerId = SU.systemuserid WHERE (SU.eu_securityrole LIKE ''%RAM%'') '+@SQLbu+' '+@SQLown+' '+@SQLhub+' ) p unpivot (val for Category in ([eu_mondayamid],[eu_mondaypmid],[eu_tuesdayamid] ,[eu_tuesdaypmid] ,[eu_wednesdayamid] ,[eu_wednesdaypmid] ,[eu_thursdayamid] ,[eu_thursdaypmid] ,[eu_fridayamid] ,[eu_fridaypmid] ,[eu_saturdayamid] ,[eu_saturdaypmid] ,[eu_sundayamid] ,[eu_sundaypmid] ))pvt ), cte2 as ( select DATEADD(day, c.WeekVal, W.EU_Startdate) as WorkedDate, c1.val, c1.OwnerIdName, c1.OwnerId from cte c inner join cte1 c1 on c.WeekName = c1.Category inner join filteredEU_attendancedatetable W on c1.eu_weekid = W.EU_attendancedatetableId ) select isnull( (sum( ISNULL( case when c.val = ''1BD4F625-1709-DF11-9B58-005056AE05B7'' then 0.5 else 0 end,0)) * 1.0),0) as NOOfWorkingDays from cte2 c where WorkedDate &gt;= '''+cast(@fromdate as varchar(30))+''' and WorkedDate &lt;= '''+cast(@todate as varchar(30))+''' ' exec (@SQL) </CommandText> <QueryParameters> <QueryParameter Name="@businessunitidname"> <Value>=Parameters!businessunitidname.Value</Value> </QueryParameter> <QueryParameter Name="@ownerid"> <Value>=Parameters!ownerid.Value</Value> </QueryParameter> <QueryParameter Name="@eu_reporthubname"> <Value>=Parameters!eu_reporthubname.Value</Value> </QueryParameter> <QueryParameter Name="@CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </QueryParameter> <QueryParameter Name="@fromdate"> <Value>=Parameters!fromdate.Value</Value> </QueryParameter> <QueryParameter Name="@todate"> <Value>=Parameters!todate.Value</Value> </QueryParameter> </QueryParameters> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="userinfo"> <Fields> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select fullname from FilteredSystemUser where systemuserid = dbo.fn_FindUserGuid()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> <Code /> <Width>1.25in</Width> <Body> <ReportItems> <Textbox Name="countfield"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=round(IIF(Fields!NOOfWorkingDays.Value = 0.00, 0, Fields!NOOfWorkingDays.Value),1)</Value> </Textbox> </ReportItems> <Height>0.25in</Height> </Body> <Language>en-US</Language> <TopMargin>1in</TopMargin> <PageHeight>0.25in</PageHeight> <CustomProperties> <CustomProperty> <Name>Custom</Name> <Value>&lt;MSCRM xmlns="mscrm"&gt;&amp;lt;ReportFilter&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredSystemUser"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="systemuser"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;/ReportFilter&amp;gt;&lt;/MSCRM&gt;</Value> </CustomProperty> </CustomProperties> </Report>
March 7th, 2011 9:30am

Hi, Try This: Main report Modified <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="CRM_Data"> <rd:DataSourceID>3ef44b2e-4401-449d-aa22-08db1d3d1b8e</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=.;Initial Catalog=MSCRM</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <ReportParameters> <ReportParameter Name="CRM_FilterText"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FullName"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>userinfo</DataSetName> <ValueField>fullname</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="CRM_CalendarType"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>CalendarType</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatDate"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>DateFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatTime"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>TimeFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="StartDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>Start Date</Prompt> </ReportParameter> <ReportParameter Name="EndDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>End Date</Prompt> </ReportParameter> <ReportParameter Name="CRM_FilteredSystemUser"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select systemuser0.* from FilteredSystemUser as systemuser0</Value> </Values> </DefaultValue> </ReportParameter> <ReportParameter Name="CRM_FilteredAppointment"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select appointment0.* from FilteredAppointment as appointment0</Value> </Values> </DefaultValue> </ReportParameter> </ReportParameters> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>0.03125in</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>b9fb94db-3087-456a-833e-0ed0735eb704</rd:ReportID> <DataSets> <DataSet Name="userinfo"> <Fields> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select fullname from FilteredSystemUser where systemuserid = dbo.fn_FindUserGuid()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="DSNumbandCurrency"> <Fields> <Field Name="DateFormat"> <DataField>DateFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="TimeFormat"> <DataField>TimeFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberLanguageCode"> <DataField>NumberLanguageCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CalendarType"> <DataField>CalendarType</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_0_Precision"> <DataField>NumberFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_1_Precision"> <DataField>NumberFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_2_Precision"> <DataField>NumberFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_3_Precision"> <DataField>NumberFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_4_Precision"> <DataField>NumberFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_5_Precision"> <DataField>NumberFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_0_Precision"> <DataField>CurrencyFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_1_Precision"> <DataField>CurrencyFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_2_Precision"> <DataField>CurrencyFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_3_Precision"> <DataField>CurrencyFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_4_Precision"> <DataField>CurrencyFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_5_Precision"> <DataField>CurrencyFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select * from dbo.fn_GetFormatStrings()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="ds_Contacts"> <Fields> <Field Name="businessunitidname"> <DataField>businessunitidname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="systemuserid"> <DataField>systemuserid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="eu_reporthubname"> <DataField>eu_reporthubname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="activityid"> <DataField>activityid</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="subject"> <DataField>subject</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText> declare @EndDate_str varchar(30); set @EndDate_Str=@EndDate+' 23:59:59'; declare @SQL varchar(4000); SET @SQL = 'SELECT UserSys.businessunitidname, UserSys.systemuserid, UserSys.eu_reporthubname, Convert(VarChar(50), AP.activityID) as [activityid], UserSys.fullname, AP.subject FROM ('+@CRM_FilteredSystemUser+') AS UserSys LEFT OUTER JOIN ('+@CRM_FilteredAppointment+') AS AP ON UserSys.systemuserid = AP.ownerid WHERE (UserSys.eu_securityrole LIKE ''%RAM%'') and UserSys.eu_reporthub is not null and AP.scheduledend &gt;= '''+cast(@StartDate as varchar(30))+''' and AP.scheduledend &lt;= '''+@EndDate_Str +''' UNION ALL SELECT SU.businessunitidname, SU.systemuserid, SU.eu_reporthubname, null as [activityid], SU.fullname, null as [subject] FROM ('+@CRM_FilteredSystemUser+') AS SU Inner join ('+@CRM_FilteredAppointment+') AS AP ON SU.systemuserid = AP.ownerid where (SU.eu_securityrole LIKE ''%RAM%'') and SU.eu_reporthub is not null ' EXEC (@SQL) </CommandText> <QueryParameters> <QueryParameter Name="@CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </QueryParameter> <QueryParameter Name="@CRM_FilteredAppointment"> <Value>=Parameters!CRM_FilteredAppointment.Value</Value> </QueryParameter> <QueryParameter Name="@StartDate"> <Value>=Parameters!StartDate.Value</Value> </QueryParameter> <QueryParameter Name="@EndDate"> <Value>=Parameters!EndDate.Value</Value> </QueryParameter> </QueryParameters> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> <Code> Function CalcPct(Num, Den) Dim RetVal as Decimal if Den = 0 then RetVal = 0 else RetVal = Num/Den End if Return RetVal End Function </Code> <Width>10.65625in</Width> <Body> <ReportItems> <Rectangle Name="rectangle1"> <ZIndex>1</ZIndex> <ReportItems> <Textbox Name="txtReportHeaderLabel"> <Top>0.0625in</Top> <Width>3.5in</Width> <Style> <FontFamily>tahoma</FontFamily> <FontSize>14pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.3125in</Height> <Value>Calls per field day</Value> </Textbox> <Rectangle Name="rectangle4"> <Visibility> <Hidden>=IIF(IsNothing(Parameters!CRM_FilterText.Value ) or ( Parameters!CRM_FilterText.Value ) = "" or (Parameters!CRM_FilterText.Value) = " ", True, False)</Hidden> </Visibility> <ReportItems> <Textbox Name="txtFilterSummaryValue"> <Visibility> <Hidden>true</Hidden> <ToggleItem>txtFilterSummary</ToggleItem> </Visibility> <Top>0.34375in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>=Parameters!CRM_FilterText.Value</Value> </Textbox> <Textbox Name="txtFilterSummary"> <Top>0.03125in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>Filter Summary</Value> </Textbox> </ReportItems> <Top>0.4375in</Top> <Width>4.25in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> </Rectangle> </ReportItems> <Width>7.875in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> <Height>1.125in</Height> </Rectangle> <Table Name="table2"> <DataSetName>ds_Contacts</DataSetName> <Top>1.25in</Top> <TableGroups> <TableGroup> <Grouping Name="ReportingHub"> <GroupExpressions> <GroupExpression>=Fields!eu_reporthubname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!eu_reporthubname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox11"> <rd:DefaultName>textbox11</rd:DefaultName> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>14</ZIndex> <CanGrow>true</CanGrow> <Value>=iif( IsNothing(Fields!eu_reporthubname.Value), "No Hub Assigned", "Hub: " &amp; Fields!eu_reporthubname.Value)</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox16"> <rd:DefaultName>textbox16</rd:DefaultName> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>13</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox14"> <rd:DefaultName>textbox14</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>12</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> </TableGroup> <TableGroup> <Grouping Name="BusinessUnit"> <GroupExpressions> <GroupExpression>=Fields!businessunitidname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!businessunitidname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>11</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!businessunitidname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox19"> <rd:DefaultName>textbox19</rd:DefaultName> <Style> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>10</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>9</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox47"> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!businessunitidname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <List Name="list2"> <ZIndex>4</ZIndex> <DataSetName>userinfo</DataSetName> <ReportItems> <Subreport Name="subreport1"> <ReportName>SUB_REPORT</ReportName> </Subreport> </ReportItems> <Style> <BorderColor> <Default>#9e9e9e</Default> </BorderColor> </Style> <Grouping Name="list2_Details_Group"> <GroupExpressions> <GroupExpression>=1</GroupExpression> </GroupExpressions> </Grouping> </List> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox50"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>g</Format> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value>=CountDistinct(Fields!activityid.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.34375in</Height> </TableRow> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>8</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>7</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox13"> <rd:DefaultName>textbox13</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>6</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.125in</Height> </TableRow> </TableRows> </Footer> </TableGroup> <TableGroup> <Grouping Name="RAM"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!fullname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox7"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!fullname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <List Name="list3"> <ZIndex>1</ZIndex> <DataSetName>userinfo</DataSetName> <ReportItems> <Subreport Name="subreport2"> <ReportName>SUB_REPORT</ReportName> </Subreport> </ReportItems> <Grouping Name="list3_ListControl"> <GroupExpressions> <GroupExpression>=1</GroupExpression> </GroupExpressions> </Grouping> </List> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox17"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>g</Format> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=CountDistinct(Fields!activityid.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.34375in</Height> </TableRow> </TableRows> </Footer> </TableGroup> </TableGroups> <Width>8.875in</Width> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox32"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>17</ZIndex> <CanGrow>true</CanGrow> <Value>RAM</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox33"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>16</ZIndex> <CanGrow>true</CanGrow> <Value>Total # of days on the field</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox35"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>15</ZIndex> <CanGrow>true</CanGrow> <Value># of Calls</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.28125in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <TableColumns> <TableColumn> <Width>3.59375in</Width> </TableColumn> <TableColumn> <Width>2.875in</Width> </TableColumn> <TableColumn> <Width>2.40625in</Width> </TableColumn> </TableColumns> <Height>1.59375in</Height> </Table> </ReportItems> <Height>2.9375in</Height> </Body> <CustomProperties> <CustomProperty> <Name>Custom</Name> <Value>&lt;MSCRM xmlns="mscrm"&gt;&amp;lt;ReportFilter&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredSystemUser"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="systemuser"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredAppointment"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="appointment"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;/ReportFilter&amp;gt;&lt;/MSCRM&gt;</Value> </CustomProperty> </CustomProperties> <Language>en-US</Language> <PageFooter> <PrintOnFirstPage>true</PrintOnFirstPage> <ReportItems> <Textbox Name="txtPageCount"> <Top>0.01172in</Top> <Width>1.8125in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Right</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Left>6in</Left> <Height>0.21875in</Height> <Value>=String.Format("Page {0} of {1}", Format(Globals!PageNumber, "N0"), Format(Globals!TotalPages, "N0"))</Value> </Textbox> <Textbox Name="txtUserIDLabel"> <Top>0.01172in</Top> <Width>2.5in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>2.75in</Left> <Height>0.21875in</Height> <Value>=String.Format(System.Globalization.CultureInfo.InvariantCulture, "Prepared by: {0}", Parameters!CRM_FullName.Value)</Value> </Textbox> <Textbox Name="txtExecutionDateTimeLabel"> <Top>0.01953in</Top> <Width>1.85935in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>=Parameters!CRM_FormatDate.Value + " " + Parameters!CRM_FormatTime.Value</Format> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> <Calendar>=Parameters!CRM_CalendarType.Value</Calendar> </Style> <CanGrow>true</CanGrow> <Height>0.21875in</Height> <Value>=Now()</Value> </Textbox> </ReportItems> <Height>0.25in</Height> <PrintOnLastPage>true</PrintOnLastPage> </PageFooter> </Report> Thanks Shiven:)
Free Windows Admin Tool Kit Click here and download it now
March 8th, 2011 12:41am

Try This also: <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="CRM_Data"> <rd:DataSourceID>3ef44b2e-4401-449d-aa22-08db1d3d1b8e</rd:DataSourceID> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=.;Initial Catalog=MSCRM</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> </DataSource> </DataSources> <InteractiveHeight>11in</InteractiveHeight> <ReportParameters> <ReportParameter Name="CRM_FilterText"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FullName"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>userinfo</DataSetName> <ValueField>fullname</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="CRM_CalendarType"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>CalendarType</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatDate"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>DateFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="CRM_FormatTime"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DSNumbandCurrency</DataSetName> <ValueField>TimeFormat</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> </ReportParameter> <ReportParameter Name="StartDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>Start Date</Prompt> </ReportParameter> <ReportParameter Name="EndDate"> <DataType>DateTime</DataType> <AllowBlank>true</AllowBlank> <Prompt>End Date</Prompt> </ReportParameter> <ReportParameter Name="CRM_FilteredSystemUser"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select systemuser0.* from FilteredSystemUser as systemuser0</Value> </Values> </DefaultValue> </ReportParameter> <ReportParameter Name="CRM_FilteredAppointment"> <DataType>String</DataType> <DefaultValue> <Values> <Value>select appointment0.* from FilteredAppointment as appointment0</Value> </Values> </DefaultValue> </ReportParameter> </ReportParameters> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>8.5in</InteractiveWidth> <rd:GridSpacing>0.03125in</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <RightMargin>1in</RightMargin> <LeftMargin>1in</LeftMargin> <BottomMargin>1in</BottomMargin> <rd:ReportID>b9fb94db-3087-456a-833e-0ed0735eb704</rd:ReportID> <DataSets> <DataSet Name="userinfo"> <Fields> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select fullname from FilteredSystemUser where systemuserid = dbo.fn_FindUserGuid()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="DSNumbandCurrency"> <Fields> <Field Name="DateFormat"> <DataField>DateFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="TimeFormat"> <DataField>TimeFormat</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberLanguageCode"> <DataField>NumberLanguageCode</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CalendarType"> <DataField>CalendarType</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_0_Precision"> <DataField>NumberFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_1_Precision"> <DataField>NumberFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_2_Precision"> <DataField>NumberFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_3_Precision"> <DataField>NumberFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_4_Precision"> <DataField>NumberFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="NumberFormat_5_Precision"> <DataField>NumberFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_0_Precision"> <DataField>CurrencyFormat_0_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_1_Precision"> <DataField>CurrencyFormat_1_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_2_Precision"> <DataField>CurrencyFormat_2_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_3_Precision"> <DataField>CurrencyFormat_3_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_4_Precision"> <DataField>CurrencyFormat_4_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="CurrencyFormat_5_Precision"> <DataField>CurrencyFormat_5_Precision</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText>select * from dbo.fn_GetFormatStrings()</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="ds_Contacts"> <Fields> <Field Name="businessunitidname"> <DataField>businessunitidname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="systemuserid"> <DataField>systemuserid</DataField> <rd:TypeName>System.Guid</rd:TypeName> </Field> <Field Name="eu_reporthubname"> <DataField>eu_reporthubname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="activityid"> <DataField>activityid</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="fullname"> <DataField>fullname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="subject"> <DataField>subject</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>CRM_Data</DataSourceName> <CommandText> declare @EndDate_str varchar(30); set @EndDate_Str=@EndDate+' 23:59:59'; declare @SQL varchar(4000); SET @SQL = 'SELECT UserSys.businessunitidname, UserSys.systemuserid, UserSys.eu_reporthubname, Convert(VarChar(50), AP.activityID) as [activityid], UserSys.fullname, AP.subject FROM ('+@CRM_FilteredSystemUser+') AS UserSys LEFT OUTER JOIN ('+@CRM_FilteredAppointment+') AS AP ON UserSys.systemuserid = AP.ownerid WHERE (UserSys.eu_securityrole LIKE ''%RAM%'') and UserSys.eu_reporthub is not null and AP.scheduledend &gt;= '''+cast(@StartDate as varchar(30))+''' and AP.scheduledend &lt;= '''+@EndDate_Str +''' UNION ALL SELECT SU.businessunitidname, SU.systemuserid, SU.eu_reporthubname, null as [activityid], SU.fullname, null as [subject] FROM ('+@CRM_FilteredSystemUser+') AS SU Inner join ('+@CRM_FilteredAppointment+') AS AP ON SU.systemuserid = AP.ownerid where (SU.eu_securityrole LIKE ''%RAM%'') and SU.eu_reporthub is not null ' EXEC (@SQL) </CommandText> <QueryParameters> <QueryParameter Name="@CRM_FilteredSystemUser"> <Value>=Parameters!CRM_FilteredSystemUser.Value</Value> </QueryParameter> <QueryParameter Name="@CRM_FilteredAppointment"> <Value>=Parameters!CRM_FilteredAppointment.Value</Value> </QueryParameter> <QueryParameter Name="@StartDate"> <Value>=Parameters!StartDate.Value</Value> </QueryParameter> <QueryParameter Name="@EndDate"> <Value>=Parameters!EndDate.Value</Value> </QueryParameter> </QueryParameters> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> <Code> Function CalcPct(Num, Den) Dim RetVal as Decimal if Den = 0 then RetVal = 0 else RetVal = Num/Den End if Return RetVal End Function </Code> <Width>13.21875in</Width> <Body> <ReportItems> <List Name="list2"> <ZIndex>1</ZIndex> <Left>0.03125in</Left> <DataSetName>ds_Contacts</DataSetName> <ReportItems> <Table Name="table2"> <ZIndex>1</ZIndex> <DataSetName>ds_Contacts</DataSetName> <Top>0.125in</Top> <TableGroups> <TableGroup> <Grouping Name="ReportingHub"> <GroupExpressions> <GroupExpression>=Fields!eu_reporthubname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!eu_reporthubname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox11"> <rd:DefaultName>textbox11</rd:DefaultName> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>5</ZIndex> <CanGrow>true</CanGrow> <Value>=iif( IsNothing(Fields!eu_reporthubname.Value), "No Hub Assigned", "Hub: " &amp; Fields!eu_reporthubname.Value)</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox14"> <rd:DefaultName>textbox14</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>4</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox5"> <rd:DefaultName>textbox5</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>3</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> </Header> </TableGroup> <TableGroup> <Grouping Name="BusinessUnit"> <GroupExpressions> <GroupExpression>=Fields!businessunitidname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!businessunitidname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <Style> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!businessunitidname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox6"> <rd:DefaultName>textbox6</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.25in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> </TableGroup> <TableGroup> <Grouping Name="RAM"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> <Sorting> <SortBy> <SortExpression>=Fields!fullname.Value</SortExpression> <Direction>Ascending</Direction> </SortBy> </Sorting> </TableGroup> </TableGroups> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox32"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>8</ZIndex> <CanGrow>true</CanGrow> <Value>RAM</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox35"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>7</ZIndex> <CanGrow>true</CanGrow> <Value># of Calls</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox9"> <Style> <Color>White</Color> <BackgroundColor>#04090f</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>6</ZIndex> <CanGrow>true</CanGrow> <Value>Total # of days on the field</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.28125in</Height> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <TableColumns> <TableColumn> <Width>3.59375in</Width> </TableColumn> <TableColumn> <Width>2.40625in</Width> </TableColumn> <TableColumn> <Width>2.875in</Width> </TableColumn> </TableColumns> <Height>0.78125in</Height> </Table> <List Name="list1"> <DataSetName>ds_Contacts</DataSetName> <ReportItems> <Table Name="table1"> <ZIndex>1</ZIndex> <Width>6in</Width> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox2"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Value>=Fields!fullname.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox8"> <Style> <Color>White</Color> <BackgroundColor>#9e9e9e</BackgroundColor> <FontFamily>Tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>g</Format> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Value>=CountDistinct(Fields!activityid.Value)</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.34375in</Height> </TableRow> </TableRows> </Details> <TableColumns> <TableColumn> <Width>3.59375in</Width> </TableColumn> <TableColumn> <Width>2.40625in</Width> </TableColumn> </TableColumns> </Table> <Subreport Name="subreport2"> <ReportName>SUB_REPORT</ReportName> <Left>6in</Left> </Subreport> </ReportItems> <Top>0.90625in</Top> <Grouping Name="list1_Details_Group"> <GroupExpressions> <GroupExpression>=Fields!fullname.Value</GroupExpression> </GroupExpressions> </Grouping> </List> </ReportItems> <Top>1.1875in</Top> <Width>8.875in</Width> <Grouping Name="list2_Details_Group"> <GroupExpressions> <GroupExpression>=0</GroupExpression> </GroupExpressions> </Grouping> <Height>1.25in</Height> </List> <Rectangle Name="rectangle1"> <ReportItems> <Textbox Name="txtReportHeaderLabel"> <Top>0.0625in</Top> <Width>3.5in</Width> <Style> <FontFamily>tahoma</FontFamily> <FontSize>14pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.3125in</Height> <Value>Calls per field day</Value> </Textbox> <Rectangle Name="rectangle4"> <Visibility> <Hidden>=IIF(IsNothing(Parameters!CRM_FilterText.Value ) or ( Parameters!CRM_FilterText.Value ) = "" or (Parameters!CRM_FilterText.Value) = " ", True, False)</Hidden> </Visibility> <ReportItems> <Textbox Name="txtFilterSummaryValue"> <Visibility> <Hidden>true</Hidden> <ToggleItem>txtFilterSummary</ToggleItem> </Visibility> <Top>0.34375in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>=Parameters!CRM_FilterText.Value</Value> </Textbox> <Textbox Name="txtFilterSummary"> <Top>0.03125in</Top> <Width>3.53125in</Width> <Style> <Color>#313336</Color> <FontFamily>tahoma</FontFamily> <FontSize>12pt</FontSize> <FontWeight>700</FontWeight> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <CanGrow>true</CanGrow> <Left>0.15625in</Left> <Height>0.25in</Height> <Value>Filter Summary</Value> </Textbox> </ReportItems> <Top>0.4375in</Top> <Width>4.25in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> </Rectangle> </ReportItems> <Width>7.875in</Width> <Style> <BackgroundColor>#deddcf</BackgroundColor> </Style> <Height>1.125in</Height> </Rectangle> </ReportItems> <Height>2.5625in</Height> </Body> <CustomProperties> <CustomProperty> <Name>Custom</Name> <Value>&lt;MSCRM xmlns="mscrm"&gt;&amp;lt;ReportFilter&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredSystemUser"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="systemuser"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;ReportEntity paramname="CRM_FilteredAppointment"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="appointment"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;/ReportFilter&amp;gt;&lt;/MSCRM&gt;</Value> </CustomProperty> </CustomProperties> <Language>en-US</Language> <PageFooter> <PrintOnFirstPage>true</PrintOnFirstPage> <ReportItems> <Textbox Name="txtPageCount"> <Top>0.01172in</Top> <Width>1.8125in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Right</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>2</ZIndex> <CanGrow>true</CanGrow> <Left>6in</Left> <Height>0.21875in</Height> <Value>=String.Format("Page {0} of {1}", Format(Globals!PageNumber, "N0"), Format(Globals!TotalPages, "N0"))</Value> </Textbox> <Textbox Name="txtUserIDLabel"> <Top>0.01172in</Top> <Width>2.5in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <TextAlign>Center</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> <ZIndex>1</ZIndex> <CanGrow>true</CanGrow> <Left>2.75in</Left> <Height>0.21875in</Height> <Value>=String.Format(System.Globalization.CultureInfo.InvariantCulture, "Prepared by: {0}", Parameters!CRM_FullName.Value)</Value> </Textbox> <Textbox Name="txtExecutionDateTimeLabel"> <Top>0.01953in</Top> <Width>1.85935in</Width> <Style> <Color>#666666</Color> <FontFamily>tahoma</FontFamily> <FontSize>8pt</FontSize> <FontWeight>700</FontWeight> <Format>=Parameters!CRM_FormatDate.Value + " " + Parameters!CRM_FormatTime.Value</Format> <TextAlign>Left</TextAlign> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> <Calendar>=Parameters!CRM_CalendarType.Value</Calendar> </Style> <CanGrow>true</CanGrow> <Height>0.21875in</Height> <Value>=Now()</Value> </Textbox> </ReportItems> <Height>0.25in</Height> <PrintOnLastPage>true</PrintOnLastPage> </PageFooter> </Report> Thanks Shiven:)
March 8th, 2011 12:56am

Hi Shiven, I applied both the layouts. I m getting either of these problems if i use any of the two layout. 1. Sub report data is not exported to excel. getting the same error "Data Regions within table/matrix cells are ignored" 2. If sub Report data is exported using the other layout, it is getting displayed after the mainreport data in excel. Would you please help me . Thanks again for your help.
Free Windows Admin Tool Kit Click here and download it now
March 8th, 2011 11:31am

Hi, Go for 2nd approach: 2. If sub Report data is exported using the other layout, it is getting displayed after the mainreport data in excel. Try to do some changes and check how it is coming. This is only way you can achieve. If you will put inside table it will not come.Thanks Shiven:)
March 8th, 2011 11:53pm

Hi All, I too face the same problem if i use 2nd approach. Has anybody found any solution, Please let me know.Thank you. Sudha
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 2:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics