locked
Limit the data points in a chart RRS feed

  • Question

  • Hi,

        I have a requirement to create a bar chart where the data points on display have to be limited to 5 per page. When there are more than 5, a page break should be enforced and the rest of the data should continue on the next page. Is this possible? Any help would be appreciated. Thanks.

    Thursday, July 15, 2010 2:19 AM

Answers

  • Hi Unarmedcivlian,

    Based on your scenario, I get that you only want to display 5 data points on the Y-Axis of bar chart. The other data points show be displayed in the follow pages. This requirement could be achieved in Sql Server Reporting Service by utilizing the list control. For detailed steps please follow these:

    Supposing the datafield on the Y-Axis of bar chart is MonthName to display “Jan, Feb,Mar,……..”, besides this datafield, you must have another datafield MonthNumberOfYear(1,2,3,......) which datatype is INT

    1.     Design your bar chart with datafield MonthName on the Y-Axis.

    2.     Drag a list control into the report body, right-click the list ,and then select Properties

    3.     Click Edit details group…button, and then type in the expression =INT((Fields!MonthNumberOfYear.Value-1)/6)

    4.     Click the checkbox of Page break at end at the bottom of the dialog box.

    5.     Switch to Sorting tab, and then type in the expression =INT((Fields!MonthNumberOfYear.Value-1)/6) as well, select a direction depending on your requirement.

    6.     Click OK.

    Preview the report; you will see the bar chart with top 6 months on the page1, and other 6 months on the second page. Just like these pictures:

    Page1:

     

    Page2:

     

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by Challen Fu Friday, July 23, 2010 8:41 AM
    Monday, July 19, 2010 2:01 PM

All replies

  • This is not available out of box. You will have to implement that as a manual logic. Following is what I could think:

    1. Build the entire result set as per your business needs

    2. Load the final result to a temporary table and attach an identity column to it

    3. Have two text boxes about the bar chart with Texts "Prev" and "Next" respectively.

    4. On both of the text boxes, define action so that you jump to the same report and pass the max identity value that is shown in the current result set and have that as a parameter to the dataset source as well

    5. While displaying the result set, SELECT only those rows which are having more than that Id value followed by 5 values.

    Below is the example:

    @CurrentMaxIdentity in report = 15

    Next result would range between (@CurrentMaxIdentity + 1) and (@CurrentMaxIdentity + 5).

    Similarly the Prev logic by subtracting the numbers

    and so on...

    Hope this helps.

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    Thursday, July 15, 2010 5:15 AM
  • Hi Unarmedcivlian,

    Based on your scenario, I get that you only want to display 5 data points on the Y-Axis of bar chart. The other data points show be displayed in the follow pages. This requirement could be achieved in Sql Server Reporting Service by utilizing the list control. For detailed steps please follow these:

    Supposing the datafield on the Y-Axis of bar chart is MonthName to display “Jan, Feb,Mar,……..”, besides this datafield, you must have another datafield MonthNumberOfYear(1,2,3,......) which datatype is INT

    1.     Design your bar chart with datafield MonthName on the Y-Axis.

    2.     Drag a list control into the report body, right-click the list ,and then select Properties

    3.     Click Edit details group…button, and then type in the expression =INT((Fields!MonthNumberOfYear.Value-1)/6)

    4.     Click the checkbox of Page break at end at the bottom of the dialog box.

    5.     Switch to Sorting tab, and then type in the expression =INT((Fields!MonthNumberOfYear.Value-1)/6) as well, select a direction depending on your requirement.

    6.     Click OK.

    Preview the report; you will see the bar chart with top 6 months on the page1, and other 6 months on the second page. Just like these pictures:

    Page1:

     

    Page2:

     

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by Challen Fu Friday, July 23, 2010 8:41 AM
    Monday, July 19, 2010 2:01 PM