Analysis :: YTD / MTD Functions Return Empty Values Probably Due To Old Test Data
Jun 17, 2015
I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.
I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?
For eg I have written the following function which I called from a stored procedure. BEGIN --Declare the return variable here DECLARE @Rows NUMERIC(10) DECLARE @RETURN_ENABLED VARCHAR(1) -- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name Group By Profile_INdex
-- Return the result of the function RETURN @RETURN_ENABLED;
END
Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?
From the stored procedure I'm calling the function inside a CTE.
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled) AS (--Anchor. select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled From sys_menu_item as tb1 where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help') And tb1.item_parent= @menu_name --Members UNION ALL select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled from sys_menu_item as tb2 , GetHierarchy where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help') And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name ) select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled From GetHierarchy
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I am trying to find what datatype I can use for variable values like below in a column
E.g. column which we get
10000.10 100 180.34 98203710231.34
From the above example, you can see some of the values contains no decimal and with decimal
Also we cant say whether the decimal comes after the 5th number or 10th number. Any other datatype to capture this values. If not last option is to give varchar2.
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
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 want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried: select left(s2.name,32) as TableName, max(s1.rows) as Records from sysindexes s1 inner join sysobjects s2 on s1.id=s2.id where type = 'U' and s2.name like 'New%' group by s2.name HAVING max(rows) = 0 ORDER BY TableName but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like: TableNameRows NewARTxn0 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables: If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice. The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application. Has anybody suffered this problem and what solution was adopted? Thanks for any information you can provide.
I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.
SELECT 3 row_id, '2003' year, 'Mar' period, (SELECT count(*) FROM news WHERE news.id IN ( SELECT news.id FROM news WHERE news.announced_date >= '2003-03-01' AND ........
So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.
SELECT 3 row_id, '2003' year, 'Mar' period, (SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN ( SELECT news.id FROM news WHERE news.announced_date >= '2003-03-01' AND ............
I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.
SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13 Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
So I was creating a new table-valued function today which queries some data from a preexisting table. Since this is my first table-valued function, I decided to check out some of the examples and see what I can figure out.
One particular example helped me out a bit until I ran into some data access issues... http://msdn2.microsoft.com/en-us/library/ms165054.aspx
So I create my function:
[SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read,FillRowMethodName = "FillMyRow",TableDefinition ="p1 int, p2 int"] public static IEnumerable getMyTable() { using (SqlConnection conn = ....) { using (SqlCommand command = conn.CreateCommand()) { ///.... populate command text, open connection using (SqlDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { customObject1 o = new customObject1(); ///... populate o's parameters from reader ... yield return o; } } } }
public static void FillMyRow( object source, out int p1, out int p2) { customObject1 f = (customObject1)source; p1 = f.p1; p2 = f.p2; }
Notice, this example yield returns the value o upon each iteration of the reader. Despite the fact that the DataAccess is set to Read I still get the error...
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
I did however get past this error, by creating a collection of customObject1, populated it within the while(rdr.Read()) loop, then return the collection after closing the connection, command and reader.
I assume this error has something to do with the fact that you can't yield return results from within an open reader. Is this error right though in this case? Whats causing it to throw a InvOp Exception? Or is this a bug?
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. It works effectively however i am capturing the movement of multiple fields. A simple example, for Order lines, i have a price, a discount and a date. I am capturing a 001, 010, 100 respectively for each change.
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server. but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
Ali
/*Truncate from all tables*/ select 'Truncate table ' + name from sysobjects where type ='u' order by name
/*Count all table rows from all tables*/ select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u' order by name
/*View all sp*/
select * from sysobjects where type ='p' and name not like 'dt%' order by name
/*View all triggers*/
select * from sysobjects where type ='tr' order by name
/*View all Views*/ use master select * from sysobjects where type ='v' order by name
I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int) RETURNS intASBEGIN RETURN SELECT COUNT(intCourseID) AS Expr1 FROM dbo.tbl_guru_course_list WHERE (intCatID = @intCatID)END
The first 10 rows would be the top ten values for a Measure and a Dimension that has 20 members. I can get it with the following MDX expresion:
SELECT TopCount(EXCEPT([Dim Category].[Dim Category].AllMembers,[Dim Category].[Dim Category].[All]),10,[Measures].[Value]) ON ROWS, [Measures].[Value] ON COLUMNS FROM [My Cube]
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:
INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')
with empty values, but then it doent update in the dataBase-only if all the values appear- what the solution of it? Thanks
pls what functions in sql format values like: 122334.98765 to become only with 2 digits after coma like this: 122334.98 what part of help in books online
and also what functions do methods on dates like getting date value from string, adding two different dates, getting the day, getting th month.....
I'm trying to return the EOY (12/31/14) value for measure Average Balance when I pass in a Date current member. This current member may also be at a Month, Quarter or Year level of the Post Date dimension. I've tried:
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.
We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.
