Menu System

Hi,
Has anybody come across a good menu system for MSRS? We could write our own
but are perfectly willing to look at a packaged product. Thanks for your
help.
CBHere is the code for a simple, yet customizable, dotnet web form treeview
style menu. Paste it into notepad and give it an aspx extension, copy it to
your web server and run it from your browser.
A couple of things first:
1. you need to create a reporting services web proxy and reference it by its
namespace. Change the namespace in the line of code {<%@. import
Namespace="Your RS Proxy Name" %>} below.
2. The line in the code specifying the web server for a report path
{newNode.NavigateURL = "http://Your Report Server/reportserver?" & ci.Path}
has to be changed to point to your web server where reporting services is
installed and the name of the installation. If you took the defaults during
the installation it should be http://Your Report Server/reportserver?
3. you have to download and install the MS web controls. They can be gotten
for free at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/aspnet-usingtreeviewiewebcontrol.asp
The site has instructions for installing and using the web controls, plus
there are plenty of threads about them on the google news groups pertaining
to customization, look and feel, etc...
4. Finally, this was create in web matrix, another free download from MS.
The difference between web matrix and VS (aside from a lot of funtionality)
is web matrix doesn't use code-behind, it simply places the code between
script tags. But then again, you can use notepad too. Web matrix can be
gotten at http://www.asp.net/WebMatrix/
good luck!
************************* CODE BEGINS
HERE***********************************
<%@. Page Language="VB" Debug="true" AspCompat="true" %>
<%@. Register TagPrefix="myTree" Namespace="Microsoft.Web.UI.WebControls"
Assembly="Microsoft.Web.UI.WebControls" %>
<%@. import Namespace="System" %>
<%@. import Namespace="System.IO" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Web.Services" %>
<%@. import Namespace="System.Web.Services.Protocols" %>
<%@. import Namespace="Your RS Proxy Name" %>
<%@. import Namespace="System.Text" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
if not ispostback then
PopulateMenu()
end if
end sub
sub doThis(Sender As Object, E as
Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs)
dim currentNode as
TreeNode=CatalogTreeView.GetNodeFromIndex(e.NewNode)
end sub
sub AddChildNodes(currentNode as TreeNode)
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
currentNode.Expanded = true
Dim items As CatalogItem() = Nothing
' Retrieve a list of all items from the report server
database.
if currentNode.Type="Folder" then
items = rs.ListChildren(currentNode.ID, false)
end if
if currentNode.Type="Folder" then
dim ci as CatalogItem
for each ci in items
dim type as ItemTypeEnum= ci.Type
if ci.Type.ToString()="Report" then
dim newNode as TreeNode= new TreeNode()
newNode.Text = ci.Name
newNode.ID = ci.Path
newNode.Type = type.ToString()
newNode.HoverStyle.CssText
="font-size:8pt;color:dimgray;font-family:MS Sans Serif;font-weight:bold;"
newNode.DEFAULTSTYLE.CssText="font:MS Sans Serif;
color:Indigo; background:white;font-size:8pt"
newNode.NavigateURL = "http://Your Report
Server/reportserver?" & ci.Path
newNode.Expandable = ExpandableValue.Auto
currentNode.Nodes.Add(newNode)
else 'if it is a folder
dim newNode as TreeNode= new TreeNode()
newNode.Text = ci.Name
newNode.ID = ci.Path
newNode.Type = ItemTypeEnum.Folder.ToString()
newNode.Expanded = false
newNode.HoverStyle.CssText
="font-size:8pt;color:dimgray;font-family:MS Sans Serif;font-weight:bold;"
newNode.DEFAULTSTYLE.CssText="font:MS Sans Serif;
color:Indigo; background:white;font-size:8pt"
newNode.Expandable = ExpandableValue.CheckOnce
currentNode.Nodes.Add(newNode)
end if
next ci
end if
impersonationContext.Undo()
end sub
'Then for the Expand event, something like:
sub CatalogTreeView_Expand(Sender As Object, E as
Microsoft.Web.UI.WebControls.TreeViewClickEventArgs)
dim node as TreeNode=CatalogTreeView.GetNodeFromIndex(e.Node)
' Add the children of the node
node.Nodes.Clear()
AddChildNodes(node)
end sub
sub PopulateMenu()
Dim impersonationContext As
System.Security.Principal.WindowsImpersonationContext
Dim currentWindowsIdentity As
System.Security.Principal.WindowsIdentity
currentWindowsIdentity = CType(User.Identity,
System.Security.Principal.WindowsIdentity)
impersonationContext = currentWindowsIdentity.Impersonate()
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim items As CatalogItem() = Nothing
items = rs.ListChildren("/", true)
dim ci as CatalogItem
for each ci in items
if ci.Type.ToString()="Folder" then
dim root as TreeNode= new TreeNode()
root.Text = ci.Name
root.ID = ci.Path
root.Expanded = false
root.Type = ItemTypeEnum.Folder.ToString()
root.Expandable = ExpandableValue.CheckOnce
root.SelectedStyle.CssText="background:white;color:Indigo;font-name:Arial;fo
nt-weight:bold;font-size:8pt;"
root.HoverStyle.CssText
="font-size:8pt;color:indigo;font-family:MS Sans Serif;font-weight:bold;"
root.DEFAULTSTYLE.CssText="font:arial; color:DimGray;
background:white;font-size:8pt"
root.Expandable = ExpandableValue.CheckOnce
CatalogTreeView.Nodes.Add(root)
end if
next ci
impersonationContext.Undo()
end sub
</script>
<html>
<head>
</head>
<body>
<form font-size="XX-Small" font-names="MS Sans Serif" runat="server">
<MYTREE:TREEVIEW id="CatalogTreeView" runat="server"
onselectedindexchange="doThis" onexpand="CatalogTreeView_Expand"
autopostback="true" SHOWLINES="false" SHOWPLUS="true" SelectExpands="true"
DEFAULTSTYLE="font:arial;color:DimGray;background:white;font-size:8pt;text-d
ecoration:underline;"></MYTREE:TREEVIEW>
</form>
</body>
</html>
"Chuck Burns" <chuckb@.180solutions.com> wrote in message
news:%239fNrp1bEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Has anybody come across a good menu system for MSRS? We could write our
own
> but are perfectly willing to look at a packaged product. Thanks for your
> help.
> CB
>

Menu Hierarchy Display Order (Recursion)

Hi -
I'm using the following Table and Stored Procedure below, to store and
access a menu hierarchy. The recursive stored procedure is working
perfectly and returns a text string outlining my current menu hierarchy.
Currently the stored procedure returns the hiearchy in the order that the
records were created (MenuItemID)
However, I've recently added a "DisplayOrder" column to my table. For
example, let's say that I want Biking to appear before Skiing. Changing the
item's MenuItemID is not an option. So, I'm using a DisplayOrder column. I
need to integrate the DisplayOrder column into my stored procedure so that
it returns the hierarchy in the correct order. I'm not sure what I'm doing
wrong, but I just can't seem to get this part working.
Any help would be appreciated.
Thanks,
Brian
CREATE TABLE MenuItem
(
MenuItemID INT,
ItemName VARCHAR(30),
ParentID INT,
DisplayOrder INT
)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(1, 'Activities', NULL, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(2, 'Fishing', 1, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(3, 'Skiing', 1, 1)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(4, 'Hiking', 1, 2)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(5, 'Biking', 1, 3)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(6, 'Swimming', 1, 4)
CREATE PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT
SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID)
WHILE @.MenuItemID IS NOT NULL
BEGIN
EXEC spCreateMenu @.MenuItemID
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND MenuItemID > @.MenuItemID)
END
END
EXEC spCreateMenu 1Hi Brian,
Try this.. Hope this helps.
ALTER PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT,
@.rowcount int
SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
WHILE (@.rowcount <> 0)
BEGIN
EXEC spCreateMenu @.MenuItemID
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
END
END|||Excellent - Thanks very much for your help!!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B9AE2855-8675-4E07-BF3D-52A2BF892335@.microsoft.com...
> Hi Brian,
> Try this.. Hope this helps.
> ALTER PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT,
> @.rowcount int
> SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> WHILE (@.rowcount <> 0)
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> END
> END|||Look up CTEs (common table expressions). They handle recursive logic
without actually having a recursive stored procedure. One SQL statement
will do it for you.
"Brian Patrick" <bpatrick100@.hotmail.com> wrote in message
news:OUFoLJKaGHA.3652@.TK2MSFTNGP03.phx.gbl...
> Hi -
> I'm using the following Table and Stored Procedure below, to store and
> access a menu hierarchy. The recursive stored procedure is working
> perfectly and returns a text string outlining my current menu hierarchy.
> Currently the stored procedure returns the hiearchy in the order that the
> records were created (MenuItemID)
> However, I've recently added a "DisplayOrder" column to my table. For
> example, let's say that I want Biking to appear before Skiing. Changing
the
> item's MenuItemID is not an option. So, I'm using a DisplayOrder column.
I
> need to integrate the DisplayOrder column into my stored procedure so that
> it returns the hierarchy in the correct order. I'm not sure what I'm
doing
> wrong, but I just can't seem to get this part working.
> Any help would be appreciated.
> Thanks,
> Brian
>
> CREATE TABLE MenuItem
> (
> MenuItemID INT,
> ItemName VARCHAR(30),
> ParentID INT,
> DisplayOrder INT
> )
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (1, 'Activities', NULL, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (2, 'Fishing', 1, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (3, 'Skiing', 1, 1)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (4, 'Hiking', 1, 2)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (5, 'Biking', 1, 3)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (6, 'Swimming', 1, 4)
>
> CREATE PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT
> SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID
FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID)
> WHILE @.MenuItemID IS NOT NULL
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND MenuItemID > @.MenuItemID)
> END
> END
>
> EXEC spCreateMenu 1
>