Running the query in SSMS, Im using something like this to test
WHERE ( [SalesPerson].[SalesPerson].&[17] , [Department].[Department].[All] )
I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.
When I am executing below MDX query, it's giving correct result with out any issue
SELECT NON EMPTY { [Measures].[Daystorecieve] , [Measures].[PO Recieved], [Measures].[Post Award Milestone PO Analysis Count], [Measures].[Powith80pct Received]
[Code] ....
After the successful execution of the above query, I am trying to filter on my measure group [Measures].
[Daystorecieve] values not equal to "0". With minimum number of the dimension selection my query executing fine.
Please find the below query.
SELECT NON EMPTY { [Measures].[Daystorecieve] , [Measures].[PO Recieved], [Measures].[Post Award Milestone PO Analysis Count], [Measures].[Powith80pct Received] } ON COLUMNS,
[Code] ....
But, when I am trying to execute with total number of dimensions. It's running long time and giving out of memory exception. Is there any way to apply where condition on my measure group like where [Measures].[Daystorecieve]<>0.
Select Non Empty{[Measures].[Exposure Amount]} On Columns, Non Empty TopCount( ( AddCalculatedMembers(
[Code] ....
As a result I only get 29 records. One record in the middle is missing. If I change to Topcount 31 I get 32 records, where the missing record is also included.
If I remove the Zero supression I receive 30 records as expected. For me the MDX looks good. I will try to reproduce on AW.
I have this simplified query below to illustrate what I'm looking for. I would like to get the data for the time between the two dates in the where clause ('01-05-2015' and '10-03-2015'). In SQL it would be a BETWEEN. I'm looking for the MDX equivalent.
I have these dates hard coded but what I eventually will be doing is an @BeginDate / @EndDate parameter in an SSRS report.
SELECT [Measures].[Ship Resale S&D Run Rate] ON COLUMNS, [Branches].[Region].[Region] ON ROWS FROM [Sales] WHERE [Time].[Date].&[01/05/2015]:[Time].[Date].&[10/30/2015] *WHERE @BeginDate : @EndDate
I need help identifying the following SQL. Sounds like a simple question, but I am new to this type of design and ad-hoc sql with no store procedures. Basically we have no stored procs and all the selects are siting in a table for retrieval.
Select Column1 from db where SomeColum = '{1}'
Can someone explain the '{1}' and is it similar to ?
hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
I have my where clause as follows, but it's not working:
WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)
I give the user two paramters.
The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.
if I have a SQL string that has an IN clause in its WHERE statement so that I only bring back rows for certains primary key values, how do I handle this with SQL Parameters without having to do any string concatenation?
I have a MDX query , where I have a date Range in where clause.
I want to replace it with Cuurent Date and Last 7 days date.
I tried multiple ways using NOW function , but could not get it correct .
modifying the Query so that I can fetch DATA for last 7 days
SELECT NON EMPTY { [Measures].[X] } ON COLUMNS, NON EMPTY { ([PRODUCT].[PRODUCT].[PRODUCT].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [COLOR].[COLORName].&[BLACK], [COLOR].[COLORName].&[BLUE] } ) ON COLUMNS FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS FROM [MYCUBE]))
I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.
Is it possible to use parameters in MDX Studio? I've tried using SSRS2008 for query design but... SSRS2008 query designer complains that parameters do not exist when they quite obviously do ( in the report) LaCie drives. Failing when you need them most."La" meaning "Terrible", "Cie" meaning "customer service"
SO I have a need to to limit the members of a dimension that get included with a query to just a few. So that means specifying which ones. How do I limit the main set based on a defined list of members in one of the dimensions im using.
In the query below, I need to limit the number of dimension members being included for CommType and MetricType. I get errors if I use a list of specific members in a where clause that includes these same dimensions.
WITH MEMBER [Measures].[YTD Actual] AS SUM({[DimCalendar].[Month Year].&[2015]&[1] : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[5]') } , [Measures].[Actual]
I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?
For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.
When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.
Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].
I have created a ssrs report which connects to vertica database through odbc connection. When I try to pass parameter value through parameter (e.g.: column name IN (@parameter) ) then getting error message in query designer prompting "Error in list of values in IN clause. Unable to parse query text. ". Using sql server 2012 , visual studio 2010 version and HP Vertica 7.1 .
How @StartDate and @EndDate parameters must added to the MDX query for usage in SSRS data set. strtomember can be used like
SELECT ( strtomember(@StartDate) : strtomember(@EndDate) ) ON COLUMNS FROM [Cube]
How can i specify that sub-select must work on the [Fact A] and [Fact B] rundate? strtomember(@StartDate) does not specify on which attribute this sub select is going to work. Any pointers ?
FROM ( SELECT ( [Fact B].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM ( SELECT ( [Fact A].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM [Cube] ) )
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Hi, I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId', N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question... Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too. The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like (@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row. Example Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application. Thoughts? Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname mike jackson roy mires
table2
district_name district_number South 123 Daggerty 7845 duffel 7224 rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause: select * from course where (name like 'sci%' or name like 'eng%')
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column? Do I use 'Having' or temp tables or Views? Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob FROM Transactions SELECT Count(Ticker) as Trades_Lng FROM Transactions WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM " sSQL = sSQL & sView sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!