Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

Nested stored procedure

I have a report, say for example "Dummy report" which has its dataset to be a
stored procedure SP1. This stored procedure SP1 gets its data from another
stored procedure (SP2) that is nested in it.
When I try to generate "Dummy report" from report designer for the first
time, it fails. What I need to do now is run SP2 individually from SSMS, and
then run the "Dummy report" from designer to see the report output. Every
time I make a change to SP2, I have to do the same thing - run SP2
individually and then generate the report. I am not able to understand why
this is happening. Can anyone help me with this problem? Thanks in advance.
SQL Server 2005 SP1/Windows Server 2003On Mar 9, 12:23 pm, KMP <K...@.discussions.microsoft.com> wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003
If I'm understanding you correctly, it has something to do w/why a
stored procedure cannot be initially used when creating a report.
Basically, the report needs to know the dataset format and what data
to expect, etc up front. Hopefully in a future version of SSRS, this
will be corrected.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Have you checked, that your sp1 is working from SSMS ? Just check whether
you have given hints in the query or recompile etc... and check whether it
returns single set of data.
Amarnath, MCTS
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||If your first stored procedure is creating a temp table that is then filled
by the second stored procedure then you need to add a line to your stored
procedure.
SET FMTONLY OFF
The issue is that when RS is trying to create the field list it calls the
stored procedure with the set fmtonly on (i.e. it doesn't really execute
it). This means it does not create the temp table either (although I have
found I only have this issue with nested stored procedures).
Anyway, add the statement at the top of you calling stored procedure.
The only other issue, sometimes you have to click on the refresh fields
button, it is one of the buttons to the right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21160AC2-8078-4CA8-A70D-494ACB4F3168@.microsoft.com...
>I have a report, say for example "Dummy report" which has its dataset to be
>a
> stored procedure SP1. This stored procedure SP1 gets its data from
> another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS,
> and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in
> advance.
> SQL Server 2005 SP1/Windows Server 2003|||SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either. Thanks for all the
ideas. Still need to find a solution though...
"Amarnath" wrote:
> Have you checked, that your sp1 is working from SSMS ? Just check whether
> you have given hints in the query or recompile etc... and check whether it
> returns single set of data.
> Amarnath, MCTS
>
> "KMP" wrote:
> > I have a report, say for example "Dummy report" which has its dataset to be a
> > stored procedure SP1. This stored procedure SP1 gets its data from another
> > stored procedure (SP2) that is nested in it.
> >
> > When I try to generate "Dummy report" from report designer for the first
> > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > then run the "Dummy report" from designer to see the report output. Every
> > time I make a change to SP2, I have to do the same thing - run SP2
> > individually and then generate the report. I am not able to understand why
> > this is happening. Can anyone help me with this problem? Thanks in advance.
> >
> > SQL Server 2005 SP1/Windows Server 2003|||Please help!!!
"KMP" wrote:
> SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either. Thanks for all the
> ideas. Still need to find a solution though...
> "Amarnath" wrote:
> > Have you checked, that your sp1 is working from SSMS ? Just check whether
> > you have given hints in the query or recompile etc... and check whether it
> > returns single set of data.
> >
> > Amarnath, MCTS
> >
> >
> > "KMP" wrote:
> >
> > > I have a report, say for example "Dummy report" which has its dataset to be a
> > > stored procedure SP1. This stored procedure SP1 gets its data from another
> > > stored procedure (SP2) that is nested in it.
> > >
> > > When I try to generate "Dummy report" from report designer for the first
> > > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > > then run the "Dummy report" from designer to see the report output. Every
> > > time I make a change to SP2, I have to do the same thing - run SP2
> > > individually and then generate the report. I am not able to understand why
> > > this is happening. Can anyone help me with this problem? Thanks in advance.
> > >
> > > SQL Server 2005 SP1/Windows Server 2003|||Any help will be greatly appreciated. Thank you!
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||I thought I had answered this one.
Anyway, is the issue that fields are not showing up in RS?
If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
issue is that you are not seeing the new fields then try the below:
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
defined by calling the SP with SET FMTONLY ON because Temp tables don't get
created if you select from the temp table the metadata from the rowset can't
be returned. This can be worked around by turning FMTONLY OFF in the SP."
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> Any help will be greatly appreciated. Thank you!
> "KMP" wrote:
>> I have a report, say for example "Dummy report" which has its dataset to
>> be a
>> stored procedure SP1. This stored procedure SP1 gets its data from
>> another
>> stored procedure (SP2) that is nested in it.
>> When I try to generate "Dummy report" from report designer for the first
>> time, it fails. What I need to do now is run SP2 individually from SSMS,
>> and
>> then run the "Dummy report" from designer to see the report output. Every
>> time I make a change to SP2, I have to do the same thing - run SP2
>> individually and then generate the report. I am not able to understand
>> why
>> this is happening. Can anyone help me with this problem? Thanks in
>> advance.
>> SQL Server 2005 SP1/Windows Server 2003|||Sorry if I am posting this over and over again. Seems to be more a SQL Server
issue, not RS. But below is my previous post and what happened when I tried
the different ideas:
"SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either."
"Bruce L-C [MVP]" wrote:
> I thought I had answered this one.
> Anyway, is the issue that fields are not showing up in RS?
> If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
> issue is that you are not seeing the new fields then try the below:
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...)
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
> defined by calling the SP with SET FMTONLY ON because Temp tables don't get
> created if you select from the temp table the metadata from the rowset can't
> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> > Any help will be greatly appreciated. Thank you!
> >
> > "KMP" wrote:
> >
> >> I have a report, say for example "Dummy report" which has its dataset to
> >> be a
> >> stored procedure SP1. This stored procedure SP1 gets its data from
> >> another
> >> stored procedure (SP2) that is nested in it.
> >>
> >> When I try to generate "Dummy report" from report designer for the first
> >> time, it fails. What I need to do now is run SP2 individually from SSMS,
> >> and
> >> then run the "Dummy report" from designer to see the report output. Every
> >> time I make a change to SP2, I have to do the same thing - run SP2
> >> individually and then generate the report. I am not able to understand
> >> why
> >> this is happening. Can anyone help me with this problem? Thanks in
> >> advance.
> >>
> >> SQL Server 2005 SP1/Windows Server 2003
>
>|||Ahh, this is not a Reporting Services issue. If you cannot run this from
outside of Reporting Services then all the advice I gave does not help you.
I suggest posting on the SQL Server newsgroups. They will be able to help
you,. First make sure everything works from outside of RS before trying to
create a report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:07013913-3721-4A9C-9B54-C6B2259ED23D@.microsoft.com...
> Sorry if I am posting this over and over again. Seems to be more a SQL
> Server
> issue, not RS. But below is my previous post and what happened when I
> tried
> the different ideas:
> "SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either."
>
> "Bruce L-C [MVP]" wrote:
>> I thought I had answered this one.
>> Anyway, is the issue that fields are not showing up in RS?
>> If SP1 calls SP2 then when you run SP1 from RS both should execute. If
>> the
>> issue is that you are not seeing the new fields then try the below:
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...)
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP
>> is
>> defined by calling the SP with SET FMTONLY ON because Temp tables don't
>> get
>> created if you select from the temp table the metadata from the rowset
>> can't
>> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
>> > Any help will be greatly appreciated. Thank you!
>> >
>> > "KMP" wrote:
>> >
>> >> I have a report, say for example "Dummy report" which has its dataset
>> >> to
>> >> be a
>> >> stored procedure SP1. This stored procedure SP1 gets its data from
>> >> another
>> >> stored procedure (SP2) that is nested in it.
>> >>
>> >> When I try to generate "Dummy report" from report designer for the
>> >> first
>> >> time, it fails. What I need to do now is run SP2 individually from
>> >> SSMS,
>> >> and
>> >> then run the "Dummy report" from designer to see the report output.
>> >> Every
>> >> time I make a change to SP2, I have to do the same thing - run SP2
>> >> individually and then generate the report. I am not able to understand
>> >> why
>> >> this is happening. Can anyone help me with this problem? Thanks in
>> >> advance.
>> >>
>> >> SQL Server 2005 SP1/Windows Server 2003
>>

Nested dataregions

Hi!

I'm having some problems with a basic thing I guess.

I have a table of visits as the whole Dataset. Let's call it group A.

I then group the visits per customer, let's call that group B.

After that i filter out some unwanted visits and call that group C.

The hiarchy then looks like this, A contains B that contains C.

The problem is that I want to know the number of rows in C above the actual table. Something like

CustomerName (John Doe) Number of visits (54 from group C but printed out while in group B)

Visit 1 blablabal
Visit 2 blablabla
... and so on ...

As far as I understand the aggregate functions, you can only use them on the current group or a group above, never below. I tried to make an invisible textbox below C and it works if there is just one customer. If there are more than one customer the sum flipped between all the customers. (A gets the sum of B, and so on).

Thanks
Johan

But you can have multiple C group sections (each time your grouping expression changes), correct? If an outer group can reference an inner group, there will be multiple totals that are only known at runtime.|||

In my case, I only have one C groping per B grouping. Its a table that filters out preknown types of visits. Its the number of rows in this table that I would like to print out above the table in question, inside grouping B. What I did was to create a textbox within the C grouping that I referenced from a B grouping. Works for one (1) customer, but if there are more customers the totals get calculated correctly, but displayed in the wrong order.

I realise that it might be inpossible to do this, but I would hope that I at least could "transfer" a number higher up if I know that it only would be one other C grouping.

What I would like to do is something like this:

(pseudocode)

=Count(Fields!VisitId.Value, "not VisitType=BadVisit")

Am I making sence at all? :)

|||Yes, you are but in general you can have more than one group sections and referencing an inner group is not allowed. Why don't you bring the C totals at the B level in your dataset? A simple scalar function (assuming SQL Server) will do the job.|||

The problem is that I don't deside the datasource at the moment and all I have is a view containing Visits of two types. The first type is carried out visits and the second one is missed visits.

They are not in any specific order and missed and carried out visits could be at random rows.

If I could change the dataset, are you suggesting that I put the total amount of visits with each row per customer and just read the first row by each customer grouping to get the value? Kind of like how they denormalized the name of the customer in every row?

Thanks for a quick answer by the way! :)

|||Correct. In this way, you don't have waste your time to find a hack. In addition, you'll never go wrong by pushing work to the database.

nested data regions

I am trying to nest 2 data regions. I have two data tables using a
different dataset but using the same data source.
I am trying to nest these and I've put the second data table in the group
footer of the first. How can I have the second data table work properly and
filter by the current group field value of the first data table. The
reporting services documentation is not clear.
Just testing it I am getting errors such as:
c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
textbox 'Comment' refers to the field 'Comment'. Report item expressions
can only refer to fields within the current data set scope or, if inside an
aggregate, the specified data set scope.
The reason I moved it into a seperate data region from a sub report is
because I could not find a way for the subreport to NOT "keep together".
Thanks for helping...Also,
Even though I have set the dataset of the second data table to a different
data set than the first table, the only fields that show up in the value
property for text boxes in the second data table are fields from the first
data table.
I am getting frustrated...
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>I am trying to nest 2 data regions. I have two data tables using a
>different dataset but using the same data source.
> I am trying to nest these and I've put the second data table in the group
> footer of the first. How can I have the second data table work properly
> and filter by the current group field value of the first data table. The
> reporting services documentation is not clear.
> Just testing it I am getting errors such as:
> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for the
> textbox 'Comment' refers to the field 'Comment'. Report item expressions
> can only refer to fields within the current data set scope or, if inside
> an aggregate, the specified data set scope.
>
> The reason I moved it into a seperate data region from a sub report is
> because I could not find a way for the subreport to NOT "keep together".
> Thanks for helping...
>
>|||Nested data regions have to share the same data set. The closest you can get
is to use subreports - they are independent reports and will retrieve the
data on their own (you can pass parameters to subreports).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"D Witherspoon" <dwitherspoon@.noway.org> wrote in message
news:Oz%23MDAfGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Also,
> Even though I have set the dataset of the second data table to a different
> data set than the first table, the only fields that show up in the value
> property for text boxes in the second data table are fields from the first
> data table.
> I am getting frustrated...
> "D Witherspoon" <dwitherspoon@.noway.org> wrote in message
> news:Oc76I6eGFHA.3072@.tk2msftngp13.phx.gbl...
>>I am trying to nest 2 data regions. I have two data tables using a
>>different dataset but using the same data source.
>> I am trying to nest these and I've put the second data table in the group
>> footer of the first. How can I have the second data table work properly
>> and filter by the current group field value of the first data table. The
>> reporting services documentation is not clear.
>> Just testing it I am getting errors such as:
>> c:\inetpub\wwwroot\wcr\wcrreports\rptWCR_.rdl The value expression for
>> the textbox 'Comment' refers to the field 'Comment'. Report item
>> expressions can only refer to fields within the current data set scope
>> or, if inside an aggregate, the specified data set scope.
>>
>> The reason I moved it into a seperate data region from a sub report is
>> because I could not find a way for the subreport to NOT "keep together".
>> Thanks for helping...
>>
>