I 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 RepliesI'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 want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
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 have a table with 5000 rows history about working and retired time.
It is the several rows from table(User, StartDate, EndDate):
User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL
How many users have worked and have retired by years and months,
Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,
Does it need to join to a time dimension table?
Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
-------------------------------------------------------------------
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx
I have a fact table which lists the last 12 month historical sales of a product,country,month combination so the table structure is like:
<month, productID, countryID, (month-3 sales), (month-4 sales).........,(month-14 sales)>
I need to write an MDX query which will return a flag that indicates if a product has a gap of more than 2 consecutive months in terms of positive sales. So I calculate if there is a gap of 3 months. If yes, then return 1 else null. The calculation below is scoped at product level.
IIF(([month-3 sales]<=0 AND [month-4 sales]<=0 AND [month-5 sales]<=0)
OR ([month-4 sales]<=0 AND [month-5 sales]<=0 AND [month-6 sales]<=0)
OR ([month-5 sales]<=0 AND [month-6 sales]<=0 AND [month-7 sales]<=0)
...............
OR ([month-12 sales]<=0 AND [month-13 sales]<=0 AND [month-14 sales]<=0)),1,NULL)
Unfortunately I cannot do this calculation at relational level as a product may have a gap of more than 2 months at country level but may not have this at a higher level in geography.
optimizing this as the performance is bad and other calculated measures are dependent on this. Based on this calculation, I can write a scope which sums up all products so that I get the count of products with a gap of more than 2 months in sales.
I have also tried CASE statement and NESTED IIFs but the performance is worse than the above.
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
I'm trying to generate a report using matrix like this
Month
Product PreviousYearSalesAmount SelectedYearSalesAmount %SalesDifference
I can populate year sales amount, but i cant calculate the percentage.
Note: Month and Year are passed as parameters.
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
I have sales data in SSAS cube and mapping data in RDBMS table. I want to apply join to get result into SSRS report.
Here we should get data of yesterday from time dimension of cube.
Time is in [Time].[FiscalYearHierarchy].[Fiscal Day].&[2015-05-28T00:00:00] format.
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250
I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.
CustLoc
A35
B15
If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
Developing a measure which displays the difference of two values from the selected years.
An example : Show the difference of the sales amount from 2013 and 2015.
Since i am not really into mdx or calculated members.
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies View RelatedHi,
I have 2 dimensions Time and Product....
If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:
Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):
Total Sales YTD Sales
----------- ----------
$ 50,000 $ 125,000
Again, I'll appreciate your help in writing the MDX for this.
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 am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
I have a sales order table that has customer, sales, product and sales date. I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer
Product
Sales Date
Sales
First Bike Date
Jon
Bike
12/4/2011
$10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Can I use function to complete? I have a datedim table as well related to the sales date.
I want to display the data in a datasheetview in ms access project.
The data is between particular date. What command should i used for this?
My form contailn feilds : UserId,FromDate,ToDate
Enter the userid & Particular date & after click on submit data for that period should display in datasheet view.
Please help me in this as soon as possible.
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
I have date hierarchy with year - qtr- month - date. from the below query, if i have a date or month or year on rows, i want to derive the top member in the hierarchy that is year. I f i have date on rows,
[Date].[Calendar Hierarchy].[Date].&[20150106],I should get [Date].[Calendar Hierarchy].[year].&[2015].
How to find the parent?
with member [dt7] as Drillupmember([Date].[Calendar Hierarchy].currentmember,
[Date].[Calendar Hierarchy].[Calendar Year])
select {[dt7]} on 0,
([Date].[Calendar Hierarchy].[Month].&[201501]) on 1
from Cube
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output
id Startdate
1 2014-10-01
2 2014-01-01
Where to find history of cube backup? I am new to analysis services.
View 4 Replies View RelatedAttached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
View 9 Replies View RelatedI provide default values for parameters, but we need to wait for the user to click "View Report" before the report is run. It is an expensive report to run, and the user needs the defaults, but also needs to be able to change them before execution. I cannot find a setting for this. Is there a way to do this that does not involve adding dummy parameters? (that's too tacky a solution!)
View 4 Replies View RelatedI have a simple gridview that loads on page load. It uses an on page sqldatasource declaration in which there's a parameter in which value is already available in cookies. I added an asp:HiddenField and set that value on PageLoad() to the value of the cookies. I then set a FormParameter in the sqldatasource mapped to that hidden field. However that appears to have no effect at all. I'm guessing the sqldatasource will only use the form field after postback.
View 2 Replies View RelatedHi guys
What's this nonsense all about?
Im attempting to install the Analysis Services on NT 4 and I get this strange error message (about not finding the TEMP environmental variable).
I checked the TEMP environmental variables they are just fine thank you very much.
Did anyone encounter this thing?
Thanks.