SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Help (Excel, SSRS, MDX)


Reporting Help (Excel, SSRS, MDX)

Author
Message
BogusGeorge
BogusGeorge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 62
I have built my first cube and I believe it is setup properly but I am having hard time wrapping my mind around how to report some data.

My cube is based on admissions to a university and I would like to build a report that compares applications to date for a specific term and compare it to another term based on the same date last year. Example, Dec 5, 2013|2014Fall and Dec, 5, 2012|2013Fall. The users should be able to pick the 2 terms and at least 1 date.

I have tried to build an MDX script to test this with hard-coded values but I am having problems getting the dates to work from essentially the beginning of time to the specified date and summarizing the data by term.

Ultimately, some end users will be getting this data in a canned report through SSRS and some will be using Excel to slice and dice the data. However, each instance requires this flexibility.

Am I going down the wrong path with the MDX script since I will be using Excel and SSRS? If yes, then how is the reporting requirement performed in Excel and SSRS?

Thanks
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1993 Visits: 2519
You could generate a calculation in your cube using ParallelPeriod. Then you can use your Date dimension to slice and dice the data as you need. A simple example would be
 
SUM(
PARALLELPERIOD([Date].[<Hierarchy>].[Year], 1, [Date].[<Hierarchy>].CURRENTMEMBER)
, [Measures].[<Your Measure>])
)



for previous year comparison at any level of your date hierarchy.

For a more comprehensive date based MDX solution, check out Marco Russo's DateTool dimension here and adapt to your needs.

Hope that helps :-)




I'm on LinkedIn
BogusGeorge
BogusGeorge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 62
Great help. I have played around with the code somewhat and came up with this...
WITH MEMBER [MEASURES].[COUNTAPPS] AS 
(SUM(
PARALLELPERIOD([Dim Date 1].[CALENDAR].[Calendar Year Number],1 , [Dim Date 1].[CALENDAR].CurrentMember)
, [Measures].[EVENT]) )
SELECT {
([Dim Start Term].[APPL START TERM].[2013AY]),
([Dim Start Term].[APPL START TERM].[2012AY])}*
{ [MEASURES].[COUNTAPPS]} ON 0
,{[Dim Application Category].[APPLICATION CATEGORY DESC].CHILDREN} ON 1
FROM FACT_APPLICATIONS
WHERE ([Dim Date 1].[CALENDAR].[Calendar Year Number].[2013])



Unfortunately, it does need some tweaking. Again, I am pretty new to MDX but I think what this is showing me is all of applications for the year versus the previous year. That's great and I will use this for a separate measure however, what I really need it to do is to compare each start term from the 'beginning of time' or at least 1960 which is how far back my date dimension goes to a choosen date. The reason I need to go back so far (probably not back to the 60's but at least 3 years) is that some applicant defer their application to 1 or 2 years out and those deferrals still get captured in the deferred term. In easy terms, I would to "all time" to date(preferably yyyyMMdd)

In saying that, what would I need to do to modify my script to reflect the requirements?
BogusGeorge
BogusGeorge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 62
I think I solved it!


WITH
MEMBER[Measures].[APPLICATIONS2013AY] AS SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2013-06-21 00:00:00.000]},[Measures].[EVENT])
MEMBER [Measures].[APPLICATIONS2012AY] AS SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2012-06-21 00:00:00.000]},[Measures].[EVENT])
SELECT
{([Dim Start Term].[APPL START TERM].[2013AY],[Measures].[APPLICATIONS2013AY]),
([Dim Start Term].[APPL START TERM].[2012AY],[Measures].[APPLICATIONS2012AY])}ON COLUMNS,

{[Dim Application Category].[PK APPLICATION CATEGORY].CHILDREN}*
{[Dim Application Category].[APPLICATION CATEGORY DESC].CHILDREN} ON ROWS
FROM FACT_APPLICATIONS




I have verified the numbers against the TSQL script and the numbers at least check out. If someone could critique the logic that would be great!

Eventually, I would like users to be able to be able to select the dates and terms for comparison via SSRS and or Excel. Any tips moving forward would be helpful.

Thanks.
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1993 Visits: 2519
Hi George,
I'm glad you figured it out - apologies, I had gone home for the weekend and I don't even like to think about this sort of thing at the weekend :-D

Your code will do it but I have to say I'm not a fan of those hard-coded dates. Perhaps consider adding an attribute to your date dimension (either in the SQL table or as a Calculated Member in the data source view) for something like TermStart as a boolean. You could then construct your MDX to be far more dynamic.

As you're new to MDX, I can think of no better guide to not only the language but the concept of MDX than the guide by Frank Banin on this very site: http://www.sqlservercentral.com/articles/MDX/91228/

Good luck :-)




I'm on LinkedIn
BogusGeorge
BogusGeorge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 62
Brilliant! Thanks.

The hard-coded dates are there just for testing. I am hoping to use the dates and start terms as parameters in SSRS reports.

However, I am intrigued by your suggestion. Are you suggesting that in my date dimension that I apply a start term to a date as well? If so, I can't really do that only because applicants can defer their application to the following year which would then leave me with the possibility of having 2 start terms associated to a date. If not, I am open to any enhances that will make my data more dynamic and easier to manage.

Thanks.
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1993 Visits: 2519
Ah ok, I didn't think of that. Perhaps if you created a measure group from your candidate data you could include a StartTermDateKey (or however you would name it) in the source which could then be used in calculations if you linked it to the Date dimension in the cube in a role playing context (possibly hidden if you're only using it for calculation).

Role Playing Dimensions:
http://technet.microsoft.com/en-us/library/ms174487%28v=sql.90%29.aspx




I'm on LinkedIn
BogusGeorge
BogusGeorge
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 62
I checked out you suggestion but I don't think it will work because even though the start term itself has a start and end date, the time period that applicants apply for it do not.

My problem now is how to create a calculated member such as

SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[2013-06-21 00:00:00.000]},[Measures].[EVENT])



into

SUM({NULL:[Dim Date 1].[CALENDAR].[full date].[@full date]},[Measures].[EVENT])



I can get the hard-coded version above to work after I deploy my cube and browse the data but I only get #value for the 2nd code option. The reason I am doing this is to prepare a report in Excel 2010 so that users can easily pick a date.
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1993 Visits: 2519
You'll only be able to use parameters if the calculation is part of a query (say, one that's being run from SSRS).

Depending on exactly what your parameter is passing the code would look something like this:

SUM({NULL:STRTOMEMBER("[Dim Date 1].[CALENDAR].[full date].&["+CSTR(@full date)+"]"},[Measures].[EVENT])



The above is assuming your parameter would be passing a date in the format of "2013-06-21 00:00:00.000".

I hope that helps.




I'm on LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search