is it cache or something else
-
hi a few days ago i narrowed down the cause of a new problem in ssrs to 2 columns (of maybe 50) in a view. nobody is claiming responsibility. the calcs for both are hard to look at. when i eliminated the 2 columns, the view ran in ssrs fine.
if you haven't seen this before ssrs sometimes complains about a division by zero and how it cant get the next row even though t-sql can run the same query without aborting. i've fixed this issue before.
what im doing for the current issue, is eliminating the 2 columns from the view and planning to break them up into their pieces parts.
what i saw on the 2nd day after totally eliminating the 2 columns and even temporarily throwing away the slow intro of pieces parts, was that the view was aborting again in ssrs. while other things could suddenly have been broken on other columns being changed, im suspecting ssrs cache is at fault. at least until im proven wrong.
does the community know how at the report level i can clear cache in ssrs so im testing apples to apples in ssrs's use of views?
-
Connect to Report server in SSMS .
DELETE FROM ReportServerTempDB.dbo.ExecutionCache
Restart SSRS Services
-
You can also disable report caching.
https://iatric.com/ssrs-tip-adding-a-tool-to-clear-the-ssrs-data-cache/
-
I would think your problem is going to be a field where you are doing a calculation, also look at if your are grouping with totals.
-
hi tav29. i noticed it ISNT cache because in the cache properties of the report it said to always pick the latest results.
so i narrowed again and focused on one field (calcdfield) that indeed is the result of a sum from another view (call it v2) and then used in my view (call it v1) in yet a further v1 calc.
in v2 the calc'd field (obfuscated) looks kind of like this ISNULL(SUM((colA * Constant1 / colB)
* (colC / (CASE WHEN colD = 'XX' THEN Constant2 ELSE 1 END))), 0) AS calcdfield
referencing calcfield in v1 in just the list of returned columnss aborts. but replacing the latter calc with the value 1 and referencing calcfield in v1's return list or further calc doesn't abort.
how did you know a sum was involved? and what do you do to correct it?
-
I'm not sure what your issue is? Is it timing out? or giving you an error? Make a copy of you two views as a backup, just in case.
Does your view run to completion in SSMS?
Try this:
ISNULL(
SUM(
colA * Constant1 * colC /
(colB * CASE WHEN colD = 'XX' THEN Constant2 ELSE 1 END)
), 0
) AS calcdfieldAre your calculations just in the views or are you also doing calculations in the report ? besides group totals.
-
hi tav29. SSRS is giving an error ive seen once or twice before ... something about divide by zero and cant get next row. but if i run the view outside of ssrs it completes without error. i took the group by and sum off the offending calc in a second layer view and ran it in ssms. the ten or so rows of factors and results that would otherwise be summed as i showed show nothing suspicious in the way of nulls or really close to zero (very few significant digits right of the decimal) values that one might think could explain/lead to a divide by zero. calcs ate just in the views. will look shortly at the diff between my calc and your recommended calc.
-
Try this:
ISNULL(
SUM(
colA * Constant1 * colC /
(NULLIF(colB, 0) * CASE WHEN colD = 'XX' THEN Constant2 ELSE 1 END)
), 0
) AS calcdfieldAre you making any calculations in the report?
-
hi tav29, i ran with your recommended alternative calc. same error. same calc.
-
Are you making any calculations in the report?
-
you ran this?
ISNULL(
SUM(
colA * Constant1 * colC /
(NULLIF(colB, 0) * CASE WHEN colD = 'XX' THEN Constant2 ELSE 1 END)
), 0
) AS calcdfield
-
hmmmm.....
ISNULL(
SUM(
CASE
WHEN colB = 0 THEN 0
ELSE colA * Constant1 * colC /
(colB * CASE WHEN colD = 'XX' THEN Constant2 ELSE 1 END)
END
), 0
) AS calcdfield -
hi tav29. no calcs in the report. yes, i ran what you suggested. also, i saw the underlying data so im reluctant to try other algebraic solutions. my boss is gonna take a look. its possible im missing something fundamental.
-
Stan, you said the report is set not to cache, but the error indicates a shared dataset. Have you checked the shared dataset's caching settings, not just the report? (JIC)
I'd also take a look at the other settings of the shared dataset rather than the view you are looking at. What's the actual query for that shared dataset, is it simply select * from the_view, or does it have its own complexity? What happens if you do a data preview for it in Report Manager? This will only give you 5 rows, but it will suffice to see if the error ALWAYS happens for the shared dataset or requires some specific rows to show up.
If not that... if the view runs fine externally, and for all rows, it does sound like the report is tripping up on a percent of total or similar group calculation.
-
thx Lisa, sorry for the delay, i just got back.
i got the same error embedding the query , ie not using a shared dataset.
the whole thing is pretty complex especially in the layers of selects from other views. im just the messenger so i cant explain the reasons for the complexity.
the offending columns arent used in the report. and the outermost view doesnt apply a pct on them. however, one layer down there is a sum etc over a group by which i think i showed earlier in this post and tav29 tried his best to dissect but i'll go back to make sure. I simulated that logic showing the underlying values being grouped and didnt see anything suspicious. most of the results are <1 fractions (i think 5 places) but with significant digits in the tens or 100ths place.
i feel like im missing something obvious.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply