.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Creating a subscription that sends out an email for each value in a multi valued parameter

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server
 

I have a report that has  a paramter (drop  down) which has about 1000 possible values. I need to create a subscription that generates an excel sheet for each value in that paramter and send it as an email attachment to a selected set of people based on the paramter chosen.

assume that the paramter is called bank name and has the following possible values, pnc, wachovia, boa..

when the subscription runs

it should run the report with the parameter value for bankname as "pnc" generating an excel and emailing it to user@pnc.com

then it should run the report with the parameter value for bankname as "wachovia" generating an excel and emailing it to user@wachovia.com

then it should run the report with the parameter value for bankname as "boa" generating an excel and emailing it to user@boa.com

and so on

What is the best way to accomplish this

 

Thanks for any help in advance

 


smathew


View Complete Post


More Related Resource Links

multi-valued parameter

  
I need to do something like this: DECLARE @SalespersonCode SET  @SalespersonCode = '''smith'' + ',' + ''jones'''@SalespersonCode = '''SMITH'',''JONES'',''ADAMS''' SELECT * FROM Salesperson WHERE SalespersonCode in (@SalespersonCode) but I cannot get any results to return.  Is there a way to put multiple parameters in a single variable to use with in IN statement? John Schroeder* FROM Salesperson@Salesperson Varchar(100)

SSRS 2005 Error: Cannot add multi valued query parameter for data set because it is not supported b

  

Hello,

getting the above error when running a report in SSRS 2005, using a stored procedure with many input parameters,

connected to a Sybase IQ database,

I want to have some of the prompts to be multi valued prompts.......

Please advise on how to fix......

 

Thanks.


Cutting Edge: Creating a Multi-table DataGrid in ASP.NET

  

If you bind a multi-table DataSet to a DataGrid, only the first table is recognized. Here Dino Esposito writes a custom solution the the multi-table problem.

Dino Esposito

MSDN Magazine August 2003


Creating a table-valued function on a user defined type in SQL Server CLR

  
We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

SSRS integrated mode email subscription to AD dist group

  
I have MOSS 2007 running SQL 2008 SSRS integrated mode and have created some reports and report subscriptions. Everything is working great. Now I want to use an Active Directory Distribution Group as a recipient for a report subscription - does not work, however the "last results" show no error. Try again using Dist Group in TO: field but adding regular email in CC: field...only email in CC field receive report, again all successful. Does SSRS integrated mode support AD distribution groups? if so is there any special setup required? Thanks LmmMmmm...yummy...

email subscription

  
Hello All Experts, I have created email subscription for one report, which is working properly. I have scheduled it on everyday,its automatically send emails to the concern email id on everyday basis. Now is there any way suppose when reports get executed and reports comes blank then its shouldnt send mail on that day. Is it possible or not? Because currently the report is coming blank then also its sending blank report to the concern email id. Please guide me. Regards, AKM  

email subscription problem

  
Hello All Experts, I have created email subscription for one report, which is working properly. I have scheduled it on everyday,its automatically send emails to the concern email id on everyday basis. Now is there any way suppose when reports get executed and reports comes blank then its shouldnt send mail on that day. Is it possible or not? Because currently the report is coming blank then also its sending blank report to the concern email id. Please guide me. Regards,AKM  

Multivalued parameter & Data driven subscription for SSRS 2005

  
I need to create data driven subscription for our reports. Most our reports have multi valued parameters (7-12 parameters with at least 4-5 with multiple values)and need to be send to a users between the range of 25-200. So standard subscription is really not a choice.I have gone through most of the threads before. Question 1:Does any one have a solution for the SSRS 2005 regarding passing mutiple values for a parameter for the Data subscription table. If yes, could you please share the information. Question 2:I found some solutions using the SOAP API. Can any one explain that in detail- how we can do it ?Reference:http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/8ab615b4-1373-4258-bf49-c2843cfea8e9/http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516735&SiteID=1 Question 3:Here is the other solution I found in one of the postsApparently passing in values like {A, B, C} only works when you configure the parameter values not to come from a dataset so that SSRS doesnt check for ValidValues.Reference: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/b2c50aea-2032-4025-a155-306c00fcb856/Any commends about this solution?Thanks

Having problem accessing multi-choice parameter in SQL Query in Report.

  
Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

NULL label doesn't appear on drop down or multi-select parameter.

  
Hi, I am using SSRS 2005 SP3. I have the following query that I use for my parameter: SELECT 0 AS c1, 'NOT Specified' AS c2 UNION SELECT 1 AS c1, NULL AS c2 UNION SELECT 2 AS c1, 'Test 1' AS c2 UNION SELECT 3 AS c1, 'Test 2' AS c2 Let's call my parameter prm1. The type of prm1 is string and it has "Allow null value" and "Allow blank value" options ticked. C1 is the value field and C2 is the label field for prm1. When I preview the report, and I didn't see NULL in my drop down list,  but that record has been shown as 1 (which is the value). I am wondering if this is a normal behavior of SSRS? It's not a big deal. I can replace NULL with something else, but I am just curious. Cheers, Uzzie

ReportViewer 2008 multi-select parameter alignment

  
We are using SSRS 2008 SP1 & ReportViewer 2008 SP1. The multi-select parameter options are left aligned in the drop-down listbox on the Report Server but when run thru ASP.Net & reportViewer, they are aligned in the center of the drop-down list. This is very problematic because the width of the drop-down is also narrow compared to the text size of the fields. I tried wrapping the ReportViewer in a Div tag (<div style="vertical-align: left; text-align: left">), but still no joy.  Would appreciate any help thanks.

Issue with multi-valued crawled property

  
Hi, The problem is, we have a multi-valued crawled property (Managed Metadata type) which we are mapping to a managed property. The managed property has been created, setting MergeCrawledProperties to false.   On the page http://technet.microsoft.com/en-us/library/ff393811.aspx , it states that if MergeCrawledProperties is not set, only the first element is stored in the managed property. We are seeing this behaviour in our environment.   That is causing an issue with refinement results based on this managed property, because it is not including the second element as a result. For example, if we crawl the following content:   Doc1.doc           Property1;Property2 Doc2.doc           Property1;Property2 Doc3.doc           Property2   Refinement results look like this: Property 1           RefinementCount = 2 Property 2           RefinementCount = 1   Ideally we would like to see Property 2’s RefinementCount = 3 to accurately reflect our source data.   We also tried setting MergeCrawledProperties to true , however this merges results together.   For example, with our scenario above, refi

Custom Email Alert template creating links to DispForm.aspx

  
I have a requirement to customise the alert template for a standard blog post list. To do this I: 1. Created a copy of alertstemplate.xml 2. Copied the genericList alert template node and customised 3. Registered the new template file against the site collection, iisreset and finally assigned the new Alert template to the list, when alerts are created the custom template is emailed. The problem now arises that the 'View <Post Title>' in the toolbar section now creates a hyperlink to http..../Lists/Posts/Dispform.aspx?ID=xx rather than post.aspx. I have checked using sharepoint designer that the list supporting files has the correct display item form of Lists/Posts/Post.aspx. Additionally if I reset the alert template for the list back to use the Generic list template the same problem still occurs so this leads me to believe it is not the customisations that are the problem. Code used to assign the alert template to the Posts list: var ats = new SPAlertTemplateCollection((SPWebService) site.WebApplication.Parent); var newTemplate = ats[alertName]; if (newTemplate == null) { // We did not find the specified Template. throw new Exception("Failed to find template SPAlertTemplateType.NewsPost in the template collection."); } list.AlertTemplate = newTemplate; list.Update(); Any assistance will be most welcome, thanks in advance. Alan

Passing multi values to another multi value parameter

  
Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

In a SQL2005 Reporting Subscription I need to revert the parameter back to the default value "overri

  
Hi, In a SQL2005 Reporting Subscription I need to revert the parameter back to the default value "override-default." This value was updated to a specific value and now I am unable to have the report updated daily with the new data.  Within Report Manager, drill into the specific report, click on the properites tab, and chose the second link on the left "Parameters."  For the value in question (DataID) someone unchecked the "Has Defalult" button.  This results in the "Use Default" check box being removed from the UI in the Subscription tab.  I can get the Use Defalut box back by entering a specific value in the 'defalut value' but the report runs against that one value (instead of updating daily.) Before the "Has Defalult" was unchecked, the Defalut Value is a buttton, which says "Override Defalut."  It is this 'button' that I am trying to get back. I've googled this and looked thru MS kb without luck.  (I think it maybe a matter of using the right language...) Thank you for your reply.   Victoria  

How to pass multi-value parameters to DDS (data driven subscription)?

  
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!!

Subscription email using Load Balancer IP address instead of host name

  

Hi,

     How to configure reporting services instance to use load balancer IP address as part of the URL generation before sending subscription emails?

Example: We have 4 resporting service instances routed through a load balancer. When an email subscription is triggered, the reporting server generating reporting URL is using host name as part of the report server URL. Instead, we would like to use load balancer IP so that when a user click the URL received in an email, will be routed through load balancer instead of hitting directly the reporting server URL.

 Any pointers on this?

 

 


Architect
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend