Nested Groups with Two datasources? Noob design approach question.

I've got these two tables

FileLog:

ProcessKey

Filename

EventLog:

ProcessKey

Eventtype

Date

Both tables can be related by Processkey key, but there data does not

relate. Anwhere from 5 to 10 Filelog rows per ProcessKey.

Anywhere from 5 to 200 Eventlog rows per ProcessKey.

Filelog contains Filenames that make up a Process Key batch, and Eventlog contains rows of events for that ProessKey Batch.

I'd like a report where Filelog enteries are in my group header by

Process Key, followed by details with Enventlog by ProcessKey..

something like this:

ProcessKey1:

filename1,filename2,filename3

filename4 (wraping and growing a cell)

Eventtype Date

Eventtype Date

Eventtype Date

Eventtype Date

ProcessKey2:

filename4,filename5,filename6

filename7 (wraping and growing a cell)

Eventtype Date

Eventtype Date

Eventtype Date

Eventtype Date

How do I do this? If the grid only allows for one datasource?

Nested Groups? Two grids with an expression connecting them?

Thanks for any help or information.

The only way to nest two independent query is via subreports. However, why can't you simply join the filelog and event log in the same query?

The one thing you can't do is the concatenated file name. This would be considered a custom aggregate. You could probably create this string in your query.

|||

I thought about joining the tables in a view or on the report, but it would be kindof an ugly join with up 20 files per Processid and up to 200 entries in the event log per Processid. I'm not sure a join make sense .. every filelog entry under the processid would be the header for for all events under that process id.

Question about subreports, Can I have a subreport that serves as the header for my eventlog group? Can that subreport use processid from the group break?

Thanks.

Need to store duplicate values to DB

Hi,

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON

DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost,
TagID=@.TagID
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID
END

ELSE

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

VALUES(
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)

SET NOCOUNT OFF

this is the one to clear:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spCRMPublisherSummaryClear](
@.SiteID INT,
@.DataFeedID INT,
@.ReportDate SMALLDATETIME) AS

DELETE LandingSiteSummary
WHERE SiteID=@.SiteID AND ReportDate=@.ReportDate

but it doesnt seem to be working.

Please suggest.

avidyarthi:

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times.

you cannot repeat a value in a column that is set as your primary key. If you need to repeat values in that column, then you need to remove its designation as your primary key.

|||

hey,

thanks i worked my way around it.