Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What
I
need is to return an additional row when the earliest dtmready is after toda
y.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query ar
e
returned to a Java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a Java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>
>[quoted text clipped - 52 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via droptable.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Monday, March 26, 2012
Need Extra Rows
Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What I
need is to return an additional row when the earliest dtmready is after today.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query are
returned to a java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>> Here is the basic sql I am trying to implement:
>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via SQLMonster.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
>>Hi
>>The easiest way to do this is to use a calendar table see
>>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>>CROSS JOIN dbo.calendar c
>>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>>u.rmpropid = '123'
>>GROUP BY v.classid, c.dt
>>ORDER BY c.dt
>>The cross join will get all date and classid combinations.
>>John
>> Here is the basic sql I am trying to implement:
>>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each
>> classid
>> that has a minimum dtmready > today.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>> Hi John. Thanks for the response. I do not think I will be allowed to
>> create
>[quoted text clipped - 27 lines]
>> classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What I
need is to return an additional row when the earliest dtmready is after today.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query are
returned to a java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>> Here is the basic sql I am trying to implement:
>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via SQLMonster.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
>>Hi
>>The easiest way to do this is to use a calendar table see
>>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>>CROSS JOIN dbo.calendar c
>>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>>u.rmpropid = '123'
>>GROUP BY v.classid, c.dt
>>ORDER BY c.dt
>>The cross join will get all date and classid combinations.
>>John
>> Here is the basic sql I am trying to implement:
>>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each
>> classid
>> that has a minimum dtmready > today.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>> Hi John. Thanks for the response. I do not think I will be allowed to
>> create
>[quoted text clipped - 27 lines]
>> classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
Monday, March 19, 2012
Need all rows within each group to be shown on 1 page
Hi,
I have data that is grouped by a code number. One of the code numbers have over 600 rows, while other code numbers have around 10 to 20 rows within it.
When I run the report the code number that has over 600 rows gets split over 2 pages while the other code numbers each get their own page.
How do I make it so that when I run the report the code number that has over 600 rows gets all displayed on the 1 page instead of being split over 2?
Thanks
Ben.
can anyone help please?|||Hi ben22,
I'm afraid there is not really a functionality in Reporting Services that does this. I know this is a lack in Reporting Services.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi Ben
Do you just want it to display on 1 page in the browser or
do you require this for printing aswell?
If it is for viewing purposes only, you can try to set the
'Interactive Page size' in the report properties to have a higher width.
G
|||need it both for display in the browser and for printing, as when it gets exported to excell it needs to be in the same sheet instead of being expanded over 2. This is because a certain value in the row gets summed at the end and I need that sum to include all the rows within the group, as when it splits over 2 pages I get two different sums where they should be altogether.|||Hi ben,
As mentioned before, it is not possible to make the page size dynamic for printing and viewing at the same time.
You mentioned that you receive 2 sum results. It is possible to only have only a total line at the end in case of on each page.
For example:
When you use a Table you can set the total in the Table footer. Even if the table overlaps multiple pages, it will only be displayed at the last page.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Sorry, I should of explain things a bit better before. By groups I meant I have 1 group in the table. This group breaks the data up by the unique field assigned to each data item and uses a group footer to do the sum. For example:
I have 800 cars, 600 are red 100 are blue and 100 are green. So when I run the report is looks like this
Page 1
RED
Name | Model | Price
Car1 | c1 | 20000
Car2 | c2 | 30000
…
Car400 | c400 | 15000
-
Total Price: 3000000
Page 2
RED
Name | Model | Price
Car401 | c401 | 20000
Car402 | c402 | 30000
…
Car600 | c600 | 15000
-
Total Price: 1500000
Page 3
BLUE
Name | Model | Price
Car1 | c1 | 10000
Car2 | c2 | 30000
…
Car100 | c100 | 40000
-
Total Price: 1000000
Page 4
GREEN
Name | Model | Price
Car1 | c1 | 25000
Car2 | c2 | 17000
…
Car100 | c100 | 40000
-
Total Price: 1200000
As you can see there are 4 pages. It splits the RED cars up over 2 pages giving the 2 different sums ie: cars 1 – 400 with the sum 3000000 and then cars 401 – 600 with the sum 1500000. I want my report to work so that the RED cars are all together and not on different pages, so altogether there are only 3 pages. So to look like this:
Page 1
RED
Name | Model | Price
Car1 | c1 | 20000
Car2 | c2 | 30000
…
Car600 | c600 | 15000
-
Total Price: 4500000
Page 2
BLUE
Name | Model | Price
Car1 | c1 | 10000
Car2 | c2 | 30000
…
Car100 | c100 | 40000
-
Total Price: 1000000
Page 3
GREEN
Name | Model | Price
Car1 | c1 | 25000
Car2 | c2 | 17000
…
Car100 | c100 | 40000
-
Total Price: 1200000
|||
Hi,
It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Geert Verhoeven wrote:
It is not possible to force a group on 1 page but it is possible to have only 1 total per group. You need to set the Color field in the expresion of the group. When you then look at the group footer, you will see that the total is done on group base.
Sorry but I dont really understand what you have said or how to implement it
|||Hi,
What I mean is that you can not force the contents of a group to be on 1 page. Although the problem you have that the total is displayed on each page can be avoided by using the footer of the group. When you set the total in the group of the footer, only at the end of the group a total will be displayed and not at the end of each page.
Hope this is more clear.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||im using a group a footer and it still displays the total at the end of each page, not at the end of the groupMonday, February 20, 2012
NDF File in a Secondary File group was lost
Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in
*.mdf?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup.
I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
>Assuming you didn't get my reply due to ng problems, here's a reply:
You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:%23npbmhPiDHA.752@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in
> *.mdf?
>
>
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > > claudio
> > > Do you have a last backup of your database?
> > > Please refer to BOL for more details about how to perfom restore
> > filegroups
> > > and files.
> > >
> > >
> > > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > > Hi people,
> > > > I have a big problem here. I have one database with the following
> > > > configuration:
> > > > 1- Primary FileGroup - teste.mdf
> > > > 2- Secondary FileGroup - teste.ndf
> > > >
> > > > My trouble is: I lost my datafile that belongs to Secondary FileGroup.
> I
> > > > don´t need to recover my data in teste.ndf, but my database was in
> > > Suspect
> > > > State and I need to change to Online State.
> > > > What Should I do?
> > > >
> > > > thanks
> > > >
> > > >
> > >
> > >
> >
> >
>
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in
*.mdf?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup.
I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
>Assuming you didn't get my reply due to ng problems, here's a reply:
You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:%23npbmhPiDHA.752@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in
> *.mdf?
>
>
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > > claudio
> > > Do you have a last backup of your database?
> > > Please refer to BOL for more details about how to perfom restore
> > filegroups
> > > and files.
> > >
> > >
> > > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > > Hi people,
> > > > I have a big problem here. I have one database with the following
> > > > configuration:
> > > > 1- Primary FileGroup - teste.mdf
> > > > 2- Secondary FileGroup - teste.ndf
> > > >
> > > > My trouble is: I lost my datafile that belongs to Secondary FileGroup.
> I
> > > > don´t need to recover my data in teste.ndf, but my database was in
> > > Suspect
> > > > State and I need to change to Online State.
> > > > What Should I do?
> > > >
> > > > thanks
> > > >
> > > >
> > >
> > >
> >
> >
>
NDF File in a Secondary File group was lost
Hi people,
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
navigation on subtotal
I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".
Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.
I have not been able to override the subtotal to specify a new link with different paramters.
Anybody can help would be great!
Thanks.
In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.
Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...
I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.
If you can give me some instructions that would be great.
Thanks.
Jason
|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||
Use the InScope function as described earlier in this thread.
For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.
-- Robert
|||Hi, Robert,
The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:
co1 col2 col3 subtotal
row1 3 5 7 15
row2 1 2 3 6
subtotal 4 7 10 21
3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.
Thanks!
Henry
|||Yes, that should work fine.
For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).
Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.
Hope this clarifies it.
|||Hi, Robert,
It works now. Thanks!
The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.
Henry
|||hi,
can u please send me the query you used.
and please also gimme a explanation if possible.
Thanks in advance,
Vinu
navigation on subtotal
I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".
Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.
I have not been able to override the subtotal to specify a new link with different paramters.
Anybody can help would be great!
Thanks.
In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.
Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...
I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.
If you can give me some instructions that would be great.
Thanks.
Jason|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||
Use the InScope function as described earlier in this thread.
For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.
-- Robert
|||Hi, Robert,
The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:
co1 col2 col3 subtotal
row1 3 5 7 15
row2 1 2 3 6
subtotal 4 7 10 21
3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.
Thanks!
Henry
|||Yes, that should work fine.
For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).
Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.
Hope this clarifies it.
|||Hi, Robert,
It works now. Thanks!
The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.
Henry
|||hi,
can u please send me the query you used.
and please also gimme a explanation if possible.
Thanks in advance,
Vinu
Subscribe to:
Posts (Atom)