| Author |
Topic |
|
under2811
Constraint Violating Yak Guru
366 Posts |
 Posted - 2011-05-26 : 03:21:15
|
| Hi All Creating report as below in my report columns i need to show as columns like ID, Name, Country, Monthly expenses, agents, %Ret, MOM, Equity,Annual Expenses, Total Revenues, Party Passing one parameter as Type = "Annual" & Type = "Monthly". Based on selection Type. If Type is Annual then columns need to show as ID, Name, Country,Equity,Annual Expenses, Total Revenues, Party using table A_Finance,B_Region and some more tables. But when parameter selection is Type = "Monthly" then columns like ID, Name, Country, Monthly expenses, agents, %Ret, MOM,Total Revenues, Party using different tables. So i have created conditions like if @type is Annual then use one set of joins and if @type is Monthly then use different set of joins (sql syntax as if type = 'Annual' begin end else ...(like that) but what i observed is only columns coming in first set of joins is available for Data set. i.e.ID, Name, Country,Equity,Annual Expenses, Total Revenues, Party But not getting Monthly expenses, agents, %Ret, MOM columns...why it is like that if i am using two different dataset for two tables, for 1st table on report use first join conditions and populate 1st set of columns and visible only when type is Annual and for 2nd table use 2nd join on visibility as if type = monthly and populate columns in 2nd data set. then in back ground of SQL SERVER execute both data set -- correct me if i am wrong. this is hitting performance. what will be possible solutions in such cases.T.I.A |
|
|
Bazalddo
Starting Member
22 Posts |
Posted - 2011-05-26 : 06:00:30
|
| Hi,This can be quite convoluted but it can be achieved, you might be better off using different datasets but if need be then try this....If returning a different set of results which uses an IF statement all result sets have to have the same number of columns and the same names. The data can be different though.Once you get this working, you can change your column headers dynamically by using the parameter so switch(@type = monthly, blah, @type = annual, blah etc)In the event you have more columns in one result set than another then just insert blank columns into the results set with less columns i.e SELECT ID, Name, Country, NULL AS Equity, Annual Expenses, Total Revenues, Party etcYou could then hide the columns dynamically based on the parameter selectionHope this helpsBazShahbaz AhdiApps DeveloperODEON/UCI Cinema |
 |
|
|
|
|
|
Two data sets having population of columns issue.