November 2, 2018 at 3:28 pm
Hello,
I am new to SSRS and trying to create a sql health report and have few Tables, SP's and views written which pull the health data from client databases.
I am able to open SSDT- create new report server project - add datasource ( connection is good) and create dataset
My question is when you create dataset shall I select tables or SP ( for SP I have 10 plus SP's so how can I select multiple SP's)
If not then shall I select tables and create multiple datasets. ( the idea is to create a single report from data fetched in multiple tables).
What should be the best approach?
Please guide and help me out. Thanks.
November 2, 2018 at 4:23 pm
" the idea is to create a single report from data fetched in multiple tables"
I'd write the SP in SQL Server first, and make sure it returns the data you want. Then just base the report on that.
Rereading your post, are you trying to get all the data from the 10 stored procedures into the same report?
November 2, 2018 at 8:55 pm
Thanks, yes, I am trying to get the data from all the 10 SP's but the problem is when I try to create the dataset it only allows 1 sp ( cannot select multiple) in SSDT report project. Since I am new to SSRS I tried using SSRS wizard and it shows all the 10 SP's BUT cannot run the query due to error - relationship between few SP's is broken. I used the autodetect but that does not work either. My idea is to create a simple health report of sql server heath data coming from different databases. Secondly, once the report is ready then schedule the report to auto email to clients at specific email addresses. Hope I was able to provide the information you are looking for. Are they any prebuilt templates which I can use for my requirement? also, does my plan workable? Do I have to comeup with only single SP which works with all the SP's and gets the data?
I apologize for to many questions but trying to get the Job done...thanks.
November 2, 2018 at 9:14 pm
No need to apologize for asking questions here! That's what it's here for. As long as you read the Best Practices article and follow it, you'll stand a really good chance of getting a working answer.
Are you doing something like running the same SP against a bunch of different servers and collecting all the data into one place? If you're doing that, you probably want to insert the results of all those SP calls into a single temporary table and return that in your stored procedure, then report on that. It's just not completely clear where all the data is coming from. If the structures are all the same, I'd just dump it all into a temporary table (or permanent if you want to keep it), and then return that in your stored procedure.
November 2, 2018 at 9:36 pm
Please find the code below for 1 of one the SP's for you to understand and 'attached' sample data from all the SP's( please see the different workbooks/tabs in .xls) how the data is being imported into DB."The data is coming from a monitoring app which is installed on different servers into tables and looks like SP is pulling the information from tables ( I might be wrong but thats what I think)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [Report].[sp_GetCurrntMemStatistics]
as
begin
select
a.ServerIdentifier, replace(a.PhysicalMemoryFreePercent,'%','') FreeMemory, replace(a.PageFileFreePercent,'%','') FreePageFile, cast(a.MemoryStatsDate as date) MemoryStatsDate
from
dbo.MemoryStats a
end
Please find the answers to your questions below and appreciate your help.
q)"Are you doing something like running the same SP against a bunch of different servers and collecting all the data into one place?"
a) yes, so each SP gathers data for say 6-7 client databases.
I think I get the answer and need to use only single stored procedure and point towards the "temp table" which has consolidated date.
"Please - Do you have or can share any sample T-sql code to create Temp table in these kind of situations?
Also, please share if there are any good tips/links for creating a temp table. Also, any resource you feel might help me in building the case which I have on hand. thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply