August 20, 2007 at 10:38 am
Had a request which I've no idea how to takle: They want parameters to be remembered for each report. So that when user A views report and set params P1, P2, ... Pn., these are then set automatically (replacing param defaults) when they next view the page. But user B, want different params to be remembered to user A.
Ideally a cookie with the last selected params, that gets read when you load the report to overide the report param defaults.
Is this at all possible?
August 20, 2007 at 11:56 am
If not in a cookie, this set of tables will solve your problem :
Reports
ReportId
ReportName
ReportsParameters
ParameterID
ReportID
ParameterName
Users
UserID
UserName
UsersParameters
UserID
ParameterID
ParamValue
Then just select those values from the db and overwrite the default parameters when they exist. I'm assuming that this part is possible but I've never done anything like this.
August 21, 2007 at 5:13 am
if your report is based on a stored procedure, you can capture the UserID and the parameters they've chosen in the tables like the ones Ninja suggests. The default value for a parameter can then be calculated by checking the 'from query' option and choosing a dataset you've already created which picks up the parameter value for that UserID.
August 21, 2007 at 5:34 am
I'm using procs to do the query, however I've found it better to not pass params into the proc, rather have the proc return a full resultset, then let the report do the filtering. This way ensures the full resultset is cached for later queries or others to use. Otherwise others will only use the cached resultset if they select the very same params to pass to the proc. This leads to a long query every time different params are set.
So although on the face of it, it seems better, more versatile and quicker to filter results in the proc, its actually better to filter in the report so that you can get more cache hits, which is faster overall.
So, getting back to the thread, the tables to store users params is a good one. I'll have a look into it. Any ideas how I could get the report to update the tables?
August 21, 2007 at 12:50 pm
What are you using to actually produce the report? If you are going to have sql to retrieve the values, you will need to store the sql in a similar process, or at least capture the current combination shortly before running the report. Whether you store it 'permanently' or temporarily and then make it permanent after the user indicates he/she is happy with the results is up to you.
August 22, 2007 at 6:42 am
I'm using a stored proc to produce the report. However I'm not passed this proc any params, as mentioned in my last post, the params are used for filtering within the report itself.
I was just wondering how I capture these params elsewhere for saving to a table. Do I just add another data source, which calls a second stored proc, which does take this params?
August 22, 2007 at 9:16 am
I think it would depend on when / where you actually receive the parameters that filter the report. Also, how you would redeploy them the next time, and whether the user will be prompted to revise them the next time (and whether you need a different process to capture any changes). It could be in a stored procedure (keeping all content and control on the database side), or you could even use a dynamic storage from the application (not recommended), or other solutions as well.
August 23, 2007 at 3:34 am
I think the way I'd like it to work is something like this:-
User opens report, data set reads any previous params from database and sets the params displayed on the report to these. If no previous params exist, defaults could be used.
Then users can hit 'View Report' possibly tweaking the params beforehand.
The current params are then saved to the database and the report is displayed.
So I think I could rig a data set to save and load the params easily enough. I'm just not sure how I update the param controls actually displayed on the report.
Most of my params come from a query/view/func, which I union with a row for wildcards like 'all' etc. Perhaps I could union in a query for the previous param or a default value. So my param drop down read like:-
Previous: Foo
option 1
option 2
...
Using a data set like:
Select Value, Desc from FetchPrevParam('ReportBlar', 'ParamThingy')
union
select 0 as Value, '' as Desc
union
select Value, Desc from table_options order by Desc
Hmmm, have I just answered my own question, will have to give it a shot.
Also I've just noticed my shared data source, is not using Windows integrated security, so any SYSTEM_USER querying isn't going to work. Can't remember why I did it like that...
August 23, 2007 at 4:00 am
Just remembered, I can't use windows integrated security, as these credentials aren't remembered for scheduled snapshots etc. So I may need another way to pass the current user to the param saving function/proc.
August 28, 2007 at 3:10 am
Just send User!UserID as a parameter.
August 28, 2007 at 4:31 am
"User!UserID" has the value I want, but how do I reference this from within a query in a dataset?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply