How To Execute Multiple Reports With The Same Parameter
Mar 29, 2007
We have timed-based subscriptions set up for our standard set of 10 client
reports which run monthly with the appropriate parameters set for each
of our clients. Our management has requested that we provide them with
the ability to run the set of 10 reports together at will. So we would
provide the client parameter and all 10 reports would be generated.
What is the recommended approach to accomplish this? We are using Sql
Server 2005, Visual Studio 2003 and .Net 2.0.
View 1 Replies
ADVERTISEMENT
Sep 15, 2015
I'm trying to create a report that will be data-driven and produce multiple reports based on a parameter. For example, 5 reports go out today based on invoice numbers from yesterday. Each invoice can have multiple trasnsaction lines and each line contains the invoice number. What I have so far is only taking the first invoice number (let's say it has 10 transactions) and sending me the same report 10 times and stopping.
I get nothing for the remaining 4 invoices/reports. Here is what I have. To me this should enter the distinct invoice no's from yesterday into #temp, while a invoice no exiists begin query with one invoice selected from #temp then delete that invoice no and select another one and repeat till no more invoice no's. But it's only going through the one invoice no.
select distinct InvoiceNo
into #temp
from table
where Invoicedate between getdate()-1 and getdate()
declare @InvoiceNo VARCHAR(25)
[Code] ....
View 0 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Aug 28, 2007
Hi,
I run reports using rs.exe from inside a stored procedure like this :
set @cmd = 'rs -i "C:
ep.rss" -s http://localhost/reportserver -v custparam=70 -v orderparam=20 -l 0 -e mgmt2000 -t -b'
EXEC xp_cmdshell @cmd
The rep.rss file looks like this :
Dim format as string = "PDF"
Dim filePath as String = "C:Reports"
Dim fileN as String = "MyReport"
Dim fileName as String = filePath + fileN + ".pdf"
Dim reportPath as String = "/MyReports/Report1"
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "application/pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' Report Parameters
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "cust_no"
parameters(0).Value = custparam
parameters(1) = New ParameterValue()
parameters(1).Name = "order_no"
parameters(1).Value = orderparam
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
The problem is that the command hangs the stored proc for a long time. At times when I use the same command on a command line, it executes and generates a pdf at times. Sometimes it just keeps on executing endlessly.
How do I troubleshoot this? I am using Sql 2005 with sp2 on Vista.
Has it got to do something with xp_cmdshell? I enabled it using sql server surface area configuration.
Vidkshi
View 1 Replies
View Related
Apr 14, 2014
can i connect to SAP from SSRS and execute reports?
View 1 Replies
View Related
Oct 4, 2007
Hi everyone, I am working with SSRS 2000, and I have 2 reports that are linked with a hyperlink. Well, one is the report with all the data (numbers, months, etc) and the second one is a Chart with all the information. Well, both have a parameter, that prompts the part #. I want to know, how can I get both reports to run with one parameter only. For example, the first report with the data, fill in the parameter (part#), and when click on the hyperlink to have the Chart (second report) automatically fill in the part # that was typed in the first report so it can display the information. I know excell does it, but how can I do this with SSRS?
Thanks a bunch,
Abner
View 1 Replies
View Related
Sep 4, 2007
I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'
So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?
Thanks, Iris
View 4 Replies
View Related
Sep 27, 2007
Hi,
I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.
Thanks a bunch.
View 1 Replies
View Related
Nov 13, 2007
Hi all,
Is it possible to generate mulitple reports from parameter? what I want is for example: I have a report with a sales orders table, it has 2 input parameter fields; two dates to make up a date range. If the date range is between yesterday and today, I will get two reports (two reports with two different total page number); one is for yesterday and one is for today.
I did some research on resetting the total page, but their solution doesnt work on my special case (very complicate..). A similiar work out also apperciate.
Regards,
Bryan
View 3 Replies
View Related
Aug 13, 2007
I've seen some theads on this but cant seem to get this right. Report A has 5 parameters 2 of them are multi value-- when I click on part of Report A I want to jump to report B and pass all parameter values from report A. The single value ones work but multi value only passes on value and not all values selected-- what am I missing here? Do I need to pass the values in the multi value parameters in an array??
thanks
k
View 1 Replies
View Related
Jul 20, 2005
I would like to pass an unique identifier (UserID) to a Crystal Reportfrom a SQL stored procedure. I found an article from Business Objectsabout this issue, but I couldn't get my head around it's hard codedexample.Does anyone have an example of this? Would I need to also create aparameter in my Crystal Report?Help appreciated.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Sep 11, 2007
Under My Subscriptions I have a report that runs daily. This report allows me to select certain items from a dropdown in parameter fields or select all. Some days the report fails and I get the follow error: The subscription contains parameter values that are not valid. When I click Edit to review, the parameter field is blank and my selections have been unselected. This report has several parameter fields but only only one or two of the parameter fields are blank, the others are ok.
View 4 Replies
View Related
Mar 3, 2004
Hello,
How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp
set @sql= 'declare @res int select @res=count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
???????? set @result=exec (@sql) ????????????? Something like this
Thanks
Paulo
View 2 Replies
View Related
Nov 7, 2006
I am trying to create an Execute SQL task that sets a variable.
This is my SQL
DECLARE @Period AS DATETIME
SET @Period =Parameter0 + '/01/' + Parameter1
SET @Period = DATEADD(m, -1, @Period)
SELECT DATEADD(s, -1, @Period)
This statement parses okay.
I mapped two variables called "User::PeriodMonth" and "User::PeriodYear" in the Parameter Mapping tab to the parameters.
In the Result Set tabl I have mapped a variable "User::PeriodStartDate" to Result Name "PeriodStartDate".
The error I get is the following:
[Execute SQL Task] Error: Executing the query "DECLARE @Period AS DATETIME SET @Period =Parameter0 + '/01/' + Parameter1 SET @Period = DATEADD(m, -1, @Period) SELECT DATEADD(s, -1, @Period) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Online Books are not helpful. They just say you have to bind the parameters to the application variables.
What am I doing wrong?
I want to set another variable called "PeriodEndDate" also. Can both variables be set in the same task?
View 12 Replies
View Related
Dec 20, 2007
Hi Friends,
How do I display a bar graph in my main report? I dont want it to be displayed on separate page. When I click on some entities on my main report, a graph should be popped up in the same report in some corner? how do I do that ? Please let me know.
Thanks & Regards
Naveen J V
View 3 Replies
View Related
Apr 23, 2007
Hi all,
I have created a report that everyone is happy with.
My dataset (simplified) looks like this:
SELECT *
FROM FamilyMember
WHERE QuestionnaireID = @QuestionnaireID
Now, instead of running individual reports the users want to run several reports on a list of QuestionnaireID's.
I thought something like this would work:
SELECT *
FROM FamilyMember
WHERE QuestionnaireID IN (@QuestionnaireID)
but RS complains about converting '152,153' to an INT.
Is there a way to accomplish this?
I am guessing I need to convert the nvarchar @QuestionnaireID to a real list of INTs but am unsure how to do this...
View 9 Replies
View Related
Aug 16, 2007
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- =============================================ALTER PROCEDURE [dbo].[Product_FindByParameters](@Name Varchar(255),@ManufactureID bigint,@ShortDescription Varchar(255),@ManufactureProductID Varchar(255),@ItemsInStock bigint,@StorePartNumber Varchar(255)) ASBEGIN SELECT P.ProductId,P.StorePartNumber,P.ShortDescription,P.ManufactureProductID,P.Name,P.Price,P.ItemsInStock,M.ManufactureName FROM Product P left join Manufacture MON P.ManufactureID=M.ManufactureIDWHERE ( P.Name like '%' + @Name + '%' OR @Name is null)AND (P.ShortDescription LIKE '%' + @ShortDescription + '%' OR @ShortDescription is null)AND( P.ManufactureProductID LIKE '%' + @ManufactureProductID + '%' OR @ManufactureProductID is null)AND (P.ItemsInStock=@ItemsInStock)AND (P.ManufactureID = @ManufactureID OR @ManufactureID is null)END--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,'' First 2 exec statement gives many data row as result,But why the last donot give any row ;( ;(how can i rewrite the stored procedure, such that it gives out put even if i don't supply ManufactureID as inputkindly help me
View 2 Replies
View Related
Jun 26, 2001
I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.
What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')
Where I would later use @val something like:
Select Value * @val as Total Value
Is there an easy way to do this..i've read and read to no avail.
TIA,
Charles
View 5 Replies
View Related
Sep 17, 2006
Hello.
It is the first time that Im using IMAGE field type..
I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@OutImg IMAGE declared as an output parameter of the procedure.)
(Select @OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.
Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:o
View 1 Replies
View Related
Apr 17, 2008
I have 2 questions on this
(1) I know how to use the ? ? ? and 0, 1, 2 notation in Parameter Mapping within Execute SQL Task. However, the interface allows me to give descriptive names to my parameters (other than the ordinals 0, 1, 2, ...). To be more clear, if you go into Parameter Mapping and click in Parameter Name column, you are not just restricted to typing in 0, 1, 2, ... You can type anything you want for the name. Does this suggest that I can use other things besides a "?" in my SQL command?
(2) What is Parameter Size? Is this like a data type? If so, why am I allowed to type in anything I want in there?
View 3 Replies
View Related
Dec 13, 2007
Hi All,
I am using a stored procedure defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetPriority] @PriorityID TINYINT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [Priority]
FROM [MTD Dashboard].[dbo].[Priority] WHERE [Priority ID]=@PriorityID
END
I want to use this stored procedure in a Execute SQL Task. What should be the SQL Statement, Parameter mappings and Result Set?
Can someone please help me in doing this.
Thanks
View 5 Replies
View Related
Mar 9, 2006
I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:
if exists
(
select name
from sys.databases
where name = ?
)
begin
drop database ?;
end;
go
create database ?;
go
This is the error I am getting:
[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.
Regards,
DO
View 13 Replies
View Related
Mar 20, 2008
Hi All,
I have a Doubt in Parameter Passing in Execute SQL Task.
In Execute SQL Task, I have an Insert query in which I want to pass the Database Name Dynamically i.e. passing it as a parameter.
Eg.
The query is --------- Insert into [?].[dbo].[DimCurrency] values( value1, value2, value3)
I want to pass this Database name using a user variable. But I am not able to do so.
What is the catch in this, Can anybody please help me out.
Thanks & Regards,
kapadia Shalin P.
View 5 Replies
View Related
Jun 22, 2006
Is it not possible to have table name as a parameter? For example have the SQL something like:
Delete From ? Where ID = ?
.. I get error:
[Execute SQL Task] Error: Executing the query "Delete From ? Where ID = ?" failed with the following error: "Must declare the table variable "@P1".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 5 Replies
View Related
Jun 18, 2007
I have a stored proc which starts like this:
CREATE PROCEDURE dbo.AddAttachmentListItem
@ListID uniqueidentifier,
@AttachmentPath varchar (260),
@DeleteAttachmentAfterSend bit = 0
I have a Script task which generates a GUID and stores it in the variable @[User::AttachmentListId], which is of type System.Object since Guid wasn't an option. Following this is an Execute SQL task (with an OLE DB connection to an SQL Server 2000 database) whose SQL statement is
EXEC AddAttachmentListItem ?, ?, 0
My parameter mapping looks like this (variable name, direction, data type, parameter name, parameter size):
@[User::AttachmentListId], Input, GUID, 0, -1
@[User::AFilePath], Input, VARCHAR, 1, 260
When I execute my package I get the following error:
[Execute SQL Task] Error: Executing the query "EXEC AddAttachmentListItem ?, ?, 0" failed with the following error: "The type is not supported.DBTYPE_GUID". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This worked earlier when the first parameter was an int and not a uniqueidentifier, but I reworked my design because the GUID was a better choice for what I was doing. Well, at least 'til I got to this point....
Any ideas?
View 6 Replies
View Related
May 11, 2008
How to pass master reports (fields!OrderId) value to a subreport as a parameter value.
if i right click the subreport control on the main report layout, i do see parameters tab, under that would like to pass the (fields!orderID).
Thanks for the info.
View 4 Replies
View Related
Aug 21, 2007
HI,
i am a new to SSRS and i am facing a problem with Ad Hoc reporting.
actually i wanted to create a report with data from two different table. now its the relation between these two tables that is not allowing me to create a req. adhoc report.
i want the report to have data from table A and table B and the relationship between A and B is many-to-many. so in the database design i have an associate table C between A and B. so in .dsv file i have C related to A as one-to-many and C related to B as one to many.
Now here how do i get data from both table A and table B?
View 1 Replies
View Related
Sep 25, 2007
I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.
I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.
Does anyone have any ideas? Thanks in advance!
View 2 Replies
View Related
Oct 30, 2007
Hi,
Can we share and schedule whole report folder(not just one report) by using management studio?
Thanks
View 1 Replies
View Related
Jan 4, 2008
I have a data set which has more than one record. Iam tyring to create a report without using the wizard. I want the report on multiple pages. I dragged some text boxes on to the design and added the expressions in there. If I run the program now Iam only getting the report with the last record. How do I get the report for all the records in the Dataset on multiple pages ? . I want each record on one page. I am forsure getting more than one record into the Dataset.
Thanks in advace for the help
View 2 Replies
View Related
Oct 4, 2007
Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()
Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapter.Fill(dt)
Dim row As DataRow
Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]
@InputDate Datetime
AS
SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions
Where
dtmDate > @InputDate
Order By dtmDate
View 3 Replies
View Related
Mar 3, 2007
hi
I would like to create a SSIS package that is going to be called by store procedures.
What i have done so far.
1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?
2) I save this package as a DTSX file and will called it from a proc.
My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks
View 1 Replies
View Related
Apr 17, 2008
Hi guys. I have a procedure with just one parameter. That's a output parameter. It's type is NVARCHAR
I think there's no problem with the procedure. Now I want to execute it. Can I declare that variable passed by parameter to the procedure ? Must I use the Execute command ?
thanks a lot.
View 3 Replies
View Related
