Creating reports in .NET 2003 for Reporting Services

I'm totally new to the art of creating reports in .NET for RS. Here's what I
need to accomplish. I need to create a report that captures a monthly total
in one column and the Year-To-Date total in another column. How can I use
sequel statements to populate each column with the correct total? The two
queries that I've created are below, but when used in a stored procedure only
the first one is read (both columns have the same totals)
This one used for populating MONTHLY total
SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD = '01') AND
(f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
This one used for populating YEAR-TO-DATE total
SELECT a.LDGACT, a.ACCTNAME, SUM(f.BILLAPPREV) AS MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN '01'
AND '02') AND (f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
THANKS
--
DCountI don't know why you would need to do this in a stored procedure. You
are really doing simple selects. As far as stored procedures are
concerened you can only really return one select from a stored
procedure. There are multi resultset stored procedures but these aren't
handled in SQL Reporting Services.
Probably what you should do is just use the data tab in the report
designer. Copy and paste the two selects into two different
datasets...or you could get away with unioning the two together and
putting them in one dataset...like this
SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod , null as
MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD ='01') AND
(f.idACCOUNT = a.idACCOUNT)
Union all
SELECT a.LDGACT, a.ACCTNAME, null AS MCurrPeriod, SUM(f.BILLAPPREV)
AS MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN
'01'
AND '02') AND (f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV|||Another option is to bring back all months data in a singkle query. Summing
all data for an account will give you your YTD figures. To get the MTD
amount, using RunningValues() to sum only those amounts for the current month.
"DCount17" wrote:
> I'm totally new to the art of creating reports in .NET for RS. Here's what I
> need to accomplish. I need to create a report that captures a monthly total
> in one column and the Year-To-Date total in another column. How can I use
> sequel statements to populate each column with the correct total? The two
> queries that I've created are below, but when used in a stored procedure only
> the first one is read (both columns have the same totals)
> This one used for populating MONTHLY total
> SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD = '01') AND
> (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
> This one used for populating YEAR-TO-DATE total
> SELECT a.LDGACT, a.ACCTNAME, SUM(f.BILLAPPREV) AS MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN '01'
> AND '02') AND (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
> THANKS
> --
> DCount|||Thanks Luke, this gives me the figures I'm looking for. However, the figures
are displayed in 2 rows instead of one. Instead of this I need the report to
do the following:
Acct# AcctName Monthly Amt YTD Amt
12 CVS 531.90 1223.99
What the reports does now is:
Acct# AcctName Monthly Amt YTD Amt
12 CVS 531.90 1223.99
12 CVS 1223.99
THANKS AGAIN
"Luke" wrote:
> I don't know why you would need to do this in a stored procedure. You
> are really doing simple selects. As far as stored procedures are
> concerened you can only really return one select from a stored
> procedure. There are multi resultset stored procedures but these aren't
> handled in SQL Reporting Services.
> Probably what you should do is just use the data tab in the report
> designer. Copy and paste the two selects into two different
> datasets...or you could get away with unioning the two together and
> putting them in one dataset...like this
> SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod , null as
> MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD => '01') AND
> (f.idACCOUNT = a.idACCOUNT)
> Union all
> SELECT a.LDGACT, a.ACCTNAME, null AS MCurrPeriod, SUM(f.BILLAPPREV)
> AS MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN
> '01'
> AND '02') AND (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
>