Hi,
Using Reporting Service 2005, I want to give User an Interface where user can directly paste sql query like -
select * from employee.
and corrosponding data will be displayed to user.
Is there are any way?
Thanks,
Ashwin
Hi Gurus,
We are planning to user SQL server 2005 reporting services in our project, for this we are doing a Proof of concept exercise to evaluate if SSRS 2005 will be good for our project.
For this we are trying if we can make dashboard or composite reports (combine 4-5 reports to create one report).
Also if we can make this dashboard ad-hoc. As we are already using the Ad-Hoc reporting capability of SSRS 2005 we wanted to check if dashboards can also be created using Ad-hoc reporting feature.
Please let me know if you any information or pointers for this.
Thanks in Advance.
Regards,
-Gaurav
Hi,
I am looking for a strategy document for Adhoc reporting. Does MS has published anything in this regard?
Thanks,
S Suresh
Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).
Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.
I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.
Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?
I am looking for a solution for my customers who use the 2005 report models to create detail list reports. Â All I am able to find on 2012 are pivot type reports, SSAS tabular and cubes. Â It seems that MS assumes that all clients need statistical or summary reports. Â My users need to be able to create adhoc lists of data from multiple view and tables using drag and drop. Any third party distributable tool which could be used for adhoc reporting?
View 2 Replies View RelatedHi !!We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets say http://salebyowner.com/advancedSearch.phpWe have few more options than this on which customer can do search. I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?
View 6 Replies View Related
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurÃcio
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
i have a list report
some values of this are blank or null
i want to avoid that spacing if it is null
like
name:jjj
lastname kkk
firstname
address uuuu
i want it should be
name jjj
lastname kkk
address uuuu
Hi ,
We are trying to implement a 2 tier architecture for our inbuild SQL 2005 backed online application. We want the SQL Reporting Server Interface --- Reporting service website to sit on Server 'A' and the actual reporting server to sit on Server 'B'.
Can someone suggest or advise us how to proceed with this.
Server 'A' is our application server and has IIS & Application.
Server 'B' is our DB server and has IIS,SQL 2005, Reporting Server,Intergration Service.
All suggestions are welcome.
Thanks,
Namit Sethi
Hello,
This is chirag patel.
we are going to SSRS with our asp.net application to generate reports and charts.
i having query about license of SSRC.
Is it completely free with SQL Server ?
i mean if my web application will have SQL server 2005 as Database on production server , then do we need to purchase anything else to use SSRC ??
can anyone please let me know brief detail about license of SSRC if we want to use SSRC with web based application ??
Thanks.
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
Has anyone any comments or the same needs ?
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
Hi all,
I have some problem about reporting service add-in.
After I install reporting service add-in for SharePoint, reporting service menu does not appear in Application Management Tab in SharePoint Central Administration.
I try to uninstall and re-install again, it remain not work.
How can I solve this problem?
Thank you very much.
Hi,
Can we pass the Database name/Catalog name for the datasouce that will be used for report,
as a report parameter . Is yes can any one help me on how to achieve that .
Thanks in Advance
Hi,
I am using SQL Server 2005 Reporting Services. I want to make it secure. I am also using WCF services and made them secure using Claim based System.Identity Model.
I want to apply same claim based model to Reporting Services.
How can I do that?
Amit
Hi Guru,When I ran my adhoc script below it generated only 45000 reads or 4seconds but when I wrapped it into procedure it took about two minutesor millions of reads. The parameters calling both adhoc and proc areindeed the same. I'm pretty 99.9% sure that the proc does not recompilebecause I don't mix up between DDL and DML, no temp tables or any thingto cause proc to recompile. The big difference is adhoc used index scanfor 45% but proc used bookmark lookup for 75%. Why it's so differencesince they both returned the same results?Please help...Silaphet,Below is my code,DECLARE @Modevarchar(10),@UserIDvarchar(36),@FromDatesmalldatetime,@ToDatesmalldatetime,@Insttinyint,@LocationIDsmallint,@BunitIDtinyint,@TeamIDintSET @Mode='TEAM'SET @UserID=''SET @FromDate='Dec 1 2006 12:00AM'SET @ToDate='Dec 31 2006 12:00AM'SET @Inst=28SET @LocationID=0SET @BunitID=2SET @TeamID=805--IF @Mode = 'TEAM'BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_Grouped RIGHT OUTER JOINdbo.MyTeamsRpt ONvw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerIdLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (MyTeamID = @TeamID) AND ((StartDateBETWEEN @FromDate-365 AND @ToDate-365) OR (StartDate BETWEEN @FromDateAND @ToDate))GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveENDIF @Mode = 'RM'BEGINIF @BUnitId 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, OffBUnitDesc As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate)) AND OffBUnitID = @BUnitIDGROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveEND--ELSEIF @BUnitId = 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate))GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveENDENDEND
View 3 Replies View RelatedHello
We got third party software, we are using adhoc queries more for the Applications (i am checking the DB Dashboard, it is showing as 97% CPU for adhoc queries). Could any one suggest how to tune SQL Server (at server level) for adhoc queries, we can not change the code now.
Let me know how to improve the SQL Server, whenever 3rd party applications use adhoc queries more?
Thanks in advance.
Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
I then attempted to run the ALTER TABLE SWITCH and I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column and the "TEXTIMAGE_ON", then altered the column to a varchar and another table with just the varchar column and no "TEXTIMAGE_ON" spoecified. After copying the data from the original table, I tried to run the Alter Switch. It failed once again for the table with the text column that was altered to varchar, but it worked for the table that had the column specified as varchar from the start.
All other things have been checked and the two source tables in this test are identical execpt for the Text column specification. The alter column changes the definition of the column, but how would you remove the €œlob_data_space_id€? setting, since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
I am trying to write a query that counts how many clients were part of program at the first of each month.To make it simple, the data comes out of one table which looks like this:
Client # Program Start_date End_date
1 Fruit eater 03-27-2014 01-10-2015
2 Veggi eater 01-16-2015 null
3 Veggi eater 12-05-2013 04-16-2015
4 Fruit eater 10-01-2014 11-30-2014
Currently I have a very ugly solution that I know is not the best one:
Select
sum(convert(int,Jan_2014))as Tiers_in_Jan_2014
, sum(convert(int,Feb_2014)) as Tiers_in_Feb_2014
, sum(convert(int,Mar_2014 )) as Tiers_in_Mar_2014
, sum(convert(int,Apr_2014 ))as Tiers_in_Apr_2014
[code]....
Is there a better way to write this query?
Hi,
i want to know whether it is possible to develop ADHOC reports using SSRS 2005 and OLAP Cubes. the requirement is the user should be able to select the columns he wants to see in the report.
I think this requires Dynamic RDL generation. It is very urgent. i have to give POC to the client on this. Any help in this regad is appreciated.
Thanks,
Srik
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
View 2 Replies View Related
I require to update sysxlogins table to include one more column into it in SQL 2000 sp4.
I am trying to develop a wrapper UI over sql security so that logins can created and modified right from my UI.
Doing this is very crucial for my current project.
Kindly help. Any help would be much appreciated.
Currently i have tried: sp_configure "allow updates", 1
reconfigure with override
I also tried giving -m switch in sql service parameters and restrating the service
but nothing works....
What is DAC. I am using queryanalyzer how can i use sqlcmd -a. ? cuz i am familiar with query analyzer only .Is that necessary to do as well ??
When i am trying to update the data inside the ' sys.sql_modules' view, i am facing the following error:- 'Ad hoc updates to system catalogs are not allowed.' Is there any alternate way to update the data inside the ' sys.sql_modules' view?
View 2 Replies View RelatedHello sir
I am using SQL Reporting Service in SQL Server 2005
There I am creating a Report from Parameterised Stored Procedure
Its show the report after clicking on Button View
but it not sort a data according to parameter
Please help me out in this
Thanks
Ever Smiling
Ashish
You Have to Loss Many Times to Win Single Time
let's work together
View 1 Replies View RelatedHow to deploy the newly created report on the Reprt Server
View 3 Replies View RelatedHi all,
I am new to reporting services.
I have to call the reporting service(some else did this) from the web application that I have created using VB.NET . Not sure how to do it .. was reading few articles on the web .
Any good atricles out there ?
I have created web reference to http://server/ReportServer/ReportService.asmx in my web app. The report expects one parameter .
Below is the code I was starting to write to call the reporting service
Dim rs As New RSWebReference.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim result As Byte
Dim encoding As String
Dim mimetype As String
Dim parameters(2) As Parametervalue()
For some reason I cannot do ParameterValue() it says undefined. I am using Microsoft .NET Framework 1.1 . do I have to use ASP.net2.0 for using reporting services?
ANy way i can get this working
Thanks in advance.
I have already Installed SQL Server management studio express edition in my computer. Now I want to install SQL reporting service.
Can anyone please guide me to how to do that.
Thanks
Kalloo
Hi all
When I'm installing sql reporting service I get an error that says "ASP.NET is not installed or is not registered with your web server."
I have iis installed, all of .net, and just installed .NET Framework Redist version 1.1
Can anyone tell me what I need to do to register ASP.NET or what I've done wrong.
Thanks for your help
Jerry
CAN ANYONE HELP ME OUT.
I have Generated a report using Tamil in reporting services in sql server 2005... i can able to generate and preview the report in local system,
But when i deployed in report manager server, the generated report
statement font's overlapping with others, even after alignment..
hope am clear...
pls gve the solution....
I want to make a table with dataset such as
employee
and in the header of the table i want to put
another table which has another dataset such as
empdata
Could you please help me
Hello all experts!
I have a problem I would be very happy if someone could help me out with;
I have a windows 2003 server with SQLExpress and Reporting Services.
I have developed a report in visual studio and successfully deployed that report to the server. I can browse http://localhost/reports to access the report and view them (Via terminal server). But when I browse the page from outside I recieve the following error;
"The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."
If I run from VisualStudio 2005 and try to access the page I get this message;
"The request failed with HTTP status 401"
What is Wrong??
Thanks in advance for any good suggestion.
best regards
Nroblex from sweden