Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loading T-SQL Statements from a database


Loading T-SQL Statements from a database

Author
Message
Kevin.roberts25
Kevin.roberts25
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
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
[code="sql"]

Select a.ID, a.ColumnHeading, sum( b.SQLStatement )
inner join a.SequenceNo on b.SequenceNo


is something like this even possibe
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5681 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Kevin.roberts25
Kevin.roberts25
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
Ok I have 26 reports to create, the end result would look something like this

Description, Period1, Period2, Period3


Average Sales, 25, 45, 67
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


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)
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
Kevin.roberts25
Kevin.roberts25
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
SSRS and asp.net
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7955 Visits: 9392
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.


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44766 Visits: 39845
Kevin.roberts25 (2/20/2013)
Ok I have 26 reports to create, the end result would look something like this

Description, Period1, Period2, Period3


Average Sales, 25, 45, 67
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


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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search