February 19, 2013 at 4:37 pm
I want to call all of my heading from table A and call my select statements from Table B.
TABLE A
ID int ,
SequenceNo int,
ColumnHeading varchar(255)
TABLE B
ID int,
SequenceNo int,
SQLStatement nvarchar(500)
This is what I want to happen
the sql statement in table b =
select count(somecolumn) from someTable [/code
Select a.ID, a.ColumnHeading, sum( b.SQLStatement )
inner join a.SequenceNo on b.SequenceNo
is something like this even possibe
February 20, 2013 at 1:23 am
I think part of it may be lingo usage, but I'm unfortunately not really sure what you're trying to do here.
Can you explain what you'd like to do in plain english as a business requirement? Maybe we can turn it into tech-speak afterwards. I for one am lost as to your intent.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 20, 2013 at 2:09 am
Ok I have 26 reports to create, the end result would look something like this
Description, Period1, Period2, Period3
Totals Team 1, 53, 74, 27
Totals Team 2, 36,49, 35
Totals Team 3, 36,49, 35
Totals Team 4, 36,49, 35
for the descriptions of all the report rows I have stored them in a table TABLE A
ID INT,
ReportNo int,
LineSequence int, -- This is the order I want the description to be on the report
Description varchar(255) -- examples Totals Team 1
to calculate this what I want is the following. I will be loading the descriptions by passing the report number and ordering by Description. I want to call the rellevant sql queries stored in a table that will allow me to
Description, Period1, Period2, Period3
Totals Team 1,(sql query from database where @ReportNo =1 and @LineSequence=2 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 2, (sql query from database where @ReportNo =1 and @LineSequence=3 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 3,(sql query from database where @ReportNo =1 and @LineSequence=4 and @Period=1 ),(sql query from database), (sql query from database)
Totals Team 4,(sql query from database where @ReportNo =1 and @LineSequence=5 and @Period=1 ),(sql query from database), (sql query from database)
February 20, 2013 at 10:59 am
What are you using to create the report? That's the key here.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 20, 2013 at 5:17 pm
SSRS and asp.net
February 20, 2013 at 6:23 pm
Do you have to do header and body in the same query? I haven't worked much with SSRS. If you create two queries and feed the same parameters to them, that should work. Create one query for the header and one for the body.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 21, 2013 at 1:40 am
You will need to build a defined dataset using dynamic sql to union your statements or build a temp table and insert data interatively.
If your dataset contains the columns ID,SequenceNo,ColumnHeading and ColumnValue then you can build a matrix in SSRS with ID for the row, ColumnValue for the column (grouped on SequenceNo) and ColumnHeading for the column heading.
Far away is close at hand in the images of elsewhere.
Anon.
February 26, 2013 at 9:54 pm
Kevin.roberts25 (2/20/2013)
Ok I have 26 reports to create, the end result would look something like thisDescription, Period1, Period2, Period3
Average Sales, 25, 45, 67Totals Team 1, 53, 74, 27
Totals Team 2, 36,49, 35
Totals Team 3, 36,49, 35
Totals Team 4, 36,49, 35
for the descriptions of all the report rows I have stored them in a table TABLE A
ID INT,
ReportNo int,
LineSequence int, -- This is the order I want the description to be on the report
Description varchar(255) -- examples Totals Team 1
to calculate this what I want is the following. I will be loading the descriptions by passing the report number and ordering by Description. I want to call the rellevant sql queries stored in a table that will allow me to
Description, Period1, Period2, Period3
Average Sales, (sql query from database where @ReportNo =1 and @LineSequence=1 and @Period=1 ), (sql query from database), (sql query from database)Totals Team 1,(sql query from database where @ReportNo =1 and @LineSequence=2 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 2, (sql query from database where @ReportNo =1 and @LineSequence=3 and @Period=1 ), (sql query from database), (sql query from database)
Totals Team 3,(sql query from database where @ReportNo =1 and @LineSequence=4 and @Period=1 ),(sql query from database), (sql query from database)
Totals Team 4,(sql query from database where @ReportNo =1 and @LineSequence=5 and @Period=1 ),(sql query from database), (sql query from database)
This is known as a "Crosstab" or "Pivot". Please see the following articles for high performance solutions on the subject.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
If you'd like a specific coded answer, please see the article at the first link below.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply