CODEWITHSUNDEEP
sqlreporting-servicesparameterschartssubreport
pgunston
pgunston

Reputation: 302

How to use Sub-reports with SSRS Charts?

I have a report which displays overdue invoice information in a bar chart.

Each bar shows a range of total overdue invoices. For example: number of invoices overdue by 45+ days, number of Invoices overdue by 31-44 days, number of Invoices overdue by 21 - 30 days ETC.

I want to be able to click on a bar from the chart to open a sub-report which displays all of the invoice details for all invoices in the range of the bar that was clicked.

For example, if the 45+ bar was clicked, it would show all invoice details for invoices that are overdue by 45 or more days.

I have created a sub-report which has all of the fields that I need however, this report display ALL invoices from the table rather than only the ones that are within the range of the clicked bar.

My Parent Report uses the following query for the chart:

    WITH Temp ([Description], [Amount], [OverdueBy])
AS 
(select Description, SUM(Amount) as Amount, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy  from brvAPAllInvoices 
Where PaidDate is null and APCo = 1
Group By Description, DueDate

)


SELECT CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
            WHEN OverdueBy <= -46 THEN 'Due in 46+ days'
ELSE 'Less than that' END AS OverdueRange,
Count(*) as OverdueCount,
Sum(Temp.Amount) as [Total Amount]
From Temp


group by CASE WHEN OverdueBy >= 46 THEN 'Overdue by 46+ days' 
            WHEN OverdueBy >= 31 and OverdueBy <= 45 THEN 'Overdue by 31-45 days'
            WHEN OverdueBy >= 15 and OverdueBy <= 30 THEN 'Overdue by 15-30 days'
            WHEN OverdueBy >= 8 and OverdueBy <= 14 THEN 'Overdue by 8-14 days'
            WHEN OverdueBy >= 1 and OverdueBy <= 7 THEN 'Overdue by 1-7 days'
            WHEN OverdueBy <= 0 and OverdueBy >= -7 THEN 'Due in 0-7 days'
            WHEN OverdueBy <= -8 and OverdueBy >= -14 THEN 'Due in 8-14 days'
            WHEN OverdueBy <= -15 and OverdueBy >= -30 THEN 'Due in 15-30 days'
            WHEN OverdueBy <= -31 and OverdueBy >= -45 THEN 'Due in 31-45 days'
            WHEN OverdueBy <= -46 THEN 'Due in 46+ days'

ELSE 'Less than that' END


ORDER BY MAX(Temp.[OverdueBy]) DESC;

The following table is generated:

+-----------------------+--------------+
|     OverdueRange      | OverdueCount |
+-----------------------+--------------+
| Overdue by 46+ days   |           50 |
| Overdue by 31-45 days |           15 |
| Overdue by 15-30 days |           24 |
| Overdue by 8-14 days  |          121 |
| Overdue by 1-7 days   |           17 |
| Due in 0-7 days       |           17 |
| Due in 8-14 days      |            4 |
| Due in 15-30 days     |          368 |
| Due in 31-45 days     |            1 |
| Due in 46+ days       |           14 |
+-----------------------+--------------+

The Sub Report uses the following query:

select APRef as 'Invoice #', Vendor, VendorName,  InvDate, Description, (DATEDIFF(day,DueDate,GETDATE())) as OverdueBy, SUM(Amount) as Amount from brvAPAllInvoices
Where APCo = 1 and  PaidDate is null
Group By APRef, Vendor, VendorName, InvDate, Description, DueDate

The Sub Report table looks like this:

+--------------+--------+-----------------------------------------------------------+---------------------+--------------------------------+-----------+-----------+
|  Invoice #   | Vendor |                        VendorName                         |       InvDate       |          Description           | OverdueBy |  Amount   |
+--------------+--------+-----------------------------------------------------------+---------------------+--------------------------------+-----------+-----------+
|     00000418 |   3293 | Seaforth Civil Pty Ltd                                    | 2014-04-24 00:00:00 | Plant Hire, Materials, Repairs |        10 | 236602.94 |
|     00001113 |   3377 | 3D Engineering Services Pty Ltd                           | 2014-05-31 00:00:00 | Manufacture Handrails          |       -20 | 10236.60  |
|     00001701 |   3273 | ADE Electrical Pty Ltd                                    | 2014-05-21 00:00:00 | Test & Tag 15m, 3 phase lead   |         6 | 22.00     |
|     00001921 |   2155 | Electrical Refrigeration Enterprises                      | 2014-05-09 00:00:00 | Repair sensors and Lights      |        18 | 444.40    |
|     00001932 |   2155 | Electrical Refrigeration Enterprises                      | 2014-05-09 00:00:00 | Relocated button Security Door |        18 | 104.50    |
|     00021458 |   1946 | VacUDigga Pty Ltd                                         | 2014-04-15 00:00:00 | Vac Excavation 11/4/14         |        10 | 1729.75   |
|     00021869 |   1946 | VacUDigga Pty Ltd                                         | 2014-05-12 00:00:00 | Vacuum Excavation 12/5/14      |       -20 | 1694.00   |
|      0003091 |   2677 | Parker's Liquid Waste Pty Ltd T/As Parker's Liquid Waste  | 2014-05-06 00:00:00 | Pump out holding tank          |       -20 | 267.30    |
| 003000775835 |    685 | ADT WSale Tools P/L T/As Glenfords Tool Centre Gladstone  | 2014-05-13 00:00:00 | 9" End Nippers                 |       -20 | 116.00    |
| 003000776496 |    685 | ADT WSale Tools P/L T/As Glenfords Tool Centre Gladstone  | 2014-05-28 00:00:00 | Air Tool                       |       -20 | 19.00     |
|     00980812 |    590 | Followmont Transport                                      | 2014-05-27 00:00:00 | Transportation                 |       -16 | 441.54    |
|       104760 |   2816 | Tank Services P/L T/As The Tasman Tank Co                 | 2013-02-13 00:00:00 | Fit Ext Nut Caps to Tank 2     |       475 | 2141.93   |
|       107776 |   3385 | GoTurf                                                    | 2014-05-08 00:00:00 | TURF                           |       -20 | 25942.40  |
|       119023 |    808 | The Hose Shed                                             | 2014-05-05 00:00:00 | Flange and Plugs               |       -20 | 522.80    |
+--------------+--------+-----------------------------------------------------------+---------------------+--------------------------------+-----------+-----------+

How do I link the data to make this work?

Upvotes: 0

Views: 730

Answers (1)

shawnt00
shawnt00

Reputation: 17935

You need to add a parameter to the subreport and pass it from the parent.

Upvotes: 1

Related Questions