August 14, 2013 at 11:06 pm
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
August 15, 2013 at 5:25 pm
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
August 15, 2013 at 8:41 pm
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?
August 17, 2013 at 1:24 pm
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply