Grouping Multiple Dimensions?

We use Excel 2007 as our front end to our AS2005 cube. Is there a way to group/display multiple dimensions under a single area? I know that you can use Display folders with measures.

Are you wanting to have a folder displayed under which attributes from multiple dimensions are displayed, or are you just wanting attributes from multiple dimensions displayed on an axis of your pivot table?

B.

|||Hi Brian,

The former. I'd like to have a folder or section where attributes from multiple dimensions can be displayed in a group. For example, let's say I have a Products dimension with a Product Line attribute and a Ship Node dimension with a Ship Node Name attribute. I'd like to have the Product Line and Ship Node attributes under a grouping or displayed in a section called "xxx" so users would just navigate to "xxx" in the Field List to find the aforementioned attributes. Does that make sense? And is that possible in Excel 2007?
|||

When you pull data from an OLAP cube directly into Excel 2007, it gives the option of generating a Pivot Table or a Pivot Table & Chart. Either way, you get that field list on the side of the window that organizes everything the way it is organized in the cube.

I am not aware of an easy way to alter this. One thing that comes to mind is possibly connecting directly to the relational data warehouse that feeds to cube (but this would require you to by-pass SSAS security). But then, everything would be jumbled.

Another would be to have an SSRS report with a single table of the elements you want and then call the report's URL with rendering instructions for either CSV or XML (or just have the report generate an XLS and open that yourself) and then generating a pivot table off the Excel data set. Still, I can't really see going into production with the SSRS solutoin.

B.

|||

Hello! I do not think it is possible.

Actually I have created named sets with crossjoin of two separate dimensions like product and customer in AS2005 and the previous version. It is possible to build them on the server, but the problem is that no client I have seen, like ProClarity Professional, will show them(and support them). These sets(or attributes from different dimensions) are not supported in any client that I know about. They will not show up in dimension tools in clients.

Since I do not now about every client on the market I can be wrong.

HTH

Thomas Ivarsson

|||Thanks for the input Brian and Thomas! I'll give it to the rest of the day to see if I can come up with anything.

Group Headers Not Showing In Excel

Hi,
I have created some reports that I need to export to Excel. I have realised
that when they are exported, the group headers do not appear in Excel, just
the table header and footer, group footers and details group.
Does anyone know why the group headers are ommitted from the Excel files and
how I can get around this problem?
ThanksAre the group headers hidden, or are they missing completely?
"Tabby Cool via SQLMonster.com" wrote:
> Hi,
> I have created some reports that I need to export to Excel. I have realised
> that when they are exported, the group headers do not appear in Excel, just
> the table header and footer, group footers and details group.
> Does anyone know why the group headers are ommitted from the Excel files and
> how I can get around this problem?
> Thanks
>|||They are missing completely.
I hope they fix this in the 2005 version of RS!
daw wrote:
>Are the group headers hidden, or are they missing completely?
>> Hi,
>[quoted text clipped - 6 lines]
>> Thanks
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1

Group by Query Insert the Results into Different Table

I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

Here it is,

Code Snippet

--For One Time

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate into TargetTable from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

--For Multiple Times

Create table Targettable

(

Trip int,

Destination varchar(10),

LeaveDate datetime,

LeaveTime varchar(10),

ReturnDate datetime,

ReturnTime varchar(10),

Comment text,

RescheduleDate datetime

)

Insert into Targettable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||

Just create a table with fields that match those of your query. Then you can run

Code Snippet

INSERT INTO newTable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||Sweet Thanks!!!

GridLines problem when exporting a report to excel

1,
I have a report when I export it to excel it exported successfully.
But when I open the report the grid lines are gone.
I have to manually enable grid lines to view them.
How Can I export the file to excel such that grid line will be enable

2,Currently I am checking the export to excel via report manager.
What should be the url to export to excel.

Hello Kamii,

#1. Can you verify that your BorderColor is not set to White and your BorderStyle is set to Solid on your textboxes in your report?

#2. Here's the example MS had, but changed to Excel: http://servername/reportserver?/Sales/YearlySalesSummary&rs:Format=EXCEL&rs:Command=Render

This link explains the URL export. http://msdn2.microsoft.com/en-us/library/ms154040.aspx
Here's another link for Using URL Access Parameters: http://msdn2.microsoft.com/en-us/library/ms152835.aspx

Hope this helps.

Jarret

|||Thanks Jarret
I have one more question

3,
We have predefined excel tempaltes
I have a excel report with on main worksheet all data then on other work sheet data based on first work sheet with pivot table.
We were generating with old vb6 application.
Now I want's to use SSRS to generate report then export it to excel as my previous template.Can i do that ?

I know it is an odd idea by the client but we have to implement it
Can you please refer me from where i can find help regarding this ?
|||

I don't think this will be possible with Reporting Services. Here are a few posts from others that are wanting to use templates as well.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=519081&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=461709&SiteID=1

The second link above mentions a third party tool that looks like it will do what you need.

http://officewriter.softartisans.com/officewriter-250.aspx

Jarret

Gridlines in excel

We currently using reporting services to generate excel reports. I was unable
to switch on gridlines for my excel reports and reverted to the method where
I gave each cell a border to simulate gridlines.
These 'styles' prove to be problematic, especially with large spreadsheats.
I have a large 47MB spreadsheet which takes a few minutes to load. By
replacing the cell borders with gridlines and resizing the cells the size
drops to 36MB and it loads instantaniously.
How can I generate excel reports with gridlines 'switched on' by default.Not possible to enable grid lines from RS for excel, may be after excel is
created you can enable it by going to tools ->options->view click on check
box for gridlines. But I dont think you require like this to do that too each
and every users?
Amarnath, MCTS
"JRMalherbe" wrote:
> We currently using reporting services to generate excel reports. I was unable
> to switch on gridlines for my excel reports and reverted to the method where
> I gave each cell a border to simulate gridlines.
> These 'styles' prove to be problematic, especially with large spreadsheats.
> I have a large 47MB spreadsheet which takes a few minutes to load. By
> replacing the cell borders with gridlines and resizing the cells the size
> drops to 36MB and it loads instantaniously.
> How can I generate excel reports with gridlines 'switched on' by default.