I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:
This version of the Web Services Description Language (WSDL) is not supported.
Is there as simple way to do what I want?
Hello,
We have a datawarehouse that's built nightly through a number of Integration Services packages. I have several packages that compile and aggreage data used by a set of reports constructed and presented using Reporting Services. I would like to augment the packages to incorporate a step that will create a snapshot for each report. Can anyone provide a set of pointers or sample code to accomplish this?
The data is static and this method would allow the reports to be constructed before the first user runs them (caching would require them to be run once). I would also like to delete the snapshot if a report extract needed to be run again. This can occur in some occassions. I'd like to automate this as much as possible.
I imagine I'de be able to do this using a script task in SSIS that interactes with the reporting services web service in some way.
Kind regards,
Orlanzo
Dear All-
i am facing a problem with them creation of the execution Snapshot for my report
simply when i try to create a one and after choose the schedule setting and i press OK to finish
an error appear mention "The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting) (Report Services SOAP Proxy Source)"
i like to mention also i choose the datasource credentials is the redio button Windos integrated security and also the reporting services and the database are exit on the same machine
i hope someone help soon as our work is affected due to this issue
Thanks and have anice day
Maylo
Hi,
To programmatically invoke a subscription we can use -
exec ReportServer..AddEvent 'TimedSubscription', '575F96C6-A1BD-49FD-9C2F-934FC9658780'
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
TIA,
Sam.
I have a report which have multiple parameters. By default all the parameters are selected. Now I am scheduling a Cache with Report Execution Snapshot. Now if I try to view the report, it disables the prompt selection. It means user cant change the selection. Is it the expeceted behaviour of the exectuion snapshot?
Thanks,
S Suresh
We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
I have already created Tran. Replication for one database and it is working fine. But i have added 3 tables to the database and i want to do replication for that tables with same publications . I mean i can go to Publishers Properties and add articles but the problem is:
1)When i run the snapshot agent,it creates the snapshot whole database which is bigger in size .
2)Is there a way i can run snapshot agent for remaining articles in same publication.
3)How to do it step by step
Any help will be appreciated
Thanks
Experts
Hi all,
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:2007-09-12 12:05:46.58 --------------------------------------2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest2007-09-12 12:05:46.60 -ReplicationType 22007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard2007-09-12 12:05:46.60 -DistributorSecurityMode 12007-09-12 12:05:46.60 -PublisherSecurityMode 12007-09-12 12:05:46.60 --------------------------------------2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'2007-09-12 12:05:46.96 The replication agent had encountered an exception.2007-09-12 12:05:46.96 Source: Replication2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command. Contact your system administrator.2007-09-12 12:05:46.96 Message Code: 142602007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
I have a report with a parameter that I would like to have created every night but also allow a user to change the default parameter and rerun it if they need to . When the Snapshot is opened, the parameter box is disabled. How can I work around this?
TIA
Dean
I have a report that has a snapshot enabled. I made some changes to my report and redeployed it, but when I view the report from the Report Manager web site the old version is getting used. Apparently the snapshot includes the rdl and the data and not just the data. Can anyone else confirm this? If this is the case, a new snapshot must be created after the report format is modified.
Dean
Can this be done?
I want to build a report using data from the old snapshop reports, how do i use them as a datasource?
Is there a way to schedule report snapshots and still give users the option to change the parameters? I found that report parameters are disabled when I set the execution options to use a snapshot. I fixed the problem by setting the report to use cached data that expires on a custom schedule and by scheduling an e-mail subscription that creates a new report cache soon after the cache expires. I'm guessing there must be a better way to provide good report performance with the default parameters as well as flexibility.
View 1 Replies View RelatedHello,
This is my first post, and I'm hoping you all can help.
Using Reporting Services 2005, I have several reports that use embedded images.
All images render fine when the report execution is set to:
1) Always run this report with the most recent data
1a) Do not cache temporary copies of this report
However, when I change the execution to either a Cache or a Snapshot, the images and some charts render as red "X" placeholders. This is sometimes remedied when the user clicks the page refresh button, but not always.
Of course, I could just have all the concurrent users use the uncached report that hits the OLAP server, but that would be highly inefficient, and just plain slow.
Thanks for any help on this subject.
-michael
Hi,
I am facing some issues while creating report snapshots programmatically using axis (Java platform for creating and deploying web services applications).
To create a report snapshot what should be the methods called ? I tried using UpdateReportExecutionSnapshot() but didn't find any parameters to set the schedule information which would say when the execute the report snapshot. Can you please guide me for this ?
Also I had one more doubt regarding report snapshots. If a parameterized report is configured as snapshot then we would require to set default parameters for the report.
Is there a way to create snapshots of the same report with different parameters ?
eg : The employee information report displays the information based on the employee id taken as a parameter. So is it possible to create snapshots with different employee id's ?
Thanks,
Amit Shah.
Hi Everyone i wonder if there anyone know
what is "Render report with most recent data" and execution SnapShot and the different between them
and also it will be great of someone provide me with blog or a link that describe these 2 properties
Thanx
Maylo
Hi Everyone,
I want to receive some reports by email every time the datawarehouse refreshes its content. The data warehouse is being refreshed on a daily basis but the exact time varies between 9:30 am and 11:30 am.
I have created the 4 reports in reporting services and set the execution property of each report to
Render this report from a report execution snapshot
and then created 4 subscriptions for email delivery based on processing option
When the report content is refreshed
In an Integration Services package I have 4 Script steps (VB.net) that perform an UpdateReportExececutionSnapshot for each of the reports.
The problem is that these steps fail in a random order every time the package runs. Sometimes they all run fine , but then one or two fail with timeout error. So the emails are not being sent.
I am currently using SQL Server 2005 SP2.
If anyone has an idea on this issue, please let me know.
Best Regards
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
We are setting up a new reporting environment for our client and as part of the deliverable I need to achieve the following two things.
1. Most of our reports are snapshot reports and all the data needed gets populated into transaction DB tables from a batch program. Some of batches run monthly and others on request, we use a scheduling tool to run these batches. For monthly running batches since I know when they run, I have scheduled snapshot report for the respective dates. But the batch that run on request have no fixed schedule so my question : Is there a way I can invoke or kick off a particular SSRS snapshot Report as soon as the batch completes running. For example if a on-request batch program finishes running today at 6PM , I would like to create a snapshot report as soon as the batch finishes running. Is this achievable ?!
2. As I mentioned above most of the reports are snapshot reports and client wants Report history of 10yrs. We have about 170 reports on the whole which run monthly/daily/yearly. Is there a best practice known in maintaining all the reports and where to store them?
We are using MS SQL Server Reporting Services 2014.
I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.
However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
Before I start coding ...
Is there any existing software/trick/hack to create a Report Project from the DataSources and Folders/Reports on a Report Server, i.e. the inverse of Deployment?
Would there be anybody interested in such a thing, and, if not, why not?
Helo All,
I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:
An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)
I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.
My connection string looks something like this:
="/FILE C:NTSDWKPIKPI_SSISNTSKPICCA_DATA_RECON_DELIVERY.dtsx /SET Package.Variables[User::V_SSRS_PARAM].Properties[Value];" & """" & Parameters!V_SETT_DATE.Value & """"
Could any please help me out of this?
Note: SSIS is parameterized here!
Regards,
Raju
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
I need to create the following report:
Location (City)
YTD Sales Qty for item category code : X
% for Sales Qty YTD compared to Sales Qty YTD previous year
YTD Sales Amount for ALL item categories
% for Sales Amount YTD compared to Sales Amount YTD previous year
I have the following matrix showing the source (OLAP)
X
Y
Z
YTD
PYTD
PYTD
Current Time
YTD
PYTD
Quantity
Amount
Quantity
Amount
Quantity
Amount
Quantity
Amount
Quantity
Amount
Quantity
Amount
New York
12
1000
417
123958
1
100
200
2000
200
2000
2570
242690
Total
12
1000
417
123958
1
100
200
2000
200
2000
2570
242690
The X, Y, Z is the item category dimension. YTD, Current time etc. is a calculated time dimension. (YTD=year to date, PTYD = Previous year to date)
What I need to do is:
1. For the YTD quantity of X, reference the PYTD of X and calculate the % of these two.
2. Sum amount for the YTD amount of ALL (sum X, Y, Z).
3. Equals 1. but then for the YTD amount of all.
Can I do this within one matrix or table? I tried using filters on item category in columngroups but this can not produce a column showing X and a second showing ALL.
I also tried using tables with separate filters but then the row count was different and rows were not matching. Furthermore I didn't manage to reference sums from other tables in a calculation.
Any help would be appreciated!
Tom.
Dear All,
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2005 as a back End
i want to know how to create report using server 2005 reporting services and use in web application.
please give me some link or example .
Thanks
Hello,
I am trying to create a dts to schedule some reports and email it. The point is that i try to do that through Data Flow > Export to Excel....!!! Is there an other way to create reports?? Cause i don't see any crystal reports or somenthing else!!!
Thanks
Stefanos
I'm pretty new at this, how the heck do you edit and create parameters? Not really looking to edit code yet, just want to know how to use the interface to do this.
View 4 Replies View RelatedHello,
We are trying to convert our Crystal XI reports to SQL Reporting Services 2005. Our crystal reports get their data from ADO datasets which are populated through code at run time. Is it possible to do this in SQL Reporting Services?
The only options for a dataset seem to be query and stored procedure. When i have a blank dataset it throws an error. When i try to link my dataset to code, it throws an error.
Thanks for your help!
Hi Friends,
No I m not looking for a drill down report.
I want a hiperlink on my country sales reports. When user will click on this hiperlink one popup window will open(Popup report). This popup report will contain some input parameter eg list box of sales person name and list of country to be selected select.
One user will select country germany and sales person as Mr Bin and click go on the poup report I want the popup report to be closed and based on the input(country germany and sales person as Mr Bin ) want my country sales reports to get updated.
Hope you undestand my qn.
thanks
Novin
HI, Is there a way to generate a high graphic report to a web page . if so how ?
Thanks
Ali
I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.
we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.
ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio
[code]....
Query in DESIGN View
SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication
[code]....
Results from QUERY in DESIGN VIEW
TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972
[code]....
RESULTS FROM QUERY in SELECT TOP 11
TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000
[code]....
QUERY in DESIGN VIEW
SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN
[Code] .....
QUERY RESULTS IN SELECT
csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463
[Code] ....
I need to create a report showing the title the first and last name of all sales representatives-This is what I have so far but am having difficulty retrieving ONLY the "Sales Representatives" Titles.
Select Firstname, Lastname, Title
From Employees
Where ???
Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?
Please help me, thanks a lot
This Query below is used to calculate the total amount for each customer in Revenue dataset:
SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @Start) AND (Updated <= @End)
Group By CustomerID
the other one here is used to get customer code and name in Customer dataset:
SELECT ID, Code, Name
FROM Customer