SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Paging A Stacked Bar Chart


Paging A Stacked Bar Chart

Author
Message
MarbryHardin
MarbryHardin
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 153
What our users want, sorted largest - smallest on the x axis for y axis value.

I tried the put chart in list, set list to same dataset as chart, rename details row group, apply ceiling using RowCount to new grouping. And this worked, but it worked to limit the original records BEFORE any of the additional grouping in the chart is applied.

So say the first column in the chart is composed 35 individual records. It would display the chart based on the first 10 of those individual records, next page, another 10 individual records and so on.

I need it to page based on number of columns in the (overall) chart, not individual records in the original data set. Is there any way to accomplish this?
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
I think what you need to do is calculate the y-axis aggregate values in the dataset query using SQL or as a calculated field in the dataset. That way you can leverage the y-axis value for the sorting and grouping of the rows in the data and thus control the paging of the bar chart also.
MarbryHardin
MarbryHardin
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 153
Yeah, I was trying to avoid that since it would add more complexity on the SQL side. There are currently different grouping criteria that are handled in formulas within the report. Some are based on record count, some are based on actual value, or a sum of values.

If there's a way to do that within the report it would likely be a lot cleaner.
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
Perhaps what you need to do is specify the scope of the RowCount function to a higher level of grouping than the immediate, default scope.

For example, suppose there are three levels of grouping on a table: Group A, Group B, and Group C, each one nested inside the previous group such that Group C is the detail grouping, Group B contains Group C, and Group A contains Group B.

If you have a text box in Group C and you have an expression of = RowCount(Fields!field_name.Value), you will get a value of 1 since Group C is the detail "group." But in that very same text box inside Group C you can have an expression of =RowCount(Fields!field_name.Value, "Group B"), and that will usually return a value greater than 1; it will show the number of rows in Group B that are grouped along with the current individual row of Group C. The same logic applies to = RowCount(Fields!field_name.Value, "Group A").

You can even have = RowCount(Fields!field_name.Value, "dataset_name") to return the count of rows in the entire dataset.

Hope this helps,
Geoff
MarbryHardin
MarbryHardin
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 153
Thank you for your suggestions, but I've about come to the conclusion that there is no way to do this. The problem being that I need access to the final generated results, not the raw incoming data. Something like applying filtering after the category axis sorting, not before it.

I'm trying to simulate functionality on another older system users are used to. Horizontal scrolling pages the chart 10 items on the x-axis at a time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search