Create set of 3 columns dynamically based on user prompt

[raghurambo]

Let me first explain the data structure. The data comes from Excel spreadsheets for each month for Columns 1 through 9. Columns 7, 8 and 9 vary from month to month for a given set of Columns 1 through 6. The user wants the option of choosing which months he wants to compare for Columns 7, 8 and 9 and below is the format that he wants the data to be generated in Excel.

                                                                                              Month 1                              Month 2                          Month 3           
Col. 1     Col. 2     Col. 3      Col. 4     Col. 5    Col. 6     Col. 7     Col. 8    Col. 9      Col. 7     Col. 8    Col. 9      Col. 7     Col. 8    Col. 9

The user could select anywhere from 2 to several months to display the data. I am not able to figure out generating Columns 7, 8 and 9 dynamically in sets as determined by the number of months chosen by the user at the prompt. Please advise.

[Francis aka khayman]

do columns 7, 8 and 9 come from the same hierarchy?

[raghurambo]

There is no hierarchy as such. The three columns are independent of each other. Each month's spreadsheet contains data from all 9 columns. Please let me know if you have more questions.

[Francis aka khayman]

you mean your data source is excel? not a cube or DMR?

[Bark]

If the excel comes with all 9 columns each month, how do you get previous months? where are they stored?

[raghurambo]

To answer khayman's query: The source is Excel for each month's data

To Answer the next one: The data is stored in the database by adding the Cycle Date as an additional column.

[Francis aka khayman]

wait wait wait... the first answer is excel is the datasource then the second answer  says data is stored in the database?!?!?!  which is which?!?!?! :-\

[raghurambo]

Sorry for the confusion. The nine columns from the Excel spreadsheet are loaded into the database. The "Cycle Date" columns (with values of "Month 1", "Month 2" etc.) is the tenth column in the database table. Hope this clears the air.

Not sure if it helps but there is a flexibility of writing views and storing in the database.

[Bark]

Sorry for the confusion. The nine columns from the Excel spreadsheet are loaded into the database. The "Cycle Date" columns (with values of "Month 1", "Month 2" etc.) is the tenth column in the database table. Hope this clears the air.

Not sure if it helps but there is a flexibility of writing views and storing in the database.

Sorry but I still don't see how the data is stored... could you please give us an example of the table? Maybe an excel file?

If the 10th column is used to know what month the data in columns 7, 8 and 9 belongs to, what happens with columns 1 to 6? are they repeated each month?

Regards,

Bark

[raghurambo]

Yes. For a given set of values for columns 1 to 6, the values in Columns 7, 8 and 9 (may or may not) change every month.

The Excel file from the business has only 9 columns (Columns 1 to 9). The Cycle Date column values are added during the ETL process and the new month's value (say, Feb 2014) is added when loading Feb data.