isql query output issue: body

Hi guys,

I posted this question a couple of weeks ago, however, I got no response at all,. It seems that no body tried this before. Anyhow if anybody did the same thing, here is my issue;

Assume I have a pie chart whose Value: is Internet Sales Amount and Category group: is Product Subcategroy. Suppose I have 5 segments(slices) with the value 200,150,80,50,and 40. Now, here is the question; I want to aggregate those values that are less than 100 (in this case the last three values: 80+50+40 = 170) and put the sum(170) in one segment, so that in the new pie chart we will have only 3 segments with the value 200,250, and 170.

Any idea is appreciated.

There are several other ways of doing this:
- perform the pre-aggregation in the dataset query / stored procedure. If the data source is Analysis Services, you can use e.g. the MDX iif functions to "re-group" certain members within a hierarchy.

- look into RS 2005 CustomReportItem-based third party charting solutions which may offer this functionality.

- wait for a future release of RS. Adding this particular re-group feature as a native concept within RS is under consideration for a future release.

- there is a solution based on using the OLEDB provider to connect to AS. See my posting of 08/23 with a sample report in this thread.

-- Robert

|||

Robert,

The data source is Analysis Services Cube; that makes it a litle bit difficult. Anyhow, I tried all my best to aggregate the values (that are less than 100, for instance) and put them in one segment. What I did is in the value tab I wrote the following expression:

=Iif(Fields!Internet_Sales_Amount.Value < 100,Fields!sumofothers.value,fields!Internet_Sales_Amount.value),

where Fields!sumeofothers.value is sum of those values that are less than 100.

And in the Data Leble I wrote the following expression

=IIF(fields!Internet_Sales_Amount.Value <100,"Others",Fields!Subcategory.Value).

So, here what I want is to group those values that are less than 100 and aggregate them in one segment called "Others", however, I am getting multiple "others" segments.

Can you please give me some comment on the above expressions or an example of expression which perform this functionality?

The other thing is that where shall we expect the next release of reporting service?

--Amde

|||

Let's take a look at the following MDX query where you perform essentially "re-grouping" of data, such as the following query based on the Adventure Works sample cube:

WITH
SET [ProductLineTop5ModelName] AS 'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS FROM [Adventure Works]

The "Other" group is represented as a subtotal aggregate row in the flattened rowset from the AS server. The only way to retrieve this aggregate row when using the AS data extension in RS 2005 RTM/SP1 is to use the RS server aggregate function =Aggregate(...) in the correct scope in the report - otherwise the row is ignored since it is marked as an AS server aggregate row. However, in that particular case the "re-grouped" data actually is not the subtotal of the other groups. It is rather "detail data".

I attached a report at the bottom of this posting which implements two options side by side for the MDX query above:

1. the first dataset / first table in the report is based on using the data source type "Analysis Services" and it will require a new feature under consideration to be added in the next service pack of RS 2005. Without that new feature, the "VisualTotal" rows will not show up in the first table because the server aggregate function is not used. With the new feature however they will show up in the first table because the report does not explicitly use the RS server aggregate row to actually retrieve the aggregate rows as a subtotals - and they will therefore be automatically recategorized as "detail data".

2. the second dataset / second table in the report uses the data source type "OLEDB" and connects through the OleDB for AS9.0 provider - this is available in RS 2005 today. The fact that the OLEDB provider does not understand anything about hierarchies, measures, extended properties, server aggregate rows, etc. is actually a benefit in this case: the "VisualTotal" shows up as detail row in the table - as desired.

You should be able to apply the same concept to your particular situation with the chart. The important part is that for now you will need to use the OLEDB provider to connect to AS and execute the queries.

-- Robert

==================================================================================

<?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="RoseAS">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB-MD</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2853688f-8806-4d0a-ae38-2a865ce38226</rd:DataSourceID>
</DataSource>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Provider=MSOLAP.3;Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>90e8d194-dfcc-42a3-826e-042e456854e3</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_OleDB</DataSetName>
<Top>1.875in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Product_Line">
<rd:DefaultName>Product_Product_Model_Lines_Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Product_Model_Lines_Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Product_Model_Lines_Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Model">
<rd:DefaultName>Product_Product_Model_Lines_Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Product_Product_Model_Lines_Model.Value is Nothing, Fields!Product_Product_Model_Lines_Product_Line.Value & " - Other", Fields!Product_Product_Model_Lines_Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_Order_Count">
<rd:DefaultName>Measures_Order_Count</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_Order_Count.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_ADOMD</DataSetName>
<Top>0.25in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Line">
<rd:DefaultName>Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count_1">
<rd:DefaultName>Order_Count_1</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Order_Count.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Model">
<rd:DefaultName>Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Model.Value is Nothing, Fields!Product_Line.Value & " - Other", Fields!Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count">
<rd:DefaultName>Order_Count</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Order_Count.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
</ReportItems>
<Height>3in</Height>
</Body>
<rd:ReportID>2158ae1a-bacd-437c-ace7-4413c1c708e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet_ADOMD">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<rd:DesignerState><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Product Line</LevelName><UniqueName>[Product].[Product Model Lines].[Product Line]</UniqueName></ID><ItemCaption>Product Line</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Model</LevelName><UniqueName>[Product].[Product Model Lines].[Model]</UniqueName></ID><ItemCaption>Model</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Order Count</MeasureName><UniqueName>[Measures].[Order Count]</UniqueName></ID><ItemCaption>Order Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]

</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:DesignerState>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]

</CommandText>
<DataSourceName>RoseAS</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Line">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Model Lines].[Product Line]" /></DataField>
</Field>
<Field Name="Model">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Model Lines].[Model]" /></DataField>
</Field>
<Field Name="Order_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Order Count]" /></DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DataSet_OleDB">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS

'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Product_Model_Lines_Product_Line">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Product Line].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Product_Product_Model_Lines_Model">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Model].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Measures_Order_Count">
<rd:TypeName>System.Object</rd:TypeName>
<DataField>[Measures].[Order Count]</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>5.54167in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

My coleague is having these problems could any body help

1:-There is one Main report having 2 subreports in it, each sub reoprt has it own header and footer but when its called from Master report, the header and footer
of the sub report doesnt show on the Master report, how to make them visible.

2:-If we need to add some customize code for the report, we used the CustomCode tab that appear when Properties link of that report is clicked,
how can we acess the items on the report under that code ?like if the report has the subreports, tables and want to acess their visibility is there any way.?

3:-How can we check in the cutomcode of the main report that which subreport is being runned, is there any property through which subreport name can be acessed.?

4:-I want my report to cover the whole internet explorer window, but it doesnt cover that all, it just being shown on certain part, altgough i tried to make that 100%.
is there any way?

5:-I want to hide some items of the report on runtime, is there any way that report items can be acessed through code ?

6:-As in CrystalReport we had sections that can be acessed through code and we can do any thing at run time is there anyway to do same here?

7:-how can we bind the parameter to the report, we have to do it through code ? i mean to take the parameter as input we have to add the separate input page?

8:-The report only have the PageHeader,Page Body and Page Footer, dont we have the summary header summary footer, report header etc ?

9:-how can i control that certain par of report should appear on First page only and certain part of the report on last page only is there any way ?

10:-Is there any way we can control the number of records showed per page?

11:-we add the text box that contains the text, there are multiple lines, but when showed on the report all the text comes together,
what should be insert in a text box to indicate when text should coem on next line?

12:-There is a properrty called AsyncRendering we keep it true it gives improper page size but the Navigation appears right, but when we set it false Page was apearing on full screen but navigation was not happening properly can you please tell us that how to perform navigation in case of multiple pages.?

I will wait for these replies, as its really urgent we have numebr of reports having these problems,

Could anybody know how to solve the problem number 1?

Many thanks

Wednesday, March 28, 2012

iterative query

Friday, March 23, 2012

It seems there is no solution for this question?

Monday, March 19, 2012

Issues in using reporting services

Issues in using reporting services

isql query output issue

Blog Archive

About Me