how to track object dependencies (view, function). granularity - c

There is a way in management studio 2005 to get dependencies on a databse
object, to reproduce what it does is not a big deal.
This report provides object names thwe view or function depends on for me
ust fine.
For darta warehouse analysis I need to track down dependencies of a
particular column / all collumns in the view. And the existing oviews are big
time ugly, pages of code (perform not any nicer either).
SQL parses a view and builds all the dependencies, but is there a way to get
ahold of that information somehow?
I mean I have quite a bit of ugly looking views, and the idea of doung it by
hand does not look good, it is one nasty job to be honest.
I looking for means to automat that e, create a report aout column-column
dependencies of a view/function.
Did anyone here do anything similar, knows a tool that does, any other
ideas are very welcome
Thanks, Liliya
I'm not sure if I understood your question 100% but here;s my answer anyway,
hope it helps. You can use the system stored procedure called sp_depends to
get object dependencies
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>
|||Liliya
I think you need to look at RedGate third party compare product
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>
|||I can pull object dependencies easy, as I have mentioned before. sp_depends
is one way to do it. tracing what management studio does pulling dependencies
is another way. Both are easy. It does not return the level of detail I need.
view1.columnXYZ depends on object1.column1 and so on, entire dependency tree
under to the last table/function.column
Thanks, Liliya
"Knowledgy" wrote:

> I'm not sure if I understood your question 100% but here;s my answer anyway,
> hope it helps. You can use the system stored procedure called sp_depends to
> get object dependencies
> --
> Sincerely,
> John K
> Knowledgy Consulting
> www.knowledgy.org
|||Downloaded the tool, got trial expired immediately. interesting product...
I have one of their products, started wondering, how exactly do keep track
of tier licenses etc...
demo video leaves the impression that all it is a gui on top of sp_depends.
I would not buy the product on that video... I mean, no need to get what ms
management studio already has. I do not believe they parse views and
functions from what i have seen so far.
Hope, their sales rep is going to be quick to answer a question of the
existing customer.
Thanks, Liliya
"Uri Dimant" wrote:

> Liliya
> I think you need to look at RedGate third party compare product
|||the tool tracks dependencies on object level only. not granular enough for my
needs.
Thanks, Liliya
"Uri Dimant" wrote:

> Liliya
> I think you need to look at RedGate third party compare product
|||RedGate third party compare product - provides only object level dependencies
confirmed by the vendor
"
We use our own parser to build a schema representation. Unfortunately, SQL
Dependency Tracker will only go down to table/view level, it will not show
you the dependencies at column level.
This is something that lots of customers ask us for so when there is
development work planned for SQL Dependency Tracker this will be a seriously
considered feature. However, I do not have any kind of timescale on this.
"
Any other vendors?
Thanks, Liliya
|||Investigate CAST. Worked with their stuff about 10 years ago but nothing
came close to their ability to track dependencies. Note that it was an
upper 5 to as much as 7 figure product back then. :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>

how to track object dependencies (view, function). granularity - c

There is a way in management studio 2005 to get dependencies on a databse
object, to reproduce what it does is not a big deal.
This report provides object names thwe view or function depends on for me
ust fine.
For darta warehouse analysis I need to track down dependencies of a
particular column / all collumns in the view. And the existing oviews are big
time ugly, pages of code (perform not any nicer either).
SQL parses a view and builds all the dependencies, but is there a way to get
ahold of that information somehow?
I mean I have quite a bit of ugly looking views, and the idea of doung it by
hand does not look good, it is one nasty job to be honest.
I looking for means to automat that e, create a report aout column-column
dependencies of a view/function.
Did anyone here do anything similar, knows a tool that does, any other
ideas are very welcome
--
Thanks, LiliyaI'm not sure if I understood your question 100% but here;s my answer anyway,
hope it helps. You can use the system stored procedure called sp_depends to
get object dependencies
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>|||Liliya
I think you need to look at RedGate third party compare product
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>|||I can pull object dependencies easy, as I have mentioned before. sp_depends
is one way to do it. tracing what management studio does pulling dependencies
is another way. Both are easy. It does not return the level of detail I need.
view1.columnXYZ depends on object1.column1 and so on, entire dependency tree
under to the last table/function.column
--
Thanks, Liliya
"Knowledgy" wrote:
> I'm not sure if I understood your question 100% but here;s my answer anyway,
> hope it helps. You can use the system stored procedure called sp_depends to
> get object dependencies
> --
> Sincerely,
> John K
> Knowledgy Consulting
> www.knowledgy.org|||Downloaded the tool, got trial expired immediately. interesting product...
I have one of their products, started wondering, how exactly do keep track
of tier licenses etc...
demo video leaves the impression that all it is a gui on top of sp_depends.
I would not buy the product on that video... I mean, no need to get what ms
management studio already has. I do not believe they parse views and
functions from what i have seen so far.
Hope, their sales rep is going to be quick to answer a question of the
existing customer.
--
Thanks, Liliya
"Uri Dimant" wrote:
> Liliya
> I think you need to look at RedGate third party compare product|||the tool tracks dependencies on object level only. not granular enough for my
needs.
--
Thanks, Liliya
"Uri Dimant" wrote:
> Liliya
> I think you need to look at RedGate third party compare product|||RedGate third party compare product - provides only object level dependencies
confirmed by the vendor
"
We use our own parser to build a schema representation. Unfortunately, SQL
Dependency Tracker will only go down to table/view level, it will not show
you the dependencies at column level.
This is something that lots of customers ask us for so when there is
development work planned for SQL Dependency Tracker this will be a seriously
considered feature. However, I do not have any kind of timescale on this.
"
Any other vendors?
Thanks, Liliya|||Investigate CAST. Worked with their stuff about 10 years ago but nothing
came close to their ability to track dependencies. Note that it was an
upper 5 to as much as 7 figure product back then. :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Liliya Huff" <LiliyaHuff@.discussions.microsoft.com> wrote in message
news:92770E4B-F9E1-42EB-B68E-6AD50CABA825@.microsoft.com...
> There is a way in management studio 2005 to get dependencies on a databse
> object, to reproduce what it does is not a big deal.
> This report provides object names thwe view or function depends on for me
> ust fine.
> For darta warehouse analysis I need to track down dependencies of a
> particular column / all collumns in the view. And the existing oviews are
> big
> time ugly, pages of code (perform not any nicer either).
> SQL parses a view and builds all the dependencies, but is there a way to
> get
> ahold of that information somehow?
> I mean I have quite a bit of ugly looking views, and the idea of doung it
> by
> hand does not look good, it is one nasty job to be honest.
> I looking for means to automat that e, create a report aout column-column
> dependencies of a view/function.
> Did anyone here do anything similar, knows a tool that does, any other
> ideas are very welcome
> --
> Thanks, Liliya
>

How to track changes to a table using system tables ?

I would like to get a report on a monthly basis as to what tables were
changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
columns on a regular basis. sysobjects has only crdate (object creation date)
but not modify datetime. Is there a way to get that ?
TIA
MO
MO
SQL Server does not track changes like drop/modify
Visit www.dbazine.com. There you will be found a great script written by
Gregory A. Larsen (Monitoring changed objects)
"MO" <MO@.discussions.microsoft.com> wrote in message
news:0603078E-4248-419A-AA8B-C6CF0AD0D3E9@.microsoft.com...
> I would like to get a report on a monthly basis as to what tables were
> changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
> columns on a regular basis. sysobjects has only crdate (object creation
date)
> but not modify datetime. Is there a way to get that ?
> TIA
> MO
sql

How to track changes to a table using system tables ?

I would like to get a report on a monthly basis as to what tables were
changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
columns on a regular basis. sysobjects has only crdate (object creation date)
but not modify datetime. Is there a way to get that ?
TIA
MOMO
SQL Server does not track changes like drop/modify
Visit www.dbazine.com. There you will be found a great script written by
Gregory A. Larsen (Monitoring changed objects)
"MO" <MO@.discussions.microsoft.com> wrote in message
news:0603078E-4248-419A-AA8B-C6CF0AD0D3E9@.microsoft.com...
> I would like to get a report on a monthly basis as to what tables were
> changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
> columns on a regular basis. sysobjects has only crdate (object creation
date)
> but not modify datetime. Is there a way to get that ?
> TIA
> MO

How to track changes to a table using system tables ?

I would like to get a report on a monthly basis as to what tables were
changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
columns on a regular basis. sysobjects has only crdate (object creation date
)
but not modify datetime. Is there a way to get that ?
TIA
MOMO
SQL Server does not track changes like drop/modify
Visit www.dbazine.com. There you will be found a great script written by
Gregory A. Larsen (Monitoring changed objects)
"MO" <MO@.discussions.microsoft.com> wrote in message
news:0603078E-4248-419A-AA8B-C6CF0AD0D3E9@.microsoft.com...
> I would like to get a report on a monthly basis as to what tables were
> changed(from a DDL standpoint) in the last 30 days ? We add/drop/modify
> columns on a regular basis. sysobjects has only crdate (object creation
date)
> but not modify datetime. Is there a way to get that ?
> TIA
> MO

how to total the values in a numeric column?

I'm building a report using the matrix control. I have financial data in
different line items (Salary, Rental Expense etc) that needs to be shown by
month (so the columns have months). I have it pretty much working but can't
figure out how to sum up the numbers in each column at the bottom so I can
have a total for every month. Could anyone help?
Thanks a lot.
BobRight-click on the row field that you wish to total, in your case it
would be the Salary field. Choose Sub-total from the menu.
To format the subtotals you need to right-click->properties on the tiny
green triangle that appears on the Total cell.|||ahhh. I was thinking this must be something really simple since it's such a
common function. You can't believe how much time I had spent trying to
figure this out. Thanks a lot.
Bob
"grahamiec" <grahamrichter@.gmail.com> wrote in message
news:1123769689.664448.10490@.g14g2000cwa.googlegroups.com...
> Right-click on the row field that you wish to total, in your case it
> would be the Salary field. Choose Sub-total from the menu.
> To format the subtotals you need to right-click->properties on the tiny
> green triangle that appears on the Total cell.
>

how to total fields within footer

I have a report footer which includes a number of totals fields. Each
of those fields is computed based on various fields in the data set,
using some rather long expressions.
What I need now is to add a new "super total" field that displays the
sum of three of the other total fields. The only way I can reference
the other three fields in the new field's Value property (without
getting a build error) is with an expression like:
=ReportItems("txtTotal1").Value+ReportItems("txtTotal2").Value+ReportItems("txtTotal3").Value
But even then, the new field shows "#Error" anyway. Does anyone know
how to make this work?Try:
=ReportItems!txtTotal1.Value+ReportItems!txtTotal2.Value+ReportItems!txtTotal3.Value
HTH,
Magendo_man
"silverblatt@.att.net" wrote:
> I have a report footer which includes a number of totals fields. Each
> of those fields is computed based on various fields in the data set,
> using some rather long expressions.
> What I need now is to add a new "super total" field that displays the
> sum of three of the other total fields. The only way I can reference
> the other three fields in the new field's Value property (without
> getting a build error) is with an expression like:
> =ReportItems("txtTotal1").Value+ReportItems("txtTotal2").Value+ReportItems("txtTotal3").Value
> But even then, the new field shows "#Error" anyway. Does anyone know
> how to make this work?
>

How to toggle visibility of a group of columns in RS

Hi,

I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.

Thanks in anticipation.

Saeed

Hi,

This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi

Thanks for your response.

I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.

Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.

Thanks

Saeed

|||

asalamo alaykom saeed ahmad

you can do that by using models in reporting services

it means that when you using a model you can let the user select within the report builder to use columns dynamicly

and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc

http://www.123writers.com

How to toggle visibility of a group of columns in RS

Hi,

I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.

Thanks in anticipation.

Saeed

Hi,

This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi

Thanks for your response.

I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.

Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.

Thanks

Saeed

|||

asalamo alaykom saeed ahmad

you can do that by using models in reporting services

it means that when you using a model you can let the user select within the report builder to use columns dynamicly

and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc

http://www.123writers.com

How to tie ? to parameter name?

In the Visual Studio (2005) designer, I created a report, data source,
and dataset. The query is essentially Select * from Table1 where
ClientID = ?
Under Report Parameters I changed the name Parameter1 to CIDParam, so
that it is somewhat meaningful when I call it from code. But when I
try to preview the report, Studio complains that the query can't find
Parameter1. So how do I let Studio know that ? should look at
CIDParam?I go against Sybase and have to do this all the time (unfortunately). Go
back to the dataset tab, click on ..., parameters tab. Remap the query
paramters. Keep in mind that although RS creates the report parameters
automatically for you they are not the same thing. This is where they are
mapped.
Another time you will want to do this is if you want to use the same report
parameter multiple times. For instance you have a from and end date that is
used multiple times in your query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:l2jni255jalsgohek00ih5ac2h18vbh33a@.4ax.com...
> In the Visual Studio (2005) designer, I created a report, data source,
> and dataset. The query is essentially Select * from Table1 where
> ClientID = ?
> Under Report Parameters I changed the name Parameter1 to CIDParam, so
> that it is somewhat meaningful when I call it from code. But when I
> try to preview the report, Studio complains that the query can't find
> Parameter1. So how do I let Studio know that ? should look at
> CIDParam?|||On Tue, 10 Oct 2006 12:24:48 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>I go against Sybase and have to do this all the time (unfortunately). Go
>back to the dataset tab, click on ..., parameters tab. Remap the query
>paramters. Keep in mind that although RS creates the report parameters
>automatically for you they are not the same thing. This is where they are
>mapped.
>Another time you will want to do this is if you want to use the same report
>parameter multiple times. For instance you have a from and end date that is
>used multiple times in your query.
Yes, that seems to have done it. Thanks.

How to tell if a report has changed?

I'm using Progress 9.1E (it's similar to VB) and Crystal Reports 9.2.

Using the OCX properties of a Crystal Report, is it possible to tell if a report has been changed since a version you know, without simply checking the timestamp?

I want to give reports to customers for them to use, and allow them to modify them if they like...but if they do, I want to have a way of knowing that the report has changed since we gave it to them (in which case we wouldn't provide any support).

I'm thinking along the lines of looking at the count/names of parameter & formula fields, and maybe storing a simple encrypted result of this in a property of the report when it's in our hands. When the report is opened by our software, this value could be checked against the field list again.

Is there a simpler and/or more effective way than this?In Crystal Reports (http://www.shelko.com) there is a modification date and modification time formula under the special fields group in the field explorer window. This will show the date and time that the report was last modified.|||Your 'help' is about as much use as a chocolate fireguard and your sly advertising technique fools no-one|||I think In VB, use File System object to get the last modified date of the file. Search some sample code at www.planet-source-code.com

How to suppress the subreport when there is no data in it?

Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank?

Thanks.

The subreport reportitem (in the main report) has a NoRows property. If all the dataset queries inside the subreport return 0 zero rows and the NoRows property is set explicitly, it will replace the contents of the subreport with the NoRows message (which could be a blank string).

-- Robert

|||

OK, so how do you hide the row that contains the subreport is the subreport is blank? Is there an expression you can enter into the NoRows property?

Thanks

|||

Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data?

Thanks,

Pete

|||

Can you explain your scenario in more detail please?

The closest you can get right now is to have the subreport queries in the main report and then determine the visibility of the table row in the main report based on the CountRows("DataSetName") aggregation result.

-- Robert

|||

Hi Robert:

Basically I'm testing SSRS 2005 compared to our current report design/production in Crystal Reports XI. One of the features of CR that we use extensively is subreports. When a particular section of a CR report does not have relevant subreport data to display, we dynamically hide the main report section that hosts the subreport so as not to display any info that is in the same main report section but not in the subreport.

I've found a thread that appears similar in nature to my situation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423404&SiteID=1

Most discussions I've reviewed about dynamically hiding subreports in SSRS resolve to the conclusion that if a subreport does not have data, then the subreport hides itself which should be "good enough". This feature, while useful sometimes, won't help my situation in which other data from the Main report would also need to hide.

I've attempted to nest regions, instead of using subreports, (e.g., a List region that nests a Table region) whereby the List region refers to dataset1 while the Table region refers to dataset2, but I am unable to display the report successfully. I usually receive an error that I am attempting to display an aggregate within a nested data region.

By the way, the link I mention above has a comment that "in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport." Is this a correct statement?

So far, the solution I've crafted requires me to alter the sql stored proc to support both the main report and subreport, effectively forcing a one-to-one relationship between the main report and subreport. Ideally, I'd like to construct a report where the main report can have linking keys not always found in the subreport (i.e., similar to a sql outer join), and then have the main report dynamically hide/show the hosting data region or table row. Even better would be to avoid using subreports, and figure out how to have a single report use data regions with different datasets such that the same effect is achieved as if I had used subreports. (This last point assumes better query/reporting performace can be achieved.)

Lastly, I've attemped to filter results of one data region based on data in another data region without success. Again, usually I've attempted this by nesting data regions unsuccessfully -- possibly because each data region refers to different datasets?

Thanks,

Pete

|||

Hi Andrea,

Did you find the solution to hide the row?

i have same problem..

Thanks alot..

|||No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.

How to suppress the subreport when there is no data in it?

Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank?

Thanks.

The subreport reportitem (in the main report) has a NoRows property. If all the dataset queries inside the subreport return 0 zero rows and the NoRows property is set explicitly, it will replace the contents of the subreport with the NoRows message (which could be a blank string).

-- Robert

|||

OK, so how do you hide the row that contains the subreport is the subreport is blank? Is there an expression you can enter into the NoRows property?

Thanks

|||

Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data?

Thanks,

Pete

|||

Can you explain your scenario in more detail please?

The closest you can get right now is to have the subreport queries in the main report and then determine the visibility of the table row in the main report based on the CountRows("DataSetName") aggregation result.

-- Robert

|||

Hi Robert:

Basically I'm testing SSRS 2005 compared to our current report design/production in Crystal Reports XI. One of the features of CR that we use extensively is subreports. When a particular section of a CR report does not have relevant subreport data to display, we dynamically hide the main report section that hosts the subreport so as not to display any info that is in the same main report section but not in the subreport.

I've found a thread that appears similar in nature to my situation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423404&SiteID=1

Most discussions I've reviewed about dynamically hiding subreports in SSRS resolve to the conclusion that if a subreport does not have data, then the subreport hides itself which should be "good enough". This feature, while useful sometimes, won't help my situation in which other data from the Main report would also need to hide.

I've attempted to nest regions, instead of using subreports, (e.g., a List region that nests a Table region) whereby the List region refers to dataset1 while the Table region refers to dataset2, but I am unable to display the report successfully. I usually receive an error that I am attempting to display an aggregate within a nested data region.

By the way, the link I mention above has a comment that "in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport." Is this a correct statement?

So far, the solution I've crafted requires me to alter the sql stored proc to support both the main report and subreport, effectively forcing a one-to-one relationship between the main report and subreport. Ideally, I'd like to construct a report where the main report can have linking keys not always found in the subreport (i.e., similar to a sql outer join), and then have the main report dynamically hide/show the hosting data region or table row. Even better would be to avoid using subreports, and figure out how to have a single report use data regions with different datasets such that the same effect is achieved as if I had used subreports. (This last point assumes better query/reporting performace can be achieved.)

Lastly, I've attemped to filter results of one data region based on data in another data region without success. Again, usually I've attempted this by nesting data regions unsuccessfully -- possibly because each data region refers to different datasets?

Thanks,

Pete

|||

Hi Andrea,

Did you find the solution to hide the row?

i have same problem..

Thanks alot..

|||No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.

How to suppress the subreport when there is no data in it?

Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank?

Thanks.

The subreport reportitem (in the main report) has a NoRows property. If all the dataset queries inside the subreport return 0 zero rows and the NoRows property is set explicitly, it will replace the contents of the subreport with the NoRows message (which could be a blank string).

-- Robert

|||

OK, so how do you hide the row that contains the subreport is the subreport is blank? Is there an expression you can enter into the NoRows property?

Thanks

|||

Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data?

Thanks,

Pete

|||

Can you explain your scenario in more detail please?

The closest you can get right now is to have the subreport queries in the main report and then determine the visibility of the table row in the main report based on the CountRows("DataSetName") aggregation result.

-- Robert

|||

Hi Robert:

Basically I'm testing SSRS 2005 compared to our current report design/production in Crystal Reports XI. One of the features of CR that we use extensively is subreports. When a particular section of a CR report does not have relevant subreport data to display, we dynamically hide the main report section that hosts the subreport so as not to display any info that is in the same main report section but not in the subreport.

I've found a thread that appears similar in nature to my situation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423404&SiteID=1

Most discussions I've reviewed about dynamically hiding subreports in SSRS resolve to the conclusion that if a subreport does not have data, then the subreport hides itself which should be "good enough". This feature, while useful sometimes, won't help my situation in which other data from the Main report would also need to hide.

I've attempted to nest regions, instead of using subreports, (e.g., a List region that nests a Table region) whereby the List region refers to dataset1 while the Table region refers to dataset2, but I am unable to display the report successfully. I usually receive an error that I am attempting to display an aggregate within a nested data region.

By the way, the link I mention above has a comment that "in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport." Is this a correct statement?

So far, the solution I've crafted requires me to alter the sql stored proc to support both the main report and subreport, effectively forcing a one-to-one relationship between the main report and subreport. Ideally, I'd like to construct a report where the main report can have linking keys not always found in the subreport (i.e., similar to a sql outer join), and then have the main report dynamically hide/show the hosting data region or table row. Even better would be to avoid using subreports, and figure out how to have a single report use data regions with different datasets such that the same effect is achieved as if I had used subreports. (This last point assumes better query/reporting performace can be achieved.)

Lastly, I've attemped to filter results of one data region based on data in another data region without success. Again, usually I've attempted this by nesting data regions unsuccessfully -- possibly because each data region refers to different datasets?

Thanks,

Pete

|||

Hi Andrea,

Did you find the solution to hide the row?

i have same problem..

Thanks alot..

|||No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.

How to suppress the subreport when there is no data in it?

Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank?

Thanks.

The subreport reportitem (in the main report) has a NoRows property. If all the dataset queries inside the subreport return 0 zero rows and the NoRows property is set explicitly, it will replace the contents of the subreport with the NoRows message (which could be a blank string).

-- Robert

|||

OK, so how do you hide the row that contains the subreport is the subreport is blank? Is there an expression you can enter into the NoRows property?

Thanks

|||

Ditto -- What is the expression to suppress the table row that contains the subreport when the subreport has no data?

Thanks,

Pete

|||

Can you explain your scenario in more detail please?

The closest you can get right now is to have the subreport queries in the main report and then determine the visibility of the table row in the main report based on the CountRows("DataSetName") aggregation result.

-- Robert

|||

Hi Robert:

Basically I'm testing SSRS 2005 compared to our current report design/production in Crystal Reports XI. One of the features of CR that we use extensively is subreports. When a particular section of a CR report does not have relevant subreport data to display, we dynamically hide the main report section that hosts the subreport so as not to display any info that is in the same main report section but not in the subreport.

I've found a thread that appears similar in nature to my situation: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423404&SiteID=1

Most discussions I've reviewed about dynamically hiding subreports in SSRS resolve to the conclusion that if a subreport does not have data, then the subreport hides itself which should be "good enough". This feature, while useful sometimes, won't help my situation in which other data from the Main report would also need to hide.

I've attempted to nest regions, instead of using subreports, (e.g., a List region that nests a Table region) whereby the List region refers to dataset1 while the Table region refers to dataset2, but I am unable to display the report successfully. I usually receive an error that I am attempting to display an aggregate within a nested data region.

By the way, the link I mention above has a comment that "in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport." Is this a correct statement?

So far, the solution I've crafted requires me to alter the sql stored proc to support both the main report and subreport, effectively forcing a one-to-one relationship between the main report and subreport. Ideally, I'd like to construct a report where the main report can have linking keys not always found in the subreport (i.e., similar to a sql outer join), and then have the main report dynamically hide/show the hosting data region or table row. Even better would be to avoid using subreports, and figure out how to have a single report use data regions with different datasets such that the same effect is achieved as if I had used subreports. (This last point assumes better query/reporting performace can be achieved.)

Lastly, I've attemped to filter results of one data region based on data in another data region without success. Again, usually I've attempted this by nesting data regions unsuccessfully -- possibly because each data region refers to different datasets?

Thanks,

Pete

|||

Hi Andrea,

Did you find the solution to hide the row?

i have same problem..

Thanks alot..

|||No, I did not. Instead I have 'No data available' displayed when there is no data appearing from the subreport.

How to Suppress the Hiden Row

Hi Group,
When i rendered the report to MHTML the hidden items are still displaying
and in the preview mode a blank space is displayed.So how do i suppress the
hidden items.When you hide a control you , the control only doesnt show
up but it still holds its position.
So you could actually have the values in a table and then
apply the hidden expression to the column.
In this case the column if its hidden will not show a blank
space.
Hope this helps
>--Original Message--
>Hi Group,
>When i rendered the report to MHTML the hidden items are
still displaying
>and in the preview mode a blank space is displayed.So how
do i suppress the
>hidden items.
>
>.
>

How to suppress Report Header

My report has two groups and I print the details on Group Footer two. I cant seem to suppress the report header when there is no records printed.

The data comes from 3 tables (Vehicles, Servicing & ServiceType). With the following layouts:
Vehicles:
VehicleID
Description

Servicing:
ServiceType
ServiceDate
VehicleID

ServiceType:
ServiceType
Duration (How often the servicing is to be done)

The report is to show what Servicing is coming due based on how often the servicetype is performed. The report is grouped by vehicle and service type with the records printed on the Service Type (Group 2) footer. There is a running total field (MaxDate) that is used to determine when the particular service type is due next for the given vehicle since the same service type can be performed multiple times on the same vehicle.

The question is how do I suppress the header when no records are printed?

I have tried doing a Suppress formula checking for IsNull or Count>0 for ServiceType but the count is always greater than the number of fields that are actually printed.

Thanks for any help you can provide.I rarely use the report header section, unless I want to print the report to confirm it has null data (ie print an empty report as a way of confirming something has no data).
I generally use the Page Header to contain logos, titles etc to print at top of each page.
If there's no data, the page header etc won't print....

Dave|||Thanks for the response but I wouldnt want the header to appear at the top of every page. Is there a way to have it only print once?

The report would look something like:

SERVICING

Service Type Due Date
Brake Adjustment Aug 7 2004
Inspection Aug 9 2004

The Servicing title and the column headers are the items I currently have in the Report header.|||Put fields in the report header which u want to print only once. And put those fields in the page header which u want to print on the top of every page.
And to suppress in case of no record, u can put a formula in the format section of the report like

Suppress IF {...}|||I have tried putting a suppress formula in the Report Header of IsNull(ServiceType.Description) or Count(ServiceType.Description) = 0 but it didnt work. The problem was the Count was greater than 0 but they are all being suppressed by the formula in the Group 2 Footer.

Is there a way to calculate the number actually being displayed? Also, when does Crystal compute whether the Report Header should display? Does it do this before or after the details are printed?|||if you want to suppress on the basis of count then u can put formula on any of your record coming from DB, checking for 0.
I dont know this ServiceType.Description as i worked in CR-7 n didnt see this thing there.
Also while open the formatting Tab of format header dection and there you can find to put formula on suppressing the section.|||That is exactly what I did. The problem seems to be that the count is always zero in the Report Header section. If I display the count as it prints the records and in the Report Footer it shows the accurate count.

The question is how do I base the suppression on the number of records displayed?

How to suppress a list containing a subreport, when the subreport returns no data

Hi,

I have a report which contains a subreport placed inside a list. when the subreport returns no data. it appears as a blank space in the main report. So i want to suppress the list when the subreport returns no data. Can somebody help me with this?

thanks
shri

Has the NoRows property been set for the subreport reportitem in the main report? This property kicks in if the subreport returns no data.

Also, if you'd like to make the list invisible when no data is returned, the visibility of the enclosing list could be set to a boolean expression based on the subreport reportitem. Type an expression that evaluates to a Boolean value of True to hide the item and False to show the item. Alternatively, visibility of the list can be toggled by another report item like subreport. The visibility settings could be set under the "visibility" tab uner the properties of list.

How to suppress a field if it is null

I have a report where it displays demographic information. The Address2 field
is sometimes NULL and I haven't found a way to suppress this field. The only
thing I can do is make it invisible/visible with an IIF statement. I know in
Crystal Reports you could check a box that said "SUPPRESS IF BLANK" and this
would move all the other fields up on the report accordingly. With SQL
Reporting services it doesn't adjust the fields underneath it so I have a
blank/empty space. Does anyone know of any other things I can try.
ThanksHi GORAMS,
Are you using a table control? If so, take a look at the actual detail
line for Address2, and make sure that your IIF statement to control
visibility is set on the entire tablerow as opposed to just the
Address2 textbox. Just setting the visibility property on the Address2
textbox would cause your problem if the table has more than column.
Does this help?
Matt A
Rpeorting Services Newsletter at www.reportarchitex.com|||RS is shrinking fields if you specify that it is invisible (for example if
you put in Hidden property "=Fields!Something.Value is Nothing"). But, as in
CR, that depends of other fields in line, so if one is supperesed and other
one is not, it will not move fields under supperesed one up...
You can play arround little with columns maybe, or lists if you want to do
something like that..
Stjepan
"GORAMS" <GORAMS@.discussions.microsoft.com> wrote in message
news:A28602C1-D97C-4A19-9ABD-6A06D6A4A9BA@.microsoft.com...
>I have a report where it displays demographic information. The Address2
>field
> is sometimes NULL and I haven't found a way to suppress this field. The
> only
> thing I can do is make it invisible/visible with an IIF statement. I know
> in
> Crystal Reports you could check a box that said "SUPPRESS IF BLANK" and
> this
> would move all the other fields up on the report accordingly. With SQL
> Reporting services it doesn't adjust the fields underneath it so I have a
> blank/empty space. Does anyone know of any other things I can try.
> Thanks

How to supply NT Credential on RS Report Parameter

I have a Filter Dropdown parameter which calls Storeprocedure DataSet
accepts NT Domain and Username as parameter. How will i supply this
parameter to make it work. Right now i hardcode values and it works fine.
In Dot.net Program we can set Identity Impersonate to true and then use
User.Identity Security Principals . How do i use on Reporting Services.
Any idea would be great!I figured it out.. User.UserID ( User running the report)
"RAGHAVAN JAYARAMAN" wrote:
> I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> accepts NT Domain and Username as parameter. How will i supply this
> parameter to make it work. Right now i hardcode values and it works fine.
> In Dot.net Program we can set Identity Impersonate to true and then use
> User.Identity Security Principals . How do i use on Reporting Services.
> Any idea would be great!
>|||I am having the same problem.
Where did you place this code? In the Parameter field itself? Inside of
the report code tab?
Thanks,
msflinx
"RAGHAVAN JAYARAMAN" wrote:
> I figured it out.. User.UserID ( User running the report)
> "RAGHAVAN JAYARAMAN" wrote:
> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> > accepts NT Domain and Username as parameter. How will i supply this
> > parameter to make it work. Right now i hardcode values and it works fine.
> >
> > In Dot.net Program we can set Identity Impersonate to true and then use
> > User.Identity Security Principals . How do i use on Reporting Services.
> >
> > Any idea would be great!
> >|||Query parameters do not have to map to a report parameter. RS automatically
creates matching report parameters but you can instead map the query
parameter to an expression. In the dataset tab click on the ..., parameters
tab and map to an expression. When the expression builder comes up, expand
the global variable and set it to the User!UserID global variable. Note that
this has domain\username so if you need just one or the other you need to
strip off the part you don't want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:133E55D5-265D-4AAB-A703-B59EC82C3ABB@.microsoft.com...
>I am having the same problem.
> Where did you place this code? In the Parameter field itself? Inside of
> the report code tab?
> Thanks,
> msflinx
> "RAGHAVAN JAYARAMAN" wrote:
>> I figured it out.. User.UserID ( User running the report)
>> "RAGHAVAN JAYARAMAN" wrote:
>> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
>> > accepts NT Domain and Username as parameter. How will i supply this
>> > parameter to make it work. Right now i hardcode values and it works
>> > fine.
>> >
>> > In Dot.net Program we can set Identity Impersonate to true and then use
>> > User.Identity Security Principals . How do i use on Reporting Services.
>> >
>> > Any idea would be great!
>> >