Work in Process?

[cwalko]

The application I am writing is for a charity dinner that need to know how many of each item is waiting in a queue. I have a grid that will sow each customer order and the individual items that have been ordered. I need one that will show just the �total number� of each item and does not need customer info. I have written a �report� that groups everything but I want to �suppress the detail lines�. (Example screen shot below). I really only need the Description and how many are waiting to be delivered. (I have the SQL logic worked out to know which ones that is, in fact in SQL I can build what I need using the "Sum" and "Group by" functions.) It would be great if this was in a self refreshing grid but have not a clue if that is possible. Anyway, anyone have suggestions on how to suppress the detail lines? (Or how to build this in Grid that refreshes this total data every minute or so?)
WIP Report.png
I am fairly new to Alpha and SQL so if this is a dumb question, sorry. (I have looked on Wiki for hours but can�t find anything. I am sure it is out there but I can't find it.)

Carl

[kingcarol]

Re: Work in Process?

I got a little confused in reading your post whether you were trying to suppress the detail on a report or on a grid. If on a report, I was trying to do that that the other day so I could only show the group totals. I found it by clicking Report on the toolbar, then Properties, then Band, then Report Properties. Here is a screenshot: http://screencast.com/t/Q5jONqWJ

[cwalko]

Re: Work in Process?

I got a little confused in reading your post whether you were trying to suppress the detail on a report or on a grid. If on a report, I was trying to do that that the other day so I could only show the group totals. I found it by clicking Report on the toolbar, then Properties, then Band, then Report Properties. Here is a screenshot: http://screencast.com/t/Q5jONqWJ

FANTASTIC! Thank you!!! Now I just need to figure out how to get just the Description and the Total # to show with out all the extra lables. Thank you again for taking the time to help.

Carl

[MoGrace]

Re: Work in Process?

Define your Group with no header but with a footer and move the Job description to the footer with the calc->total.

[cwalko]

Re: Work in Process?

THANK YOU! Between the two suggestions I have it working. Any idea's on how to make the data refresh every x minutes other than having to close and reopen the report. The users need this data to be always current. And they will litterally need to refresh the data hundreds of times a night.

[BrentG]

Re: Work in Process?

If you are using SQL you should be able to write a procedure based on your requirements, and then schedule the process.

[cwalko]

Re: Work in Process?

Ouch, something else to learn. I was hoping there was something in A5. Off to read about SQL proceedures. :-(

BTW - Thank you for pointing me in a direction.

[MarionT]

Re: Work in Process?

Carl - I'm new to Alpha 5, and not a 'real developer' but - I am familiar with SQL stored procedures - you are welcome to ask me questions.

Basically, what I do is:

prepare the view or other SQL queries, get it working, then 'wrap it' within a stored procedure....



Then, and I've only done this once, from A5
ServerSideEvent, I added:

SQL = "exec MyStoredProcedureName"

dim flag as l
e.cn.portableSQLEnabled = .t.
flag = e.cn.execute(sql)

if flag = .t. then
e.flagError =.f.
e.errorText= ""
e.lastInsertedIdentityValue = -1
e.rowsAffected = e.cn.affectedRows()
e.SQLToDisplay = sql
e.ArgsToDisplay = ""
else
e.flagError = .t.
e.errorText = e.cn.callresult.text
e.lastInsertedIdentityValue = -1
e.rowsAffected = 0
end if

[cwalko]

Re: Work in Process?

Marion - Thank you! I am an old dog trying to learn new tricks. I am slowly figuring out SQL by trial and error (and lots or reading). Unfortunately the above is more or less Greek to me. Well, I can figure out some of it but not much. I'll 'play and read' and see what I can learn. Right now I am copying and pasting all my SQL queries from SQL Server to Alpha. (I run the query in SQL Server to debug it.) This stored procedure trick may mean I can maintain it in once spot.) I guess first thing is to learn how to create a 'stored procedure'. LOL

[MarionT]

Re: Work in Process?

hi Carl -

1. I don't know what you mean by "copying and pasting all my SQL queries from SQL Server to Alpha" - do you know about making a connection string?

perhaps you are pasting the SQL query in A5's SQL Genie....

2. I don't know what any of the code I included in my post #8 means either; I lifted it from some other Video or post and it works. ie, I don't understand why it works, but I'll worry about that some other day.

3. As for the Stored Procedures - here's a little push to get you going
let's say you have a query that is:
select * from Table1 where Column1 = 'blah'

but you want to be able to specify the filter

when you create the stored procedure, you have:

create procedure MySPname
@x = varchar(25)
AS
BEGIN
select * from Table1 where Column1 = @x
END



to execute the stored procerdure, from SQL you'd type
exec MySPname @x = 'blah'



(I resisted learning Stored Procedures for MONTHS and it drove my SQL mentor CRAZY. When I finally sat down to learn them, I loved them!)

[cwalko]

Re: Work in Process?

:-) Thank you very much for helping. There is just so much. I will try this. One stupid question, where is SQL Server do you type this in? Thank you again.

[MarionT]

Re: Work in Process?

well, you could just type it in any available query window

reminder: I recommend that you get the query working the way you sorta want it, before building the SP...

Then, what I do is this (we're in SQL now, not A5):

I have ObjectExplorer panel open on my left
the query window front and center, with the query that I'm working on (make it something super simple at first). Using my above example, I'd have:

DECLARE @X = varchar(25)
Set @x = 'blah'
select * from Table1 where Column1 = @x

Then, in the left hand Object Explorer, I have:
Databases
..MyDatabaseName
----DatabaseDiagrams
----Tables
----Views
----Synonyms
----Programmability
-------Stored Procedures
-------Functions
-------DatabaseTriggers
-------Assemblies
-------etc etc etc


I RightClick on Stored Procedures and select New Stored Procedure

that opens up a new query windowpane for me, with lots of green text (ie, commented out stuff)
and the basic requirements in blue

I delete the first 20 lines and am left with:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

from there, I copy and paste from my working query pane, so that I have:

<Procedure_Name, sysname, ProcedureName> with MySPname

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0> with @x = Varchar(25)

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> with select * from Table1 where Column1 = @x



And I just hit f5 (execute) to save it; now, if you expand the StoredProcedures tree on the left, you'll see your stored procedure listed.

There are several ways to run/execute the SP.... I type, so I tend to just type in:
exec MySPName @parameter1 = value1, @parameter2 = value2


there's more to know, but this is a start

[cwalko]

Re: Work in Process?

I think I can follow this. THANK YOU!!!