Link to home
Start Free TrialLog in
Avatar of Barry Cunney
Barry CunneyFlag for Ireland

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

Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Sounds like a good use of the MAX statement.
SELECT StartData.Asset_Number, StartData.Read_Data, StartData.Meter_Read,
    EndData.Asset_Number, EndData.Read_Data, EndData.Meter_Read,
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

Open in new window

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
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

Open in new window

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
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
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


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

Open in new window

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.
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

Open in new window

Avatar of Barry Cunney

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?
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

Open in new window

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.)
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.)?
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.
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     |
---------------------------------------------------------------------------------------------------------
If you create a view on the table, you would then be able to add the view to the model.
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
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
Thanks Shaun