cancel
Showing results for 
Search instead for 
Did you mean: 

Using multiple SELECT statements

Former Member
0 Kudos

I'm pretty new to this, so I could really use some help. Here's my problem: I need to create a report that sums a bunch of lines from my databse. The problem is, it all comes from different tables and uses a lot of different fields. There are about 25 different summaries I need to do. Is there a way to use a different record selection (SQL Select statement) for each of these 25 items?

What I have now, I can set my Record selection to work for one out of the 25, but because the others have different requirements, they do not return the correct values. What I want is to be able to make 25 seperate SQL queries for each of the 25 items. I hope this made sense, and thanks for any help you can give.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

If the fields are from different tables and you want them in the report only for the summary calculation then you can try using SQL Expressions in the report. You need to create 25 SQL Expressions where you can keep each query in each SQL Expression. Create a SQL Expression like this

(select sum() from table where =value)

Hope this helps!

Raghavendra

Answers (2)

Answers (2)

Former Member
0 Kudos

creating a seperate stored procedure for each table may cause you issues.

create parameters in the sp so your dataset is smaller when returned.

i would create one sp and joins the tables you need. once you have the dataset,

the tables should have a foreign key to join on. make sure they are outer joins so you get all your data.

sometimes a union query is the easiest

in the report create manual running totals for the summary fields(this helps avoid dups)

Former Member
0 Kudos

Morning Jeremy,

Does your report requires only one date field? I mean to get the date range? If yes then you should only use one record selection formula which would be the date range and rest can be done by using Conditional Running totals.

Regards

Jehanzeb