May 31, 2016 at 3:08 pm
I have a single report with three tablixes (lists) and a "button" (image).
The objective for the report is for a user to be able to "sign-off" their report so their manager knows it is ready for managerial review.
I had this working perfectly at one point, then I streamlined it using parameters and table valued functions and only one of the three tablixes refreshes after the report is "signed".
Each tablix uses a dataset that queries its own table-valued-function with parameters.
I have an image on the report that serves as a button which will:
1. Change a parameter value
2. "Go to report" action which directs back to itself
The parameter value that is changed by the button/image is one that is used in the TVF's.
The idea is that the results of the TVF will be different after the "button" is clicked and the report is "refreshed."
However, only the first tablix is refreshing.
At one point all three were working but that was before I converted to TVF's for the datasets. They were originally using select queries without parameters.
I'm hoping I can resolve this by adding VB code to the SSRS Properites "Code" (or directly in the xml code) that forces the datasets to refresh after the report has been reloaded. But I haven't been able to find any Event code handling examples anywhere (e.g. Onload).
Table-Value-Function pseudocode:
"select all frm TVF(parameter1, parameter2, parameter3, parameter4)"
May 31, 2016 at 5:19 pm
A couple thoughts...
First, I believe there are refresh property settings on tablixes, double check the settings on those.
Are you seeing this behavior in SSDT or when you deploy the report or both?
-- Itzik Ben-Gan 2001
June 1, 2016 at 9:07 am
Hello, thanks for your response 🙂 Also, I didn't mention that I'm actually on SQL Server 2008 R2
I haven't deployed yet, I'm only seeing it in Visual Studio when previewing the report, this development is really just a test to see if any of this is possible. It seemed like it was very possible at first but now I'm just confused why the behavior began after switching to TVF's. Seems like it should be the opposite? I looked through the tablix and dataset properties but didn't see anything about refreshing the data or anything like that, I only saw options for formatting.
June 1, 2016 at 9:15 am
I also forgot to mention that a manual refresh does work for the remaining tablixes which do not refresh with the button functionality.
Basically the sign "button" changes a signed parameter value to 1 for the refresh. A separate Dataset checks this parameter value and executes stored procedures if it is > 0, then sets the parameter back to 0 after executing the stored procedures.
The stored procedures "sign" the rows in the table by setting a column value to 1 for the displayed records.
I've verified that the stored procedures are working, which is evident when I do a manual refresh and when I check the actual data in the tables through SSMS.
Below is the pseudo code for the dataset with the stored procedures:
if signedparameter > 0
'exec' spSigntheserecords thisUser, thisMonth, thisYear, signedValue
--this is repeated three times, once for each tablix basically
'set' signedparameter = 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply