Jump to URL

If I select this item and enter some value into the field, should this field
automatically be formatted as a hyperlink or do I need to manually format
the field.
Thanks,You need the fully formed URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:eA16ZPo3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> If I select this item and enter some value into the field, should this
> field automatically be formatted as a hyperlink or do I need to manually
> format the field.
> Thanks,
>

Jump to a group section of a subreport

I have a parent report, whenever the user click a item, the user will jump
from parent report to a corresponding section of subreport like a group
section.
How do I get such function?
Thanks for your kind help.I suggest instead of a subreport, implement this a a drill through. I.e.
when they click on it you jump to the report. Modify your subreport to
include another parameter so that the report shows just the subset of data
you want. This is very powerful and users understand it quickly. Drillthough
is a great way to present data to the user.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:C9B8B574-EFBD-4790-9914-A2192BDF3C73@.microsoft.com...
> I have a parent report, whenever the user click a item, the user will
jump
> from parent report to a corresponding section of subreport like a group
> section.
> How do I get such function?
> Thanks for your kind help.

Joining Two Measure Groups

Hey all,

Our product structure is:

Category

Class

Subclass

Item

We have 1 measure group by category, and another measure group by item. So we are forced to join by Category. Which works fine except for whereever we join on Category some reason the measures get rolled down all the way to the Item level.

Example:

Category Sales

00001 50.75

Product Measure Group 1 Measure Group 2
Category 00001 50.75 50.75

Class 00002 0.00 50.75

Subclass 00003 0.00 50.75

Item 00004 0.00 50.75

It's important to note Measure Group 1 is ONLY at the category level. Some reason it rolls down when to joni to Measure Group 2? Any ideas how to prevent this?

Thanks a lot.

Hello! I have written a short post about this problem on my blog: http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!381.entry

My example is from the Adventure Works project but the way to solve this should work for your problem also.

HTH

Thomas Ivarsson

|||

Perfect - thanks!

Join to select a 'weighted' column

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @.inv table (item varchar(50), supplierid int)

declare @.supplier table (supplierid int, weight int)

set nocount on

insert into @.inv values ('item1', 1)

insert into @.inv values ('item1', 2)

insert into @.inv values ('item2', 2)

insert into @.inv values ('item2', 3)

insert into @.supplier values(1, 30)

insert into @.supplier values(2, 20)

insert into @.supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @.supplier ps2 join

(select item, min(ps.weight)'weight'

from @.inv inv join @.supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
group by i.item
) as si

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike

Join Query In SQL Server

Hi All,
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/ArticleID/15608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>

Join only returns the read rows :|

Hi all,
I am trying to build a association table (t2) to store a list of usershave viewed an item in my records table (t1). My goal is to send theUserID parameter to the query and return to the user a read / not readmarker from the query so I can handle the read ones differently in my.net code. The problem is that I cannot work out how to return anythingbut the read data to the client. So far my stored proc looks like this
DECLARE @.UserID AS Int -- FOR TESTING
SET @.UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. Ineed to return the records with null values also! I may have built theassoc table wrong and would really appreciate some pointers on what Iam doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks

Instead of this:

LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID

Do this:

LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID AND t2.UserID = @.UserID

Placing the t2.UserID = @.UseriD in the WHERE limits your results toonly those rows where there is a match in t2. You could havealternately coded it as:

WHERE t2.UserID = @.UserID OR t2.UserID IS NULL

|||Thank you ever so much!
I was racking my brains on how to get around the WHERE limitation I had imposed.
Easy when you see how.
Thank you again!