Problem formating report reporting services 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Problem formating report reporting services 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 04:13:19

Hi

I will appreciate any help on my problem.
I have a sql query with the following output:
Order id Material Shipto Billto Quantity
1005t900 wrt559200pp 176544 076544 12
1007t900 wrt559200pp 176544 076544 6
1007t900 wrt559200pp 176544 076544 6
1007t900 wrt559200pp 176544 076544 6
1011t900 wrt559200pp 1869712 086972 18
1011t900 wrt559200pp 1869712 086972 6
1015t900 wrt559200pp 1612639 062639 6
6862t900 wrt541595pp 1291804 300243 6
6862t900 wrt542615pp 1291804 300243 7
6862t900 wrt551596pp 1291804 300243 6
6862t900 wrt551405pp 1291804 300243 6
6862t900 wrt551405pp 1291804 300243 6
I need to create a report in the following format: The id and item number i create on the fly. They aren't db fields.

I am able to create the report but without the D1 and quantity column.
If i had d1 i get the following
d
d1
d
d1
and i need it to be
d
d
d1
d1

<?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="DataSource1">
      <rd:DataSourceID>0af896ca-abb7-4b53-810c-19d6b24097e7</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=(local);Initial Catalog=donald</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
    </DataSource>
  </DataSources>
  <InteractiveHeight>29.7cm</InteractiveHeight>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>21cm</InteractiveWidth>
  <rd:GridSpacing>0.25cm</rd:GridSpacing>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>2.5cm</RightMargin>
  <LeftMargin>2.5cm</LeftMargin>
  <BottomMargin>2.5cm</BottomMargin>
  <rd:ReportID>7d60cd0c-ba30-4cff-a127-6ed9aaa8b66d</rd:ReportID>
  <PageWidth>21cm</PageWidth>
  <DataSets>
    <DataSet Name="donald">
      <Fields>
        <Field Name="order_id">
          <DataField>order_id</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Material">
          <DataField>Material</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="billto_id">
          <DataField>billto_id</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="shipto_id">
          <DataField>shipto_id</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="shipped_quantity">
          <DataField>shipped_quantity</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>select o.order_id, orderitem_style_code+'-'+orderitem_color_code AS Material
,shipto.billto_id,o.shipto_id ,shipped_quantity
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id
order by order_id</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
  </DataSets>
  <Width>73.88887cm</Width>
  <Body>
    <ColumnSpacing>1cm</ColumnSpacing>
    <ReportItems>
      <Table Name="table1">
        <DataSetName>donald</DataSetName>
        <Top>1.25cm</Top>
        <TableGroups>
          <TableGroup>
            <Grouping Name="OrderGroup">
              <GroupExpressions>
                <GroupExpression>=Fields!order_id.Value</GroupExpression>
              </GroupExpressions>
            </Grouping>
            <Header>
              <TableRows>
                <TableRow>
                  <TableCells>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox19">
                          <rd:DefaultName>textbox19</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>27</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>H</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="billto_id">
                          <rd:DefaultName>billto_id</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>26</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>=First(Fields!billto_id.Value)</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="shipto_id">
                          <rd:DefaultName>shipto_id</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>25</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>=First(Fields!shipto_id.Value)</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="order_id">
                          <rd:DefaultName>order_id</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>24</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!order_id.Value</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox23">
                          <rd:DefaultName>textbox23</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>23</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox24">
                          <rd:DefaultName>textbox24</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>22</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox20">
                          <rd:DefaultName>textbox20</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>21</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                  </TableCells>
                  <Height>0.63492cm</Height>
                </TableRow>
              </TableRows>
            </Header>
            <Footer>
              <TableRows>
                <TableRow>
                  <TableCells>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox25">
                          <rd:DefaultName>textbox25</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>13</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>D1</Value>
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox26">
                          <rd:DefaultName>textbox26</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>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox27">
                          <rd:DefaultName>textbox27</rd:DefaultName>
                          <Style>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>11</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value />
                        </Textbox>
                      </ReportItems>
                    </TableCell>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox28">
                          <rd:DefaultName>textbox28</rd:DefaultName>
                          <Style>
                            <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="textbox29">
                          <rd:DefaultName>textbox29</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>
                    <TableCell>
                      <ReportItems>
                        <Textbox Name="textbox30">
                          <rd:DefaultName>textbox30</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="textbox22">
                          <rd:DefaultName>textbox22</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>
                  </TableCells>
                  <Height>0.63492cm</Height>
                </TableRow>
              </TableRows>
            </Footer>
          </TableGroup>
        </TableGroups>
        <Width>26cm</Width>
        <Details>
          <TableRows>
            <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>6</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>D</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>5</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>4</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox17">
                      <rd:DefaultName>textbox17</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>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="Material">
                      <rd:DefaultName>Material</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>2</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Material.Value</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>
                      <ZIndex>1</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>=RowNumber("OrderGroup")*10</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox21">
                      <rd:DefaultName>textbox21</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.63492cm</Height>
            </TableRow>
          </TableRows>
        </Details>
        <Header>
          <TableRows>
            <TableRow>
              <TableCells>
                <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>34</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>ID </Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox2">
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>33</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Sold to</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>32</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Shipto</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox16">
                      <rd:DefaultName>textbox16</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>31</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Order Number</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox10">
                      <rd:DefaultName>textbox10</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>30</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Material Number</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox3">
                      <rd:DefaultName>textbox3</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>29</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Item Number</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox11">
                      <rd:DefaultName>textbox11</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>28</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value>Quantity</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.63492cm</Height>
            </TableRow>
          </TableRows>
        </Header>
        <TableColumns>
          <TableColumn>
            <Width>2.75cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>2.5cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>1.75cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>2.75cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>4.75cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>5.75cm</Width>
          </TableColumn>
          <TableColumn>
            <Width>5.75cm</Width>
          </TableColumn>
        </TableColumns>
        <Height>3.1746cm</Height>
        <Footer>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox7">
                      <rd:DefaultName>textbox7</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>20</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox8">
                      <rd:DefaultName>textbox8</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>19</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox15">
                      <rd:DefaultName>textbox15</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>18</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox18">
                      <rd:DefaultName>textbox18</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>17</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox12">
                      <rd:DefaultName>textbox12</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>16</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox9">
                      <rd:DefaultName>textbox9</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>15</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox31">
                      <rd:DefaultName>textbox31</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <ZIndex>14</ZIndex>
                      <CanGrow>true</CanGrow>
                      <Value />
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.63492cm</Height>
            </TableRow>
          </TableRows>
        </Footer>
      </Table>
    </ReportItems>
    <Height>6.07936cm</Height>
  </Body>
  <Language>en-GB</Language>
  <TopMargin>2.5cm</TopMargin>
  <PageHeight>29.7cm</PageHeight>
</Report>

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 04:27:35

your image is not visible. post it in some shared servers and give link here

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 05:25:25

Hi,

It should be visible now.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:32:11

how are you ordering records currently?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 07:00:53

i am not. i tried adding a hidden column to the report table with value 1 for h
2 for d
3 for d1.

But since the values for the hidden column are manually written and not retrieved from the select i am not able to sort the group by that table column.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 07:01:55

quote:
Originally posted by collie

i am not. i tried adding a hidden column to the report table with value 1 for h
2 for d
3 for d1.

But since the values for the hidden column are manually written and not retrieved from the select i am not able to sort the group by that table column.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

nope how do you derive the value for id as h,d,etc?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 07:26:55

i added it hard coded to the report. The first row (group) is the header so i wrote H in the group row, in the details row i wrote D so it gets repeated when i run the report.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:21:01

and then you want it to order based on it like d first,d1 second etc?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 11:36:17

like this
H
D
D1

OR if order has a few items
H
D
D
D
D1
D1
D1

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:12:57

quote:
Originally posted by collie

like this
H
D
D1

OR if order has a few items
H
D
D
D
D1
D1
D1

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

its not possible unless you return it as a part of dataset.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 12:18:42

I have tried the following but i am not sure it's correct and how to plot it in the report:

If object_id('tempdb..#TempSeason') is not null

drop table #TempSeason

create table #TempSeason (row int ,id nchar (10),order_id nvarchar (100),material nvarchar (50), billto_id nvarchar (100),shipto_id nvarchar (50),shipped_quantity int)
insert into #TempSeason
select DISTINCT 1 row
,'H' AS ID
,o.order_id as order_id
,'' as Material
,shipto.billto_id as billto_id
,shipto.shipto_id shipto_id
,'' AS shipped_quantity
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

insert into #TempSeason
select DISTINCT 2 row
,'D'
,o.order_id as order_id
,orderitem_style_code+'-'+orderitem_color_code AS Material
,'' as billto_id
,'' shipto_id
,'' AS shipped_quantity
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

insert into #TempSeason
select DISTINCT 3 row
,'D1'
,o.order_id as order_id
,orderitem_style_code+'-'+orderitem_color_code AS Material
,'' as billto_id
,'' shipto_id
,shipped_quantity AS shipped_quantity
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

select * from #TempSeason
ORDER BY ROW,ORDER_ID,ID

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-03 : 14:15:39

ok i managed to do most of the report. What i am left with is the rownumber.

Hi,

if object_id('tempdb..#TempSeason') is not null

drop table #TempSeason

create table #TempSeason (row int ,itemnumber int ,id nchar (10),order_id nvarchar (100),material nvarchar (50), billto_id nvarchar (100),shipto_id nvarchar (50),shipped_quantity int,materialhidden nvarchar (50))
insert into #TempSeason
select DISTINCT 1 row
,0 AS itemnumber
,'H' AS ID
,o.order_id as order_id
,'' as Material
,shipto.billto_id as billto_id
,shipto.shipto_id shipto_id
,'' AS shipped_quantity
,'' as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

insert into #TempSeason
select DISTINCT 2 row
,ROW_NUMBER() OVER (ORDER BY o.order_id) *10 AS itemnumber
,'D'
,o.order_id as order_id
,orderitem_style_code+'-'+orderitem_color_code AS Material
,'' as billto_id
,'' shipto_id
,'' AS shipped_quantity
,orderitem_style_code+'-'+orderitem_color_code as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

insert into #TempSeason
select DISTINCT 3 row
,ROW_NUMBER() OVER (ORDER BY o.order_id) *10 AS itemnumber
,'D1'
,o.order_id as order_id
,'' AS Material
,'' as billto_id
,'' shipto_id
,shipped_quantity AS shipped_quantity
,orderitem_style_code+'-'+orderitem_color_code as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id

select distinct row ,itemnumber ,id ,order_id ,material, billto_id
,shipto_id,shipped_quantity ,materialhidden from #TempSeason
ORDER BY ORDER_ID,ROW

The result should be that each new row with row=2 and ID=D grouped by order_id should start with itemnumber 10 and row=2 and ID=D1 should also start with itemnumber=10 .
However, what happens is that each row=2 and ID=D grouped by order_id continues incrementing without starting again at 10.
In the attached the font in red is the current result and the highlight is the needed outcome.

My report design

Not sure how to go about it.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources