April 15, 2010 at 9:24 am
We have a pretty complex report with 20 parameters.
5 of them are Multi-value parameters
and each Multi-value parameter is populated with 700 rows Dataset.
The problem is that Label field is populated with pretty long strings like
P_RM parameter,for example, has values like
"Agriculture - Delta Dollar - Max Long > 6 months".
And we need to select a lot of values there. Maybe about 80 in some cases.
In Report Manger unfortunately even if the values are long strings, the HTML listbox for that parameter is
very narrow. So you can see only
"Agriculture -.."
You can scroll to the right to read the whole value but
obviously to select 80 values in P_RM parameter
listbox this way becomes a nightmare.
So I decided to do it in ReportServer database.
[Subscriptions] table has [Parameters] column.
But when I copy [Parameters] column value and throw it into Notepad it looks awful.
It's basically 200 lines XML without tree structure. Nothing formatted, Not indented.
Like this:
<ParameterValues><ParameterValue><Name>P_FILTER_RISK</Name><Value>10-CT-IRBasRsk</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>10-CT-IROutRsk</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>10-DCMFI-IROutRsk</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>10-DCM-IROutRsk</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>10-FLM-IROutRsk</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>19-Com-BmDlt</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>19-Com-BmGma</Value></ParameterValue><ParameterValue><Name>P_FILTER_RISK</Name><Value>19-Com-BmVg...
Kind of hard to even find where my P_RM parameter starts.
Is there any good way to update this XML data easily?
I tried to save it as XML file and opened with Excel as a list,
updated P_RM section,
but when I saved it back as XML I wasn't able to open it in Internet Explorer.
It gives Schema error.
Anyway,
If somebody has any good recommendations as to how I go about updating my Multi-value parameters
please share your knowledge . I would greatly appreciate it.
April 15, 2010 at 2:21 pm
perhaps I don't see what you are trying to accomplish here.
I understand the frustration with the size of the drop down list. I've seen this overcome by modifying the CSS file that is used to generate the prompts section for the report or by writing a custom front end.
Why are you trying to change the parameters within the reportserver database directly? I wouldn't recommend this solution as it could be broken by service packs and such that might change the way this is handled behind the scenes...
If you go the custom front end route that will list out the parameters and allow the users a better UI to select them take a look at using a reference to the reportserver webservice. Check out this article it should help. http://odetocode.com/Articles/123.aspx
-Luke.
April 16, 2010 at 9:43 am
Hi Luke L.
You are right. Trying to update ReportServer.dbo.Subscriptions directly is probably not a good idea.
I tried it the whole day yesterday with:
UPDATE Subscriptions set Parameters =
REPLACE(CONVERT(VARCHAR(MAX),Parameters),'qweqwe',
'<ParameterValues><ParameterValue><Name>P_FILTER_RISK</Name ....(the whole XML content) ....
WHERE SubscriptionID = 'fd8afb88-4a0f-41d5-9f43-3fc030bb176e'
and it didn't work. SQL executed fine but when I ran a subscription it generated an empty report.
Perhaps something gets corrupted with that XML that is stored as NTEXT in [Parameters] column.
I read the article you referred me to -
http://odetocode.com/Articles/123.aspx
and it will likely take a few weeks to build a new UI for Reporting Services.
Especially if you want to be able to create new Subscriptions using that UI.
What interests me is hacking CSS directly on Report Manager.
How do I do that?
This seems to be a much faster way to solve my problem with narrow drop-down parameter's list.
April 16, 2010 at 11:00 am
here's a link to an MSDN forum where they talk about it. Just note the post from the moderator talkign about it breaking in future version etc. etc...
Yup, building out a custom UI can be a bit of a pain but gives you a lot more flexibility, though depending on the circumstances it may not be worth it. Are you just wanting to change the parameters for a subscription or for a real time report? If it will just be subscription based have you thought about a data driven subscription? Perhaps you could store the selections in a table in the database and query that for the subscription?
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply