topic Need assistance creating an MDX query in SAS Programming

Need assistance creating an MDX query

anonymous_user — Sun, 01 Mar 2015 16:18:44 GMT

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)
98800	2	01	61	Reclass from Occpy	000047539265	000000000000	GEN

Re: Need assistance creating an MDX query

anonymous_user — Sun, 01 Mar 2015 17:53:27 GMT

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

Re: Need assistance creating an MDX query

Reeza — Sun, 01 Mar 2015 18:17:47 GMT

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.

Re: Need assistance creating an MDX query

ChrisHemedinger — Sun, 01 Mar 2015 18:39:53 GMT

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