Report Feasibility in SQL Reporting Services | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Report Feasibility in SQL Reporting Services

Hi All,<br /> <br /><br /><b>There a particular report we need to generate using SQL Reporting Services.</b><br /><br /><br /><b>Device History report:</b> This report shows how long a particular device is in a store. Within a given time-period, how the device moves from one store to the other.<br /> <br /><br /><b>X Axis: </b> Date (suppose Jan1 – Mar15, it should be Jan 1, Jan15, Feb 1, Feb 15, Mar 1)<br /><br /><b>Y Axis: </b> DeviceID<br /><br /> <br /><br /><b>Inputs:</b><br /><br /> <ul><li> </li><li> </li><li> </li></ul> <br /> <br /><br /><b>Sample data:</b><br /><br /> <br /><br />StoreID<br /> DeviceID<br /> StartDate<br /> EndDate<br /> Diff<br /> <br />1235<br /> 12345678901<br /> 1/5/2005<br /> 3/25/2005<br /> 80<br /> <br />1234<br /> 12345678901<br /> 3/26/2005<br /> 4/8/2005<br /> 14<br /> <br />1237<br /> 12345678901<br /> 4/9/2005<br /> 5/10/2005<br /> 32<br /> <br />1236<br /> 12345678901<br /> 5/28/2005<br /> 6/24/2005<br /> 28<br /> <br />1236<br /> 12345678902<br /> 1/5/2005<br /> 2/11/2005<br /> 38<br /> <br />1237<br /> 12345678902<br /> 3/11/2005<br /> 3/15/2005<br /> 5<br /> <br />1234<br /> 12345678902<br /> 3/26/2005<br /> 7/20/2005 10:44<br /> 117<br /> <br />1237<br /> 12345678903<br /> 1/5/2005<br /> 3/25/2005<br /> 80<br /> <br />1234<br /> 12345678903<br /> 3/26/2005<br /> 7/20/2005 10:44<br /> 117<br /> <br /><br /> <br /><br /><b>Required Report:</b><br /><br /><img src='<br />’ border=’0′ /><br /><br /><b>There are two issues,<br /><br />1. How to put the date in x-axis. (It is in run-time, based on the start date and end date given), date like Jan, Feb., Mar (or) Jun 1, Jun 15, Jul 1. <br /><br />2. How to generate a chart of this pattern.</b><br /> <br /><br />For the second problem, we tried modifying the table by adding rows for NO STORE. Like,<br /><br /> <br /><br />StoreID<br /> DeviceID<br /> StartDate<br /> EndDate<br /> Diff<br /> <br />1235<br /> 12345678901<br /> 1/5/2005<br /> 3/25/2005<br /> 80<br /> <br />1234<br /> 12345678901<br /> 3/26/2005<br /> 4/8/2005<br /> 14<br /> <br />1237<br /> 12345678901<br /> 4/9/2005<br /> 5/10/2005<br /> 32<br /> <br />NO STORE<br /> 12345678901<br /> 5/11/2005<br /> 5/27/2005<br /> 17<br /> <br />1236<br /> 12345678901<br /> 5/28/2005<br /> 6/24/2005<br /> 28<br /> <br /><br /> <br /><br />So if the difference can be used as values for the chart. But here also we faced problem, it is summing up the difference when grouped by Store. For example it is summing up NO STORE values and showing a long GREY bar.<br /><br /> <br /><br />Is this report feasible? Any pointers on the issues will be of great help.<br /><br /> <br /><br />Thanks,<br /><br />Madhu<br />
<b>Report Required:[]<br /><br /><img src=’ C:Documents and Settingsmadhumitha_rkDesktop
eportsample.bmp ‘ border=’0′ /> <br /><br /><b> Data table: []<br /><br /><img src=’ C:Documents and Settingsmadhumitha_rkDesktopdatatable.bmp ‘ border=’0′ /> <br /><br /><b> Changed Data table: []<br /><br /><img src=’ C:Documents and Settingsmadhumitha_rkDesktopchangeddatatable.bmp ‘ border=’0’ />
]]>