Simple Question about number of pages displayed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Simple Question about number of pages displayed

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-07 : 12:06:45

Greetings from Borneo,

I had been tasked to create a report that displays the number of transactions per tender (Cash or Cheque) by Site.

My report uses a Select statement that selects the number of transactions and their details from one table. In my layout, I chose to display the Tender_Type; whether it is Cash or Cheque.

I did not want to display each transaction and it's ID, but rather just the total number (the 'Count') of all transactions for each site. It give the correct result at the top of the first page, but then has tens of blank pages after (1 of 30). I think it is listing all the transactions, but because I have not specified them in the layout, they are invisible!

So how do I display it so that there are no more pages than the 1 the result is on?

Thanks.

Drew

jhermiz

3564 Posts

Posted - 2005-12-07 : 13:55:24

Huh ? Show us your select ?

SELECT SUM(Blah) As The Sum GROUP BY SITE

This sums up per site....I'd need to see a report to help you.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-08 : 05:33:28

OK.

Here's the Select

SELECT DISTINCT 
                      [Payments Detail].TenderType, T_Centres.NAME AS Centre_Name, [Payments Detail].PaymentId, [Payments Detail].CentreId, 
                      [Payments Detail].PaymentDate
FROM         [Payments Detail] INNER JOIN
                      T_Centres ON [Payments Detail].CentreId = T_Centres.Id
WHERE     ([Payments Detail].TenderType = @TenderType OR
                      @TenderType = 'All') AND ([Payments Detail].PaymentDate BETWEEN @PaymentDate AND @PaymentDate2) AND 
                      (T_Centres.NAME = @CentreName) OR
                      ([Payments Detail].TenderType = @TenderType OR
                      [Payments Detail].TenderType = 'All') AND ([Payments Detail].PaymentDate BETWEEN @PaymentDate AND @PaymentDate2) AND 
                      (@CentreName = 'All')

But forget about the above for a moment.

Pretend that this is the Select:

Select TansactionID, TenderType, Site
From Details

Lets say that this returns 750 rows over 4 pages. Each rows resembles this:

TranID      TenderTpe       Site
------      ---------       ----
ID3423        Cash         London

So all I want my report to do is display the number of transactions for a user selected site.

So in the Header row (not the Detail row) of my layout, I add these to 3 seperate columns:


Column1
=Count(Fields!TranID.Value,"SportSoft") 

Column2
=(Parameters TenderType.Value)

Column3
=Parameters!Site.Value

When I run the report, it correctly returns the required result:

No of transactions         Tender Type        Site
      750                    Cash           London

So what's the problem?
The problem is the report is saying

Page 1/4.

When I look at Page 2,3 and 4, they are all blank. I think it is displaying all transactions, but because I have not specified to see each transaction in the layout, the pages are blank. I want it to display Page 1/1 with the result on 1 line at the top of the page 1, otherwise when the user tries to print/export there will be 3 blank pages.

Thanks

Drew

jhermiz

3564 Posts

Posted - 2005-12-08 : 08:14:38

Do not add it to the header row, you will need to use a group by row. Insert a group into your report and place the data there. BTW why are you using distinct in your query ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-08 : 09:44:18

quote:
BTW why are you using distinct in your query ?

Do I not need it? There were some duplicate values somewhere.
--------------------

So I have a wee book called The Relational Guide to SQL Server Reporting Services. I highly recommend against it. There is very little about Grouping in it.

quote:
Do not add it to the header row, you will need to use a group by row. Insert a group into your report and place the data there. BTW why are you using distinct in your query ?

I've inserted a Group. Going on my

Select TansactionID, TenderType, Site
From Details

example, exactly what data am I placing in it? There are expressions such as

=Fields!TenderType.Value

that I can select, but none seem to be doing anything.
Should I no longer use the Count agreggate; =Count(Fields!TranID.Value,"SportSoft") ?

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-08 : 10:58:33

I've changed the query to do the Count, as opposed to the Report doing it, and just displayed the result in the Report. It doesn't answer my question, but it works.

SELECT COUNT([Payments Detail].PaymentId) AS Number_OF_Transactions, T_Centres.NAME AS Centre_Name, [Payments Detail].TenderType
FROM [Payments Detail] INNER JOIN
T_Centres ON [Payments Detail].CentreId = T_Centres.Id
WHERE ([Payments Detail].TenderType = @TenderType OR
@TenderType = 'All') AND ([Payments Detail].PaymentDate BETWEEN @PaymentDate AND @PaymentDate2) AND
(T_Centres.NAME = @CentreName) OR
([Payments Detail].TenderType = @TenderType OR
[Payments Detail].TenderType = 'All') AND ([Payments Detail].PaymentDate BETWEEN @PaymentDate AND @PaymentDate2) AND
(@CentreName = 'All')
GROUP BY T_Centres.NAME, [Payments Detail].TenderType

Thanks for your help, Jhermiz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources