Report of Reports

I have a report listing all my available reports.
On that report I would like to click on a line and automatically
drillthrough/navigate to a particular report for that line.
Is that possible? It looks like the navigate tab can only take a static
reference to a single report.so build a standard report that queries against the SQL Database Named
'Catalog'
-Aaron
Jim Breffni wrote:
> I have a report listing all my available reports.
> On that report I would like to click on a line and automatically
> drillthrough/navigate to a particular report for that line.
> Is that possible? It looks like the navigate tab can only take a static
> reference to a single report.|||It is possible to drillthrough different reports. Your 2nd and the 3rd
sentence are contradictary. correct me if I have understood wrongly..
ie. you said
in 2nd
I would like to click on a line and automatically
drillthrough/navigate to a particular report for that line
in 3rd
Is that possible? It looks like the navigate tab can only take a
static
reference to a single report.
Amarnath
"Jim Breffni" wrote:
> I have a report listing all my available reports.
> On that report I would like to click on a line and automatically
> drillthrough/navigate to a particular report for that line.
> Is that possible? It looks like the navigate tab can only take a static
> reference to a single report.sql

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.

CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.

CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

Report is Being Generated

I have a report with 12 parameters. They all have a default setting. When
I go to preview the report in the IDE it automatically starts to generate
the report with the user having to hit the view report button. Is there a
way to keep the report from auto generating without user interaction?I have a report with 12 parameters. They all have a default setting. When
I go to preview the report in the IDE it automatically starts to generate
the report without the user having to hit the view report button. Is there
a
way to keep the report from auto generating without user interaction?
"Chris" <cexley@.enableconsulting.com> wrote in message
news:Oak73TDdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I have a report with 12 parameters. They all have a default setting. When
>I go to preview the report in the IDE it automatically starts to generate
>the report with the user having to hit the view report button. Is there a
>way to keep the report from auto generating without user interaction?
>|||Unfortunately this is by design. If all the parameters have defaults it
immediately starts rendering. You need to have at least one parameter that
the user has to select.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cexley@.enableconsulting.com> wrote in message
news:Oak73TDdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I have a report with 12 parameters. They all have a default setting. When
>I go to preview the report in the IDE it automatically starts to generate
>the report with the user having to hit the view report button. Is there a
>way to keep the report from auto generating without user interaction?
>|||Thanks for the response. I think it is bad design, and should be a property
(IsAutoGenerate comes to mind).
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7YDbYDdGHA.5048@.TK2MSFTNGP03.phx.gbl...
> Unfortunately this is by design. If all the parameters have defaults it
> immediately starts rendering. You need to have at least one parameter that
> the user has to select.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cexley@.enableconsulting.com> wrote in message
> news:Oak73TDdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>>I have a report with 12 parameters. They all have a default setting.
>>When I go to preview the report in the IDE it automatically starts to
>>generate the report with the user having to hit the view report button.
>>Is there a way to keep the report from auto generating without user
>>interaction?
>

Report gets refreshed automatically

Hi,

I created a report using SQL 2000 Reporting Services. I have 3 input fields viz., Start Date, End Date and Configuration Item. First 2 are textboxes and the last one is a dropdown. I also have a button 'View Report' clicking which the report page will be refreshed. When I deploy the Reports in report manager, when I give the Start Date then the End date, before I could select a value from the Dropdown, the report page is getting refreshed automatically before I click View Report button. i.e., the report gets refreshed on lifting the focus from the End date textbox. Why does this happen? Note that I have not set the Auto refresh property for this report.

The most likely reason the report is being refreshed is because the "Configuration Item" parameter has a dynamic value that may depend on the values of "Start Date" and/or "End Date". The report is refreshed as soon as the value for "End Date" changes to ensure that the valid values for "Configuration Item" are accurate.

For example, RS will assume that "Configuration Item" depends on "End Date" if:

"Configuration Item" is populated from a query that uses expressions in the query, filters, calculated fields, or query parameters|||

I also faced the same problem.

In my case, by mistake I set the Report Properties " AutoRefresh " as 1 insted of 0 in properties window on right hand side

so just check whether its 1 or 0

Report Generation Chaining

Hi
Is there an easey way to set up report generation chaining (i.e.
automatically start a second report once the first completes)?
Many thanks,
Dave HughesIn a simila vein, is there a way to ensure reports that share a schedule are
processed sequentially (in the belief that the server will be happier
processing one at at time rather than 20 at the same scheduled start time)?
Ta
Dave Hughes
"David Hughes" <spamfree@.freespam.com> wrote in message
news:uCUQ%23GbxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is there an easey way to set up report generation chaining (i.e.
> automatically start a second report once the first completes)?
> Many thanks,
> Dave Hughes
>