Reporting Services :: Using Multi-Valued Parameters With Data-Driven Subscriptions
Jun 27, 2009
I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.
Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:
paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))
FROM Reports..SalesForce t2
WHERE t1.SalesManagerID = t2.SalesManagerID
ORDER BY SalesManagerID
FOR XML PATH( '' )), 3, 1000 )
To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.
View 8 Replies
ADVERTISEMENT
Sep 30, 2015
I am looking for a good tutorial on how to set up Data Driven subscriptions through the SharePoint 2012 version of SSRS. I am needing to set up a Data Driven subscription that uses one report, but sends out the specific portion of the report to the appropriate recipients. I have used the information located at URL.... but it doesn't seem to provide all the steps needed to schedule the reports needed. It focuses around setting up the reports to go to shared folder locations. I need something around setting it up to go to email recipients.
View 5 Replies
View Related
Jun 11, 2015
I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types. For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything. I guess I could walk through the interface and try different data types, but if a list exists, that would be better.
View 4 Replies
View Related
Mar 27, 2008
I am running the following ENTERPRISE version of SQL Server, I am admin on the box and do not have the New data-driven Subscription showing up on the Subscription tab.
Does anyone have any ideas why?
Edition Product Level Product Version
-----------------------------------------------------------------------
Enterprise Edition SP4 8.00.2039
View 5 Replies
View Related
Apr 20, 2015
I have a single data driven subscription that is a batch of 250 subscriptions running on SQL Server 2008 R2. All the parameter values for the report are sourced from a database table. During the execution of the data driven subscription 5 of those subscriptions failed due to timeout error.
Final Status as shown on Subscription screen "Done: 250 processed of 250 total; 5 errors."
The SSRS log file has the subscription GUID which is a single record in the reporting server database tables. How can i identify the individual failed subscriptions? Is there an easy way or a work around that i should implement to identify the failed subscriptions?
View 5 Replies
View Related
Jul 28, 2015
get the data from report to create data driven SSRS Subscriptions,
write query :
Running Report:
View 5 Replies
View Related
Apr 24, 2007
Hi,
I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.
As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.
Has anyone tried this before?
Thanks,
Dan
View 1 Replies
View Related
Jun 29, 2006
Using RS2005, how should multivalue parameters be stored in a database field so that a data driven subscription can properly read and use them? I have so far had no luck using syntax of:
1) parm1, parm2
2) parm1,parm2
Do single qutoes need to explicitly wrap the values? Can you please provide an example and a SQL INSERT statement using parm1 and parm2 to demonstrate what to store in the database field?
Thanks!!
View 22 Replies
View Related
Jul 21, 2015
I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .
View 5 Replies
View Related
Jan 14, 2008
I have a MultiValued parameter called Owners, which stores Owner Ids.
I wish to set the value of a textbox to the following:
=iif(3 IN(Parameters!Owners.Value),"foo","bar")
but the "IN" is not recognised.
How can I achieve this functionality using an expression?
Thanks,
Kate
View 1 Replies
View Related
Apr 15, 2007
Hi
i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.
basically, the report gets loads of data back from DataCube.
if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.
but
i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart
so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)
am doing this by trying to get the selected value or "xxxxx" if noting selected
to hide the second chart when only one category has been selected, tried
e.g. for the third chart
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
Parameters!LOOKUPITEMCategoryDescription.Label(2),
"xxxxx")
doesnt work - gives an #Error as a reult
But it works fine when there are > 2 categories selected
also
this works fine when there is 2 or >2 category selected
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
"more than 2",
"not more than 2")
it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!
help please
thx
View 4 Replies
View Related
May 2, 2006
Hi,
after the installation of SP1 I have a problem with multi-valued report parameters.
The option to select all values are gone.
Is there a bug within SP1?
Before the installtion of SP1 multi-valued report parameters works fine, the option to select all values was added automatically within the preview in Visual Studio 2005 and in published reports on the server.
The server was migrated from SQL2000 SP4 to SQL2005.
So long
Lemmi
View 41 Replies
View Related
May 7, 2007
I am creating a Line chart report from a table. The source table includes a code column (String), date column (Datetime), and 12 statistic columns (Int). The dates are end of month dates only. ("1/31/2006", "2/28/2006","3/31/2006", etc.) There are about 8 different codes for each month. The statisic columns are the totals for the month for each code for a particular statistic.
PeriodEndDate Code Statistic1 Statistic2 Statistic3 Statistic4 ...
XX/XX/XXXX XX 999 999 999 999 ...
On my line chart I want to show one or more statistics for one year for one or more codes. (A line for each statistic for a given code) The report has multi-valued parameters for the codes and the statistics. I have figured out how to create the chart for one or more codes for a given statistic. How can I select one or more statistic? Can any one help me out?
Thanks,
Fred
View 1 Replies
View Related
Jul 3, 2007
Hi There.
I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.
This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!
Thanks, Mike
View 4 Replies
View Related
Aug 3, 2015
I have what seems to be a common problem where I have names like "Last, First" that I need to be able to select using a multi-value parameter, which of course, SSRS passes as comma-delimited and gets messed up. I need to find a way to use a different delimiter or pass CHAR(44) instead of the comma, or something. Even worse, some people have suffixes and multiple commas in one name!
View 4 Replies
View Related
May 14, 2015
I have a multivalue parameter that has X options. Is it possible to implement a solution that when I go into the report through an action, to have one option (in this case is the first) not selected all all the others are checked?
View 4 Replies
View Related
Apr 23, 2015
I have created two report parameters and want them as Cascading. District Parameter depends on Region Parameter which should allow Multi selection. When I select single value in Region it works perfectly. But when I choose multiple values, District turns out to be a blank text box. I have used the In clause in my code :
SELECT
DISTINCT
SalesDistrict
FROMDistrict(NOLOCK)
WHERESalesRegion
IN(@SalesRegion)
View 2 Replies
View Related
May 7, 2008
I am trying to selectively show or hide a table based on the values in a multi valued parameter. How can I examine all values (simulate the like functionality) within an expression?
View 4 Replies
View Related
Dec 20, 2007
Hello,
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
Can any one help me with this. Its very urgent.
Thanks,
SqlNew
View 1 Replies
View Related
Dec 27, 2006
I am currently working with 3 multi-valued parameters whose data sources are queries. The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value. Parm3's data source is filtered by the selections of Parm1 & Parm2. The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.
In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry. Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want?
Thanks in advance,
Michael
View 6 Replies
View Related
Feb 21, 2011
I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums. Here is the issue
Software Details:
Datawarehouse Database - SQL Server 2008 R2
Reporting Services Version - SSRS 2008 R2
Development Environment - Visual Studio 2008
Problem Details:
I have been writing reports based of a Relational Datamart with dimensions and facts. The report in question uses dimensions for parameters and facts for content. Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query. It has a total of 6 multi-value parameters. I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render. This is not always the case.
Example:
SELECT *
FROM dbo.MyTable
WHERE
TableColumn0 IN (@Parameter0)
AND
TableColumn1 IN(@Parameter1)
AND ....N (continue syntax to 6 multi valued parameters)
Some of the multi-value drop down lists are defaulting to have every value selected, and some are not. The problem is I need to know why some parameters are not selecting all by default when specified.
View 23 Replies
View Related
Feb 15, 2007
Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:
ReportParm1 String [Deal Dim].[Shelf].&[AAM]
But, how would i define it with multiple values as a 'default value' ?, for example:
ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]
NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.
Any help would be greatly appreciated.
thank you.
View 1 Replies
View Related
Jan 8, 2007
Hi
I migrated my system from SQL2000 to SQL2005, now my MDX reports don't want to work with the data-driven subscriptions. The SQL reports run fine.
Any help, comments, or even suggestions will be very welcome.
Kind Regards
Carel Greaves
View 12 Replies
View Related
Feb 22, 2007
Is it possible to copy an existing Data Driven Subscriptions and make some minor changes?
Elias
View 1 Replies
View Related
Jan 8, 2007
I am using Reporting Services 2005 locally for testing purposes and also on a production server. Locally, I can create data-driven subscriptions, but not on the server. The only difference I can think of between my local test environment and the production server is the versions of SQL server database. Locally, I am using SQL server 2005 but on the server I am using SQL server 2000 (but with Reporting Services 2005). Can the version difference be the reason for not being able to use data-driven subscriptions?
Note: I have followed various guidelines and walkthroughs that describe how a data-driven subscription is created, i.e. stored credentials on the report server and such should not be the problem.
Operating systems: locally; Windows XP. Server; Windows Server 2003.
Thanks!
/Stefan
View 8 Replies
View Related
Mar 19, 2008
I downloaded the sample reports for report execution from http://technet.microsoft.com/en-us/library/ms161561.aspx
to display reporting services report execution data which worked fine until I'd written a report with multi-valued parameters. As soon as this was designed and more importantly run by an user the package used to create the reports crashed.
I've found out why it's breaking mainly through digging around in the script task where the package crashes and also through surfing the net and finding this
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=967081&SiteID=1
thread. I've tried their fix but it still crashes unfortunately.
It seems like the issue is populating an array indexes in the script component in the update parameters data flow task. I think this was written to deal with minimal parameter requests, however I've recently written a multi-valued parameter report and it's made the string containing the parameter details has now hit the maximum length for the string. Changing the WSTR 4000 to a different size doesn't seem to have an effect, and the forum entry above doesn't hint at what the problem is apart from the script not being able to deal with the length.
The error I now get is as follows:
at MyComponent1.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) in dts://Scripts/MyComponent1/ScriptMain:line 35
at MyComponent1.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) in dts://Scripts/MyComponent1/ComponentWrapper:line 68
at MyComponent1.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) in dts://Scripts/MyComponent1/ComponentWrapper:line 25
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
Anyone have any ideas on how to fix this?
View 3 Replies
View Related
Oct 1, 2015
We are facing an issue related to SSRS 2012. We have Reporting Services 2012 configured on a couple of servers. On one server, we have created Datasources and Data-driven subscriptions for our reports and selected email as the report delivery option. But the subscriptions are not working. The LastRunTime column is NULL in the Subscriptions table in the ReportServer database and no email is being triggered. Also, no error message is displayed. The same reporting solution is working on another server that also uses Reporting Services 2012.
View 14 Replies
View Related
Aug 26, 2015
I have a Data Driven Subscription which runs every day for the previous day. If for any reason the report subscription fails, i would like to get the last time it ran successfully. I know in the subscription table we have lastRunTime, but it will show the recent one which failed but i would like to know when it ran successfully last.
Example:
Saturday 5:00 AM - Ran Successfully
Sunday 5:00 AM - Ran Successfully
Monday 5:00 AM - Ran successfully
Tuesday 5:00 AM - Failed
LastRunTime column will have Tuesday 5:00 AM.
Is there a way to get Monday 5:00 AM which is the last ran successfully ?? How can i get the history of a subscription?
View 8 Replies
View Related
May 22, 2007
hi all,
i built my report with BIDS, configured my datasource and report. then i deployed it. on http://localhost/reports, I to go to the Report, but under "subscriptions", I don't see the "create data driven subscriptions" button.
however, under properties/datasources of my report, I clearly configured the
"Credentials stored securely in the report server"-setting.
Did I forgot something??????
Thx
View 3 Replies
View Related
Oct 1, 2007
I am trying to go through the MS tutorials on creating Data-Driven Subscriptions.
http://technet.microsoft.com/en-us/library/ms169673.aspx
I have followed the steps multiple times but when I go to add the Data-Driven Subscription, the option is disabled. The report's datasource is setup to use stored credentials and the server Mail is setup. Is there another prerequisite that I'm missing?
View 1 Replies
View Related
Feb 7, 2008
I have problem with using a data-driven subscription on a report with subreports. It works fine when I remove the subreport and generates error even when I add a subreport which only consists of one textbox.
I just was curious to know if using data-driven subscription on a report with subreports is supposed to work?
Thanks,
View 3 Replies
View Related
Nov 30, 2007
hi guys,
I have created data-driven subscriptions on top of the existing reports on my development server.
I have same reports on QA's report server but without the DD subscriptions.
Is there an easy way to migrate data-driven subscriptions to QA report server or the only way to do is create each subscription manually?
Thankskev
View 3 Replies
View Related