Need assistance creating an MDX query

Reply
New Contributor
Posts: 2

Need assistance creating an MDX query

Hi - I was wondering if I could get some assistance with a MDX script used to generate a file for loading to the General Ledger system, and have hit a snag.

The format of the file is as follows and will have 8 columns when all is said and done.  All of the information has to be left justified.

Column 1 - the account member name

Column 2 - a dummy column with a value of 2

Column 3 - the first three characters truncated so the end result is 01

Column 4 - the department member name

Column 5 - the alias of the account in column 1

Column 6 - a 12 character field with the value padded with zeroes

Column 7 - a 12 character field with the value padded with zeroes

Column 8 - a dummy column with a value of Gen

This is the code that I have come up with so far...

SELECT {[time periods].Children} ON COLUMNS,
{[S1_department].Levels(0).members} ON AXIS(1),
{[S1_Office].Levels(0).members} ON AXIS(2),
{[allocations].Levels(0).members} ON AXIS(3)

from [HPCMsR].[HPCMsR];

Below is the expected output

Acct code (12)Ledger(4)Office(4)Dept(4)Text(30)Debit(12)Credit(12)Prof(4)
9880020161Reclass from Occpy000047539265000000000000GEN
New Contributor
Posts: 2

Re: Need assistance creating an MDX query

Posted in reply to aberke824

Any chance someone can assist me with this?  If not, is there a support desk or something to that effect that can contact?

Super User
Posts: 19,832

Re: Need assistance creating an MDX query

Posted in reply to aberke824

At the bottom of the page, in the footer, is a link to tech support (submit a problem report).

It may also help if you post this in an appropriate forum, right now its in the general Support Communities.

Community Manager
Posts: 2,955

Re: Need assistance creating an MDX query

Posted in reply to aberke824

Do you have SAS Enterprise Guide?  If so, you may be able to use the OLAP Analyzer to design your view -- if your data source is from a SAS OLAP Server or from Microsoft Analysis Server (SQL Server).

You can use the OLAP Analyzer in SAS Enterprise Guide and no additional license is needed.  Simply define the data source as a "Private OLAP Server" in the File->Open OLAP Cube window.  Then specify the server host and credentials.  The connection uses OLE DB for OLAP (ODBO) -- a Microsoft data access standard for cubes. 

The OLAP Analyzer is quite powerful, and as you navigate the views you want you can always view the generated MDX code.

Chris

Ask a Question
Discussion stats
  • 3 replies
  • 207 views
  • 0 likes
  • 3 in conversation