Solved

SQL Reporting Services 2005 Updating a table using data from the report!

Posted on 2006-10-24
2
354 Views
Last Modified: 2008-01-09
Hi there

I don't think this can be done in RS, but I thought I'd see if anyone can come up with a good idea.

Basically I have alot of data that is created in multiple stored procedures and runs in a data driven subscription passing in different ids through each run.

The sprocs use temp databases to get the data in the form needed and returns to RS.

However, in an ideal world what I want is to sum up the totals in these various sprocs which RS does and then update a table in SQL at the end of the report with the sums of the totals from the sprocs that RS has summed up.

The srpocs all take a while to process all of them about a couple of mins, so I can;'t really run them once in SQL server to get the data and do the updates then again in RS to get the data for the report, due to the number of times the data driven sub needs to run.

RS won't update the rows in the table because it won't allow me to use field names in parameters, ie in my update statement say I have a parameter called @total it won't allow me to assign @total from another query made up of say sum(total1) + sum(total2) + sum(total3) etc, and report parameters work the same way.

I presume this just simply can't be done in RS unless someone can tell me otherwise, so I think I'll prob have to stop using temp tables and get SQL Server to do all the work in tables, then get RS to read from them, then somehow clear them down before the next ones runs in the data driven subscription.

Anyone any ideas?

Thanks

David
0
Comment
Question by:DavidGreenfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 21

Expert Comment

by:Yurich
ID: 17801081
I don't know the ways of doing it in RS directly... One way of doing it would be:

1. Schedule a report

2. Run it and export it as some data-file (excell, csv)

3. Get data from this excell file in DTS adn put it in persistent or temp table(s) in SQL

4. Again in DTS, run some queries for getting you values from the table(s) created above.

Of course you can just do all calculations in your sql server (but you'll have to get rid of temp tables).

good luck,
yurich
0
 
LVL 18

Accepted Solution

by:
chrismc earned 500 total points
ID: 17823126
You can do the updating in the sp's themselves.

This is simplistic but your sp could look something like;

    Select Name, Date, Value From MyTable

    Update MyTotalTable
        Set TotalValue = (Select Sum(Value) From MyTable)
        Where Id = SomeValue

The report will still show the data you want but it will also update the totals.

Cheers
Chris
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tableau restapi 7 250
Calculating Day of the week - Cognos 10.2 2 118
Can MSSQL 2008R2 database integrate with MSSQL 2012 6 77
SSRS Windoes Fileshare credentials Permissions 4 70
How to increase the row limit in Jasper Server.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question