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.

View 4 Replies


ADVERTISEMENT

Return And Assigning Values In Functions

Dec 19, 2007

Hi ,

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

IF @Rows > 0
SELECT @RETURN_ENABLED = 'N'
ELSE
SELECT @RETURN_ENABLED = 'Y';


-- 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

Am I doing it correctly ?

I would appreciated any help you could give me.

Thank you

View 5 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

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)

How can I code a stored procedure to do this?

View 17 Replies View Related

Analysis :: Which Data Type To Use For Variable Values

Aug 19, 2015

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.

View 3 Replies View Related

Transact SQL :: Return Preset Data Values Based On User Material ID

Jul 22, 2015

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?

View 2 Replies View Related

Analysis :: Convert A Zero To Null Or Empty?

Sep 10, 2015

I am working on a cube and I want to hide the results of a calculation if it is zero.

It is an inventory problem based on a transaction pattern. (add plus one to the inventory, add minus one to the inventory).

The sum of those two is zero, but the cube get messed but by thousands of zero.

I found some solutions of displaying 0 for NULL but I want to dispaly NULL (or empty) for 0.

View 2 Replies View Related

Analysis :: Top Count With Non Empty Clause

Aug 7, 2015

I have an autogenerated MDX.

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.

View 5 Replies View Related

Data Warehousing :: DateDiff Function To Return Positive Value Irrespective Of Values Passed

Aug 7, 2015

I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.

Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .

The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.

Any query solution so that always positive value is returned.

View 3 Replies View Related

Return Value Of Empty List

Aug 15, 2006

Hi,

I'm trying check the records which satisfies the following condn.

select @p1 = su1 from RER where (no=10 and atp ='N')

It returns empty list . bcos no match found.that's ok.

But i need to write the logic if record is not there.

i tried to do as follows

if ((@p1 =null) or (p1=' '))

...some logic

else

.. other logic

It's always executing else case. How do i catch empty value using sql query.

Is there any command there like null?

I tried in SQL query analyzer. Using SQL 2000.

Thank You

View 6 Replies View Related

How To Return A List Of Empty Tables

Mar 19, 2003

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.

Thanks for the suggestions.

Al

View 3 Replies View Related

Big Queries Return Empty Result Set

Apr 22, 2004

Hello guys,

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.

View 5 Replies View Related

SQL Case Statement To Return 0 For Empty Row

Jun 11, 2008

Hi,

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 ............

Can anyone help me please??

View 8 Replies View Related

Return Primary Key Even When Foreign Key Is Empty

Feb 20, 2015

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'])."%')

View 1 Replies View Related

Analysis :: How To Handle Empty Resultset From OpenQuery Call To Linked Server

Aug 17, 2015

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.

View 2 Replies View Related

Table Valued Functions - Yield Return Error

Aug 4, 2006

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?

Thanks for the attention.

View 4 Replies View Related

Analysis :: Bitmask Column Values As Dimension Values

Jun 18, 2015

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).

View 2 Replies View Related

Copy Table Scripts Results In Empty Return From Sysobjexts?

May 22, 2001

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

View 1 Replies View Related

Return Statements In Scalar Valued Functions Must Include An Argument

Jan 20, 2006

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

View 4 Replies View Related

RETURN Statements In Scalar Valued Functions Must Include An Argument ERROR

Aug 16, 2006

Hi,

I am trying to write a function which takes a string as input and returns the computed value.

I need to use the output of this function as a coulmn in another select query.

Here is the code (Example: @Equation = '(100*4)+12/272')

create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin

return exec('SELECT CAST('+@Equation+' AS float)')
end

I am getting this error when i compile it

"RETURN statements in scalar valued functions must include an argument"

Any suggestions would be appreciated.

Please respond

Thanks

View 6 Replies View Related

Analysis :: MDX To Show Top Ten Values And One Last Row With Sum Of Resting Values

Dec 14, 2011

I have to show 11 rows:

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]

View 4 Replies View Related

Sampling Data Set Via Integration Services Data Flow For Data Mining Models Without Saving Training And Test Data Set?

Nov 24, 2006

Hi, all here,

Thank you very much for your kind attention.

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.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

View 5 Replies View Related

Empty Values In SQL Sentence

Aug 29, 2004

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

View 4 Replies View Related

What Functions To Format Values ....

Oct 14, 2006

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.....

View 2 Replies View Related

Analysis :: Return EOY Value

Nov 12, 2015

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:

[Measures].[AverageBalance], ([Post Date].[Calendar].CurrentMember).Lag(1))
([Measures].[AverageBalance],  ClosingPeriod([Post Date].[Calendar].[Month],  Ancestor([Post Date].[Calendar].CurrentMember, 1).Lag(1)))
IIF([Post Date].[Calendar].CurrentMember.Level.Name = "Date", ([Measures].[AverageBalance], Ancestor([Post Date].[Calendar].CurrentMember, 3).Lag(1))

And a lot of other commands but nothing is returning the correct value.

View 3 Replies View Related

How To Insert Values Into Table Through CLR FUNCTIONS

Jan 15, 2008



Hi,

I have just started working with CLR Userdefined functions(SQL 2005),the below code shows I am inserting a row into
table through a function.

I dont know where am going wrong;but nuthing is happening.
I checked the connection also,in Server Explorer its getting connected with the data base.

**Please help me regarding this******

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static int EmpName()

{

using (SqlConnection conn = new SqlConnection("Context Connection=true"))

{

conn.Open();

SqlCommand cmd = conn.CreateCommand();



cmd.CommandText = "INSERT INTO dbo.Employee VALUES('MOHAN',66,22,'GDGDG',55)";

//SqlDataReader rec = new SqlDataReader();

//rec = cmd.ExecuteReader();

int rows = cmd.ExecuteNonQuery();

//string name = rec.GetString(0);

conn.Close();

return rows;

}

View 3 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

Passing Values In User Defined Functions

Apr 25, 2007

i want to pass a value from one user defined function to another how do i do it.E.g

My first function calulate a value which is to be used by another function for calculation
my 2nd function is given below

Create Function Avg_WLPD_CFS(@Res float,@TotDay int)
Returns float
As
Begin
Return(@Res/@TotDay)
end

the value @Res is calculated from first function, how do i pass this value to the above function.

View 1 Replies View Related

Problem Finding Values With Aggregate Functions

Jul 23, 2005

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

View 2 Replies View Related

Write A Report That Includes Different Lab Values For Account Number Depending On The Test?

Dec 10, 2012

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.

Here is an example of what I'm getting:

Acct Number MR # AdDateDDateCode CDateCTime Result Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0831110515 4.7 WBC
E00000000000 MR00000000 0824110902111012 0827110525 5.1 WBC
E00000000000 MR00000000 0824110902111012 0826110455 5.3 WBC
E00000000000 MR00000000 0824110902111012 0828110525 5.7 WBC
E00000000000 MR00000000 0824110902111012 0829110500 5.7 WBC
E00000000000 MR00000000 0824110902111012 0901110500 6.6 WBC
E00000000000 MR00000000 0824110902111012 0825110609 6.8 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB
E00000000000 MR00000000 0824110902111032 0831110515 10.2 HB
E00000000000 MR00000000 0824110902111032 0826110957 10.2 HB
E00000000000 MR00000000 0824110902111032 0827110525 10.4 HB
E00000000000 MR00000000 0824110902111032 0826110455 10.5 HB
E00000000000 MR00000000 0824110902111032 0901110500 10.7 HB

Her is what I need:

Acct Number MR # AdDateDDateCode CDateCTimeResult Test
E00000000000 MR00000000 0824110902111012 0830110515 4.5 WBC
E00000000000 MR00000000 0824110902111012 0824112050 9.3 WBC
E00000000000 MR00000000 0824110902111032 0830110515 10.1 HB

View 7 Replies View Related

Analysis :: How To Return Column Name Without Qualifier Within MDX

Jun 4, 2015

I have just run an MDX query which returns a result set within Excel 2013. The column names are in the following format; -

[Dimension Name].[Hierarchy Name].[Attribute Name].[MEMBER CAPTION].

Where I just simply want the attribute name on it's own and simply the measure name for the measures.

View 9 Replies View Related

Inserting Empty Values Into NOT NULL Columns Via ODBC

Jul 20, 2005

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.

View 2 Replies View Related

SSIS Converting To Date Empty Fields (no Values)

Jan 8, 2008



Hi, how do I convert dates with SSIS, when I receive dates I have no problem, with empty fields I have to go through a transformation as:


trim(field_name) == "" ? "999999" : substring....... (derive transform)

after this I have to update each field holding '999999' to NULL. (using sql task)

Is there an easier way to doing this?

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved