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 |
Any chance someone can assist me with this? If not, is there a support desk or something to that effect that can contact?
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.