|
so, here is what I am doing so far: we have an SSRS server that is already up and running, so I put my report on that server so people in HR can get to the applications while at work. Now, we're dealing with multiple HR groups on different domains, so I want to place it on the web server so everyone can hit it but I am lost on how to configure SSRS to run on the run on the server. I can get it installed and up, but when I change the urls/virtual directories for SSRS it all goes to pot. What am I doing wrong?
|
|
|
|
|
Hello CP,
I've got a package in Sql Server 2005 (Integrated Services) and had a question about the useage of parameters.
I perform a right click on my package and pick 'Run Package'.
I go to the Set Values tab and put in my parameters like this:
Property path | Value
\package.variables[Dir].Value | C:\DBExports\
\package.variables[Month].Value | 4
Here's my problem:
I want to use a single package to export the current and past month.
Is there a datetime function or macro I can call in the Value field?
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
|
|
|
|
|
Please can anyone assist me with the following questions?
1. Given the following table (the data could be hundreds of rows):
CompanyID CompanyName Town Country CompanySize
1 CompanyA Maryland USA 10
2 CompanyB Maryland USA 19
3 CompanyC Maryland USA 20
4 CompanyD Texas USA 13
5 CompanyE Texas USA 40
6 CompanyE Florida USA 4
For those towns which have more than one company in them, calculate (in one query):
· The total number of employees in the town.
· The average number of employees in a company (per town).
· The average number of employees in a company (per town) only for companies which
have less than 20 employees.
The results should exclude Florida and be sorted in descending order of the total number of
employees in the town.
I will be very grateful for your professional response.
Regards,
Current
Addendum
I forgot to mention that CompanySize is the number of employees
Thanks for your observation.
modified on Saturday, June 25, 2011 7:20 AM
|
|
|
|
|
current1999 wrote: · The total number of employees in the town.
Based on this point, I can't see any employee information, I guess exists a Employee table which is related with your sample,right?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Hi Blue_Boy,
Did you see my addendum?CompanySize is the number of employees.
Cheers
|
|
|
|
|
Look into using Group By Town and Sum(CompanySize) these will allow you to get the result you need. You may have yo do some filtering after the sum in which case look into Having sum() > #n
You weren't expecting someone to actually write the query for you were you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks.
Your propositions have been tested without success.
Since the sample data is given, if can supply full query, there is no sin!
Cheers
|
|
|
|
|
If you need the code written for you then trundle across to rentacoder, we support people who want to learn how to write the code, NOT people who want us to do their work for them.
current1999 wrote: Your propositions have been tested without success.
Then show us what you have tried and we may be able to help you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
May be you allow others to contribute if you can't soften your words a bit,You footnote speaks for you!
cheers
|
|
|
|
|
Take note of the vaote applied to the messages in this thread, others are contributing. Also note that you are not getting a response that supplies you with the codz, this should also tell you that you are asking the wrong question or are on the wrong site.
We are here to help developers, not supply free services. Either do your work or be prepared to pay someone to do it for you. Oh and if you are doing this for your own entertainment then thank you, I find it entertaining education you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Agreed. The question screamed "homework" when I looked at it. Don't know (but might guess) whi univoted, but have a reasonably heavy 5.
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Looking at the balance I'd say the sultana just worked out that he can vote, thanks!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The question has "homework" written all over it. I notice he did eventually find a sucker willing to do it for him for free.
|
|
|
|
|
try this
select mt.town , sum(mt.CompanySize) as TotalNrOfEmployees,
sum(mt.CompanySize)/(select count(*) from mytable) as AverageEmployeesPerTown,
sum(mt.CompanySize)/(select count(*) from mytable where companysize<20) as AverageEmployeesPerTownLessThen20
from mytable mt
group by mt.town
having (sum(mt.CompanySize) > 20)
order by ( sum(mt.CompanySize)) desc
This will give you result as
<br />
Town TotalNrOfEmployees AverageEmployeesPerTown AverageEmployeesPerTownLessThen20<br />
Texas 53 8 13<br />
Maryland 49 8 12
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks a million Blue_Boy.
It is good there is an exceptional person like you. Some have no clues, only to be throwing DIATRIBES.
Thanks a million Blue_Boy for sharing your knowledge.
Regards
modified on Monday, July 18, 2011 4:08 AM
|
|
|
|
|
Hope this helps
Input:
Declare @t table(CompanyID int identity, CompanyName varchar(20), Town varchar(20), Country varchar(20), CompanySize int)
insert into @t
select 'CompanyA', 'Maryland', 'USA', 10 union all
select 'CompanyB' , 'Maryland', 'USA', 19 union all
select 'CompanyC', 'Maryland', 'USA', 20 union all
select 'CompanyD' , 'Texas', 'USA', 13 union all
select 'CompanyE', 'Texas', 'USA', 40 union all
select 'CompanyE', 'Florida', 'USA' ,4
Select * from @t
Query:
Select
t2.Town
, t1.[Total Employees In the Town]
, t2.[Avg No of employees(per town)]
, t2.[AVG Less than 20]
from
-- Query1 : The total number of employees in the town
(
Select
[Total Employees In the Town] = SUM(a.CompanySize)
from @t a
join (Select Town From @t Group by Town Having Count(Town) > 1 ) x
on a.Town = x.Town
) t1
left join
(
Select
a.Town
,a.[Avg No of employees(per town)]
,b.[AVG Less than 20]
from
(
-- Query 2: The average number of employees in a company (per town).
Select Town,[Avg No of employees(per town)] = SUM(CompanySize)/COUNT(Town)
From @t
Group by Town Having Count(Town) > 1 ) a
join (
-- Query 3
-- The average number of employees in a company (per town) only for companies which
-- have less than 20 employees
Select a.Town, [AVG Less than 20] = SUM(CompanySize)/Count(a.Town)
from @t a
join (Select Town From @t Group by Town Having Count(Town) > 1 ) x on a.Town = x.Town
where a.CompanySize < 20
group by a.Town) b
on a.Town = b.Town
) t2 on 1 = 1
Output:
Town Total Employees In the Town Avg No of employees(per town) AVG Less than 20
Maryland 102 16 14
Texas 102 26 13
Thanks
Niladri Biswas
|
|
|
|
|
Hi Niladri,
Your answer and that of Blue_boy have really expanded my thoughts.First I am very grateful. Meanwhile, the results from the two queries are different. The problem lies in the filtering.
Sincerely, I am really grateful.I will work further on your posted queries.
Regards,
Current
|
|
|
|
|
Hello CP,
I'm messing around with SSIS and have run into a problem when trying to get the DataReader Source to work.
The problem is simple;
I'm trying to set the SqlCommand property to point to a stored procedure:
EXECUTE [dbo].[spMyProc] '20110101', '20110131'
The problem does not lie in calling the SP but rather with something in the SP.
My SP makes use of Temp table and it seems it cannot find it:
Error at Data Flow Taks[DataReader Source[1]]: System.Data.SqlClient.SqlException: Invalid object name '#tmpCalcTable'.
at System.Data.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
The Stored Procedure creates a temporary table and later deletes it, but it seems it's either not doing that or can't access the it.
I've tried making the temp table global by using a double hash (##) instead of one but without luck.
Does anyone have an idea how I can fix this?
|
|
|
|
|
|
The error you're getting back from SQL suggests that there's a temp table being referenced that is either not created, is dropped before it is called, or is otherwise orphaned somehow in the code. Can you run the SP through code (in SSMS) with the appropriate input parameters and it will execute correctly? If so, then it's something in your .NET code or some other setting/parameter within the SSIS package. If not, can you post your TSQL code? Thanks
modified on Friday, June 24, 2011 8:22 AM
|
|
|
|
|
It works fine in the Server manager.
I've got a workaround though, create the temp table once (outside of the query) and truncate it at the start of the query. This also means I'm no longer dropping it.
It's a strange little bug though.
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
|
|
|
|
|
Ok, cool. Glad to assist if you should need further assistance. 
|
|
|
|
|
This may be a stupid question but is there a way that I can a statement in mySQL.
I need to change how the SHOW statement works.
I realize I could do this with a different function of my own but I am needing to use the existing function if possible.
Any help is appreciated.
Humble Programmer
|
|
|
|
|
What excately is your question?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Can I change what the SHOW statement does....
For example it runs a certain SQL statement that is defined somewhere in mySQL.
I wonder can I change that statement to do something different or maybe make it call a different statement.
Humble Programmer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
