Hierarchies: Any drawbacks doing it this way?

Hi all,
I have a requirement to store hierarchical data in a SQL database. I've
seen several different solutions posted here in the past on how to retrieve
hierarchical data.
In an effort to simplify the storage and retrieval of this data, I was
thinking of initally inserting the data into the table with some type of
"Level" or "Position" column which indicates the position in the hierarchy
of each item. I will need to insert and remove items in the hierarchy
somewhat frequently.
For an insertion into the hierarchy, if I know where the new item's position
should be, I could just insert the new item into the table with that
position number, then increment the item which previously had that
particular position and all the items below it by one. Removing an item
from the hierarchy would follow similar logic.
Are there any drawbacks to storing the hierarchical data this way? Is there
any reason for me to avoid this method of maintaining a "Position" column
for each item?
Any commenets are appreciated,
BenA "hierarchy level" column seems unlikely to be useful for subtree
maintenance in an Adjacency List model (I am assuming adjacency list
because you didn't specify what other model you might be using). In
fact it almost certainly hinders inserts because it is redundant data
that needs to be updated each time - probably on many rows for each row
inserted. Don't store the hierarchy level unless you regularly need to
query that information and have a good reason to denormalize.
For subtree maintenance, Materialized Path is typically the lowest cost
model. Nested Sets is cheap for pruning the tree but unpredictably
costly for grafting on subtrees.
David Portas
SQL Server MVP
--|||Check out this article from Joe Celko:
http://www.dbazine.com/ofinterest/oi-articles/celko24
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
> retrieve hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
> position should be, I could just insert the new item into the table with
> that position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
> there any reason for me to avoid this method of maintaining a "Position"
> column for each item?
> Any commenets are appreciated,
> Ben
>|||Hi David,
This is my first time dealing with hierarchies, so I'm not sure if the model
I'm working with is an Adjacency List model or not.
Below, I've included some DDL and insert statements on the type of data I'm
going to be storing -- the data below is for a single hierarchy tree I'll be
storing -- I'll actually be storing dozens of these trees, so I'll obviously
need another column in the table to identify the Hierarchy ID. There aren't
that many rows per hierarchy though, which is why I was thinking of storing
a "Position" or "Level" column.
When I retrieve the data from the table, the desired order of the data is
the same order of the INSERT statements below -- so "catOuter", "catOne",
"spnOne", etc.
I see myself having two or three options when retrieving the data:
(1) Create and maintain a "Position" column.
(2) Create a stored procedure or UDF which calls itself recursively to
retrieve the hierarchy in the desired order.
I suppose a 3rd option would be some set-based solution which is what I
usually try to do, but for hierarchies, I'm not sure how to implement a
set-based solution.
After seeing the sample data below, I'm interested in which option (1, 2 or
3) you think might work best?
Thanks again,
Ben
--
create table PageElements
( ElementID varchar(25) primary key,
ParentID varchar(25) )
insert into PageElements
(ElementID, ParentID)
select 'catOuter', NULL union all
select 'catOne', 'catOuter' union all
select 'spnOne', 'catOne' union all
select 'imgOne', 'catOne' union all
select 'catTwo', 'catOuter' union all
select 'spnTwo', 'catTwo' union all
select 'catThree', 'catOuter' union all
select 'spnThree', 'catThree' union all
select 'spnFour', 'catThree' union all
select 'spnFive', 'catThree' union all
select 'imgTwo', 'catThree' union all
select 'imgThree', 'catThree' union all
select 'spnSix', 'catThree' union all
select 'spnSeven', 'spnSix' union all
select 'spnEight', 'spnSix' union all
select 'spnNine', 'spnSix' union all
select 'spnTen', 'spnSix' union all
select 'spnEleven', 'spnSix' union all
select 'imgFour', 'spnSix'|||SriSamp wrote:

> Check out this article from Joe Celko:
> http://www.dbazine.com/ofinterest/oi-articles/celko24
Hi SriSamp,
Thank you for the helpful link! I'll take a look at it.
Ben|||Also buy the book TREES & HIERARCHIES IN SQL; it has more details and
other methods.|||If you want to store the items in order, you might want to look at a linked
list solution.
This way you only need to edit the item after the record you're inserting.
id parent
1 <null>
2 1
3 2
4 3
insert 5 between 2 & 3.
begin trans (serializable)
insert table values ( 5 , 2 )
update table set parent = 5 where parent = 2 and id != 5
commit trans
Then you end up with
id parent
1 <null>
2 1
5 2
3 5
4 3
HTH
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
retrieve
> hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
position
> should be, I could just insert the new item into the table with that
> position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
there
> any reason for me to avoid this method of maintaining a "Position" column
> for each item?
> Any commenets are appreciated,
> Ben
>

Hiding the report parameters when i click on the View report button

hi All,
i am working in SQL reporting services 2005, i have requirement that i
need to show all the report parameters in the report layout and user
can enter or select the values when i click on the view report button,
the report parameters should be hided. i am not sure that we could hide
the report parameters when i click on the view report button.
anyone knows how to implement this. please let me know.
Thank you in advance.
VinodAny idea on the below issue!!!!
thanks
Vinod
Vinod wrote:
> hi All,
>
> i am working in SQL reporting services 2005, i have requirement that i
> need to show all the report parameters in the report layout and user
> can enter or select the values when i click on the view report button,
> the report parameters should be hided. i am not sure that we could hide
> the report parameters when i click on the view report button.
> anyone knows how to implement this. please let me know.
> Thank you in advance.
> Vinod

Hiding Other options from Format Drop down in Report Viewer Control

Hi All
We have requirement where we need to hide some of the options which we have in Format drop down i.e we need only CSV,PDF and excel .
How can we do this?
Thanks in advance
Rehan Mustafa Khan
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.To disable export to a particular rendering format, add the string
Visible="false" to the end of the rendering extension entry in
%PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config file. For example, to disable
Excel from showing up in the Export dropdown in report manager, change
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
to
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering" Visible="false"/>
Note that disabling a rendering extension does not prevent users from
rendering to those extensions using SOAP API or use URL access to reports
like so:
http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
If you want to remove the rendering extension completely, just delete the
corresponding line from the <Render> section of the config file (in which
case URL access like shown above will throw rsRenderingExtensionNotFound
exception.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi All
> We have requirement where we need to hide some of the options which we
have in Format drop down i.e we need only CSV,PDF and excel .
> How can we do this?
> Thanks in advance
> Rehan Mustafa Khan
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Ravi,
I tried ur Suggestion.Not Sure Y its not working for me'
Can u Plz Guide where im going wrong'
My Code is
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" Visible="false"//>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering"Visible="false"/ >
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering" Visible="false"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"Visible="false"/>
<Extension Name="HTMLOWC"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.HtmlOWCRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
Im waiting for ur response,
Thanks in Advance,
"Ravi Mumulla (Microsoft)" wrote:
> To disable export to a particular rendering format, add the string
> Visible="false" to the end of the rendering extension entry in
> %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config file. For example, to disable
> Excel from showing up in the Export dropdown in report manager, change
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering"/>
> to
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering" Visible="false"/>
> Note that disabling a rendering extension does not prevent users from
> rendering to those extensions using SOAP API or use URL access to reports
> like so:
> http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
> If you want to remove the rendering extension completely, just delete the
> corresponding line from the <Render> section of the config file (in which
> case URL access like shown above will throw rsRenderingExtensionNotFound
> exception.)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> > Hi All
> >
> > We have requirement where we need to hide some of the options which we
> have in Format drop down i.e we need only CSV,PDF and excel .
> >
> > How can we do this?
> >
> > Thanks in advance
> >
> > Rehan Mustafa Khan
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
>
>