Tables with Dynamics Field Values

[Luvsql]

I have a stored procedure with a parameter called #Days so depending on how many days you enter when running the report, the table will be updated with different values. For example, if I enter 10 as the parameter, the end columns of the table will be +1 Wed Nov 26, +2 Thur Nov 27 up to +10 Fri Dec 5. I would like to display the last column on the report, but it changes with each parameter I enter.

Is there a way to have a calculated field that can look at the values of a table then grab the last column of that table?

 

[Charliy]

If you're starting with the current date, then the last date of your report is CurrentDate+{?Days}

 

[Luvsql]

Yes I know the column name will be that but need the value in that field to display. The column names are +1 Wed Nov 26 etc, but the field values for each of these columns is different.

 

[Charliy]

If you're preselecting the data based on date, this sounds like a good application for a Crosstab. It will adapt to the number of dates in your data.

 

[Luvsql]

I don't want all the values to display across the top. I only want the last field. If I enter 7 days, 30 days, 120 days that would put up to 120 columns across the top. I only want to very last one.

 

[Charliy]

create a summary field that does a Maximim of your date field. You can put it in the report header.

 

[Luvsql]

Which field would I use in the formula? The field names/number of fields in the table change every time the report is run. You're assuming this one field is static but it's dynamic.

Here is what the table is when I choose parameter is 3 (Field Name - field values)

Code - STAND0001
1 Thur Nov 26 - 1557
2 Fri Nov 27 - 1547
3 Sat Nov 28 - 1496

That's just for one code.

If I enter parameter value of 5 days:
Code - STAND0001
1 Thur Nov 26 - $1557
2 Fri Nov 27 - $1547
3 Sat Nov 28 - $1496
4 Sun Nov 29 - $1598
5 Mon Nov 30 - $1588

Notice the number of fields in the table change depending on the parameter entered. The number of fields always changes.

On my report, I entered 3, I want to show $1496.
If I entered 5 days, I want to show $1588

 

[BettyJ]

Will you be able to suppress the section using the condition not(onlastrecord)

 

[Charliy]

If I understand correctly, your SQL is creating a data source that changes depending on the number of days selected.

If you select 3 you get something like DATE1, DATE2, DATE3 as field names.

Can you alter the SQL to do the dates in reverse order, so that the highest date is always DATE1?

 

[SkipVought]

Hi,

You're obviously not using an IT maintained normalized table.

This table is actually a pivot report. I'd try to find who's created this report/table (I'd guess it was done in Access or Excel) and what query/source tables they used to create the pivot. Then go after the source data directly, which would not have "dynamic field names.