hierarchical xml by DataSet()->WriteXml()

I'm playing with web-based front-end for hierarchical db in mssql2K.
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
gok
You should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok

hierarchical xml by DataSet()->WriteXml()

I'm playing with web-based front-end for hierarchical db in mssql2K.
So far my tables have 3 levels. I'm wonder how to store such hierarchical
trees outside (xml format).
DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
tree-like xml?
--
gokYou should look at either the FOR XML clause or the SQLXML 3.0 annotated
schemas.
Best regards
Michael
"gok" <gok@.discussions.microsoft.com> wrote in message
news:43FC6F1B-AE18-4C77-80EB-549712A95E2B@.microsoft.com...
> I'm playing with web-based front-end for hierarchical db in mssql2K.
> So far my tables have 3 levels. I'm wonder how to store such hierarchical
> trees outside (xml format).
> DataSet()->WriteXml() method uses "2D" or flat dataset. How to create
> tree-like xml?
> --
> gok

Hiding/Showing columns based on the columns present in the dataset

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that?

Any reply showing me the right way is appricited.

-Thanks,

Digs

In the Column properties, expand the Visibility section, and then click on Hidden, and choose Expression.

In the expression, use the same input parameters that you are using to build the column list in your SQL Statement to set the property.

An example expression might look like this:

=iif(Parameters!ShowColumn1.Value= True, false, true)

Where Parameters!ShowColumn1 is a report parameter that you have defined,

This should get you started...

BobP

|||

Hi,

Thanks for the reply..Need bit change =IIf(Fields!Collection.IsMissing=True,True,False) In Visibility -- > Hidden

-Thanks,

Digs

hiding table with null dataset

=iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
visibility expression of table properties. any ideas for using
expressions to hide a table with a null dataset would be great.
tia
mikeHi Mike,
In my reports when there is not data to display (null data set) I have a
textbox that is hidden until the NULL dataset is verified. They it shows "No
Data To Display For the Report Criteria" You can use an expression similar
to the following, just revers the True,False logic if you need to.
=iif(rownumber("DataSetName") = 0, false, true)
Hope this helps,
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"michaelwall" <michael.w.i@.gmail.com> wrote in message
news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> visibility expression of table properties. any ideas for using
> expressions to hide a table with a null dataset would be great.
> tia
> mike
>|||perfect ! thanks
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||I responded early - The expression hides the table whether the dataset is
emprty or not.
any further ideas? what do you mean by "until the NULL dataset is
verified" how is this done in the report designer, do i need custom code ? It
seems as if the visibility by expression should cover this but I find very
little documentation and no intellisense to experiment... thanks again ...
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||=iif(ReportItems!txtTotalDistribution.Value >= 1, FALSE, TRUE)
works by looking at an aggregate SUM() field in the table footer
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||Data regions (table, list, matrix, charts, subreports) have a NoRows
property. If set, the NoRows message will be shown instead of the data
region. In addition, style properties (fonts, colors, etc.) directly set on
the data region reportitem (e.g. table) will apply to the NoRows message.
Alternatively, you could use the RowCount aggregate functions to determine
the number of rows in a dataset. E.g. =RowCount("DataSetName")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"michaelwall" <michaelwall@.discussions.microsoft.com> wrote in message
news:4D53BEB6-D542-49B1-A8F5-A144990CB93C@.microsoft.com...
> perfect ! thanks
> "Rodney Landrum" wrote:
> > Hi Mike,
> > In my reports when there is not data to display (null data set) I have a
> > textbox that is hidden until the NULL dataset is verified. They it shows
"No
> > Data To Display For the Report Criteria" You can use an expression
similar
> > to the following, just revers the True,False logic if you need to.
> >
> > =iif(rownumber("DataSetName") = 0, false, true)
> >
> > Hope this helps,
> > --
> > Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> >
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> > http://www.apress.com/book/bookDisplay.html?bID=365
> >
> >
> >
> >
> > "michaelwall" <michael.w.i@.gmail.com> wrote in message
> > news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > > visibility expression of table properties. any ideas for using
> > > expressions to hide a table with a null dataset would be great.
> > > tia
> > >
> > > mike
> > >
> >
> >
> >

Hiding parameters

I have a report that has to show values in the Report Header section. This is
solved by creating parameters that gets the values from a dataset.
But I can't hide these parameters from the user. If I leave the promt field
empty it will become read only, but not hidden.
Is there a way of hiding these parameters?I beleive if you install SP1 for Reporting Services you can hide the header
(by not providing the prompt) but still be able to set the values...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"karl" <karl@.discussions.microsoft.com> wrote in message
news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
>I have a report that has to show values in the Report Header section. This
>is
> solved by creating parameters that gets the values from a dataset.
> But I can't hide these parameters from the user. If I leave the promt
> field
> empty it will become read only, but not hidden.
> Is there a way of hiding these parameters?|||Thanks.
But SP1 is already installed.
"Wayne Snyder" wrote:
> I beleive if you install SP1 for Reporting Services you can hide the header
> (by not providing the prompt) but still be able to set the values...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "karl" <karl@.discussions.microsoft.com> wrote in message
> news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> >I have a report that has to show values in the Report Header section. This
> >is
> > solved by creating parameters that gets the values from a dataset.
> >
> > But I can't hide these parameters from the user. If I leave the promt
> > field
> > empty it will become read only, but not hidden.
> >
> > Is there a way of hiding these parameters?
>
>|||Karl - did you set the prompt to blank via the report manager or the
designer? You have to do it via the designer. I don't trust using the report
manager to set properties.
If you do it via the report designer - just leave the prompt blank. I have
had great success using this method.
=-Chris
"karl" <karl@.discussions.microsoft.com> wrote in message
news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
>I have a report that has to show values in the Report Header section. This
>is
> solved by creating parameters that gets the values from a dataset.
> But I can't hide these parameters from the user. If I leave the promt
> field
> empty it will become read only, but not hidden.
> Is there a way of hiding these parameters?|||Thanks.
This is done in the Report Designer.
But since I now know that this should work, I'll just look around in my
solution to see if something is done wrong.
Thanks again.
Karl
"Christopher Conner" wrote:
> Karl - did you set the prompt to blank via the report manager or the
> designer? You have to do it via the designer. I don't trust using the report
> manager to set properties.
> If you do it via the report designer - just leave the prompt blank. I have
> had great success using this method.
> =-Chris
> "karl" <karl@.discussions.microsoft.com> wrote in message
> news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> >I have a report that has to show values in the Report Header section. This
> >is
> > solved by creating parameters that gets the values from a dataset.
> >
> > But I can't hide these parameters from the user. If I leave the promt
> > field
> > empty it will become read only, but not hidden.
> >
> > Is there a way of hiding these parameters?
>
>|||By the way...
I have the Report Server and SQL Server on two different servers.
Currently the SP1 is only installed on the Report Server (server).
Do I also need to install the SP1 on the server where SQL Server is located?
Karl
"karl" wrote:
> Thanks.
> This is done in the Report Designer.
> But since I now know that this should work, I'll just look around in my
> solution to see if something is done wrong.
> Thanks again.
>
> Karl
> "Christopher Conner" wrote:
> > Karl - did you set the prompt to blank via the report manager or the
> > designer? You have to do it via the designer. I don't trust using the report
> > manager to set properties.
> >
> > If you do it via the report designer - just leave the prompt blank. I have
> > had great success using this method.
> >
> > =-Chris
> >
> > "karl" <karl@.discussions.microsoft.com> wrote in message
> > news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> > >I have a report that has to show values in the Report Header section. This
> > >is
> > > solved by creating parameters that gets the values from a dataset.
> > >
> > > But I can't hide these parameters from the user. If I leave the promt
> > > field
> > > empty it will become read only, but not hidden.
> > >
> > > Is there a way of hiding these parameters?
> >
> >
> >|||The Report Server says Version 8.00.878.00.
And if I'm not wrong this is the correct number for SP1.
"karl" wrote:
> By the way...
> I have the Report Server and SQL Server on two different servers.
> Currently the SP1 is only installed on the Report Server (server).
> Do I also need to install the SP1 on the server where SQL Server is located?
>
> Karl
>
> "karl" wrote:
> > Thanks.
> >
> > This is done in the Report Designer.
> > But since I now know that this should work, I'll just look around in my
> > solution to see if something is done wrong.
> >
> > Thanks again.
> >
> >
> > Karl
> >
> > "Christopher Conner" wrote:
> >
> > > Karl - did you set the prompt to blank via the report manager or the
> > > designer? You have to do it via the designer. I don't trust using the report
> > > manager to set properties.
> > >
> > > If you do it via the report designer - just leave the prompt blank. I have
> > > had great success using this method.
> > >
> > > =-Chris
> > >
> > > "karl" <karl@.discussions.microsoft.com> wrote in message
> > > news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> > > >I have a report that has to show values in the Report Header section. This
> > > >is
> > > > solved by creating parameters that gets the values from a dataset.
> > > >
> > > > But I can't hide these parameters from the user. If I leave the promt
> > > > field
> > > > empty it will become read only, but not hidden.
> > > >
> > > > Is there a way of hiding these parameters?
> > >
> > >
> > >

Hiding Duplicate Records

Is it possible to output records in a dataset while not showing the
duplicates. The dataset that I am using returns records like the following.
title1; problem1; associateddata1
title1; problem1; associateddata2
title2; problem2; associateddata1
Even though first two rows do not look like duplicates, since they have the
same title, I only want to show title1 and problem1 once, then show
associateddata1 & 2 at the bottom of that group. Then go to another page and
show title2, problem2, and associateddata1. I tried using a table and various
grouping layouts with no success. Can someone help with this?Yes...
Right click on one of the Table cells in the layout and Click on "Dont
show Duplicates"|||Sorry...I should have mentioned that I tried that. Is it not working because
of the associatedata part that I said keeps it from looking like an exact
duplicate?
"sorcerdon@.gmail.com" wrote:
> Yes...
> Right click on one of the Table cells in the layout and Click on "Dont
> show Duplicates"
>|||Alright I just made a simple Table in AdventureSoft looking like this:
title1 problem1 data1
title1 problem1 data2
title2 problem2 data1
title2 problem2 data2
Do you want the following results:
title1 problem1 data1
data2
title2 problem2 data1
data2
Is this what you want?|||if this is what you want then you need to make 1 group. Group by the
title field.
Then rick click on the =Fields!title.Value field and the
=Fields!problem.Value field and say no duplicates.
Doing these together shows the desired results you see above this post.|||That is exactly what I was looking for. Thanks.
"sorcerdon@.gmail.com" wrote:
> if this is what you want then you need to make 1 group. Group by the
> title field.
> Then rick click on the =Fields!title.Value field and the
> =Fields!problem.Value field and say no duplicates.
> Doing these together shows the desired results you see above this post.
>|||Glad it helped.
Regards,
Sorcerdon|||I hope you are getting a reply on this. I tried you solution and at first
thought it worked, but later remembered that I had changed my query. So I
only thought it was working. I'm going to try attaching my query and showing
you exactly what is coming back and how I'm displaying it on a report, to see
if this helps. Course it seemed like you outlined it the same as I'm about
to, but just in case it helps. I appreciate any help you can provide. I
apologize in advance for this being so long...just want to make sure I don't
miss anything.
I have two records in the Problems table. Of the two records in the problems
table, one of the records has two associated data elements and the second
record has none attached. So the resultset would be similar to the following
(with a few rows left off for easy viewing):
Problem Table:
prob1ID; found big problem title 1; location Virginia; mike collins
prob2ID; found big problem title 2; location Maryland; mike collins
Associated Data:
Prob1ID; Data Item 1
Prob1ID; Data Item 2
Below query would return it as:
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 2; location Maryland; mike collins; data
item 2
Select t.ProblemID, t.Title, l.LocationName,
p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
Originator,
p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
Screener,
p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
SbjMtrExpert,
p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
TestDirector,
ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
From Problems t
Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
Join common..personnel p2 On p2.PersonnelID = t.FixedByID
Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
Join Locations l On t.LocationID = l.LocationID
Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
Now that is done, I am displaying everything you see the query returning on
the report using a stored procedure. I am using a table and adding many
detail rows so I can display the data looking like a form as opposed to just
rows. I tried setting the hide duplicate rows on the title and then on every
other field (with the exception of the associated data field) with no luck.
Any suggestions to what I am doing wrong?
"sorcerdon@.gmail.com" wrote:
> Glad it helped.
> Regards,
> Sorcerdon
>|||hi mike
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
"Mike Collins" wrote:
> I hope you are getting a reply on this. I tried you solution and at first
> thought it worked, but later remembered that I had changed my query. So I
> only thought it was working. I'm going to try attaching my query and showing
> you exactly what is coming back and how I'm displaying it on a report, to see
> if this helps. Course it seemed like you outlined it the same as I'm about
> to, but just in case it helps. I appreciate any help you can provide. I
> apologize in advance for this being so long...just want to make sure I don't
> miss anything.
> I have two records in the Problems table. Of the two records in the problems
> table, one of the records has two associated data elements and the second
> record has none attached. So the resultset would be similar to the following
> (with a few rows left off for easy viewing):
> Problem Table:
> prob1ID; found big problem title 1; location Virginia; mike collins
> prob2ID; found big problem title 2; location Maryland; mike collins
> Associated Data:
> Prob1ID; Data Item 1
> Prob1ID; Data Item 2
> Below query would return it as:
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 2; location Maryland; mike collins; data
> item 2
> Select t.ProblemID, t.Title, l.LocationName,
> p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
> Originator,
> p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
> p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
> Screener,
> p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
> SbjMtrExpert,
> p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
> TestDirector,
> ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
> DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
> SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
> From Problems t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.FixedByID
> Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
> Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
> Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
> Join Locations l On t.LocationID = l.LocationID
> Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
> Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
> Now that is done, I am displaying everything you see the query returning on
> the report using a stored procedure. I am using a table and adding many
> detail rows so I can display the data looking like a form as opposed to just
> rows. I tried setting the hide duplicate rows on the title and then on every
> other field (with the exception of the associated data field) with no luck.
> Any suggestions to what I am doing wrong?
> "sorcerdon@.gmail.com" wrote:
> > Glad it helped.
> >
> > Regards,
> >
> > Sorcerdon
> >
> >