Hi,
I'm working in a bank and I'm creating a report for my company. I just started learning SQL last month. I hope this forum would help me. Here's my problem:
I have date for the last 6 months. I want to filter current quarter and last quarter data. This means, on the next quarter, the data will automatically moves forward. Is that possible?
I would appreciate any help.
--Example Schema posted at end of message:---For reporting purposes, I need to build a single comma delimited string ofvalues from the "many" tableof a database.The easiest way to see what I want is to look at the sample after mysignature.By the way, this is actually a busines problem, not homework! I justcreated a simpleexample using class and persons because everyone is familiar with thatrelationship.I have two tables on the 'one' side of the relationship: PERSON and CLASSThe ENROLLMENT table resolves the many to many relationship between PERSONand CLASS.(I know that a real system would be date effective, etc, but this is just asimple example.that will show my problem). ENROLLMENT has one row for each Class in which aPerson is enrolled.Look at the sample report: I have to "flatten" the join result and listthe class titles in acomma delimited string. I am stuck with this reporting requirement, and Iam NOT going to denormalizethe tables.One way to accomplish the result is to use a cursor to step through the rowsand build a "Classes"string with concatenation. I don't much like this option. I am not writingthe front end code,but I want to make it easy for the developer. Ideally, I would like to givehim a flattened viewso he can just do a simple join and run his report.I believe that what I want cannot be accomplished with ANSI SQL. However,does MS SQL have someextensions that could help me do the job? Failing that, how could I write astored procedure that wouldreturn the personID and the "Classes" string in a format that would bejoinable to the other tables?Thanks,Bill MacLeanP.S. Some people like to see actual database scripts as samples instead ofa textual representation.I have pasted in a script that creates sample tables and populates them.--Sample Tables and Reports:TABLE PERSONPersonID LastNM FirstNM--------- ----------- ---------1 Smith John2 Jones Sara3 Smith LucilleTABLE CLASSClassID ClassNM----------- ------------------10 SQL Server 10120 C++25 Object Oriented Design40 Inorganic Chemistry50 Organic Chemistry80 Early Lit.TABLE ENROLLMENTPersonID ClassID-------- ---------1 102 101 401 803 203 25SAMPLE REPORTPerson ID Name Classes--------- ----------------------- -----------------------------------------------------------------1 Smith, John SQL Server 101, Inorganic Chemistry,Early Lit.2 Jones, Sara SQL Server 1013 Smith, Lucille C++, Object Oriented Design/************************************************** ********SQL Server Script/************************************************** ********/CREATE TABLE [dbo].[CLASS] ([ClassID] [int] NOT NULL ,[ClassNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[ENROLLMENT] ([PersonID] [int] NOT NULL ,[ClassID] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PERSON] ([PersonID] [int] NOT NULL ,[LastNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[FirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[CLASS] WITH NOCHECK ADDCONSTRAINT [PK_CLASS] PRIMARY KEY CLUSTERED([ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] WITH NOCHECK ADDCONSTRAINT [PK_ENROLLMENT] PRIMARY KEY CLUSTERED([PersonID],[ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[PERSON] WITH NOCHECK ADDCONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED([PersonID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] ADDCONSTRAINT [FK_ENROLLMENT_CLASS] FOREIGN KEY([ClassID]) REFERENCES [dbo].[CLASS] ([ClassID]),CONSTRAINT [FK_ENROLLMENT_PERSON] FOREIGN KEY([PersonID]) REFERENCES [dbo].[PERSON] ([PersonID])GO-- Insert rwo for each CLASSINSERT INTO CLASS VALUES (10,'SQL Server 101');INSERT INTO CLASS VALUES (20,'C++');INSERT INTO CLASS VALUES (25,'Object Oriented Design');INSERT INTO CLASS VALUES (40,'Inorganic Chemistry');INSERT INTO CLASS VALUES (50,'Organic Chemistry');INSERT INTO CLASS VALUES (80,'Early Lit.');-- Insert row for each PERSONINSERT INTO PERSON VALUES (1, 'Smitn','John');INSERT INTO PERSON VALUES (2, 'Jones','Sara');INSERT INTO PERSON VALUES (3, 'Smith','Lucille');--Insert row for each ENROLLMENTINSERT INTO ENROLLMENT VALUES (1,10);INSERT INTO ENROLLMENT VALUES (1,40);INSERT INTO ENROLLMENT VALUES (1,80);INSERT INTO ENROLLMENT VALUES (2,10);INSERT INTO ENROLLMENT VALUES (3,20);INSERT INTO ENROLLMENT VALUES (3,25);
View 3 Replies View RelatedI have a DB I want to make a copy of for testing purposes so we don't crash our production DB. I want the new "Test" DB to reside in the same SQL Server Group on the same SQL server. I have tried the Copy DB wizard but it will not allow you to copy the DB to the same server. What is the best way to do this?
View 3 Replies View RelatedTASK: At my work we want to categorize and summarize all our IIS web logs and make statistics from it and such. What I need to do is take the browser type from a certain column in the table. All the information is stored in 1 column, and I figure an instr function would be best to do this. I am new to SQL, so I was told to look up the cursor function. In summary, I want to take all the IIS data and match it up against a defined table and then have a sum function for each browser.
Here are some examples of what the column data looks like: (found in the [csMethod] column
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+2.0.50727)
Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+InfoPath.2;+.NET+CLR+1.1.4322)
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv:1.8.0.8)+Gecko/20061025+Firefox/1.5.0.8
I made a define table which lists an ID (primary key) and instr to search for as well as the full browser name. (define.browser)
DEFINE.BROWSER
ID# INSTR# BROWSER NAME
###############################
1___Opera+7_______Opera 7
2___Opera/9_______Opera 9
3___Safari/_______Safari
4___Firefox/1.0___Mozilla Firefox 1.0
5___Firefox/1.5___Mozilla Firefox 1.5
6___Firefox/2.0___Mozilla Firefox 2.0
7___MSIE+5.5______Microsoft Internet Explorer 5.5
8___MSIE+5________Microsoft Internet Explorer 5
9___MSIE+6________Microsoft Internet Explorer 6
10___MSIE+7________Microsoft Internet Explorer 7
11_________________OTHER BROWSER
I am having problems getting a cursor to work. Are there any good tutorials out there, or can anyone be of assistance. Thank you in advance.
Can anyone give any advice to finding db files for testing. CSV files or what have you. I want to have some nice fun and repetitive Transact practice in creating db's and users etc. I have seen some online but I wondered first if anyone here used a site in particular that they could recommend. Would be nice to have files that may be standard (csv) and in another db format that I could use, mix it up some.
View 2 Replies View RelatedHi there,
Using symmetric keys and certificates in SQL2005, can one assign users permission to only decrypt or encrypt data?
Reason would be say data capturer and data reader type roles. I tried to create some with the GRANT CONTROL and GRANT VIEW for certificates and definitions on Symmetric keys, but havent been to successfull.
Would be great if someone here can offer some advise on it, and if it's possible using SQL rights.
thanks
Is it possible in SQL Server 2005 to limit the number of processors used? For cost reasons, we are consolidating servers and want to start running SQL Server 2005 on one of our dual-processor Win2K3 machines instead of the standalone machine it's currently running on. Because we have about 75 users, it's only cost effective to purchase a processor license (vs. a server license with CALs). But right now we only need and can only afford a single processor license, not two. So...
Is there any way in 2005 to limit the number of processors used so that we only need to purchase one processor license? I know in 2000 you could set this on the "Processor" tab of the "SQL Server Properties" dialog. In 2005, is this accomplished by unchecking the "Processor Affinity" and "I/O Affinity" checkboxes for processor #2 on the "Processors" page of the "Server Properties" dialog? If I uncheck these two options does that fully disable SQL Server 2005 from accessing the second processor in any way? From things I've read I can't tell if it restricts access to the second processor completely or if it just places some limitations on the ways it accesses the second processor.
Also, the licensing information for SQL Server 2005 leads me to believe that if you are going down the "processor licensing" route that you have to buy a processor license for every processor that the OS itself has access to and not just what processors SQL Server has access to. I thought I understood that in SQL Server 2000 the licensing information did allow you to buy a processor license just for each processor that SQL Server 2000 had access to, but has that changed for 2005?
Hope someone can provide some clarification on limiting processor access and the licensing implications for SQL Server 2005.
Thanks,
Scott
Hello,
I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.
My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.
Sorry this is so long...
Thanks,
Brian R
WESNet Systems, NOC
Hi,
I work for a training company, and we'd like to run a SQL Server course. Can we use SQL Server 2005 Express, or would we have to buy a full license?
Ian
Hi
I have what seems a simple requirement. We want to import the contents of a SQL Server 2005 Reporting Services report into a SQL Server 2005 database, in order to perform some checks on reports displayed to users. Is there an easy way to achieve this? XML would seem appropriate, but I can't find a step-by-step guide on how to achieve this. Any pointers/suggestions would be appreciated.
Thanks
Neil
I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)
I have done this in the past using
Period_Sequence =
calculate
(countrows(Period)
,filter
(Period
, Period[Start_Date] <= earlier(Period[Start_Date])
&& Period[Duration_String] = earlier(Period[Duration_String])
)
)
Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)
E.g. I want
Period_Name, Type, Sequence
2015 Q4, Quarter, 1
2015 Dec, Month, 2
2015 Nov, Month, 3
2015 Oct, Month, 4
2015 Q3, Quarter, 5
2015 Sep, Month, 6
etc
even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:
Period_Sequence Desc =
calculate
(countrows(summarize(ComponentPeriod, ComponentPeriod[Period_End_Date]))
,filter
( ComponentPeriod
, ComponentPeriod[Period_End_Date] >= earlier(ComponentPeriod[Period_End_Date])
)
)
But this doesn't distinguish between the different types. I need an equivalent of the t-sql -
row_number() over (order by Period_End_Date desc, case when 'P3M' then 1 when 'P1M' then 2 end asc
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies View RelatedHi There,
Our DBA has installed reporting services on a server and now in order to access the report manager, one has to be an Admin on that Server. I am guessing that there is a mistake in the configuration of Reporting Services. Usually it should allow anybody who was added to the roles in the properties section of the Report Manager, right? I have also added the users to the DB..
Also I am using Windows Authentication to access Report Catalog items (Reporting Services is installed on Server2) from a web Application(deployed on Server1) and displaying the report using report viewer. For some reason, server1 has to be in an Admin role on Server2 to access the report catalog/report. This is kinda strange for me as I don't want everybody to be an Admin on Server2. Can anybody please point in the right direction?
Thanks.
We have installed SQL Server 2005 with reporting services.When tried to open getting errors as below: how to avoid such errors See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. ---> System.Runtime.InteropServices.COMException (0x8000000A)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
[code]...
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.
Is there any way to get the report toolbar using SOAP Api in SSRS reporting.
View 6 Replies View RelatedWe've got a requirement to build the real time report. user can browse report at any point of time and need to see the latest data(stock market) in the report.
I've few options down...
1. Directly point to OLTP database as source and write stored procedure to show result set.
2. Replicate the database and write the SP's to reports. To avoid pointing directly to OLTP db.
3. To build the datawarehouse with dim & facts to show it in reports. I prefer this as a standard method, but this would have some latency depending on trasaction load which will differ from the requirement.
HI There
We are required to gather certain information regarding reporting services.
Which reports were requested, by whom , how long did they run for, what parameters were passed etc etc.
I see that the system tables do provide some of this information, is there a 3rd party tool or something we can use to gather all this type of information for us?
This is for reporting services 2000 and 2005.
Thanx
Dear All:
My reporting manager and reporting server were working fine in IIS. But when I booted the system one day, one error message came "Unexpected error" for the IIS. So I removed the IIS and reinstalled he same.
But after that, I tried to give virtual directories to reporting manager and reporting server sites. But it ain't working. Could you please help me?
My IIS 5.1
Reporting service 2005 Express with SQL Server Express
ASP.NET 2.0 Express.
Please help.
Thanks in Advance
Emmanuel Mathew
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
I started my Reporting Server and if I use the Internet Explorer as a Administrator everything works fine.But if I don't open it as an admin it calls "rsAccess Denied The user [...] does not have the permission...".Problem is that I want to run it in an SAP Program with an HTML Viewer. how I can get those Permissions ?By the way somehow I'm not able to create or change roles in the Microsoft SQL Server Management Studio.
View 5 Replies View RelatedHello, I understand that SQL Express Advanced Edition does not support report subscriptions. However, I have two questions:
Is there a way to run a report in a batch mode where the web ui would not show and perhaps the result of the report could be automated to export to .pdf format?
Does anyone know if SQL Express 2008 with Advanced Services will support report subscriptions, or any sort of a batch reporting capability?
Thanks in advance!
Tim
Hello,
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified.
Restored my Reportserver and ReportserverTempDB databases
Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database
System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'?
Is it possible to migrate from 32bit to 64bit?
Any help is appreciated
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
We have SCCM 2012 primary site and Remote SQL 2012 server. Due to hardening and password reset we are facing reporting issue.
while we Open the SRS report in SQL server and try to edit the Report (Report Builder) we are getting following error due to which we are unable to configure Reporting Service point in the SCCM 2012 server. We created the New Reporting server database still we are getting the below error.
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
This humble grasshopper seeks wisdom.
Respectfully Submitted,
Dave Matthews
Atlanta, GA
aka FlooseMan Dave
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end.
One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows:
Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets?
Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run?
Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success.
Thanks to anyone who can help.
-- Mark
Hi all. I have a dts package called package1 that I run once a weekthat does some imports/queries against about the same amount of dataeach run. I am wondering (hoping!) if I can run a query that will showits performance over the past 6 months in terms of how long theexecution for each run took.I think SQL server saves this dts package run history somewhere and ifso how can I do this? Am I totally off base?
View 1 Replies View RelatedI work in a big project and we will begin in using reporting services as the base technique for reports and I will be responsible for this part. but I have a problem I will discuss in the following:
Currently: We use currently devexpress reports and we have 2 languages(Arabic and English). the data in tables saved in two ways (Arabic and English). when the end user change the language of the web site the report data language changed when run it.
Example:
we have table with (ID, NAME_AR, NAME_EN, JobTitle_AR, JobTitle_EN). designed report will display(ID, NAME_EN, JobTitle_EN) . but the end user change the language of the system the report will
dispaly(ID, NAME_AR, JobTitle_AR)
Hint:this done pragmatically
how to do this in reporting services.
Hi there, i have been asked to build some reports to extend an app that i wrote. I have written the basics but i am stuck on a few things
In SQL (or maybe C#) how can i break down my outstanding costs from orders into 1 - 3 days 3 - 7, 7 - 14, 14 - 21 - 21 - 30, 60, 90+
I have a basic idea, e.g. SELECT * FROM tbl_orders WHERE status = outstanding AND (date_ordered >= txtFromDate AND date_ordered <= txtToDate), i have used the between function, however because my DB is in american date, and my web.config formats it into UK date/time it throws an error
Can anyone tell me how I can create a simple job or package to run a COmmand such as dbcc sqlperf(logspace) and have the results sent to a text file?
THanks
David
I have 50 rows in my sumary table.I wantted to show them in the front end with pagination,means i wanted to show those 50 rows in 5 different pages with 10 rows per page.
How can i do that using sql stored procedure.
Anyone know anything about SQL reporting service I went to Microsofts site to check it out. How much is it anyone know??
View 1 Replies View Related