Crosstab type report -- Horizontal Summray

Question

Crosstab type report -- Horizontal Summray

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 1

Not yet Jeff. Between the syntactical nightmares I'm coming across (creating the sp) and the fact that I can't come up with a good way to present it to the users, I'm stuck..

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Ok... let's peel one potato at a time. Be gentle because I'm a data troll and don't even know how to spell things like ".adp". 😛

First, what parameters would you attempt to pass to the stored procedure?

Second, if you're using ".adp", tell me what that has to do with "VBA" (like I said, be gentle because I really don't know).

Third, I believe we can trick VPA into doing all sorts of neat things, but let's work on the first item first.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 3

Jeff:

First and foremost, thank you very much for the help you have been providing me. It is helping me to both learn more and have a stronger grip on my sanity.

I'm sorry for not getting back to you earlier. I was on vacation for awhile, amongst other things.

Here is the code as it stands right now. I'm trying to turn this into a stored procedure and pass 2 parameters to the stored procedure: Test Plan and Product. The Test Plan parameter, I already started and it is pretty straightforward. The Product parameter is a little more difficult (for me :-)). Any where you see "tblVUL_CYBmo" is where I will use it. Basically I need to concatenate a string with the product "VUL". So something like tbl + @Product + _CYBmo.

True to your analogy, this is the first potato that needs peeling.

Thanks again for the help you have provided and I look forward to hearing back from you.

USE Life

--===== Declare some variables to hold the different parts of the dynamic SQL

DECLARE @SQLSELECT VARCHAR(MAX),

@SQLCrossTab VARCHAR(MAX),

@SQLFrom VARCHAR(MAX),

@TestPlan VARCHAR(MAX)

SET @TestPlan = 'Rel 9.2 AD108 VUL'

--===== Create the " SELECT" SQL

SELECT @SQLSELECT = ' SELECT tblVUL_CYBmo.txtPolNum AS PolicyNum,' + CHAR(10)

--================================================================================================================================

--Begining of the real transaction grid.

----===== Create the multi-section, multi-line cross tab SQL.

--================================================================================================================================

SELECT @SQLCrossTab = ISNULL(@SQLCrossTab + ',' + CHAR(10) + SPACE(8), SPACE(8))

+ 'MAX(CASE WHEN txtCycle = ' + CAST(txtCycle AS VARCHAR(3)) + ' THEN txtTransaction END) AS Transaction_Cycle_'

+ CAST(txtCycle AS VARCHAR(3)) + ',' + CHAR(10) + SPACE(8)

+ 'MAX(CASE WHEN txtCycle = ' + CAST(txtCycle AS VARCHAR(3)) + ' THEN curTransAmt END) AS TransAmt_Cycle_'

+ CAST(txtCycle AS VARCHAR(3)) + ',' + CHAR(10) + SPACE(8)

+ 'MAX(CASE WHEN txtCycle = ' + CAST(txtCycle AS VARCHAR(3)) + ' THEN dtmTransDate END) AS TransDate_Cycle_'

+ CAST(txtCycle AS VARCHAR(3))

FROM tblConditions INNER JOIN

tblVUL_CYBmo ON tblConditions.lngMOnumCond = tblVUL_CYBmo.lngMONum

WHERE (tblVUL_CYBmo.txtTestPlan = @TestPlan)

GROUP BY txtCycle

ORDER BY txtCycle

--===================================================

--Create the From Statement

--===================================================

SELECT @SQLFrom = '

FROM tblConditions INNER JOIN tblVUL_CYBmo ON tblConditions.lngMOnumCond = tblVUL_CYBmo.lngMONum

WHERE (tblVUL_CYBmo.txtTestPlan = '''+ @TestPlan + ''')

GROUP BY tblVUL_CYBmo.txtPolNum

ORDER BY tblVUL_CYBmo.txtPolNum'

----===== Display the SQL that will be executed (can be commented out for production, of course)

PRINT @SQLSELECT + @SQLCrossTab + @SQLFrom

--

----===== Execute the SQL to give the desired result.

EXEC (@SQLSELECT + @SQLCrossTab + @SQLFrom)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

That certainly complicates the cross tab section of the code... lemme see what I can phinger out...

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Jackson SSCertifiable Points: 6414 More actions · Answer 5

I know I'm late to the party but here is how i would call a stored proc with parameters in vba

Sample Proc

alter procedure usp_testProc

@param1 int = NULL,

@param2 int = NULL,

@param3 int = NULL

As

select @param1 testOut

unionall

select @param2

unionall

select @param3

go

Sample VBA script, pass parameters to it on the command line. eg. Cscript TestSPCall.vbs 12 344 7645

[font="Courier New"]

Dim dbc, rs, sqlQueryText, svrName, dbName

Dim arg0, arg1, arg2

svrName = "." ' (Local)

dbName = "DBA" ' Insert real db name here

sqlQueryText = "exec usp_testProc "

'WScript arguments are zero based

If WScript.Arguments.Count > 0 Then arg0 = WScript.Arguments.item(0)

If WScript.Arguments.Count > 1 Then arg1 = WScript.arguments.item(1)

If WScript.Arguments.Count > 2 Then arg2 = WScript.arguments.item(2)

If Len(arg0) > 0 Then

sqlQueryText = sqlQueryText & " " &arg0

End If

If Len(arg1) > 0 Then

sqlQueryText = sqlQueryText &", " &arg1

End If

If Len(arg2) > 0 Then

sqlQueryText = sqlQueryText &", " &arg2

End If

Set dbc = CreateObject("ADODB.Connection")

dbc.CommandTimeout = 180

dbc.Open = "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=" &svrName &";Database=" &dbName &";"

Set rs = CreateObject("ADODB.Recordset")

rs.ActiveConnection = dbc

rs.CursorLocation = 3 'adUseClient

rs.LockType = 3 'adLockOptimistic

rs.Open sqlQueryText

If rs.RecordCount = 0 Then

WScript.Echo "Recordset is empty"

Else

rs.MoveFirst

'show field names

for i = 0 to rs.fields.count - 1

columnNames = columnNames &vbTab &rs.fields(i).name

For i = 0 to rs.fields.count - 1

columnValues = columnValues &vbTab &rs.fields(i).Value &vbCrLf

Loop

End if

WScript.Echo columnNames

WScript.Echo columnValues

[/font]

Regards,

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Joseph Henry (5/14/2009)
Jeff:
First and foremost, thank you very much for the help you have been providing me. It is helping me to both learn more and have a stronger grip on my sanity.
I'm sorry for not getting back to you earlier. I was on vacation for awhile, amongst other things.
Here is the code as it stands right now. I'm trying to turn this into a stored procedure and pass 2 parameters to the stored procedure: Test Plan and Product. The Test Plan parameter, I already started and it is pretty straightforward. The Product parameter is a little more difficult (for me :-)). Any where you see "tblVUL_CYBmo" is where I will use it. Basically I need to concatenate a string with the product "VUL". So something like tbl + @Product + _CYBmo.
True to your analogy, this is the first potato that needs peeling.
Thanks again for the help you have provided and I look forward to hearing back from you.

Ah crud... this post really got lost in the woods on my end. Are you all set on this, Joseph?

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions