Is there any way to retrieve the weight % of some values?
A quick example:
A 10 -> Result i want is 10 / 60 (60 is the SUM of A,B and C)
B 20 -> 20 /60
C 30 -> 30/60
Code for number of subscriptions
EVALUATE
SUMMARIZE('Subscriptions',
'Subscriptions Type'[DS_SUBS_TYPE],
"Number of Subscriptions ",'Subscriptions'[NR_SUBSCRIPTIONS]).
I got a measure that is a calculated member Purchase-Invoice that give the NotBillable. When I query the cube in an Excel pivot I retrieve the zero as well:
I tried to create another calculated member in this way
If
([Measures].[Not_Billed]=0,blank(),[Measures].[Not_Billed])
but Error 2 MdxScript(Billing) (11, 5) The provided expression is not a valid MDX expression. 0 0
how can I retrieve translated attributes using mdx query. I have translated my dimension's attribute in French(France) but not getting it in caption column. I used below query
SELECT * from $SYSTEM.MDSCHEMA_LEVELS
LEVEL_CAPTION column has same value as LEVEL_NAME. I ran this query after changing default language of analysis service to French(France).
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).
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]
Hello there,
I need to get the last value (status) from a child table. I try to simplify the problem with the following example.
Create Table Users
(
UserId int,
Lastname nvarchar(50)
)
Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)
Create Table Status
(
StatusId int
Status nvarchar(50)
)
A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:
Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2
Will not return the wanted records because all authorized Users have been in this status.
I hope you understand the problem and can help me out.
Thx in advance.
Etinuz
Here is some sample data:
CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
i am able to store multiple email ids in table (separated by comma).
now the problem is how do i retrieve those email ids from the table so that i can send mails to those email ids??
thanks
Jaimin
Hi, i m using vwd 2005 express and sql express. i have created following tables table_office,table_customer,table_order in my sql express. i also added one more table named table_final. In my webform i have 2 textbox and submit button plus(controls to take values for table_office,table_customer,table_order).so when user fills the form and clicks the button the data gets inserted into thier respected tables.similarly table_final gets populated with values in this way (id,name,address,table_office_id,table_customer_id,table_order_id) Now when displaying the output in the gridview. i combined values of all these tables together. its fine till here. Now what i want is i want to write a query to retrieve these values back into the webform . Here i want the values from all the tables back into the webform controls on a click of a button.so that i can modify it manually, make updation on it and finally show it into gridview. i hope i m able to make u understand. anyway can somebody help me with simple code(C#) and sql query to achieve this task.? thanks. jack.
View 8 Replies View RelatedI have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
Hi,
my configuration file provides parameters that are used for an SQL query in my package. However, for these parameters it must be possible to pass NULL to the query.
This seems to be impossible in SSIS..? If the config file provides no value, the package uses a default value - and this default value can not be null.
Any suggestions?
Thanks a lot,
Regards
Jan
when i use a filter in report builder, it's not able to retrieve the information to populate the dropdown menu, the dropdown menu is empty. I get a triangle with exclamation mark and the following message and the drop down box is not being populated:
the requested list could not be retrieved because the query is not valid or connection could not be made to datasource
I am able to generate reports but not able to use filter. what could cause this issue ?
Chi
Hello!
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102
Tab2
DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers
Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO
CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO
SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies View Relatedwhat use reason of 'weighted-term' ?explain it.
SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, 'ISABOUT(mohsen weight(.8),yaser weight(1.0))')
table [contain-1] information:
ID FIRSTNAME
1 mohsen
2 mohsen
3 yaser
4 mehdi
thanks,mohsen
HI
The below is the asp code pasted here to explain what I am trying to do. How to Sum the weight of the item group by itemcode which is passed as a parameter
Do While Not objRS.EOF
itmcode = objRS("SKU")
sSQL = "SELECT code, numpacks, weight FROM tblproducts WHERE code = '" & itmcode & "'"
Set rsNumpks = Conn.Execute(sSQL)
If Not rsNumpks.EOF Then
numpacks = CDbl(rsNumpks("Numpacks"))
weigh = CDbl(rsNumpks("Weight"))
End If
Set rsNumpks = Nothing
Loop
Hi all,
Does this paramater/metric exist in any of the DM Models in AS2005?
Thanks in advance.
Mark
Is it possible to dynamically set the font weight (bold vs normal) for a matrix row label? I always get an invalid value message box from the expression editor. For example:
IIf(Inscope("matrix1_Label_Name"),IIf(First(Fields!Label_Bold.Value,"matrix1_Label_Name") = "y"),"Bold","Normal"),"Normal")
Something like the following:
label1 | xxx xxx xxx
label2 | xxx xxx xxx
label3 | xxx xxx xxx
label4 | xxx xxx xxx
Thanks
I am getting same value all over how to get unique values for each measures.
how to resolve it.
The cube going into browser or Excel, shows the following.
Measure Measure Calculated TotalRevenue(measure) TotalCost(measure) GrossMargin(calculated) Gross Margin%(calculated)
$#,##0;($#,##0) $#,##0;($#,##0) "$#,##0;($#,##0)" "#,##0.0 %;(#,##0.0 %)"
The excel gives me..
A $552,198 ($437,190) $115,008 20.8%
B $0 $0 $0
How can I suppress/hide the row with $0 values and NULL. So the user does not need to filter, in Excel.
Hi friends,
I have a text box with n number of text.
I want to set the font weight of each text in the textbox dynamically..
For eg.. suppose the text of the textbox is "Hello Friends", then i need "Hello Friends" as output.
Is there any way to accomplish this in SQL Reporting Service.
Any help will be appreciated. Its critical.
Please help me out ASAP.
I am trying to select some values as default for a parameter, I add my data set,add my parameter then I select the values in the default pane, when I run the report I get :
'the default value expression for the query parameter contains an error[BC30451] name is not declared.
When I look at the expression of the default parameter (in the report data pane under the specify values) it appears like
=new Object() {[Sty].[ST].&[1], [Sty].[ST]..&[15]}
with a red scrible under the ()
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 am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value
WITH
MEMBER
[MEASURES].[PARAMETERVALUE]
AS
[Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
[Code] .....
I tried using the exp
=Parameters!MONTH.Value(0)
so as to return the first parameter in the parameter dataset , but i have the error the expression that ref the parameter MONTH does not exists in the parameter collection.
I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow:
===================
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight
FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight
FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight
FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight
FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode
ORDER BY 'theWeight' DESC, m.ShortName
===================
What is the better way?
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
View 3 Replies View RelatedI have a parent Child attribute in my dimension. I am currently displaying the correct ID value as the business wants. So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName) for this ID.However they do not want it concatenated. They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this.
My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID
I've a table which contains a record for each day an employee is employed:In the cube I've create a measure which is a sum of the field "Employed" which provides me the number of employees that are employed for a given date (lowest level of my time dimensions which are YEAR-MONTH-DATE)The problem is that the number aggregating on YEAR and MONTH which provides me with wrong figures at these levels - So how can create a calculated measure (maybe with Scope) that only show the Ultimo Numbers at the lowest level when I am browsing on the Month or Year level. For example if I am on the Year level I only want it to sum on the date 31-12-xxxx and if I'm at the month level(for example July 2012) it should show me the sum of 31-07-2012 which are the last level for the given month.
View 4 Replies View RelatedI have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
Cost Centre
Location
Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up with
Division
NewSet
Dollars
AA
CustomType1
$166.64
AA
CustomType3
$64.24
BB
[code]....
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
Sorry, is this possible?
select * from COLLABORATOR x inner join
containstable
(CollaborAtor,*,
' ISABOUT (
"hiv and Gender" weight (.8)
or
"hiv or Gender" weight (.2)
)
'
)
as KEY_TBL
on x.ColId = KEY_TBL.[KEY]
==
basically I want all rows with either string, but I want those with both to rank first.
Also, how can I deal with singular vs plurals of words..
Thanks.
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 View RelatedLink : [URL] .....
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
Genre - Western: Movie1, Movie2, Movie3
Language - English: Movie2, Movie4, Movie5
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?