Mdx/analysis/reporting Service For Date Calculation
Apr 8, 2008
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
View 2 Replies
ADVERTISEMENT
Nov 24, 2015
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.
View 2 Replies
View Related
Mar 31, 2006
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
View 5 Replies
View Related
Sep 17, 2015
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
View 2 Replies
View Related
Nov 22, 2007
When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?
example : Report name : Testreport
Exported file should be : Testingreport-November-22-2007.pdf
please help me in this
Thanks
suku
View 2 Replies
View Related
Nov 22, 2007
When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?
example : Report name : Testreport
Exported file should be : Testreport-November-22-20076.pdf
please help me in this
Thanks
suku
View 4 Replies
View Related
May 28, 2015
I have a fact table fct_line_details having two columns mtid, productivity
mtid productivity
1 400
1 200
1 600
2 700
3 900
I want to calculate the median for each mtid in SSAS . (median for mtid 1=400 )
View 2 Replies
View Related
May 30, 2006
Hi there,
I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.
My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.
My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.
Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005
Regards,
View 3 Replies
View Related
Aug 3, 2015
I have a set of data and calculated already the average of a specific measure, incl. the Standarddeviation of this measure. Now I want to create a average measure of the values on row level which arehigher than [Avg]-[StdDev] AND lower of [Avg]+[StDev].As far as I understand I have to check on Rowlevel, which means on Date and Brand Level, if the value of the measure is above or lower than the Mean +/- StdDev.
SUM( FILTER( {[Dim 1].[Attribue].&[selected]},[Measures].[Fact Survey Count] > [Measures].[Mean + 0,5 StDev] AND [Measures].[Fact Survey Count] < [Measures].[Mean - 0,5 StDev] ),[Measures].[Fact Survey Count])
which leads to a wrong result. This one counts all rows if true.With Descendants I got a #Value-Error, probably because I did not understand how the calculations work. I planned to include this in a IIF calculation.
SUM( Descendants(
{[Dim 1].[Attribute].&[selected],
[Dim Date].[Date].[Date].CURRENTMEMBER}, Leaves), [Measures].[Fact Survey Count])
View 4 Replies
View Related
Oct 8, 2015
Need to resolve this calculation, which I would believe is something very common on SSAS environments.
Like many companies, my company has different ways of calculating Sales and the two I want to focus are Sales Gross and Sales Net.
At a high level, we calculate Sales Gross as Sales with returns, and Sales Net as Sales without returns.
We have an attribute called Order Type that has various types of orders a user can execute with my company. One of them is Returns. If you return something back to us, we record that as a return line on the sales table. With that, we can calculate that return, breaking data down by Order Type, such as:
Order Type Line Total
Mail Orders $ 776,655.44
Internet Orders $ 2,211,334.00
Call Center Orders $ 11,223,344.00
Credit Orders $ (55,666.00)
Today, to calculate Sales Gross and Sales Net, we are creating two dimensions: DimSalesGross and DimSalesNet.
To calculate Sales Gross, we leave the data at the natural state, not making any changes to mappings.
To calculate Sales Net, we map Credit Orders to Call Center Orders at the ETL level, getting a Net value for sales (Orders - Returns), however, I doubt this is the correct way of doing.
I would like to have a Line Total Net / Line Total Gross calculation, which would be based on the Order Type value.
Perhaps using a CASE statement in MDX? Is the above possible?
View 4 Replies
View Related
Jul 8, 2015
I'm trying to calculate the average number of sick days per person so that it can be broken down by person role or department or some other dimension. I have a calculation for sick days that works ok and is [measures].[sick days.I'm trying to create another calculation that is [total staff] but it doesn't work.
My final calculation will be [avg sick days] = [measures].[sick days] / [measures].[total staff]
The bit I can't get to work is making it ignore any filters. For example I have a measure called [staff] and as you would expect it can be broken down by [person].[role] or [department].[department].
I'm trying to get [total staff] to return the total when it's used with [person] or [department]. I've managed to get it to work with [person] by using ROOT([Person]) but then I will need to do this for all different hierarchies that will use it.
View 12 Replies
View Related
May 30, 2015
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies
View Related
Jun 29, 2015
I have a calculation that granularity is on a specific level and therefore would like the calculation to be only visible/calculate when with a specific dimension structure & attributes
SCOPE([Measures].[Complaint Rate]);
SCOPE([Item Dimension].[Item Structure].Members);
this=(IIF([Measures].[Sales Units]=0,NULL,(([Measures].[Count]/[Measures].[Sales Units])*1000000)));
END SCOPE;
END SCOPE;
This displays the calculation all the time even if no item attributes are selected, I only want the calculation applicable to the structure & attributes belonging to structure - I tried children, currentmember etc. but no luck - its probably something small I am missing. Tried this too
this=(IIF([Measures].[Sales Units]=0,NULL,(([Item Dimension].[Item Structure].CURRENTMEMBER,[Measures].[Count]/[Item Dimension].[Item Structure].CURRENTMEMBER,[Measures].[Sales Units])*1000000)));
View 4 Replies
View Related
Aug 6, 2015
I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies
View Related
Nov 30, 2015
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
View 4 Replies
View Related
Oct 30, 2015
Does any1 know why preview of a DAX calculation does not work? I mean once you enter the calculation in the bottom of the table it doesnt´t show up.
View 8 Replies
View Related
Jul 2, 2015
I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )
= Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );
[Code] ....
However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.
View 2 Replies
View Related
Oct 27, 2015
I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?
Calculated Member:
CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
VISIBLE = 1;
It seems that Total is calculated without checking value in "AverageScore" for that month.
View 3 Replies
View Related
Sep 9, 2015
I have been following the tutorial/blog post HERE to create an annualization (or "run rate") of my Gross Amount measure. What I want to do now is exclude any period that is not "complete".
For example, if today is 9/9 then Q3 is not complete - only Q1 and Q2 are complete. And if I'm looking at it monthly then January through August are complete, but September is not.
Cells B5 and D5 look exactly as I expect and want them to be. On row 6 below under each "Gross Annualized" value I have basically just put the formula for what it's actually doing.
What I would like to see in the blue cell is $67,211,697,268 - essentially the most recent annualization for a completed period. The annualization for Q3 is misleading because the quarter has not yet completed. There's $16b in Q1 and $17b in Q2, so the measly $78m in Q3 (yellow cell) is dragging the annualization down significantly. Even worse, the Gross Amount for Q4 is being treated as a $0.00, which is further dragging down the "2015 Gross Annualized" amount in blue. In a T-SQL average calculation, for example, I think the green cell would be treated as NULL rather than $0. That's kind of the behavior I want.
So I would like to do two things:
Create a calculation (probably just a 0 or 1 flag) that indicates whether the current period is complete or not. Again, using 9/9/2015 as an example, Q2 would be complete but Q3 would not be. And August would be complete, but September would not. Make the blue cell show $67,211,697,268 - an annualization based on completed quarters only.
For whatever it's worth here is the current calculation for Gross Annualized.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross YTD]
AS AGGREGATE(
YTD([Pay Date].[Calendar].CurrentMember)
,[Measures].[Gross Amount]),
FORMAT_STRING = "Currency",
VISIBLE = 0;
[Code] .....
View 2 Replies
View Related
May 16, 2008
i have a list report
some values of this are blank or null
i want to avoid that spacing if it is null
like
name:jjj
lastname kkk
firstname
address uuuu
i want it should be
name jjj
lastname kkk
address uuuu
View 3 Replies
View Related
Sep 14, 2015
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.
Level 1 Level 2
A
X1
X2
=================
(Avg of X1 + X2)
B
Y1
Y2
Y3
=================
(Avg of Y1 + Y2 + Y3)
===================================================
(Sum of (Avg of X1 + X2) + (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like that
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
View 4 Replies
View Related
Oct 12, 2004
I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
View 4 Replies
View Related
Mar 14, 2001
hello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks
View 1 Replies
View Related
Nov 12, 1999
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
View 1 Replies
View Related
Jan 10, 2006
I need to use the first day of previous month, can anybody help me with that please?
Thanks in advance!!
View 6 Replies
View Related
May 20, 2008
I want all dates from DATEADD(DAY,-90,GETDATE()).
1/1/2008
1/2/2008
1/3/2008 .....
and so on. This is probably easy, but I can't figure it out. Any help would be appreciated. Thanks.
View 11 Replies
View Related
Feb 4, 2008
I have an issue with a query.
How to calculate the last day of a previous month
that is for example
2007-09-30 23:59:59 is passed then how to calculate last date of a previous month as 2007-08-31 23:59:59
OR 2007-10-31 23:59:59 as 2007-09-30 23:59:59
Thanks for your Help.
Sri
View 3 Replies
View Related
Aug 23, 2005
I have a problem with date/time:
1. I have data something like this:
start 07.30
end 16.00
How can i count how many hours and minutes from start to end?
2. Another data
start: 20050805 -> August 5, 2005
end: 20050810
How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end
thx
View 1 Replies
View Related
Jul 25, 2007
Hello,
if a customer DOB is 1933-06-30 and a date of service is on 2007-06-21
that makes them 73 and not 74. How do I calculate an accurate years of age?
Thanks in advance.
View 7 Replies
View Related
Jan 8, 2008
Hi,
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0
View 3 Replies
View Related
Jan 11, 2007
I have a table that hold a couple of fields.
STARTDATE - The day the service starts
FREQTYPE - This field tells me how often a schedule occurs. Here are the vaules -1=oncall, 0=days, 1=weeks, 2=months
FREQPERIOD - This is the number of days, weeks, or months between services. a value of -1 would indicate being oncall.
DayOfWeek, Day of week for week or month-based frequencies 0=sunday 6=saturday -1=if on call
WEEKOFMONTH - week of month for month based frequencies 1-4, value of 5 if it the last week of the month,
So what I need is for this report to look at the start date then figure out what the frequency is (days, weeks, months) then look at the frequency period (how many days are between services) what day of the week it falls on and list each service date on a report. There is an example at the bottom
I have tried numerous things and I can't get it and I need this asap today. I can provide what ever anybody needs, Many thanks.
Here is an example of the output that I need.
Service Frequency Every 12 weeks or 84 Days
Pickup Starting Point 1/4/2007
Pickup Schedule
3/29/2007
6/21/2007
9/13/2007
12/6/2007
2/28/2008
5/22/2008
8/14/2008
11/6/2008
1/29/2009
4/23/2009
7/16/2009
10/8/2009
12/31/2009
Here is sample data with the relationships
http://www.balzoutonline.com/reporting/database.jpg
Here is the service table schema
http://www.balzoutonline.com/reporting/service.jpg
Here is the serviceinfo table schema
http://www.balzoutonline.com/reporting/serviceinfo.jpg
Here is the serviceschedule table schema
http://www.balzoutonline.com/report...iceschedule.jpg
And here is the site table schema
http://www.balzoutonline.com/reporting/site.pdf
Here is something that was created in excel wich is pretty easy but a manual process. This is what I would like to replicate in crystal
http://www.balzoutonline.com/reporting/Schedule.xls
View 1 Replies
View Related
May 29, 2008
Hi -
I created a Year To Date Calculation via the Buisness Intellligence Wizard. The Script is as follows:
( [Acctg Date].[Acctg Time Acctg Date Calculations].[Year to Date],
[Acctg Date].[AcctgYear].[AcctgYear].Members ) =
Aggregate(
{ [Acctg Date].[Acctg Time Acctg Date Calculations].DefaultMember } *
PeriodsToDate(
[Acctg Date].[Acctg Time].[AcctgYear],
[Acctg Date].[Acctg Time].CurrentMember
)
)
Now I'd like to edit it so that it produces "Inception To Date" to date ("Inception To Date" picks up all amounts from the beginning which in my case is 2002, as opposed to Year To Date which is from the beginning of the year).
To change to Inception To Date can I change the PeriodsToDate function to:
PeriodsToDate(
[Acctg Date].[Acctg Time].[2002],
[Acctg Date].[Acctg Time].CurrentMember
)
By the way, I'm very new to SSAS so the code is foreign somewhat foreign at this point.
Thanks,
Mark
View 2 Replies
View Related
Apr 3, 2008
I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).
--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))
when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.
Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).
Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.
View 8 Replies
View Related
