Report prompt timing out due to complex variable

building reports
1

Report built in Webi 4.2, six queries linked with 7 merged dimensions. Prompting on 1 text field with about 100 values, and a date range.

I have a series of complex nested variables, each “series” uses 9 nested variables to calculate 2 values linked to a participant record. The series is duplicated 10 times, so 90 total variables are calculating up to 20 values for each participant record. Ultimately, I need to have all 10 of each value for each participant record to appear in a cell. I created a variable to concatenate all values, it works perfectly. Here’s where it gets weird.

When I refresh the report, it times out. This happens before offering me a prompt, so it’s not an issue of too much data. It was suggested to me that the complexity of the variables could be hanging it up, so I stripped down my “show all” variable to only show the first 3 values in series. The report refreshes with this setup, so I created [Values 4-6] and [Values 7-10] variables. Placing each of these variables in separate cells in a table still causes the report to not provide a prompt for 10+ minutes.

Here’s where it gets really weird. If I take the 4-6 and 7-10 variables out of the table, I get a prompt and the data refreshes fairly quickly. The variables can be in the report, and after data has refreshed, I can add the 4-6 and 7-10 variables to any table and they work. What is the report trying to do before data is refreshed, even before prompting, that would hang it up, and how can I work around it?

2

Perhaps the prompt needs to do a full table scan when you include those variables?

3

If so, is there a way to disable this? A coworker mentioned that WebI prepares for conditional formatting before making calculations, so I stripped all of those out also. refreshing the List of Values for the prompt was mentioned, but that list populates quickly when the variables are not placed in a table.

4

Could you try to do a select query on the database using those variables to see how the response time compares, if that is responsive perhaps you can create a view, then use the view to populate the prompt. That is where I’d usually start to look.

5

Update on this- The prompt display is not the problem. I deleted the prompt and hard-wired the query filter data. When the complex variables are not in a table, the data refreshes quickly. When I add 1 variable, the refresh time slows to around 2:00, but checking the query summary, none of the 3 queries are taking more than :05 to refresh.
I currently have 3 object merged across each of the 3 queries. These could be deleted if it will help the performance.

I also saw a post saying it was possible to force calculations to be done AFTER data retrieval, but I can’t find a way to set that, either within a variable or globally for the whole report.