I am loading multiple sheets from an Excel doc into my report, but need to know which sheet the data comes from. This means I need a column in every row with the sheet name. This is what I'm trying, but it doesn't work:
SET i = JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC;
FOR Each m in $(i);
text('$(m)') as Month,
(ooxml, embedded labels, Table is [$(m)]);
The data loads, but Month is blank on every row. How can I do this?
Thanks for the reply, but as soon as I place any of the elements past JAN in quotes I get a 'Field not found' error because the sheet cannot be loaded. If I just put JAN in quotes the first 12th of all the row numbers have 'JAN' populated in them.
Thanks Jonathan Dienst and Marcus Sommer for trying to help, but the query only works without any quotes - I too have tested it! The query will run with JAN in quotes, but no more than that for some reason. I guess this is the difference between using a set and using a variable.