November 9, 2012 at 4:41 pm
I have a table that we created and populated from COBOL flat files and our Marketing department would like the data displayed in a matrix report format. I cannot seem to sort out the design of the Report in Report Builder 3.0.
Example:
If you do a select * statement on the table you would get:
END_APOL_NO END_NUM1 END_LIMIT1 END_PREM1
FOL0196401 FO 5000 0.00
FOL0196401 FO 3050 0.00
FOL0196401 FO-2080 23.00
FOL0198101 GL-76 0 0.00
FOL0198101 GL-10 0 0.00
FOL0198102 FO-3030 0.00
FOL0198102 GL-10 0 0.00
FOL0198102 FO-3000 0.00
FOL0198103 GL-8900 0.00
FOL0198103 FO-3000 0.00
FOL0198103 GL-73 0 0.00
FOL0198103 INS LVL 10 -510.00
FOL0198103 FO-405B1 -69.00
FOL0198103 FO 14280 0.00
FOL0199402 ML-2160 -7.00
What The would like to see is this:
POLICY NO END_NO LIMIT PREMIUM END_NO LIMIT PREMIUM
FOL0196401 FO 5000 0.00 FO 305 0 0.00
FOL0198102 FO-3030 0.00 GL-10 0 0.00
FOL0198103 GL-8900 0.00 FO-300 0 0.00
So I would guess I would have a row group for END_APOL_NO (POLICY NO)
But I am not sure how to do the Column Groupings. Some of the Policies will have only 1 or 2 columns but some may have as many as 72. I would think the columns would have to be generated dynamically based on the policy number.
I may be making this more difficult than I need to, but i could use any assistance that would come my way.
Thank You
November 12, 2012 at 7:31 am
If you format your data sections, it makes your question a little easier to read. Is the IFCode Shortcuts on the left-hand side of your window like:
END_APOL_NO END_NUM1 END_LIMIT1 END_PREM1
FOL0196401 FO 500 0 0.00
FOL0196401 FO 305 0 0.00
FOL0196401 FO-208 0 23.00
FOL0198101 GL-76 0 0.00
FOL0198101 GL-10 0 0.00
FOL0198102 FO-303 0 0.00
FOL0198102 GL-10 0 0.00
FOL0198102 FO-300 0 0.00
FOL0198103 GL-890 0 0.00
FOL0198103 FO-300 0 0.00
FOL0198103 GL-73 0 0.00
FOL0198103 INS LVL 1 0 -510.00
FOL0198103 FO-405B 1 -69.00
FOL0198103 FO 1428 0 0.00
FOL0199402 ML-216 0 -7.00
POLICY NO END_NO LIMIT PREMIUM END_NO LIMIT PREMIUM
FOL0196401 FO 500 0 0.00 FO 305 0 0.00
FOL0198102 FO-303 0 0.00 GL-10 0 0.00
FOL0198103 GL-890 0 0.00 FO-300 0 0.00
Are you saying that your source table has a variable number of columns (up to 72) based upon how many premium periods a policy has?
If this is the case, why don't you try UNPIVOT to get all of those columns into rows, then sort by your policy number and then put that into your report.
http://msdn.microsoft.com/en-US/library/ms177410(v=SQL.90).aspx
See if that does the trick.
Good luck,
Rob
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply