Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Limit to the number of rows that a user can export to excel

Hi to everyone,

Does any one knows if when exporting to excel, is there a way to set a maximum row limit? (So the users cannot export more than 20 rows, for example)

Or at least a maximum memory limit?

Users can access to the published document using only ie plugin.

Thanks in advance!

1 Solution

Accepted Solutions
etay_netintent
New Contributor III

Limit to the number of rows that a user can export to excel

Hello

I have run into the exact same issue, and I used a work around.

I have a macro that checks for the number of records and only if they are lower then X sends to excel.

If the number of records is too large a MsgBox pops up for the user.

I have removed the send to excel icon from the table and placed a small button with XL as description that triggers the macro.

That way the user can see everything - but unless he limits the record number he is unable to export

Here is the SUB

SUB SendChartToExcel

SET chart = ActiveDocument.GetSheetObject("POLICIES")

SET p = chart.GetProperties

SET v = ActiveDocument.Variables("vPolicy_Count")

IF v.GetContent.String > 20000 THEN

MsgBox("Please Limit Export to 20,000 Records")

ELSE

chart.SendToExcel

END if

END SUB

Have fun !!
Etay Elazar


8 Replies
Not applicable

SV:Limit to the number of rows that a user can export to excel

There is a max memory value to be set for Export in the command center for Qlikview server (Sheet performance). The same can be set on document level (User preferences - > Export).

/Michael

Not applicable

SV:Limit to the number of rows that a user can export to excel

So if I understood correctly you're telling me to go to the server. In my case would be using Enterprise Management Console and there:

System/Setup/Command Center/Qlikview Servers/(the correponding server)/Performance

And in this tab setting the 'Export' value in the Max Memory section. However, according to the reference manual: "The Export setting specifies the memory allocation that QlikView server may temporarily use when exporting data. Setting this value too high may cause the server to run out of memory."

So it's not a limit for the data you export, but a limit for the server memory resources when exporting, no? Anyway, I've tried before and it didn't work...

But thanks! Smiley Happy

paul_w_routledg
Contributor II

Limit to the number of rows that a user can export to excel

For the chart that can be downloaded to Excel we place a calcualtion condition limiting the number of rows the chart can show. This forces the user to apply some filtering before the chart will dispay.

Chart -> Properties -> General -> Calculation Condition

Count (keyfield) < excelrowlimit

Of course this fundamentally limits tha actual that chart in QlikView rather than just the Excel export. But it might be an option.

Not applicable

Limit to the number of rows that a user can export to excel

Not exactly what I was looking for... Smiley Happy

Now it seems to me that the only solution would be developing a macro, although it's not supported and it's not exactly my strongest point.

Thanks Paul.

etay_netintent
New Contributor III

Limit to the number of rows that a user can export to excel

Hello

I have run into the exact same issue, and I used a work around.

I have a macro that checks for the number of records and only if they are lower then X sends to excel.

If the number of records is too large a MsgBox pops up for the user.

I have removed the send to excel icon from the table and placed a small button with XL as description that triggers the macro.

That way the user can see everything - but unless he limits the record number he is unable to export

Here is the SUB

SUB SendChartToExcel

SET chart = ActiveDocument.GetSheetObject("POLICIES")

SET p = chart.GetProperties

SET v = ActiveDocument.Variables("vPolicy_Count")

IF v.GetContent.String > 20000 THEN

MsgBox("Please Limit Export to 20,000 Records")

ELSE

chart.SendToExcel

END if

END SUB

Have fun !!
Etay Elazar


Not applicable

Limit to the number of rows that a user can export to excel

That should work. I'll ask you if I have any problems.

I would have prefered not to use macros, but in this case it seems the only option.

Thank you Etay! Smiley Very Happy

Edit: FYI, although it's logic, it should be pointed out that the macro works even if the option 'Allow Export' in 'Document Properties/Security' its disabled.

Not applicable

Re: Limit to the number of rows that a user can export to excel

Hello.

The solution looks like something I´m looking for but where and how to you set "vPolicy_Count"?

Regards

BL

Not applicable

Re: Limit to the number of rows that a user can export to excel

What is  "vPolicy_Count" here? Is that variable updated based on changes on the filters? I meant are there any triggers associated with it?

Community Browser