Hello all,
I got a problem when using Sql Service 2005. I have two parameters @ID and @Name. I want to choose either and only one parameter to generate my report at one time. Could anyone tell me how to do it? I tried the "or" tab after filter tab, but it only generate the report with both @ID and @name values I gave.
Thanks in advance.
Hi there - I have a longish bit of MDX which enable some MTD reporting. I'd like to power up the report so that users can select to report by WTD, QTD, YTD by clicking hyperlinks / buttons / whatever [I'm quite new to reporting services - not quite sure what's possible.]
I could create 4 separate reports with 4 separate datasets, or one report with 4 datasets, or is there some reasonable way I can tweak the MDX for a dataset on the fly, e.g. "search and replace, find MTD replace with QTD" &c &c.
Cheers,
JG
WITH
MEMBER [MEASURES].[WT TO MTD]
AS SUM(MTD([Time].[Year - Quarter - Month - Date].CURRENTMEMBER)
,[Measures].[TURNOVER])
MEMBER [MEASURES].[WT TO MTD PrEq]
AS SUM(MTD(parallelperiod([Time].[Year - Quarter - Month - Date].[Month] ,1
,[Time].[Year - Quarter - Month - Date].CURRENTMEMBER))
,[Measures].[TURNOVER])
MEMBER [MEASURES].[WT TO MTD Vs PrEq]
AS IIF([MEASURES].[WT TO MTD PrEq]=0, null
,([MEASURES].[WT TO MTD] / [MEASURES].[WT TO MTD PrEq])-1), format='0.00%'
MEMBER [MEASURES].[WT TO MTD Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD])
MEMBER [MEASURES].[WT TO MTD PrEq Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD PrEq])
MEMBER [MEASURES].[WT TO MTD Vs PrEq Rank]
AS RANK([SECTOR3].[SECTOR3 ID].CurrentMember
,[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,
[Measures].[WT TO MTD Vs PrEq])
SELECT NON EMPTY { [Measures].[WT TO MTD]
, [Measures].[WT TO MTD PrEq]
, [MEASURES].[WT TO MTD Vs PrEq]
, [MEASURES].[WT TO MTD Rank]
, [Measures].[WT TO MTD PrEq Rank]
, [MEASURES].[WT TO MTD Vs PrEq Rank]
} ON COLUMNS,
NON EMPTY { order([SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,[MEASURES].[WT TO MTD Vs PrEq], bdesc) } ON ROWS
FROM [WmCube4]
where [TIME].[Date].&[2007-06-04T00:00:00]
Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?
For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.
When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?
Thanks,
Rick
I want to use a Multi-Value Parameter in a COALESCE statement; e.g.:
SELECT COALESCE (@Selected,NULLIF((COALESCE(A,B,C,D,E,F,0) ^ 1)* COALESCE(H,I,J,0),0)) AS FOO
where @Selected is a list of column names.
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
At the moment I already added the DataSet I'm gonna be using. I have 2 date parameters Start Time and End Time.
What I would like to do for the report is to only pull results greater than 48 hours to the report
How can I accomplish this?
Hello,
How can I display data that only compare the date part (ignoring the time part) between the value of the date parameter and the database?. It displays data only if I pass the whole date (date and time)
I tried with the following query in the report services 2000, but it even didn't pass the query parse
SELECT * FROM table
WHERE (CONVERT(CHAR(8), table.PRODUCTION_DATE, 112)
= CONVERT(CHAR(8), @parameterDate, 112))
Any ideas will be appreciated.
Thanks,
Marco
Hi all,
If I use the following query for a Dataset and the execution takes a few seconds to show results
SELECT *
FROM dbo.ICParameter
WHERE (PatientID = @ID ) AND (LogTime > DATEADD(day, -1, GETDATE()))
ORDER BY LogTime
If I replace '99010200101' with @ID and enter '99010200101' when prompted for ID, the execution takes forever. Actually I have never got any results even after waiting for 10 minutes.
Could anyone shed some light on this?
Thanks in advance.
Hello All,
I am trying to set parameter in reporting services that lets the user select "All Hours" or "Cour Hours" of the day in a given date range.
Core hours would filter the transactions (and aggregations) that happened between 6am and 10pm on certain days and another range of hours on some days. The days that the core hours exist will be static. i.e. every day the core hours are 6am to 10pm but on Mondays the core hours are Midnight to 2am and then 6am to 11:59:59.
I am trying to figure out what that code would look like. Any help is appreciated.
- Clint
Hi,
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @Startdate is fine because the timestamps is at 12:00:00AM but my @EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
Thanks,
I have a problem with Date/Time parameters. When I try to preview reports locally, I get the following error message for dates past the 12th of a month:
An error occured during local report processing. The value provided for the report parameter 'Date' is not valid for its type.
My report is set to de-CH. If I enter a date into the datepicker in Swiss/German format (dd.mm.yyyy) Visual Studio changes them to mm/dd/yyyy but somehow tests them against dd.mm.yyyy and throws an error.
Windows language is German, SQL Server and Visual Studio are in English.
I installed the German language pack for Visual Studio and switched over to German to no avail. Changing the report language to en-US did not work either.
I have a situation
1. I have a parameter @Param1 that accepts multiple values
2. I have to display data based on selected values of @Param1
Here comes the tricky part.
3. I have to aggregate on all the available values of the @Param1
Do I need to take a hidden parameter just for aggregation? Or is there any other way?
In my report I have two date parameters, both are of type DateTime.
The problem is that when the report is called for the first time the report parameter value is not shown in the expected format (de-DE, 01.01.2007) but in en-US 01/01/2007
Only when I change a Date using the calendar popup and click on the "show report" button, the right format will be taken over.
Language properties of the report are"=User!Language"
IE language is de-DE.
Report Server is a MS Windows Server 2003 R2 Standart Edition SP2
with MS SQL SERVER 2005 SP2
P.S.
on a server with MS SQL SERVER 2005 SP1, this report works fine.
may be this bug is a new feature of SP2?
I have a production server log shipping to a secondary server every 30 minutes (both SQL 2000), which the second server is used for both a warm standby server and for reporting from users. Issue: the log shipping locks the DB so reporting can't be done until the load is finished, the load to the second set of databases has taken up to 15 minutes to finish allowing the users only 15 minutes to run reports, this is not acceptable. The server also needs to be used for DR.
I am looking for another solution, I can't use Transactional Log shipping as not all of the tables in the databases have a primary key identified. So, I am looking for a real-time or near real-time reporting server that is more available to running reports and a warm standby server for Disaster recovery. I am trying to figure out what SQL Server 2000 has to provide (or even 2005 or 2008?) or I am also looking at some third party software, but not sure what is the best for a reasonable price.
Any help is appreciated.
Thanks....JB
Hi All,
I have a dilemn:
On one side, I have a column C1 which could be a primary key because it is never null, the value is unique and identify the record. The problem is its a char type and its lenght can be close to 30.
Then, I've planned to add another column C2 of int type as PK. But then I need to add a unique constraint index on C1. Does it improve performance anyway?
Thanks
good day, everyone
if i have a transaction table with fields below :
transaction_no, product_id, product_desc, product_qty, product_txn, transaction_date
can some expert here point out to me , which is the best cluster-index and non-clsuter index ?
and possible kindly please explain why is it so? i'm not good in database so just explain like to beginner
thank you very much for guidance
(Hard to put a good subject on this one...)
I have a database containing a lot of users and these users can have four different kind of telephone numbers connected to them: "Direct phone", "Switchboard", "Cell phone", "Home phone". The phone numbers are stored in a separate table. Some users have 0 phone numbers, some have 1, some have 3 etc.
Now I have to transfer the data to another database with a strict table structure and here the table that contains the user also should contain the users phone number and an alternative phone number, if the user currently has more than one phone number connected.
This means that if for instance we have three or more phone numbers connected to one user, we can maximum transfer two of them. This is not a big issue though...
We have ranked the importance of the phone numbers in the order as I presented them above.
What I do in my T-SQL query is to do a ISNULL() and see if the user has "Direct phone" connected, if not I check for the next type and so on.
Now to my problem! Can anyone give me a suggestion of how to write the code for the extraction of the Alternative phone? What I need to do is to check if there is a "Direct phone" connected to the user, if so I should NOT chose that but the next phone number that I find.
I'm currently developing an ASP.NET website which is using SQL Server 2005 and I couldn't decide between two table designs and I hope you can give me your opinions
The website is for a school and it'll be used to create tests from questions. The teacher will:
1. Select grade (could be multiple selection)
2. Select class
3. Select subject
The thing is that same question could be used for multiple grades.
Example query: "Get me questions of trigonometry of math from grades 7,8,9"
(Names used instead of ID's to make it more clear)
The first design:
[BigRelationsTable]
ID - QuestionID - GradeID - ClassID - SubjectID
1 - Question123 - Grade7 - Math - Trig
2 - Question123 - Grade8 - Math - Trig
3 - Question123 - Grade9 - Math - Trig
This is a simple design but all of the columns will need indexes because all of them will be used for searching and that makes me think about table performance.
Second design:
[GradeClassRelations]
ID - Grade - Class
1 - 7 - Math
2 - 8 - Math
3 - 9 - Math
[ClassSubjectRelations]
ID - GradeClassRelationsID - SubjectID
1 - GradeClassRelations1 - Trig
2 - GradeClassRelations2 - Trig
3 - GradeClassRelations3 - Trig
[SubjectQuestionRelations]
ID - ClassSubjectRelationsID - QuestionID
1 - ClassSubjectRelations1 - 1
2 - ClassSubjectRelations2 - 1
3 - ClassSubjectRelations3 - 1
This one is more normalised but this time the need of doing multiple joins makes me wonder.
What do you think? Which one should I use? Or if you have any other suggestions I'm all ears
hi guys,
I have 4 columns and 3 rows. Columns are Name, Age, Gender and Weight. I have values entered for each column. I need to pick the highest value of weight if Name, Age, Gender are same and put that into new table. how can i do that?
Greetings SSIS friends
I want to implement the following query using SSIS Data flow Source component :
SELECT * FROM someTable WHERE someColumn = 'H'
How do I restrict the data coming from my data source? By that I mean how do I apply the WHERE clause in SSIS?! Should I use a conditional Split component?! but that would mean retrieving all records first then adding the split component (not the most efficient method surely).
Any suggestions would be much appreciated.
Hi there,
I have created an SSIS package which pushes data from SQL Server (2005) to Oracle (Version 9.2.0.7.0), but having some issues with setting up the connection objects to Oracle.
I have attempted the connection with both the OLE DB Destination connection in SSIS and also with an ODBC connection, but both come up with the same error "ORA-12154: TNS could not resolve service name".
I am using a 32-bit version of SQL Server 2005 and the Oracle DB is 64-bit. Any ideas?
Hi there,
There's a field with Varchar(5) type which contains both alpha and alpha-numeric data. When sorting based on this field 1001 comes before 101. Is there anyway I can sort it appropriately; is there any collation or something which could be used.
Result of the sort:
1001
101
102
1022
103
Thanks,
LOA
I want to choose a column dynamically depending on a session variable.
In other words, each customer has a different price level. so I need to choose the column that corresponds to thier price level in thier customer record
Any help is appreciated.
below is my futile attempt
SelectCommand="SELECT cove.CATALOG.ITEM_NO,cove.catalog."& <%#= session("pl") %> & ", cove.CATALOG.DESCRIPTION, cove.CATALOG.DESCRIPTION2, cove.CATALOG.DESCRIPTION3, cove.PRODLINE.DESCRIPTION AS Expr1, cove.CATALOG.GRP, cove.COMPANY.PRL, cove.COMPANY.COMPANY FROM cove.CATALOG INNER JOIN cove.COMPANY ON cove.CATALOG.WEB_STATUS <>'[I]' cove.COMPANY.I LEFT OUTER JOIN cove.PRODLINE ON cove.CATALOG.PRODLINE = cove.PRODLINE.CODE WHERE (cove.COMPANY.COMPANY = @company) AND (cove.CATALOG.GRP <> '[ACC]')">
<SelectParameters>
I want this...:
SELECT [DateEntered], [From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs] WHERE ([From] > 5/20/2008 12:00:00 AM) AND ([To] < 5/21/2008 11:59:59 PM)
Obviously this won't work, but can anyone tell me what WOULD work?
I have to write a large data migration script to move data from one SQL Server database to another. Is there any way to dynamically specify the server and database name? I would like to do something like the following, (but this does not seem to work):
Delete From [@ServerName].[@ImportToDatabase].[MyTable]
Any help appreciated
Hi,
I'm helping a small company built a new website and intranet in asp.net 2.0 and SQL.
I'm having trouble choosing among vs.net 2005, vwd express, SQL 2005
express or SQL 2005 Developers Edition for the prototype/testing phase.
Any advice? I want to build production-ready versions with easy
migrations to production, but I don't want them to have to spend too
much $$$ while it's just in alpha and being prototyped.
thanks,
Bruce
I have a job that selects alot of data from one database into another.
Can I choose not to log this operation (doesn't need to be and the log fills up before it's done)
Thanks..
type of code:
Insert into database_1
Select * from database_2
Hi guys!
I need to create a table always that a new register is add in TABLE_ONE
The name of this new table must by 'TABLE_' + TABLE_ONE.Cod
It will be:
TABLE_1
TABLE_2
TABLE_3
...
I know this code must be in a trigger of insert... but how? I did some tests but didnt work.
Thanks in advance.
Hello, everyone:
There are some explanations on this topic from books and BOL. Could you offer the real experience? Thanks.
ZYT
Hi,
I have a table with its ID field set as an Identity column with "seed" and "increment" set to 1. Now, I have rows in there that I don't want to change. I'd like the next addtitions to the table to start at a specific value for their ID columns.
Basically, how can I specify a new starting value for an identity column?
Thanks,
Skip.
I have visual studio 2005 and I am going to create a web application. Which data source or data provider will I choose when I'm connecting to the database(MS SQL Server)? and how about when I create a windows application? will I use the same thing? tnx
View 9 Replies View RelatedHello all I am trying to create a stored procedure that will either give the user a chance to use the wild cards or the IR_Number, or subjLastName (from the RevisedSubjects_Table) or a begin Date and an End Date.
CREATE PROCEDURE [dbo].[AdvSrchWildCardsIR]
(@Enter_IR_Number nvarchar(100))
AS
SET @Enter_IR_Number = REPLACE(@Enter_IR_Number, '*', '%')
SELECT dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type],
dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
FROM dbo.Revised_MainTable INNER JOIN
dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
IF patindex( '*', @Enter_IR_Number) > 0
BEGIN
SET @Enter_IR_Number = replace( @Enter_IR_Number, '*', '%' )
SELECT dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type],
dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
FROM dbo.Revised_MainTable INNER JOIN
dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
END
How Choose function in SQL is useful in a table. Any example with a simple table and how it can be useful for any particular column in a table
View 1 Replies View Related