Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help on this SSRS project Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2013 11:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:25 AM
Points: 21, Visits: 65
I need to generate a drill-down tabular report with the following requirements:
Here're the columns to be included in the main report:
Batch Barcode
status of batch
Number of samples
Location shipped from
Location shipped to
Date the batch created
Date the batch received
shipping username
receiving username
In the above columns Batch Barcode should be drilled down with a hyper-link to a sub-report which includes the following columns:
Sample Barcode
sample status
Batch Barcode
Receiving Username

In the meantime, the report should provide the ability to query by the following inputs:
a. Batch Barcode
b. Sample Barcode
c. Date range (user specified start date and end date )

A.The report shall return an error message to the user if the range between the user entered start date and entered end date of shipping exceeds one year.
B. If the user set a start date and end date is not set the report shall display data from start date to one year after the start date.
C. If the user set a end date but not a start date the report shall display data from one year before the end date
D. If neither a start date nor a end date is specified the report shall display data starting one year before the date of querying.

It's straightforward to create a main report with a drill-down sub-report, however I'm kind of stuck at how to implement the query based on the three criteria (Batch code shown both in main report and sub-report, Sample barcode shown only in sub-report and the tough one will be the date range)
I'm the only one responsible for generating report in the group. Please help on that!
Thank you a whole bunch!
Maggie
Post #1484592
Posted Thursday, August 15, 2013 5:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Maggie,

I'm not clear on which piece is giving you trouble. Are you having trouble with the query itself, or with passing the values from the main report to the drill report(s)?




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1484949
Posted Thursday, August 15, 2013 8:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:25 AM
Points: 21, Visits: 65
Hi, Tim
Thank you for your reply!
My question is that the [sample barcode] only shows on the sub-report but I need to generate a search field on the main report for sample_barcode, how to achieve that?
Post #1484970
Posted Saturday, August 17, 2013 1:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
If I understand correctly, you want to filter the sample barcode from the main report even though this field is not present in the main report?

If that's the issue, you can simply add a parameter to the main report to allow you specify which sample barcode values should be present. You don't necessarily have to use that parameter in the query for the main report. When linking to the subreport, be sure to include the sample barcode value as a parameter mapping from main report to subreport.

If this doesn't answer your question, perhaps you could include some code or screenshots for clarification?




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1485514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse