Analysis :: How To Get Distinct List Of Member Values For Month Numbers From Date Dimension
Sep 10, 2015
Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
View 6 Replies
ADVERTISEMENT
Aug 28, 2015
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 Related
May 29, 2015
I am Using following MDX Query to get the data for max date in the Dimension. But every time I am passing date key to dimension to get data. How to automatically get the Max available Level member for that dimension.In this case I am passing 20150429 to every line instead i need to get that automatically.
SELECT
(
{
[DimDate].[HierYQMD].[Date Key].&[20150429],
{[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT.LAG(4):
[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT},
[code]....
View 4 Replies
View Related
Oct 19, 2015
<SQL Server 2008 R2>
I created a Time table using BIS. I found that the default naming of time members is too long and redundant.
For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me.
Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?
View 2 Replies
View Related
Mar 14, 2014
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
View 4 Replies
View Related
Feb 24, 2011
I am using sql server 2005 enterprise edition.
How to list all the dimension and fact column names with mdx or tsql query...
View 9 Replies
View Related
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
Sep 22, 2015
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]
[Code] ....
View 4 Replies
View Related
Nov 18, 2015
I have a measure group product price, the measures are
1. Product Id
2. Price
3. Date
I have mapped the product id and date with product and date dimensions.I need to get the latest price of the product.For example:
Prod1, 100, 1/1/2015
Prod1, 300, 2/1/2015
Prod1, 250, 3/1/2015
Prod1, 150, 10/1/2015
So, i need the latest price. The latest price is 150.I new to MDX query. MDX query to achieve this?
View 3 Replies
View Related
Jun 4, 2015
I am in a situation to slice a fact in two date dimension .. my <g class="gr_ gr_73 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="73" id="73">sql</g> code is like this
select count(1) from fact_table where end_date >=(selecteddate on filter) and startdate <=end_date .
The common date dimension is Start date. and the filter will be only on <g class="gr_ gr_168 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="168" id="168">end</g> date.
Below is the code so far
<g class="gr_ gr_228 gr-alert gr_tiny gr_spell ContextualSpelling multiReplace" data-gr-id="228" id="228">i</g> did, but <g class="gr_ gr_229 gr-alert gr_tiny gr_spell ContextualSpelling
multiReplace" data-gr-id="229" id="229">i</g> am not finding how to filter the start date <=end_date
with member [Measures].[X] as
Sum(
{[EndDate].[YQMWD].currentmember:NULL},
([Measures].[Count]))
select [Measures].[X] on 0 ,
[EndDate].[YQMWD].year.members on 1
from Cube
What is the trick I can put so that I can filter those data where start date <=selected end date ....
View 3 Replies
View Related
May 29, 2015
Link : [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?
View 10 Replies
View Related
Aug 11, 2010
I have a date dimension with below Hieararchy:
Year-->Quarter-->Month-->Week
Week is the lowest granular attribute.
Also i have a measure 'Holdings'
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings],
ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember
))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month],
[DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
View 13 Replies
View Related
Jul 7, 2015
We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
View 4 Replies
View Related
Oct 26, 2015
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
View 4 Replies
View Related
Apr 22, 2015
I create a Dimension Date using SSAS 2008 but when i execute the dimension and i go to see the result i have this result:the result is not sorted..what i need is having the result order by year i mean i have Calendrier 2020,Calendrier 2019 ...
View 6 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Jul 21, 2015
I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as
Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000
1/2/2000
1/3/2000
etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
View 2 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Oct 14, 2015
I am trying to create a comma delimited list of InvNo along with the JobNo .
CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'
[Code] ....
View 6 Replies
View Related
Jul 27, 2015
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8
View 3 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Apr 7, 2006
Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanks
View 4 Replies
View Related
Aug 15, 2007
I'm putting together a Kimball method SSIS package. My factSales table has an OrderRep key. If a match isn't found in the dimRep table I am inserting a dummy dimRep row and going on. That seems to be working.
My question is what do I do when the OLTP sales row has NULL for the OrderRep. This is possible; every sale does not have to have an order rep. My package is seeing that as a non match and trying to create a dummy row in the dimRep table for every NULL. I really don't want to do this. I can trap for the NULL rep and convert it to "unknown" or something but then the program would still create a single row in the dimRep table for unknown. Is that the best way to handle this? Or is there a way to trap for NULL and ignore the entire lookup process? A conditional split before every key lookup?
I have about 5 or 6 other dimension tables that will have the same NULL possibility.
Thanks.
View 5 Replies
View Related
May 21, 2008
hi all,
I was wondering if it is possible in SSAS 2005 that a calculated member is based off of an (integer) dimension attribute and another (integer) measure (let's say a multiplication operation) ?
If there a trick on doing so? other than stuffing the (integer) dimension attribute back in the fact table, as an measure?
thx much,
Cos
View 6 Replies
View Related
May 20, 2008
I want an MDX calculated dimension member, (with no measure specified in expression, so that any related measure can be used in a query/browse), and that accomplishes either one of the following:
Ideal: To calculate Median values on every level of a user hierarchy
If "Ideal" is not possible, then "Acceptable" is: To calculates Median values on one or more levels OTHER than the highest level.
View 3 Replies
View Related
Aug 10, 2015
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
View 3 Replies
View Related
May 28, 2008
Hi
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
Thanks,
Julia.
View 9 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Jul 1, 2015
I have a Calculated Member in SSAS that I need to adjust based what the current member is.
The code is below
CASE WHEN [Measures].[End LIS] = 0 AND "HELP" THEN
CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE
ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END
ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)
END
In English - i need this to translate to - of End LIS is 0 "AND the current member is the current month and current year" THEN carry on
Else ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2).
I came up with
CASE WHEN [Measures].[End LIS] = 0
AND [Dim Date].[FSCL_YM].[Month Nm].currentmember.membervalue = Format(now(), "yyyy")+"]&["+Format(now(), "M")
THEN
CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE
ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END
ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2)
END
But to no avail.
View 4 Replies
View Related
May 28, 2015
[DIMCustomerBuyer].[CustId].[CustId] is working.
[DIMCustomerBuyer].[CustId].
All members is not working any hint why this is not working.
View 2 Replies
View Related
Nov 21, 2008
Uknown Member is set to "Uknown" in the cube . This cannot be changed because it needs to be visible in other applications. However, in my particular MDX I want to create a custom set that excludes this member.
WITHSET setNoUnknowns as EXCLUDE( [Dim].[Hierarchy].CHILDREN, { [Dim].[Hierarchy].UNKNOWNMEMBER } )SELECT{[Measure].[MeasureName]} on COLUMNS,setNoUnknowns on ROWSFROM [Cube]
With the above MDX, I still get a return for member "Unknown". I've confirmed that there isn't an explicit member name of "Unknown".Excluding [Dim].[Hierarchy].[Unknown] does not work either.
View 5 Replies
View Related
Aug 23, 2015
I’m trying to build a calculated member (see below script) using “Except” function but I get an error result:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
My idea is to take a measure and exclude 2 members from the dimension.
I tried using “Aggregate” but got the error:
#Error Query (3, 1) Aggregate functions cannot be used on calculated members in the Measures dimension.
Please note that my measure is ACD that is already calculated average in olap and I can’t use AVG function instead of Aggregate. What can I do?
With
member [Measures].[AppOrig All Roaming]
as
(
Except(
[Source IP Location].[Country Name].[All].children,{
[Source IP Location].[Country Name].&[Colombia]
[Code] ....
View 6 Replies
View Related
May 16, 2008
Hi,
I need to calculate the average of an existing measure of my cube.
The measure is PA_Salaire from a column of my fact table.
I would like to calculate an average i could use with all the dimensions of my cube but i think i can only create one linked to a single dimension. Is that true ?
I dont really understand the way i should write my request. Here is what i did:
(Manager is one of my dimensions, it has a parent child relation... is that a problem? does it change the way i should do?)
Avg(Descendants([Manager].CurrentMember, [Manager].[Name]), [Measures].[PA Salaire]
But that doesnt work at all i get the following result: " #VALUE! " for every manager
Thx for your help
Francois
View 5 Replies
View Related
