SQL, Alpha and MS SQL Reporting Services

[Scholin]

In a recent post (http://msgboard.alphasoftware.com/alphaforum/showthread.php?t=82843) I was asking how best to use MS SQL with Alpha Reports that contains several linked subreports - without using Active/Passive links and Sets.

After researching other past posts and attempting a lot of potential variations I was unable to get the alpha report to display the data correctly. With DBF it was easy using Sets but active sets using active SQL links can be slow, and my application needs frequent reporting of live data by many clients throughout the day.

So I started to look for alternatives. Crystal reports are a possibility but very expense and can be complex. However, I stumbled upon MS SQL Server 2008 reporting software that is FREE and seems very powerful!

I have been playing with it and it seems like it can do what I need plus a whole lot more (charts, graphs, WYSIWUG editors and a lot more.

Before I spend a lot of time learning this, I am curious if anyone has any experience with using this tool with SQL and Alpha Five?

[christappan]

Re: SQL, Alpha and MS SQL Reporting Services

I'm going through the same process at the moment, related to the speed of report creation of a complex-but-frequently-generated report from an SQL table. Did you find that the SQL reporting services were faster than the Alpha report generator?

Thanks in advance.

[Scholin]

Re: SQL, Alpha and MS SQL Reporting Services

That is a bit more complicated on several levels.

Yes I am using Reporting Services now for several of my more complex reports that would normally require the use of multiple sub-reports using "sets" in dbf tables. Is it faster than a dbf report? . . not really, in fact it can be a little slower (more processes involved going from sql, to report server, to reporting services).

BUT - it is a LOT faster than using active link tables in a "set" with Alpha five reports. Not to mention that if your report requires multiple sub-reports then there really is no good way to produce them quickly unless you use Reporting Services.

Now, there is another complication - with reporting services you have to deal with credentialing issues (passwords being passed along with your report). Otherwise the end user will have to enter in credentials each time they click on your report button.

I spent many, many hours, days, and bought several books on working out a solution - finally had to hire a consultant to write a short aspx script for me (of which I don't fully understand).

If you find a better way - PLEASE let me know!

But I have to say overall I really, really do like doing reports in Reporting Services. They are very powerful and fairly easy to use. The end results are great looking reports and you can use an endless number of sub-tables, sub-reports, dashboards, gauges, charts, etc fairly easily. There are MANY books out there just on this single topic, so it is ONE more thing to learn . . . . but I think it will be worth the effort.:)

[bea2701]

Re: SQL, Alpha and MS SQL Reporting Services

Maybe http://www.dynamiai.nl / is a alternative.
You can download the Personal Edition. multi language available
Why don't give it a try, cheaper then Crystal Reports and perhaps easier then reporting Services somewhere in between?.

[chadbrown]

Re: SQL, Alpha and MS SQL Reporting Services

I use Navicat to build any views and then connect directly to Mysql and have no issues with making reports.

Chad

[christappan]

Re: SQL, Alpha and MS SQL Reporting Services

Thanks all. I'll let you know what I figure out.

[NoeticCC]

Re: SQL, Alpha and MS SQL Reporting Services

I will have to look into this as I am facing the same problem also, with complex multi-child reports that I would LOVE to be able to create as SQL reports but have to resort to passive link tables and background updating etc. I have been told that Alpha are always looking to improve things like this esp. SQL reporting, but it takes a lot of rewriting etc.

[chadbrown]

Re: SQL, Alpha and MS SQL Reporting Services

I know that Navicat also has a report writer i just havent had the time to test it all out. Navicat has support for oracle aswell. It looks like the only sql it doesnt handle is microsoft.

I can tell you that it is an awesome product.

Chad

[NoeticCC]

Re: SQL, Alpha and MS SQL Reporting Services

Maybe http://www.dynamiai.nl / is a alternative.
You can download the Personal Edition. multi language available
Why don't give it a try, cheaper then Crystal Reports and perhaps easier then reporting Services somewhere in between?.

The proper link is: http://www.dynamicai.nl/

Managed to download it but already managed to "hang" it within 5 minutes. Oops :(

I am pretty sure that this does NOT produce any PDF reports, the most it can do is export to Excel.

[bea2701]

Re: SQL, Alpha and MS SQL Reporting Services

thanks for the correction,

As you looked at it as I did, there is the benefit of A5 as ONE Package.
I hope/request to the future A5 team looking forward of separation for the webserver splitting it up to WAS en reportserver. Benefit ? Scheduled reports, Batch processing,

The A5 team can do this it is a matter of priority and time.
I see clearly we don't have if it ALL, like Reporting Services etc.
But going from > left to right and versus ... noway......
I made a point for myself to A5 as one tool for my customer and the future.
my2p

tally ho...

Eric

[frankbicknell]

Re: SQL, Alpha and MS SQL Reporting Services

I'm not sure what types of reports that are needed, but, I am fairly certain that it can be accomplished with alpha's report writer and sql.

The way to do a complex report using sql is to build the proper view in sql and then create your report.

I think it would take less time and be less expensive to learn how to create the views and use the alpha report writer.

[Scholin]

Re: SQL, Alpha and MS SQL Reporting Services

If that were only so . . . .

No you can't create multiple sub reports in Alpha Report writer with SQL directly without using "sets". The entire definition of an Alpha sub-report is based on DBF Sets.

Since a normalized SQL data structure is based on many child or related records then creating some reports absolutely require multiple sub-reports or drill downs to show the associated data.

The only alternatives with using the built in Alpha reports are to use links to create DBF sets with the links. This does work but you greatly sacrifice speed and add addition stress to the server.

Do note though that many other types of reports that will work perfectly with Alpha report writer using SQL directly - the problem is really when you need a report with multiple sub-reports.

This is not only from many hours of experimenting but I also addressed this to Selwyn, who confirmed this. On a bright note, he did say that this is something they will be addressing down the road.

[frankbicknell]

Re: SQL, Alpha and MS SQL Reporting Services

I guess I don't understand what a 'sub report' is. If it is a group of detail records within a group of detail records of a parent record. You can do that forever in sql and alpha. All that those other reports writers do is build a view, you can only get data from sql server with sql language, it's not magic.

If you could give me an example of a 'sub report' I would appreciate having a better understanding of the issue.

Thanks

[BigBoss1]

Re: SQL, Alpha and MS SQL Reporting Services

Frank
here is an example of 'sub reports'

I need to produce an invoice to a customer each month.

first sub report covers his pallets stored in the warehouse based on a daily rate for the number of days the pallets are in the store.

second sub reports covers the storage bins used by the customer based on the number of days the bins have been used.

third sub report covers the admin. fee, use of forklifts, trucks, packing materials etc. based on the fixed rate for each item times the number of uses.

All of these items are in a single transaction file and based on a category decide on which of the sub reports the transactions belong.

Each sub report is summarised and added together on the Invoice and has a drill down on each of the sub reports to look at the details.

Now try that in most report writers it is pretty tough -- Crystal Report does this well once you know how to do it.

Unfortunately Alpha cannot handle the drill down effect on three different sub reports.

Does this help?

[frankbicknell]

Re: SQL, Alpha and MS SQL Reporting Services

Thanks for sharing that. Each Development tool seems to call the same thing by a different name.
In VFP you can have as many 'sub reports' or detail bands as you need.
Having a quick look at alpha's report designer I assumed that each group added to the report would have a detail band like many other writers.
One of my reports has 2 'sub reports' and I have to combine and build it in a third table first. Back to the drawing board for me.