| Author |
Topic |
|
Annakonda
Starting Member
17 Posts |
 Posted - 2009-09-22 : 08:04:00
|
| Hi everyone,I didn't know how to formulate the subject the best way, so pls don't judge :-)The idea is to have 1 table as a template which would be used multiple times within the same report, but how many times I can't know as it depends on the report parameters. As an example, a user gives (today) and (today+7) as the report parameters, so the table in question should come up for each day of the given period (the date to be used as a table filter). Is there a way to make this happen?Thanks a lot.Regards, |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-22 : 09:31:33
|
| Annakonda, I am doing similar but using monthly buckets. I created a column that represents how you want to select data, in your case that column would be the date (minus the time) and then use a Matrix Report. For every day the use selects, the Matrix report will create a new column with the heading being the date.John"The smoke monster is just the Others doing barbecue" |
 |
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-22 : 09:46:33
|
| John, thank you.That could be an elegant solution, but I need another presentation. My table is not a matrix, rather a table with groups (allowing drill-downs). Once I have the reporting period, I have to show all tables at once, day by day. |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-22 : 10:17:41
|
| Not sure you are aware that a Matrix report definitely supports drill down. It has saved my but a number of times :)Is there a maximum number of days you allow? Maybe code for the maximum and use parameter passing to determine what columns to display?John"The smoke monster is just the Others doing barbecue" |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-24 : 02:41:55
|
quote:
Originally posted by JCirocco
Not sure you are aware that a Matrix report definitely supports drill down. It has saved my but a number of times :)
:-)I use a "corporate house style", so it's not really my free choice.quote:
Is there a maximum number of days you allow?
No. I would expect that most of the times it would be a week. But there is no garantee that the user would need more (or less) days.quote:
Maybe code for the maximum and use parameter passing to determine what columns to display?John"The smoke monster is just the Others doing barbecue"
How? You mean in the "Report parameters"?Thank you very much for helping me.Anna |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-24 : 03:31:47
|
quote:
Originally posted by JCiroccoMaybe code for the maximum and use parameter passing to determine what columns to display?
Realized only now that we are speaking about different things. The number of columns always remains the same. The idea is to show the same table (but with different values, of course) day per day. Thus, my report page might contain either 1 table or multiple tables one after the other. But how many days to show, I can't know for sure in advance. |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-24 : 08:19:05
|
quote:
Originally posted by Annakonda
... As an example, a user gives (today) and (today+7) as the report parameters, so the table in question should come up for each day of the given period (the date to be used as a table filter)...
In the above example, are you saying that you want to select the last 7 days of data? Or Just those 2 days, Today and 7 days ago?Can you possibly give sample data and what the expected ersults would be?John |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-24 : 08:28:12
|
| I'll explain the meaning of the report.My chief wants to see the cash situation at any day, for any period in perspective. For example, today he wants to estimate the situation for today and tomorrow, so he enters today and tomorrow as the report parameters. In response, my report should output the same table (which shows totals and details per bank as well as sales and purchase invoices) TWICE - once for today and once for tomorrow. Another day he might be willing to see the week perspective (my report should show 5 tables - one for each working day). And so on...My idea is to re-use the template table as many times, as "declared" at run-time, instead of copy-pasting the same template within the report (if so, how many times then?). But I don't know how to implement this... In the list of supported functions I find nothing like "loop" or "while" or similar :(Is it possible? |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-24 : 10:14:23
|
quote:
Originally posted by Annakonda
...For example, today he wants to estimate the situation for today and tomorrow, so he enters today and tomorrow as the report parameters. In response, my report should output the same table (which shows totals and details per bank as well as sales and purchase invoices) TWICE - once for today and once for tomorrow...
How will the two days be displayed him? One page with today, a page break and then a page for tomorrow? OR as a column for today and another column for tomorrow?John |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-25 : 02:41:01
|
quote:
Originally posted by JCirocco
quote:
Originally posted by Annakonda
...For example, today he wants to estimate the situation for today and tomorrow, so he enters today and tomorrow as the report parameters. In response, my report should output the same table (which shows totals and details per bank as well as sales and purchase invoices) TWICE - once for today and once for tomorrow...
How will the two days be displayed him? One page with today, a page break and then a page for tomorrow? OR as a column for today and another column for tomorrow?John
As 2 separate tables, not necessarily with a page break in between.Thank you. |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-25 : 07:55:28
|
| Does a simple:Where (Report_Date > @StartDate) AND (Report_Date < @EndDate + 1)Order By Report_Datework?John |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-25 : 08:02:35
|
quote:
Originally posted by JCirocco
Does a simple:Where (Report_Date > @StartDate) AND (Report_Date < @EndDate + 1)Order By Report_Datework?John
Where do you suggest to put this? |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-25 : 08:05:39
|
| In SQL statement like:Select Field11, Field2, Field3, etc...From TableNameWhere (Report_Date > @StartDate) AND (Report_Date < @EndDate + 1)Order By Report_DateJohn |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-25 : 08:23:52
|
| Sorry for being dumb, I'm not an expert and SQL is not my full time job... It might be very simple, but I don't get it...When I write a query, I get a results set (one of the columns is "due date"). But how can I pass the number of "repetitions" to SRSS (how many times to reproduce the table based on the template)? In my understanding, if I put 1 table in a report template, I'll have just 1 table as the output. If I need 2 tables, I "draw" 2 templates in SRSS. I would like to avoid this and make it more general. Is it possible? If not, then "no is no". |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-25 : 08:48:31
|
| not dumb, we all start somewhere... By saying you need one table per day, you can have both days in a single table but have reporting services break them apart. Since you posted this in reporting services forum I am assuming that is where you are going to do this.John |
|
|
|
Annakonda
Starting Member
17 Posts |
Posted - 2009-09-25 : 09:06:00
|
quote:
Originally posted by JCirocco
By saying you need one table per day, you can have both days in a single table but have reporting services break them apart. John
Yes? How?My query provides me with due dates per line (invoice in my case), do I need anything else? |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-09-25 : 09:14:51
|
| Bunch of different ways. I am assuming that the time portion of your due date field either has zeros (midnight) for the time or all zeros. Make sure the first OrderBy in your SQL is by Due Date. If you want to create a tabular report, use the report wizard and select Due Date for the Page. Wizard helps beginners (and us lazy people) to create a functional report fast.John |
|
|
|
|
How to create a table in a loop?