Table Format/Display problem -- sql server reporting services

sql server reporting services : Table Format/Display problem

Den
2/2/2006 9:54:54 PM

OK, Hopefully this is an easy one, but it's got me stumped. I'm trying to
build a table that will show a daily amt total and a month-to-date amt total
I'm grouping the data by brand. Below is a partial result set. I have one
column with ifamt that needs to be filtered by report date. and one column
that will be filtered by report month using the same result set. Here it is:

ifamt BrandGrp Transaction Date Mth EndDate CountryCd
242.25 Brand1 2006-01-31 2006-02-25 10
3933.00 Brand1 2006-01-31 2006-02-25 10
150.10 Brand1 2006-01-31 2006-02-25 10
153.00 Brand1 2006-01-31 2006-02-25 10
2388.55 Brand2 2006-02-01 2006-02-25 10
139.90 Brand2 2006-02-01 2006-02-25 10
207.70 Brand2 2006-02-01 2006-02-25 10
1608.85 Brand2 2006-02-01 2006-02-25 10
12.60 Brand2 2006-01-31 2006-02-25 10

Thanks for the help

Dan
2/3/2006 7:36:24 AM

If I understand the requirements correctly, you want the report to look
something like the following:

Month: January
Day ifamt
1 3432.39
2 43234.52
3 2349.39
Month Total xxxxxx.xx

If this is correct, this is probably how I would approach it. Using a
table, I would make the first group be grouped on the expression
=Format("MM", Fields!DATE.Value) This will group on the Month number.

The second group would be =Format("dd", Fields!DATE.Value)

In the table header, you can put your column headings like Day &
Amount.

In group 1 header, I would merge the cells and do something like
="Month:" & Fields!DATE.Value. (you can format it to be how you'd
like).

I would delete the details row.

In group 2 footer (Daily Totals), there would be two textboxes.
=Format("dd", Fields!DATE.Value) and =sum(Fields!ifamt.Value)

In group 1 footer (Monthly Totals), you can do =Format("MM",
Fields!DATE.Value) and =sum(Fields!ifamt.Value)

Now a lot of this leaves the formatting up to you - but I'm hoping the
trick you were looking to find was grouping on month and day.

There are dozens of ways to do this depending on how you return the
data. If you returned the data already grouped by the SQL by Day, You
could just use 1 group in the report on the month, and in the details
have the daily info.

Let me know if I misunderstood the requirements.

Dan

Dan
2/3/2006 12:17:55 PM

I think I might understand. Feel free to contact me offline if I'm
still misunderstanding.

Your problem is that you need to get the data for the entire month in
order to get the MTD. However, you don't want to display the details
for any day unless it is the date the user specified when they ran the
report.

I'm sure there are many solutions. I can think of two right off the
top of my head (keep in mind they might not be the most efficient of
solutions).

1. You could return one record per brand for the day run. A subselect
query could get the MTD amount. (or it might be better to have the
main query get the MTD and the sub query get the values for the day).

2. You could hide the rows based on the parameter value. If the row's
date is the same as the parameter show, else hide.

Would any of that work, or am I misunderstanding the requirements
again?

Contact me offline and I would be glad to try and take a look at it
(with any private info removed).

Dan

Den via SQLMonster.com
2/3/2006 4:26:39 PM

Actually, I have it working like that. What I really need is something along
the lines of the following:

Brand Type DailyAmt MTDAmt
Brand1 1232.00 10564.00
Brand2 1564.00 8674.00
Brand3 950.00 7895.00

What I end up with is something like this
Brand Type DailyAmt MTDAmt
Brand1 0.00 1232.00
Brand2 0.00 1564.00
Brand3 000 950.00
Brand1 0.00 1123.00
Brand2 0.00 1456.00
Brand3 000 1020.00
Brand1 1232.00 8794.00
Brand2 1564.00 4564.00
Brand3 950.00 2134.00

I basically get Day 1, day2, day3... Where 0 replaces results not equal to
report date.
*note: Not actual results. What I really need is to display Daily for the
Report Date, and sum all days For month to date and group by brand.

Does that make sense?

[quoted text, click to view]

--
Message posted via SQLMonster.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn