Adding Table in ssrs

Let me make things a little more clear.. I have a date/time pick so the total needs to be a daily total the of the tables.

Sam,

can you share your query, some sample data & what results you are trying to get to?

Sure I have two querys, one for each ssrs table.

Table 1

SELECT     FlowDaily.Volume, FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime, DeviceSummary.MdbDeviceID
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (DeviceSummary.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Table 2

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (DailyFlowDateTime >= @StartDate) AND
                      (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY TheDate DESC

I am trying to add both querys for one table in ssrs.. but having a problem getting the data i want.

thanks for the help.

SamRunyon,

a Table in SSRS requires you to assign it a dataset so in this case it would be one or the other.  

It looks like you are querying from the same table & just using the second table to make your running total for each day is that correct?

If so we can eliminate dataset 2 and do all of the grouping & manipulation right in SSRS

Yes, that is what I am doing.
How would I eliminate the dataset 2?

Sam

Setup your table to group on the DailyFlowDateTime by right clicking on the table insert Group use the dailyflow date as your group field.

You will then be able to report by day, the transactions (in the detail line) and the summary flow information in the group header or footer.

Not sure if you understand what I need and I am sorry for not getting the information clear.

This is what I am trying to do.

Table 1              Table 1  Table 2  Table 3
  05/05/09             10          10    =    20
  05/06/09               5            5    =   10

Table 1 is a query by it self. Each other table is separate.
is there no way to just add the tables?

again thanks for your help.
Sam

No, the "Table" reporting object uses a single dataset to manage the rows.  The reason being if you had two datatables with different recordcounts how would you relate them so that the reporting tool knew that May 5 in Table 1 is row # X in Tables 2 and 3?

My question would be how are Tables 1 2 and 3 related? is there a way we could normalize the information into one query so that you can do row operations on it?

This is the query.

Table 1

SELECT     FlowDaily.Volume, FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime, DeviceSummary.MdbDeviceID
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (DeviceSummary.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Table 2

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (DailyFlowDateTime >= @StartDate) AND
                      (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY TheDate DESC

I can figure out how to get my totals (table 2) with and add the other table.
So I can do the math in SSRS.

Sam

Sorry I meant to say

I can't figure out how to get my totals (table 2) with and add the other table.
So I can do the math in SSRS.

Sam

I got it work with this query but I know this is not the right way.

SELECT     CONVERT(varchar, FlowDaily.DailyFlowDateTime, 111) AS TheDate, SUM(FlowDaily.Volume) AS Volume, SUM(FlowDaily.Energy) AS Energy,
                      CONVERT(varchar, FlowDaily_1.DailyFlowDateTime, 111) AS TheDate2, AVG(FlowDaily_1.Volume) AS Volume2, AVG(FlowDaily_1.Energy) AS Energy2
FROM         FlowDaily INNER JOIN
                      FlowDaily AS FlowDaily_1 ON FlowDaily.DailyFlowDateTime = FlowDaily_1.DailyFlowDateTime
WHERE     (FlowDaily.MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) AND (FlowDaily.DailyFlowDateTime >= @StartDate) AND
                      (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily_1.MdbDeviceID = 1) AND (FlowDaily_1.DailyFlowDateTime >= @StartDate) AND
                      (FlowDaily_1.DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, FlowDaily.DailyFlowDateTime, 111), CONVERT(varchar, FlowDaily_1.DailyFlowDateTime, 111)

any help.

Sam

Sam, whats the difference between the devices? are you trying to get a summary for certain devices or what?

Can you give me a sample of the records returned? I have an idea.

Same database What I am doing here is getting a total of the all the meters.


(FlowDaily.MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244))

Now I need the Value of Meter 1

(FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily_1.MdbDeviceID = 1)

then I have to add them together in a ssrs table.

thanks, Sam

Sam,

Can you get the dataset into a form similar to this

MeterID, Date,Volume, Energy

If you can get the "list"  of entries, we can easily do all the manipulation in the SSRS report.

Not sure what you need as a "list"?

Let me show the querys that I need as one Query.
I have two databases.

Data Source=GMWWDB;Initial Catalog=Runtime

SELECT TagName, DateTime, Value = convert(decimal(38, 2), Value), vValue
 FROM History
 WHERE TagName IN ('duke-btu')

now this database is
Data Source=WWSCADA;Initial Catalog=VaTfLt

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY CONVERT(varchar, DailyFlowDateTime, 111) DESC

And I need to get this meter in the query.

SELECT     FlowDaily.MdbDeviceID, DeviceSummary.MdbDeviceID AS Expr1, DeviceSummary.MeterID, DeviceSummary.Location, FlowDaily.Volume,
                      FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

hope this helps.
Sam

Sam,

Sorry for the abundance of questions, I'm just trying to get you the best answer...

The way I'm understanding it is

This query

SELECT     CONVERT(varchar, DailyFlowDateTime, 111) AS TheDate, SUM(Volume) AS Volume, SUM(Energy) AS Energy
FROM         FlowDaily
WHERE     (MdbDeviceID NOT IN (1, 3, 17, 18, 238, 239, 240, 241, 242, 243, 244)) and (DailyFlowDateTime >= @StartDate) AND (DailyFlowDateTime <= @EndDate)
GROUP BY CONVERT(varchar, DailyFlowDateTime, 111)
ORDER BY CONVERT(varchar, DailyFlowDateTime, 111) DESC is

is giving you a sum for particular Devices (or all the devices?)

and this one

SELECT     FlowDaily.MdbDeviceID, DeviceSummary.MdbDeviceID AS Expr1, DeviceSummary.MeterID, DeviceSummary.Location, FlowDaily.Volume,
                      FlowDaily.Energy, FlowDaily.Tf, FlowDaily.AP, FlowDaily.DP, FlowDaily.DailyFlowDateTime
FROM         FlowDaily INNER JOIN
                      DeviceSummary ON FlowDaily.MdbDeviceID = DeviceSummary.MdbDeviceID
WHERE     (FlowDaily.DailyFlowDateTime >= @StartDate) AND (FlowDaily.DailyFlowDateTime <= @EndDate) AND (FlowDaily.MdbDeviceID = 1)
ORDER BY FlowDaily.DailyFlowDateTime DESC

Is giving you a list of all entries for the date range.

How is the top query related to this table? does TagName = The DeviceID or MeterID?

The middle query, is probably redundant because you can do the Math operations in the SSRS table.