how to use a sql data source, and still use the cube.

[NewtonsCannon]

First, I'm new. very new. Second I can not change the cube and add this dimension.

I have a cube that has locations and regions.  What it does not have are the markets.

The goal is the add the Markets to the bottom of the report and have cognos roll up the totals for each market.

I have made an sql to get the store code, market.  I have tried to do stuff with it, but alas I am just a fish on a hot sunny rock.

Any thoughts on how I can get the markets into a dimension (probably wrong terminology) and have them joined/linked to the store codes?

Thanks, Eric

[blom0344]

If you make sure that both dimensional and relational data are published within 1 package you can use both sources within a RS report. Combining them is another matter.  You should extent the cube design to accomodate the market data.

[NewtonsCannon]

Thanks,

They are not in the same package sadly, and I do not have the functional authority to make changes to the package or the cube.  With that sadness being said, does any one have thoughts on how I can take the relational data from an sql/query object and use a field in it to make a subtotal line in the report. 

Or how to define a market dynamically using the SQL statement to load a variable with a set[] function to hold the locations that make up that market.

I'll continue my trial and error.

[blom0344]

You cannot use more than 1 package.. (period..) for a report. That is the bottom line AFAIK

[NewtonsCannon]

Thanks for the info.

[wyconian]

Hi Blom is right (as usual  :) ) you can only use one package but if you're using C10 you have an option to load external data. 

I haven't used this yet so can't give any advice on how good it is but maybe worth a try.  Maybe you could save the output from your SQL query as an excel fiel or csv etc then load it into the report.  It's the green database icon at the top of the insertable oibjects pane.

Good luck