Getting result for this
from [Trans]
where [Date].[Date].[2015-07-22]
But when i used like this not getting any result
With
member measures.A as "2015-07-22"
from [Trans]
where [Date].[Date].[measures.A]
I am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
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.
I need to filter my select statement with 2 parameters, each of which defaults to the "All" member. Some of the members may have spaces in the name.
So I need to handle something like these:
FROM MySalesCube
WHERE ( ... expression here ...)
The expression above needs to resolve to a set using both parameters
STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")
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.
[update]
Here is what I have now:
WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"] , [Department].[Department].[" + @pDept + "])") ) )
If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error
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
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.
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.
i want to limit the data being stored in a table to the past 6 months only.
data in the table is refreshed each night.
how do i do this without hard coding a date?
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] ....
Hi all. Fairly new to all this, but I just came across a problem today with SQL 2000. I need to get a report on all data in a table for the previous 12 months. So when ever the report gets run, say it gets run on Decemebr 4th, I would get all data from the beginning of last december up til the end of this november.
I tried saying WHERE dbo.Table.Date BETWEEN ((MONTH(GETDATE())-13) AND (MONTH(GETDATE())-1)) but that gave me everything between between December and November regardless of year.
Anyone know a good way to word this? I can't just ask for everything older than GETDATE()-365 because it needs to be Start of Month to End of Month regardles of when it's run.
Any help would be greatly appreciated.
I am working on query where i have to get data from 1 October 2007 till Current month - 1.
i.e
Current month is March 2008 then the data should be between 1 October 2007 till 29 Feburary 2008, and so on and so forth for every month.
I have written a query and It working fine but for some reason I think it can be done in some simple way.
Code Snippet
Select
datepart(mm,Tce1lsc0.budat) as 'Month'
,datepart(yyyy,Tce1lsc0.budat) as 'Year'
, Tmarketsector.market as 'Market'
, Tmarketsector.sector as 'Sector'
,Tce1lsc0.ARTNR AS 'Material_Number'
,Tce1lsc0.KNDNR AS 'Customer_Number'
from Tce1lsc0
left outer join TMarketSector on TMarketSector.[Kndnr]= Tce1lsc0.[KNDNR]
and TMarketSector.[Artnr] = Tce1lsc0.[ARTNR]
where convert (varchar (10) ,tce1lsc0.budat , 112) > 20070930
and ltrim (rtrim (datepart (mm,tce1lsc0.budat)+ datepart (yyyy,tce1lsc0.budat))) <> ltrim (rtrim (datepart(mm,getdate()) + datepart(yyyy,getdate())))
Hello all
I have a simple DataSource control setup to a basic database that includes a datetime column, I want to retrieve records from today and beyond.
How do incorporate this into the Where clause?
Im getting stuck with the syntax too so a specific example would be most helpfull.This is the DataSource
<asp:SqlDataSource ID="ResultsSqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT client, date FROM clients WHERE (client = @client) Order by date asc "><SelectParameters><asp:controlparameter ControlID="TextBox1" Name="client" PropertyName="Text" /></SelectParameters></asp:SqlDataSource>
If this requires code can you give C#
Thanks
Tino
Hi ALL,
Well, i have one view:
create view vwdate
as
select custno, custname, city, date
from salestab
where date = ????
well, this question mark...i have to fill..
like i want if month is june then i want for whole may month results of custno, custname and city..
if month is july then i want results for whole june month..
means for previous month results i want whatever the date is for that particular month..
Example:
if date='06/15/08'
then results will be
custno custname city date
---------------------------------
1 AAAA NJ 05/01/08
2 SDS CT 05/04/08
........................ 05/18/08
.......................... 05/30/08
if date='06/30/08' OR '06/1/08' then also results will be same..
so i want for previous month results..
so can anyone help me to get the correct condition in where clause?
Thanks ALL..
I'm trying to pull a list of users with upcoming or recently passed birthdays. I'm using a CAST to create a birthday of the person using their actual birth month and day, but substituting the current day. This CAST works just fine in the actual query, but fails when I put it in the WHERE clause with the error: Conversion failed when converting date and/or time from character string. I don't see how it can work in one area and not in the other.
Code:
SELECT
P.EMF_EMPL_ID,
CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS Birthday,
DateDiff(d, getdate(), CAST(CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) + '/' +
[code]...
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?
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 ....
I'm trying to make a parameter in SSRS that defaults to most recent date with sales. Below gives me all dates with sales. How can I get the results to give me just the most recent? I've tried MAX and LastChild in a couple different ways but no luck.
WITH
MEMBER [Measures].[ParameterCaption] AS [Time].[Date].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Time].[Date].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Time].[Date].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
[Code] ....
I don't have much knowledge on MDX queries. Here, requirement is to get the data less than the date passed from the parameter(Date - IV Financial].[calendar YQMD].[Date]).
I am able to create the below query(through Query designer) which is giving the data EQUALS to operation. But, I need the same with LESS THAN.
SELECT NON EMPTY { [Measures].[InventTrans Quantity] } ON COLUMNS,
NON EMPTY { ([Items].[Item Id].[Item Id].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DateIVFinancialCalendarYQMD, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( { [Inventory].[Is posted].&[Posted] } ) ON COLUMNS
[Code] ....
HELP... I’m new to ASP.NET so please
excuse my inexperienced question. I’m using VWD 2005 and a remote SQL 2000.In the (Configure Data Source) under the (Select) tab I have
this simple query:
Select * from calendarWhere event_date = now
Right off the that bat you can probably see the problem. I
just want to retrieve all event at is equal to today (current date). In classic
ASP is can use <%now%> and in ColdFusion I can use #now()#. What is the
proper syntax the get the server current date and time, like (getDate()) in SQL?
Thank you in advanced for you help!
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View 1 Replies View RelatedOK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
Hi,
I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount
I need to output the following report
Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2
1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?
thanks
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
I've got a cube with :
- A Training dimension
- An Employee dimension
- A fact table with each training session for each employee (including the date), with a Count measure and the associated fact dimension
Looking for syntax to add for each training session the date of the previous training session for the same employee ?
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITH
MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Code] ....
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.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of
SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated.
Many thanks,
James.
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
View 4 Replies View RelatedHi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
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 RelatedI can pull a last processed date for the entire model: URL...That works great for showing the last time anything in the entire model was processed. It is not table specific. Updating / processing table A also changes the timestamp on tables B and C.However, if I want to look at just a specific table in the model (build a column for each fact table and a measure to go with it) I find that doing any process operation updates all the =Now() columns in all the fact tables.If I have a model with 3 fact tables and I do a process table using ProcessFull on one of them, all three tables calculated columns "LastProcessed" =NOW() are updated. URL...
Type>ProcessFull</Type>
<
Object>
<
DatabaseID>MyModel</DatabaseID>
<
DimensionID>TableA</DimensionID>
</
[code]...
Is there a way to setup a measure in the model on a per table level to show last processed date for each individual table? LastDataRefresh:= MAX ('TableA'[LastProcessed])
For example: Our sales table may update three times a day, where as our warehouse inventory table is only updated nightly.I wanted to let end users see by adding a measure for each table when the last process event was for a given table in the model.
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 sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achieve this?
View 2 Replies View Related