Each row group item need to get different columns in a cross tab report

6 posts, 0 answers
  1. Jaideep
    Jaideep avatar
    4 posts
    Member since:
    Feb 2015

    Posted 06 Mar 2015 Link to this post

    Hi All,

    I am new to Telerik reporting and am working on my first Telerik report. We are using Telerik Reproting Q1 2015 in Visual Studio 2010.

    My query/data source returns a set of product names, quarter of the week sold, week sold, qty sold columns. The report needs to group by quarter. Each quarter will have the following columns: Product, Each week in the group's quarter. And the values contain the qty sold in the week.

    In my report, using Visual Studio designer, I added Quarter, Product to row group, Week to column group and qty Sold to values.

    Issue:
    This is generated all the weeks of all the quarters in the entire table. But what is desired is each row group for the quarter should show weekly columns that pertain to the quarter, meaning In the row group for Q1 2015 I need to have columns for the weeks in Jan-Mar of 2015 and in the row group Q2 2015 should have Apr-Jun 2015 week columns. This also means that each group item will have different columns.

    How can I achieve this? I tried google and also looking through the Telerik examples but could not find any major help.

    I appreciate any help or guidance

    Thanks
    Jaideep

  2. Jaideep
    Jaideep avatar
    4 posts
    Member since:
    Feb 2015

    Posted 09 Mar 2015 Link to this post

    Hi All,

    I am sorry if I was unable to express my question clearly. I have prepared a small Excel file to be more clear in what I am expecting teh output to be. I am attaching the same file in this post. I appreciate any help.

    Thanks
    Jaideep
  3. Hinata
    Hinata avatar
    146 posts
    Member since:
    Dec 2013

    Posted 11 Mar 2015 in reply to Jaideep Link to this post

    Hi Jaideep,

    You can post the current results you are having and the report definition. It will make it a bit easier for other people to help if you attach samples which clearly show what you are doing.
    The layout presented in Excel can be achieved in different ways. For example, you can nest a Table item inside the detail section of the report and show one table for each different Q. You also need to apply filtering in the table, which I believe you are currently lacking and this is why you get all weeks in each Q.
  4. Jaideep
    Jaideep avatar
    4 posts
    Member since:
    Feb 2015

    Posted 11 Mar 2015 in reply to Hinata Link to this post

    Hi Hardik,

    I am using Visual Studio 2010 Designer and could not find on how to export the report definition.

    Regarding your suggestion, are there any examples nesting a Table item in detail section and filtering that you can point me to?

    I appreciate any help!

    Thanks
    Jaideep
  5. Jaideep
    Jaideep avatar
    4 posts
    Member since:
    Feb 2015

    Posted 11 Mar 2015 in reply to Jaideep Link to this post

    Hi Hardik,

    Using the standalone Telerik report designer I was able to generate the xml for the report design and following is the report design. I appreciate for any and all help

    <?xml version="1.0" encoding="utf-8"?>
    <Report Width="6in" Name="PlanFlowchart2" xmlns="http://schemas.telerik.com/reporting/2012/3.8">
      <DataSources>
        <SqlDataSource ConnectionString="TelerikReports.Properties.Settings.gabdv1" SelectCommand="select dp.proposal_id, dp.NAME deal_name, dpp.proposal_plan_id, dp.equivalenced_flag, gs.name agency_name, gaa.addr_line1, gaa.addr_line2, gaa.city, &#xD;&#xA;    gaa.state, gaa.zip, ga.name adver_name, gp.name category_name, dp.flt_beg_date, dp.flt_end_date, gu.lname || ', ' || gu.fname ae_name, gd.name primary_demo,&#xD;&#xA;    to_char(das.create_dt, 'mm/dd/yyyy') revision_date, to_char(das.revision_orig) revision_number, &#xD;&#xA;    pht.mkt_place_id, pht.proposal_title, pdt.season_code, pdt.qtr_ind, bt.brand_id, bt.brand_name, crt.rateclass_id, crt.rateclass_name, &#xD;&#xA;    cutt.unit_type_id, cutt.unit_type_name, pdt.wk_start_date, pdt.rotation_id, rnt.rotation_name, pdt.spot_length_id, pdt.spot_length, pdt.nbr_spots, &#xD;&#xA;    pdt.actual_weekly_amt Rate, pdt.primary_demo_impressions, pht.primary_demo_id, pht.primary_demo_code, pdt.spot_type_id, pdt.spot_type, pdt.network_code, &#xD;&#xA;    nt.network_desc, dt.daypart_name, CASE pdt.QTR_IND WHEN 1 THEN '1st' WHEN 2 THEN '2nd' WHEN 3 THEN '3rd' ELSE '4th' END || ' Qtr ' || pdt.SEASON_CODE QUARTER_NAME, pdt.SEASON_CODE || pdt.QTR_IND QUARTER_SORT, &#xD;&#xA;    TRUNC(CASE WHEN pdt.nbr_spots &gt; 0 THEN (pdt.nbr_spots * pdt.spot_length) / 30 ELSE 0 END, 4) Equiv_Units,&#xD;&#xA;    CASE WHEN pdt.nbr_spots &gt; 0 THEN (pdt.actual_weekly_amt) ELSE 0 END Dollars,&#xD;&#xA;    CASE WHEN pdt.nbr_spots &gt; 0 THEN (pdt.primary_demo_impressions) ELSE 0 END Equiv_Imps,&#xD;&#xA;    total_imps.Total_Equiv_Imps&#xD;&#xA;from dp_proposal dp, dp_proposal_plan dpp, ge_agencies gs, ge_agency_address gaa, ge_advertisers ga, de_prod dep, ge_prod gp, ge_user gu, ge_demos gd, de_audit_summary das,&#xD;&#xA;    proposal_header_t pht, proposal_detail_t pdt, network_t@ADSOPE.WORLD nt, daypart_t@ADSOPE.WORLD dt, rotation_name_t rnt, mkt_place_t mpt, brand_t@ADSOPE.WORLD bt, &#xD;&#xA;    cust_spot_type_mapping_t@ADSOPE.WORLD cstmt, cust_unit_type_t@ADSOPE.WORLD cutt, cust_rateclass_t@ADSOPE.WORLD crt,&#xD;&#xA;    (SELECT dpp.proposal_id, dpp.proposal_plan_id, Sum(CASE WHEN pdt.nbr_spots &gt; 0 THEN (pdt.primary_demo_impressions) ELSE 0 END) Total_Equiv_Imps&#xD;&#xA;       FROM proposal_detail_t pdt, dp_proposal_plan dpp&#xD;&#xA;      WHERE dpp.proposal_id = 597182&#xD;&#xA;        AND dpp.proposal_plan_id = 247448&#xD;&#xA;        AND dpp.proposal_plan_id = pdt.proposal_id&#xD;&#xA;   GROUP BY dpp.proposal_id, dpp.proposal_plan_id) total_imps&#xD;&#xA;where dp.proposal_id= 597182&#xD;&#xA;    and dpp.proposal_plan_id = 247448&#xD;&#xA;    and dp.proposal_id = dpp.proposal_id&#xD;&#xA;    and dp.agency_id = gaa.agency_id&#xD;&#xA;    and dp.agency_id = gs.agency_id&#xD;&#xA;    and dp.adver_id = ga.adver_id&#xD;&#xA;    and dp.proposal_id = dep.deal_id&#xD;&#xA;    and dep.prod_id = gp.prod_id&#xD;&#xA;    and dep.primary_flag = 'Y'&#xD;&#xA;    and dp.acct_exec_code = gu.gab_id&#xD;&#xA;    and dp.primary_demo_id = gd.demo_id&#xD;&#xA;    and dp.proposal_id = das.deal_id&#xD;&#xA;    and das.revision_orig = (select max(revision_orig)&#xD;&#xA;                                 from de_audit_summary das2&#xD;&#xA;                                 where das2.deal_id = dp.proposal_id)&#xD;&#xA;    and pht.mkt_place_id = dp.proposal_id&#xD;&#xA;    and pdt.proposal_id = dpp.proposal_plan_id&#xD;&#xA;    and pht.proposal_id = pdt.proposal_id&#xD;&#xA;    and pdt.network_code = nt.network_code&#xD;&#xA;    and pdt.daypart_id = dt.daypart_id&#xD;&#xA;    and pdt.rotation_id = rnt.rotation_id&#xD;&#xA;    and pht.mkt_place_id = mpt.mkt_place_id&#xD;&#xA;    and mpt.brand_id = bt.brand_id&#xD;&#xA;    and pdt.spot_type_id = cstmt.spot_type_id&#xD;&#xA;    and cstmt.unit_type_id = cutt.unit_type_id&#xD;&#xA;    and cstmt.rateclass_id = crt.rateclass_id&#xD;&#xA;    and gd.demo_id = 310&#xD;&#xA; order by pdt.season_code, pdt.qtr_ind, bt.brand_name, crt.rateclass_name, cutt.unit_type_name, pdt.wk_start_date" Name="sqlData" />
      </DataSources>
      <Items>
        <DetailSection Height="1.80000007152557in" Name="detailSection1">
          <Items>
            <Crosstab DataSourceName="sqlData" Width="5.81666660308838in" Height="2.40000009536743in" Left="0in" Top="0.39999994635582in" Name="crosstab1" StyleName="Apex.TableNormal">
              <Body>
                <Cells>
                  <TableCell RowIndex="0" ColumnIndex="0" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="0.904166877269745in" Height="0.200000032782555in" Left="0in" Top="0in" Value="= Sum(Fields.EQUIV_UNITS)" Name="textBox7" StyleName="Apex.TableBody" />
                    </ReportItem>
                  </TableCell>
                  <TableCell RowIndex="2" ColumnIndex="0" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="0.904166877269745in" Height="1.00000011920929in" Left="0in" Top="0in" Value="= Sum(Fields.EQUIV_UNITS)" Name="textBox14" StyleName="Apex.TableBody" />
                    </ReportItem>
                  </TableCell>
                  <TableCell RowIndex="1" ColumnIndex="0" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="0.904167056083679in" Height="1in" Left="0in" Top="0in" Value="= Sum(Fields.EQUIV_UNITS)" Name="textBox17" StyleName="Apex.TableBody" />
                    </ReportItem>
                  </TableCell>
                </Cells>
                <Columns>
                  <Column Width="0.904167056083679in" />
                </Columns>
                <Rows>
                  <Row Height="0.200000017881393in" />
                  <Row Height="1in" />
                  <Row Height="1.00000011920929in" />
                </Rows>
              </Body>
              <Corner>
                <Cells>
                  <TableCell RowIndex="0" ColumnIndex="1" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="1.99791657924652in" Height="0.200000017881393in" Left="0in" Top="0in" Value="Selling Title" Name="textBox5" StyleName="Apex.TableHeader" />
                    </ReportItem>
                  </TableCell>
                  <TableCell RowIndex="0" ColumnIndex="2" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="0.914583265781403in" Height="0.200000017881393in" Left="0in" Top="0in" Value="Unit Length" Name="textBox6" StyleName="Apex.TableHeader" />
                    </ReportItem>
                  </TableCell>
                  <TableCell RowIndex="0" ColumnIndex="0" RowSpan="1" ColumnSpan="1">
                    <ReportItem>
                      <TextBox Width="1.99999976158142in" Height="0.200000017881393in" Left="0in" Top="0in" Value="row Group" Name="textBox12" StyleName="Apex.TableHeader">
                        <Style Visible="True" />
                      </TextBox>
                    </ReportItem>
                  </TableCell>
                </Cells>
              </Corner>
              <RowGroups>
                <TableGroup Name="rowGroup">
                  <ReportItem>
                    <TextBox Width="1.99999952316284in" Height="1.20000004768372in" Left="0in" Top="0in" Value="= Fields.QUARTER_NAME" Name="textBox10" StyleName="Apex.TableGroup">
                      <Style Visible="True" />
                    </TextBox>
                  </ReportItem>
                  <ChildGroups>
                    <TableGroup Name="ROTATION_NAME1">
                      <ReportItem>
                        <TextBox Width="1.99791657924652in" Height="0.200000032782555in" Left="0in" Top="0in" Value="= Fields.ROTATION_NAME" Name="textBox3" StyleName="Apex.TableGroup" />
                      </ReportItem>
                      <ChildGroups>
                        <TableGroup Name="SPOT_LENGTH1">
                          <ReportItem>
                            <TextBox Width="0.914583265781403in" Height="0.200000032782555in" Left="0in" Top="0in" Value="= Fields.SPOT_LENGTH" Name="textBox4" StyleName="Apex.TableGroup" />
                          </ReportItem>
                          <Groupings>
                            <Grouping Expression="= Fields.SPOT_LENGTH" />
                          </Groupings>
                          <Sortings>
                            <Sorting Expression="= Fields.SPOT_LENGTH" Direction="Asc" />
                          </Sortings>
                        </TableGroup>
                      </ChildGroups>
                      <Groupings>
                        <Grouping Expression="= Fields.ROTATION_NAME" />
                      </Groupings>
                      <Sortings>
                        <Sorting Expression="= Fields.ROTATION_NAME" Direction="Asc" />
                      </Sortings>
                    </TableGroup>
                    <TableGroup Name="group3">
                      <ReportItem>
                        <TextBox Width="1.99791657924652in" Height="1in" Left="0in" Top="0in" Value="Total" Name="textBox15" StyleName="Apex.TableGroup" />
                      </ReportItem>
                      <ChildGroups>
                        <TableGroup Name="group4">
                          <ReportItem>
                            <TextBox Width="0.914583265781403in" Height="1in" Left="0in" Top="0in" Value="" Name="textBox16" StyleName="Apex.TableGroup" />
                          </ReportItem>
                        </TableGroup>
                      </ChildGroups>
                    </TableGroup>
                  </ChildGroups>
                  <Groupings>
                    <Grouping Expression="= Fields.QUARTER_NAME" />
                  </Groupings>
                  <Sortings>
                    <Sorting Expression="= Fields.QUARTER_NAME" Direction="Asc" />
                  </Sortings>
                </TableGroup>
                <TableGroup Name="group">
                  <ReportItem>
                    <TextBox Width="1.99999976158142in" Height="1.00000011920929in" Left="0in" Top="0in" Value="Total" Name="textBox1" StyleName="Apex.TableGroup" />
                  </ReportItem>
                  <ChildGroups>
                    <TableGroup Name="group1">
                      <ReportItem>
                        <TextBox Width="1.99791657924652in" Height="1.00000011920929in" Left="0in" Top="0in" Value="" Name="textBox11" StyleName="Apex.TableGroup" />
                      </ReportItem>
                      <ChildGroups>
                        <TableGroup Name="group2">
                          <ReportItem>
                            <TextBox Width="0.914583265781403in" Height="1.00000011920929in" Left="0in" Top="0in" Value="" Name="textBox13" StyleName="Apex.TableGroup" />
                          </ReportItem>
                        </TableGroup>
                      </ChildGroups>
                    </TableGroup>
                  </ChildGroups>
                </TableGroup>
              </RowGroups>
              <ColumnGroups>
                <TableGroup Name="WK_START_DATE1">
                  <ReportItem>
                    <TextBox Width="0.904166877269745in" Height="0.200000017881393in" Left="0in" Top="0in" Value="= Fields.WK_START_DATE" Name="textBox2" StyleName="Apex.TableGroup" />
                  </ReportItem>
                  <Groupings>
                    <Grouping Expression="= Fields.WK_START_DATE" />
                  </Groupings>
                  <Sortings>
                    <Sorting Expression="= Fields.WK_START_DATE" Direction="Asc" />
                  </Sortings>
                </TableGroup>
              </ColumnGroups>
            </Crosstab>
          </Items>
        </DetailSection>
        <PageHeaderSection Height="0.400000005960464in" Name="pageHeaderSection1">
          <Items>
            <TextBox Width="6in" Height="0.400000005960464in" Left="0in" Top="0in" Value="PlanFlowchart2" Name="ReportNameTextBox">
              <Style>
                <Font Name="Segoe UI" Size="14pt" Bold="True" />
              </Style>
            </TextBox>
          </Items>
        </PageHeaderSection>
        <PageFooterSection Height="0.200000122189522in" Name="pageFooterSection1">
          <Items>
            <TextBox Width="1.57480311393738in" Height="0.19999997317791in" Left="4.42519664764404in" Top="0.00000011920928955in" Value="Page: {PageNumber}" Name="ReportPageNumberTextBox">
              <Style>
                <Font Name="Segoe UI" />
              </Style>
            </TextBox>
          </Items>
        </PageFooterSection>
      </Items>
      <StyleSheet>
        <StyleRule>
          <Style>
            <Padding Left="2pt" Right="2pt" />
          </Style>
          <Selectors>
            <TypeSelector Type="TextItemBase" />
            <TypeSelector Type="HtmlTextBox" />
          </Selectors>
        </StyleRule>
        <StyleRule>
          <Style Color="Black">
            <BorderStyle Default="Solid" />
            <BorderColor Default="Black" />
            <BorderWidth Default="1px" />
            <Font Name="Book Antiqua" Size="9pt" />
          </Style>
          <Selectors>
            <StyleSelector Type="Table" StyleName="Apex.TableNormal" />
          </Selectors>
        </StyleRule>
        <StyleRule>
          <Style BackgroundColor="236, 236, 237">
            <BorderStyle Default="Solid" />
            <BorderColor Default="Black" />
            <BorderWidth Default="1px" />
            <Font Name="Book Antiqua" Size="9pt" />
          </Style>
          <Selectors>
            <DescendantSelector>
              <Selectors>
                <TypeSelector Type="Table" />
                <StyleSelector Type="ReportItem" StyleName="Apex.TableGroup" />
              </Selectors>
            </DescendantSelector>
          </Selectors>
        </StyleRule>
        <StyleRule>
          <Style BackgroundColor="105, 103, 109" Color="206, 185, 102" VerticalAlign="Middle">
            <BorderStyle Default="Solid" />
            <BorderColor Default="Black" />
            <BorderWidth Default="1px" />
            <Font Name="Book Antiqua" Size="10pt" />
          </Style>
          <Selectors>
            <DescendantSelector>
              <Selectors>
                <TypeSelector Type="Table" />
                <StyleSelector Type="ReportItem" StyleName="Apex.TableHeader" />
              </Selectors>
            </DescendantSelector>
          </Selectors>
        </StyleRule>
        <StyleRule>
          <Style>
            <BorderStyle Default="Solid" />
            <BorderColor Default="Black" />
            <BorderWidth Default="1px" />
            <Font Name="Book Antiqua" Size="9pt" />
          </Style>
          <Selectors>
            <DescendantSelector>
              <Selectors>
                <TypeSelector Type="Table" />
                <StyleSelector Type="ReportItem" StyleName="Apex.TableBody" />
              </Selectors>
            </DescendantSelector>
          </Selectors>
        </StyleRule>
      </StyleSheet>
      <PageSettings>
        <PageSettings PaperKind="Letter">
          <Margins>
            <MarginsU Left="1in" Right="1in" Top="1in" Bottom="1in" />
          </Margins>
        </PageSettings>
      </PageSettings>
    </Report>
  6. Hinata
    Hinata avatar
    146 posts
    Member since:
    Dec 2013

    Posted 16 Mar 2015 in reply to Jaideep Link to this post

    Hi Jaideep,

    Can you tell me the steps to run this, so I can preview the report and check how it currently looks?
    What is preventing me to check the report at the moment is that I am missing the database data, so I can only see the design of the report.
Back to Top