Cross Tab SQL Performance numerous hits

4 posts, 0 answers
  1. Brian
    Brian avatar
    2 posts
    Member since:
    Feb 2016

    Posted 08 Feb Link to this post

    Hi all,

    New user here (long term crystal user...so be nice please).

     I am attempting to create a cross tab report and I am noticing a couple of things. First some background.

    Local install of Microsoft SQL 2014.

    Query in SQL runs in under 1 sec returning 5707 records.

    Telerik is Q3 2015.

    If I just put the fields on the report (no crosstab), the report is rendered in ~ 3 seconds (takes a few more seconds to get to 600 pages).

    I remove all the data fields and start the CrossTab wizard. I have my Customers in as the rows, Year as the columns (limited to just 2015 via the SQL query) and the sales amounts in my summary field.

     Here are my issues:

    1. I have sat for 30 minutes and it never displayed a preview.

    2. I run an SQL trace and see the exact same query hitting my server every few seconds. That query is the same query I used to build the report.

     I am assuming as a new user to the system I have done something horribly wrong.

  2. Nasko
    Admin
    Nasko avatar
    909 posts

    Posted 09 Feb Link to this post

    Hello Brian,

    Telerik Report consists of a number of sections, each of a different type and with a different behavior.
    There are sections which will appear only once in the report (Report Header/Footer) and sections which will be repeated once for each data record in your data source (Detail Section). More information on these can be found in the Report Structure help article.

    There are also Data Items in Telerik Reporting. Data items often contain a detail row/section which will be repeated for each data record in your data source.

    If you by mistake place a data item (e.g. Crosstab) inside the Detail section of the report, the whole data item will be repeated together with the Detail section of the report. In your case, you are trying to generate a report containing 5707 crosstab items, each of which queries the same 5707 data rows again and again.
    To resolve this, you need to either place the Crosstab item outside of the detail section or set the Report.DataSource to none.

    Regards,
    Nasko
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. DevCraft banner
  4. Brian
    Brian avatar
    2 posts
    Member since:
    Feb 2016

    Posted 09 Feb in reply to Nasko Link to this post

    Good Morning.

    I completely understand what you are saying and I am going to try to get a video attached to this issue to help show the problem. 

    I thought it was very strange that the crosstab object would not insert into the page header/footer section. This would explain why it was repeating the data.

    My process is this:

    1. Create a new file.

    2. Set the datasource query to a view I have built (simply flat query with no pivot in it).

    3. Click on either the page header/footer section (bounding box is now blue in color).

    4. Attempt to click on the insert ribbon and select the crosstab option.

    5. All options are disabled on the ribbon.

    6. Click on the details section of the report and notice that the crosstab object is now available for selection.

     Again, i will get a video attached on this issue as I am assuming I am missing a simple step.

     

    Thanks again,

    Brian

  5. Nasko
    Admin
    Nasko avatar
    909 posts

    Posted 09 Feb Link to this post

    Hello Brian,

    The Page Header/Footer section does not support data items. This section and its items are processed by the corresponding rendering extension after the report data has been processed. The implications are described in the Report Structure help article.

    You can create a static Group Header/Footer section or a Report Header/Footer section which can contain the Crosstab.

    Regards,
    Nasko
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top