Report to show current year revenue only if last year has no reven

I need to create a report that correctly displays ONLY accounts that have
revenue for the current year and no revenue at all for the previous year
(calendar year). The sql below does not do it.
SELECT DISTINCT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE FROM
FORD.dbo.DATA f, FORD.dbo.ACCOUNT a, FORD.dbo.DATE d
WHERE (d.YEAR = '2005') AND (d.YEAR <> '2004')
GROUP BY a.ACCTNBR, a.ACCTNAME
Please Help
--
DCounttry something like:
SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f
join FORD.dbo.ACCOUNT a on whatever
join FORD.dbo.DATE d on whatever
left outer join (
SELECT a1.ACCTNBR
FROM FORD.dbo.DATA f1
join FORD.dbo.ACCOUNT a1 on whatever
join FORD.dbo.DATE d1 on whatever
WHERE (d1.YEAR = '2004')
GROUP BY a1.ACCTNBR, a1.ACCTNAME
having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
subquery that returns all the accounts you don't want
WHERE (d.YEAR = '2005')
and oj.ACCTNBR is null --this gets rid of the unwanted accounts
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f
join FORD.dbo.ACCOUNT a on whatever
join FORD.dbo.DATE d on whatever
WHERE (d.YEAR = '2004')
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"DCount17" <DCount17@.discussions.microsoft.com> wrote in message
news:469690F5-456F-4B07-9610-29646B7A81A5@.microsoft.com...
>I need to create a report that correctly displays ONLY accounts that have
> revenue for the current year and no revenue at all for the previous year
> (calendar year). The sql below does not do it.
> SELECT DISTINCT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE FROM
> FORD.dbo.DATA f, FORD.dbo.ACCOUNT a, FORD.dbo.DATE d
> WHERE (d.YEAR = '2005') AND (d.YEAR <> '2004')
> GROUP BY a.ACCTNBR, a.ACCTNAME
> Please Help
> --
> DCount|||Thanks for your help Mary. However, I'm not sure how to use the "oj"
statement you placed in the sql statement (see below). I thought you meant
outer join, but I get an error when doing that. I'm not good at complex
queries, so I really appreciate your help. Thanks again.
having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
subquery that returns all the accounts you don't want
WHERE (d.YEAR = '2005')
and oj.ACCTNBR is null --this gets rid of the unwanted accounts
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
"Mary Bray" wrote:
> try something like:
> SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
> FROM FORD.dbo.DATA f
> join FORD.dbo.ACCOUNT a on whatever
> join FORD.dbo.DATE d on whatever
> left outer join (
> SELECT a1.ACCTNBR
> FROM FORD.dbo.DATA f1
> join FORD.dbo.ACCOUNT a1 on whatever
> join FORD.dbo.DATE d1 on whatever
> WHERE (d1.YEAR = '2004')
> GROUP BY a1.ACCTNBR, a1.ACCTNAME
> having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
> subquery that returns all the accounts you don't want
> WHERE (d.YEAR = '2005')
> and oj.ACCTNBR is null --this gets rid of the unwanted accounts
> GROUP BY a.ACCTNBR, a.ACCTNAME
> having SUM(f.REVENUE)>0
> SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
> FROM FORD.dbo.DATA f
> join FORD.dbo.ACCOUNT a on whatever
> join FORD.dbo.DATE d on whatever
> WHERE (d.YEAR = '2004')
> GROUP BY a.ACCTNBR, a.ACCTNAME
> having SUM(f.REVENUE)>0
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "DCount17" <DCount17@.discussions.microsoft.com> wrote in message
> news:469690F5-456F-4B07-9610-29646B7A81A5@.microsoft.com...
> >I need to create a report that correctly displays ONLY accounts that have
> > revenue for the current year and no revenue at all for the previous year
> > (calendar year). The sql below does not do it.
> >
> > SELECT DISTINCT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE FROM
> > FORD.dbo.DATA f, FORD.dbo.ACCOUNT a, FORD.dbo.DATE d
> > WHERE (d.YEAR = '2005') AND (d.YEAR <> '2004')
> > GROUP BY a.ACCTNBR, a.ACCTNAME
> >
> > Please Help
> > --
> > DCount
>
>

Report Server access via WebClient / HttpWebRequest

Hi,
This is a follow-up question I have to a previous post about accessing the
Reporting Services server without exposing a URL to the user that can be
tampered with.
I am using WebClient / HttpWebRequest to screen scrape a server-side
initiated request to the Reporting Service and writing the output into an
ASPX page. This works to a certain extent.
Unfortunately, when the ASPX page initiates the call, all relative URLs are
not correctly processed. For example, the usual address of the report server
might be http://brensydsql/ReportServer however, using a WebClient /
HttpWebRequest will use the hostname of the machine it's on, so relative URLs
will be relative to the local hostname instead of the report server's
hostname.
ie /images/blah.jpg would resolve as http://hostname/images/blah.jpg instead
of http://brensydsql/images/blah.jpg
Adding a <base href='http://brensydsql'> doesn't work either because Sql
Reporting services resources are only accessible during the request
processing and cannot be directly linked after the request has finished. The
Reporting Server needs to be fooled into thinking the root server is
brensydsql when the request is first initiated.
For some reason, after the report has been processed, the fully qualified
url to the image / whatever resource is no longer valid - seems like it is
only valid for the duration of the request?
I've tried to set the BaseAddress property of the WebClient object but this
has no effect.I have been looking at the same problem. I can't believe Microsoft
decided to pass the parameters unencrypted. Surely they must have
realised developers would need to have hidden and secured params.
Anyway, I digress.
Try using regular expressions to alter all of the links in the report.
The basics of this is here:
http://www.junto.co.uk/Diary/2005/10/6363e490-89a1-4f22-b811-d8d82c98acc9.aspx
Hope this helped.
Philip York wrote:
> Hi,
> This is a follow-up question I have to a previous post about accessing the
> Reporting Services server without exposing a URL to the user that can be
> tampered with.
> I am using WebClient / HttpWebRequest to screen scrape a server-side
> initiated request to the Reporting Service and writing the output into an
> ASPX page. This works to a certain extent.
> Unfortunately, when the ASPX page initiates the call, all relative URLs are
> not correctly processed. For example, the usual address of the report server
> might be http://brensydsql/ReportServer however, using a WebClient /
> HttpWebRequest will use the hostname of the machine it's on, so relative URLs
> will be relative to the local hostname instead of the report server's
> hostname.
> ie /images/blah.jpg would resolve as http://hostname/images/blah.jpg instead
> of http://brensydsql/images/blah.jpg
> Adding a <base href='http://brensydsql'> doesn't work either because Sql
> Reporting services resources are only accessible during the request
> processing and cannot be directly linked after the request has finished. The
> Reporting Server needs to be fooled into thinking the root server is
> brensydsql when the request is first initiated.
> For some reason, after the report has been processed, the fully qualified
> url to the image / whatever resource is no longer valid - seems like it is
> only valid for the duration of the request?
> I've tried to set the BaseAddress property of the WebClient object but this
> has no effect.

Report running really really slowly!!

Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.

In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services? Any ideas of how to speed this process up?

probobaly becuase in access the data is cached locally.

|||How do you have your parameters structured? Are you building the sql string dynamically? If you give a few more details/examples, I might be able to point you in a direction with less overhead...|||

I wont list all my report parameters, but to show you what I am doing I will list the first few. I have parameters:

Publication, Division, Product Manager.

Parameter Publication is just bringing back a list of publications, divison is bringing back a list of divisions where publication is in paramter publication, i.e

="SELECT
division_code
FROM base_table
WHERE publication_code in ('
" & Parameters!par_pub.Value & "
') " &
"
GROUP BY 1
ORDER BY 1"

Product Manager is bringing back a list of product managers, where publication is in parameter publication and where division is in parameter division, ie

="SELECT
product_range_manager_code,
trim(product_range_manager_desc)as product_range_manager_desc
FROM base_table WHERE division_code IN ('
" & join(Parameters!par_div.Value," ','
") & "
') " &
"

AND publication_code in ('

" & Parameters!par_pub.Value & "

') " &

"
GROUP BY 1,2
ORDER BY 1,2"

Like I said this goes on, so the 4th parameter will contain 3 WHERE conditions until the 18th parameter contains 17 where conditions. My main dataset which populates my report table looks at each of the parameters and filters based on these values. Oh also as you probably have guessed all the processing is done on the server. Hope you can help.

|||First of all, I think that you will see some speedup if you make stored procedures. Depending on the database structure, and where the bottle-neck is that speedup could be considerable or not. It seemed that you were pulling all of your data from one base table? If that is the case, you could write one stored procedure that would populate each dropdown, with a where clause of the form:

Code Snippet

WHERE foo IN (@.param1) AND (@.param2 IS NULL OR bar IN (@.param2)) AND ...

You should also verify whether there are any dependencies in your parameters that you can exploit to reduce calls to the database. In other words check if selecting a particular parameter is the same as selecting another one downstream (1 to 1), in which case that second filter (and clause) does not need to be applied at all. Similarly, verify that you need the previous filters applied once you know the downstream selections. For example, if all publications are produced by only one division, then in the second query you supplied you do not need to filter for the divison code at all.

Since you are not aggregating any columns, the group by clause is unnecessary. The SQL compiler should have caught that, so eliminating it probably won't save you anything, but it never hurts to remove unnecessary bits.

Finally, if these steps still result in a report that takes too long to refresh, you have a couple of options. You can create a drill-through report, wherein you actually navigate to a child report with fewer parameters, and you can look at improving your database structure to optimize this sort of activity, e.g. via normalization and adding indexes.

I hope this helps.