October 25, 2011 at 5:40 am
hello all,
I am stumped at this and pulling my hair out. Below is the Stored Procedure I am using.
I am developing a Circulation report which shows how many titles are checked out for a library. Here is the problem: If a library has parents, then parent will be able to see it's chid's circulations, but it's not the other way around. A Child will only be able to see it's parents circulations.
When a library log in the website, it passes it's ID to SSRS, and then SSRS will filter circulations based on the library ID.
In libraryentity table, ID is the id for a library, and if library has parents, it exists in the ParentLibraryEntityID. It is self referencing. When a library doesn't have parents, it's treated as a Parent; if it has parents, it's treated as a child.
Now, how can I make a report so that when a library logs in, it sees it's own circulations. But when a Parents logs in, it sees it's own circulation, and it will be able to see it's child's circulations. Perhaps I can expand the circulations for child.
I tried to group these in several ways and I also tried the SSRS built in parent child relationship, but it didn't work. When I defined parent child relationshop in SSRS, it only gave me one row, that's it.
select
l.ID,
l.ParentLibraryEntityID,
p.id,
p.name,
count(*) totalcheckouts,
s.isbn,
p.authors,
s.publishername,
s.dateadded,
l.name as libraryname,
l.ParentLibraryEntityID as ConsortiaName,
ISNULL(vcr.name, 'Purchased') as collectionName,
min(vcr.[owner]) as ownername
from libraryentity l
inner join vwcheckouts vc on vc.libraryid = l.id
inner join skus s on vc.skuid = s.id
inner join product p on s.productid = p.id
left outer join SubscriptionCollections vcr on vcr.ID = vc.SubscriptionCollectionId and vc.PurchasedTitleFlag = 0
where l.id = @libraryid
and
vc.dateadded between @start And @end
Group By
l.ID,
l.ParentLibraryEntityID,
p.id,
p.name,
s.isbn,
p.authors,
s.publishername,
s.dateadded,
l.name,
l.ParentLibraryEntityID,
ISNULL(vcr.name, 'Purchased')
END
------------
🙂
October 25, 2011 at 10:54 am
Hi,
How about using a recursive cte?
Read up about it in BOL...that should work.
Martin.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply