February 26, 2014 at 1:40 pm
Sorry for the very generic title, I just couldn't figure out what to put as a title.
So here's my dilemma. I have a report which is sourced from several SQL tables. The main part of the report (which I call the "header info" is the heading information for a medical claim - trying not to go into too much detail due to privacy issues) comes from one table. This table has 1 row per claim id basically.
Then there is a section of the report which will contain diagnostic codes. There will always be at least 1 but there can be up to 24 diagnostic codes per claim, and these come from a "claim detail" table, with a FK relationship to the "claim header" table. So, basically, a 1-to-many header to detail relationship. Easy so far.
Here's the thing. The form that I am printing this information onto has 2 rows of 6 "boxes" for diagnostic information, so up to 12 per page. If there are more than 12 diagnostic codes, the new ones are to be printed on the next page, repeating the "header info" from the previous page.
I am trying to figure out how to code the report so that it will print the first (up to) 6 codes on the first line, the next (up to) 6 on the 2nd line, then if there are more than 12, repeat with the remaining codes on a new page. One way to do this is to create 12 "code fields" (e.g. code01, code02, code03,...) and then write the select to drop the diagnostic codes into those fields, but it seems like there should be a more elegant solution. In addition, while there are up to 24 codes now, this may change and I'd rather not hard-code that into the query (in fact, the fact that there are 24 is a recent thing - it used to be we could only have a maximum of 8 codes.)
Has anyone else run into this type of situation? If so, how did you go about solving it?
Thanks in advance!
Timothy
February 26, 2014 at 8:30 pm
This is an interesting problem. It seems like, and I haven't tried this so I don't know for sure if it'll work, you need to have a Table with a header or group header that has your claim information with the repeat header property set to true so that this header information is repeated on each page and keeping all the rest of the items in the correct location so it prints on the form correctly. Then another group header into which you'd place a matrix for the diagnostic codes which you would somehow force to use 2 rows with a max of 6 columns. I'm not even sure that is possible, but that's what I'd start with. I know you can force a table to start a new page after N rows with a hidden group, but I don't know if you can do that with a column group in a matrix.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2014 at 6:35 am
Jack - thanks for the answer. I will try that. My other thought is to write a C# script to handle populating the fields, but I'd like to stay away from that if possible. It seems to me that this would be something that has been done before but the only solution I've seen is having column identified as fld01, fld02, fld03, etc. which while it works leads to problems when the number of fields changes (as it has for me.)
February 28, 2014 at 6:48 am
Check out this article on horizontal tables.
http://blogs.msdn.com/b/chrishays/archive/2004/07/23/horizontaltables.aspx
Phil
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply