January 6, 2016 at 9:56 pm
I have created a report which gets emailed to users each week as follows:
[font="Arial Narrow"]Use Reporting
SELECT
--Campaign organistion Data
GC.[CampaignID] as Campaign_CampaignID
,GC.[OrganisationName] as Campaign_OrganisationName
,GC.[CampaignName]as Campaign_CampaignName
--Donor Data
,D.[UniqueID] as Donor_UniqueDonorId
,D.[PPNo] as Donor_PPNo
,D.[CampaignID] as Donor_CampaignID
,CASE WHEN D.[Surname] IS Null THEN '' else D.[Surname] end AS Donor_Surname
,CASE WHEN D.[BusinessName] IS Null THEN '' else D.[BusinessName] end AS Donor_BusinessName
--Transaction Data
,GT.[CodeNumber]
,GT.[Transprocessdate]
,GT.[Transamount]
,GT.[Transcode]
--,GT.[TransPPNarrative]
FROM [Reporting].[dbo].[GiveCampaign] as GC
inner join dbo.DonorsPPs as D
on D.CampaignID = GC.CampaignID
inner join GiveTransactions as GT
on GT.TransPPNarrative = D.PPno
where Transprocessdate <= (@EndDate) and Transprocessdate >= (@StartDate)and transcode like '2A' and (d.PPNo like @PPNo)or
Transprocessdate < (@EndDate) and Transprocessdate >= (@StartDate) and transcode like '6B' and (d.PPNo like @PPNo)
order by GC.OrganisationName, D.UltracsPPno, GT.Transprocessdate [/font]
The report has the Organisation name and campaign name etc in the header and then a table with the list of transactions. The report also has a Title "Give Transactions" (in a text box). If there are no transactions that match the Start Date, End Date, transcode and PPno then the report is blank except for the "Give transactions" text box. How can I either still keep the table with no transactions listed or place a comment instead of the table "No transactions for the period". If there are no transactions the report does not even show the Organisation, Campaign name, etc.
Have been racking my brain and cannot find a solution.:w00t::w00t::w00t:
January 7, 2016 at 2:49 am
I'd suggest you break the report into a header report and a subreport for the transactions.
You can still have the date parameters etc in the header report, you just pass them from there to the subreport.
The header dataset would just be the campaign names so would not be filtered by your dates and therefore would always return the details.
You can take a look at the NoRowsMessage property for the subreport to return a message if there are no transactions.
January 7, 2016 at 2:45 pm
Thank you for the suggestion.
I have actually come up with a different solution
[font="Comic Sans MS"]Use Reporting
SELECT
--Campaign organistion Data
GC.[CampaignID] as Campaign_CampaignID
,GC.[OrganisationName] as Campaign_OrganisationName
,GC.[CampaignName]as Campaign_CampaignName
--Donor Data
,D.[UniqueID] as Donor_UniqueDonorId
,D.[PPNo] as Donor_PPNo
,D.[CampaignID] as Donor_CampaignID
,CASE WHEN D.[Surname] IS Null THEN '' else D.[Surname] end AS Donor_Surname
,CASE WHEN D.[BusinessName] IS Null THEN '' else D.[BusinessName] end AS Donor_BusinessName
--Transaction Data
,GT.[CodeNumber]
,GT.[Transprocessdate]
,case when (Transprocessdate <= (@EndDate) and Transprocessdate >= (@StartDate)and transcode like '2A' or
Transprocessdate < (@EndDate) and Transprocessdate >= (@StartDate) and transcode like '6B') then GT.Transamount else '0.00' end as Transamount]
,GT.[Transcode]
--,GT.[TransPPNarrative]
FROM [Reporting].[dbo].[GiveCampaign] as GC
inner join dbo.DonorsPPs as D
on D.CampaignID = GC.CampaignID
inner join GiveTransactions as GT
on GT.TransPPNarrative = D.PPno
where (d.PPNo like @PPNo)
order by GC.OrganisationName, D.UltracsPPno, GT.Transprocessdate[/font]
and then used the Hide expression in the ROW VISIBILITY of '=IIF(Fields!Transamount.Value = 0, True, False)'
It now allows me to create statements/reports that all look the same except the rows of transactions.
I had not realised I could use parameters in the list of data section. 😀
January 7, 2016 at 2:48 pm
Hope it's not for Trump's campaign. 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 7, 2016 at 3:06 pm
Alvin, No I'm from Australia and it is for a donation campaign for a church.:-)
January 7, 2016 at 6:39 pm
Whatever, just be aware if you still have no transactions at all in the GiveTransactions table you won't get a result back as you are inner joining on it. Right now you are relying on there being transactions of types other than 2A and 6B to get rows back if there are no 2A or 2B transactions. Maybe there always will be, but you may need to left join or something.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply