Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reporting Help (Excel, SSRS, MDX) Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:07 AM
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
Post #1520230
Posted Friday, December 6, 2013 3:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1520476
Posted Friday, December 6, 2013 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:07 AM
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?
Post #1520641
Posted Friday, December 6, 2013 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:07 AM
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.
Post #1520711
Posted Monday, December 9, 2013 2:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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

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
Post #1520953
Posted Monday, December 9, 2013 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:07 AM
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.

Post #1521185
Posted Tuesday, December 10, 2013 3:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1521441
Posted Wednesday, December 11, 2013 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:07 AM
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.

Post #1521951
Posted Thursday, December 12, 2013 2:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1522210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse