If you’ve ever created a top N report in Crystal Reports you probably noticed a couple of things. One, the report may still return the same number of records from the database as a regular report. You may also notice that there’s some extra work to make it a dynamic top N and to also have bottom N capability…in those cases you’re building some formulae to do so. While creating those extra formulae may not be a big deal, the performance loss from not bringing the reduced dataset back from the database can be noticeable.

In this blog we’ll look at 3 different scenarios of building dynamic top N or bottom N queries (using a Crystal Reports Command Object) which all let the end user choose top or bottom, N and the measure that they wish to use in the group select.

/wp-content/uploads/2013/10/pic1_307657.jpg

I used the word “select” above as we’re going to select the groups from the database. It’s important to note that when you’re using a Group Selection formula or using the Group Sort Expert that you’re not really selecting the groups but you’re actually suppressing or hiding the remaining groups…this is not a big deal when you’re reporting off of smaller databases, but if you feel that your report could use some speeding up, then hopefully this blog post will help you out.

You may have also tried the Perform Grouping On Server option (in the Database menu) and in many cases, even though you followed the proper steps, that grouping is not being performed on the server. This option works great when your summary types are Maximum, Minimum, Count and Sum, but the other summary types like Distinct Count, Average, etc. at this time disallow this option.

If you’re building a top or bottom N report which is supposed to bring back the aggregated records for 10 groups, your Report > Performance Information should show only 10 records returned. If your report is also bringing back the details records for these groups that number will be higher, but still just a sub-set of the records returned should you be using a Group Selection Formula.

/wp-content/uploads/2013/10/pic2_308114.jpg

You’ll see 3 attachments to this blog, which are basic reports where you can have a look at the various pieces covered in this blog post. Once you extract the content from the .zip files, change the .txt extension to .rpt.

You can still look at the database Command syntax by going to the Database menu > Database Expert > right click on the Command and choose Edit. Just set the database to any existing ODBC connection you have. Make sure to Cancel out of the Database Expert or you’ll get database errors. Unfortunately you can’t change the report using the 3 parameters as you’ll be prompted for the correct database.

Building a Dynamic Top or Bottom N Aggregate Report

To start when you create a new Blank Report, instead of adding tables, choose Add Command. To create a simple dynamic top or bottom N report, your query will in the end look something like the picture below. The syntax is for SAP HANA and your SQL structure or functions may vary depending on your database.

/wp-content/uploads/2013/10/pic3_308115.jpg

Note that while building this command, 3 parameters were added directly in the Command dialogue using the Create button. Do this instead of adding parameters to your report outside of the Command dialogue. For measure (String type) you will want to add a default value that matches a value in your query…for example the default could be COUNT(ORDERID). The reason for this is so that the parameter results are added as direct syntax to your query versus creating a large if then else statement.

The N parameter will be a Number type and the default can be set to 10. Order will also be a string and you can use a default value of DESC. Again, this is an aggregate query that will not bring back the details records. However, you can just edit the query later so that it will be a top or bottom groups query with details records.

Once you close the Command and then enter your values and have no syntax errors, you can edit the 3 parameters on the report. For example, in the Measure parameter I’ve added every value that will be in the query. A description has been added for each value (the value is passed to the query’s ORDER BY clause) and Show On (Viewer) Panel is set to Editable and Prompt With Description Only has been set to True.

/wp-content/uploads/2013/10/pic4_308116.jpg

You should also add an ASC value to the Order parameter. When ASC is selected by your end user, the query will bring back the bottom N groups based on the Measure that they select.

Building a Dynamic Top or Bottom N Report with Details Records

To add the details records to the report (i.e. bring back the 10 or bottom N groups but also show that group’s details records) then we can make an adjustment to the existing Command object syntax, reusing the existing parameters.

Note that the SELECT TOP query is now a sub-query but only the CUSTOMERID is brought back…i.e. all of the summaries are deleted from the query except for the dynamic Measure-based summary “ORDER BY {?Measure} {?Order}” which determines the TOP N customers . This sub-query is then used as the basis for the WHERE clause of the new query which returns details records. If you need an actual Group on your report, this is then done in the report (outside of the Command) as are the summaries that you need for the report.

/wp-content/uploads/2013/10/pic5_308117.jpg

If you’ve gotten this far (you’ve got the query working and you’ve created the group and summaries on your report) and are looking at your report you’ll notice that the groups are not in the order that you want. You can create an ‘if then else’ formula based on the ?Measure and ?Top parameter entries and use that in the Group Sort Expert but there’s no need for that. You can do this by further editing your query which you may find a lot easier than creating the above mentioned formula.

Building a Dynamic Top or Bottom N Report with Details Records and a Proper Group Sort

To add the proper group sort (to sort the groups based on the measure) you need to make some small adjustments to your query.

/wp-content/uploads/2013/10/pic6_308822.jpg

The main change that has been made is that our Top N sub-query is now aliased (as TN) as a table in a join with our existing database table. Also, we’ve added “{?Measure} AS MEASURE” as a column in TN and then used this in the order clause “ORDER BY TN.MEASURE {?Order}” .

To summarize what this command is doing, the aliased table TN brings back the top or bottom N customers based on the chosen measure, our pre-existing table (FCTCUSTOMERORDER) is joined to the N customers in TN, and then the order of the groups is determined by using TN.MEASURE & the Order parameter.

When you finish up your Command object you’ll still need to make one adjustment on your report’s group…go to the Group Expert and make sure that the sort is In Original Order.

/wp-content/uploads/2013/10/pic7_308119.jpg

You may be wondering why you would ever need to use syntax like in the second scenario. For this dynamic top or bottom N report with details records you probably don’t…but if you’re not familiar with sub-queries they can come in handy when you’re mixing aggregate data up with details records and hopefully you found that piece of the exercise useful.

Summary points:

  1. Group Selection is actually group suppression for the groups and records not being shown on the report…these records are still returned from the database.
  2. Perform Grouping on Server is not applicable to all Summary types…only Maximum, Minimum, Count, and Sum.
  3. Creating a dynamic top or bottom N report using a Command object can help to optimize report performance.
  4. The reason for a performance gain with creating a dynamic top or bottom N Command is that the group filtering (and aggregation if applicable) is done by the database.
  5. Command SQL syntax that you’ve seen in this blog is for SAP HANA…your syntax structure may vary depending on your database. Please consult online help specific to your database for the appropriate syntax.

If you are a SAP HANA user, you can find free instructional videos and courses at the SAP HANA Academy. There are also some Crystal Reports videos using SAP HANA as the data source.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Jyothirmayee A

    Nice blog Jamie,

    Quick question, how to get “ALL” values for the prompts than user limit the records by select N value, Measure and Order (DESC/ASC)?.

    Also, If I do a command prompt, I don’t see parameters defined at HANA source. Do we need a universe in that case?.

    Thanks,

    Jothi

    (0) 
    1. Jamie Wiseman Post author

      thanks Jothi.

      for question 1

      one of the ways to do this with the least changes to your report is to edit the parameter you’ve created on the report. this way you don’t need to redo your Command object’s syntax in any way.

      while in the report Field Explorer, right click on the parameter and choose Edit and:

      1) in the Description field enter a description for every Value you have…e.g. add 5 as a Description for a value of 5, 10 for a value of 10…

      2) add a new Value of 999999999999 and then add a Description for this of * or All or whatever you want the end user to see

      3) in the parameter Options, change Prompt With Description Only to True. the end user sees only the Descriptions you’ve entered and they can still enter whatever number they want freehand.

      now if the end user enters * or All for the parameter, all values will be returned.

      for question 2

      you won’t see the parameters defined in HANA at all as they live only in the report itself, and the contents of the Command are ran at the hana level, just like code in hana studio.

      question for you: do you have a need to have the code to reside in the hana repository itself?  if so, consider creating a procedure and running the report off of that procedure.

      cheers,

      -jamie

      (0) 
      1. Jyothirmayee A

        Thank you for the reply Jamie,

        Yes I did gave value “All” for “{?N} prompt . Is it possible to show all the values for Measure and Order too?.

        No, I don’t have a view . I was just using your query to show Top/ Bottom N values.

        Is there a way get HANA parameters in Crystal without sql command pls?. The one you provided is best practice towards the performance and maintenance but still want to find out a way around.

        Scenario is: I get 3 rows extra and HANA view returns just 1 row. When I remove these parameters and run report without parameters I get 1 row.

        Command:

        SELECT

        ” CLIENT_NAME”,

        “TEXT”,

        “FISCAL_YEAR”,

        “FISCAL_YEAR_PERIOD”,

        “FISCAL_PERIOD”,

        SUM(“NSR_CY_PER”) AS “Net Service Revenue”,

        SUM(“CSR_CY_PER”) AS “Client Service Revenue”,

        FROM “_SYS_BIC”.”GL_REVENUE”

        GROUP BY “CLIENT_NAME”, “TEXT” , “FISCAL_YEAR”, “FISCAL_YEAR_PERIOD”,

        “FISCAL_PERIOD”


        Output:

        Capture.JPG

        When I create with parameters , the output gives me 4 rows of data:

        Capture2.JPG

        Thanks again and looking forward for suggestions.

        Jothi

        (0) 
        1. Jamie Wiseman Post author

          hi Jothi, i would recommend either

          a) writing the entire sql inside of the command object based on the tables…i.e. don’t write the command syntax off of the view. then you would create the parameters directly inside the command object.

          b) if want to keep using the view from sys_bic that you base the report directly off of the view as opposed to using a command. this will probably be a lot more work than (a) above as it may involve creating a conditional formulas for the order and the measure etc.  that’s why i would recommend (a) instead of (b).

          (a) is probably the best for this particular situation though.

          for the final result set coming back it’s difficult to see what’s actually happening in your report as the results will depend on the view’s syntax. having a command (essentially a sql query) on top of a view’s query may cause some issues including not having the best performance as the sql is probably not going to be optimized by the hana engine…what will happen is that the view will run returning its record set and then the report will then pare down that record set when it runs. if you create a command with a query based directly off of tables, like in this blog example, then you can ensure that you optimize your query.

          if you’re still having issues with this after using a or b, please do create a new Discussion in scn’s crystal report’s area as this could get quite involved.  if you do post a new Discussion, please email me directly with the thread url using the email address that’s in my profile and if one of the excellent contributors to scn crystal reports hasn’t gotten to it already i’ll be glad to have a look at it. please do include as much details as you can though including any syntax used and if you do continue to use a view, the view syntax.

          (0) 
          1. Jyothirmayee A

            Thanks Jamie,

            Backend is pulling so many measures and creating each one of them with respective calculations will be time consuming.

            I would opt for option “b” , on working with view but is there a way that I get the TOP/Bottom N for each measure at report level. so that user brings all the values first and then select the sorting by measure/records?.

            I have already opened a discussion post and Abhilash did replied with suggestion that includes a link to your blog.

            http://scn.sap.com/thread/3774625

            Please do share your thoughts.

            Thanks,

            Jothi

            (0) 
            1. Ludek Uher

              Hi Guys

              I wonder if you could please take these comments and create an actual discussion? In this way we follow the SCN Rules of Engagement and the actual Discussion can be searched for by those following in your foot steps.

              Many thanks for your understanding,

              – Ludek

              (0) 

Leave a Reply