Limit rows displayed in crosstab

[Former Member]

Before I ask a question let me provide you with the scenario.

I'm working with a data source that has a high probability of exceeding the number of cells Design Studio can display in a crosstab.  Given that the data source could exceed the capabilities of the crosstab my solution is to restrict the number of rows displayed at a given time with the ability to cycle through the rest of the data.

Simplistic Example: Lets say I have a data source that returns a total of 500 rows and DS has a limit that can only show 400 of them in a crosstab.  When the dashboard loads the crosstab will display the first 100 of those records.  There will be a button the user clicks that will take them to the next 100 records, if they click it again it goes to the next 100 records, and so on until the last 100 records are shown (rows 401-500 that otherwise wouldn't be displayed).

So, the question is how can I limit the data displayed within a crosstab and have the ability to cycle through the rest of the data?

Thanks in advance for your help and ideas. 

Things to note:

I'm using DS 1.4

The data source is a Calculation View

[MustafaBensan · Accepted solution]

Hi Matthew,

What you have described is a pagination mechanism for the crosstab.  The crosstab component is already capable of handling large data sets and has an internal paging mechanism to achieve this.  When you say in your scenario there is a "high probability of exceeding the number of cells Design Studio can display in a crosstab", can you be more specific about the upper limit of the number of rows you expect?

There is currently no mechanism to programmatically implement crosstab paging via scripting and a pagination button as you have described.  The only way to limit the data set would be to implement guided filtering or drilldown.

If you can provide some more details about the user interaction that influences the data set size in your application we can try to make suggestions accordingly.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hello Mustafa,

Thank you for your response.  The reason I mentioned exceeding the capabilities of the crosstab is because I was told that the limitations of DS was 10,000 cells.  I now know this is not correct and expect this number was derived from an Xcelsius limitation, or so I'm told.  The upper limit for the data I'm working with would be a couple 100,000 rows.  In most cases the upper limit would be a few thousand but normally I'd expect a few hundred rows of data.  I was able to get my hands on a good data source today and tested a crosstab with 10,000+ rows and 240,000+ cells which seem to load just fine.

Regarding my original question, the reason I was looking for a pagination mechanism was to work around my perceived DS limitation.  Do you know what/if there is a crosstab row limitation?  It would seem I no longer need to pursue crosstab paging further but if there is a crosstab row limitation I may have to think of another work around.

Thanks again for your help.

Regards,

Matthew

[MustafaBensan · Accepted solution]

Hi Matthew,

A crosstab should be able to handle your upper limit of a couple of hundred thousand rows (albeit the data source load may be a little slow depending on your backend).  I am not aware of a specific limit on the crosstab component itself but the limit is more so on the data source depending on the platform.  For BW data sources there is a hardcoded limit of 500,000 cells and for Universe data sources the limit is 50,000 cells.  I'm not sure if there is a limit for HANA.

Based on your data volume I'd say there probably isn't a need to implement crosstab paging.  If you had to do this though, since there isn't a scriptable paging mechanism available for the crosstab, as I mentioned previously, you could consider implementing a guided filtering or drilldown approach.

Regards,

Mustafa.

[IngoH · Accepted solution]

HI,

to my knowledge the components - even the standard components - are also based on the Design Studio SDK and they will face the same limit : 10.000 cells.

the hard coded limits per data source are :

500.000 cells for BW

5.000 rows for the Universe

regards

Ingo Hilgefort. Visual BI

[MustafaBensan · Accepted solution]

Hi Ingo,

I believe the crosstab is a special case and can definitely handle significantly more than 10,000 cells, although it doesn't have an explicit limit.

Regards,

Mustafa.

[Former Member]

Hi,

In Properties of CROSSTAB .

If you have Enabled Pixel-Based Scrolling .

Set The Row Limit to YOUR_DATA_LIMIT_HERE

[MustafaBensan]

Hi Nikhil,

The Pixel-Based-Scrolling option appears to be for allowing smooth scrolling through the entire data set when the data volume is quite low in the first place.  If you set the Row Limit to anything less than the number of rows in the data set then instead of implementing paging, the Crosstab displays an interesting torn page effect without the possibility of seeing the rest of the data, as shown below:

The data set I used above only has 317 rows.  If I set the Row Limit to 317 or above, then I can scroll through the entire data set in the crosstab.  If I set the Row Limit to anything less then 317, like 20 for instance, then I only see the first 20 rows but there doesn't appear to be the possibility of seeing the rest of the data through scrolling or paging.

Have I missed something here?

Regards,

Mustafa.

[Former Member]

Hi,

You are right , There is no Scrolling or Paging option available .

I misunderstood the Question .

[MustafaBensan]

Well at least you gave me an opportunity to explore the pixel-based scrolling option in the crosstab

[TammyPowlas]

Could you consider something like background processing for your application?  See Dirk Mayrock's post here on the topic: