Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner - Contributor III
Partner - Contributor III

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


View solution in original post

8 Replies
Not applicable
Author

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
Author

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! 🙂

Anonymous
Not applicable
Author

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
Author

Not exactly what I was looking for... 🙂

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
Partner - Contributor III
Partner - Contributor III

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
Author

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! 😄

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
Author

Hello.

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

Regards

BL

Not applicable
Author

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