SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SSRS Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  10:46:19  Show Profile  Reply with Quote
Hey Everyone,

I need to build a report that will display the client name, start date and end date ONCE at the top of the report.

the codes looks like --

----------------------------------------------------------------------(SELECT AGYID AS AccountID,
-----------------------------------------------------------------
CONVERT (VARCHAR, CAST (DATE_POST AS DATE),100) AS OrigDate,
------------------------------------------------------------------
DIST_AMT AS OrigDebtorPayment,
------------------------------------------------------------------
NET_COMM AS OrigContingencyFee,
------------------------------------------------------------------
AP AS OrigRemitted,
------------------------------------------------------------------
CONVERT (VARCHAR, CAST (DATE_PAID AS DATE),100) AS NSFDate,
------------------------------------------------------------------
(SELECT TOP 1 C.CLIENT_NAME FROM OPENITEM O WITH(NOLOCK)
INNER JOIN
DEBT D WITH(NOLOCK) ON O.DEBT_KEY = D.DEBT_KEY
INNER JOIN
CLIENT C WITH(NOLOCK) ON D.CLIENT_KEY = C.CLIENT_KEY)
AS CLIENT_N,
------------------------------------------------------------------ (SELECT TOP 1 CONVERT (NVARCHAR, CAST (I.START_DATE AS DATE),100)
FROM TRANS T WITH(NOLOCK)
INNER JOIN
INVOICE I WITH(NOLOCK) ON T.INVOICE_KEY = I.INVOICE_KEY
INNER JOIN
OPENITEM O WITH(NOLOCK) ON T.DEBT_KEY = O.DEBT_KEY)
AS S_DATE,
------------------------------------------------------------------
(SELECT TOP 1 CONVERT (NVARCHAR, CAST (I.END_DATE AS DATE),100)
FROM TRANS T WITH(NOLOCK)
INNER JOIN
INVOICE I WITH(NOLOCK) ON T.INVOICE_KEY = I.INVOICE_KEY
INNER JOIN
OPENITEM O WITH(NOLOCK) ON T.DEBT_KEY = O.DEBT_KEY)
AS E_DATE
FROM OPENITEM WITH(NOLOCK)
WHERE INV_KEY = @INV_NUMBER)



The issue is I added a column to the top of the report and since there are multiple invoices the report is bringing back its listing the client over and over, date also...is there a way to only bring back one client name and one set of dates?

Thanks,
Chris
Jr Programmer

gbritton
Flowing Fount of Yak Knowledge

1094 Posts

Posted - 08/28/2014 :  10:59:57  Show Profile  Reply with Quote
Define a report header section and put the name, date, etc. in it.
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  11:13:59  Show Profile  Reply with Quote
THE CLIENT NAME AND DATE COME FROM THE USER ENTERING THE INVOICE NUMBER, SO IT ISNT STATIC..

i AM TRYING TO SET A FILTER TO

EXPRESSION [CLIENT_N]
OPERATOR TOP N
VALUE 1

SAME FOR S_DATE AND E_DATE BUT SSRS ISNT HAVING IT

Thanks,
Chris
Jr Programmer
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1094 Posts

Posted - 08/28/2014 :  11:18:59  Show Profile  Reply with Quote
Yes, but you can put dynamic data in the header.
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  11:29:01  Show Profile  Reply with Quote
Sorry, about the caps....I do not think my report has a header, do I have to back out and re-start with a header to get the client name only to appear once? (same with s_date and e_date)

sorry to be so dense

Thanks,
Chris
Jr Programmer
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1094 Posts

Posted - 08/28/2014 :  11:43:19  Show Profile  Reply with Quote
You can add a header at any time. Right-click in the report body. You should see "Insert >" which brings up a submenu for various items, including header. The option is also in the REPORT menu at the top of the Visual Studio window for the report.
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  11:46:44  Show Profile  Reply with Quote
SSRS doesnt seem to allow me to drag a table to the header section

Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

102 Posts

Posted - 08/28/2014 :  12:09:04  Show Profile  Reply with Quote
at the end I added another dataset with the help from a upper developer, everything is working now, thanks everyone

Thanks,
Chris
Jr Programmer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000