Barry Cunney
asked on
Report Model Design for specific transactional data
Hi,
I was wondering if some experts could give any advice on how to design a report model, or even if it is actually possible to design a report model to satisfy the below reporting requirement:
It is required to get the following data in a single matrix like the below(this example is for just one asset, but the report will have to work for numerous assets) if the user passes in a start date parameter of 31/07/2010 and an end date parameter of 25/08/2010.
That is to say, the report must return the meter read record exactly on the start date passed in, or if no meter read record exists for an asset, for the start date passed in, it should return the very last meter read record, on a date just before the start date passed in
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
and the underlying raw data exists in a SQL Server database table like the following
Asset_Number Read_Date Meter_Read
PAQ23487 30/07/2010 20730
PAQ23488 02/08/2010 20743
PAQ23489 03/08/2010 20749
PAQ23490 04/08/2010 20750
PAQ23491 05/08/2010 20765
PAQ23492 06/08/2010 20766
PAQ23493 09/08/2010 20780
PAQ23494 10/08/2010 20794
PAQ23495 11/08/2010 20800
PAQ23496 12/08/2010 20805
PAQ23497 13/08/2010 20819
PAQ23498 16/08/2010 20826
PAQ23499 17/08/2010 20829
PAQ23500 19/08/2010 20851
PAQ23501 20/08/2010 20868
PAQ23502 23/08/2010 20872
PAQ23503 24/08/2010 20881
PAQ23504 25/08/2010 20893
Any advice appreciated
I was wondering if some experts could give any advice on how to design a report model, or even if it is actually possible to design a report model to satisfy the below reporting requirement:
It is required to get the following data in a single matrix like the below(this example is for just one asset, but the report will have to work for numerous assets) if the user passes in a start date parameter of 31/07/2010 and an end date parameter of 25/08/2010.
That is to say, the report must return the meter read record exactly on the start date passed in, or if no meter read record exists for an asset, for the start date passed in, it should return the very last meter read record, on a date just before the start date passed in
--------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
--------------------------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
--------------------------
and the underlying raw data exists in a SQL Server database table like the following
Asset_Number Read_Date Meter_Read
PAQ23487 30/07/2010 20730
PAQ23488 02/08/2010 20743
PAQ23489 03/08/2010 20749
PAQ23490 04/08/2010 20750
PAQ23491 05/08/2010 20765
PAQ23492 06/08/2010 20766
PAQ23493 09/08/2010 20780
PAQ23494 10/08/2010 20794
PAQ23495 11/08/2010 20800
PAQ23496 12/08/2010 20805
PAQ23497 13/08/2010 20819
PAQ23498 16/08/2010 20826
PAQ23499 17/08/2010 20829
PAQ23500 19/08/2010 20851
PAQ23501 20/08/2010 20868
PAQ23502 23/08/2010 20872
PAQ23503 24/08/2010 20881
PAQ23504 25/08/2010 20893
Any advice appreciated
it would be a query like
select top 1 asset_number, read_date, meter_read from table where read_date <= @userDate and asset_number = @assetNumber order by read_date desc
that would give the reading on that date or the reading before that..
if you want it for more than 1 asset it would be
select asset_number, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
select top 1 asset_number, read_date, meter_read from table where read_date <= @userDate and asset_number = @assetNumber order by read_date desc
that would give the reading on that date or the reading before that..
if you want it for more than 1 asset it would be
select asset_number, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
I missed the volume column in your report sample, but that would be just subtracting the StartData.Meter_Read from the EndData.Meter_Read:
SELECT StartData.Asset_Number, StartData.Read_Date Start_Date, StartData.Meter_Read Start_Meter_Read, EndData.Read_Date End_Date, EndData.Meter_Read End_Meter_Read, EndData.Meter_Read - StartData.Meter_Read Volume
FROM <your table> StartData
INNER JOIN (
SELECT Asset_Number, MAX(Read_Date) Read_Date
FROM <your table>
WHERE Read_Date < @StartDate
GROUP BY Asset_Number) MaxStartDate ON StartData.Asset_Number = MaxStartDate.Asset_Number
AND StartData.Read_Date = MaxStartDate.Read_Date
INNER JOIN <your table> EndData ON StartData.Asset_Number = EndData.Asset_Number
INNER JOIN (
SELECT Asset_Number, MAX(Read_Date) Read_Date
FROM <your table>
WHERE Read_Date < @StartDate
GROUP BY Asset_Number) MaxEndDate ON EndData.Asset_Number = MaxEndDate.Asset_Number
AND EndData.Read_Date = MaxEndDate.Read_Date
sorry more easy with the min date too
select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading from table
where read_date <= @userdate
group by asset_number
I've missed the volume too..
select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading
, max(meter_read) - min(meter_read) as volume
from table
where read_date <= @userdate
group by asset_number
select asset_number, min(read_date) as firstdate, min(meter_read) as first reading, max(read_date) as ReadDate, max(meter_read) as reading
, max(meter_read) - min(meter_read) as volume
from table
where read_date <= @userdate
group by asset_number
I don't see why you need to do the 2 joins..
fixed a small typo and added the select asset
just take out the -- if you want to be able to select on 1 or more asset_numbers
just take out the -- if you want to be able to select on 1 or more asset_numbers
select
asset_number
, min(read_date) as firstdate
, min(meter_read) as firstreading
, max(read_date) as ReadDate
, max(meter_read) as reading
, max(meter_read) - min(meter_read) as volume
from table
where read_date <= @userdate
-- and asset_number in (@assetNumber)
group by asset_number
There are two reasons for the joins:
1.) There are two date parameters, a Start Date and an End Date, so you would need two different sets of data.
2.) There is no guarantee that the Max Reading will occur on the Max Date. The sample data may appear that way, but there was nothing stated that the meter reading can never be lower (though it would be assumed as such).
I noticed I missed the End Date parameter and corrected my SQL.
1.) There are two date parameters, a Start Date and an End Date, so you would need two different sets of data.
2.) There is no guarantee that the Max Reading will occur on the Max Date. The sample data may appear that way, but there was nothing stated that the meter reading can never be lower (though it would be assumed as such).
I noticed I missed the End Date parameter and corrected my SQL.
SELECT StartData.Asset_Number, StartData.Read_Date Start_Date, StartData.Meter_Read Start_Meter_Read, EndData.Read_Date End_Date, EndData.Meter_Read End_Meter_Read, EndData.Meter_Read - StartData.Meter_Read Volume
FROM <your table> StartData
INNER JOIN (
SELECT Asset_Number, MAX(Read_Date) Read_Date
FROM <your table>
WHERE Read_Date < @StartDate
GROUP BY Asset_Number) MaxStartDate ON StartData.Asset_Number = MaxStartDate.Asset_Number
AND StartData.Read_Date = MaxStartDate.Read_Date
INNER JOIN <your table> EndData ON StartData.Asset_Number = EndData.Asset_Number
INNER JOIN (
SELECT Asset_Number, MAX(Read_Date) Read_Date
FROM <your table>
WHERE Read_Date < @EndDate
GROUP BY Asset_Number) MaxEndDate ON EndData.Asset_Number = MaxEndDate.Asset_Number
AND EndData.Read_Date = MaxEndDate.Read_Date
ASKER
Hi Guys,
I already have a SQL statement written(see attached)that gives me what is required if I run it on the records in the SQL Server database table, but how does one design a report model so that people who are Report Developers can design reports in BIDS/Report Builder 2.0 to achieve the same result as the attached SQL statement?
I already have a SQL statement written(see attached)that gives me what is required if I run it on the records in the SQL Server database table, but how does one design a report model so that people who are Report Developers can design reports in BIDS/Report Builder 2.0 to achieve the same result as the attached SQL statement?
DECLARE @parameter_Start_Date datetime;
DECLARE @parameter_End_Date datetime;
SET @parameter_Start_Date = '2010-07-31'
SET @parameter_End_Date = '2010-08-25'
-- Get Start Meter Read, End Meter Read and Volume based on dates above
SELECT DISTINCT Asset_Number,
(SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date) Start_Read_Date,
(SELECT Meter_Read FROM tbl_meter_read_hist m3 WHERE m3.Asset_Number = m2.Asset_Number AND m3.Read_Date = (SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date)) Start_Read,
(SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date) End_Read_Date,
(SELECT Meter_Read FROM tbl_meter_read_hist m5 WHERE m5.Asset_Number = m2.Asset_Number AND m5.Read_Date = (SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date)) End_Read,
(
(SELECT Meter_Read FROM tbl_meter_read_hist m5 WHERE m5.Asset_Number = m2.Asset_Number AND m5.Read_Date = (SELECT MIN(Read_Date) FROM tbl_meter_read_hist m4 WHERE m4.Asset_Number = m2.Asset_Number AND m4.Read_Date >= @parameter_End_Date))
-
(SELECT Meter_Read FROM tbl_meter_read_hist m3 WHERE m3.Asset_Number = m2.Asset_Number AND m3.Read_Date = (SELECT MAX(Read_Date) FROM tbl_meter_read_hist m1 WHERE m1.Asset_Number = m2.Asset_Number AND m1.Read_Date <= @parameter_Start_Date))
) Volume
FROM
tbl_meter_read_hist m2
If your report developers are using BIDS, you can create a stored procedure for your code and the developers can use your stored procedure as the data source of their report. The Report Models use table and view objects from your database, which will make it difficult to use your code as part of the Report Model (mostly because of the need for start and end date parameters.)
ASKER
Thanks Shaun,
So I would have to create a stored procedure which resides in my SQL Server database?
What would have to be then done so as when the developers are using BIDS, they could then see this stored procedure as a data source?
Could they also use the stored procedure if using Report Builder 2.0 and using a Report Model or can we categorically say that such a stored procedure(with my T-SQL above), or what the stored procedure does could not actually be implemented in a report model(Entities etc.)?
So I would have to create a stored procedure which resides in my SQL Server database?
What would have to be then done so as when the developers are using BIDS, they could then see this stored procedure as a data source?
Could they also use the stored procedure if using Report Builder 2.0 and using a Report Model or can we categorically say that such a stored procedure(with my T-SQL above), or what the stored procedure does could not actually be implemented in a report model(Entities etc.)?
If the developers use the wizard to build the report, they will be presented with a screen titled "Design the Query" (at least that is what it is titled in 2005) asking for T-SQL statement to use as the data source. They should enter the T-SQL statement just like how you would enter it in the Query editor in SSMS.
Because the Report Model only uses Tables and Views, and because your stored procedure would require two parameters (so it cannot be re-written as a view), I do not believe it can be included in the Report Model.
Because the Report Model only uses Tables and Views, and because your stored procedure would require two parameters (so it cannot be re-written as a view), I do not believe it can be included in the Report Model.
ASKER
Thanks Shaun,
Is there a way to include the same table twice in a Report Model so that there are 2 linked Entities for the same table? The reason I am asking is I wondering if this was possible, that developers could then use the first entity to get the start meter read and use the second entity to get the end meter read and link them in a single dataset and therfore get in a single matrix what is required
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
Is there a way to include the same table twice in a Report Model so that there are 2 linked Entities for the same table? The reason I am asking is I wondering if this was possible, that developers could then use the first entity to get the start meter read and use the second entity to get the end meter read and link them in a single dataset and therfore get in a single matrix what is required
--------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
--------------------------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
--------------------------
If you create a view on the table, you would then be able to add the view to the model.
ASKER
Thanks Shaun,
Do you think would it be possible to create 2 views - Both views would look at the meter_read table?
Then could these 2 views be added to the report model but that they could be linked by asset_number and so then it would be possible tp get the result when designing a report?
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
I should have qualified in the beginning that I am new totally new to Report Models so if I am asking a totally invalid question, let me know.
I have good SQL Server/T-SQL expertise and expertise in Report Builder on the client side but not Report Models or Reporting services, but the above is a real-life requirment that I have encountered and I am trying to get an idea on any possibilities on how to approach it - but I do appreciate it, that it is kind of a big question I am asking, but any advice appreciated - Thanks
Do you think would it be possible to create 2 views - Both views would look at the meter_read table?
Then could these 2 views be added to the report model but that they could be linked by asset_number and so then it would be possible tp get the result when designing a report?
--------------------------
| Asset_Number | Start_Read_Date | Start_Read | End_Read_Date | End_Read | Volume |
--------------------------
| PAQ23487 | 30/07/2010 | 20730 | 25/08/2010 | 20893 | 163 |
--------------------------
I should have qualified in the beginning that I am new totally new to Report Models so if I am asking a totally invalid question, let me know.
I have good SQL Server/T-SQL expertise and expertise in Report Builder on the client side but not Report Models or Reporting services, but the above is a real-life requirment that I have encountered and I am trying to get an idea on any possibilities on how to approach it - but I do appreciate it, that it is kind of a big question I am asking, but any advice appreciated - Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much Shaun,
I was sort of starting to think that may be the case(that this just is not possible in a Report Model) even with my limited expertise, but it's great to get an expert opinion on it.
So really the only way to go is to create a stored procedure with the T-SQL I already have and which I know gives the result required and allow developers using Report Builder 2.0 to use Stored Procedures as a Query type and be able to select this Stored Procedure I create, when creating datasets?
I was sort of starting to think that may be the case(that this just is not possible in a Report Model) even with my limited expertise, but it's great to get an expert opinion on it.
So really the only way to go is to create a stored procedure with the T-SQL I already have and which I know gives the result required and allow developers using Report Builder 2.0 to use Stored Procedures as a Query type and be able to select this Stored Procedure I create, when creating datasets?
Sorry for the delay in responding. To answer your question, if when you refer to Report Builder you mean BIDS (SQL Server Business Intelligence Development Studio) then yes. You cannot add stored procedures to a Data Model, therefore you would not be able to use the stored procedure using the Report Builder available through Reporting Services 2005.
ASKER
Thanks Shaun
Open in new window