Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

[#1]

I have created dynamic report based on conditional show/hide of expression and dimension (Qv 11 feature). Report is generated fine but the Qlikview in-built Export to excel is taking too long (Export to excel takes 8 mins just to export 2000 rows with 30 columns).

More details

1) Report is created with Straight table having 80 dimesions and 30 expression with paramaterized variables (conditional show or hide based on user selection) So only few will be enable based on user selections.

Ad-hoc report (Straight  table)

     Dimension tab:

Expression tab:

2) I have tried export to csv using macro and its just taking 1 min but we must have to use AJAX (so i believe macro is not possible.)

3) We are using QV Version 11.2 (SR 7) and 64 bit. (I read post about some patch from QV to improve export performance but its old post (around 2012) and not sure if its applicable to this version - Extremely slow Excel export)

I believe inbuilt Export to excel trying evaluate all dimensions (all 80) and expression (all 30) even though user has selected only few and could be the root cause.

Any suggestion or design change to improve the Export to excel performance?

[#2]

Any suggession to above approach?

or

What is the best approach to allow user to create ad hoc report with available selection of dimension (around 80) and expression (around 30)? Most important thing is the inbuilt (without macro) export to excel should work normally.

[#3 · Accepted solution]

Sharing below solution to benifit all.

Issue:

The root cause was the use of calculated dimensions (i.e. =$(vReportDimName(1) etc.).  While exporting to excel it evaluates this expression and get the actual dimension name.

Solution: I have replaced =$(vReportDimName(1)) and other dimensions with actual dimension names (i.e. replaced actual column names avaible in data model like Region, Country etc). It boost the performance and now it just take few second (around 30 second) to export 80 dims and 50 expression.

Please note that i have just replaced expression with acual column/dimension names in Used dimension part. I have still kept parameterized expression for the "Enable Conditional" and "Label".

Label Expressions for each columns

=$(vReportDimDisplayName(1))

=$(vReportDimDisplayName(2))

=$(vReportDimDisplayName(3))

Underlying Variable

vReportDimDisplayName  =             ({<[_dimension Field Name] = {$1}>} [_dimension Field Name])

The [_dimension Field Name] is coming from xls file and used to generate avaible report demesion list.

Further Enhancement: I have created one time macro and button to execute that. The macro generates the above dynamic report straight table based on .xls file. It can be improved to run the macro post reload without clicking any button so that any new additional report columns in xls will be automaticaly added during every refresh in the straight table and to user for ad-hoc/self service report.

[koresaket]

Hi Dharm,

I'm currently facing the same issue wherein I've used over 100 calculated dimensions to create a dynamic report. The Excel exports take forever. We raised this issue with Qlik and they've recognized it as a bug.

Lets hope they fix this in the upcoming service-release!

[#5]

Hi Saket,

what SR have you had that acknowledged as a bug for? I have recently move to SR10 and finding the export slow here.

Joe

[koresaket]

Hi Joe..

The issue has not been resolved with any of their service-release till date.. They said they'll try to fix it with the next one! Hope so this gets resolved with SR13.. if there's going to be any.

[#7]

Hi Saket,

do you have the issue number that Qlik created for this if possible?

thanks

Joe

[#8]

Saket/Joe, The use of many Calculated Dimension surely causing the export to excel slow.

I have made following changes for export work around (while qlik improve calculated dimension export enhancements).

1) Try to use Actual Column names coming from data model (in below screen the Region, Country, State are my data model columns.)

2) Try to avoid use of calculated columns as much as possible (if possible move that logic to data model/scripting side).

3) Use Export to CSV (instead of Export to Excel, where ever possible.)

[amien]

@Dharm or someone else.

I have 3 questions:

* I'm running 11.2 SR9. Is there much improvemnt in SR12? Is it worth upgrading for this reason?

* Why do i need to set the label? Can't i just the expression as label? As i'm using column names from the datamodel

* Anyone got a tip for optimising this?

I have this string:

SET vGetFieldSelections = Replace(
GetFieldSelections(sel1,'|',100) & '|' &
GetFieldSelections(sel2,'|',100) & '|' &
GetFieldSelections(sel3,'|',100) & '|'

,'||','|')
;

SET vDimCustomReport = SubField($(vGetFieldSelections),'|',$1);
SET vConditionDimCustomReport = Len(SubField($(vGetFieldSelections),'|',$1));

My chart:

Dimension : =$(='[' & $(vDimCustomReport(1)))

Condition on Dimension : $(vConditionDimCustomReport(1))

Label : <Use Field Name>

Thanks in advanced

[#10]

Hi Amien,

1) I have implemented above solution and its working well in SR7 (with 200+ dimensions). I haven't tried on SR12.

2) You can keep the Label : <Use Field Name> as long as you want to keep report column name exactly same as data model column.

in my case, I have used Expression to populate Label because user wants user friendly name for column heading. Sometimes which are different than data model column name. I have created xlsx mapping file which has data model column name and related user define name.

3) I would advise you to use Actual data model column name in the Used Dimension box otherwise you will face major performance issue.

Dimension : =$(='[' & $(vDimCustomReport(1)))      should be replaced by actual data model column name